DB version: 11.1
OS : RHEL 5.10
I have around 100 users in a DB. I want to export the CREATE USER DDLs and the privileges and roles granted to these users.
SQLFILE is not an option with expdp. So, I tried to export to a dumpfile as shown below. Both of my below attempts failed.
## Attempt1 using INCLUDE=USER
$ expdp "'/ as sysdba'" DUMPFILE=prod_users.dmp INCLUDE = USER directory=DATA_PUMP nologfile=yes
Export: Release 11.1.0.7.0 - 64bit Production on Friday, 02 December, 2016 12:25:38
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DUMPFILE=prod_users.dmp INCLUDE = USER directory=DATA_PUMP nologfile=yes
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39168: Object path USER was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_SCHEMA_01" completed with 2 error(s) at 12:25:47
## Attempt2 using INCLUDE=CREATE_USER
$ expdp "'/ as sysdba'" DUMPFILE=prod_users.dmp INCLUDE=CREATE_USER directory=DATA_PUMP nologfile=yes
Export: Release 11.1.0.7.0 - 64bit Production on Friday, 02 December, 2016 12:26:21
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39041: Filter "INCLUDE" either identifies all object types or no object types.
I can generate the CREATE USER commands using the below query. But, generating privileges and roles granted to these users can be tedious. Hence thought of datapump
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) DDL FROM DBA_USERS