Script to create grant commands for objects datapump does not extract

The following script will create a list of grant commands to re-create explicit object priveileges that will not be extracted by datapump (as they relate to internal tables). These can then be applied to the environment being imported into.

select 'grant ' || PRIVILEGE || ' on ' || GRANTOR || '.' || TABLE_NAME ||
       ' to ' || GRANTEE || ' ;'
  from dba_tab_privs
 where grantor in
       (select name from sys.ku_noexp_tab where obj_type = 'OBJECT_GRANT')
   and grantee not in (select role from dba_roles)
   and grantee not in (select user_name from sys.default_pwd$)
   and grantee <> 'PUBLIC'
This script can then be cut and pasted into the new environment to recreate any of these missing rights.

The output is of the format shown below which can be spooled to a file or directly cut and pasted.


Post a Comment