Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In this article, I will tell you to get the create script of an oracle user. First, we will create a file named user.sql that contains the below script. Then, using this sql file, we will get a user’s create script. We can then use this create script to create the user in another database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on column ddl format a1000 begin dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true); dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true); end; / variable v_username VARCHAR2(30); exec:v_username := upper('&1'); select dbms_metadata.get_ddl('USER', u.username) AS ddl from dba_users u where u.username = :v_username union all select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl from dba_ts_quotas tq where tq.username = :v_username and rownum = 1 union all select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl from dba_role_privs rp where rp.grantee = :v_username and rownum = 1 union all select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl from dba_sys_privs sp where sp.grantee = :v_username and rownum = 1 union all select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl from dba_tab_privs tp where tp.grantee = :v_username and rownum = 1 union all select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl from dba_role_privs rp where rp.grantee = :v_username and rp.default_role = 'YES' and rownum = 1 union all select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from dba_users u where u.username = :v_username and u.profile <> 'DEFAULT' / set linesize 80 pagesize 14 feedback on trimspool on verify on |
Use of the script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | -bash-4.3$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 6 13:44:25 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @user.sql SCOTT CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:268AB71B15071D81F19C6FC5041FA8F8E49397470FFE05458B8C90D9E7F8;F894844C34402B67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PASSWORD EXPIRE ACCOUNT LOCK; GRANT "CONNECT" TO "SCOTT"; GRANT "RESOURCE" TO "SCOTT"; GRANT UNLIMITED TABLESPACE TO "SCOTT"; ALTER USER "SCOTT" DEFAULT ROLE ALL; SQL> |
