I had an interesting challenge when trying to explain the dictionary to a colleague. So we all have a general idea of what the data dictionary is and what it does (just read the first page of this chapter in the docs). When does it get built? Interesting question. Usually we say that catalog.sql builds the dictionary, or even bsq.sql. However, did you realize that 601 fixed tables (10.2.0.4) exist in memory just for starting the instance (NOTE: no database!)? Try this one on for size:
/u01/app/oracle/product/10.2.0.4/dbs: echo "db_name = SAC" > initSAC.ora /u01/app/oracle/product/10.2.0.4/dbs: . oraenvORACLE_SID = [DUMMY] ? SACORACLE_HOME = [/u01/app/oracle] ? /u01/app/oracle/product/10.2.0.4 /u01/app/oracle/product/10.2.0.4/dbs: sqlplus / as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 23 09:32:35 2009Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to an idle instance.SAC_SQL > startup nomountORACLE instance started.Total System Global Area 419430400 bytesFixed Size 2286584 bytesVariable Size 114105352 bytesDatabase Buffers 268435456 bytesRedo Buffers 34603008 bytesSAC_SQL > select count(*) from X$KQFTA;COUNT(*)----------601
If you want all the names of those fixed tables, select KQFTANAM.
So, even without running any scripts, we have "data dictionary" objects residing in memory. This helps me to understand what the dictionary is and what it does. For an analogy, it is like the overhead address space a program uses to store all its global variables. In this case, it is just happens to be organized into relational tables with gobbly-gook table/column names and normalized up the wazoo! =)
I have to confess, I was quite disappointed with this chapter. Of the 5 pages, the first 4 tell you not to change anything and give a brief overview of the differences between USER, ALL and DBA views. The last page starts to get just a tiny bit juicy and at least mention dynamic performance views, but then like a cheap battery dies out too soon.
There is some really cool stuff locked away in the x$ tables, let alone the rest of the db metadata. For a quick glimpse under the covers, I found eygle's list quit informative and helpful; it is allegdegly a copy of Metalink note 22241.1, which is supposed to be internal only (I cannot see it at this point in time); it merely lists the fixed tables to demonstrate the naming convention, but it also gives us an idea how things are organized and what types of things the kernel tracks. I hope one day Oracle wakes up and realizes the advantage of sharing more information like this.
Yong Huang also has a listing.