How to Generate user ddl in Oracle

How to Generate user ddl in Oracle

by

Generate user ddl in Oracle: In this article we will discuss about how to Generate user ddl in Oracle. You can use below scripts to generate DDL statements for a user or multiple users with their roles, system and object privileges.

To generate ddl for a specified user:

In this case we generate ddl for ‘scott’ user with their roles, system and object privileges.

SQL>
SQL> set heading off;
SQL> set echo off;
SQL> set lines 1000 pages 4000
SQL> set long 999999
SQL> spool ddl_scott_user.sql
SQL> SELECT dbms_metadata.get_ddl('USER',UPPER('&&uname')) FROM dual;
Enter value for uname: scott
old   1: SELECT dbms_metadata.get_ddl('USER',UPPER('&&uname')) FROM dual
new   1: SELECT dbms_metadata.get_ddl('USER',UPPER('scott')) FROM dual


   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:B3946CE220EC5F68F70ACB35B66802FBC
27AAB728660736A12DC4F7F87A5;T:8A7E53DEAF11B8D200E61D980986BED85DCC44F47EF0E4DD57
73904984EDA1F6CA48D760D75DDFA40206D421D50C8F2D610EFE6C38974BA6EBECDD40CF36E5301D
465ACE82FEAAD1260578D5F608C53B'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


SQL>
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',UPPER('&&uname')) from dual;
old   1: SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',UPPER('&&uname')) from dual
new   1: SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',UPPER('scott')) from dual


  GRANT UNLIMITED TABLESPACE TO "SCOTT"


SQL>
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',UPPER('&&uname')) from dual;
old   1: SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',UPPER('&&uname')) from dual
new   1: SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',UPPER('scott')) from dual


   GRANT "CONNECT" TO "SCOTT"
   GRANT "RESOURCE" TO "SCOTT"


SQL>
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',UPPER('&&uname')) from dual;
old   1: SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',UPPER('&&uname')) from dual
new   1: SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',UPPER('scott')) from dual


  GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "SCOTT"


SQL>
SQL> spool off;
SQL>

To generate ddl for multiple users:

In this case we generate ddl for ‘scott’ and ‘hr’ users.

SQL>
SQL> set heading off;
SQL> set echo off;
SQL> set lines 1000 pages 4000
SQL> set long 999999
SQL> spool ddl_multiple_user.sql
SQL> select dbms_metadata.get_ddl('USER',U.username) from dba_users U where username in ('SCOTT','HR');


   CREATE USER "HR" IDENTIFIED BY VALUES 'S:286220D1B0C2427EC2A75BABDEAEF756C069
E011527B5815E6D96AA04805;T:E25EE257AC8CAD798C5179DC8EB5ED27A0C7D2B87B1A8EAEB322F
87FEEC1DB6220BA942B60C754886FEACA3A76BCB26C93464F8130CD1A8AA1F8825BA9451EDF0DB15
B805BEC7793E1EDACEAF76A4A33'
      DEFAULT TABLESPACE "SYSAUX"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK


   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:B3946CE220EC5F68F70ACB35B66802FBC
27AAB728660736A12DC4F7F87A5;T:8A7E53DEAF11B8D200E61D980986BED85DCC44F47EF0E4DD57
73904984EDA1F6CA48D760D75DDFA40206D421D50C8F2D610EFE6C38974BA6EBECDD40CF36E5301D
465ACE82FEAAD1260578D5F608C53B'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


SQL>

To generate ddl for all users:

Process-1:

By this below query you can generate all users ddl in a spool file.

SQL>
SQL> set heading off;
SQL> set echo off;
SQL> set lines 1000 pages 4000
SQL> set long 999999
SQL> spool ddl_all_user.sql
SQL> select dbms_metadata.get_ddl('USER',U.username) from dba_users U;
SQL>
SQL> spool off;

Process-2:

By this query we can get the DDL for all users:

