select name from v$database;

set echo off head off verify off feedb off pages 0 long 10000 longchunk 10000 trimspool on lines 2000 timing off term off

exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',TRUE);

define _filename='cr_user_&1..sql'

define _dblink='cr_user_dblink_&1..sql'

spool &_filename

select (case

        when ((select count(*)

               from   dba_users

               where  username = UPPER('&&1')) > 0)

        then  dbms_metadata.get_ddl ('USER', UPPER('&&1'))

                else  to_clob ('--')

        end ) Extracted_DDL from dual

UNION ALL

select (case

        when ((select count(*)

               from   dba_ts_quotas

               where  username = UPPER('&&1')) > 0)

        then  dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', UPPER('&&1'))

                else  to_clob ('--')

        end ) from dual

UNION ALL

select (case

        when ((select count(*)

               from   dba_role_privs

               where  grantee = UPPER('&&1')) > 0)

        then  dbms_metadata.get_granted_ddl ('ROLE_GRANT', UPPER('&&1'))

                else  to_clob ('--')

        end ) from dual

UNION ALL

select (case

        when ((select count(*)

               from   dba_sys_privs

               where  grantee = UPPER('&&1')) > 0)

        then  dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', UPPER('&&1'))

                else  to_clob ('--')

        end ) from dual

UNION ALL

select (case

        when ((select count(*)

               from   dba_tab_privs

               where  grantee = UPPER('&&1')) > 0)

        then  dbms_metadata.get_granted_ddl ('OBJECT_GRANT', UPPER('&&1'))

        else  to_clob ('--')

        end ) from dual

UNION ALL

select dbms_metadata.get_granted_ddl ('DEFAULT_ROLE', UPPER('&&1')) from dual

;

spool off

spool &_dblink

select dbms_metadata.get_ddl('DB_LINK',DB_LINK,OWNER) as DDL FROM DBA_DB_LINKS where OWNER=UPPER('&&1');

spool off

set termout on

prompt ******************************

prompt Execute: &_filename

prompt Execute: &_dblink as '&&1'

prompt ******************************

exit