set long 20000
set lines 200
set pages 666
col DBMS_METADATA.GET_DDL('USER',:NAME) format a90
variable name varchar2(50)
exec :name:='CON29';
SELECT dbms_metadata.get_ddl('USER', :name)
FROM dual
UNION ALL
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', grantee)
FROM dba_role_privs
WHERE grantee = :name
AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('DEFAULT_ROLE', grantee)
FROM dba_role_privs
WHERE grantee = :name
AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', grantee)
FROM dba_sys_privs sp,
system_privilege_map spm
WHERE sp.grantee = :name
AND sp.privilege = spm.name
AND spm.property <> 1
AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT', grantee)
FROM dba_tab_privs
WHERE grantee = :name
AND ROWNUM = 1
UNION ALL
SELECT dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', username)
FROM dba_ts_quotas
WHERE username = :name
AND ROWNUM = 1
SQL> /
DBMS_METADATA.GET_DDL('USER',:NAME)
------------------------------------------------------------------------------------------
CREATE USER "CON29" IDENTIFIED BY VALUES 'AbcDefGhi'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMPORARY"
.....etc
Run against the new database to create the user.
In AIX to run against multiple DBs e.g.
#!/usr/bin/env bash
set -A array XYZDBA
## Set the env b4 running this
for i in "${array[@]}"
do
exit | sqlplus -silent / as sysdba << EOF
set head on
set feedback off
set long 30000
set lines 220
set pages 666
## col DBMS_METADATA.GET_DDL('USER',:NAME) format a220
##
col DBMS_METADATA.GET_DDL('USER','XYZDBA') format a220
SELECT dbms_metadata.get_ddl('USER', '$i')
FROM dual......
( same script as above )
EOF
done
declare
data CLOB;
BEGIN
SELECT dbms_metadata.get_ddl('USER','SFS') into data from dual;
DBMS_XSLPROCESSOR.CLOB2FILE(data,'DIRECTORY**','create_user.sql');
END;
**DIRECTORY - change as approp. e.g. DATA_PUMP_DIR find from dba_directories
For the tablespaces for the above..
SQL> SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
SQL> SQL> 2 3 DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)
FROM dba_tablespaces
WHERE tablespace_name = DECODE(UPPER('&1'), 'ALL', tablespace_name, UPPER('&1'));
4 5 SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
SQL> SQL> 2 3 Enter value for 1: Enter value for 1: SQL>
SQL> /
Enter value for 1: NWLMEAVX
Enter value for 1: NWLMEAVX
CREATE TABLESPACE "NWLMEAVX" DATAFILE etc......
You will need to alter to suit when going from ASM to Unix datafiles or vice-versa.
