Oracle SQL - Introduction Data Dictionary


Oracle stores data dictionary data the same way as they store "regular" data: in tables.

You can use the SQL language to query data dictionary data in the same way that you query ordinary data.

You need to know only the names of the data dictionary tables and the names of their columns.

For example, there is a role, SELECT_CATALOG_ROLE, which contains all privileges that you need to be able to access the data dictionary data.

The following code demonstrates how Oracle controls data dictionary access.

SQL> describe dba_sys_privs 
ORA-04043: object "SYS"."DBA_SYS_PRIVS" does not exist 
SQL> connect / as sysdba 
SQL> grant select_catalog_role to joe; 
Grant succeeded. 
SQL> connect joe/book 
SQL> desc dba_sys_privs 
 Name                          Null?    Type 
 ----------------------------- -------- --------------- 
 GRANTEE                       NOT NULL VARCHAR2(30) 
 PRIVILEGE                     NOT NULL VARCHAR2(40) 
 ADMIN_OPTION                           VARCHAR2(3)