SQL Scripts

This chapter describes the SQL scripts that are required for optimal operation of the Oracle7 Server. The SQL scripts are described in the following sections:

Creating the Data Dictionary

Creating Additional Data Dictionary Structures

Migration Scripts

Note: Check the header of each SQL script for more detailed information and examples.

Creating the Data Dictionary

The data dictionary is automatically created when a database is created. Thereafter, whenever the database is in operation, Oracle7 updates the data dictionary in response to every DDL statement.

The data dictionary base tables are the first objects created in any Oracle database. They are created and must remain in the SYSTEM tablespace. The data dictionary base tables are present to store information about all user-defined objects in the database.

During database creation, the initialization parameter INIT_SQL_FILES specifies the names of script files that are run immediately following database creation. These SQL scripts create the data dictionary and other important structures.

The initialization parameter INIT_SQL_FILES can also specify other files to run during database creation, after the data dictionary is created. These other files may create site-specific tables. You must specify the names of your files after the default filenames, as in the following example:

INIT_SQL_FILES = (CATALOG.SQL, CATPROC.SQL ACME_DBA.SQL)

In this example, ACME_DBA.SQL is an additional file to run during database creation.

The default filenames specified by INIT_SQL_FILES vary by operating system. See your operating system specific Oracle documentation for the default filenames.

These initial SQL files do the following tasks:

3. Load data into some data dictionary tables. Table 6 - 1 lists the scripts that are required for the Oracle7 Server with the indicated options. The appropriate scripts for your Oracle7 Server options are run automatically when you create a database. They are described here because you might need to run them again, when upgrading to a new release of Oracle7. Your release notes and Oracle7 Server Migration indicate when this is necessary. Run these scripts connected to the Oracle7 Server as the user SYS.

For more information about scripts with names starting with DBMS, see the Oracle7 Server Application Developer's Guide.

The exact names and locations of these scripts are operating system dependent. See your operating system specific Oracle documentation for the names and locations on your system.

