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 Server 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 Server Administrator's Guide.
.
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.
For more information, see Oracle8 Server Migration.
Migration Scripts
The scripts in Table 6-4 are useful when migrating to another version or release.
For more information, see Oracle8 Server Migration.
