Oracle Concepts - 6 Data Dictionary and Dynamic Performance Views

Data dictionary, which is a read-only set of tables that provides administrative metadata about the database

Data dictionary consists of the following types of objects:

1. Base tables
2. Views

Data Dictionary View Sets:

1. DBA_ > Database administrators > All objects
2. ALL_ > All users > Objects to which user has privileges
3. USER_ > All users > Objects owned by user

$ sqlplus / as sysdba
SQL> set pages 0
SQL> set lines 1000
SQL> column table_name format a35;
SQL> column comments format a75;

### list data dictionary views
SQL> select * from dictionary order by table_name;

### list Views with the Prefix DBA_
SQL> column owner format a25;
SQL> column object_name format a45;
SQL> column object_type format a35;
SQL> select owner, object_name, object_type from dba_objects order by owner, object_name;

### list Views with the Prefix ALL_
SQL> select owner, object_name, object_type from all_objects order by owner, object_name;


### list Views with the Prefix USER_
SQL> set lines 80
SQL> desc user_objects;
SQL> set lines 800
SQL> column object_name format a45;
SQL> column object_type format a65;
SQL> select object_name, object_type from user_objects order by object_name;

### Use the SET ROLE statement to enable and disable roles for your current session. You cannot enable more than 148 user-defined roles at one time.

### Restriction on the ALL Clause You cannot use this clause to enable roles with passwords that have been granted directly to you.

SQL> connect system/oracle
Connected.
SQL> set role all;
SQL> select count (*) from all_objects;
     73726

SQL> set role none;

Role set.

SQL> select count (*) from all_objects;
     67841

### DUAL table has one column called DUMMY and one row containing the value X.
SQL> select ((3*4)+5)/3 from dual;
 5.66666667

- Dynamic performance (V$ views  or fixed views) contain information such as the following:

• System and session parameters
• Memory usage and allocation
• File states (including RMAN backup files)
• Progress of jobs and tasks
• SQL execution
• Statistics and metrics

- In Oracle Real Application Clusters (Oracle RAC), querying a GV$ view retrieves the V$ view information from all qualified database instances

- catalog.sql script, which contains definitions of the views and public synonyms for the dynamic performance views.

- DBMS_METADATA package provides interfaces for extracting complete definitions of database objects.