tag:blogger.com,1999:blog-4096575.post2588114712109396081..comments2024-03-19T03:20:26.726-04:00Comments on The Arup Nanda Blog: Ultra-Fast MV Alteration using Prebuilt Table OptionArup Nandahttp://www.blogger.com/profile/03392706779349258765noreply@blogger.comBlogger22125tag:blogger.com,1999:blog-4096575.post-17999928091707922762022-06-18T01:54:03.787-04:002022-06-18T01:54:03.787-04:00Great website, continue the Excellent work!Great website, continue the Excellent work!Tizanidine 4 mghttps://www.healthcareshopy.com/product/tizanidine-4-mg/noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-17477062536675188462012-09-11T15:15:20.521-04:002012-09-11T15:15:20.521-04:00Great information. Lots of things I did not know b...Great information. Lots of things I did not know before!Elliott Broidyhttp://elliottbroidy.corporatenewssource.comnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-65979398654644503862012-08-31T13:42:10.906-04:002012-08-31T13:42:10.906-04:0013CREATE MATERIALIZED VIEW "ARUP"."...13CREATE MATERIALIZED VIEW "ARUP"."MV1" ("CNT")<br />ORGANIZATION HEAP ON PREBUILT TABLE PCTFREE 10<br /><br />It doesnt work. <br /><br />Could you provide full test-script?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-60003819956783270412012-07-31T02:18:01.625-04:002012-07-31T02:18:01.625-04:00Hello .. I have never seen a smart articles that y...Hello .. I have never seen a smart articles that you created. It really helped me to get back my ideas for writing. I will save this post, for I learn more.Buy Generic Viagrahttp://www.callpills.com/generic_viagra.phpnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-72980499623988167082011-12-14T13:04:36.646-05:002011-12-14T13:04:36.646-05:00Great informative article.Great informative article.Viagrahttp://www.kwikmed.com/viagra.aspnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-83845431995093198662011-07-11T14:39:38.803-04:002011-07-11T14:39:38.803-04:00Hi Arup,
good post. Have a question though. After...Hi Arup,<br /><br />good post. Have a question though. After adding a column, how does the the data for newly added column refresh in the mv?<br /><br />Will it not require a Full refresh? That is an outage for the application and database. If so, how can we minimize the downtime without doing a complete refresh.<br /><br />thanks<br />Srikanthsrikanth-rajavaramhttps://www.blogger.com/profile/13497922081783904990noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-64422048510762651062011-04-14T20:27:25.522-04:002011-04-14T20:27:25.522-04:00Hi, We would like to add a LAST_UPDATE_DATE field...Hi, We would like to add a LAST_UPDATE_DATE field to the MView because our base table does not have a valid CDC column. We use the PREBUILT approach to add this column as follows: ALTER TABLE table_MV <br />ADD (LAST_UPDATE_DT DATE DEFAULT SYSDATE).<br /><br />This works for newly inserted records, which are stamped with the record insert date, but for existing records which are updated, the LAST_UPDATE_DT is not modified. Do you know how to get it updated.<br /><br />Thanks a lot for your help.Hichemnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-19408459631630517872010-07-20T17:18:39.491-04:002010-07-20T17:18:39.491-04:00I think that MV is a bit confusing, Same happens t...I think that MV is a bit confusing, Same happens to me when I try to understand what is used <a href="http://www.xlpharmacy.com/" title="viagra online " rel="nofollow">viagra online </a>for. I think I have a headache again.Unknownhttps://www.blogger.com/profile/05059208787433064919noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-18064544502738458832010-04-30T11:17:58.601-04:002010-04-30T11:17:58.601-04:00It's Ok. I am looking how to refresh the newly...It's Ok. I am looking how to refresh the newly added column in MV. <br />I have MV which is created with refresh FAST. MV log is created in master DB. This MV is altered and added a new column and MV is refreshed manually, But it is not populating the new added column. It is refresing the all the rows but not populating the newly added column. Is there any way to do this?Discover your worldhttps://www.blogger.com/profile/00118110183568239463noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-50551580188134832532010-04-02T10:16:40.215-04:002010-04-02T10:16:40.215-04:00@John: the issue is very much valid. The example y...@John: the issue is very much valid. The example you gave is the simplest and rudimentary. Note your example:<br /><br />create MV yyy refresh fast on demand with primary key as select * from xxx<br /><br />The defining query is "select * from base table", which allows this change. In all cases, the MV is not as simple as "select *", because it is not useful in anyway. MVs are usually defined with a grouping function, or joins with many tables. In that case, you can't alter the column. Here is an example:<br /><br />SQL> create table src1 (col1 number, col2 number);<br /><br />Table created.<br /><br />SQL> alter table src1 add constraint pk_src1 primary key (col1);<br /><br />Table altered.<br /><br />SQL> create materialized view mv1 <br /> 2 refresh complete on demand<br /> 3 as<br /> 4 select col1, count(1) cnt from src1 group by col1;<br /><br />Materialized view created.<br /><br />SQL> alter table mv1 modify (col1 number(2));<br />alter table mv1 modify (col1 number(2))<br /> *<br />ERROR at line 1:<br />ORA-30556: functional index is defined on the column to be modified<br /><br />The error is a little strange; but point is that it can't be.<br /><br />Hope this helps.Arup Nandahttps://www.blogger.com/profile/03392706779349258765noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-58999914651312823282010-02-10T12:12:41.592-05:002010-02-10T12:12:41.592-05:00along with other ways to keep your data safe, you ...along with other ways to keep your data safe, you may also try the <a href="http://www.recoverytoolbox.com/how_to_recover_corrupted_system_database_in_sql_2000.html" rel="nofollow">sql server 2000 recovery</a> program, the service of data recovery prevents the corruption of specified filesUnknownhttps://www.blogger.com/profile/01695087703725036923noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-47480876003832212022009-09-12T21:33:49.318-04:002009-09-12T21:33:49.318-04:00Hi Arup,
I am facing a challenge while creating a...Hi Arup,<br /><br />I am facing a challenge while creating a Materialized view from a table, which is of close 60 GB. The source table has a CLOB column. The source table belongs to a diffrent application, I got only read only access to the same. The source table has a primary key. If follow the usual approach of createing mv ( i.e. CREATE MATERIALIZED VIEW ABC .... WITH PRIMARY KEY ...AS SELECT * FROM ABC@DBLINK ) . It never completes even after running for 25-30days. It is erroring out saying "Snashot too OLD" . Any suggestion how to proceed here. Thanks in advance . PratapUnknownhttps://www.blogger.com/profile/18063408679301871221noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-23143370491679409192009-08-20T04:59:32.419-04:002009-08-20T04:59:32.419-04:00Sorry, I think this tip is not valid. Regardless o...Sorry, I think this tip is not valid. Regardless of whether or not an MV is built on a prebuilt table, you can always alter the underlying table.<br /><br />I proved it by:<br />1. create source table xxx, add index, primary key and MV log<br />2. create MV yyy refresh fast on demand with primary key as select * from xxx<br />3. insert into xxx<br />4. dbms_snapshot.refresh(yyy,'FAST')<br />5. alter table xxx modify a column<br />6. alter table yyy modify same column<br />7. insert into xxx<br />8. dbms_snapshot.refresh(yyy,'FAST')<br /><br />Works perfectlyJohnnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-2903996270048770312009-08-06T07:47:39.142-04:002009-08-06T07:47:39.142-04:00I also noticed that when you do complete refresh, ...I also noticed that when you do complete refresh, I see Oracle is issuing a delete and then an insert. This is not helping us as delete preserves the high water mark causing it to slowdown over time. Besides it is also generating huge redo. Is there a way to make it issue a truncate ?Unknownhttps://www.blogger.com/profile/13712357331578522843noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-78742127391096605242009-07-16T16:48:26.914-04:002009-07-16T16:48:26.914-04:00Arup,
Thanks.
I got another question. I wonder ...Arup,<br /><br />Thanks. <br /><br />I got another question. I wonder do you have any tip about offline instantiation of a large prebuilt-table based MV. That means the master table should be always available for user ( user may do DMLs during exp/imp for example) and it is so big that online complete refresh is not an option.<br /><br />The Oracle <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96568/rarmanmv.htm#12817" rel="nofollow"> doc </a> is not clear to me at all.<br /><br />- Denisydshttps://www.blogger.com/profile/15884036034395020134noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-77441016028822687462009-07-10T20:27:44.287-04:002009-07-10T20:27:44.287-04:00Denis - yes, that is exactly the approach Oracle u...Denis - yes, that is exactly the approach Oracle uses for the dbms_redefinition package. So, yours is not only feasible, it's sort of tested as well.Arup Nandahttps://www.blogger.com/profile/03392706779349258765noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-9048144633577748642009-07-10T14:01:09.283-04:002009-07-10T14:01:09.283-04:00I am curious about prebuilt option for a while. Yo...I am curious about prebuilt option for a while. Your post and some comments enlightened me. Recently, We have a task to build databases which will contain subset data of exsiting databases. I am thinking if using MV approach is feasible. I can set up MV and refresh them from time to time. At cutover, I can convert MV to normal tables. Does it sound feasible?ydshttps://www.blogger.com/profile/15884036034395020134noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-23267680665124703212009-07-06T03:29:34.493-04:002009-07-06T03:29:34.493-04:00Excellent article and great hint. I can add that a...Excellent article and great hint. I can add that also privileges seems to be bound with table segment and they are preserved when dropping mview.rems@wp.plnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-44066132194005896892009-07-02T13:53:22.687-04:002009-07-02T13:53:22.687-04:00True. The indexes, triggers, constraints - all con...True. The indexes, triggers, constraints - all continue to exist happily even after the MV is dropped. Yet another reason for using the PREBUILT clause while creatng MVs.Arup Nandahttps://www.blogger.com/profile/03392706779349258765noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-86253120785322253912009-07-02T13:12:38.622-04:002009-07-02T13:12:38.622-04:00Arup:
use
drop materialized view mv1 preserve tab...Arup:<br />use <br />drop materialized view mv1 preserve table will convert MV to regular table and also preserve all indexes and triggers.<br /><br />Johnsonjzhttps://www.blogger.com/profile/05779956556367834767noreply@blogger.comtag:blogger.com,1999:blog-4096575.post-10936383803730473722009-03-28T22:06:00.000-04:002009-03-28T22:06:00.000-04:00Hi Arup -This article was really helpful for me.I ...Hi Arup -<BR/><BR/>This article was really helpful for me.<BR/><BR/>I have been a reader of all your posts and got chance to talk to you once as well.<BR/><BR/>Can you please share what makes you motivated to keep testing/practicing<BR/>and how you do manage your professional/personal life along with writing such a good articles.<BR/><BR/>Obviously, all these things really required time/testing.<BR/><BR/><BR/>Regards,Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4096575.post-17942638024898535012009-02-10T14:55:00.000-05:002009-02-10T14:55:00.000-05:00Excellent article Arup. Thanks and this will be ve...Excellent article Arup. Thanks and this will be very helpful.Anonymousnoreply@blogger.com