Oracle8i Reference
Release 8.1.5

A67790-01

Library

Product

Contents

Index

Prev Next

5
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 5-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 Oracle8i. Your release notes and Oracle8i 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 Oracle8i Administrator's Guide.

.
Table 5-1 Creating the Data Dictionary Scripts
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 Oracle8i 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 5-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.

Table 5-2 Creating Additional Data Dictionary Structures
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 Oracle8i 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  

UTLCHAIN1.SQL  

Storage Management  

any user  

For use with Oracle 8.1. Creates tables for storing the output of the ANALYZE command with the CHAINED ROWS option. Can handle both physical and logical rowids  

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  

UTLEXCPT1.SQL  

Constraints  

any user  

For use with Oracle 8.1. Creates the default table (EXCEPTIONS) for storing exceptions from enabling constraints. Can handle both physical and logical rowids  

UTLHTTP.SQL  

Web Access  

SYS or SYSDBA  

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

UTLIP.SQL  

PL/SQL  

SYS  

Used primarily for migration, upgrade, and downgrade operations. It invalidates all existing PL/SQL modules by altering certain dictionary tables so that subsequent recompilations will happen in the format required by the database. It also reloads the packages STANDARD and DBMS_STANDARD, which are necessary for any PL/SQL compilations  

UTLIRP.SQL  

PL/SQL  

SYS  

Used to change from 32-bit to 64-bit word-size or vice-versa. This script recompiles existing PL/SQL modules in the format required by the new database. It first alters certain dictionary tables. Then, it reloads the packages STANDARD and DBMS_STANDARD, which are necessary for using PL/SQL. Finally, it triggers a recompile of all PL/SQL modules, such as packages, procedures, types, etc.  

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  

UTLRP.SQL  

PL/SQL  

SYS  

Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, etc.  

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 5-3 are used to remove dictionary information for certain optional services or components.

Table 5-3 The NO Scripts
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  

CATNSNMP.SQL  

Distributed Management  

SYS  

Drops the DBSNMP user and SNMPAGENT role  

For more information, see Oracle8i Migration.

Migration Scripts

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

For more information, see Oracle8i Migration.

Table 5-4 Migration Scripts
Script Name  Needed For  Run By  Description 

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  

R070304.SQL  

Replication  

SYS  

Performs a post-CATREP.SQL replication upgrade  

RM804813.SQL  

Recovery Manager  

Owner of the Recovery Catalog Tables  

Upgrades the recovery catalog from either 8.0.4 or 8.0.5 to 8.1.3  

U703040.SQL  

Migration from Oracle7  

SYS or
SYSDBA  

Creates new Oracle8i dictionary metadata  




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index