please help with materialized view question
Im sure its a privilege issue. 1. I have 3 tables with two different owners 2. I want to create a materialized join view of these tables in a 3rd user account. 3. I altered the session to enable query rewrite and query_rewrite_integrity=trusted 4. I granted query rewrite enabled to every owner involved. 5. I can create the materialized view, if I do not join them to one of the owners or leave off 'query rewrite enabled. Here is what I get. create materialized view test build immediate refresh on demand enable query rewrite as select columns from user1.table1, user1.table2, user2.table3 where table1.pk = table2.pk and table2.pk = table3.pk ERROR at line 9: ORA-00942: table or view does not exist I have all privileges on this table otherwise. I can do a select, describe, create materialized view without query rewrite I take out 'query rewrite enabled' and it works. I have granted query rewrite enabled to the user in question -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: please help with materialized view question
I figured it out. I have another problem. I create my materialized view. I now want to write a query that joins it to a transactional table. I want to use query rewrite. Problem is the join is not on the primary key of either table. Is it possible to enable query rewrite without that? I have it in trusted mode? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: please help with materialized view question
public synonym? ryan_oracle @cox.netTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: ml-errorsSubject: please help with materialized view question 12/26/2003 12:54 PM Please respond to ORACLE-L Im sure its a privilege issue. 1. I have 3 tables with two different owners 2. I want to create a materialized join view of these tables in a 3rd user account. 3. I altered the session to enable query rewrite and query_rewrite_integrity=trusted 4. I granted query rewrite enabled to every owner involved. 5. I can create the materialized view, if I do not join them to one of the owners or leave off 'query rewrite enabled. Here is what I get. create materialized view test build immediate refresh on demand enable query rewrite as select columns from user1.table1, user1.table2, user2.table3 where table1.pk = table2.pk and table2.pk = table3.pk ERROR at line 9: ORA-00942: table or view does not exist I have all privileges on this table otherwise. I can do a select, describe, create materialized view without query rewrite I take out 'query rewrite enabled' and it works. I have granted query rewrite enabled to the user in question -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: please help with materialized view question
I figured it out. I need some help with query re-write. Im not sure its possible. My materialized view joins 3 tables on the primary key/foreign key. I have a query that would join that materialized view to a third transactional table, but that join is not on any primary key or foreign key. I cant get it to re-write my query. My query joins 4 tables. 3 are in the materialized view. One is not. is this possible? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: please help with materialized view question
Warning: I have not actually used query rewrite in this way, so take this with a grain of salt. If you're joining the MV directly to a table, what is there to rewrite? If you were joining the tables that make up the MV, and doing so on the same key that was used to create the MV, and joining that result to a transactional table, it would make sense to use query rewrite. Based on your statement though, I don't see the need. Clarification? Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/26/2003 10:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: please help with materialized view question I figured it out. I have another problem. I create my materialized view. I now want to write a query that joins it to a transactional table. I want to use query rewrite. Problem is the join is not on the primary key of either table. Is it possible to enable query rewrite without that? I have it in trusted mode? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: please help with materialized view question
my bad on the explanation. I have 4 tables. 3 are non-transactional. These are joined in a primary key/foreign key relationship. These are going in the materialized view. I want to join my 4th table to my materialized view. 1. The application current has code that joins all 4 tables. I dont know if they will re-write this. 2. The refresh on that materialized view is possibly time consuming. Im worried about stale data. I want oracle to determine if its stale or not. If I explicitly hit the materialized view, I have to handle that with code. We do nightly data loads, then the materialized view needs to be reloaded. This could take a little while. From: [EMAIL PROTECTED] Date: 2003/12/26 Fri PM 02:09:27 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: please help with materialized view question Warning: I have not actually used query rewrite in this way, so take this with a grain of salt. If you're joining the MV directly to a table, what is there to rewrite? If you were joining the tables that make up the MV, and doing so on the same key that was used to create the MV, and joining that result to a transactional table, it would make sense to use query rewrite. Based on your statement though, I don't see the need. Clarification? Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/26/2003 10:44 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: please help with materialized view question I figured it out. I have another problem. I create my materialized view. I now want to write a query that joins it to a transactional table. I want to use query rewrite. Problem is the join is not on the primary key of either table. Is it possible to enable query rewrite without that? I have it in trusted mode? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Warning: I have not actually used query rewrite in this way, so take this with a grain of salt. If you're joining the MV directly to a table, what is there to rewrite? If you were joining the tables that make up the MV, and doing so on the same key that was used to create the MV, and joining that result to a transactional table, it would make sense to use query rewrite. Based on your statement though, I don't see the need. Clarification? Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/26/2003 10:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: please help with materialized view question I figured it out. I have another problem. I create my materialized view. I now want to write a query that joins it to a transactional table. I want to use query rewrite. Problem is the join is not on the primary key of either table. Is it possible to enable query rewrite without that? I have it in trusted mode? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: please help with materialized view question
Normally you can get extra tables involved with an MV by creating a Dimension that describes all the relationships between the tables in the MV and the tables outside the MV - but the only times I've done this, the extra tables have always been at the parent end of a parent/child link to a table in the MV. Given the way the 'create dimension' defines levels and hierarchies, I think this may be a requirement; so you may not be able to do what you want to do. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 6:59 PM I figured it out. I need some help with query re-write. Im not sure its possible. My materialized view joins 3 tables on the primary key/foreign key. I have a query that would join that materialized view to a third transactional table, but that join is not on any primary key or foreign key. I cant get it to re-write my query. My query joins 4 tables. 3 are in the materialized view. One is not. is this possible? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
dropping materialized view
Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen
Re: dropping materialized view
Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dropping materialized view
select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'SNAP$_MEDIUMS') -Oorspronkelijk bericht- Mohammed, Thanks for your reaction but this doesn't help. I think table must be snap$_mediums and when I issue your query I get: TABLE_NAME CONSTRAINT_NAMESTATUS -- -- DEBTOR_CLAIM_COMPONENTSFK_DCC_MEDIUMS DISABLED I even tried disabling all constraints. I tried removing the view Through enterprise manager, it looked ok because it doesn't show there Anymore but in the dictionary it does. I tried set constraints all deferred; drop table snap$_mediums; alter table snap$_mediums drop primary key all do not seem to work, I am starting to think I have somehow corrupted the dictionary. I hope you can shed some light on this Regards, Jeroen Van: Mohammed Shakir [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 17:59 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: dropping materialized view Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeroen van Sluisdam INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dropping materialized view
Disabling constraint (when you want to drop the parent table) will not help. This should help: Alter table DEBTOR_CLAIM_COMPONENTS drop constraint FK_DCC_MEDIUMS. After this you should be able to drop MV. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jeroen van Sluisdam Sent: Tuesday, December 09, 2003 2:40 PM To: Multiple recipients of list ORACLE-L select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'SNAP$_MEDIUMS') -Oorspronkelijk bericht- Mohammed, Thanks for your reaction but this doesn't help. I think table must be snap$_mediums and when I issue your query I get: TABLE_NAME CONSTRAINT_NAMESTATUS -- -- DEBTOR_CLAIM_COMPONENTSFK_DCC_MEDIUMS DISABLED I even tried disabling all constraints. I tried removing the view Through enterprise manager, it looked ok because it doesn't show there Anymore but in the dictionary it does. I tried set constraints all deferred; drop table snap$_mediums; alter table snap$_mediums drop primary key all do not seem to work, I am starting to think I have somehow corrupted the dictionary. I hope you can shed some light on this Regards, Jeroen Van: Mohammed Shakir [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 17:59 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: dropping materialized view Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeroen van Sluisdam INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dropping materialized view
Oops I feel very stupid, thanks a lot Igor this did the tric. Regards Jeroen -Oorspronkelijk bericht- Van: Igor Neyman [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 21:09 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: dropping materialized view Disabling constraint (when you want to drop the parent table) will not help. This should help: Alter table DEBTOR_CLAIM_COMPONENTS drop constraint FK_DCC_MEDIUMS. After this you should be able to drop MV. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jeroen van Sluisdam Sent: Tuesday, December 09, 2003 2:40 PM To: Multiple recipients of list ORACLE-L select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'SNAP$_MEDIUMS') -Oorspronkelijk bericht- Mohammed, Thanks for your reaction but this doesn't help. I think table must be snap$_mediums and when I issue your query I get: TABLE_NAME CONSTRAINT_NAMESTATUS -- -- DEBTOR_CLAIM_COMPONENTSFK_DCC_MEDIUMS DISABLED I even tried disabling all constraints. I tried removing the view Through enterprise manager, it looked ok because it doesn't show there Anymore but in the dictionary it does. I tried set constraints all deferred; drop table snap$_mediums; alter table snap$_mediums drop primary key all do not seem to work, I am starting to think I have somehow corrupted the dictionary. I hope you can shed some light on this Regards, Jeroen Van: Mohammed Shakir [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 17:59 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: dropping materialized view Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeroen van Sluisdam INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing
RE: dropping materialized view
Don't (feel stupid) :) It happens to all of us... Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jeroen van Sluisdam Sent: Tuesday, December 09, 2003 3:39 PM To: Multiple recipients of list ORACLE-L Oops I feel very stupid, thanks a lot Igor this did the tric. Regards Jeroen -Oorspronkelijk bericht- Van: Igor Neyman [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 21:09 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: dropping materialized view Disabling constraint (when you want to drop the parent table) will not help. This should help: Alter table DEBTOR_CLAIM_COMPONENTS drop constraint FK_DCC_MEDIUMS. After this you should be able to drop MV. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jeroen van Sluisdam Sent: Tuesday, December 09, 2003 2:40 PM To: Multiple recipients of list ORACLE-L select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'SNAP$_MEDIUMS') -Oorspronkelijk bericht- Mohammed, Thanks for your reaction but this doesn't help. I think table must be snap$_mediums and when I issue your query I get: TABLE_NAME CONSTRAINT_NAMESTATUS -- -- DEBTOR_CLAIM_COMPONENTSFK_DCC_MEDIUMS DISABLED I even tried disabling all constraints. I tried removing the view Through enterprise manager, it looked ok because it doesn't show there Anymore but in the dictionary it does. I tried set constraints all deferred; drop table snap$_mediums; alter table snap$_mediums drop primary key all do not seem to work, I am starting to think I have somehow corrupted the dictionary. I hope you can shed some light on this Regards, Jeroen Van: Mohammed Shakir [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 9 december 2003 17:59 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: dropping materialized view Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeroen van Sluisdam INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
How does Oracle determine the materialized view eligible for text
Hi, Anybody knows how Oracle determine the materialized view eligible for textmatch or general rewrite? TIA Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Refresh option for Materialized view , want to use it during refresh - for
Thanks, Arup. Your advice is always good. David From: Arup Nanda [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Refresh option for Materialized view , want to use it during refresh - for Date: Tue, 28 Oct 2003 12:09:25 -0800 David, Glad to be of help. Looking at your list of steps, I see that your questions is whether building an MV on the table T is beneficial. Tables and MVs, on prebuilt table or not, are stored as segments in the database; so space-sise there is no difference, nor there is any change in the way the MV/Table is accessed. There are a few situations where you may want to convert a table to MV. They are: * Building an MV enables Query Rewrite, where Oracle smartly decides to rewrite a user query to select from the MV instead of the main tables. This is not possible on a regular table. The user must explicitly select from it. * If you want to refresh FAST, then MVs are required. You can do a fast refresh on a table, but you have to write your own procedures for that. DBMS_MVIEW package does it for you on MVs. * Your designer software will recognize MV as one and will report it to all users, who are aware of the fact that it's an MV, useful for queries. A mere table will not be clear on that regard. Converting a table to MV does not cost any resource, as the change is done inside the data dictionary only. So, if you are in doubt, you may just convert the table to MV anyway. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 12:04 PM refresh - for Arup, I really appreciate your answer in great details. I got on prebuilt table work. Thanks a lot for your help. Here is another question: Do you see any advantage to use materialized view on prebuilt table for my data loading over just simple renaming tables as steps below: 1. create table t that is always accessed by applications 2. create table t1 that is a temp table for loading 3. load data into table t1 4. rename table t to table t2 5. rename table t1 to t 6. rename table t2 to t1 7. truncate table t1 for next day loading David From: Arup Nanda [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Refresh option for Materialized view , want to use it during refresh - for Date: Fri, 24 Oct 2003 18:04:33 -0800 David, Answers to your questions: (1) Without knowing your exact needs, I wil offer a few different scenarios. I am assuming that you are doing a complete refresh every time. The following pertain to that. Say, your name of the MV is MV1. Here are the steps the first time. 1. Create table MV1 2. Create MV MV1 on that table. When you want to refresh complete: 1. Create table MV1_TEMP. Choose a suitable method: CTAS across dblink (with NOLOGGING), SQL*Loader, Direct Load Insert, or simpley exp/imp. 2. Drop MV MV1. This drops the MV but doesn't drop the table. 3. Drop table MV1. 4. Rename table MV1_TEMP to MV1. 5. Recreate MV MV1. 6. Allow users to proceed as usual. Note the time consumed between Steps 2 and 6 are in the order of a few seconds. And it's the only time the users will not have access to the MV, as opposed to a full refresh using dbms_mview.refresh approach., which will lock the MV for the entire duration and generate tons of redo and rollback. Even if you do a incremental refresh, this is still a better approach. In that case, you don't drop the table during the refresh. (2) Yes, the option is available in 8i, at least in 8.1.7. Make sure the syntax is correct. create materialized view MV1 on prebuilt table refresh fast as select ... from In the article I mentioned, you can find the complete syntax. www.proligence.com/downloads.html is the site. It also dscribes a step by step solution to the issue and compares the common solution with this new one. Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach
Re: Refresh option for Materialized view , want to use it during refresh - for
Arup, I really appreciate your answer in great details. I got on prebuilt table work. Thanks a lot for your help. Here is another question: Do you see any advantage to use materialized view on prebuilt table for my data loading over just simple renaming tables as steps below: 1. create table t that is always accessed by applications 2. create table t1 that is a temp table for loading 3. load data into table t1 4. rename table t to table t2 5. rename table t1 to t 6. rename table t2 to t1 7. truncate table t1 for next day loading David From: Arup Nanda [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Refresh option for Materialized view , want to use it during refresh - for Date: Fri, 24 Oct 2003 18:04:33 -0800 David, Answers to your questions: (1) Without knowing your exact needs, I wil offer a few different scenarios. I am assuming that you are doing a complete refresh every time. The following pertain to that. Say, your name of the MV is MV1. Here are the steps the first time. 1. Create table MV1 2. Create MV MV1 on that table. When you want to refresh complete: 1. Create table MV1_TEMP. Choose a suitable method: CTAS across dblink (with NOLOGGING), SQL*Loader, Direct Load Insert, or simpley exp/imp. 2. Drop MV MV1. This drops the MV but doesn't drop the table. 3. Drop table MV1. 4. Rename table MV1_TEMP to MV1. 5. Recreate MV MV1. 6. Allow users to proceed as usual. Note the time consumed between Steps 2 and 6 are in the order of a few seconds. And it's the only time the users will not have access to the MV, as opposed to a full refresh using dbms_mview.refresh approach., which will lock the MV for the entire duration and generate tons of redo and rollback. Even if you do a incremental refresh, this is still a better approach. In that case, you don't drop the table during the refresh. (2) Yes, the option is available in 8i, at least in 8.1.7. Make sure the syntax is correct. create materialized view MV1 on prebuilt table refresh fast as select ... from In the article I mentioned, you can find the complete syntax. www.proligence.com/downloads.html is the site. It also dscribes a step by step solution to the issue and compares the common solution with this new one. Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space. The segment that used to be a table simply becomes an MV. (2) When you drop the MV, the MV is gone, but the table remains instact. (3) The table can be create by any means - export/import, SQL*Loader, INSERT APPEND, etc. (4) IT places less strain on the system comapred to the MV refresh option, simply because the MV refresh truncates the segment and then builds it. I presented a paper to the same effect at IOUG Live 2003. You can download a modified version of the same from my website www.proligence.com/downlaods.html, titled Painless Master Table Alter from the Presentations Section. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, October 21, 2003 3:59 AM refresh Hi Gurus, I have a materialized view, which is based on Oracle Apps tables and on remote database. The view refresh takes around ½ hour, during this time period I cannot see any records in the materialized view and therefore my application faces errors. The following is the view definition
RE: Refresh option for Materialized view , want to use it during
This was my original suggestion. The only snag I can think of is if you have a bunch of dependencies like stored procedures and triggers. Even if somebody was selecting from the old table when you renamed it, the select would continue OK ... as long as you don't truncate it. You could even delete from the old table without hosing a running select ... as long as the rollback segment holds up. You could rename the tables, then truncate maybe an hour later. -Original Message- Arup, I really appreciate your answer in great details. I got on prebuilt table work. Thanks a lot for your help. Here is another question: Do you see any advantage to use materialized view on prebuilt table for my data loading over just simple renaming tables as steps below: 1. create table t that is always accessed by applications 2. create table t1 that is a temp table for loading 3. load data into table t1 4. rename table t to table t2 5. rename table t1 to t 6. rename table t2 to t1 7. truncate table t1 for next day loading David -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Refresh option for Materialized view , want to use it during refresh - for
materialized views have the create statements in the database. you just have to refresh them. can you refersh a materialized view in parallel? if not than create can be faster... From: David Boyd [EMAIL PROTECTED] Date: 2003/10/28 Tue PM 12:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Refresh option for Materialized view , want to use it during refresh - for Arup, I really appreciate your answer in great details. I got on prebuilt table work. Thanks a lot for your help. Here is another question: Do you see any advantage to use materialized view on prebuilt table for my data loading over just simple renaming tables as steps below: 1. create table t that is always accessed by applications 2. create table t1 that is a temp table for loading 3. load data into table t1 4. rename table t to table t2 5. rename table t1 to t 6. rename table t2 to t1 7. truncate table t1 for next day loading David From: Arup Nanda [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Refresh option for Materialized view , want to use it during refresh - for Date: Fri, 24 Oct 2003 18:04:33 -0800 David, Answers to your questions: (1) Without knowing your exact needs, I wil offer a few different scenarios. I am assuming that you are doing a complete refresh every time. The following pertain to that. Say, your name of the MV is MV1. Here are the steps the first time. 1. Create table MV1 2. Create MV MV1 on that table. When you want to refresh complete: 1. Create table MV1_TEMP. Choose a suitable method: CTAS across dblink (with NOLOGGING), SQL*Loader, Direct Load Insert, or simpley exp/imp. 2. Drop MV MV1. This drops the MV but doesn't drop the table. 3. Drop table MV1. 4. Rename table MV1_TEMP to MV1. 5. Recreate MV MV1. 6. Allow users to proceed as usual. Note the time consumed between Steps 2 and 6 are in the order of a few seconds. And it's the only time the users will not have access to the MV, as opposed to a full refresh using dbms_mview.refresh approach., which will lock the MV for the entire duration and generate tons of redo and rollback. Even if you do a incremental refresh, this is still a better approach. In that case, you don't drop the table during the refresh. (2) Yes, the option is available in 8i, at least in 8.1.7. Make sure the syntax is correct. create materialized view MV1 on prebuilt table refresh fast as select ... from In the article I mentioned, you can find the complete syntax. www.proligence.com/downloads.html is the site. It also dscribes a step by step solution to the issue and compares the common solution with this new one. Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space. The segment that used to be a table simply becomes an MV. (2) When you drop the MV, the MV is gone, but the table remains instact. (3) The table can be create by any means - export/import, SQL*Loader, INSERT APPEND, etc. (4) IT places less strain on the system comapred to the MV refresh option, simply because the MV refresh truncates the segment and then builds it. I presented a paper to the same effect at IOUG Live 2003. You can
** materialized view fast not working
Hi, I have a table tableA owned by userA. If I create a materialized view inanother schema/user fast and complete refresh set to refresh every 10 minutesthen onlycomplete refresh works and refreshes automatically every 10 minutes. However the fast refresh mv does not refresh automatically. It refreshes fine if the user executes DBMS_SNAPSHOT.REFRESH procedure. If I create a materialized view in the same user fast and complete refresh set to refreshevery 10 minutesboth work fine and refresh automatically every 10 minutes. Can some one help. Thank you Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: ** materialized view fast not working
Does the user have select access on the mlog? A Joshi wrote: Hi, I have a table tableA owned by userA. If I create a materialized view in another schema/user fast and complete refresh set to refresh every 10 minutes then only complete refresh works and refreshes automatically every 10 minutes. However the fast refresh mv does not refresh automatically. It refreshes fine if the user executes DBMS_SNAPSHOT.REFRESH procedure. If I create a materialized view in the same user fast and complete refresh set to refresh every 10 minutes both work fine and refresh automatically every 10 minutes. Can some one help. Thank you Do you Yahoo!? The New Yahoo! Shopping http://shopping.yahoo.com/?__yltc=s%3A15443%2Cd%3A22708228%2Cslk%3Atext%2Csec%3Amail - with improved product search -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ** materialized view fast not working
Mike : Yes the user has select access and is able to do a select on the MLOG table and the original table. Thanks for your help.Mike Spalinger [EMAIL PROTECTED] wrote: Does the user have select access on the mlog?A Joshi wrote: Hi, I have a table tableA owned by userA. If I create a materialized view in another schema/user fast and complete refresh set to refresh every 10 minutes then only complete refresh works and refreshes automatically every 10 minutes. However the fast refresh mv does not refresh automatically. It refreshes fine if the user executes DBMS_SNAPSHOT.REFRESH procedure. If I create a materialized view in the same user fast and complete refresh set to refresh every 10 minutes both work fine and refresh automatically every 10 minutes. Can some one help. Thank you Do you Yahoo!! ? The New Yahoo! Shopping - with improved product search-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mike SpalingerINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Exclusive Video Premiere - Britney Spears
Re: Refresh option for Materialized view , want to use it during refresh - for
David, Glad to be of help. Looking at your list of steps, I see that your questions is whether building an MV on the table T is beneficial. Tables and MVs, on prebuilt table or not, are stored as segments in the database; so space-sise there is no difference, nor there is any change in the way the MV/Table is accessed. There are a few situations where you may want to convert a table to MV. They are: * Building an MV enables Query Rewrite, where Oracle smartly decides to rewrite a user query to select from the MV instead of the main tables. This is not possible on a regular table. The user must explicitly select from it. * If you want to refresh FAST, then MVs are required. You can do a fast refresh on a table, but you have to write your own procedures for that. DBMS_MVIEW package does it for you on MVs. * Your designer software will recognize MV as one and will report it to all users, who are aware of the fact that it's an MV, useful for queries. A mere table will not be clear on that regard. Converting a table to MV does not cost any resource, as the change is done inside the data dictionary only. So, if you are in doubt, you may just convert the table to MV anyway. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 12:04 PM refresh - for Arup, I really appreciate your answer in great details. I got on prebuilt table work. Thanks a lot for your help. Here is another question: Do you see any advantage to use materialized view on prebuilt table for my data loading over just simple renaming tables as steps below: 1. create table t that is always accessed by applications 2. create table t1 that is a temp table for loading 3. load data into table t1 4. rename table t to table t2 5. rename table t1 to t 6. rename table t2 to t1 7. truncate table t1 for next day loading David From: Arup Nanda [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Refresh option for Materialized view , want to use it during refresh - for Date: Fri, 24 Oct 2003 18:04:33 -0800 David, Answers to your questions: (1) Without knowing your exact needs, I wil offer a few different scenarios. I am assuming that you are doing a complete refresh every time. The following pertain to that. Say, your name of the MV is MV1. Here are the steps the first time. 1. Create table MV1 2. Create MV MV1 on that table. When you want to refresh complete: 1. Create table MV1_TEMP. Choose a suitable method: CTAS across dblink (with NOLOGGING), SQL*Loader, Direct Load Insert, or simpley exp/imp. 2. Drop MV MV1. This drops the MV but doesn't drop the table. 3. Drop table MV1. 4. Rename table MV1_TEMP to MV1. 5. Recreate MV MV1. 6. Allow users to proceed as usual. Note the time consumed between Steps 2 and 6 are in the order of a few seconds. And it's the only time the users will not have access to the MV, as opposed to a full refresh using dbms_mview.refresh approach., which will lock the MV for the entire duration and generate tons of redo and rollback. Even if you do a incremental refresh, this is still a better approach. In that case, you don't drop the table during the refresh. (2) Yes, the option is available in 8i, at least in 8.1.7. Make sure the syntax is correct. create materialized view MV1 on prebuilt table refresh fast as select ... from In the article I mentioned, you can find the complete syntax. www.proligence.com/downloads.html is the site. It also dscribes a step by step solution to the issue and compares the common solution with this new one. Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW
Materialized view and index
Hi, All Wondering whether anyone created indexes on materialized view to further improve the performance? What's the pros and cons of this method? Thanks in advance. Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Materialized view and index
Chuan, You can crteate indexes on MVs if you want to - there is nothing against it; in fact it may be specifically desirable to do so. MVs are designed to help in query optimization by selecting against a materialized collection f data as opposed to selecting from a set of tables as in case of normal views and hence you can create as many indexes as you like to improve access plan. The only downside I can think of, and it is pretty minimal, is increased time spent on index maintenance - be it in a fast refresh or complete one. But the cost is marginal compared to the benefit. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 27, 2003 10:29 PM Hi, All Wondering whether anyone created indexes on materialized view to further improve the performance? What's the pros and cons of this method? Thanks in advance. Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Materialized view and index
Creating Indexes on MV is pretty ok and it is good for performance. I even create mv on mv to further improve the performance of some really frequently executed SQL.:) Regards. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 11:29 AM Hi, All Wondering whether anyone created indexes on materialized view to further improve the performance? What's the pros and cons of this method? Thanks in advance. Chuan Important: This transmission is intended only for the use of the addressee and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error please notify the author immediately by telephone and delete all copies of this transmission together with any attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Refresh option for Materialized view , want to use it during
Whether or not you can do it across a dblink depends on the following, in no particular order: * the number of transactions occurring during the refresh period, and the amount of data resulting from them. * the usable bandwidth of the network connection * whether or not it is acceptable to have triggers on the production tables of the source system * the period, or frequency of the refresh, which, along with the network bandwidth and amount of data generated during the refresh period will determine if a fast refresh will work in your environment. That is, apart from the other technical requirements imposed on a fast refresh as outlined in TFM. * probably some other stuff I'm not thinking of at the moment, but I believe these are the primary technical considerations. The refresh period can be determined by simply polling your users on what an acceptable lag is. We have a 'reporting' database here that is based on materialized views from 2 different production systems. There are currently 53 MV's, all of which are fast refresh. 40 or so are refreshed every 2 minutes, and the rest are refreshed every 5 minutes. It all works very well, and keeps the Crystal Reports users out of the production systems. :) HTH Jared On Fri, 2003-10-24 at 20:49, Ryan wrote: no we dont need query rewrite. we load data every night across a database link. we drop and recreate all the tables from scratch. I thought about using materialized views. i thought they might load faster because of fast refresh. not sure if we can do that across a db link. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 10:14 PM refresh - for No, you didn't read it completely. Create table and create MV do the same thing - produce a copy of the data on a different location (or a different segment) that can be queried independently. However, I proposed a different way of doing the MV creating and refreshing, not using the dbms_mview.refresh procedure as documented, but by using prebuilt table and using other faster methods such as CTAS and Direct Path load to do a complete refresh. It offers severa advantages such as faster execution, much less outage window and low resource utilization. As an added bonus, you don't have to drop and recreate the read only MV when you add/alter a column to the master table. In your case, you might want to consider converting the tables to MV if MVs are used in such a way. One example is if you see some benefit from Query Rewrite, you may want to create the MVs on the tables using the ON PREBUILT TABLE clause for Oracle to use QR. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:24 PM refresh - for did i read that correctly that create table as is superior to a materialized view for nightly loads? We drop all the tables in some of our schemas and rebuild them with create table as statements. I was going to try out materialized views to see if they were faster. guess they are not? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space
RE: Refresh option for Materialized view , want to use it during refresh - for
Hi Arup,
This is a very good method. I would like to use it to modify some of my
data loading procedures. Here are my questions:
1. Do I need to create the table on the step 1 every time when I refresh the
data If I refresh data once per day?
2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the
method on Oracle 8i, I got missing keyword error on PREBUILT.
Dave
Siddharth,
I will offer a slightly out-of-the-box solution. Please read it through
till the end to determine its applicability in your case.
It seems yours refresh interval is once a day and you don't mind stale
data for a max of 24 hours. You also refresh is complete, not
incremental. So, I would suggest the follwoing approach.
(1) Create a table first
CREATE TABLE CT_PRODUCTID_VW
TABLESPACE
NOLOGGING
AS
SELECT .
(2) When you are ready to refresh, drop the MV
DROP MATERIALIZED VIEW CT_PRODUCTID_VW;
(3) Create the MV with the PREBUILT TABLE option.
CREATE
MATERIALIZED VIEW CT_PRODUCTID_VW
BUILD IMMEDIATE
REFRESH START WITH SYSDATE
NEXT (SYSDATE + 1)
ON PREBUILT TABLE
AS
SELECT
msi.segment1productid,
...
Your MV is not accessible between STEP 2 and STEP3, which is really a
dictionary update and takes about a second or so. So the outage is
really 1 second, not 1/2 hr.
A few explanations are in order here.
(1) Creating an MV on a Prebuilt Table does not consume more space. The
segment that used to be a table simply becomes an MV.
(2) When you drop the MV, the MV is gone, but the table remains instact.
(3) The table can be create by any means - export/import, SQL*Loader,
INSERT APPEND, etc.
(4) IT places less strain on the system comapred to the MV refresh
option, simply because the MV refresh truncates the segment and then
builds it.
I presented a paper to the same effect at IOUG Live 2003. You can
download a modified version of the same from my website
www.proligence.com/downlaods.html, titled Painless Master Table Alter
from the Presentations Section.
HTH.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, October 21, 2003 3:59 AM
refresh
Hi Gurus,
I have a materialized view, which is based on Oracle Apps tables and on
remote database. The view refresh takes around ½ hour, during this time
period I cannot see any records in the materialized view and therefore
my application faces errors.
The following is the view definition
CREATE
MATERIALIZED VIEW CT_PRODUCTID_VW
BUILD IMMEDIATE
REFRESH START WITH SYSDATE
NEXT (SYSDATE + 1)
AS
SELECT
msi.segment1productid,
msi.description description,
msi.inventory_item_id inventory_item_id,
mc.segment1 product_family,
mc.segment2 product_type
FROM [EMAIL PROTECTED] mcs,
[EMAIL PROTECTED] mc,
[EMAIL PROTECTED]mic,
[EMAIL PROTECTED] msi
where 1=1
and mc.structure_id = 50112
and mc.segment3 != 'SPARE'
and mc.global_name= 'US'
and mc.enabled_flag = 'Y'
and mcs.global_name = mc.global_name
and mcs.category_set_name = 'PROD GROUP'
and mic.category_set_id = mcs.category_set_id
and mic.category_id = mc.category_id
and mic.global_name = mc.global_name
and mic.organization_id = 1
and mic.inventory_item_id = msi.inventory_item_id
and msi.organization_id = mic.organization_id
and msi.global_name = mc.global_name
AND msi.auto_created_config_flag = 'N'
AND msi.item_type IN ('ATO MODEL','CONFIG SPARE','CONFIG
SUB','FEATURE PACK','PRODUCT LIST$0','PTO MODEL','SPARE')
and msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
Please note that the tables referenced are remote tables and Oracle Apps
tables and not logging on it is possible.
Please suggest an appropriate refresh mechanism to see the records even
during refresh period.
Thanks in advance.
With Warm Regards
Siddharth Haldankar
Zensar Technologies Ltd.
Cisco Systems Inc.
(Offshore Development Center)
# : 091 020 4128394
[EMAIL PROTECTED]
[EMAIL PROTECTED]
_
Cheer a special someone with a fun Halloween eCard from American Greetings!
Go to http://www.msn.americangreetings.com/index_msn.pd?source=msne134
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Boyd
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Refresh option for Materialized view , want to use it during refresh - for
did i read that correctly that create table as is superior to a materialized
view for nightly loads? We drop all the tables in some of our schemas and
rebuild them with create table as statements. I was going to try out
materialized views to see if they were faster.
guess they are not?
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, October 24, 2003 12:34 PM
refresh - for
Hi Arup,
This is a very good method. I would like to use it to modify some of my
data loading procedures. Here are my questions:
1. Do I need to create the table on the step 1 every time when I refresh
the
data If I refresh data once per day?
2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the
method on Oracle 8i, I got missing keyword error on PREBUILT.
Dave
Siddharth,
I will offer a slightly out-of-the-box solution. Please read it through
till the end to determine its applicability in your case.
It seems yours refresh interval is once a day and you don't mind stale
data for a max of 24 hours. You also refresh is complete, not
incremental. So, I would suggest the follwoing approach.
(1) Create a table first
CREATE TABLE CT_PRODUCTID_VW
TABLESPACE
NOLOGGING
AS
SELECT .
(2) When you are ready to refresh, drop the MV
DROP MATERIALIZED VIEW CT_PRODUCTID_VW;
(3) Create the MV with the PREBUILT TABLE option.
CREATE
MATERIALIZED VIEW CT_PRODUCTID_VW
BUILD IMMEDIATE
REFRESH START WITH SYSDATE
NEXT (SYSDATE + 1)
ON PREBUILT TABLE
AS
SELECT
msi.segment1productid,
...
Your MV is not accessible between STEP 2 and STEP3, which is really a
dictionary update and takes about a second or so. So the outage is
really 1 second, not 1/2 hr.
A few explanations are in order here.
(1) Creating an MV on a Prebuilt Table does not consume more space. The
segment that used to be a table simply becomes an MV.
(2) When you drop the MV, the MV is gone, but the table remains instact.
(3) The table can be create by any means - export/import, SQL*Loader,
INSERT APPEND, etc.
(4) IT places less strain on the system comapred to the MV refresh
option, simply because the MV refresh truncates the segment and then
builds it.
I presented a paper to the same effect at IOUG Live 2003. You can
download a modified version of the same from my website
www.proligence.com/downlaods.html, titled Painless Master Table Alter
from the Presentations Section.
HTH.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, October 21, 2003 3:59 AM
refresh
Hi Gurus,
I have a materialized view, which is based on Oracle Apps tables and on
remote database. The view refresh takes around ½ hour, during this time
period I cannot see any records in the materialized view and therefore
my application faces errors.
The following is the view definition
CREATE
MATERIALIZED VIEW CT_PRODUCTID_VW
BUILD IMMEDIATE
REFRESH START WITH SYSDATE
NEXT (SYSDATE + 1)
AS
SELECT
msi.segment1productid,
msi.description description,
msi.inventory_item_id inventory_item_id,
mc.segment1 product_family,
mc.segment2 product_type
FROM [EMAIL PROTECTED] mcs,
[EMAIL PROTECTED] mc,
[EMAIL PROTECTED]mic,
[EMAIL PROTECTED] msi
where 1=1
and mc.structure_id = 50112
and mc.segment3 != 'SPARE'
and mc.global_name= 'US'
and mc.enabled_flag = 'Y'
and mcs.global_name = mc.global_name
and mcs.category_set_name = 'PROD GROUP'
and mic.category_set_id = mcs.category_set_id
and mic.category_id = mc.category_id
and mic.global_name = mc.global_name
and mic.organization_id = 1
and mic.inventory_item_id = msi.inventory_item_id
and msi.organization_id = mic.organization_id
and msi.global_name = mc.global_name
AND msi.auto_created_config_flag = 'N'
AND msi.item_type IN ('ATO MODEL','CONFIG SPARE','CONFIG
SUB','FEATURE PACK','PRODUCT LIST$0','PTO MODEL','SPARE')
and msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
Please note that the tables referenced are remote tables and Oracle Apps
tables and not logging on it is possible.
Please suggest an appropriate refresh mechanism to see the records even
during refresh period.
Thanks in advance.
With Warm Regards
Siddharth Haldankar
Zensar Technologies Ltd.
Cisco Systems Inc.
(Offshore Development Center)
# : 091 020 4128394
[EMAIL PROTECTED]
[EMAIL PROTECTED]
_
Cheer a special someone with a fun Halloween eCard from American
Greetings!
Go to http://www.msn.americangreetings.com/index_msn.pd?source=msne134
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
Re: Refresh option for Materialized view , want to use it during refresh - for
David, Answers to your questions: (1) Without knowing your exact needs, I wil offer a few different scenarios. I am assuming that you are doing a complete refresh every time. The following pertain to that. Say, your name of the MV is MV1. Here are the steps the first time. 1. Create table MV1 2. Create MV MV1 on that table. When you want to refresh complete: 1. Create table MV1_TEMP. Choose a suitable method: CTAS across dblink (with NOLOGGING), SQL*Loader, Direct Load Insert, or simpley exp/imp. 2. Drop MV MV1. This drops the MV but doesn't drop the table. 3. Drop table MV1. 4. Rename table MV1_TEMP to MV1. 5. Recreate MV MV1. 6. Allow users to proceed as usual. Note the time consumed between Steps 2 and 6 are in the order of a few seconds. And it's the only time the users will not have access to the MV, as opposed to a full refresh using dbms_mview.refresh approach., which will lock the MV for the entire duration and generate tons of redo and rollback. Even if you do a incremental refresh, this is still a better approach. In that case, you don't drop the table during the refresh. (2) Yes, the option is available in 8i, at least in 8.1.7. Make sure the syntax is correct. create materialized view MV1 on prebuilt table refresh fast as select ... from In the article I mentioned, you can find the complete syntax. www.proligence.com/downloads.html is the site. It also dscribes a step by step solution to the issue and compares the common solution with this new one. Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space. The segment that used to be a table simply becomes an MV. (2) When you drop the MV, the MV is gone, but the table remains instact. (3) The table can be create by any means - export/import, SQL*Loader, INSERT APPEND, etc. (4) IT places less strain on the system comapred to the MV refresh option, simply because the MV refresh truncates the segment and then builds it. I presented a paper to the same effect at IOUG Live 2003. You can download a modified version of the same from my website www.proligence.com/downlaods.html, titled Painless Master Table Alter from the Presentations Section. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, October 21, 2003 3:59 AM refresh Hi Gurus, I have a materialized view, which is based on Oracle Apps tables and on remote database. The view refresh takes around ½ hour, during this time period I cannot see any records in the materialized view and therefore my application faces errors. The following is the view definition CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) AS SELECT msi.segment1productid, msi.description description, msi.inventory_item_id inventory_item_id, mc.segment1 product_family, mc.segment2 product_type FROM [EMAIL PROTECTED] mcs, [EMAIL PROTECTED] mc, [EMAIL PROTECTED]mic, [EMAIL PROTECTED] msi where 1=1 and mc.structure_id = 50112 and mc.segment3 != 'SPARE' and mc.global_name= 'US' and mc.enabled_flag = 'Y' and mcs.global_name = mc.global_name and mcs.category_set_name = 'PROD GROUP' and mic.category_set_id = mcs.category_set_id
Re: Refresh option for Materialized view , want to use it during refresh - for
No, you didn't read it completely. Create table and create MV do the same thing - produce a copy of the data on a different location (or a different segment) that can be queried independently. However, I proposed a different way of doing the MV creating and refreshing, not using the dbms_mview.refresh procedure as documented, but by using prebuilt table and using other faster methods such as CTAS and Direct Path load to do a complete refresh. It offers severa advantages such as faster execution, much less outage window and low resource utilization. As an added bonus, you don't have to drop and recreate the read only MV when you add/alter a column to the master table. In your case, you might want to consider converting the tables to MV if MVs are used in such a way. One example is if you see some benefit from Query Rewrite, you may want to create the MVs on the tables using the ON PREBUILT TABLE clause for Oracle to use QR. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:24 PM refresh - for did i read that correctly that create table as is superior to a materialized view for nightly loads? We drop all the tables in some of our schemas and rebuild them with create table as statements. I was going to try out materialized views to see if they were faster. guess they are not? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space. The segment that used to be a table simply becomes an MV. (2) When you drop the MV, the MV is gone, but the table remains instact. (3) The table can be create by any means - export/import, SQL*Loader, INSERT APPEND, etc. (4) IT places less strain on the system comapred to the MV refresh option, simply because the MV refresh truncates the segment and then builds it. I presented a paper to the same effect at IOUG Live 2003. You can download a modified version of the same from my website www.proligence.com/downlaods.html, titled Painless Master Table Alter from the Presentations Section. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, October 21, 2003 3:59 AM refresh Hi Gurus, I have a materialized view, which is based on Oracle Apps tables and on remote database. The view refresh takes around ½ hour, during this time period I cannot see any records in the materialized view and therefore my application faces errors. The following is the view definition CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) AS SELECT msi.segment1productid, msi.description description, msi.inventory_item_id inventory_item_id, mc.segment1 product_family, mc.segment2 product_type FROM [EMAIL PROTECTED] mcs, [EMAIL PROTECTED] mc, [EMAIL PROTECTED]mic, [EMAIL PROTECTED] msi where 1=1 and mc.structure_id = 50112 and mc.segment3 != 'SPARE' and mc.global_name= 'US' and mc.enabled_flag = 'Y' and mcs.global_name = mc.global_name and mcs.category_set_name = 'PROD GROUP' and mic.category_set_id = mcs.category_set_id
Re: Refresh option for Materialized view , want to use it during refresh - for
no we dont need query rewrite. we load data every night across a database link. we drop and recreate all the tables from scratch. I thought about using materialized views. i thought they might load faster because of fast refresh. not sure if we can do that across a db link. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 10:14 PM refresh - for No, you didn't read it completely. Create table and create MV do the same thing - produce a copy of the data on a different location (or a different segment) that can be queried independently. However, I proposed a different way of doing the MV creating and refreshing, not using the dbms_mview.refresh procedure as documented, but by using prebuilt table and using other faster methods such as CTAS and Direct Path load to do a complete refresh. It offers severa advantages such as faster execution, much less outage window and low resource utilization. As an added bonus, you don't have to drop and recreate the read only MV when you add/alter a column to the master table. In your case, you might want to consider converting the tables to MV if MVs are used in such a way. One example is if you see some benefit from Query Rewrite, you may want to create the MVs on the tables using the ON PREBUILT TABLE clause for Oracle to use QR. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:24 PM refresh - for did i read that correctly that create table as is superior to a materialized view for nightly loads? We drop all the tables in some of our schemas and rebuild them with create table as statements. I was going to try out materialized views to see if they were faster. guess they are not? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space. The segment that used to be a table simply becomes an MV. (2) When you drop the MV, the MV is gone, but the table remains instact. (3) The table can be create by any means - export/import, SQL*Loader, INSERT APPEND, etc. (4) IT places less strain on the system comapred to the MV refresh option, simply because the MV refresh truncates the segment and then builds it. I presented a paper to the same effect at IOUG Live 2003. You can download a modified version of the same from my website www.proligence.com/downlaods.html, titled Painless Master Table Alter from the Presentations Section. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, October 21, 2003 3:59 AM refresh Hi Gurus, I have a materialized view, which is based on Oracle Apps tables and on remote database. The view refresh takes around ½ hour, during this time period I cannot see any records in the materialized view and therefore my application faces errors. The following is the view definition CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) AS SELECT msi.segment1productid, msi.description description, msi.inventory_item_id inventory_item_id
Re: Refresh option for Materialized view , want to use it during refresh - for
i thought they might load faster because of fast refresh. If you can do fast refresh, then MVs may be better. However, here are a few considerations before you do so: 1. Fast refresh requires creation of mv logs on the master tables, which are populated by triggers. This will affect performance on your source db, which might not be acceptable. 2. Fast refresh is transactional, and it may be slower. If the number of changes are large between two refreshes, it might be actually faster to do a full refresh. not sure if we can do that across a db link. Of course you can across db links. Fast refreshable MVs are also created in replication environments; how do you suppose they work between two different databases? HTH. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 11:49 PM refresh - for no we dont need query rewrite. we load data every night across a database link. we drop and recreate all the tables from scratch. I thought about using materialized views. i thought they might load faster because of fast refresh. not sure if we can do that across a db link. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 10:14 PM refresh - for No, you didn't read it completely. Create table and create MV do the same thing - produce a copy of the data on a different location (or a different segment) that can be queried independently. However, I proposed a different way of doing the MV creating and refreshing, not using the dbms_mview.refresh procedure as documented, but by using prebuilt table and using other faster methods such as CTAS and Direct Path load to do a complete refresh. It offers severa advantages such as faster execution, much less outage window and low resource utilization. As an added bonus, you don't have to drop and recreate the read only MV when you add/alter a column to the master table. In your case, you might want to consider converting the tables to MV if MVs are used in such a way. One example is if you see some benefit from Query Rewrite, you may want to create the MVs on the tables using the ON PREBUILT TABLE clause for Oracle to use QR. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:24 PM refresh - for did i read that correctly that create table as is superior to a materialized view for nightly loads? We drop all the tables in some of our schemas and rebuild them with create table as statements. I was going to try out materialized views to see if they were faster. guess they are not? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 12:34 PM refresh - for Hi Arup, This is a very good method. I would like to use it to modify some of my data loading procedures. Here are my questions: 1. Do I need to create the table on the step 1 every time when I refresh the data If I refresh data once per day? 2. Is ON PREBUILT TABLE available on Oracle 8i? When I was trying the method on Oracle 8i, I got missing keyword error on PREBUILT. Dave Siddharth, I will offer a slightly out-of-the-box solution. Please read it through till the end to determine its applicability in your case. It seems yours refresh interval is once a day and you don't mind stale data for a max of 24 hours. You also refresh is complete, not incremental. So, I would suggest the follwoing approach. (1) Create a table first CREATE TABLE CT_PRODUCTID_VW TABLESPACE NOLOGGING AS SELECT . (2) When you are ready to refresh, drop the MV DROP MATERIALIZED VIEW CT_PRODUCTID_VW; (3) Create the MV with the PREBUILT TABLE option. CREATE MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATE REFRESH START WITH SYSDATE NEXT (SYSDATE + 1) ON PREBUILT TABLE AS SELECT msi.segment1productid, ... Your MV is not accessible between STEP 2 and STEP3, which is really a dictionary update and takes about a second or so. So the outage is really 1 second, not 1/2 hr. A few explanations are in order here. (1) Creating an MV on a Prebuilt Table does not consume more space. The segment that used to be a table simply becomes an MV. (2) When you drop the MV, the MV is gone, but the table remains instact. (3) The table can be create by any means - export/import, SQL*Loader, INSERT APPEND, etc. (4) IT places less strain on the system comapred to the MV refresh option, simply because the MV refresh truncates the segment and then builds it. I presented a paper
RE: Refresh option for Materialized view , want to use it during refresh - for Arup
Hi Arup,
Really appreciate for taking out time for
answering to my query.
Thanks a ton for your solution. It fits
perfectly for my problem.
Thanks once again
With Warm Regards
Siddharth
Haldankar
Zensar Technologies Ltd.
Cisco Systems Inc.
(Offshore Development
Center)
# : 091 020 4128394
[EMAIL PROTECTED]
[EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Arup
Nanda
Sent: Tuesday,
October 21, 2003 9:34 PM
To: Multiple recipients of list
ORACLE-L
Subject: Re: Refresh option for
Materialized view , want to use it during refresh
Siddharth,
I will offer a slightly out-of-the-box solution. Please read it through till
the end to determine its applicability in your case.
It seems yours refresh interval is once a day and you don't mind stale data for
a max of 24 hours. You also refresh is complete, not incremental. So, I would
suggest the follwoing approach.
(1) Create a table first
CREATE TABLE CT_PRODUCTID_VW
TABLESPACE
NOLOGGING
AS
SELECT .
(2) When you are ready to refresh, drop the MV
DROP MATERIALIZED VIEW CT_PRODUCTID_VW;
(3) Create the MV with the PREBUILT TABLE option.
CREATE
MATERIALIZED VIEW CT_PRODUCTID_VW
BUILD IMMEDIATE
REFRESH START WITH SYSDATE
NEXT (SYSDATE + 1)
ON PREBUILT TABLE
AS
SELECT
msi.segment1
productid,
...
Your MV is not accessible between STEP 2 and STEP3, which is really a
dictionary update and takes about a second or so. So the outage is
really 1 second, not 1/2 hr.
A few explanations are in order here.
(1) Creating an MV on a Prebuilt Table does not consume more space. The segment
that used to be a table simply becomes an MV.
(2) When you drop the MV, the MV is gone, but the table remains instact.
(3) The table can be create by any means - export/import, SQL*Loader, INSERT
APPEND, etc.
(4) IT places less strain on the system comapred to the MV refresh option,
simply because the MV refresh truncates the segment and then builds it.
I presented a paper to the same effect at IOUG Live 2003. You can download a
modified versionof the same from my website www.proligence.com/downlaods.html,
titled Painless Master Table Alter from the Presentations Section.
HTH.
Arup Nanda
- Original Message -
style='font-size:10.0pt;
font-family:Arial'>Siddharth Haldankar
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, October 21, 2003 3:59 AM
refresh
Hi Gurus,
I have a materialized view, which is based on Oracle Apps tables and on remote
database. The view refresh takes around ½ hour, during this time period I
cannot see any records in the materialized view and therefore my application
faces errors.
The following is the view definition
CREATE
MATERIALIZED VIEW CT_PRODUCTID_VW
BUILD IMMEDIATE
REFRESH START WITH SYSDATE
NEXT (SYSDATE + 1)
AS
SELECT
msi.segment1
productid,
msi.description
description,
msi.inventory_item_id
inventory_item_id,
mc.segment1
product_family,
mc.segment2
product_type
FROM [EMAIL PROTECTED] mcs,
[EMAIL PROTECTED] mc,
[EMAIL PROTECTED] mic,
[EMAIL PROTECTED] msi
where 1=1
and mc.structure_id =
50112
and mc.segment3
!= 'SPARE'
and mc.global_name = 'US'
and mc.enabled_flag = 'Y'
and mcs.global_name =
mc.global_name
and mcs.category_set_name = 'PROD GROUP'
and mic.category_set_id = mcs.category_set_id
and mic.category_id = mc.category_id
and mic.global_name =
mc.global_name
and mic.organization_id = 1
and mic.inventory_item_id = msi.inventory_item_id
and msi.organization_id = mic.organization_id
and msi.global_name =
mc.global_name
AND msi.auto_created_config_flag = 'N'
AND msi.item_type
IN ('ATO MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT
LIST$0','PTO MODEL','SPARE')
and msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
Please note that the tables referenced are remote tables and Oracle Apps tables
and not logging on it is possible.
Please suggest an appropriate refresh mechanism to see the records even during
refresh period.
Thanks in advance.
With Warm Regards
Siddharth Haldankar
Zensar Technologies Ltd.
Cisco Systems Inc.
(Offshore Development Center)
# : 091 020 4128394
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Refresh option for Materialized view , want to use it during refresh
Hi
Gurus,
I
have a materialized view, which is based on Oracle Apps tables and on remote
database. The view refresh takes around ½ hour, during this time period I
cannot see any records in the materialized view and therefore my application
faces errors.
The
following is the view definition
CREATE
MATERIALIZED VIEW CT_PRODUCTID_VW
BUILD IMMEDIATE
REFRESH START WITH SYSDATE
NEXT (SYSDATE + 1)
AS
SELECT
msi.segment1
productid,
msi.description
description,
msi.inventory_item_id inventory_item_id,
mc.segment1
product_family,
mc.segment2
product_type
FROM
[EMAIL PROTECTED] mcs,
[EMAIL PROTECTED] mc,
[EMAIL PROTECTED] mic,
[EMAIL PROTECTED]
msi
where 1=1
and
mc.structure_id = 50112
and
mc.segment3 != 'SPARE'
and
mc.global_name = 'US'
and
mc.enabled_flag = 'Y'
and
mcs.global_name = mc.global_name
and mcs.category_set_name = 'PROD GROUP'
and mic.category_set_id =
mcs.category_set_id
and
mic.category_id = mc.category_id
and
mic.global_name = mc.global_name
and mic.organization_id = 1
and mic.inventory_item_id =
msi.inventory_item_id
and msi.organization_id =
mic.organization_id
and
msi.global_name = mc.global_name
AND msi.auto_created_config_flag = 'N'
AND
msi.item_type IN ('ATO
MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT LIST$0','PTO
MODEL','SPARE')
and msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
Please
note that the tables referenced are remote tables and Oracle Apps tables and
not logging on it is possible.
Please
suggest an appropriate refresh mechanism to see the records even during refresh
period.
Thanks
in advance.
With Warm Regards
Siddharth Haldankar
Zensar Technologies Ltd.
Cisco Systems Inc.
(Offshore Development Center)
# : 091 020 4128394
[EMAIL PROTECTED]
[EMAIL PROTECTED]
RE: Refresh option for Materialized view , want to use it during
One possibility
would be to replicate each of the four source tables, then perform the join on
the replicated tables. This will require you to create snapshot logs on
the source tables. Obviously, this will slow the performance of the
whatever report is running against the replicated data since the join must be
done for each query instead of when the data was originally replicated.
But the data will remain continuously available.
-Original Message-From: Siddharth Haldankar
[mailto:[EMAIL PROTECTED]Sent: Tuesday, October 21, 2003 2:59
AMTo: Multiple recipients of list ORACLE-LSubject:
Refresh option for Materialized view , want to use it during
refresh
Hi
Gurus,
I
have a materialized view, which is based on Oracle Apps tables and on remote
database. The view refresh takes around ½ hour, during this time period I
cannot see any records in the materialized view and therefore my application
faces errors.
The
following is the view definition
CREATE
MATERIALIZED VIEW
CT_PRODUCTID_VW
BUILD
IMMEDIATE
REFRESH START WITH
SYSDATE
NEXT (SYSDATE +
1)
AS
SELECT
msi.segment1
productid,
msi.description
description,
msi.inventory_item_id
inventory_item_id,
mc.segment1
product_family,
mc.segment2
product_type
FROM
[EMAIL PROTECTED]
mcs,
[EMAIL PROTECTED]
mc,
[EMAIL PROTECTED] mic,
[EMAIL PROTECTED]
msi
where
1=1
and
mc.structure_id =
50112
and
mc.segment3 !=
'SPARE'
and
mc.global_name =
'US'
and
mc.enabled_flag = 'Y'
and
mcs.global_name =
mc.global_name
and
mcs.category_set_name = 'PROD GROUP'
and
mic.category_set_id = mcs.category_set_id
and
mic.category_id = mc.category_id
and
mic.global_name =
mc.global_name
and
mic.organization_id = 1
and
mic.inventory_item_id = msi.inventory_item_id
and
msi.organization_id = mic.organization_id
and
msi.global_name =
mc.global_name
AND
msi.auto_created_config_flag = 'N'
AND
msi.item_type IN ('ATO
MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT LIST$0','PTO
MODEL','SPARE')
and
msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
Please note that the tables referenced are remote
tables and Oracle Apps tables and not logging on it is
possible.
Please suggest an appropriate refresh mechanism to see
the records even during refresh period.
Thanks in advance.
With Warm
Regards
Siddharth
Haldankar
Zensar Technologies
Ltd.
Cisco Systems Inc.
(Offshore
Development
Center)
# : 091 020
4128394
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Re: Refresh option for Materialized view , want to use it during refresh
Siddharth,I will offer a slightly out-of-the-box
solution. Please read it through till the end to determine its applicability in
your case.It seems yours refresh interval is once a day and you don't
mind stale data for a max of 24 hours. You also refresh is complete, not
incremental. So, I would suggest the follwoing approach.(1) Create a
table firstCREATE TABLE CT_PRODUCTID_VW
TABLESPACE NOLOGGINGASSELECT .(2) When
you are ready to "refresh", drop the MVDROP
MATERIALIZED VIEW CT_PRODUCTID_VW;(3) Create the MV with the
PREBUILT TABLE option.CREATE MATERIALIZED VIEW
CT_PRODUCTID_VW BUILD IMMEDIATEREFRESH START WITH SYSDATENEXT
(SYSDATE + 1)ON PREBUILT TABLEAS SELECT
msi.segment1
productid, Your MV is not
accessible between STEP 2 and STEP3, which is really a dictionary update and
takes about a second or so. So the "outage" is really 1 second, not 1/2
hr.A few explanations are in order here.(1) Creating an MV on a
Prebuilt Table does not consume more space. The segment that used to be a table
simply becomes an MV.(2) When you drop the MV, the MV is gone, but the table
remains instact.(3) The table can be create by any means - export/import,
SQL*Loader, INSERT APPEND, etc.(4) IT places less strain on the system
comapred to the MV refresh option, simply because the MV refresh truncates the
segment and then builds it.I presented a paper to the same effect at
IOUG Live 2003. You can download a modified versionof the same from my
website www.proligence.com/downlaods.html,
titled "Painless Master Table Alter" from the Presentations
Section.HTH.Arup Nanda- Original
Message - From: Siddharth Haldankar To: Multiple recipients of list
ORACLE-L Sent: Tuesday, October 21, 2003 3:59 AMSubject: Refresh option
for Materialized view , want to use it during refreshHi
Gurus,I have a materialized view, which is based on Oracle Apps tables
and on remote database. The view refresh takes around ½ hour, during this time
period I cannot see any records in the materialized view and therefore my
application faces errors.The following is the view definitionCREATE
MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATEREFRESH START WITH
SYSDATENEXT (SYSDATE + 1)AS SELECT
msi.segment1
productid,
msi.description
description,
msi.inventory_item_id
inventory_item_id,
mc.segment1
product_family,
mc.segment2
product_typeFROM [EMAIL PROTECTED]
mcs,
[EMAIL PROTECTED]
mc,
[EMAIL PROTECTED]
mic,
[EMAIL PROTECTED] msiwhere
1=1and mc.structure_id
= 50112and
mc.segment3 !=
'SPARE'and
mc.global_name =
'US'and mc.enabled_flag =
'Y'and mcs.global_name =
mc.global_nameand mcs.category_set_name = 'PROD
GROUP'and mic.category_set_id =
mcs.category_set_idand
mic.category_id = mc.category_id
and mic.global_name =
mc.global_nameand mic.organization_id =
1and mic.inventory_item_id =
msi.inventory_item_idand msi.organization_id =
mic.organization_idand
msi.global_name =
mc.global_nameAND msi.auto_created_config_flag =
'N'AND
msi.item_type IN ('ATO
MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT LIST$0','PTO
MODEL','SPARE')and msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')Please note that the
tables referenced are remote tables and Oracle Apps tables and not logging on it
is possible.Please suggest an appropriate refresh mechanism to see the
records even during refresh period.Thanks in advance.With Warm
RegardsSiddharth HaldankarZensar Technologies Ltd.Cisco
Systems Inc. (Offshore Development Center)# : 091 020
4128394[EMAIL PROTECTED][EMAIL PROTECTED]
RE: Refresh option for Materialized view , want to use it during
Here's a weird
idea to consider: You might replicate to a dummy table XYZ, then rename
CT_PRODUCTIED_VW to CRAP; then rename XYZ to CT_PRODUCTIED_VW. Then rename
CRAP to XYZ. If there are any dependent stored procedures, you will
probably be required to recompile them. But, if the dependencies aren't
toohairy,this could be put into a little script that executes in
about two seconds.
Then there's the
idea of fiddling with partitions and swapping them in and
out.
-Original Message-From: Siddharth Haldankar
[mailto:[EMAIL PROTECTED]Sent: Tuesday, October 21, 2003 2:59
AMTo: Multiple recipients of list ORACLE-LSubject:
Refresh option for Materialized view , want to use it during
refresh
Hi
Gurus,
I
have a materialized view, which is based on Oracle Apps tables and on remote
database. The view refresh takes around ½ hour, during this time period I
cannot see any records in the materialized view and therefore my application
faces errors.
The
following is the view definition
CREATE
MATERIALIZED VIEW
CT_PRODUCTID_VW
BUILD
IMMEDIATE
REFRESH START WITH
SYSDATE
NEXT (SYSDATE +
1)
AS
SELECT
msi.segment1
productid,
msi.description
description,
msi.inventory_item_id
inventory_item_id,
mc.segment1
product_family,
mc.segment2
product_type
FROM
[EMAIL PROTECTED]
mcs,
[EMAIL PROTECTED]
mc,
[EMAIL PROTECTED] mic,
[EMAIL PROTECTED]
msi
where
1=1
and
mc.structure_id =
50112
and
mc.segment3 !=
'SPARE'
and
mc.global_name =
'US'
and
mc.enabled_flag = 'Y'
and
mcs.global_name =
mc.global_name
and
mcs.category_set_name = 'PROD GROUP'
and
mic.category_set_id = mcs.category_set_id
and
mic.category_id = mc.category_id
and
mic.global_name =
mc.global_name
and
mic.organization_id = 1
and
mic.inventory_item_id = msi.inventory_item_id
and
msi.organization_id = mic.organization_id
and
msi.global_name =
mc.global_name
AND
msi.auto_created_config_flag = 'N'
AND
msi.item_type IN ('ATO
MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT LIST$0','PTO
MODEL','SPARE')
and
msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
Please note that the tables referenced are remote
tables and Oracle Apps tables and not logging on it is
possible.
Please suggest an appropriate refresh mechanism to see
the records even during refresh period.
Thanks in advance.
With Warm
Regards
Siddharth
Haldankar
Zensar Technologies
Ltd.
Cisco Systems Inc.
(Offshore
Development
Center)
# : 091 020
4128394
[EMAIL PROTECTED]
[EMAIL PROTECTED]
RE: Problem in dropping Materialized View
Hi,
Did you rename the materialized view? If so you are probably hitting bug
1335477. Can you see the view name in user_mviews? I think you have to try
and re-create the materialized view and then drop it.
Really suggest you contact Oracle, unless someone on the list has the
definitive fix.
Regards
Pete
[END]
-Original Message-
Sent: 17 October 2003 07:54
To: Multiple recipients of list ORACLE-L
Hi Gurus
Oracle 8.1.7 on HP Unix
I have some problems in Materialized view
Here are the sequence of steps
Created a normal view CT_PRODUCTID_VW
Created a materialized view CT_PRODUCID_MVW
Dropped view CT_PRODUCTID_VW
Rename CT_PRODUCTID_MVW to CT_PRODUCTID_VW
Now I cannot drop the materialized view CT_PRODUCTID_VW
SQL select owner,object_type from all_objects where
object_name='CT_PRODUCTID_VW';
OWNER OBJECT_TYPE
-- --
COMMADMTABLE
SQL ANALYZE TABLE CT_PRODUCTID_VW VALIDATE STRUCTURE CASCADE;
Table analyzed.
SQL DROP MATERIALIZED VIEW COMMADM.CT_PRODUCTID_VW;
DROP MATERIALIZED VIEW COMMADM.CT_PRODUCTID_VW
*
ERROR at line 1:
ORA-12003: snapshot COMMADM.CT_PRODUCTID_VW does not exist
SQL drop snapshot COMMADM.CT_PRODUCTID_VW;
drop snapshot COMMADM.CT_PRODUCTID_VW
*
ERROR at line 1:
ORA-12003: snapshot COMMADM.CT_PRODUCTID_VW does not exist
SQL DROP MATERIALIZED VIEW LOG ON CT_PRODUCTID_VW;
DROP MATERIALIZED VIEW LOG ON CT_PRODUCTID_VW
*
ERROR at line 1:
ORA-12002: there is no snapshot log on table COMMADM.CT_PRODUCTID_VW
SQL drop table ct_productid_vw;
drop table ct_productid_vw
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop
COMMADM.CT_PRODUCTID_VW
The definition of the materialized view is as follows:
CREATE
MATERIALIZED VIEW CT_PRODUCTID_VW
BUILD IMMEDIATE
REFRESH START WITH SYSDATE
NEXT (SYSDATE + 1)
AS
SELECT
msi.segment1productid,
msi.description description,
msi.inventory_item_id inventory_item_id,
mc.segment1 product_family,
mc.segment2 product_type
FROM [EMAIL PROTECTED] mcs,
[EMAIL PROTECTED] mc,
[EMAIL PROTECTED]mic,
[EMAIL PROTECTED] msi
where 1=1
and mc.structure_id = 50112
and mc.segment3 != 'SPARE'
and mc.global_name= 'US'
and mc.enabled_flag = 'Y'
and mcs.global_name = mc.global_name
and mcs.category_set_name = 'PROD GROUP'
and mic.category_set_id = mcs.category_set_id
and mic.category_id = mc.category_id
and mic.global_name = mc.global_name
and mic.organization_id = 1
and mic.inventory_item_id = msi.inventory_item_id
and msi.organization_id = mic.organization_id
and msi.global_name = mc.global_name
AND msi.auto_created_config_flag = 'N'
AND msi.item_type IN ('ATO MODEL','CONFIG SPARE','CONFIG
SUB','FEATURE PACK','PRODUCT LIST$0','PTO MODEL','SPARE')
and msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
Any help to drop this object is highly appreciated.
With Warm Regards
Siddharth Haldankar
Cisco Systems Inc. ODC
Zensar Technologies Ltd.
# : 4128374
[EMAIL PROTECTED]
[EMAIL PROTECTED]
__
The information contained in this email is confidential and
intended only for the use of the individual or entity named
above. If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly
prohibited. Thomson Scientific will accept no responsibility
or liability in respect to this email other than to the addressee.
If you have received this communication in error, please
notify us immediately via email: [EMAIL PROTECTED]
__
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hitchman, Peter
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Problem in dropping Materialized View
Hitchman
Thanks a ton, Yes it is a bug.
With Warm Regards
_
Siddharth Haldankar
Cisco Systems Inc. ODC
Zensar Technologies Ltd.
# : 4128374
[EMAIL PROTECTED]
[EMAIL PROTECTED]
-Original Message-
Hitchman, Peter
Sent: Friday, October 17, 2003 3:04 PM
To: Multiple recipients of list ORACLE-L
Hi,
Did you rename the materialized view? If so you are probably hitting bug
1335477. Can you see the view name in user_mviews? I think you have to
try
and re-create the materialized view and then drop it.
Really suggest you contact Oracle, unless someone on the list has the
definitive fix.
Regards
Pete
[END]
-Original Message-
Sent: 17 October 2003 07:54
To: Multiple recipients of list ORACLE-L
Hi Gurus
Oracle 8.1.7 on HP Unix
I have some problems in Materialized view
Here are the sequence of steps
Created a normal view CT_PRODUCTID_VW
Created a materialized view CT_PRODUCID_MVW
Dropped view CT_PRODUCTID_VW
Rename CT_PRODUCTID_MVW to CT_PRODUCTID_VW
Now I cannot drop the materialized view CT_PRODUCTID_VW
SQL select owner,object_type from all_objects where
object_name='CT_PRODUCTID_VW';
OWNER OBJECT_TYPE
-- --
COMMADMTABLE
SQL ANALYZE TABLE CT_PRODUCTID_VW VALIDATE STRUCTURE CASCADE;
Table analyzed.
SQL DROP MATERIALIZED VIEW COMMADM.CT_PRODUCTID_VW;
DROP MATERIALIZED VIEW COMMADM.CT_PRODUCTID_VW
*
ERROR at line 1:
ORA-12003: snapshot COMMADM.CT_PRODUCTID_VW does not exist
SQL drop snapshot COMMADM.CT_PRODUCTID_VW;
drop snapshot COMMADM.CT_PRODUCTID_VW
*
ERROR at line 1:
ORA-12003: snapshot COMMADM.CT_PRODUCTID_VW does not exist
SQL DROP MATERIALIZED VIEW LOG ON CT_PRODUCTID_VW;
DROP MATERIALIZED VIEW LOG ON CT_PRODUCTID_VW
*
ERROR at line 1:
ORA-12002: there is no snapshot log on table COMMADM.CT_PRODUCTID_VW
SQL drop table ct_productid_vw;
drop table ct_productid_vw
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop
COMMADM.CT_PRODUCTID_VW
The definition of the materialized view is as follows:
CREATE
MATERIALIZED VIEW CT_PRODUCTID_VW
BUILD IMMEDIATE
REFRESH START WITH SYSDATE
NEXT (SYSDATE + 1)
AS
SELECT
msi.segment1productid,
msi.description description,
msi.inventory_item_id inventory_item_id,
mc.segment1 product_family,
mc.segment2 product_type
FROM [EMAIL PROTECTED] mcs,
[EMAIL PROTECTED] mc,
[EMAIL PROTECTED]mic,
[EMAIL PROTECTED] msi
where 1=1
and mc.structure_id = 50112
and mc.segment3 != 'SPARE'
and mc.global_name= 'US'
and mc.enabled_flag = 'Y'
and mcs.global_name = mc.global_name
and mcs.category_set_name = 'PROD GROUP'
and mic.category_set_id = mcs.category_set_id
and mic.category_id = mc.category_id
and mic.global_name = mc.global_name
and mic.organization_id = 1
and mic.inventory_item_id = msi.inventory_item_id
and msi.organization_id = mic.organization_id
and msi.global_name = mc.global_name
AND msi.auto_created_config_flag = 'N'
AND msi.item_type IN ('ATO MODEL','CONFIG SPARE','CONFIG
SUB','FEATURE PACK','PRODUCT LIST$0','PTO MODEL','SPARE')
and msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
Any help to drop this object is highly appreciated.
With Warm Regards
Siddharth Haldankar
Cisco Systems Inc. ODC
Zensar Technologies Ltd.
# : 4128374
[EMAIL PROTECTED]
[EMAIL PROTECTED]
__
The information contained in this email is confidential and
intended only for the use of the individual or entity named
above. If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly
prohibited. Thomson Scientific will accept no responsibility
or liability in respect to this email other than to the addressee.
If you have received this communication in error, please
notify us immediately via email: [EMAIL PROTECTED]
__
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hitchman, Peter
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http
RE: Problem in dropping Materialized View
Title: Message
Re-create the materialized view and then try to drop
it.
Because you tried to rename it to the view name, I would say try it both
as the view name and as the original name.
I know
it sounds silly but we had this problem once and creating it , will create
whatever internal
structures were needed to do the delete the MV. Re-creating the MV, put
everything back to consistent state and we could drop
cleanly
Babette
-Original Message-From:
Siddharth Haldankar [mailto:[EMAIL PROTECTED] Sent: 2003-10-17 2:54
AMTo: Multiple recipients of list ORACLE-LSubject: Problem
in dropping Materialized View
Hi
Gurus
Oracle 8.1.7 on HP Unix
I
have some problems in Materialized view
Here
are the sequence of steps
Created a normal view CT_PRODUCTID_VW
Created a materialized view
CT_PRODUCID_MVW
Dropped view CT_PRODUCTID_VW
Rename CT_PRODUCTID_MVW to
CT_PRODUCTID_VW
Now I
cannot drop the materialized view CT_PRODUCTID_VW
SQL select owner,object_type from all_objects
where object_name='CT_PRODUCTID_VW';
OWNER
OBJECT_TYPE
--
--
COMMADM
TABLE
SQL ANALYZE TABLE CT_PRODUCTID_VW VALIDATE
STRUCTURE CASCADE;
Table
analyzed.
SQL DROP MATERIALIZED VIEW
COMMADM.CT_PRODUCTID_VW;
DROP MATERIALIZED VIEW
COMMADM.CT_PRODUCTID_VW
*
ERROR
at line 1:
ORA-12003: snapshot "COMMADM"."CT_PRODUCTID_VW" does
not exist
SQL drop snapshot
COMMADM.CT_PRODUCTID_VW;
drop
snapshot COMMADM.CT_PRODUCTID_VW
*
ERROR
at line 1:
ORA-12003: snapshot "COMMADM"."CT_PRODUCTID_VW" does
not exist
SQL DROP MATERIALIZED VIEW LOG ON
CT_PRODUCTID_VW;
DROP
MATERIALIZED VIEW LOG ON CT_PRODUCTID_VW
*
ERROR
at line 1:
ORA-12002: there is no snapshot log on table
"COMMADM"."CT_PRODUCTID_VW"
SQL drop table ct_productid_vw;
drop
table ct_productid_vw
*
ERROR
at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop
"COMMADM"."CT_PRODUCTID_VW"
The
definition of the materialized view is as follows:
CREATE
MATERIALIZED VIEW CT_PRODUCTID_VW
BUILD
IMMEDIATE
REFRESH START WITH SYSDATE
NEXT
(SYSDATE + 1)
AS
SELECT
msi.segment1
productid,
msi.description
description,
msi.inventory_item_id
inventory_item_id,
mc.segment1
product_family,
mc.segment2
product_type
FROM
[EMAIL PROTECTED]
mcs,
[EMAIL PROTECTED]
mc,
[EMAIL PROTECTED] mic,
[EMAIL PROTECTED]
msi
where
1=1
and
mc.structure_id =
50112
and
mc.segment3 !=
'SPARE'
and
mc.global_name =
'US'
and
mc.enabled_flag = 'Y'
and
mcs.global_name =
mc.global_name
and mcs.category_set_name = 'PROD
GROUP'
and mic.category_set_id =
mcs.category_set_id
and
mic.category_id = mc.category_id
and
mic.global_name =
mc.global_name
and mic.organization_id =
1
and mic.inventory_item_id =
msi.inventory_item_id
and msi.organization_id =
mic.organization_id
and
msi.global_name =
mc.global_name
AND msi.auto_created_config_flag =
'N'
AND
msi.item_type IN ('ATO
MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT LIST$0','PTO
MODEL','SPARE')
and msi.inventory_item_status_code IN
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
Any
help to drop this object is highly appreciated.
With Warm
Regards
Siddharth
Haldankar
Cisco Systems Inc.
ODC
Zensar Technologies
Ltd.
# :
4128374
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Problem in dropping Materialized View
Hi
Gurus
Oracle
8.1.7 on HP Unix
I
have some problems in Materialized view
Here
are the sequence of steps
Created
a normal view CT_PRODUCTID_VW
Created
a materialized view CT_PRODUCID_MVW
Dropped
view CT_PRODUCTID_VW
Rename
CT_PRODUCTID_MVW to CT_PRODUCTID_VW
Now I
cannot drop the materialized view CT_PRODUCTID_VW
SQL
select owner,object_type from all_objects where object_name='CT_PRODUCTID_VW';
OWNER
OBJECT_TYPE
--
--
COMMADM
TABLE
SQL
ANALYZE TABLE CT_PRODUCTID_VW VALIDATE STRUCTURE CASCADE;
Table
analyzed.
SQL
DROP MATERIALIZED VIEW COMMADM.CT_PRODUCTID_VW;
DROP
MATERIALIZED VIEW COMMADM.CT_PRODUCTID_VW
*
ERROR
at line 1:
ORA-12003:
snapshot COMMADM.CT_PRODUCTID_VW does not exist
SQL
drop snapshot COMMADM.CT_PRODUCTID_VW;
drop
snapshot COMMADM.CT_PRODUCTID_VW
*
ERROR
at line 1:
ORA-12003:
snapshot COMMADM.CT_PRODUCTID_VW does not exist
SQL
DROP MATERIALIZED VIEW LOG ON CT_PRODUCTID_VW;
DROP
MATERIALIZED VIEW LOG ON CT_PRODUCTID_VW
*
ERROR
at line 1:
ORA-12002:
there is no snapshot log on table
COMMADM.CT_PRODUCTID_VW
SQL
drop table ct_productid_vw;
drop
table ct_productid_vw
*
ERROR
at line 1:
ORA-12083:
must use DROP MATERIALIZED VIEW to drop
COMMADM.CT_PRODUCTID_VW
The
definition of the materialized view is as follows:
CREATE
MATERIALIZED
VIEW CT_PRODUCTID_VW
BUILD
IMMEDIATE
REFRESH
START WITH SYSDATE
NEXT
(SYSDATE + 1)
AS
SELECT
msi.segment1
productid,
msi.description
description,
msi.inventory_item_id inventory_item_id,
mc.segment1
product_family,
mc.segment2
product_type
FROM
[EMAIL PROTECTED] mcs,
[EMAIL PROTECTED] mc,
[EMAIL PROTECTED] mic,
[EMAIL PROTECTED]
msi
where
1=1
and
mc.structure_id = 50112
and
mc.segment3 != 'SPARE'
and
mc.global_name = 'US'
and
mc.enabled_flag = 'Y'
and
mcs.global_name = mc.global_name
and
mcs.category_set_name = 'PROD GROUP'
and
mic.category_set_id = mcs.category_set_id
and
mic.category_id = mc.category_id
and
mic.global_name = mc.global_name
and
mic.organization_id = 1
and
mic.inventory_item_id = msi.inventory_item_id
and
msi.organization_id = mic.organization_id
and
msi.global_name = mc.global_name
AND
msi.auto_created_config_flag = 'N'
AND
msi.item_type IN ('ATO
MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT LIST$0','PTO
MODEL','SPARE')
and
msi.inventory_item_status_code IN ('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
Any
help to drop this object is highly appreciated.
With Warm Regards
Siddharth Haldankar
Cisco Systems Inc. ODC
Zensar Technologies
Ltd.
# : 4128374
[EMAIL PROTECTED]
[EMAIL PROTECTED]
RE: what is a materialized view ?
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76994/mv.h tm#721 Ofer -Original Message- Sent: Wednesday, September 10, 2003 1:04 PM To: Multiple recipients of list ORACLE-L Dear Freinds, what is a materilized view ? what is the use of it and how to create it. Any docs or notes or white papers will be helpful. TIA, Rajuveera ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Veeraraju_Mareddi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** The information contained in this e-mail message may be privileged and confidential. The information is intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone, or by e-mail and delete the message from your computer. Thank you! Unless otherwise stated, any views or opinions expressed in this e-mail are solely those of the author and do not represent those of Barak I.T.C (1995)The International Telecommunications Services Corp. Ltd. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ofer Harel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: what is a materialized view ?
Consider this : Research is the best way to discover and to remember information. The acquisition of information increases a DBA's skill. Skilled DBAs should (?!!) always be in demand. Therefore, by encouraging people to read manuals you're helping to make me unemployed. Just something to consider =) Mike Hately PS Personally I think that the discontinuation of paper manuals was a disaster. I used to take concepts manuals when I went in the bath. A great place to learn. No kids, no TV. -Original Message- Sent: 10 September 2003 13:54 To: Multiple recipients of list ORACLE-L I have a problem with unwillingness to invest effort and do some very basic research from the readily available literature. Oracle has a manual called Database Concepts which is accessible on approximately 87 billion places on the internet, for free, of course. Previous incarnations of this list (and I am a long time member) were, in my opinion, rendered unusable by a flood of trivial questions. I do honestly believe that, in order to fully participate in this list, a person must satisfy certain pre-requisites, knoledge-wise and experience-wise. That is why I'm trying to discourage questions like the one above. My reply was jovial and humorous, but the RTFM was dead serious. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wolfe Stephen S GS-11 6 MDSS/SGSI Sent: Wednesday, September 10, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: what is a materilized view ? ROFLMAO ... What a kidder ... v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 10, 2003 7:59 AM To: Multiple recipients of list ORACLE-L Subject: Re: what is a materilized view ? That would be a question for Madonna, not for this list. Read The Fine Manual. On 2003.09.10 07:04, Veeraraju_Mareddi wrote: Dear Freinds, what is a materilized view ? what is the use of it and how to create it. Any docs or notes or white papers will be helpful. TIA, Rajuveera ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Veeraraju_Mareddi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfe Stephen S GS-11 6 MDSS/SGSI INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You
RE: what is a materialized view ?
Title: RE: what is a materialized view ? I have our print room print them double sided and glued binding. It's just so hard to highlight and mark up a PDF April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas /\ / \ / \ \ / \/ \ \ \ \ Few people really enjoy the simple pleasure of flying a kite Adam Wells age 11 -Original Message- From: Hately, Mike (LogicaCMG) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 10, 2003 9:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: what is a materialized view ? Consider this : Research is the best way to discover and to remember information. The acquisition of information increases a DBA's skill. Skilled DBAs should (?!!) always be in demand. Therefore, by encouraging people to read manuals you're helping to make me unemployed. Just something to consider =) Mike Hately PS Personally I think that the discontinuation of paper manuals was a disaster. I used to take concepts manuals when I went in the bath. A great place to learn. No kids, no TV. -Original Message- Sent: 10 September 2003 13:54 To: Multiple recipients of list ORACLE-L I have a problem with unwillingness to invest effort and do some very basic research from the readily available literature. Oracle has a manual called Database Concepts which is accessible on approximately 87 billion places on the internet, for free, of course. Previous incarnations of this list (and I am a long time member) were, in my opinion, rendered unusable by a flood of trivial questions. I do honestly believe that, in order to fully participate in this list, a person must satisfy certain pre-requisites, knoledge-wise and experience-wise. That is why I'm trying to discourage questions like the one above. My reply was jovial and humorous, but the RTFM was dead serious. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Wolfe Stephen S GS-11 6 MDSS/SGSI Sent: Wednesday, September 10, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: what is a materilized view ? ROFLMAO ... What a kidder ... v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 10, 2003 7:59 AM To: Multiple recipients of list ORACLE-L Subject: Re: what is a materilized view ? That would be a question for Madonna, not for this list. Read The Fine Manual. On 2003.09.10 07:04, Veeraraju_Mareddi wrote: Dear Freinds, what is a materilized view ? what is the use of it and how to create it. Any docs or notes or white papers will be helpful. TIA, Rajuveera ** This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Veeraraju_Mareddi INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net
RE: what is a materialized view ?
Re: PS Personally I think that the discontinuation of paper manuals was a disaster. I used to take concepts manuals when I went in the bath. A great place to learn. No kids, no TV. Absolutely! I still have several of my old v6 and v7 manuals just because of the little schnibbles of information I have scribbled in the margins. Much has changed, but not everything. Just my 2¢ worth... Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
materialized view failing to create in 9.2.0.3
Hi Mview gurus I have tried to create a materialized view with the following characteristics. If I have something wrong with my design that will avoid this i would like to know but I suspect that it is a bug of sorts. Can anyone provide some useful input Sorry for the long email CREATE MATERIALIZED VIEW LOG ON EP.EP_CURVEDETAILS TABLESPACE EP PCTFREE 60 PCTUSED 0 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) WITH ROWID, PRIMARY KEY ( CURVEDETAILS_DATE, CURVE_ID) EXCLUDING NEW VALUES CREATE MATERIALIZED VIEW EP.EP_MV_CURVEPERIOD_VALUES TABLESPACE EP BUILD IMMEDIATE USING INDEX TABLESPACE EP REFRESH FAST ON COMMIT AS SELECT curvedetails_id, curve_id, curvedetails_date, 'P0030' AS P_No, P0030 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P0100' AS P_No, P0100 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P0130' AS P_No, P0130 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0200' AS P_No, P0200 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0230' AS P_No, P0230 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P0300' AS P_No, P0300 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P0330' AS P_No, P0330 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P0400'AS P_No, P0400 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0430'AS P_No, P0430 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0500'AS P_No, P0500 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0530'AS P_No, P0530 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0600'AS P_No, P0600 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0630'AS P_No, P0630 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0700'AS P_No, P0700 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0730'AS P_No, P0730 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0800'AS P_No, P0800 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0830'AS P_No, P0830 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0900'AS P_No, P0900 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P0930'AS P_No, P0930 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1000'AS P_No, P1000 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1030'AS P_No, P1030 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1100'AS P_No, P1100 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1130'AS P_No, P1130 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1200'AS P_No, P1200 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1230'AS P_No, P1230 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1300'AS P_No, P1300 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1330'AS P_No, P1330 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1400'AS P_No, P1400 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1430'AS P_No, P1430 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id, curvedetails_date, 'P1500'AS P_No, P1500 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1530'AS P_No, P1530 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1600'AS P_No, P1600 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1630'AS P_No, P1630 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date, 'P1700'AS P_No, P1700 AS P_Reading FROM EP_CURVEDETAILS UNION ALL SELECT curvedetails_id, curve_id,curvedetails_date
materialized view log
Hi, All I have a question on how to interpret materialized view log, which has a field change_vector$$. I think this fields record all the changes on master table, I need to understand the updates on the master table so I can develop my own API to copy the change to a flat file. Any help would be appreciated. regards, -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Li, Xiangli INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Materialized view
I'm not sure if you want to improve the query, or the replication process. If it's the replication process, one other option is 'refresh force'. Then, if I understand the docs correctly, you get a fast refresh if it can; and if refresh fast is not possible, then refresh complete. That's the theory. Now whether or not it actually works that way For what it's worth, I always use 'refresh force'. -Original Message- How can I create materialized view(mv) for the following statement using 'refresh fast' method. I can use 'refresh complete' option to create this mv, but how can I use 'refresh fast' option for the below statement in order to improve performance in query. Any workaround ? select mas.salesrep_id,sum(del.qty*del.price)-sum(del.discount) from sales_master mas, sales_details del where mas.transaction_id =del.transaction_id group by mas.salesrep_id. Thanks in advance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Materialized view
Title: RE: Materialized view don't you need a 'count(*)' for refresh fast? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Stephen Lee [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 26, 2003 11:50 AM To: Multiple recipients of list ORACLE-L Subject: RE: Materialized view I'm not sure if you want to improve the query, or the replication process. If it's the replication process, one other option is 'refresh force'. Then, if I understand the docs correctly, you get a fast refresh if it can; and if refresh fast is not possible, then refresh complete. That's the theory. Now whether or not it actually works that way For what it's worth, I always use 'refresh force'. -Original Message- How can I create materialized view(mv) for the following statement using 'refresh fast' method. I can use 'refresh complete' option to create this mv, but how can I use 'refresh fast' option for the below statement in order to improve performance in query. Any workaround ? select mas.salesrep_id,sum(del.qty*del.price)-sum(del.discount) from sales_master mas, sales_details del where mas.transaction_id =del.transaction_id group by mas.salesrep_id. Thanks in advance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Materialized view
For fast refresh, you need to have a snapshot log on the master table and the snapshot must be registered with the master. Keep in mind, we are only talking about simple, read-only, snapshots here. There are fancier ways of moving data here and there. But I would have to do the RTFM thing before expounding on those. -Original Message- don't you need a 'count(*)' for refresh fast? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, June 26, 2003 11:50 AM To: Multiple recipients of list ORACLE-L I'm not sure if you want to improve the query, or the replication process. If it's the replication process, one other option is 'refresh force'. Then, if I understand the docs correctly, you get a fast refresh if it can; and if refresh fast is not possible, then refresh complete. That's the theory. Now whether or not it actually works that way For what it's worth, I always use 'refresh force'. -Original Message- How can I create materialized view(mv) for the following statement using 'refresh fast' method. I can use 'refresh complete' option to create this mv, but how can I use 'refresh fast' option for the below statement in order to improve performance in query. Any workaround ? select mas.salesrep_id,sum(del.qty*del.price)-sum(del.discount) from sales_master mas, sales_details del where mas.transaction_id =del.transaction_id group by mas.salesrep_id. Thanks in advance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Materialized view restriction?
I am trying to create a top layer materialized view that joins other materialized views and I am getting an ORA-12053: this is not a valid nested materialized view. I can't even use one of the MV's. Here is a simple example to illustrate the problem. Could someone please tell me what I'm missing? QUESTD:scott create materialized view log on emp 2 with sequence, rowid ( 3 EMPNO, 4 ENAME, 5 JOB, 6 MGR, 7 HIREDATE, 8 SAL, 9 COMM, 10 DEPTNO) 11 including new values 12 / Materialized view log created. QUESTD:scott create materialized view log on dept 2 with sequence, rowid( 3 DEPTNO, 4 DNAME, 5 LOC) 6 including new values 7 / Materialized view log created. QUESTD:scott CREATE MATERIALIZED VIEW EMP_DEPT_MVT 2BUILD IMMEDIATE 3USING INDEX 4REFRESH FAST 5-- ON COMMIT 6WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT 7ENABLE QUERY REWRITE 8 as 9 select a.rowid emprowid, b.rowid deptrowid, a.ename, b.loc 10from emp a, dept b 11 where a.deptno = b.deptno 12 / Materialized view created. QUESTD:scott create materialized view log on emp_dept_mvt 2 with sequence, rowid( 3 ENAME, 4 LOC) 5 including new values 6 / Materialized view log created. QUESTD:scott QUESTD:scott CREATE MATERIALIZED VIEW EMP_DEPT_NEST_MVT 2BUILD IMMEDIATE 3USING INDEX 4REFRESH FAST 5-- ON COMMIT 6WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT 7ENABLE QUERY REWRITE 8 as 9 select a.ename 10from emp_dept_mvt a 11 / from emp_dept_mvt a * ERROR at line 10: ORA-12053: this is not a valid nested materialized view TIA, Mike _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Killough INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: [Replication] Altering Master Object at materialized view replication environment
Hello! That's one of the nuances of MV replication. If you alter the master table, you have to drop and recreate the MV on the snapshot site. This is as per Oracle's internal documentation. :-( However, there is a trick. If you have created the MV using a prebuilt table, then you can have a workaround. [ ... ] Of course, there are several small but crucial steps to be followed. I have described the procedure completely in http://www.dbazine.com/nanda2.html . Hope this helps. Thank you, it works. But it's reasonable only if a table size is huge ... or channel capacity is small :-), so complete refresh becoms very expensive operation ... /sds PS: About http://www.dbazine.com/nanda2.html , imho, something wrong in this sentence: The table has two columns, COL1 NUMBER (9) and COL2 CHAR(1000), COL3 CHAR(900) ... 8-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dennis Sorokin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Replication] Altering Master Object at materialized view replication environment
Dennis, I am glad that it worked for you. Of course, it's useful if the table size is big. In case of a small table, you would just drop and recreate the snapshot. Thanks for pointing out the typo. Regards, Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 20, 2003 9:44 AM replication environment Hello! That's one of the nuances of MV replication. If you alter the master table, you have to drop and recreate the MV on the snapshot site. This is as per Oracle's internal documentation. :-( However, there is a trick. If you have created the MV using a prebuilt table, then you can have a workaround. [ ... ] Of course, there are several small but crucial steps to be followed. I have described the procedure completely in http://www.dbazine.com/nanda2.html . Hope this helps. Thank you, it works. But it's reasonable only if a table size is huge ... or channel capacity is small :-), so complete refresh becoms very expensive operation ... /sds PS: About http://www.dbazine.com/nanda2.html , imho, something wrong in this sentence: The table has two columns, COL1 NUMBER (9) and COL2 CHAR(1000), COL3 CHAR(900) ... 8-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dennis Sorokin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: [Replication] Altering Master Object at materialized view replication environment
Dennis, That's one of the nuances of MV replication. If you alter the master table, you have to drop and recreate the MV on the snapshot site. This is as per Oracle's internal documentation. However, there is a trick. If you have created the MV using a prebuilt table, then you can have a workaround. When you build an MV on a table, Oracle simply assumes the type of the segment as MV rather than the table, i.e. the segment that used to be known as the table is now known as the MV. when the MV is dropped, Oracle does not drop the segment; rather it reverts the type to the table as it used to be. So when you alter the master table, follow the sequence of steps. stop replication drop the MV alter the master table alter the prebuilt table in the same way. Build the MV on the prebuilt table again. Of course, there are several small but crucial steps to be followed. I have described the procedure completely in http://www.dbazine.com/nanda2.html . Hope this helps. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 14, 2003 3:53 AM replication environment Hello How can i replicate DDL on table (modify column for example) in materialized view replication environment ? DBMS_REPCAT.ALTER_MASTER_REPOBJECT alter the object, put changes are not propagated to MV site ... I try DBMS_REFRESH.REFRESH and DBMS_REPCAT.REFRESH_MVIEW_REPGROUP at MV site, but I steel don't see the changes. Is it possible to propagate DDL to MV site ? Thnx. /sds -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dennis Sorokin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[Replication] Altering Master Object at materialized view replication environment
Hello How can i replicate DDL on table (modify column for example) in materialized view replication environment ? DBMS_REPCAT.ALTER_MASTER_REPOBJECT alter the object, put changes are not propagated to MV site ... I try DBMS_REFRESH.REFRESH and DBMS_REPCAT.REFRESH_MVIEW_REPGROUP at MV site, but I steel don't see the changes. Is it possible to propagate DDL to MV site ? Thnx. /sds -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dennis Sorokin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
problems with materialized view
Hi, On Oracle 8.1.7.2 /aix 4.3 Refreshing complete a MV took forever, I canceled them after 10 hours. Dropping and recreating it took 10 minutes. On another MV, refreshing complete uses over 1G temp space, when dropping and recreating no temp tablespace busting. On smaller ones no difference between drop/create and refresh. The ones I've got problems with have query over 3 pages long. Oracle bugs ? = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Cross version SNAPSHOT/MATERIALIZED VIEW replication
Our current master is 8.1.6.3 as is our Snapshot database. However, at some time in the future our master database will be upgraded to 8.1.7.4. Our options are to stay with 8.1.6.3, upgrade to 8.1.7.4, or upgrade to 9.1.2. We can do this either before or after the master's upgrade. So our possible configurations are: MASTERSLAVE 8.1.6.3 8.1.7.4 8.1.6.3 9.1.2 8.1.7.4 8.1.6.3 8.1.7.4 8.1.7.4 8.1.7.4 9.1.2 These will be upgrades in place. Also, our customers are using a variety of methods to access the snapshot database including SQLNet and ODBC. I don't think that the OS environments are an issue but the master is on a DEC/Alpha UNIX box and the snapshot is on a Win2K box. Has anybody done this (upgrades in place for any of the master/slave pairs) and, if so, did you run into any issues? Any input will be gratefully accepted in the spirit in which it is offered. We are RTing the FMs for all they're worth but the upgrade in place is treated as a non-issue (which I suspect is not the case) and I'd rather hear from someone who has actually done it. TIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
materialized view of a remote partition
8.1.7.4 create snapshot TEMP as select * from [EMAIL PROTECTED] partition (020904) ERROR at line 1: ORA-14100: partition extended table name cannot refer to a remote object why not? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: materialized view of a remote partition
Hi Ray, Please let me know if you received any answer. Thanks, Ashoke -Original Message- Sent: Friday, September 06, 2002 10:23 AM To: Multiple recipients of list ORACLE-L 8.1.7.4 create snapshot TEMP as select * from [EMAIL PROTECTED] partition (020904) ERROR at line 1: ORA-14100: partition extended table name cannot refer to a remote object why not? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: materialized view of a remote partition
On Fri, Sep 06, 2002 at 07:23:28AM -0800, Ray Stell wrote: 8.1.7.4 create snapshot TEMP as select * from [EMAIL PROTECTED] partition (020904) ERROR at line 1: ORA-14100: partition extended table name cannot refer to a remote object Well, it ain't pretty, but... on the remote db: SQL create snapshot eh_snap as select * from emp partition (e18); Materialized view created. on the local db: SQL create snapshot eh_snap as select * from [EMAIL PROTECTED]; Materialized view created. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Materialized view selection.
Depends mostly on your users needs. If most of the time your users would want monthly sum of measures, you give them a materialized view for that. You can also use the summary advisor feature of oracle (dbms_olap) to find out which mviews oracle thinks is needed based on usage. =) Chuan Zhang wrote: Hi, All, Just wonder what's tool or methodology you are using to select materialized view in Oracle data warehouse environment? Many thanks in advance, Chuan Name: InterScan_Disclaimer.txt InterScan_Disclaimer.txtType: Plain Text (text/plain) Encoding: 7bit -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maria Aurora VT de la Vega INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Materialized View
Title: RE: Simple question on logging.. Not sure but I think you need to have Enterprise Edition for that. Otherwise it should work fine Aleem -Original Message- From: Mandal, Ashoke [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 5:18 AM To: Multiple recipients of list ORACLE-L Subject: Materialized View Hi All, Is it true that I cannot create a materialized view by accessing a remote table? Here is the syntax for creating the materialized view but it does not work. REM Create materialized view mast_time_mv CREATE MATERIALIZED VIEW mast_time_mv PCTFREE 0 TABLESPACE mviews_tb STORAGE (INITIAL 256k NEXT 256k PCTINCREASE 0) PARALLEL BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS SELECT * from [EMAIL PROTECTED]; Where WHST.TEST.CORP.ICI.COM is database link. Thanks, Ashoke
Materialized view selection.
Hi, All, Just wonder what's tool or methodology you are using to select materialized view in Oracle data warehouse environment? Many thanks in advance, Chuan Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
Materialized View
Title: RE: Simple question on logging.. Hi All, Is it true that I cannot create a materialized view by accessing a remote table? Here is the syntax for creating the materialized view but it does not work. REM Create materialized view mast_time_mvCREATE MATERIALIZED VIEW mast_time_mv PCTFREE 0 TABLESPACE mviews_tb STORAGE (INITIAL 256k NEXT 256k PCTINCREASE 0) PARALLEL BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS SELECT * from [EMAIL PROTECTED]; Where WHST.TEST.CORP.ICI.COM is database link. Thanks, Ashoke
Please Help: estimating size for materialized view Question
Hi
I am trying to find estimated size for a view using
DBMS_OLAP.ESTIMATE_SUMMARY_SIZE package.
Using the following sql (logged in as SYS). I am new
to materialized views
and DBMS_OLAP.ESTIMATE_SUMMARY_SIZE usage.
I am doing this through SQL*Plus logged in as SYS.
I also set SERVEROUTPUT ON
and VAR num_bytes NUMBER
VAR num_rows NUMBER at SQL prompt in SQL*Plus.
Can someone help me how to find the estimated
size before creating a materialized view for the
following syntax for
the following.
thanks,
Srinivas
exec DBMS_OLAP.estimate_summary_size('test_est_size',
'SELECT empno, ename, dname, loc FROM scott.emp,
scott.dept where emp.deptno=dept.deptno',
:num_rows, :num_bytes)
This is the output I got :
SQL set SERVEROUTPUT ON
SQL show serveroutput
serveroutput ON size 2000 format WORD_WRAPPED
SQL VAR num_rows number
SQL var num_bytes number
SQL var
variable mv_size
datatype NUMBER
variable num_bytes
datatype NUMBER
variable num_rows
datatype NUMBER
SQL exec
DBMS_OLAP.estimate_summary_size('test_est_size',
'SELECT empno, ename, dname, loc FROM scott.emp,
scott.dept where emp.deptno=dept.deptno',
:num_rows, :num_bytes)
PL/SQL procedure successfully completed.
SQL print :num_rows
NUM_ROWS
--
SQL print :num_bytes
NUM_BYTES
--
__
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Srinivas
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Join Performance in Materialized View
Title: RE: Join Performance in Materialized View I have a join creating a materialized view. I have been using use_hash hint and parallelism to tune part of the query (join that involves 2 large tables). Thanks for the info. on setting hash_area_size explictly it made a big difference. I am using Direct IO and - Wow! at least 40% perf. increase. Here is the rub .I am somewhat limited in what I can do if I want to use refresh fast in MV creation. I have a number of small codetables to join into the 2 large tables doing a hash join. Also, I am using outerjoins and hitting the same codetables with aliases more than once. I created all small tables as IOT which is part of outer join predicate per suggestion from another colleague and friend. This is for a 16 cpu server with 20Gb rams. It is on the largest table with 9M rows so the results in the MV will be 9M rows. Just the two tables took 33 minutes adding these smaller tables results in time taking about 2 hours...hmmm. Like, create materialized view partition select /*+USE_HASE(crt) PARALLEL(crt,8)*/ Adding these small table joins along with the hint above causes the CBO to want to do hash outer joins on everything which subsequently results in writing to temporary tablespace every setting sort_area_size, sort_area_retained_size and hash_area_size very high. I have allocated a special temporary tablespace with large extent sizes and LMT. Since my sort_area is so large it would be silly to set temporary tablespace as a multiple of it but what are the implications of it being smaller. Doing a lot of I/O waits on temporary tablespace even yet just by adding these small tables. Seems I would be better off joining the large tables then using nested materialized view to join the rest but how deep can I get with these MV's or is there another way I can accomplish getting the result set for the first two large tables then the smaller tables by using hints (ordered or use_merge). Inline views are not supported in refresh options of MV. Any ideas?
Materialized View
I tried following SQL SQL create materialized view mat_dept 2 refresh fast with rowid 3 start with sysdate next sysdate+ 1/(24*60) 4 as (select * from dept@softek1); as (select * from dept@softek1) * ERROR at line 4: ORA-01435: user does not exist ORA-06512: at SYS.DBMS_SNAPSHOT_UTL, line 845 ORA-06512: at line 1 Why such error is coming and how can i create materialized view? Thanks in advance Atul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Materialized View
I would start with checking what database links are vissible. Do a select * from all_db_links. A database link in the invoking schema should take precedence over a public one. Whatever link is being used, verify that you can connect to the remote database using it's connection symantics. You can pull the clear text password out of sys.link$ for this puspose. Bill --- Atul Kumar [EMAIL PROTECTED] wrote: I tried following SQL SQL create materialized view mat_dept 2 refresh fast with rowid 3 start with sysdate next sysdate+ 1/(24*60) 4 as (select * from dept@softek1); as (select * from dept@softek1) * ERROR at line 4: ORA-01435: user does not exist ORA-06512: at SYS.DBMS_SNAPSHOT_UTL, line 845 ORA-06512: at line 1 Why such error is coming and how can i create materialized view? Thanks in advance Atul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pass INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Attn. : Bill Pass (RE: Materialized View)
Hi Bill Thanks for your reply. In fact when I try following SQL i get result (few records available in table DEPT) select * from dept@softek1 I could not understand the meaning of your statement You can pull the clear text password out of sys.link$ for this puspose.. Please clarify and help. Also, please note that I am not a DBA instead I am an Oracle Developer. Thanks again Atul Kumar -Original Message- Sent: Wednesday, April 24, 2002 7:54 PM To: Multiple recipients of list ORACLE-L I would start with checking what database links are vissible. Do a select * from all_db_links. A database link in the invoking schema should take precedence over a public one. Whatever link is being used, verify that you can connect to the remote database using it's connection symantics. You can pull the clear text password out of sys.link$ for this puspose. Bill --- Atul Kumar [EMAIL PROTECTED] wrote: I tried following SQL SQL create materialized view mat_dept 2 refresh fast with rowid 3 start with sysdate next sysdate+ 1/(24*60) 4 as (select * from dept@softek1); as (select * from dept@softek1) * ERROR at line 4: ORA-01435: user does not exist ORA-06512: at SYS.DBMS_SNAPSHOT_UTL, line 845 ORA-06512: at line 1 Why such error is coming and how can i create materialized view? Thanks in advance Atul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pass INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Atul Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
What makes a fast refresh materialized view too complex?
I am not clear on what will make a fast refresh materialized view too complex. What functions cannot be used in a FRMV that is a single table aggregation? The following example won't work if I try to add the decode: create materialized view log on emp tablespace tbsname with sequence, primary key, rowid (job, hiredate, sal, comm) including new values ; create materialized view emp_mv2 tablespace tbsname build immediate refresh fast on commit with rowid enable query rewrite as select count(*) n, job, to_char(hiredate,'DD-MON-') hiredate, sum(sal) sum_sal, count(sal) cnt_sal, sum(comm) sum_comm, count(comm) cnt_comm -- , sum(decode(status,'O',sal,comm)), count(decode(status,'O',sal,comm)) -- Add this line and it is not fast refresh. from emp group by job, to_char(hiredate,'DD-MON-') / Thanks, Mike _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Killough INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fast refresh materialized view is not updateable
I create a fast refresh materialized view that is a single table aggregation, but dml on the base table does not update the materialized view. In dba_mviews, FAST_REFRESHABLE = DIRLOAD_LIMITEDDML. I think that I have satisfied the requirements for a fast refresh MV. I can't see the problem. Here is a stripped down version of the view, that can be created, but is not updateable: create materialized view log on scd_financial_detail tablespace tbsname with rowid (claim_carrier_key, eval_date, trn_reserve) including new values ; create materialized view scd_financial_summary_pit_mv tablespace tbsname build immediate refresh fast on commit with rowid enable query rewrite AS select count(*), claim_carrier_key, eval_date, sum(trn_reserve) fin_tot_res from scd_financial_detail group by claim_carrier_key, eval_date / Could anyone please tell me what I'm missing? Thanks, Mike _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Killough INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Fast refresh materialized view is not updateable
Vadim,
I added the field but it still won't fast refresh. When it is first created,
the STALENESS col in dba_mviews = FRESH. When I delete a record in the base
table, it becomes UNUSABLE. A fast refresh gives me this error:
QUESTT:hcl_data exec
dbms_mview.refresh('scd_financial_summary_pit_mv1','F');
BEGIN dbms_mview.refresh('scd_financial_summary_pit_mv1','F'); END;
*
ERROR at line 1:
ORA-12057: materialized view HCL_DATA.SCD_FINANCIAL_SUMMARY_PIT_MV1 is
INVALID and must complete refresh
ORA-06512: at SYS.DBMS_SNAPSHOT, line 814
ORA-06512: at SYS.DBMS_SNAPSHOT, line 872
ORA-06512: at SYS.DBMS_SNAPSHOT, line 852
ORA-06512: at line 1
Mike
From: Vadim Gorbounov [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Fast refresh materialized view is not updateable
Date: Tue, 16 Apr 2002 13:53:29 -0800
Mike,
You missing this field:
count(trn_reserve) cnt_tot_res
Cheers,
Vadim Gorbounov
Oracle DBA
724 Solutions Inc.
Tel:(416)226-2900 ext 5070
Email: [EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, April 16, 2002 4:14 PM
To: Multiple recipients of list ORACLE-L
I create a fast refresh materialized view that is a single table
aggregation, but dml on the base table does not update the materialized
view. In dba_mviews, FAST_REFRESHABLE = DIRLOAD_LIMITEDDML. I think that I
have satisfied the requirements for a fast refresh MV. I can't see the
problem. Here is a stripped down version of the view, that can be created,
but is not updateable:
create materialized view log on scd_financial_detail
tablespace tbsname
with rowid (claim_carrier_key, eval_date, trn_reserve)
including new values
;
create materialized view scd_financial_summary_pit_mv
tablespace tbsname
build immediate
refresh fast on commit
with rowid
enable query rewrite AS
select count(*),
claim_carrier_key,
eval_date,
sum(trn_reserve) fin_tot_res
from scd_financial_detail
group by
claim_carrier_key,
eval_date
/
Could anyone please tell me what I'm missing?
Thanks,
Mike
_
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mike Killough
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vadim Gorbounov
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mike Killough
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Fast refresh materialized view is not updateable
Mike,
What is your Oracle version? There should be no problem. I see you are
refreshing 'scd_financial_summary_pit_mv1'
is trailing 1 a typo or you changed MV name?
I have no problem with your scenari on 8.1.7.2 on Solaris. Although, I
removed enable query rewrite - it is not enabled on this instance, but
it shouldn't make a difference.
SQL connect test/test@dev
Connected.
SQL create table scd_financial_detail(
2 claim_carrier_key varchar2(10),
3 eval_date date,
4 trn_reserve number);
Table created.
SQL
SQL
SQL create materialized view log on scd_financial_detail
2 with rowid (claim_carrier_key, eval_date, trn_reserve)
3 including new values
4 ;
Materialized view log created.
SQL create materialized view scd_financial_summary_pit_mv
2 build immediate
3 refresh fast on commit
4 with rowid
5 AS
6 select count(*) n,
7 claim_carrier_key,
8 eval_date,
9 sum(trn_reserve) fin_tot_res,
10 count(trn_reserve) cnt_tot_res
11from scd_financial_detail
12group by
13 claim_carrier_key,
14 eval_date
15 /
Materialized view created.
SQL insert into scd_financial_detail values('1', sysdate, 23);
1 row created.
SQL commit;
Commit complete.
SQL select * from scd_financial_summary_pit_mv;
N CLAIM_CARR EVAL_DATE FIN_TOT_RES CNT_TOT_RES
-- -- - --- ---
1 1 16-APR-02 23 1
SQL delete from scd_financial_detail;
1 row deleted.
SQL commit;
Commit complete.
SQL select * from scd_financial_summary_pit_mv;
no rows selected
SQL exec dbms_mview.refresh('scd_financial_summary_pit_mv', 'F');
PL/SQL procedure successfully completed.
SQL
-Original Message-
Sent: Tuesday, April 16, 2002 6:28 PM
To: Multiple recipients of list ORACLE-L
Vadim,
I added the field but it still won't fast refresh. When it is first created,
the STALENESS col in dba_mviews = FRESH. When I delete a record in the base
table, it becomes UNUSABLE. A fast refresh gives me this error:
QUESTT:hcl_data exec
dbms_mview.refresh('scd_financial_summary_pit_mv1','F');
BEGIN dbms_mview.refresh('scd_financial_summary_pit_mv1','F'); END;
*
ERROR at line 1:
ORA-12057: materialized view HCL_DATA.SCD_FINANCIAL_SUMMARY_PIT_MV1 is
INVALID and must complete refresh
ORA-06512: at SYS.DBMS_SNAPSHOT, line 814
ORA-06512: at SYS.DBMS_SNAPSHOT, line 872
ORA-06512: at SYS.DBMS_SNAPSHOT, line 852
ORA-06512: at line 1
Mike
From: Vadim Gorbounov [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Fast refresh materialized view is not updateable
Date: Tue, 16 Apr 2002 13:53:29 -0800
Mike,
You missing this field:
count(trn_reserve) cnt_tot_res
Cheers,
Vadim Gorbounov
Oracle DBA
724 Solutions Inc.
Tel:(416)226-2900 ext 5070
Email: [EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, April 16, 2002 4:14 PM
To: Multiple recipients of list ORACLE-L
I create a fast refresh materialized view that is a single table
aggregation, but dml on the base table does not update the materialized
view. In dba_mviews, FAST_REFRESHABLE = DIRLOAD_LIMITEDDML. I think that I
have satisfied the requirements for a fast refresh MV. I can't see the
problem. Here is a stripped down version of the view, that can be created,
but is not updateable:
create materialized view log on scd_financial_detail
tablespace tbsname
with rowid (claim_carrier_key, eval_date, trn_reserve)
including new values
;
create materialized view scd_financial_summary_pit_mv
tablespace tbsname
build immediate
refresh fast on commit
with rowid
enable query rewrite AS
select count(*),
claim_carrier_key,
eval_date,
sum(trn_reserve) fin_tot_res
from scd_financial_detail
group by
claim_carrier_key,
eval_date
/
Could anyone please tell me what I'm missing?
Thanks,
Mike
_
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mike Killough
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like
Complete Refresh of Materialized view taking long time
Hi, We are trying to refresh 2 MV'S and they are taking more than 23 hrs to refresh and consuming 100% cpuis this behaviour of using 100% CPU is normal. if we run the query to create view it takes only about 5 min to run... What can be the possible reasons of this behaviour. Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Materialized View over 2 tables
Title: Materialized View over 2 tables Thats not a problem. That is well-documented feature and you can check it with standard documents. Fast Refreshes are meant only for simple MVs/Snapshots and not for complex ones. You are supposed to do a complete refresh for complex ones. HTH, Rajesh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Daiminger, Helmut Sent: Tuesday, October 30, 2001 11:40 AM To: Multiple recipients of list ORACLE-L Subject: Materialized View over 2 tables Hi! Can I have a materialized view to be refreshed fast, when it is created over 2 base tables? Fast refreshes work fine as long as the materialized view is based on 1 base table. But if the view is based on more base tables, it only seems to work with a complete refresh. Does anybody have any experience with that problem? This is 8.1.7 on Sun Solaris. Thanks, Helmut
RE: Materialized View over 2 tables
Helmut, Rajesh is absolutely correct. If one or more of the base tables are in a remote db then the fast refresh restrictions in the Replication manual apply; if all the base tables are local then the fast refresh restrictions in the Data Warehousing manual apply. A workaround would be to create simple snapshots off of the base tables so they can be fast refreshed and then either create a view or another snapshot off of the base tables. HTH, -- Anita --- Rajesh Dayal [EMAIL PROTECTED] wrote: That's not a problem. That is well-documented feature and you can check it with standard documents. Fast Refreshes are meant only for simple MVs/Snapshots and not for complex ones. You are supposed to do a complete refresh for complex ones. HTH, Rajesh -Original Message- Helmut Sent: Tuesday, October 30, 2001 11:40 AM To: Multiple recipients of list ORACLE-L Hi! Can I have a materialized view to be refreshed fast, when it is created over 2 base tables? Fast refreshes work fine as long as the materialized view is based on 1 base table. But if the view is based on more base tables, it only seems to work with a complete refresh. Does anybody have any experience with that problem? This is 8.1.7 on Sun Solaris. Thanks, Helmut __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Materialized View over 2 tables
Title: Materialized View over 2 tables Hi! Can I have a materialized view to be refreshed fast, when it is created over 2 base tables? Fast refreshes work fine as long as the materialized view is based on 1 base table. But if the view is based on more base tables, it only seems to work with a complete refresh. Does anybody have any experience with that problem? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Materialized View/dbms_mview.refresh Question
I have a materialized view created by user A in schema
A. The view consists of simple table joins and union
statements. I want user B to be able to execute the
refresh on the MV using dbms_mview.refresh.
I've granted select on all the tables to user B along
with creating public synonyms for the tables. I've
also granted all privileges on the MV to user B and
granted REWRITE privs to user B.
When I exec dbms_mview.refresh('A.MVIEW_NAME', 'A');
as user B, I get the following error:
ORA-01031: Insufficent privileges
ORA-06512: at SYS.DBMS_SNAPSHOT, line 610
ORA-06512: at SYS.DBMS_SNAPSHOT, line 667
ORA-06512: at SYS.DBMS_SNAPSHOT, line 647
ORA-06512: at line 1
Any thoughts?
Thanks
__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mohammed bhatti
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Materialized View/dbms_mview.refresh Question
Just solved it...Duh!
as sys
grant alter any materialized view to User_B;
--- mohammed bhatti [EMAIL PROTECTED] wrote:
I have a materialized view created by user A in
schema
A. The view consists of simple table joins and
union
statements. I want user B to be able to execute the
refresh on the MV using dbms_mview.refresh.
I've granted select on all the tables to user B
along
with creating public synonyms for the tables. I've
also granted all privileges on the MV to user B and
granted REWRITE privs to user B.
When I exec dbms_mview.refresh('A.MVIEW_NAME', 'A');
as user B, I get the following error:
ORA-01031: Insufficent privileges
ORA-06512: at SYS.DBMS_SNAPSHOT, line 610
ORA-06512: at SYS.DBMS_SNAPSHOT, line 667
ORA-06512: at SYS.DBMS_SNAPSHOT, line 647
ORA-06512: at line 1
Any thoughts?
Thanks
__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: mohammed bhatti
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mohammed bhatti
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Need script to generate DDL for snapshot/materialized view recrea
!! Please do not post Off Topic to this List !! Hello all, I need to regenerate the DDL to recreate a snapshot/materialized view in an 8.1.6 database, but I don't want to reinvent the wheel. Can anyone help me out with a script? Thanks in advance, Jim __ Jim Hawkins Oracle Database Administrator Data Management Center of Expertise Pharmacia Corporation 800 North Lindbergh Blvd. St. Louis, Missouri 63167 (314) 694-4417 [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 12, 2001 2:46 PM To: Multiple recipients of list ORACLE-L --- Forwarded message follows --- To: [EMAIL PROTECTED],[EMAIL PROTECTED] Send reply to: [EMAIL PROTECTED] Date sent: Wed, 12 Sep 2001 11:28:23 -0700 Indeed. Hopefully people will be motivated to seek truth. For your further consideration: http://www.tikkun.org/index.cfm/action/current/article/40.html - http://www.tikkun.org/index.cfm/action/current/article/50.html (excerpted below) regards, ep On 12 Sep 2001, at 13:53, Erik J. Varney scribbled with alacrity and cogency: To: [EMAIL PROTECTED], [EMAIL PROTECTED] r Date sent: Wed, 12 Sep 2001 13:53:32 -0400 Organization: Central Security Alarm, Inc. I just have to say that the shining moment that has come out this horrible tragedy is this We as Americans have been UNITED EJV -- AntiSemitism at Durban Israel's Best Protection is a World Without Racism Rabbi Michael Lerner | 09.07.2001 New York Times Op-Ed September 5, 2001 By MICHAEL LERNER AN FRANCISCO -- The walkout by the United States and Israel from the United Nations conference on racism in Durban, South Africa, was a predictable and unfortunate consequence of the irresponsibility of those in the Arab world who sought to make Israel a central issue. In supporting this shortsighted choice, the Arab states have played into the hands of right-wing politicians, in both Israel and the United States, who will benefit from the moral discrediting of the antiracist efforts that many had hoped would be the center of the conference. American right-wingers who resist confronting the damaging legacy of slavery and segregation in the United States and the issue of reparations for slavery can hide behind the claim that in repudiating the Durban conference, they were standing up against racist anti-Semitism. Israeli right-wingers can use the record of intolerance at the conference to discredit criticism of the violence inherent in the occupation of the West Bank and Gaza, the bulldozing of Palestinian homes, and stonewalling on the issue of Israeli responsibility for the fate of Palestinian refugees. See, they can now say, all these criticisms are merely the latest attempt to label Zionism as racism, another episode in the eternal history of anti-Jewish sentiments and double standards among the nations. On its face, the charge against Israel is ludicrous. Anyone visiting Israel is immediately struck by the fact that it is one of the most multiethnic societies in the world. It is, to be sure, a state for those who have accepted Judaism. But that includes black Jews from Ethiopia, Jews from India and China who bear all the racial characteristics of people in those societies, Jews who escaped persecution in Arab lands and are racially indistinguishable from Arab Muslims. The fact is that whatever your racial background, you can convert to Judaism and be accepted with full rights in Israel. Moreover, unlike South Africa under apartheid, which targeted anyone born of a certain race, regardless of religion, Israel has given its largest minority, the Israeli Arabs, the vote and the right to representation in the Knesset. Israeli Arabs have an easier time having their votes counted than blacks in some parts of Florida do. Israel has no segregated movie theaters or beaches. And the patterns of segregation in housing are not sanctified by the law. Israel, of course, gives special privileges to Jews - but this has nothing to do with race. It has a great deal to do with the history of the Jewish people, who have been persecuted for thousands of years for being part of a particular religious community. It was in light of that history that Israel was created by the United Nations - as a kind of international affirmative action to rectify a long history of abuse by other nations. Like all such programs, the special privileges for Jews should be phased out over time as anti-Semitism in the world becomes less of a threat-- and unfortunately Durban shows that that may take longer than most of us had hoped. In its treatment of Palestinians, Israel has engaged in activities that are morally unacceptable - violations of fundamental human rights - and deserve to be criticized. [***] Every day I get death threats from various Jews around the world [***] for the role
RE: Need script to generate DDL for snapshot/materialized view re
!! Please do not post Off Topic to this List !!
quick and dirty;
select 'create snapshot '||owner||'.'||name||' as ',query,';'
from dba_snapshots where owner = upper('ownernm')
order by name;
Join to dba_tables for storage params...
-Original Message-
Sent: Thursday, September 13, 2001 10:25 AM
To: Multiple recipients of list ORACLE-L
recrea
!! Please do not post Off Topic to this List !!
Hello all,
I need to regenerate the DDL to recreate a snapshot/materialized view in an
8.1.6 database, but I don't want to reinvent the wheel. Can anyone help me
out with a script?
Thanks in advance,
Jim
__
Jim Hawkins
Oracle Database Administrator
Data Management Center of Expertise
Pharmacia Corporation
800 North Lindbergh Blvd.
St. Louis, Missouri 63167
(314) 694-4417
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, September 12, 2001 2:46 PM
To: Multiple recipients of list ORACLE-L
--- Forwarded message follows ---
To:
[EMAIL PROTECTED],[EMAIL PROTECTED]
Send reply to: [EMAIL PROTECTED]
Date sent: Wed, 12 Sep 2001 11:28:23 -0700
Indeed.
Hopefully people will be motivated to seek truth.
For your further consideration:
http://www.tikkun.org/index.cfm/action/current/article/40.html
-
http://www.tikkun.org/index.cfm/action/current/article/50.html
(excerpted below)
regards,
ep
On 12 Sep 2001, at 13:53, Erik J. Varney scribbled with alacrity and
cogency:
To: [EMAIL PROTECTED], [EMAIL PROTECTED]
r
Date sent: Wed, 12 Sep 2001 13:53:32 -0400
Organization: Central Security Alarm, Inc.
I just have to say that the shining moment that has come out this horrible
tragedy is this We as Americans have been UNITED
EJV
--
AntiSemitism at Durban
Israel's Best Protection is a World Without Racism
Rabbi Michael Lerner | 09.07.2001 New York Times Op-Ed
September 5, 2001
By MICHAEL LERNER
AN FRANCISCO -- The walkout by the United States and Israel from the United
Nations
conference on racism in Durban, South Africa, was a predictable and
unfortunate
consequence of the irresponsibility of those in the Arab world who sought to
make
Israel a central issue. In supporting this shortsighted choice, the Arab
states have
played into the hands of right-wing politicians, in both Israel and the
United States,
who will benefit from the moral discrediting of the antiracist efforts that
many had
hoped would be the center of the conference.
American right-wingers who resist confronting the damaging legacy of slavery
and
segregation in the United States and the issue of reparations for slavery
can hide
behind the claim that in repudiating the Durban conference, they were
standing up
against racist anti-Semitism. Israeli right-wingers can use the record of
intolerance
at the conference to discredit criticism of the violence inherent in the
occupation of
the West Bank and Gaza, the bulldozing of Palestinian homes, and
stonewalling on the
issue of Israeli responsibility for the fate of Palestinian refugees. See,
they can
now say, all these criticisms are merely the latest attempt to label
Zionism as
racism, another episode in the eternal history of anti-Jewish sentiments and
double
standards among the nations.
On its face, the charge against Israel is ludicrous. Anyone visiting Israel
is
immediately struck by the fact that it is one of the most multiethnic
societies in the
world. It is, to be sure, a state for those who have accepted Judaism. But
that
includes black Jews from Ethiopia, Jews from India and China who bear all
the racial
characteristics of people in those societies, Jews who escaped persecution
in Arab
lands and are racially indistinguishable from Arab Muslims. The fact is that
whatever
your racial background, you can convert to Judaism and be accepted with full
rights in
Israel.
Moreover, unlike South Africa under apartheid, which targeted anyone born of
a certain
race, regardless of religion, Israel has given its largest minority, the
Israeli
Arabs, the vote and the right to representation in the Knesset. Israeli
Arabs have an
easier time having their votes counted than blacks in some parts of Florida
do. Israel
has no segregated movie theaters or beaches. And the patterns of segregation
in
housing are not sanctified by the law.
Israel, of course, gives special privileges to Jews - but this has nothing
to do with
race. It has a great deal to do with the history of the Jewish people, who
have been
persecuted for thousands of years for being part of a particular religious
community.
It was in light of that history that Israel was created by the United
Nations - as a
kind of international affirmative action to rectify a long history of abuse
by other
nations. Like all such programs, the special privileges for Jews should be
phased out
over time as anti-Semitism in the world becomes less of a threat
Materialized View Problem
Hi All, I am trying to create a materialized view that contains a subquery for the FROM clause. I can run the sql in the view fine, but when I try to create the view I get the following error: MES_LOT a, * ERROR at line 27: ORA-00998: must name this expression with a column alias This is the first table listed in the subquery. As you can see, I have the table aliased. Does anyone have an idea of what could be causing this error, or what I am doing wrong? Thanks; Terri Williamson Kemet Electronics [EMAIL PROTECTED] (864) 963-6428 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Terri Williamson/MIS/HQ/KEMET/US INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Materialized View Problem
Can you post the entire CREATE statement? Rick -Original Message- From: Terri Williamson/MIS/HQ/KEMET/US [SMTP:[EMAIL PROTECTED]] Sent: Thursday, February 22, 2001 9:26 AM To: Multiple recipients of list ORACLE-L Subject: Materialized View Problem Hi All, I am trying to create a materialized view that contains a subquery for the FROM clause. I can run the sql in the view fine, but when I try to create the view I get the following error: MES_LOT a, * ERROR at line 27: ORA-00998: must name this expression with a column alias This is the first table listed in the subquery. As you can see, I have the table aliased. Does anyone have an idea of what could be causing this error, or what I am doing wrong? Thanks; Terri Williamson Kemet Electronics [EMAIL PROTECTED] (864) 963-6428 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Terri Williamson/MIS/HQ/KEMET/US INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
