DDL to generate the Grants of a User in Oracle Database

clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt  ' Output filename : '

spool &&outfile..gen

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);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;

spool off

-- run_pre_drop_ddl.sql

-- Execute as SYS or a DBA user before dropping schemas

SET LONG 100000

SET PAGESIZE 0

SET LINESIZE 300

SET FEEDBACK OFF

SET TRIMSPOOL ON

SPOOL /u01/app/oracle/dpump/schema_supplemental_ddl.sql

-- 1. User creation DDL (includes default tablespace, profile, quota)

SELECT DBMS_METADATA.GET_DDL('USER', username) || ';' AS ddl

FROM dba_users

WHERE username IN ('SCHEMA1','SCHEMA2','SCHEMA3');

-- 2. System privileges granted to the users

SELECT 'GRANT ' || privilege || ' TO ' || grantee ||

       CASE WHEN admin_option = 'YES' THEN ' WITH ADMIN OPTION' ELSE '' END || ';'

FROM dba_sys_privs

WHERE grantee IN ('SCHEMA1','SCHEMA2','SCHEMA3');

-- 3. Role grants

SELECT 'GRANT ' || granted_role || ' TO ' || grantee ||

       CASE WHEN admin_option = 'YES' THEN ' WITH ADMIN OPTION' ELSE '' END || ';'

FROM dba_role_privs

WHERE grantee IN ('SCHEMA1','SCHEMA2','SCHEMA3');

-- 4. Object privileges granted TO the schemas (by other users)

SELECT 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name ||

       ' TO ' || grantee ||

       CASE WHEN grantable = 'YES' THEN ' WITH GRANT OPTION' ELSE '' END || ';'

FROM dba_tab_privs

WHERE grantee IN ('SCHEMA1','SCHEMA2','SCHEMA3');

-- 5. Profile DDL (if custom profiles are used)

SELECT DBMS_METADATA.GET_DDL('PROFILE', profile) || ';' AS ddl

FROM (

  SELECT DISTINCT profile

  FROM dba_users

  WHERE username IN ('SCHEMA1','SCHEMA2','SCHEMA3')

    AND profile != 'DEFAULT'

);

-- 6. Role DDL (if schemas own custom roles)

SELECT DBMS_METADATA.GET_DDL('ROLE', granted_role) || ';' AS ddl

FROM (

  SELECT DISTINCT granted_role

  FROM dba_role_privs

  WHERE grantee IN ('SCHEMA1','SCHEMA2','SCHEMA3')

    AND granted_role NOT IN ('CONNECT','RESOURCE','DBA')

);

-- 7. Public synonyms pointing to schema objects

SELECT 'CREATE OR REPLACE PUBLIC SYNONYM ' || synonym_name ||

       ' FOR ' || table_owner || '.' || table_name || ';'

FROM dba_synonyms

WHERE table_owner IN ('SCHEMA1','SCHEMA2','SCHEMA3')

  AND owner = 'PUBLIC';

-- 8. Tablespace quotas

SELECT 'ALTER USER ' || username || ' QUOTA ' ||

       CASE WHEN max_bytes = -1 THEN 'UNLIMITED'

            ELSE TO_CHAR(max_bytes) END ||

       ' ON ' || tablespace_name || ';'

FROM dba_ts_quotas

WHERE username IN ('SCHEMA1','SCHEMA2','SCHEMA3');

SPOOL OFF