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

col "Directory path" format a90   heading 'Directory path'

select

    *

from  dba_directories

order by owner,directory_name;

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> ;

create or replace directory data_pump_dir1 as '/home/oracle';

grant read on directory data_pump_dir1 to scott ;

grant write on directory data_pump_dir1 to scott ;

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; 

set linesize 250 trimspool on

set pagesize 100

col owner format a15

col directory_name format a30

col directory_path format a100

col oreated format a19

col last_ddl_time format a19

select

distinct

o.con_id

,d.owner

, d.directory_name

, to_char(o.created,'dd-mm-yyyy hh24:mi:ss') oreated

, to_char(o.last_ddl_time,'dd-mm-yyyy hh24:mi:ss') last_ddl_time

, d.directory_path

from cdb_directories d

join cdb_objects o on o.owner = d.owner and o.object_name = d.directory_name

order by 1,2,3

/

 expdp userid=scott/tiger@//rac02.int.s.de.net:1523/anujz tables=scott.emp directory=DATA_PUMP_DIR dumpfile=emp1.dmp logfile=emp.log