【OH】常用資料字典指令碼說明 SQL Scripts

lhrbest發表於2016-05-12


來源於   ,主要對一些資料字典指令碼進行說明。


Database Reference

B SQL Scripts

This appendix describes SQL scripts that are required for optimal operation of the Oracle Database.

The SQL scripts are described in the following sections:


Creating the Data Dictionary

When you use the Database Configuration Assistant to create a database, Oracle automatically creates the data dictionary. 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 in the SYSTEM tablespace and must remain there. The data dictionary base tables store information about all user-defined objects in the database.

 lists required scripts, which are run automatically when you create a database using the Database Configuration Assistant. They are described here because you might need to run them if you create a database manually. To run these scripts, you must be connected to Oracle as a user with SYSDBA privileges.

Table B-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

Grants PUBLIC access to the synonyms

catproc.sql

All databases

Runs all scripts required for, or used with, PL/SQL

catclust.sql

Real Application Clusters

Creates Real Application Clusters data dictionary views




Creating Additional Data Dictionary Structures

Oracle supplies other scripts that create additional structures you can use in managing your database and creating database applications. These scripts are listed in .

See Also:

Your operating system-specific Oracle documentation for the exact names and locations of these scripts on your operating system

Table B-2 Creating Additional Data Dictionary Structures

Script Name Needed For Run By Description

catblock.sql

Performance management

SYS

Creates views that can dynamically display lock dependency graphs

catexp7.sql

Exporting data to Oracle7

SYS

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

caths.sql

Heterogeneous Services

SYS

Installs packages for administering heterogeneous services

catio.sql

Performance management

SYS

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

catoctk.sql

Security

SYS

Creates the Oracle Cryptographic Toolkit package

catqueue.sql

Advanced Queuing

 

Creates the dictionary objects required for Advanced Queuing

catrep.sql

Oracle Replication

SYS

Runs all SQL scripts for enabling database replication

dbmsiotc.sql

Storage management

Any user

Analyzes chained rows in index-organized tables

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

utlchn1.sql

Storage management

Any user

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

utlconst.sql

Year 2000 compliance

Any user

Provides functions to validate that CHECKconstraints on date columns are year 2000 compliant

utldtree.sql

Metadata management

Any user

Creates tables and views that show dependencies between objects

utlexpt1.sql

Constraints

Any user

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

utlip.sql

PL/SQL

SYS

Used primarily for upgrade and downgrade operations. It invalidates all existing PL/SQL modules by altering certain dictionary tables so that subsequent recompilations will occur in the format required by the database. It also reloads the packages STANDARD andDBMS_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 some data dictionary tables. Then it reloads the packagesSTANDARD and DBMS_STANDARD, which are necessary for using PL/SQL. Finally, it triggers a recompilation of all PL/SQL modules, such as packages, procedures, and types.

utllockt.sql

Performance monitoring

SYS or SYSDBA

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

utlpwdmg.sql

Security

SYS or SYSDBA

Creates PL/SQL functions 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, and types.

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

Oracle Replication

Any user

Copies a snapshot schema from another snapshot site

utltkprf.sql

Performance management

SYS

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

utlvalid.sql

Partitioned tables

Any user

Creates tables required for storing output ofANALYZE TABLE ...VALIDATE STRUCTUREof a partitioned table

utlxplan.sql

Performance management

Any user

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




The "NO" Scripts

The scripts in  are used to remove dictionary information for various optional services or components.

See Also:

 for more information about these scripts

Table B-3 The NO Scripts

Script Name Needed For Run By Description

catnoadt.sql

Objects

SYS

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

catnoaud.sql

Security

SYS

Drops views and synonyms on auditing metadata

catnohs.sql

Heterogeneous Services

SYS

Removes Heterogeneous Services dictionary metadata

catnoprt.sql

Partitioning

SYS

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

catnosvm.sql

Server Manager

SYS

Removes Oracle7 Server Manager views and synonyms

catnsnmp.sql

Distributed management

SYS

Drops the DBSNMP user and SNMPAGENT role



Upgrade and Downgrade Scripts

The scripts in  are used when upgrading or downgrading to another release of Oracle. To run these scripts, you must be connected to Oracle as a user with SYSDBA privileges.

Table B-4 Upgrade and Downgrade Scripts

Script Name Needed For Description

catdwgrd.sql

Downgrading

Provides a direct downgrade path from the new Oracle Database 11g release

catupgrd.sql

Upgrading

Provides a direct upgrade path to the new Oracle Database 11g release

utlu112i.SQL

Pre-Upgrade Information

Analyzes the database to be upgraded, detailing requirements and issues for the upgrade to Oracle Database 11g Release 2 (11.2)

utlu112s.SQL

Post-Upgrade Status

Displays the component upgrade status after an upgrade to Oracle Database 11g Release 2 (11.2)




Java Scripts

The scripts in  are useful only if the JServer option is installed.

Table B-5 Java Scripts

Script Name Description

initjvm.sql

Initializes JServer by installing core Java class libraries and Oracle-specific Java classes

rmjvm.sql

Removes all elements of the JServer

catjava.sql

Installs Java-related packages and classes


About Me



...............................................................................................................................................................................

本文來自於oracle官方文件,地址:

本文地址: http://blog.itpub.net/26736162/viewspace-2098205

ITPUB BLOG:http://blog.itpub.net/26736162

QQ:642808185 若加QQ請註明您所正在讀的文章標題

【版權所有,文章允許轉載,但須以連結方式註明源地址,否則追究法律責任】

...............................................................................................................................................................................


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2098205/,如需轉載,請註明出處,否則將追究法律責任。

相關文章