Oracle import / export Grant source database grantee does not exist in target database

Hi there,

today I was confronted with the following problem:

I had to export a schema from one database and import to another database. The schema contained grants granted to some user/grantee, who only existed in the source database. These grants should be replaces by another grantee for the target database. A normal export and import was failing with the following errors:

GRANT ON ... ON "SCHEMA"."TABLE" TO "GRANTEE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'GRANTEE' does not exist

A solution for this scenario is:

Do the normal import and ignore the errors. Then do the import again with the sqlfile parameter:

impdp system/peBa4ci9 schemas=... dumpfile=xxx.dmp sqlfile=getddl.sql

When you run this import command, it will not execute an import. Rather it will only create the sql file which will contain all the DDL statements that the import consists of.

Once you have the file, open it and search for the grants DDL's that affect the user/grantee you want to replace. All DDL's for this will be at the same place such as:

GRANT DEBUG ON "SCHEMA"."TABLE1" TO "GRANTEE";
GRANT QUERY REWRITE ON "SCHEMA"."TABLE2" TO "GRANTEE";
GRANT ON COMMIT REFRESH ON "SCHEMA"."TABLE3" TO "GRANTEE";
GRANT REFERENCES ON "SCHEMA"."TABLE4" TO "GRANTEE";
GRANT UPDATE ON "SCHEMA"."TABLE5" TO "GRANTEE";
GRANT SELECT ON "SCHEMA"."TABLE6" TO "GRANTEE";

Replace the GRANTEE with the one you want to have in the target database and run the GRANT commands again on the target database so that the GRANTS are set correctly. FINISH :)

ORA-30372: fine grain access policy conflicts with materialized view

Hello, I was getting the following error when creating and refreshing a materialized view:

ORA-30372 -  "fine grain access policy conflicts with materialized view"
*Cause:    A fine grain access control procedure has applied a non-null policy
           to the query for the materialized view.

This error occurs, when you are creating/refreshing a materialized view on the base of a remote table which is located on another database where Virtual Private Database (VPD) is enabled.

The simplest workaround is to use the "USING TRUSTED CONSTRAINTS" keyword in the syntax: