Oracle Data Dictionary

Data Dictionary tables/Views contain information about the database and its objects. These are automatically created and maintained by oracle server.

You use SQL statements to access the data dictionary. Because the data dictionary is read-only,you can issue only queries against its tables and views.You can query the dictionary views that are based on the dictionary tables to find information such as:

Definitions of all schema objects in the database (tables, views, indexes, synonyms, sequences, procedures, functions, packages, triggers, and so on)

Default values for columns

Integrity constraint information

Names of Oracle users

Privileges and roles that each user has been granted

Other general database information

Oracle Data Dictionary has two parts :
1 Base Tables
2 User Accessible Views

Base tables can only read and written by Oracle Server. Views summarize and display the information stored in the base tables of data dictionary.
The Oracle user SYS owns all base tables and user-accessible views of the data dictionary. No Oracle user should ever alter (UPDATE, DELETE, or INSERT) any rows or schema objects contained in the SYS schema, because such activity can compromise data integrity.

View are categorized into 4 types.
USER_: These views contain data from your schema or what you own
ALL_: These views contain data from your schema and what all you can access from other schemas
DBA_: These views contains data from the whole database
V$ : Dynamic performance views

DICTIONARY

SQL> DESC DICTIONARY;

Name       Type           Nullable Default Comments                  
---------- -------------- -------- ------- --------------------------
TABLE_NAME VARCHAR2(30)   Y                Name of the object        
COMMENTS   VARCHAR2(4000) Y                Text comment on the object

SQL> SELECT *
FROM dictionary
WHERE table_name = 'USER_OBJECTS';

USER_OBJECTS and ALL_OBJECTS

USER_OBJECTS:
Query USER_OBJECTS to see all the objects that you own.

Using USER_OBJECTS, you can obtain a listing of all object names and types in your schema, plus the following information:
-Date created
-Date of last modification
-Status (valid or invalid)

ALL_OBJECTS:

Query ALL_OBJECTS to see all the objects to which you have access.

SQL> SELECT object_name, object_type, created, status
  2  FROM user_objects
  3  ORDER BY object_type;

OBJECT_NAME         OBJECT_TYPE         CREATED     STATUS
------------------- ------------------- ----------- -------
CHANNELS_DIM        DIMENSION           7/26/2016 1 VALID
TIMES_DIM           DIMENSION           7/26/2016 1 VALID
PRODUCTS_DIM        DIMENSION           7/26/2016 1 VALID
CUSTOMERS_DIM       DIMENSION           7/26/2016 1 VALID
PROMOTIONS_DIM      DIMENSION           7/26/2016 1 VALID
CUSTOMERS_PK        INDEX               7/26/2016 1 VALID
COUNTRIES_PK        INDEX               7/26/2016 1 VALID
SALES_PROD_BIX      INDEX               7/26/2016 1 VALID
SALES_CUST_BIX      INDEX               7/26/2016 1 VALID

USER_TABLES

SQL> DESC user_tables

Name                      Type         Nullable Default Comments                                                                                       
------------------------- ------------ -------- ------- ------------------------------------------------------------------------------------------------
TABLE_NAME                VARCHAR2(30)                  Name of the table                                                                             
TABLESPACE_NAME           VARCHAR2(30) Y                Name of the tablespace containing the table                                                   
CLUSTER_NAME              VARCHAR2(30) Y                Name of the cluster, if any, to which the table belongs                                       
IOT_NAME                  VARCHAR2(30) Y                Name of the index-only table, if any, to which the overflow or mapping table entry belongs     
STATUS                    VARCHAR2(8)  Y                Status of the table will be UNUSABLE if a previous DROP TABLE operation failed,VALID otherwise

SQL> SELECT table_name
  2  FROM user_tables;

TABLE_NAME
------------------------------
EMPLOYEES_3
ERR$_BIGDATA
BIGDATA_MOVE
TAB_VIRTUAL_COL
SALES
EMPLOYEE_PHONE
USER_TAB_COLUMNS


SQL> DESCRIBE user_tab_columns

Name                 Type          Nullable Default Comments                                                          
-------------------- ------------- -------- ------- --------------------------------------------------------------------
TABLE_NAME           VARCHAR2(30)                   Table, view or cluster name                                       
COLUMN_NAME          VARCHAR2(30)                   Column name                                                       
DATA_TYPE            VARCHAR2(106) Y                Datatype of the column                                            
DATA_TYPE_MOD        VARCHAR2(3)   Y                Datatype modifier of the column                                  
DATA_TYPE_OWNER      VARCHAR2(30)  Y                Owner of the datatype of the column                              
DATA_LENGTH          NUMBER                         Length of the column in bytes                                     
DATA_PRECISION       NUMBER        Y                Length: decimal digits (NUMBER) or binary digits (FLOAT)          
DATA_SCALE           NUMBER        Y                Digits to right of decimal point in a number                      
NULLABLE             VARCHAR2(1)   Y                Does column allow NULL values?                                     
COLUMN_ID            NUMBER        Y                Sequence number of the column as created                          
DEFAULT_LENGTH       NUMBER        Y                Length of default value for the column                            
DATA_DEFAULT         LONG          Y                Default value for the column                                      
NUM_DISTINCT         NUMBER        Y                The number of distinct values in the column                      
LOW_VALUE            RAW(32)       Y                The low value in the column                                       
HIGH_VALUE           RAW(32)       Y                The high value in the column                                       
SQL>