SQL>
SQL> set heading off;
SQL> set echo off;
SQL> set lines 1000 pages 4000
SQL> set long 999999
SQL> spool ddl_all_user.sql
SQL> select 'select dbms_metadata.get_ddl(''USER'',''' || USERNAME || ''') from dual;' from dba_users;

select dbms_metadata.get_ddl('USER','SYS') from dual;
select dbms_metadata.get_ddl('USER','SYSTEM') from dual;
select dbms_metadata.get_ddl('USER','XS$NULL') from dual;
select dbms_metadata.get_ddl('USER','OJVMSYS') from dual;
select dbms_metadata.get_ddl('USER','LBACSYS') from dual;
select dbms_metadata.get_ddl('USER','OUTLN') from dual;
select dbms_metadata.get_ddl('USER','SYS$UMF') from dual;
select dbms_metadata.get_ddl('USER','DBSNMP') from dual;
select dbms_metadata.get_ddl('USER','APPQOSSYS') from dual;
select dbms_metadata.get_ddl('USER','DBSFWUSER') from dual;
select dbms_metadata.get_ddl('USER','GGSYS') from dual;
select dbms_metadata.get_ddl('USER','ANONYMOUS') from dual;
select dbms_metadata.get_ddl('USER','HR') from dual;
select dbms_metadata.get_ddl('USER','CTXSYS') from dual;
select dbms_metadata.get_ddl('USER','SI_INFORMTN_SCHEMA') from dual;
select dbms_metadata.get_ddl('USER','DVSYS') from dual;
select dbms_metadata.get_ddl('USER','DVF') from dual;
select dbms_metadata.get_ddl('USER','GSMADMIN_INTERNAL') from dual;
select dbms_metadata.get_ddl('USER','ORDPLUGINS') from dual;
select dbms_metadata.get_ddl('USER','MDSYS') from dual;
select dbms_metadata.get_ddl('USER','OLAPSYS') from dual;
select dbms_metadata.get_ddl('USER','ORDDATA') from dual;
select dbms_metadata.get_ddl('USER','XDB') from dual;
select dbms_metadata.get_ddl('USER','WMSYS') from dual;
select dbms_metadata.get_ddl('USER','ORDSYS') from dual;
select dbms_metadata.get_ddl('USER','GSMCATUSER') from dual;
select dbms_metadata.get_ddl('USER','MDDATA') from dual;
select dbms_metadata.get_ddl('USER','SYSBACKUP') from dual;
select dbms_metadata.get_ddl('USER','REMOTE_SCHEDULER_AGENT') from dual;
select dbms_metadata.get_ddl('USER','GSMUSER') from dual;
select dbms_metadata.get_ddl('USER','SYSRAC') from dual;
select dbms_metadata.get_ddl('USER','AUDSYS') from dual;
select dbms_metadata.get_ddl('USER','DIP') from dual;
select dbms_metadata.get_ddl('USER','SYSKM') from dual;
select dbms_metadata.get_ddl('USER','ORACLE_OCM') from dual;
select dbms_metadata.get_ddl('USER','SCOTT') from dual;
select dbms_metadata.get_ddl('USER','SYSDG') from dual;
select dbms_metadata.get_ddl('USER','SPATIAL_CSW_ADMIN_USR') from dual;

38 rows selected.

SQL>

After getting output you need to run this queries to get ddl for user which you needed. Example is given below,

SQL>
SQL> select dbms_metadata.get_ddl('USER','HR') from dual;


   CREATE USER "HR" IDENTIFIED BY VALUES 'S:286220D1B0C2427EC2A75BABDEAEF756C069
E011527B5815E6D96AA04805;T:E25EE257AC8CAD798C5179DC8EB5ED27A0C7D2B87B1A8EAEB322F
87FEEC1DB6220BA942B60C754886FEACA3A76BCB26C93464F8130CD1A8AA1F8825BA9451EDF0DB15
B805BEC7793E1EDACEAF76A4A33'
      DEFAULT TABLESPACE "SYSAUX"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK


SQL>
SQL> select dbms_metadata.get_ddl('USER','SCOTT') from dual;


   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:B3946CE220EC5F68F70ACB35B66802FBC
27AAB728660736A12DC4F7F87A5;T:8A7E53DEAF11B8D200E61D980986BED85DCC44F47EF0E4DD57
73904984EDA1F6CA48D760D75DDFA40206D421D50C8F2D610EFE6C38974BA6EBECDD40CF36E5301D
465ACE82FEAAD1260578D5F608C53B'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


SQL>

Retrieving Metadata :

If you are retrieving metadata, you can specify:

  • The kind of object to be retrieved. This can be either a particular object type (such as a table, index, or procedure) or a heterogeneous collection of object types that form a logical unit (such as a database export or schema export).
  • Optional selection criteria, such as owner or name.
  • Parse items (attributes of the returned objects to be parsed and returned separately).

How to generate tablespace ddl in Oracle

How to generate table ddl in Oracle

I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.

2 thoughts on “How to Generate user ddl in Oracle”

  1. Pingback: How to generate tablespace ddl in Oracle - orakldba
  2. Pingback: How to Get ddl of any Object in Oracle - orakldba - ORACLE Tutorial

Leave a Comment