Data Dictionary

Data Dictionary

Sys user owns the data dictionary.

Dictionary contains is data about data

They exist in datafiles of system tablespaces and sysaux tablespaces.

Data dictionary reference tables are read only so only SELECT sql is allowed. Even if we own these tables (sys user) we use views in order to rule of the possibilities of corrupting any data dictionary base table.

SQL> desc dict

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 TABLE_NAME                                         VARCHAR2(30)

 COMMENTS                                           VARCHAR2(4000)

Types of mata data stored in data dictionary:

It contains SQL definitions of the following:

  • Schema definitions like name, space allocated etc..
  • Constraint information
  • User privileges
  • Auditing information

Contents of Data Dictionary:

  • Base Tables
  • Views (Views are virtual tables):
  1. USER
  2. ALL
  3. DBA

And DPV (Dynamic performance views) like V$view_name

The SQL commands are exactly the same as for normal tables.

e.g;

select * from v$diag_info;

To get the information of the columns in the view we can use the below SQL command:

DESCRIBE v$view_name;

 

The Dynamic performance views are two as described above, listing them summarizing:

  1. Views (USER_tablename, All_tablename, DBA_tablename)
  2. Dynamic Performance views

USER_...: Shows the result of just the objects that the logged in user created

All_...:Shows the mata data of the objects created by the logged in user and the other objects that the user has access to.

DBA_...:Shows all the mata data of all the related objects of the entire data dictionary.

The Dual Table

It is a type of data dictionary table.

See:

SQL> desc dual;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 DUMMY                                              VARCHAR2(1)

 

SQL>

We can select against “pseudocolumns” like sysdate, user.

Dynamic Performance Views Details:

 

The DPVs are the virtual tables that store the current and live database activity which change dynamically. It’s like speedometer of a motor bike that always shows the current speed of the bike.

The format of the tables are v_$, v$.

Some information that v$ views have are:

Sessions, locks, file status, SQL execution plans, backup status etc.

e.g.;

SQL> select count(*) from dict;

 

  COUNT(*)

----------

      2553

 

SQL> select count(*) from dictionary;

 

  COUNT(*)

----------

      2553

 

SQL> select count(*) from dictionary where table_name like 'DBA_%';

 

  COUNT(*)

----------

       705

 

SQL> desc dict;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 TABLE_NAME                                         VARCHAR2(30)

 COMMENTS                                           VARCHAR2(4000)

 

SQL>

 

 

 

like1