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, > > > > > >
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 PREBUIL
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 >
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 websi
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 simp
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 materi
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]
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' >
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]
> >
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 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 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 -
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]
