How to check whether user has datapump privilege

SQL>SET lines 100 
COL privilege FOR a50
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;
GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
ARJU DBA YES
DBA EXP_FULL_DATABASE YES
DBA IMP_FULL_DATABASE YES
FARUK DBA YES
MOMIN DBA YES
SYS DBA YES
SYS EXP_FULL_DATABASE YES
SYS IMP_FULL_DATABASE YES
SYSMAN DBA YES
SYSTEM DBA YES
TEST EXP_FULL_DATABASE YES

 Check for granted privileges on directory objects

SET lines 110
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;
A typical output is 
GRANTEE PRIVILEGE OWNER DIRECTORY_NAME
-------------------- ---------- -------------------- -----------------
TEST READ SYS D
TEST WRITE SYS D
EXP_FULL_DATABASE READ SYS DATA_PUMP_DIR
EXP_FULL_DATABASE WRITE SYS DATA_PUMP_DIR

Directory Name : TEST 

Check for tablespace quota
 
SET lines 100 numwidth 12
SELECT q.username, q.tablespace_name, q.bytes, q.max_bytes
FROM dba_ts_quotas q, dba_users u
WHERE q.username=u.username AND q.username in ('TEST');
USERNAME                       TABLESPACE_NAME                       BYTES    MAX_BYTES
------------------------------ ------------------------------ ------------ ------------
TEST SYSTEM 65536 10485760


2 thoughts on “How to check whether user has datapump privilege

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.