6
SQL Scripts

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

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, Oracle 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.

Table 6-1 lists the scripts that are required for the Oracle Server with the indicated options. The appropriate scripts for your Oracle 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 Oracle8. Your release notes and Oracle8 Migration indicate when this is necessary. Run these scripts connected to the Oracle Server as the user SYS.

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.

For more information about scripts with names starting with DBMS, see the Oracle8 Administrator's Guide.

Script Name Needed For Description

CATALOG.SQL

All databases

Creates the data dictionary and public synonyms for many of its views, and grants PUBLIC access to the synonyms

CATPROC.SQL

All databases

Runs all scripts required for or used with PL/SQL. It is required for all Oracle8 databases.

Script Name	Needed For	Description
CATALOG.SQL	All databases	Creates the data dictionary and public synonyms for many of its views, and grants PUBLIC access to the synonyms
CATPROC.SQL	All databases	Runs all scripts required for or used with PL/SQL. It is required for all Oracle8 databases.

Creating Additional Data Dictionary Structures

Oracle supplies other scripts with the Oracle 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 Needed For Run By Description

CATBLOCK.SQL

Performance
Management

Must be run when connected to SYS

Creates views that can dynamically display lock dependency graphs

CATEXP7.SQL

Exporting data to Oracle7

Must be run when connected to SYS

Creates the dictionary views needed for the Oracle7 Export utility to export data from Oracle8 in Oracle7 Export file format

CATHS.SQL

Heterogeneous
Services

Must be run when connected to SYS

Installs packages for administering heterogeneous services.

CATIO.SQL

Performance
Management

Must be run when connected to SYS

Allows I/O to be traced on a table-by-table basis

CATOCTK.SQL

Security

Must be run when connected to SYS

Creates the Oracle Cryptographic Toolkit package

CATPARR.SQL

Parallel Server

SYS or SYSDBA

Creates parallel server data dictionary views.

CATREP.SQL

Advanced Replication

Must be run when connected to SYS

Runs all SQL scripts for enabling database replication.

CATRMAN.SQL

Recovery Manager

RMAN or any user with grant_recovery_catalog_owner role

Creates recovery manager tables and views (schema) to establish an external recovery catalog for the backup, restore and recovery functionality provided by the Recovery Manager (RMAN) utility

DBMSIOTC.SQL

Storage Management

any user

Analyzes chained rows in index-organized tables

DBMSOTRC.SQL

Performance
Management

SYS or SYSDBA

Used to enable and disable Oracle Trace trace generation

DBMSPOOL.SQL

Performance
Management

SYS or SYSDBA

Enables DBA to lock PL/SQL packages, SQL statements, and triggers into the shared pool

USERLOCK.SQL

Concurrency Control

SYS or SYSDBA

Provides a facility for user-named locks that can be used in a local or clustered environment to aid in sequencing application actions.

UTLBSTAT.SQL and UTLESTAT.SQL

Performance
Monitoring

SYS

Respectively start and stop collecting performance tuning statistics

UTLCHAIN.SQL

Storage Management

any user

Creates tables for storing the output of the ANALYZE command with CHAINED ROWS option

UTLCONST.SQL

Year 2000 Compliance

any user

Provides functions to validate CHECK constraints on date columns are year 2000 compliant

UTLDTREE.SQL

Metadata Management

any user

Creates tables and views that show dependencies between objects

UTLEXCPT.SQL

Constraints

any user

Creates the default table (EXCEPTIONS) for storing exceptions from enabling constraints

UTLHTTP.SQL

Web Access

SYS or SYSDBA

PL/SQL package retrieve data from Internet or intranet web servers via HTTP protocol

UTLLOCKT.SQL

Performance
Monitoring

SYS or SYSDBA

Displays a lock wait-for graph, in tree structure format

UTLPG.SQL

Data Conversion

SYS or SYSDBA

Provides a package that converts IBM/370 VS COBOL II

UTLPWDMG.SQL

Security

SYS or SYSDBA

Creates PL/SQL function for default password complexity verification. Sets the default password profile parameters and enables password management features

UTLSAMPL.SQL

Examples

SYS or any user with DBA role

Creates sample tables, such as EMP and DEPT, and users, such as SCOTT

UTLSCLN.SQL

Advanced Replication

any user

Copies a snapshot schema from another snapshot site

UTLTKPROF.SQL

Performance
Management

SYS

Creates the TKPROFER role to allow the TKPROF profiling utility to be runs by non-DBA users

UTLVALID.SQL

Partitioned Tables

any user

Creates table required for storing output of ANALYZE TABLE ...VALIDATE STRUCTURE of a partitioned table.

UTLXPLAN.SQL

Performance
Management

any user

Creates the table PLAN_TABLE, which holds output from the EXPLAIN PLAN command