SQL> SELECT column_name, data_type, data_length,
  2  data_precision, data_scale, nullable
  3  FROM user_tab_columns
  4  WHERE table_name = 'EMPLOYEES';

COLUMN_NAME                    DATA_TYPE         DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE
------------------------------ ----------------- ----------- -------------- ---------- --------
EMPLOYEE_ID                    NUMBER                     22              6          0 N
FIRST_NAME                     VARCHAR2                   20                           Y
LAST_NAME                      VARCHAR2                   25                           N
EMAIL                          VARCHAR2                   25                           N
PHONE_NUMBER                   VARCHAR2                   20                           Y
HIRE_DATE                      DATE                        7                           N
JOB_ID                         VARCHAR2                   10                           N
SALARY                         NUMBER                     22              8          2 Y
COMMISSION_PCT                 NUMBER                     22              2          2 Y
MANAGER_ID                     NUMBER                     22              6          0 Y
DEPARTMENT_ID                  NUMBER                     22              4          0 Y
11 rows selected
SQL>

USER_CONSTRAINTS and USER_CONS_COLUMNS

USER_CONSTRAINTS describes the constraint definitions on your tables.

USER_CONS_COLUMNS describes columns that are owned by you and that are specified in constraints.

SQL> DESCRIBE user_constraints
Name              Type         Nullable Default Comments                                                                 
----------------- ------------ -------- ------- ---------------------------------------------------------------------------
OWNER             VARCHAR2(30) Y                Owner of the table                                                       
CONSTRAINT_NAME   VARCHAR2(30)                  Name associated with constraint definition                               
CONSTRAINT_TYPE   VARCHAR2(1)  Y                Type of constraint definition                                             
TABLE_NAME        VARCHAR2(30)                  Name associated with table with constraint definition                     
SEARCH_CONDITION  LONG         Y                Text of search condition for table check                                 
R_OWNER           VARCHAR2(30) Y                Owner of table used in referential constraint                             
R_CONSTRAINT_NAME VARCHAR2(30) Y                Name of unique constraint definition for referenced table                 
DELETE_RULE       VARCHAR2(9)  Y                The delete rule for a referential constraint                             
STATUS            VARCHAR2(8)  Y                Enforcement status of constraint -  ENABLED or DISABLED                   
DEFERRABLE        VARCHAR2(14) Y                Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE               
DEFERRED          VARCHAR2(9)  Y                Is the constraint deferred by default -  DEFERRED or IMMEDIATE           
VALIDATED         VARCHAR2(13) Y                Was this constraint system validated? -  VALIDATED or NOT VALIDATED       
GENERATED         VARCHAR2(14) Y                Was the constraint name system generated? -  GENERATED NAME or USER NAME 
BAD               VARCHAR2(3)  Y                Creating this constraint should give ORA-02436.  Rewrite it before 2000 AD.
RELY              VARCHAR2(4)  Y                If set, this flag will be used in optimizer                               
LAST_CHANGE       DATE         Y                The date when this column was last enabled or disabled                   
INDEX_OWNER       VARCHAR2(30) Y                The owner of the index used by the constraint                             
INDEX_NAME        VARCHAR2(30) Y                The index used by the constraint                                         
INVALID           VARCHAR2(7)  Y                                                                                         
VIEW_RELATED      VARCHAR2(14) Y                                                                                         


SQL>

SQL>

SQL> SELECT constraint_name, constraint_type,
  2  search_condition, r_constraint_name,
  3  delete_rule, status
  4  FROM user_constraints
  5  WHERE table_name = 'EMPLOYEES';
CONSTRAINT_NAME                CONSTRAINT_TYPE SEARCH_CONDITION                                                                 R_CONSTRAINT_NAME              DELETE_RULE STATUS
------------------------------ --------------- -------------------------------------------------------------------------------- ------------------------------ ----------- --------
EMP_MANAGER_FK                 R                                                                                                EMP_EMP_ID_PK                  NO ACTION   ENABLED
EMP_JOB_FK                     R                                                                                                JOB_ID_PK                      NO ACTION   ENABLED
EMP_DEPT_FK                    R                                                                                                DEPT_ID_PK                     NO ACTION   ENABLED
EMP_LAST_NAME_NN               C               "LAST_NAME" IS NOT NULL                                                                                                     ENABLED
EMP_EMAIL_NN                   C               "EMAIL" IS NOT NULL                                                                                                         ENABLED
EMP_HIRE_DATE_NN               C               "HIRE_DATE" IS NOT NULL                                                                                                     ENABLED
EMP_JOB_NN                     C               "JOB_ID" IS NOT NULL                                                                                                        ENABLED
EMP_SALARY_MIN                 C               salary > 0                                                                                                                  ENABLED
EMP_EMAIL_UK                   U                                                                                                                                           ENABLED
EMP_EMP_ID_PK                  P                                                                                                                                           ENABLED
10 rows selected



