8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Script: role_ddl.sql
( Download Script )
-- ----------------------------------------------------------------------------------- -- File Name : https://oracle-base.com/dba/script_creation/role_ddl.sql -- Author : Tim Hall -- Description : Displays the DDL for a specific role. -- Call Syntax : @role_ddl (role) -- Last Modified: 27/07/2022 - Increase long to 1000000. -- ----------------------------------------------------------------------------------- set long 1000000 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_role VARCHAR2(30); exec :v_role := upper('&1'); select dbms_metadata.get_ddl('ROLE', r.role) AS ddl from dba_roles r where r.role = :v_role union all select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl from dba_role_privs rp where rp.grantee = :v_role 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_role 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_role and rownum = 1 / set linesize 80 pagesize 14 feedback on verify on