How To Get an Existing Oracle User’s Create Script

dbtut March 14, 2019 ORACLE

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.

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

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:

-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>

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 6 13:44:25 2017

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>

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

How To Get an Existing Oracle User’s Create Script

About dbtut

Leave a Reply Cancel reply