How To Get User Creation Script In Oracle

Melek Durdu

2 years ago

In today’s article, we will look for an answer to the question of How To Get User Creation Script In Oracle.

We can get the script of a previously created user in the Oracle database as follows.

SQL > set long 1000000
SQL > spool userScript.sql;
SQL > SELECT DBMS_METADATA.GET_DDL('USER','USERNAME') FROM dual;
SQL > SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','USERNAME') FROM dual;
SQL > SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','USERNAME') FROM dual;
SQL > SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','USERNAME') FROM dual;
SQL > spool off;

SQL > set long 1000000

SQL > spool userScript.sql;

SQL > SELECT DBMS_METADATA.GET_DDL('USER','USERNAME') FROM dual;

SQL > SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','USERNAME') FROM dual;

SQL > SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','USERNAME') FROM dual;

SQL > SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','USERNAME') FROM dual;

SQL > spool off;