What is Data Dictionary?

The  Data Dictionary   is  a  repository   of  database  metadata (data  about  data), about  all the  information  inside  the database. This  repository  is owned  by  "sys",  and  is  stored principally  in  the "system"  tablespace, though  some  components  are stored in the "sysaux"  tablespace (in Oracle 10g and 11g). The  Oracle  user  "SYS"  stores  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. 

A data dictionary contains the following contents  :

• The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
• How much space has been allocated for, and is currently used by, the schema objects
• Default values for columns
• Integrity constraint information
• The names of Oracle users
• Privileges and roles each user has been granted
• Auditing information, such as who has accessed or updated various schema objects
• Other general database information.

The data dictionary consists of the following :

1.) Base Table : The  underlying  tables  that  store  information about the associated database. Only 

Oracle  should write to and read these  tables. Users  rarely access them directly because they  are  normalized, and most of the data is stored in a cryptic format.

2.) User-Accessible Views :  The  views that summarize and  display  the  information  stored  in  the base tables  of  the  data dictionary. These  views  decode  the  base table data into  useful information, such as user or table names, using joins and where clauses to simplify the information. Most users are given access to the views rather than the base tables.

The  user-accessible views  come in two primary forms :

1.) The static performance views : The static views are dba_xx , all_xx  , user_xx  views  (eg. dba_users, user_tables, all_tables). These views are used to manage database structures.

2.) The v$dynamic performance views : The dynamics views are v$xx views (eg v$process).These views are used to monitor real time database statistics.

Some  of  these tables  are inside  of  the Oracle  kernel, so  we  would  never  work  directly  with them unless  we  are  working  for Oracle support  or  performing  a  disaster  recovery scenario . But instead we can  access  to  the  views  in  order  to know  the “information about the information”.  For example,  a possible  usage  of  this  data  dictionary  would  be  to  know all  the tables  owned  by  a  single user, or  the list  or  relationships between  all  the  tables  of  the  database.

The  main view of the data dictionary is the view DICT (or DICTIONARY):  
SQL> desc dict
Name                           Null?                Type
---------------               --------         -----------------
TABLE_NAME                                   VARCHAR2(30)
COMMENTS                                      VARCHAR2(4000)

Through  the  DICT view, we can access  to all the data dictionary views that could provide us the information that  we  need. For  example, if  we are looking  for  information  related  to  link , but we  don’t know  where  to  look  for, then query the DICT view:

SQL> select  table_name  from  dict  where  table_name  like   '%DATAPUMP%'  ; 
TABLE_NAME
----------------------------
DBA_DATAPUMP_JOBS

DBA_DATAPUMP_SESSIONS
USER_DATAPUMP_JOBS
GV$DATAPUMP_JOB
GV$DATAPUMP_SESSION
V$DATAPUMP_JOB
V$DATAPUMP_SESSION
7 rows selected.


Now, we  have just to query  one of  these  views  to  find  the  data  we are  looking for  .GV$ views  are very  useful  when we  are working  with  RAC,  and V$  views  are instance  related. Remember  that  the data  dictionary  provides  critical  information  of  the database, and  it should  be  restricted  to users. However,  if  a  user  really  needs  to query  the  data dictionary,  we  can  grant  the  following  privileges .

SQL> grant select_catalog_role to username ;

As  a DBA, we  can see why the data dictionary is so important. Since we can’t possibly remember everything  about our database (like the names of all the tables and columns) Oracle remembers this for us. All  we need is to learn how to find that information .

Enjoy     :-)