Saturday, October 15, 2016

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

    Name       Type           Nullable Default Comments                  
    ---------- -------------- -------- ------- --------------------------
    TABLE_NAME VARCHAR2(30)   Y                Name of the object        
    COMMENTS   VARCHAR2(4000) Y                Text comment on the object
    FROM dictionary
    WHERE table_name = '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)


  • 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;
    ------------------- ------------------- ----------- -------
    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

    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;
    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> SELECT column_name, data_type, data_length,
      2  data_precision, data_scale, nullable
      3  FROM user_tab_columns
      4  WHERE table_name = 'EMPLOYEES';
    ------------------------------ ----------------- ----------- -------------- ---------- --------
    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

  • 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> 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> 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> SELECT constraint_name, column_name
      2  FROM user_cons_columns
      3  WHERE table_name = 'EMPLOYEES';
    ------------------------------ --------------------------------------------------------------------------------
    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

    Other Useful views:
    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;

    No comments :

    Post a Comment