There are many Oracle SQL scripts which are automatically run by the catalog.sql and catproc.sql scripts during automated database builds or must be run manually during a manual build Others optional scripts help with database tuning checking of locking problems and adding useful DBA tables.
Three scripts, catalog.sql, cataudit.sql / catnoaudit.sql warrant attention.
catalog.sql - creates all of commonly used data dictionary views
cataudit.sql - creates the audit tables and is run by catproc.sql
catnoaud.sql - the opposite of cataudit.sql (drops all audit tables). Negative-scripts usually contain no in their names.
catproc.sql runs all of the dbms*.sql and prvt*.sql scripts. Listed below are useful extra scripts.
utlchain.sql - default table for storing output of the ANALYZE LIST CHAINED ROWS command.
utldidxs.sql - has two parameters to allow DBAs to specify which statistics are desired from INDEX$INDEX_STATS and INDEX$BADNESS_STATS commands.
utldtree.sql - will allow seeing of all objects which are recursively dependant on a given object. Permissions are relevant.
utlbstat.sql & utlestat.sql - utlestat.sql generates the delta statistics based on the initial statistics loaded using utlbstat.sql. These scripts are used to generate the report.txt output file of statistics used for tuning.
utlexcpt.sql - creates the EXCEPTIONS table used to hold table entry information that causes conflicts when creating constraints.
utllockt.sql - generates a lock wait-for graph. Prints sessions waiting for locks and the locks they are waiting for.
dbmspool.sql & prvtpool.sql - these scripts build the DBMS_SHARED_POOL package of procedures for managing the shared pool.
utloidxs - used to find information about the selectivity of columns.
utlsidxs.sql - uses the utloidxs.sql script to analyse all indexes in a schema for usefulness.
utlkprf.sql - grants public access to all views used by TKPROF with verbose=y option and creates the TKPROFER role.
utlxplan.sql - creates the PLAN_TABLE table that is used by the EXPLAIN_PLAN statement. The EXPLAIN statement used in the SET AUTOTRACE ON command in SQL*Plus requires this table's presence to store the descriptions of the rows sources. Data in the PLAN_TABLE table is used to query and evaluate SQL statements using the EXPLAIN clause with the AUTOTRACE command, without using tracing and TKPROF.
catblock.sql - creates many useful database lock views.
The creation of a database is not completed until several scripts have been run. These scripts create data dictionary views and installs the procedure options and utilities (DBMS_packages). Also the catblock.sql, dbmspool.sql and prvtpool.sql scripts must be run. Also execute the catparr.sql script, this installs parallel server views which are useful for tuning purposes.