The Oracle tutorial you are looking at is: Oracle database management scripting naming conventions. The data dictionary in Oracle is a very important part of the database, providing information about the relevant databases, which is a very important source of information for all Oracle users, from the end-user of the application, the design developer of the application, to the database administrator. The creation of a data dictionary is done by running some related database management scripts after the Oracle database is created. When the Oracle Unified Installer (Oracle Universal Installer) is used to create the database, the Oracle server-related dictionary views and scripts are eventually installed automatically. However, when you need to upgrade your current Oracle database server, the database administrator must manually rerun the SQL that created the views or scripts. Once the database is installed, Oracle database management scripts are stored in the operating system. For UNIX and NT operating systems, they are located in separate physical directories, as shown in table 1:
Table 1 Physical location of database management scripts in the operating system
Almost all of the system administration scripts in Oracle are saved in the file directory, which contains hundreds of different files. What is the effect of these different documents? This article discusses in detail the naming conventions that make up Oracle database management scripts. This allows a user to see a file and probably know what the main purpose of the file is. The files under this directory can be divided into 4 categories according to their functions, and Oracle follows certain rules for naming files in order to distinguish between these different types of files. See table 2 for the following.
[NextPage]
Here's a detailed description of the four different types of scripting features:
1. Cat*.sql Script
This type of script is primarily used to create a data dictionary view. where scripts Catalog.sql and Catproc.sql are two scripts that must be run after the database is created. The functional descriptions of the two scripts are shown in table 3.
Table 3 Creating a data dictionary View script description
(1) Catalog.sql
The script creates views and system dynamic performance views relative to the system base table and their synonyms. The script also runs scripts that create other objects, mainly:
Basic Pl/sql environment, including Pl/sql declaration:
-Data type
-Predefined exceptions
-Built-in processes and functions
-SQL operations, etc.
Audit
Import/Export
Sql*loader
Installation options
(2) Catproc.sql
This script is primarily used to establish the use environment for PL/SQL functionality. In addition, several pl/sql packages are created to extend the RDBMS functionality. The script also creates additional packages and views for some of the following RDBMS features:
Warning (Alerts)
Pipe (pipes)
Log analysis (Logminer)
Large object (Large objects)
Object (Objects)
Advanced Queue (Advanced queuing)
Replication options (Replication option)
Some of the other built-in packages and options (other built-ins and options)
(3) Other scripts
There are other scripts in this directory to extend the Oracle database server functionality. For example, the Catadt.sql script is used to create a data dictionary view of the RDBMS used to display the data in megabytes. Catnoadt.sql scripts are used to delete these tables and views.
2, Dbms*.sql and PRVT*.PLB scripts
These two types of scripts are used to create objects of some of the predefined Oracle packages that extend the capabilities of the Oracle server. These programs are used to complete database management 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 an object in a shared pool and can be marked for retention or deletion in the SGA in order to reduce the share pool fragment.
3. Utl*.sql Script
This type of script needs to run only if the database requires additional views and tables. For example, the script utlxplan.sql, which is used to create a table that observes the running schedule of the SQL statement (Execution plan).
It is important to note that most database management scripts must be run under User sys. If the database administrator intends to run these scripts, it is best to first read the relevant information in the script content to see what users should run the scripts.
Previous page
