Source Extractors - SQL PLSQL - Information - Determine the Views DBA VIEWS or ALL VIEWS being used by the User for Oracle Extraction

This page will help you to determine the Views (DBA_SYSTEM or ALL_VIEW) being used during a Oracle extraction.

The extractor decides to use DBA_VIEWS or ALL_VIEWS according to these assumptions:

1. Extractor uses DBA_VIEWS while extraction if the user has 'SELECT ANY DICTIONARY' privilege granted to it.
This is confirmed by the following query:

select 1 from session_privs where privilege = 'SELECT ANY DICTIONARY';

If 1 is displayed, if you will use this user to extract some schemas you will have access to DBA_VIEWS,else go to step-2.

2. If the user don't have this privilege, then the extractor looks if the user has access to the following views/tables system and will use the ALL_VIEWS:

select 1 from user_tab_privs_recd
where privilege = 'SELECT'
and table_name in ('TAB$','OBJ$','COL$','USER$','SYN$','ARGUMENT$','DEPENDENCY$',
'COLTYPE$', 'SNAP$', 'DBA_COL_COMMENTS', 'DBA_DB_LINKS',
'DBA_INDEXES', 'DBA_IND_COLUMNS','DBA_IND_EXPRESSIONS','DBA_OBJECTS',
'DBA_PROCEDURES', 'DBA_SEQUENCES', 'DBA_SYNONYMS', 'DBA_TABLES',
'DBA_OBJECT_TABLES', 'DBA_TAB_COLUMNS', 'DBA_USERS', 'DBA_VIEWS',
'DBA_CONSTRAINTS', 'DBA_CONS_COLUMNS', 'DBA_TYPES', 'DBA_COLL_TYPES',
'DBA_SOURCE','DBA_TRIGGERS','DBA_DEPENDENCIES','DBA_MVIEWS'
)

If 1 is displayed, if you will use this user to extract some schemas you will have access to ALL_VIEWS.

You can do the following test to see if a user DBA granted role has access to DBA_VIEWS :
1. Create your user :

CREATE USER SYSADM IDENTIFIED BY cast
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFIE DEFAULT
/
GRANT DBA TO SYSADM
/

2.Then connect as SYSADM and execute the 1st query :

select 1 from session_privs where privilege = 'SELECT ANY DICTIONARY';

If 1 is displayed, if you will use this user to extract some schemas you will have access to DBA_VIEWS.