Script Name Requires Options Description
CATALOG.SQL none Creates the data dictionary and public synonyms for many of its views, and grants PUBLIC access to the synonyms; also calls the scripts CATAUDIT.SQL, CATESP.SQL, and CATLDR.SQL
CATAUDIT.SQL none Creates the database audit train and views (This is run automatically by CATALOG.SQL; it can be removed by running CATNOAUD.SQL)
CATEXP.SQL none Creates data dictionary tables for Import/EXPORT (This is run automatically by CATALOG.SQL.)
CATLDR.SQL none Creates views for using SQL*Loader (This is run automatically by CATALOG.SQL)
CATNOREP.SQL none Remove the objects created by CATREPAD.SQL
CATNOSVM.SQL none Drops all objects created by CATSVRMG.SQL
CATPARR.SQL Parallel Server Creates data dictionary views on Parallel Server information
CATPROC.SQL none Runs all scripts required for or used with PL/SQL: CATPRC.SQL, CATRSNAP.SQL, CATRPC.SQL, STANDARD.SQL, DBMSSTDX.SQL, PIPDL.SQL, PIDIAN.SQL, DIUTIL.SQL, PISTUB.SQL, DBMSSNAP.SQL, DBMSLOCK.SQL, DBMSPIPE.SQL, DBMSALRT.SQL, SBMSOTPT.SQL, DBMSDESC.SQL
CATPRC.SQL none Creates data dictionary views for stored procedures, packages, and database triggers (This is run automatically by CATPROC.SQL)
CATREPAD.SQL none Creates the views and tables required for Symmetric Replication in Server Manager
CATRPC.SQL distributed Creates data dictionary views on distributed database information (This is run automatically by CATPROC.SQL; it requires CATPRC.SQL.)
CATSNAP.SQL distributed Creates data dictionary structures for storing and maintaining snapshots (This is run automatically by CATPROC.SQL; it requires CATPRC.SQL.)
CATSVRMG.SQL none Creates the views used by Server Manager (This is run automatically by CATPROC.SQL)
CATTRUST.SQL Trusted Oracle7 Defines structures needed for the Trusted Oracle7 Server
DBMSALRT.SQL none Allows users and applications to use event alerters (This is run automatically by CATPROC.SQL; it requires PISTUB.SQL.)
DBMSDESC.SQL none Creates a package that allows you to describe the arguments and return values of program units (This is run automatically by CATPROC.SQL; it requires PISTUB.SQL.)
DBMSLOCK.SQL none Allows users and applications to send Oracle* Mail messages (This is run automatically by CATRPOC.SQL; it requires PISTUB.SQL. You must run it on the sending database, and run TULMAIL.SQL on the receiving database.)
DBMSMAIL.SQL none Allows users and applications to send Oracle7*Mail messages (This requires PISTUB.SQL. You must run it on the sending database, and run UTLMAIL.SQL on the receiving Oracle7*Mail database.)
DBMSOTPT.SQL none Allows application developers to receive I/O from procedures. (This is run automatically by CATPROC.SQL; it requires PISTUB.SQL.)
DBMSPIPE.SQL none Allows sessions in the same instance to communicate with each other (This is run automatically by CATPROIC.SQL; it requires PISTUB.SQL.)
DBMSSNAP.SQL distributed Creates procedures for administering snapshots (This is run automatically by CATPROC.SQL; it requires CATSNAP.SQL, and you must run it on both the snapshot and master table nodes.)
DBMSSTDX.SQL none Includes extensions to the package standard (This is run automatically by CATPROC.SQL; it requires STANDARD.SQL.)
DBMSUTIL.SQL none Creates utilities that can be called from within procedures (this is run automatically by CATPROC.SQL; it requires PICTUB.SQL.)
DIUTIL.SQL none Creates PL/SQL packages for the none option (This is run automatically by CATPROC.SQL; it requires PIDIAN.SQL.)
PIDIAN.SQL none Creates PL/SQL packages for the none option (This is run automatically by CATPROC.SQL; it requires DBMSSTDX.SQL.)
PIPIDL.SQL none Creates PL/SQL packages for the none option (This is run automatically by CATPROC.SQL; it requires DBMSSTDX.SQL.)
PISTUB.SQL none Creates PL/SQL packages for the none option (This is run automatically by CATPROC.SQL; it requires DIUTIL.SQL.)
SQL.BSQ none Database bootstrap script.
STANDARD.SQL none Creates PL/SQL packages for the none option (This is run automatically by CATPROC.SQL; it requires CATPRC.SQL; it requires STANDARD.SQL.)

Table 6 - 1. (continued) Required SQL Scripts

Creating Additional Data Dictionary Structures

Oracle supplies other scripts with the Oracle7 Server that create additional structures you can use in managing your database and creating database applications. These scripts are listed in Table 6 - 2.

The exact names and locations of these scripts are operating system dependent. See your operating system specific Oracle documentation for the names and locations on your system.

Script Name Options Required Run By Description
CATBLOCK.SQL none SYS Creates the view BLOCKING_LOCKS, which shows which locks are blocking the system
CATNOAUD.SQL none SYS Removes the database audit trail created by CATAUDIT.SQL, including its data and views
CATNOPRC.SQL none SYS Removes data dictionary structures that were created by CATPRC.SQL
UTLBSTAT.SQL none any user Begins collecting performance tuning statistics (end with UTLESTAT.SQL.)
UTLCHAIN.SQL none any user Creates tables for storing the output of the ANALYZE command with CHAINED ROWS option
UTLDIDXS.SQL none any user Displays the results of running the scripts UTLIDXSS.SQL and UTLIDXSO.SQL
UTLDTREE.SQL none any user Creates tables and views that show dependencies between objects
UTLESTAT.SQL none any user Ends collecting of performance tuning statistics started by UTLBTAT.SQL
UTLEXCPT.SQL none any user Creates the default table (EXCEPTIONS) for storing exceptions from enabling constraints
UTLLOCKT.SQL none SYS Displays a lock wait-for graph, in tree structure format
UTLMAIL.SQL none SYS Allows Oracle7*Mail database to receive messages from procedures (run on Oracle7*Mail database; requires that you run DBMSMAIL.SQL on the database that will send messages)
UTLMONTR.SQL none SYS Grants access to all performance tables used by Server Manager Monitors to PUBLIC group, including access to Monitors
UTLOIDXS.SQL none any user, Runs UTLIDXSS.SQL on multiple columns
UTLSAMPL.SQL none any user Creates sample tables, such as EMP and DEPT, and users, such as SCOTT
UTLSIDXS.SQL none any user Computes the selectivity of a column, and tests whether an index created on the column would be appropriate
UTLXPLAN.SQL none any user Creates the table PLAN_TABLE, which holds output from the EXPLAIN PLAN command

