-- #!/bin/sh
-- #@(#) \$Id get_ddl_grant_system_privs.sh, v1.0
-- #
-- # su - oracle
-- # export ORACLE_SID=xxxx
-- # export ORACLE_HOME=/opt/app/oracle/product/12.2.0
-- #
-- # ../sql/get_ddl_grant_system_privs.sh
-- #
-- #export NLS_LANG=JAPANESE_JAPAN.AL32UTF8
-- ${ORACLE_HOME}/bin/sqlplus / as sysdba << EOF
--
-- スキーマ及びロールに付与されたシステム権限
--
SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 100
SET PAGESIZE 50000
SET TAB OFF
SET TRIMOUT OFF
SET TRIMSPOOL ON
-- SPOOL pass includes CONNECT_IDENTIFIER, USER_NAME and SYSDATE
COLUMN USER_NAME    NEW_VALUE USER_NAME
COLUMN SYSTEM_DATE  NEW_VALUE SYSTEM_DATE
SELECT USER || '_' AS USER_NAME
     , TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE
FROM DUAL ;
SPOOL get_ddl_grant_system_privs_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..log
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL get_ddl_grant_system_privs_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..log
COLUMN GRANTS  FORMAT A1000
SELECT 'GRANT ' || RPAD(SP.PRIVILEGE, 40) || ' TO ' || SP.GRANTEE
    || CASE
           WHEN SP.ADMIN_OPTION = 'YES' THEN  -- ADMIN OPTION の有無
               ' WITH ADMIN OPTION'
       END || ' ;' AS GRANTS
FROM(
    SELECT COLUMN_VALUE AS GRANTEE FROM TABLE(PKG_USERS_SCHEMA.GET_USERS_SCHEMA) -- ユーザーが作成したスキーマのみ
    ) A
INNER JOIN DBA_SYS_PRIVS SP
ON    SP.GRANTEE = A.GRANTEE
ORDER BY SP.GRANTEE
       , SP.PRIVILEGE ;
--
-- ROLE_SYS_PRIVS
-- PL/SQLではロール経由の権限は無視されるので注意のこと。
--
SELECT 'GRANT ' || RPAD(SP.PRIVILEGE, 40) || ' TO ' || SP.ROLE
    || CASE
           WHEN SP.ADMIN_OPTION = 'YES' THEN  -- ADMIN OPTION の有無
               ' WITH ADMIN OPTION'
       END || ' ;' AS GRANTS
FROM ROLE_SYS_PRIVS SP
ORDER BY SP.ROLE
       , SP.PRIVILEGE ;
SPOOL OFF
COLUMN GRANTS  CLEAR
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 80
SET TRIMOUT ON
-- exit
-- EOF
-- #export NLS_LANG=en_US
-- # ---------------------- End of get_ddl_grant_system_privs.sh ------------------------------
参照
Oracle 純粋にユーザー側のスキーマのみを取得