Creating the Data Dictionary

The data dictionary is not created automatically when the database is created. The DBA has to run a series of scripts that create the data dictionary and get it ready to help with database instance operation.

Running the Catalog Scripts

The data dictionary views are created when the SYS user runs the catalog.sql script located in the %ORACLE_HOME%\rdbms\admin directory on Windows, or the $ORACLE_HOME/rdbms/admin directory on Unix.

catalog.sql is a driver script that is run as SYS. Although it is possible to run this script as anyone with SYSDBA privileges, it is not suggested to run it as anyone other than SYS because this is one of the only userids that you are never likely to delete.

• catalog.sql Calls several other scripts that create the other data dictionary contents. The following list of called scripts provides you with an idea of what the script creates.

• cataudit.sql Creates the AUD$ data dictionary table that tracks all the audit trail information generated by Oracle when the auditing feature of the database is used.

• catldr.sql Creates the views used by SQL*Loader, the tool used to process large volume data loads from flat files into the database tables.

• catexp.sql Creates data dictionary views used by the IMPORT/EXPORT utilities.

• catpart.sql Creates views that support Oracle 9i's partitioning options.

• catadt.sql Creates the views that support the user-defined types and object components that are part of Oracle 9i's object features.

• standard.sql Creates the STANDARD package. The STANDARD package is used to store all Oracle scalar or simple data types. STANDARD also contains the SQL built-in functions.

It is important that you remember that catalog.sql calls these other scripts automatically. You don't have to do anything extra to have them run in your database.

Running the catproc.sql Script

catproc.sql creates the procedural options and utilities for use by PL/SQL. catproc.sql runs two different types of scripts when it is running.

If you were to examine the contents of the catproc.sql script (located in the same directory as catalog.sql), you would see references to other scripts similar to the way that catalog.sql references and calls other scripts. The scripts that catproc.sql calls, also located in the rdbms\admin directory, that end in .sql are package specifications for the many varied Oracle Server packages. These specifications contain the procedure, function, and type reference as well as constant definitions available in the package. These package specifications don't contain any actual code for the components. The scripts ending in .plb contain the actual code that is behind the procedures and functions from the specification definitions. The .plb ending, however, signifies that these package bodies are wrappered and, therefore, are unreadable by anyone except the Oracle Server.

Again, it is important that catproc.sql is run by SYS. And, again, although it is possible for any other user with SYSDBA privileges to run the script, those users could go away and the removal of the user who created the objects and packages in the data dictionary could cause the database to perform erratically.

Again, it is important that you remember that catproc.sql calls these other scripts automatically. You don't have to do anything extra to have them run in your database.