Table 6 - 2. (continued) Additional SQL Scripts

Migration Scripts

The scripts in Table 6 - 3 are useful when migrating to another version or release.

For more information, see Oracle7 Server Migration.

Script Name Options Required Run By Description
CATALOG6.SQL none SYS Creates the view BLOCKING_LOCKS, which shows which locks are blocking the system
CATDBSYN.SQL none user with access to data dictionary tables Creates views for using the Version 6 Export utility with Oracle7
CATEXP6.SQL none SYS Creates views for using the Version 6 Export utility with Oracle7
DROPCAT6.SQL none SYS Removes the Version 6 views and restores the data dictionary to full Oracle7 form.
UTLEXP6.SQL none SYS Returns a list of objects not exported by SQL*Net export of a Version 6 database

Table 6 - 3. Migration SQL Scripts

Prev Next

Library

Product

Contents

Index

Script Name	Requires Options	Description
CATALOG.SQL	none	Creates the data dictionary and public synonyms for many of its views, and grants PUBLIC access to the synonyms; also calls the scripts CATAUDIT.SQL, CATESP.SQL, and CATLDR.SQL
CATAUDIT.SQL	none	Creates the database audit train and views (This is run automatically by CATALOG.SQL; it can be removed by running CATNOAUD.SQL)
CATEXP.SQL	none	Creates data dictionary tables for Import/EXPORT (This is run automatically by CATALOG.SQL.)
CATLDR.SQL	none	Creates views for using SQL*Loader (This is run automatically by CATALOG.SQL)
CATNOREP.SQL	none	Remove the objects created by CATREPAD.SQL
CATNOSVM.SQL	none	Drops all objects created by CATSVRMG.SQL
CATPARR.SQL	Parallel Server	Creates data dictionary views on Parallel Server information
CATPROC.SQL	none	Runs all scripts required for or used with PL/SQL: CATPRC.SQL, CATRSNAP.SQL, CATRPC.SQL, STANDARD.SQL, DBMSSTDX.SQL, PIPDL.SQL, PIDIAN.SQL, DIUTIL.SQL, PISTUB.SQL, DBMSSNAP.SQL, DBMSLOCK.SQL, DBMSPIPE.SQL, DBMSALRT.SQL, SBMSOTPT.SQL, DBMSDESC.SQL
CATPRC.SQL	none	Creates data dictionary views for stored procedures, packages, and database triggers (This is run automatically by CATPROC.SQL)
CATREPAD.SQL	none	Creates the views and tables required for Symmetric Replication in Server Manager
CATRPC.SQL	distributed	Creates data dictionary views on distributed database information (This is run automatically by CATPROC.SQL; it requires CATPRC.SQL.)
CATSNAP.SQL	distributed	Creates data dictionary structures for storing and maintaining snapshots (This is run automatically by CATPROC.SQL; it requires CATPRC.SQL.)
CATSVRMG.SQL	none	Creates the views used by Server Manager (This is run automatically by CATPROC.SQL)
CATTRUST.SQL	Trusted Oracle7	Defines structures needed for the Trusted Oracle7 Server
DBMSALRT.SQL	none	Allows users and applications to use event alerters (This is run automatically by CATPROC.SQL; it requires PISTUB.SQL.)
DBMSDESC.SQL	none	Creates a package that allows you to describe the arguments and return values of program units (This is run automatically by CATPROC.SQL; it requires PISTUB.SQL.)
DBMSLOCK.SQL	none	Allows users and applications to send Oracle* Mail messages (This is run automatically by CATRPOC.SQL; it requires PISTUB.SQL. You must run it on the sending database, and run TULMAIL.SQL on the receiving database.)
DBMSMAIL.SQL	none	Allows users and applications to send Oracle7Mail messages (This requires PISTUB.SQL. You must run it on the sending database, and run UTLMAIL.SQL on the receiving Oracle7Mail database.)
DBMSOTPT.SQL	none	Allows application developers to receive I/O from procedures. (This is run automatically by CATPROC.SQL; it requires PISTUB.SQL.)
DBMSPIPE.SQL	none	Allows sessions in the same instance to communicate with each other (This is run automatically by CATPROIC.SQL; it requires PISTUB.SQL.)
DBMSSNAP.SQL	distributed	Creates procedures for administering snapshots (This is run automatically by CATPROC.SQL; it requires CATSNAP.SQL, and you must run it on both the snapshot and master table nodes.)
DBMSSTDX.SQL	none	Includes extensions to the package standard (This is run automatically by CATPROC.SQL; it requires STANDARD.SQL.)
DBMSUTIL.SQL	none	Creates utilities that can be called from within procedures (this is run automatically by CATPROC.SQL; it requires PICTUB.SQL.)
DIUTIL.SQL	none	Creates PL/SQL packages for the none option (This is run automatically by CATPROC.SQL; it requires PIDIAN.SQL.)
PIDIAN.SQL	none	Creates PL/SQL packages for the none option (This is run automatically by CATPROC.SQL; it requires DBMSSTDX.SQL.)
PIPIDL.SQL	none	Creates PL/SQL packages for the none option (This is run automatically by CATPROC.SQL; it requires DBMSSTDX.SQL.)
PISTUB.SQL	none	Creates PL/SQL packages for the none option (This is run automatically by CATPROC.SQL; it requires DIUTIL.SQL.)
SQL.BSQ	none	Database bootstrap script.
STANDARD.SQL	none	Creates PL/SQL packages for the none option (This is run automatically by CATPROC.SQL; it requires CATPRC.SQL; it requires STANDARD.SQL.)

