Generate DDL scripts using DBMS_METADATA.GET_DDL

 

Use DBMS_METADATA.GET_METADATA to copy the user scripts from one database to another database.
First, we will take the DDL scripts of the tablespace using the following command:

SQL> set head off echo off
SQL> select 'select dbms_metadata.get_ddl(''TABLESPACE'','''||  tablespace_name   || ''') from dual;' from dba_tablespaces;
select dbms_metadata.get_ddl('TABLESPACE','SYSTEM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','SYSAUX') from dual;
select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') from dual;
select dbms_metadata.get_ddl('TABLESPACE','TEMP') from dual;
select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
select dbms_metadata.get_ddl('TABLESPACE','TOOLS') from dual;
select dbms_metadata.get_ddl('TABLESPACE','RDPSDT01') from dual;

SQL> select dbms_metadata.get_ddl('TABLESPACE','RDPSDT01') from dual;  
  CREATE TABLESPACE "RDPSDT01" DATAFILE 
  '/apps/oradata/RAIMUMDI2/RDPSDT01/RDPSDT01_01.dbf' SIZE 4294967296, 
  '/apps/oradata/RAIMUMDI2/RDPSDT01/RDPSDT01_02.dbf' SIZE 4294967296   
  AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M   
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192   
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  
  SEGMENT SPACE MANAGEMENT AUTO

Now, Get the script for the user creation also for the roles & object grant & system grants provided to user.

set head off
set pages 0
set long 9999999
 15:26:43 SYS > select  dbms_metadata.get_ddl('USER', 'OPS$RPO') || '/' usercreate from DUAL;
 USERCREATE
----------------------------------------------------------------------
 CREATE USER "OPS$RPO" IDENTIFIED BY VALUES 'S:C952B1BD452B5E63692668680C22A8F
7C8E9F26557E808DADB396E85ADBF;01F95BD48BDDF041'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE/

Get all their roles and grants as well

15:27:05 SYS > SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','OPS$RPO') FROM DUAL;
 DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','OPS$RPO')
--------------------------------------------------------------------------------
  GRANT "CONNECT" TO "OPS$RPO"
  GRANT "RESOURCE" TO "OPS$RPO"
  GRANT "DBA" TO "OPS$RPO"
  GRANT "IVISION_USER" TO "OPS$RPO"

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','ADMIN') FROM DUAL;
 15:27:34 SYS > SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','OPS$RPO') FROM DUAL;
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','OPS$RPO')
--------------------------------------------------------------------------------
 GRANT EXECUTE ANY PROCEDURE TO "OPS$RPO"
 GRANT ALTER ANY PROCEDURE TO "OPS$RPO"
 GRANT CREATE PROCEDURE TO "OPS$RPO"
 GRANT GRANT ANY ROLE TO "OPS$RPO"
 GRANT DROP PUBLIC SYNONYM TO "OPS$RPO"
 GRANT CREATE PUBLIC SYNONYM TO "OPS$RPO"
 GRANT SELECT ANY TABLE TO "OPS$RPO"
 GRANT CREATE TABLE TO "OPS$RPO"
 GRANT DROP USER TO "OPS$RPO"
 GRANT ALTER USER TO "OPS$RPO"
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','OPS$RPO')
--------------------------------------------------------------------------------
 GRANT CREATE USER TO "OPS$RPO"
 GRANT UNLIMITED TABLESPACE TO "OPS$RPO"
 GRANT CREATE SESSION TO "OPS$RPO"

15:27:51 SYS > SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','OPS$RPO') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','OPS$RPO')
--------------------------------------------------------------------------------
 GRANT EXECUTE, DEBUG ON "SYS"."DBMS_PIPE" TO "OPS$RPO"
 GRANT EXECUTE ON "OPS$VTF"."DBK_C_ERR" TO "OPS$RPO"

You can use the following scripts to get the DDL scripts

SELECT 'SELECT DBMS_METADATA.GET_DDL(''TABLESPACE'','''||  TABLESPACE_NAME || ''') FROM DUAL;' FROM DBA_TABLESPACES;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','ADMIN_TBS')  FROM DUAL;
SELECT 'SELECT DBMS_METADATA.GET_DDL(''USER'','''||  USERNAME || ''') FROM DUAL;' FROM DBA_USERS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','ADMIN') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','ADMIN') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','ADMIN') FROM DUAL;
SELECT 'SELECT DBMS_METADATA.GET_DDL(''TABLE'','''||  TABLE_NAME|| ''') FROM DUAL;' FROM DBA_TABLES;
SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM all_db_links a;