No question at this time
DBA Top 10
1 M. Cadot 15900
2 A. Kavsek 11800
3 B. Vroman 10800
4 M. Hidayathullah ... 7600
5 T. Boles 4800
6 P. Wisse 4000
7 J. Schnackenberg 3000
8 K. Van Nieuwenhov... 2100
9 A. Hudspith 700
9 T. P 700
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48114
Total active users1700
Act. users last 24h12
Act. users last hour1
Registered user hits last week507
Registered user hits last month1127
Go up

views creation using impdp
Next thread: Facing issue during applying PSU Patch July-2017 patch 26609783
Prev thread: split ASM diskgroup into two part

Message Score Author Date
Hi Guys I have production environment that run ...... Tso P Jan 11, 2018, 11:56
Dear Tso, if there are dependent object it will...... Mirza Hidayathullah Baig Jan 11, 2018, 13:47
Hello Tso, <<<I>when I do the impdp my import f...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Jan 11, 2018, 15:59
Indeed:   http://www.dba-village.com/village/dvp_...... Bruno Vroman Jan 11, 2018, 16:00
Thanks Bruno I have a consistent set but the vi...... Tso P Jan 15, 2018, 10:11
I checked Oracle documentation: If the view def...... Tso P Jan 15, 2018, 10:26
Hello Tso, as a workaround you have to re-run t...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Jan 15, 2018, 14:17

Follow up by mail Click here


Subject: views creation using impdp
Author: Tso P, South Africa
Date: Jan 11, 2018, 11:56, 6 days ago
Os info: solaris 10
Oracle info: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Message: Hi Guys

I have production environment that run Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production.

Now, I trying to push data from prod to datawarehouse environment that run Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production.

I used datapump expdp with version parameter, now when I do the impdp my import fails on the views and when I check the issue is with how the views are created.

After the import i get the errors ORA-00942: table or view does not exist, upon inspection I realized the views have constraint and the order in which the views are created causes these errors.

For example the first view to be created have a foreign key that reference the next view(s) to be created...as such that the view fail to be created.

How I can I manage this issue?

Thanks in advance...

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: views creation using impdp
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Jan 11, 2018, 13:47, 6 days ago
Message: Dear Tso,

if there are dependent object it will look for that reference object and failed with error, or created with compilation error, sometime this error we receive if we dont have grants, please compare the objects between source and target to narrow down the issue.

Regards
Mirza
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: views creation using impdp
Author: Bruno Vroman, Belgium
Date: Jan 11, 2018, 15:59, 6 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Tso,

<<when I do the impdp my import fails on the views>>

if you have exported and imported a "consistent" set, everything will be fixed at the end of the impdp (datapump will create the view with option "FORCE" so the view is created even if it uses another view that doesn't exist yet).
If you still have invalid objects at the end of the impdp, first try to recompile all invalid objects, for example with "?/rdbms/admin/utlprp.sql"
If there are objects that can't be recompiled, check the errors to react accordingly.
It can be for example that the same objects are also invalid in the source database (and can't be recompiled), then we can't expect a miracle; or it can be that "some pieces are missing" on the target side, like non-existing table used by a view, or existing table but with missing privilege... than this can be fixed by granting the missing privilege or by adding the missing table in the set of objects exported (or by removing the view of the set)

I think that we already had a similar discussion some time ago...

Best regards,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: views creation using impdp
Author: Bruno Vroman, Belgium
Date: Jan 11, 2018, 16:00, 6 days ago
Message: Indeed:
  http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=89167#245221
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: views creation using impdp
Author: Tso P, South Africa
Date: Jan 15, 2018, 10:11, 2 days ago
Message: Thanks Bruno

I have a consistent set but the view creation fails because some views have referential integrity constraints, that reference views that are not yet created.

As such those views are not created even if we have FORCE.

Thanks for the reply...

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: views creation using impdp
Author: Tso P, South Africa
Date: Jan 15, 2018, 10:26, 2 days ago
Message: I checked Oracle documentation:

If the view definition contains any constraints, CREATE VIEW ... FORCE fails if the base table does not exist or the referenced object type does not exist. CREATE VIEW ... FORCE also fails if the view definition names a constraint that does not exist.

This is exactly what I get...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: views creation using impdp
Author: Bruno Vroman, Belgium
Date: Jan 15, 2018, 14:17, 2 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Tso,

as a workaround you have to re-run the CREATE VIEW statements once the impdp session is complete (you can extract the .sql file from the dmp file), and worst case is to create one view per run of the sql file, best case is to fix all the issues in one run (there is something abnormal if one run doesn't create at least one of the missing views)

Good luck,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here