Data Dictionary catalog scripts Oracle Tips by Burleson Consulting

Question:  I'm creating a new database manually and I don't know all of the data dictionary scripts that I need to run.  I know that I need to run catalog.sql and catproc.sql, but what other data dictionary scripts are required?

Answer:  The answer depends on what options that you are installing.  Also note that some scripts have sub-scripts, and catalog.sql calls other scripts within the $ORACLE_HOME/rdbms/admin directory.  Here are the sub-scripts within catalog.sql:

• catadt.sql - Creates views that support user-defined types and object components.
• cataudit.sql - Creates the sys.aud$ dictionary table,
• catexp.sql - Creates views for the import/export utilities
• catldr.sql - Creates views for the sql*loader tool
• catpart.sql - Creates views for partitioning oracle option,

Also, most DBA will run pupbld.sql.  The Oracle docs note:

The PRODUCT_USER_PROFILE (PUP) table provides product-level security that supplements the user-level security provided by the SQL GRANT and REVOKE commands and user roles.  To create the PUP table, log in to SQL*Plus as the SYSTEM user and run PUPBLD.SQL which is located in the ORACLE_HOME\SQLPLUS\ADMIN directory with:

SQL> @%ORACLE_HOME%\SQLPLUS\ADMIN\PUPBLD.SQL

Here is a list of the data dictionary catalog scripts in the admin directory:

catalog.sql - create data dictionary views.
cataudit.sql - data dictionary views for auditing. (catnoaud.sql will remove)
catblock.sql - create views of oracle locks
catclust.sql - create cluster database specific views definitions
catdbsyn.sql - catalog dba synonyms (dba_synonyms.sql)
catexp7.sql - create v7 style export/import views
catexp81.sql - create internal views for export/import utility
catio.sql - collect i/o per table/object stats by statistical sampling
catjava.sql - catalog scripts for java
catjobq.sql - catalog views for the job queue
catjvm.sql - create user|dba|all_java_* views
catldap.sql - catalog for ldap pl/sql api
catldr.sql - views for the direct path of the loader
catlsby.sql - logical standby tables and views
catpart.sql - creates data dictionary views for the partitioning table.
catoctk.sql - catalog - oracle cryptographic toolkit
catol.sql - outline views and synonyms
catparr.sql - parallel-server specific views, performance queries, etc
catpatch.sql - script to apply catalog patch set
catpitr.sql - tablespace point in time specific views
catplug.sql - pluggable tablespace check views
catprc.sql - creates data dictionary views for types, stored procedures, and triggers.
catproc.sql - run all sql scripts for the procedural option
catqm.sql - creates the tables and views needed to run the xdb system
catqueue.sql - contains the queue dictionary information
catrep.sql - run all sql scripts for replication option
catrepc.sql - repcat tables, views, and sequences
catrepm.sql - catalog script for master replication packages
catrepr.sql - catalog replication remove
catreps.sql - installs pl/sql packages for snapshot functionality.
catrls.sql - catalog views for row level security
catrm.sql - packages for the dbms resource manager
catrpc.sql - rpc procedural option
catrule.sql - rules catalog creation
catsnap.sql - data dictionary views for snapshots
catsnmp.sql - creates an snmpagent role
catspace.sql - catalog space management (remove with catspaced.sql)
catsum.sql - data dictionary views for summary management
catsvrmg.sql - create the views and tables required for server manager
catblock.sql - creates the view blocking_locks, which shows which locks are blocking the system
catnoaud.sql - removes the database audit trail created by cataudit.sql, including its data and views
catnoprc.sql - removes data dictionary structures that were created by catprc.sql

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.