Oracle Database Management script

The data dictionary in Oracle is a very important part of the database and provides information about the relevant database. It is used by all oracle users (from application end users, application design developers to database administrators) is a very important source of information. The data dictionary is created by running related database management scripts after the Oracle database is created. When the Oracle universal installer is used to create a database, the dictionary view and script related to the Oracle server will be automatically installed. However, to upgrade the current Oracle database server, the database administrator must manually re-run the SQL statement used to create these views or scripts. After the database is installed, the Oracle database management script is stored in the operating system. For Unix and NT operating systems, they are located in different physical directories, as shown in table 1:

Operating System Script directory location
UNIX $ ORACLE_HOME/rdbms/admin
NT % ORACLE_HOME %/rdbms/admin

Almost all system management scripts in Oracle are stored in this file directory, which contains hundreds of different files. What are the functions of these different files? This article will discuss in detail the naming rules for Oracle database management scripts. In this way, the user can probably know the main purpose of a file. Files in this directory can be divided into four categories based on different functions. to distinguish these types of files easily, Oracle must follow certain rules for file naming. See table 2.

Description
Cat *. SQL information about directories and data dictionaries
DBMS *. Description of packages in SQL database
Prvt *. PLB encrypted package code
Utl *. Tables and views of other SQL database functions
(Table 2 naming rules for database management scripts)

The following describes the four different types of script functions:
1. Cat *. SQL script

This script is mainly used to create a data dictionary view. The scripts catalog. SQL and catproc. SQL are the two scripts that must be run after the database is created. The functions of these two scripts are shown in table 3.

Script description
Catalog. SQL creates common data dictionary views and synonyms in the system.
Catproc. SQL pl/SQL scripts required for running the server
(Table 3 script description for creating a data dictionary view)

(1) catalog. SQL
This script creates a view and System Dynamic Performance view relative to the system base table and their synonyms. This script also runs the script to create other objects at the same time, mainly including:
Basic PL/SQL environment, including PL/SQL statement:
-Data Type
-Predefined exceptions
-Built-in processes and functions
-SQL operations

Audit
Import/Export
SQL * Loader
Installation Options

(2) catproc. SQL
This script is mainly used to establish the environment for PL/SQL functions. In addition, several PL/SQL packages are created to extend the functions of RDBMS. This script also creates additional packages and views for some of the following RDBMS functions:

Warning (alerts)
Pipelines)
Logminer)
Large Object (large objects)
Object (objects)
Advanced queue)
Replication Option)
Other built-in packages and options (other built-ins and options)

(3) other scripts

There are other scripts in this directory to expand the functions of the Oracle Database Server. For example, the catadt. SQL script is used to create a data dictionary view that displays megabytes of data in RDBMS. The catnoadt. SQL script is used to delete these tables and views.

2. DBMS *. SQL and prvt *. PLB scripts

These two types of scripts are used to create Oracle package objects predefined by the system that extends the functions of the Oracle server. These programs are used to complete database management tasks. Most SQL scripts are executed when running catproc. SQL scripts. Some additional scripts must be executed separately by the database administrator. For example, the dbmspool. SQL script is used to display the size of objects in the Shared Pool. To reduce the size of fragments in the shared pool, it can be marked as "keep" or "delete" in SGA.

3. utl *. SQL script

This type of script must be run only when the database requires additional views and tables. For example, the script utlxplan. SQL is used to create a table, which is used to observe the SQL statement execution plan ).

Note that most database management scripts must be run under user SYS. If the database administrator wants to run these scripts, it is best to first read the relevant information in the script content to learn who should run these scripts.