oracle-databaseimpdpexpdporacle-dump

How to export users in Oracle with its roles and system privileges using expdp?


I am trying to export a schema/user in Oracle with its roles and system privileges. I don't want to export any data or any table. I have tried to export users using the following command.

expdp system/system@gisdblocal include=user DIRECTORY = TestBack  
logfile=test12.log DUMPFILE=test12.dmp SCHEMAS=test_replication

When I import this in other database or in the same database with a different name i.e

impdp system/system@gisdblocal DIRECTORY = TestBack  DUMPFILE = test12.dmp   
SCHEMAS = test_replication REMAP_SCHEMA = 
test_replication:test_replication_copy 

the user or schema is created but it has not been granted any role or system privileges.

I am doing this because I have created a backup of a schema using the user that did not have the required rights DATAPUMP_IMP_FULL_DATABASE or DATAPUMP_EXP_FULL_DATABASE. When I restore that backup in another database, it says the user does not exist. Therefore, I am thinking to create a user with the same privileges first and then restore the backup.


Solution

  • Using SQL...

    SELECT dbms_metadata.get_ddl('USER', :name)
      FROM dual
    UNION ALL
    SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', grantee)
      FROM dba_role_privs
     WHERE grantee = :name
       AND ROWNUM = 1
    UNION ALL
    SELECT dbms_metadata.get_granted_ddl('DEFAULT_ROLE', grantee)
      FROM dba_role_privs
     WHERE grantee = :name
       AND ROWNUM = 1
    UNION ALL
    SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', grantee)
      FROM dba_sys_privs          sp,
           system_privilege_map   spm
     WHERE sp.grantee = :name
       AND sp.privilege = spm.name
       AND spm.property <> 1
       AND ROWNUM = 1
    UNION ALL
    SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT', grantee)
      FROM dba_tab_privs
     WHERE grantee = :name
       AND ROWNUM = 1
    UNION ALL
    SELECT dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', username)
      FROM dba_ts_quotas
     WHERE username = :name
       AND ROWNUM = 1
    

    :name being...a bind variable for the USER you want to re-create.