How to extract an user definition in Oracle
Use this script to extract/display a specific user DDL(Data Definition), you must provide the script with a value for @user_ddl paramater.
Use with care and always test before you use.
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
to_clob('/* Start profile creation script in case they are missing') AS ddl
FROM
dba_users u
WHERE
u.username = :v_username
AND u.profile < 'DEFAULT'
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'
UNION ALL
SELECT
to_clob('End profile creation script */') AS ddl
FROM
dba_users u
WHERE
u.username = :v_username
AND u.profile < 'DEFAULT'
AND rownum = 1 / SET linesize 80 pagesize 14 feedback
ON
trimspool
ON
verify
ON
