Clone Oracle User

Needed to clone an Oracle user from one database to another database. Turns out this was not as easy as I had planned. We will be using the DBMS_METADATA function to get data to create the new user.

On the Source server:

select DBMS_METADATA.GET_DDL('USER','your user') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','your user') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','your user') FROM DUAL; 
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','your user') FROM DUAL; 
SELECT DBMS_METADATA.GET_granted_DDL(‘TABLESPACE_QUOTA’, ‘your user’) FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL ('DEFAULT_ROLE', 'your user') from DUAL;

Then execute the output from this command on the target server

CREATE USER "your user" IDENTIFIED BY VALUES 'encoded password' 
DEFAULT TABLESPACE "your tablespace" TEMPORARY TABLESPACE "TEMP" 

GRANT "CONNECT" TO "your user"

GRANT SELECT ON "your object" TO "your user";
GRANT SELECT ON "your object" TO "your user";
... //rest of your objects