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:
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 scriptsSELECT '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;
