Oracle datapump Dir & grant Info ....

set heading on linesize 200 pagesize 100 termout on trimout on trimspool on 

col owner            format a10   heading 'Owner'

col directory_name   format a30   heading 'Directory name'

col directory_path   format a90   heading 'Directory path'

select

    owner

  , directory_name

  , directory_path

from  dba_directories

order by owner,directory_name;

clear columns  breaks  computes

create or replace directory data_pump_dir as '<operating_system_path>';

grant read on directory <directory_name> to <schema_name> ;

revoke write on directory <directory_name> from <schema_name> ;

set linesize 200 pagesize 200 

col owner for a10

col privilege for a10

col directory_path for a50

col grantee         for a20 

col directory_name  for a30 

select d.owner, d.directory_name, p.grantee, p.privilege, d.directory_path from dba_directories d, dba_tab_privs p

where d.owner = p.owner

and d.directory_name = p.table_name

order by d.owner, d.directory_name;

col grantor for a20

col grantee for a20

col table_schema for a20

col table_name for a20

col privilege for a10

select grantor, grantee, table_schema, table_name, privilege from all_tab_privs where table_name = 'EMP';

set lines 200

col privilege for a12

col grantee for a25

col owner for a25

select p.grantee, p.privilege, p.owner, d.directory_name from dba_tab_privs p, dba_directories d

WHERE p.table_name=d.directory_name

  and (grantee IN ('TEST', 'PUBLIC')  OR grantee IN (select granted_role from dba_role_privs  where grantee IN ('TEST', 'PUBLIC')))

ORDER BY 4,3,2;

set lines 80  

col privilege for a40  

SELECT grantee, granted_role, default_role  FROM dba_role_privs  

WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')  

ORDER BY 1,2;