Oracle8 Reference Release 8.0 A58242-01 |
Library | Product | Contents | Index

# 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:

- Creating the Data Dictionary
- Creating Additional Data Dictionary Structures
- The "NO" Scripts
- 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, 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.

## 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

## 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

For more information, see Oracle8 Migration.

## Migration Scripts

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

For more information, see Oracle8 Migration.

Script Name | Needed For | Run By | Description
--- | --- | --- | ---
CAT8000.SQL | Migration from Oracle7 | SYS or SYSDBA | Creates new Oracle8 dictionary metadata
CATREP8M.SQL | Advanced Replication | SYS | Loads replication packages/views and adjusts 7.3 replication-specific packages/views
DROPCAT6.SQL | Removing legacy metadata | SYS | Drops the Oracle6 data dictionary catalog views
DROPCAT5.SQL | Removing legacy metadata | SYS | Drops the Oracle5 data dictionary catalog views

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index