SQL>


SQL> DESCRIBE user_cons_columns

Name            Type           Nullable Default Comments                                                                                       
--------------- -------------- -------- ------- ------------------------------------------------------------------------------------------------
OWNER           VARCHAR2(30)                    Owner of the constraint definition                                                             
CONSTRAINT_NAME VARCHAR2(30)                    Name associated with the constraint definition                                                 
TABLE_NAME      VARCHAR2(30)                    Name associated with table with constraint definition                                         
COLUMN_NAME     VARCHAR2(4000) Y                Name associated with column or attribute of object column specified in the constraint definition
POSITION        NUMBER         Y                Original position of column or attribute in definition                                         


SQL>

SQL>

SQL> SELECT constraint_name, column_name
  2  FROM user_cons_columns
  3  WHERE table_name = 'EMPLOYEES';

CONSTRAINT_NAME                COLUMN_NAME
------------------------------ --------------------------------------------------------------------------------
EMP_MANAGER_FK                 MANAGER_ID
EMP_JOB_FK                     JOB_ID
EMP_DEPT_FK                    DEPARTMENT_ID
EMP_LAST_NAME_NN               LAST_NAME
EMP_EMAIL_NN                   EMAIL
EMP_HIRE_DATE_NN               HIRE_DATE
EMP_JOB_NN                     JOB_ID
EMP_SALARY_MIN                 SALARY
EMP_EMAIL_UK                   EMAIL
EMP_EMP_ID_PK                  EMPLOYEE_ID
10 rows selected

SQL>

Other Useful views:

SELECT * FROM DICTIONARY;
SELECT * FROM User_Users;
SELECT * FROM all_users;
SELECT * FROM User_Sys_Privs;
SELECT * FROM User_Role_Privs;
SELECT * FROM user_catalog;
SELECT * FROM user_tablespaces;
SELECT * FROM User_Free_Space;
SELECT * FROM User_Ts_Quotas;
SELECT * FROM User_Errors
SELECT * FROM user_source
SELECT * FROM user_indexes
SELECT * FROM User_Procedures
SELECT * FROM User_Triggers
SELECT * FROM User_Indexes
SELECT * FROM User_Views
SELECT * FROM User_Synonyms
SELECT * FROM user_tab_partitions
SELECT * FROM user_part_indexes;
SELECT * FROM User_Part_Tables;
SELECT * FROM User_All_Tables;
SELECT * FROM User_Tab_Privs;
SELECT * FROM User_Tab_Privs_Made;
SELECT * FROM user_tab_privs_recd;
SELECT * FROM user_password_limits;
SELECT * FROM user_scheduler_jobs;
SELECT * FROM User_Jobs;
SELECT * FROM user_datapump_jobs;
SELECT * FROM User_Constraints;
SELECT * FROM user_dependencies;
SELECT * FROM User_Db_Links;
SELECT * FROM User_Extents;
SELECT * FROM User_Segments;
SELECT * FROM User_External_Tables;
SELECT * FROM User_Flashback_Archive;
SELECT * FROM user_identifiers;
SELECT * FROM User_Java_Classes;
SELECT * FROM user_mviews;
SELECT * FROM user_nested_tables;
SELECT * FROM user_recyclebin;
SELECT * FROM user_tab_statistics;
SELECT * FROM user_unused_col_tabs;
SELECT * FROM User_Updatable_Columns;
SELECT * FROM user_varrays;
SELECT * FROM user_xml_column_names;
SELECT * FROM user_xml_indexes;
SELECT * FROM all_Xml_Schemas;
SELECT * FROM User_Xml_Views;
SELECT * FROM v$instance;
SELECT * FROM v$version;
SELECT * FROM v$parameter;
SELECT * FROM v$spparameter;
SELECT * FROM v$session;
SELECT * FROM v$session_blockers;
SELECT * FROM v$session_longops;
SELECT * FROM v$session_wait;
SELECT * FROM v$session_event;
SELECT * FROM v$sql
SELECT * FROM v$sql_plan
SELECT * FROM v$sql_shared_memory;
SELECT * FROM v$sql_workarea;
SELECT * FROM v$lock;
SELECT * FROM v$library_cache_memory;
SELECT * FROM v$license;
SELECT * FROM v$listener_network;
SELECT * FROM v$sess_io;
SELECT * FROM v$memory_target_advice;
SELECT * FROM v$sga;
SELECT * FROM v$reserved_words;
SELECT * FROM v$flash_recovery_area_usage;
SELECT * FROM v$process
SELECT * FROM v$db_cache_advice;
SELECT * FROM v$datapump_session;
SELECT * FROM v$nls_parameters;
SELECT * FROM v$undostat;
SELECT * FROM v$database;