Wednesday, 17 April 2013

How DATA Dictionary is created in Oracle?

I feel silly when I presumed the below information was already available in the blog/internet. However later I realized that it is NOT and hence it prompted me to add this blog.

Question : What is DATA Dictionary in an Oracle Database?


Answer : DATA Dictionary is also called METADATA. Oracle documentation tells that METADATA is created by running catalog.sql. While this is partially TRUE, the catalog.sql creates only the view/synonyms and few additional package/procedure/functions..also few more objects. However it certainly doesn't create the actual metadata. These views are created to access the metadata in a readable format. Metadata doesn't reside in the VIEWs rather it resides in the $(dollar) tables. For example, whatever information that you see by querying dba_objects view is majorly sourced from the dollar table called obj$. Likewise dba_tables view is sourced from tab$

In summary all the metadata resides in the DOLLAR table and they are not views.

Few examples,
obj$ - For objects
tab$ - For tables
ts$ - For tablespace
file$ - For datafiles
seg$ - For segments

Question : How & When DATA Dictionary is created in an Oracle Database?


Answer : DATA Dictionary is actually created during the Database creation time. After NOMOUNTING the instance you run the CREATE DATABASE command. This command, after creating the SYSTEM datafile it calls a SQL file ?/rdbms/admin/sql.bsq which creates the metadata



1 comment:

Unknown said...

It's really helpfull, Thanks Badri. :)
- Tarun Arya.