Script Name	Options Required	Run By	Description
CATBLOCK.SQL	none	SYS	Creates the view BLOCKING_LOCKS, which shows which locks are blocking the system
CATNOAUD.SQL	none	SYS	Removes the database audit trail created by CATAUDIT.SQL, including its data and views
CATNOPRC.SQL	none	SYS	Removes data dictionary structures that were created by CATPRC.SQL
UTLBSTAT.SQL	none	any user	Begins collecting performance tuning statistics (end with UTLESTAT.SQL.)
UTLCHAIN.SQL	none	any user	Creates tables for storing the output of the ANALYZE command with CHAINED ROWS option
UTLDIDXS.SQL	none	any user	Displays the results of running the scripts UTLIDXSS.SQL and UTLIDXSO.SQL
UTLDTREE.SQL	none	any user	Creates tables and views that show dependencies between objects
UTLESTAT.SQL	none	any user	Ends collecting of performance tuning statistics started by UTLBTAT.SQL
UTLEXCPT.SQL	none	any user	Creates the default table (EXCEPTIONS) for storing exceptions from enabling constraints
UTLLOCKT.SQL	none	SYS	Displays a lock wait-for graph, in tree structure format
UTLMAIL.SQL	none	SYS	Allows Oracle7Mail database to receive messages from procedures (run on Oracle7Mail database; requires that you run DBMSMAIL.SQL on the database that will send messages)
UTLMONTR.SQL	none	SYS	Grants access to all performance tables used by Server Manager Monitors to PUBLIC group, including access to Monitors
UTLOIDXS.SQL	none	any user,	Runs UTLIDXSS.SQL on multiple columns
UTLSAMPL.SQL	none	any user	Creates sample tables, such as EMP and DEPT, and users, such as SCOTT
UTLSIDXS.SQL	none	any user	Computes the selectivity of a column, and tests whether an index created on the column would be appropriate
UTLXPLAN.SQL	none	any user	Creates the table PLAN_TABLE, which holds output from the EXPLAIN PLAN command