No question at this time
DBA Top 10
1 M. Cadot 5400
2 P. Wisse 1300
3 P. Tsongayinwe 1100
4 B. Vroman 500
4 J. Schnackenberg 500
6 M. Pagac 400
7 A. Deledda 200
7 D. Walgude 200
7 B. M 200
10 J. Alcroft 100
10 G. Trivedi 100
10 A. Hudspith 100
10 V. Sharma 100
10 L. Ywema 100
About
DBA-Village
Advertise here
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48742
Total active users1337
Act. users last 24h1
Act. users last hour0
Registered user hits last week108
Registered user hits last month471
Go up

Why would materialized views, refresh group,view logs be excluded from export dump ?
Next thread: Would it be useful to enable Hyper-Threading in Oracle 12.1.0.2 on Linux
Prev thread: Difference of sql execution time in awr and dba_hist_sqlstat.

Message Score Author Date
Hi all, I just took over a db, and there is an ...... sze jie koh Sep 04, 2015, 11:16
Hi Noob, Size. Size matters . . . Regards, P...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Philip Wisse Sep 04, 2015, 11:35
Bugs. Bugs also matters :) You can spend the rest...... Score: 900 PtsScore: 900 PtsScore: 900 PtsScore: 900 PtsScore: 900 Pts Ales Kavsek Sep 04, 2015, 13:05
Dear Phillip, Ales, Thanks for your "twin" repl...... sze jie koh Sep 06, 2015, 16:10
Hi Noob, Exclusion of MV is a deliberate action...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Philip Wisse Sep 07, 2015, 08:48
Thank you all for your replies!... sze jie koh Sep 14, 2015, 03:11

Follow up by mail Click here

Subject: Why would materialized views, refresh group,view logs be excluded from export dump ?
Author: sze jie koh, Singapore
Date: Sep 04, 2015, 11:16, 2829 days ago
Os info: Linux 64
Oracle info: 11gr2
Message: Hi all,

I just took over a db, and there is an expdp done monthly which exclude the following
- materialized view
- materialized view logs
- refresh group

This expdp dump is then use to import into test and trial environment.

After the import, the previous dba will run manual script to recreate the mviews, mview logs and groups.

I feel like including them in the expdp and impdp.

What could be the reason for exlucding them in the 1st place ?

Regards,
Noob


Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: Why would materialized views, refresh group,view logs be excluded from export dump ?
Author: Philip Wisse, Netherlands
Date: Sep 04, 2015, 11:35, 2829 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi Noob,

Size. Size matters . . .

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: Why would materialized views, refresh group,view logs be excluded from export dump ?
Author: Ales Kavsek, Slovenia
Date: Sep 04, 2015, 13:05, 2829 days ago
Score:   Score: 900 PtsScore: 900 PtsScore: 900 PtsScore: 900 PtsScore: 900 Pts
Message: Bugs. Bugs also matters :)
You can spend the rest of the day reading all the bugs related notes about to datapump and mviews....you can start with these...

Doc ID 1529844.1 "DataPump: Materialized Views Are Not Exported When Using Export Dump In 11.2.0.2"
Doc ID 1114344.1 "Materialized Views are Not In Export If Base Table Is IOT, Indexed Organized Table"
Doc ID 1909855.1 "DataPump Export (EXPDP) Errors ORA-39126 ORA-1427 Returns More Than One Row When Exporting Materialized Views"

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: Why would materialized views, refresh group,view logs be excluded from export dump ?
Author: sze jie koh, Singapore
Date: Sep 06, 2015, 16:10, 2827 days ago
Message: Dear Phillip, Ales,

Thanks for your "twin" reply. ;). I am quite sure i would have to exclude them out after seeing your replies.

Do you mind if i ask the following question below ->

Right now, the expdp is excluding the mviews, mvlogs, and refresh groups but after doing the impdp, it will use another script to recreate all the mviews,mvlogs,and refreshgroups.


q1) Bugs mentioned by Ales aside, if expdp dump size is also not a concern, will it be faster to recreate the mviews after the import or directly impdp the mviews in ?

q2) is there any other alternative way out, because everytime a new mview is created at the source db, i would have to manually add them to post script after the impdp and sometimes the dba ain't aware that new mviews are created.


Regards,
Noob
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: Why would materialized views, refresh group,view logs be excluded from export dump ?
Author: Philip Wisse, Netherlands
Date: Sep 07, 2015, 08:48, 2826 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message: Hi Noob,

Exclusion of MV is a deliberate action.
It will save time to export.
It will save space.
It will save time to transfer the export.
It will save time to import.
The other side of the medal:
It will cost time to retrieve MV metadata.
It will take some time to rebuild MV's and MV logs.

So: Time matters too.
How do you value your own time?

Another path would be to truncate MV's and MV logs before export.
Or save the scripts and drop them . . .

HTH, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: Why would materialized views, refresh group,view logs be excluded from export dump ?
Author: sze jie koh, Singapore
Date: Sep 14, 2015, 03:11, 2819 days ago
Message: Thank you all for your replies!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here