Script Name	Needed For	Run By	Description
CATBLOCK.SQL	Performance Management	Must be run when connected to SYS	Creates views that can dynamically display lock dependency graphs
CATEXP7.SQL	Exporting data to Oracle7	Must be run when connected to SYS	Creates the dictionary views needed for the Oracle7 Export utility to export data from Oracle8 in Oracle7 Export file format
CATHS.SQL	Heterogeneous Services	Must be run when connected to SYS	Installs packages for administering heterogeneous services.
CATIO.SQL	Performance Management	Must be run when connected to SYS	Allows I/O to be traced on a table-by-table basis
CATOCTK.SQL	Security	Must be run when connected to SYS	Creates the Oracle Cryptographic Toolkit package
CATPARR.SQL	Parallel Server	SYS or SYSDBA	Creates parallel server data dictionary views.
CATREP.SQL	Advanced Replication	Must be run when connected to SYS	Runs all SQL scripts for enabling database replication.
CATRMAN.SQL	Recovery Manager	RMAN or any user with grant_recovery_catalog_owner role	Creates recovery manager tables and views (schema) to establish an external recovery catalog for the backup, restore and recovery functionality provided by the Recovery Manager (RMAN) utility
DBMSIOTC.SQL	Storage Management	any user	Analyzes chained rows in index-organized tables
DBMSOTRC.SQL	Performance Management	SYS or SYSDBA	Used to enable and disable Oracle Trace trace generation
DBMSPOOL.SQL	Performance Management	SYS or SYSDBA	Enables DBA to lock PL/SQL packages, SQL statements, and triggers into the shared pool
USERLOCK.SQL	Concurrency Control	SYS or SYSDBA	Provides a facility for user-named locks that can be used in a local or clustered environment to aid in sequencing application actions.
UTLBSTAT.SQL and UTLESTAT.SQL	Performance Monitoring	SYS	Respectively start and stop collecting performance tuning statistics
UTLCHAIN.SQL	Storage Management	any user	Creates tables for storing the output of the ANALYZE command with CHAINED ROWS option
UTLCONST.SQL	Year 2000 Compliance	any user	Provides functions to validate CHECK constraints on date columns are year 2000 compliant
UTLDTREE.SQL	Metadata Management	any user	Creates tables and views that show dependencies between objects
UTLEXCPT.SQL	Constraints	any user	Creates the default table (EXCEPTIONS) for storing exceptions from enabling constraints
UTLHTTP.SQL	Web Access	SYS or SYSDBA	PL/SQL package retrieve data from Internet or intranet web servers via HTTP protocol
UTLLOCKT.SQL	Performance Monitoring	SYS or SYSDBA	Displays a lock wait-for graph, in tree structure format
UTLPG.SQL	Data Conversion	SYS or SYSDBA	Provides a package that converts IBM/370 VS COBOL II
UTLPWDMG.SQL	Security	SYS or SYSDBA	Creates PL/SQL function for default password complexity verification. Sets the default password profile parameters and enables password management features
UTLSAMPL.SQL	Examples	SYS or any user with DBA role	Creates sample tables, such as EMP and DEPT, and users, such as SCOTT
UTLSCLN.SQL	Advanced Replication	any user	Copies a snapshot schema from another snapshot site
UTLTKPROF.SQL	Performance Management	SYS	Creates the TKPROFER role to allow the TKPROF profiling utility to be runs by non-DBA users
UTLVALID.SQL	Partitioned Tables	any user	Creates table required for storing output of ANALYZE TABLE ...VALIDATE STRUCTURE of a partitioned table.
UTLXPLAN.SQL	Performance Management	any user	Creates the table PLAN_TABLE, which holds output from the EXPLAIN PLAN command

The "NO" Scripts

The scripts in Table 6-3 are used to remove dictionary information for certain optional services or components.

Script Name Needed For Run By Description

CATNOADT.SQL

Objects

Must be run when connected to SYS

Drops views and synonyms on dictionary metadata that relate to Object types

CATNOAUD.SQL

Security

Must be run when connected to SYS

Drops views and synonyms on auditing metadata

CATNOHS.SQL

Heterogeneous Services

Must be run when connected to SYS

Removes Heterogeneous Services dictionary metadata

CATNOPRT.SQL

Partitioning

Must be run when connected to SYS

Drops views and synonyms on dictionary metadata that relate to partitioned tables and indexes

CATNOQUEUE
.SQL

Advanced Queuing

Must be run when connected to SYS

Removes Advanced Queuing dictionary metadata

CATNORMN.SQL

Recovery
Manager

Owner of recovery catalog

Removes recovery catalog schema

CATNOSVM.SQL

Server Manager

Must be run when connected to SYS

Removes Oracle7 Server Manager views and synonyms

CATNOSNMP.SQL

Distributed Management

SYS

Drops the DBSNMP user and SNMPAGENT role

Script Name	Needed For	Run By	Description
CATNOADT.SQL	Objects	Must be run when connected to SYS	Drops views and synonyms on dictionary metadata that relate to Object types
CATNOAUD.SQL	Security	Must be run when connected to SYS	Drops views and synonyms on auditing metadata
CATNOHS.SQL	Heterogeneous Services	Must be run when connected to SYS	Removes Heterogeneous Services dictionary metadata
CATNOPRT.SQL	Partitioning	Must be run when connected to SYS	Drops views and synonyms on dictionary metadata that relate to partitioned tables and indexes
CATNOQUEUE .SQL	Advanced Queuing	Must be run when connected to SYS	Removes Advanced Queuing dictionary metadata
CATNORMN.SQL	Recovery Manager	Owner of recovery catalog	Removes recovery catalog schema
CATNOSVM.SQL	Server Manager	Must be run when connected to SYS	Removes Oracle7 Server Manager views and synonyms
CATNOSNMP.SQL	Distributed Management	SYS	Drops the DBSNMP user and SNMPAGENT role

For more information, see Oracle8 Migration.

Migration Scripts

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