dbms_metadata.get_ddl user

Extract CREATE USER DDL

It is a DBA task to create users or migrate users from one database environment to another.

Let’s use DBMS_METADATA.GET_DDL package to extract CREATE USER DDL.

SQL> select dbms_metadata.get_ddl

  2   ('USER',username)

  3   as col1

  4  from dba_users

  5  where username

  6   = 'SCOTT';

COL1

-----------------------------------

CREATE USER "SCOTT" IDENTIFIED BY

VALUES

'S:6BA47D5BAA9A46E9A3E9073AA77F486B

01E2AF0AA28D98515B969693B8AE;191F2D

E583F11B20'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP";

Let’s get the system grants and object grants using function GET_GRANTED_DLL for this user SCOTT.

SQL> select dbms_metadata.get_granted_ddl

  2   ('SYSTEM_GRANT',username)

  3  as col1

  4  from dba_users

  5  where username

  6   = 'SCOTT';

COL1

-----------------------------------

GRANT SELECT ANY TABLE TO "SCOTT";

GRANT UNLIMITED TABLESPACE TO

"SCOTT";

GRANT CREATE SESSION TO "SCOTT";

Changing the value from SYSTEM_GRANT to OBJECT_GRANT, and running the same SQL, gives the following object grants given to SCOTT.

SQL> select dbms_metadata.get_granted_ddl

  2   ('OBJECT_GRANT',username)

  3  as col1

  4  from dba_users

  5  where username

  6   = 'SCOTT';

COL1

-----------------------------------

GRANT ALTER, DELETE, INDEX, INSERT,

SELECT, UPDATE, REFERENCES, ON

COMMIT REFRESH, QUERY REWRITE,

DEBUG, FLASHBACK ON "BHAVESH"."T11"

TO "SCOTT";

GRANT DELETE, INSERT, SELECT,

UPDATE ON "BHAVESH"."T2" TO

"SCOTT";

GRANT DELETE, INSERT, SELECT,

UPDATE ON "BHAVESH"."T3" TO

"SCOTT";

GRANT DELETE, INSERT, SELECT,

UPDATE ON "BHAVESH"."T4" TO

"SCOTT";

The following SQL gives the roles granted to username SCOTT.

SQL> select dbms_metadata.get_granted_ddl

  2   ('ROLE_GRANT',username)

  3  as col1

  4  from dba_users

  5  where username

  6   = 'SCOTT';

COL1

-----------------------------------

GRANT "CONNECT" TO "SCOTT";

GRANT "RESOURCE" TO "SCOTT";

GRANT "DEV_ROLE" TO "SCOTT";

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

If you are interested in the book "Oracle SQL Scipting". here is the link.
http://www.amazon.com/Oracle-SQL-Scripting-ebook/dp/B00E2T1DVA/ref=sr_1_1?ie=UTF8&qid=1374436123&sr=8-1&keywords=oracle+sql+scripting

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++