Search This Blog

Total Pageviews

Friday 18 November 2016

Oracle datapump Dir & grant Info ....

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; 


Oracle DBA

anuj blog Archive