Oracle database management script naming specification
- 2020-05-24 06:23:17
- OfStack
The ORACLE tutorial you are looking at is :Oracle database management script naming specification. The data dictionary in Oracle is a very important part of the database and provides information about the relevant database. It is a very important source of information for all Oracle users, from the end user of the application to the designer of the application to the database administrator. The creation of the data dictionary was completed by running some related database management scripts after the Oracle database was created. When a database is created using the Oracle unified 1 installer (Oracle Universal Installer), the dictionary views and scripts associated with the Oracle server are eventually installed automatically. But when you need to upgrade your current Oracle database server, the database administrator must manually rerun SQL, which created these views or scripts. Once the database is installed, the Oracle database management script is stored in the operating system. For the UNIX and NT operating systems, they are located in different physical directories, as shown in table 1:
Table 1 physical locations of database management scripts in the operating system
Almost all the system administration scripts in Oracle are stored in this directory, which contains hundreds of different files. What do these different files do? This article discusses in detail the naming conventions that make up the Oracle database management scripts. This allows the user to see a file and roughly know what the main purpose of the file is. The files in this directory can be divided into four categories according to different functions. In order to distinguish these different types of files easily, Oracle follows the rules set by 1 for the naming of files. See table 2.
1, cat*.sql script
This type of script is primarily used to create the data dictionary view. Among them, the scripts catalog.sql and catproc.sql are the two scripts that must be run after the database is created. The functional descriptions of these two scripts are shown in table 3.
Table 3 script description for creating the data dictionary view
(1) catalog sql
This script creates views of system dynamic performance relative to the system base table and their synonyms. This script also runs the script that creates other objects at the same time, mainly including:
Basic PL/SQL environment, including PL/SQL declarations:
- data type
- predefined exceptions
- built-in procedures and functions
-SQL operations, etc
The audit
Import/export
SQL * Loader
Installation options
(2) catproc sql
This script is mainly used to set up a usage environment for PL/SQL functions. In addition, several PL/SQL packages were created to extend the RDBMS functionality. The script also creates additional packages and views for the following RDBMS features:
Warning (Alerts)
Piping (Pipes)
Log analysis (LogMiner)
Large object (Large objects)
Object (Objects)
Advanced queue (Advanced queuing)
Copy options (Replication option)
Other built-in packages and options (Other built-ins and options)
(3) other scripts
There are other scripts in this directory that extend the Oracle database server functionality. For example, the catadt.sql script is used to create the data dictionary view used in RDBMS to display Megabyte data information. 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 objects that extend the Oracle server's capabilities with one of the Oracle packages predefined by the system. These programs are used to perform database administration tasks. Most SQL scripts are executed when the catproc.sql script is run. 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 a Shared pool and to reduce the size of the Shared pool fragments it can be marked as either kept or deleted in SGA.
3, utl*.sql script
This type of script needs to be run only if the database needs additional views and tables. For example, the script utlxplan.sql is used to create a table that is used to observe the run plan of the SQL statement (Execution Plan).
It is important to note that most database administration scripts must be run under user SYS. If the database administrator intends to run these scripts, it is a good idea to first read the information in the content of the scripts to see what users should run the scripts.
Table 1 physical locations of database management scripts in the operating system
Almost all the system administration scripts in Oracle are stored in this directory, which contains hundreds of different files. What do these different files do? This article discusses in detail the naming conventions that make up the Oracle database management scripts. This allows the user to see a file and roughly know what the main purpose of the file is. The files in this directory can be divided into four categories according to different functions. In order to distinguish these different types of files easily, Oracle follows the rules set by 1 for the naming of files. See table 2.
[NextPage]
Here's a detailed look at the four different types of scripting capabilities:
1, cat*.sql script
This type of script is primarily used to create the data dictionary view. Among them, the scripts catalog.sql and catproc.sql are the two scripts that must be run after the database is created. The functional descriptions of these two scripts are shown in table 3.
Table 3 script description for creating the data dictionary view
(1) catalog sql
This script creates views of system dynamic performance relative to the system base table and their synonyms. This script also runs the script that creates other objects at the same time, mainly including:
Basic PL/SQL environment, including PL/SQL declarations:
- data type
- predefined exceptions
- built-in procedures and functions
-SQL operations, etc
The audit
Import/export
SQL * Loader
Installation options
(2) catproc sql
This script is mainly used to set up a usage environment for PL/SQL functions. In addition, several PL/SQL packages were created to extend the RDBMS functionality. The script also creates additional packages and views for the following RDBMS features:
Warning (Alerts)
Piping (Pipes)
Log analysis (LogMiner)
Large object (Large objects)
Object (Objects)
Advanced queue (Advanced queuing)
Copy options (Replication option)
Other built-in packages and options (Other built-ins and options)
(3) other scripts
There are other scripts in this directory that extend the Oracle database server functionality. For example, the catadt.sql script is used to create the data dictionary view used in RDBMS to display Megabyte data information. 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 objects that extend the Oracle server's capabilities with one of the Oracle packages predefined by the system. These programs are used to perform database administration tasks. Most SQL scripts are executed when the catproc.sql script is run. 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 a Shared pool and to reduce the size of the Shared pool fragments it can be marked as either kept or deleted in SGA.
3, utl*.sql script
This type of script needs to be run only if the database needs additional views and tables. For example, the script utlxplan.sql is used to create a table that is used to observe the run plan of the SQL statement (Execution Plan).
It is important to note that most database administration scripts must be run under user SYS. If the database administrator intends to run these scripts, it is a good idea to first read the information in the content of the scripts to see what users should run the scripts.
On 1 page