asked on

Grants_failure_during_import...

I have two databases with 3 schemas in each and datbase links.

I using expdp and impdp to refresh TEST with production copy.

The problem I am having is with GRANTS.

The first schema in DB1 has grants to schema 1 in DB2 and vice versa.

If the user does not exist the grants will fail during import.

How do you get around this?

Do you have to create a dump for all grants from some views for each database and
run that after the import for database schemas is completed?

SOLUTION

Wasim Akram Shaik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

I cannot find anything explicitly on this with datapump but in the classic exp/imp there has always been race conditions when something imported has a dependency on something else that hasn't been imported yet.

Looks like datapump still has this.

The work-around in the past was import the base objects only then run another import to get the extras.

In this case: import first without grants then import grants only.

sam15

ASKER

so basically you are suggesting to do two imports: first with GRANTS=N and second for grants only.

Does IMPDP support this syntax?

Interesting link on this question too

https://www.experts-exchange.com/questions/24985395/grants-after-schema-import.html

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sam15

ASKER

Yes but on the second import I do not think you can take a dump file and only import grants without anything else?

Are you thinking of doing a normal import and let it all fail since objects were already created in first import?

slightwv (䄆 Netminder)

I believe you can do a grants only import. Then again, I've never tried...

I would not do a normal second import and 'let it fail' because it could double up some data.

sam15

ASKER

it seems there is an INCLUDE and EXCLUDE option

INCLUDE=grant will only import grants from the dump file.

slightwv (䄆 Netminder)

That is how I read it. I only posted the EXCLUDE for the first import. Sorry for not posting the INCLUDE link as well for the second.

I took your comment above:

and second for grants only.

to mean you already knew you could do grants only.