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;