Oracle Database glossary
Structured Query Language (Structured Query Language) --- Structured Query Language
VPD (Virtual Private Database) --- virtual private database
OAS (Oracle Application Server) --- Oracle application Program server
synonym --- synonym
instance --- instance
SGA (system global area) --- system global zone
RAC (Oracle Real Application cluster) --- Oracle Real-time application cluster
relational table --- relational table
object-relational table --- object relational table
index-organized table --- index orchestration table
external table --- External table
partitioned table --- partitioned table
materialized view --- materialized view
temporary table --- temporary table
clustered table --- cluster Table
dropped table --- deleted table
flashback query --- flashback query
B *-tree index --- B * tree index
bitmap index --- bitmap index
reverse key index --- reverse key-value index
function-based index --- function-based index
OMF (Oracle managed file) --- Oracle hosted file
quota --- storage quota
ASM (Automatic Storage Management) --- Automatic Storage Management
undo segment --- undo Data Segment
Aum (automatic undo Management) --- automatic revocation management
recycle bin --- recycle bin
Oracle Database system is a complex software system. Without understanding its internal structure principles and relationships, it is impossible to design and Compile High-Quality application software systems or manage a complex application system. To lay a solid foundation for subsequent chapters, this chapter briefly describes the system structure of Oracle 8/Oracle8i databases.
§ 2. 1 terminology
L database Block)
The minimum storage and processing unit in the Oracle database, including the header information data of the block or PL/SQLCode.
The size of the Oracle block can be specified by selecting "custom installation" during installation, or when creating a database instance in create database. The minimum value is 2 K and the maximum value is 64 K.
A part that limits system performance.
L object-oriented Relational Database
It has all the functions of relational databases and supports object-oriented databases, called object-oriented relational database systems. Oracle7 is a fully functional relational database system; oracle8 is an object-oriented relational database system.
L Client/Server structure)
It has three elements: client, server, and network. The client (PC) connects and works with the server through the network.
L fat client)
Generally, clients in the Client/Server structure are fat clients. Because these clients need to configure high devices, such as memory, hard disk, clock speed, and cd_rom.
L thin client)
Also known as NC (Network Computer), it is a kind of memory configuration that is small (usually only 4 MB in the past, which cannot be defined now), no hard disk, only a processor of the processing chip.
L Online Data Analysis and Processing (OLAP)
It is a technology that provides quick, interactive, and convenient insight into data conditions. Such as Oracle Express, Cognos (Canada) power play, Business Objects business object, etc.
L multithreading (MTS)
A user can run multiple programs to access the database at the same time. For example, Visual C/C ++ can start multiple processes at the same time, but Visual Basic cannot.
L Data Warehouse)
Data warehousing or data warehousing is supported to store a large amount of data information. When several small databases are integrated into a large database and serve a wide range of organizations, if the database stores historical data, it provides decision-making support, data aggregation, and read-only data, in essence, it acts as a data receiver for all the finished databases that provide data to it. Therefore, it is usually called a data warehouse.
It is a part of the computer memory required to access the Oracle database and assists in processing background processes.
L object (objects)
Is a software representation that implements world entities, such as tables, indexes, views, synonyms, and processes.
L database file)
Files used to store data (Application System data) in the Oracle system. (<Oracle8 beginner's guide> the examples in p14 are inaccurate and easy to mislead readers ).
L control file)
Oracle stores some data files used by the system. For example, the path and file name of the data file, the initial file path and file name are all stored in the control file.
L logs file)
It is also called a transaction record file, which records all modification operations or transactions performed by transactions on the database.
L DBA (Database Administrator-Database Administrator)
It is a technical tool or personnel for managing database systems. It provides DBA functions in a complete database system.
L dirty data block)
The raw data stored in the memory has been modified, and the modified data has not been written into the database.
L hot data block)
Stores frequently-modified data called Hot data blocks.
L LRU (least recently used-least recently used)
An Oracle SystemAlgorithmFor data blocks (memory blocks) that are not used in the memory but recently called LRU, Oracle will remove the data from the memory based on the LRU and free up space to load other data.
Space created and allocated for storing specialized data.
L table space)
Is a collection of one or more data files. All data objects are stored in the specified tablespace, but they are mainly stored in tables, so they are called tablespaces.
Recover the changed user (or transaction) to the last state.
L undo Information)
The information used for recovery is called recovery information. Rollback is performed based on the recovery information.
2. 2 Oracle Database Structure
The Oracle database structure is the main part of the Oracle database system. It is a visible software, including:
L Oracle Data Dictionary
L tablespace and data files
L Oracle instance)
§ 2. 2.1 Oracle Data Dictionary
An important part of Oracle databases is data dictionary. It stores the information used by the Oracle database. It is a set of read-only tables for users. The data dictionary includes:
L information of all schema objects in the database, such as tables, views, clusters, and indexes.
L how much space is allocated and how much space is currently used.
The default value of column L.
L constraints integrity of information.
L Oracle user name.
L permissions granted to users and roles.
Audit information accessed or used by the user.
L other generated database information.
A database data dictionary is a group of tables and view structures. They are stored in the system tablespace.
The database data dictionary is not only the center of each database. It is also very important to every user. You can use SQL statements to access the database data dictionary.
§ 2. 2.2 tablespace and data files
Simply put, a tablespace is a set of one or more data files (physical files) (logical files ). The following table space is generally used:
1) system tablespace)
It is an essential part of different versions of Oracle. It stores some information about the Oracle system and generally only has one system tablespace.
2) Temporary tablespace (temporary tablespace)
Since Oracle often requires some temporary disk space when working, these space is mainly used for queries with sorting (group by, order by, etc.) algorithms, and will be released immediately after use, the information recorded in the disk area is no longer used, so it is called a temporary tablespace. Generally, there is only one temp temporary tablespace after installation.
3) Tools tablespace)
Used to save the database objects required by the Oracle database tools. These tools store tablespaces in dedicated tables used during work. The tablespace is not available in the old Oracle version. The tool tablespace after installation is tools.
4) User tablespace (users tablespace)
It is used to store the space used in some experiment examples, and of course it can also be used to store the data of the application system. The old version does not have users tablespace. The current user tablespace is users.
5) data and index tablespace
Table spaces that store data and indexes are promoted in application systems after oracle7, especially systems with large amounts of data. Data and indexes should be stored separately in different partitions, this improves the query speed. After installation, the index tablespace may be indx. The data table space is users.
6) rollback segment table space (rollback segment)
The Oracle database system requires a disk space to store the Undo information when executing insert, update, and delete operations, so that the original information can be read during recovery. The tablespace In the rollback segment after installation is the RBS tablespace. The database administrator can also create another tablespace for the rollback segment based on the needs of the application.
A database is divided into one or more logical components, which are called tablespaces. The tablespace is also divided into logical parts called segments. These segments are further subdivided into extents ).
§ 2. 2.3 Oracle instances
An Oracle instance is composed of a group of background processes and memory structures. Each running database system is related to an instance. Therefore, Oracle instances are sometimes called database operating systems. The following is an explanation of the Oracle instance.
Oracle has two types of processes: Server process and background process ).
An Oracle server process is a transaction that processes user-to-instance connections. The task is:
L analyze and execute the SQL statements issued by the application.
L read necessary data from the data file to the shared data zone in the SGA area.
L return necessary information to the application.
The Oracle system uses some additional processes to handle the necessary work of the system. These processes are called background processes:
L database write (dbw0 or dbwn)
L log write (lgwr)
L detection site (ckpt)
L System Monitoring (SMON)
L Process Monitoring (pmon)
L archive (arcn)
L recovery (reco)
L lock (lck0)
L work queue (snpn)
L queue monitoring (qmnn)
L DISPATCHER (dnnn)
L server (snnn)
Oracle memory structure (SGA)
The SGA structure is:
L Data High-Speed Buffer
L redo log Buffer
L sharing pool
L large sharing pool (optional)
L Data Dictionary Buffer
L other data buffers
§ 2. 3 Oracle Database Files
Oracle Database is a complex database operating system consisting of a series of components, including: 1) initialization parameter files, 2) data files, 3) control files, and 4) log files.
§ 2. 3.1 initialize the parameter file
The initialization parameter file (initsid. ora) is the main configuration point of Orale RDBMS. It is a set of configuration parameters and values. Each parameter value controls or modifies a certain aspect of the database and instance. In earlier versions, all parameters were written in initsid. ora file, but after oracle8, most parameters are no longer in this file. to modify these parameters, you can only use set session or set System in SQL>. View the availability of system parameters. For example:
SQL> select * from V $ parameter;
The Oracle initialization parameter file is a editable text file. It is accessed when the database instance is started to obtain the path of data files, log files, control files, and so on. In addition, the initialization parameter file also provides the parameters used by the instance.
For the initsid. ora file, see the appendix.
§ 2. 3.2 data files
It is used to store data files of all databases. Multiple necessary data files are automatically created during Oracle installation. These data files are used to store basic data of the Oracle system. During application system development, we can create some data files as needed.
If data files are classified based on their data types, they can be divided:
L user data
The data stored in the application system is user data.
L System Data
The management of user data and the data of the database system, such as the data dictionary, the name and type of the table created by the user, are all recorded in the system data.
§ 2. 3.3 Control File
The control files are internal binary files accessed by the Oracle database instance at startup. Their storage paths are determined by the control_files = parameter of the parameter file.
Oracle generally has two or more control files, and each control file record has the same information. If an error occurs in a control file during database operation, Oracle will automatically use another control file. The system cannot work when all control files are corrupted.
L The Oracle database must have at least one control file;
L after the general database system is installed, more than two control files are automatically created;
L It is generally recommended to create multiple control files for security purposes;
L The control file can be found using the following command:
Select name, value from V $ parameter where name like 'control _ FILES ';
§ 2. 3.4 redo log files
Redo log files are an important file in the Oracle system. Features:
L redo logs are Oracle journals that record all user objects or system changes;
L after the installation is complete, there are multiple redo log files, which are divided into several groups, and the size of the redo log files in the group should be the same;
L create multiple Log File groups for better system performance;
L you can run the following command to find the redo log file:
Select * from V $ logfile;
§ 2. 3.5 Other supported files
In addition to the above three types of files, there are:
L sqlnet. ora file;
L tnsnames. ora file;
L listener. ora file.
§ 2.4 redo log and redo log Modes
Redo logs, log files, and redo daily work modes are important concepts in the Oracle system. administrators must fully understand their principles and usage methods. The following is a simple concept. The related management is provided in another chapter.
§ 2. 4.1 redo log
All operations performed by Oracle are recorded in the log file. The Oracle system does not save the disk immediately (write data files) For Every modification of a record, but only makes modification records, online redo logs save all the changes. When all the modifications are finally written to the data file, all the modifications are still recorded in the online redo log, which will facilitate the recovery of these transaction records. However, if the log is not redone online, only some recent operations can be restored.
§ 2. 4.2 redo log Working Mode
Oracle can work in one of two modes:
1) archivelog mode (recoverable)
Oracle generally has at least two log files. They are written in turn with all the changes made. When the system is set to the recovery mode, Oracle automatically backs up the log information to be overwritten (washed out) each time, and then records the modified information on it. In this way, you can use backup and recovery to recover data in a certain period of time.
2) noarchivelog mode (limited recovery)
The default value is noarchivelog. Oracle does not retain the old redo log information (you can overwrite the original basic information ). Therefore, it is generally irrecoverable.
§ 2. 5 data blocks, intervals, and segments
§ 2. 5.1 data blocks)
L ORACLE data blocks are also called Oracle blocks;
L when creating a tablespace, the Oracle System formats the data file into several Oracle blocks;
L each Oracle block is the minimum Unit processed by the Oracle system;
L The Block Size is determined during system installation. You can select "custom installation" to set the block size;
L The block size cannot be changed once determined;
The size of the L Block ranges from 2 K to 64 K;
Run the following command to check the block size:
Select name, value from V $ parameter where name like 'db _ block_size ';
§ 2. Range 5.2 (extent)
L any continuous block allocated to an object (such as a table) is called an interval;
L an interval is also called an extension, because when it runs out of the allocated interval, a new record insertion must be performed in a new interval (that is, to expand some blocks );
L The interval size is determined by next;
L once an interval is assigned to an object (table, index, and cluster), the interval cannot be assigned to other objects;
L how many zones can an object be used? Run the following command to check:
Select segment_name, tablespace_name, count (*) from dba_extents
Having count (*)> 1 group by segment_name, tablespace_name;
Section 2 (segment)
L segment refers to the general name that occupies the data file space or the set of space used by the database object;
L segments can include table segments, index segments, rollback segments, temporary segments, and high-speed cache segments;
Segment L uses several Oracle blocks in the same tablespace (which can be located in different data files ).
Create Table ABC (empno number (4), ename varchar2 (20), Sal number (9, 2 ))
Tablespace user_data storage (initial 500 K next 256 k pctincrease 0 );
1) segment allocated = initial interval = 500 K;
2) 256 K will be allocated after the first 500 k is used up; this time period = 256 K + K;
3) if the allocated range is used up, 256 K will be allocated ,...
§ 2. 6 Oracle Database Process
Generally, when the database is started successfully (the instance is started successfully), at least six background processes are active. These processes are divided according to the needs of the database. They are:
1) database writer (dbwr)
The database writer task writes the modified (in memory) data blocks back to the database file. In some operating systems, Oracle can have two bdwr processes.
2) Check Point (ckpt)
Is an optional process. When a data query request occurs during system operation, the system finds the data from the database and stores the data in the memory zone. In this way, you can modify the data in the memory zone. When you need to write the modified data back to the data file, a switch is generated for the redo log, and the verification point appears. The system writes the information in the gray data (modified) block in the memory back to the data file on the disk. In addition, the system will redo the log notification control file. DBA can change the checkpoint_process true parameter file to enable (make valid or invalid) the process.
3) log writer (lgwr)
The process used to write log information in the SGA area to the log file. Generally, the user's modification value is first recorded in the log file. The modification results will be written back to the data file after a certain period of time.
4) System Monitor (SMON)
System monitor is a mandatory process for recovery when the database system is started. For example, in Parallel Server mode (two servers share one disk group), SMON can restore another database that is in failure. Switch the system to another normal server.
5) Process Monitor (pmon)
Process Monitor is used to terminate failed users and release resources occupied by the user.
6) archive (ARCH)
Optional process. used when the database system is in archivelog mode.
7) Lock (lckn)
Optional process. Multiple locked processes can appear in Parallel Server mode to facilitate database communication.
Optional processes used in distributed databases (with different machines and different Oracle Systems in different locations) mode for data inconsistency recovery. Before the reco resolves the restoration, the mark of the modified data is marked as "suspicious ".
9) Scheduling (dnnn)
Optional process, used in multiple threads, that is, for each in use (d000 ,..., dnnn) Communication Protocol creates at least one scheduling process. Each scheduling process is responsible for routing requests from the connected user process to the available server process. Return the response to the appropriate user process.
10) snapshot process (snpn)
The Snapshot process processes the automatic refresh of database snapshots and runs the predefined database process through the dbms_job package. initsid. the ora parameter job_queue_process sets the number of snapshot processes. The parameter job_queue_interval determines the number of seconds for the snapshot process to sleep before being awakened to process suspended jobs or transactions.
11) parallel query process (pnnn)
The Oracle server can start or stop query processes based on the database activity parallel query options. These processes involve the creation of parallel indexes, table creation, and query. The number of started instances is the same as the number specified by parallel_min_servers. The number cannot exceed the value specified by this parameter.
§ 2. 7 Oracle Memory Structure
As mentioned above, Oracle database instances are composed of a group of background processes and memory structures. The memory structure includes:
L system global area)
L program Global Area)
§ 2. 7.1 system global Zone
The Oracle System is a storage area used to store system information. Both user processes and Oracle background processes can use SGA. SGA contains many components (different parts ).
L data buffer cache)
Data blocks recently used by the Oracle system are stored in the data high-speed buffer zone (that is, the user's high-speed buffer zone). When writing data to the database, it reads and writes data blocks, when the data high-speed buffer is filled up, the system automatically removes some infrequently accessed data. If the data you want to query is not in the Data High-speed buffer, Oracle automatically reads data from the disk. The data high-speed buffer zone includes three types:
1) Dirty partition (dirty buffers): contains data blocks that have been changed and need to be written back to the data file.
2) Free Zone (free buffers): it does not contain any data and can be written again. Oracle can read data blocks from data files.
3) Retention zone (pinned buffers): This zone contains a zone that is being processed or explicitly reserved for future use.
After Oracle8i, the buffer pool will be divided into three zones (when multiple buffer pool features are used ):
1) Keep Buffer Pool: data blocks are retained in the memory and will not be squeezed out from the memory;
2) The recycle buffer pool removes data from the unwanted memory;
3) The default buffer pool contains allocated blocks.
L redo log buffer (RaDO log buffer)
Any transaction must be put in the redo log buffer before it is recorded to the redo log (online redo log is required for recovery. The log is then written to the process (lgwr) to regularly write the content in the buffer to the redo log.
L shared pool)
A shared pool is a reserved area of SGA and is used to store stored procedures and packages such as SQL and PL/SQL, data dictionaries, locks, character set information, and security attributes. The shared pool includes:
1) Library cache );
2) dictionary high-speed buffer (Dictionary cache ).
L library Cache)
The zone includes:
1) shared SQL zone (shared pool area );
2) Private SQL area (Private SQL area );
3) PL/SQL stored procedures and packages (PL/SQL procedure and Package );
4) control structure ).
That is to say, this area contains SQL statements that have been analyzed by syntax and are ready to be executed at any time.
L dictionary high-speed buffer (data dictionary cache)
It is used to store all the information required by the Oracle System Management. This information is the username used to log on to Oracle. These users have database objects and their locations.
Svrmgr> show SGA
Total system global area 35544188 bytes
Fixed size 22208 bytes
Variable Size 3376332 bytes
Database buffer 122880 bytes
Redo buffer 32768 bytes
The last four items are added to the previous one.
You can also use select * from V $ SGA; to query the SGA of the current instance.
L large pool)
A large pool in SGA is an optional buffer. It can be configured with management right as needed. It provides a large zone for backup and recovery operations such as databases.
2) program global zone (PGA)
PGA is the memory area used by Oracle. It can only be stored and controlled by a single process at a time, used to store session variables and internal arrays.
§ 2. 7.2 global procedures
PGA is the memory area used by Oracle. It can only be stored and controlled by a single process at a time, used to store session variables and internal arrays.
§ 2. 8 Oracle instances
An Oracle instance is composed of SGA, background processes, and data files. Each database has its own SGA and an independent Oracle process set. :
In the case of distribution, Oracle uses a SID (system identifer) to identify the names of each Oracle server so that the names of different database systems are not confused, in UNIX environments, the oracle_sid variable is used to distinguish between them.
§ 2. 9 multi-threaded server (MTS)
Multi-threaded ORACLE Server (multithreader server) allows multiple connections to the database to fully share memory and resources, which allows a small amount of memory to support more users.
All processes connected to the Oracle database occupy a certain amount of memory. Therefore, if too many processes connect to Oracle, a performance bottleneck occurs.
Oracle8 allows more than 10 thousand users to connect to Oracle at the same time, but not all users use MTS. Currently, some 4gl tools do not support MTS, such as VB and Pb, and VC/C ++ does not support MTS. The Oracle multi-thread server has its own connection pool (that is, the Shared Server process ). Because users share open connections, this is much faster than the original dedicated method (eliminating bottlenecks ).
Multithreading is very suitable for some specialized application systems, such as the order registration system, where the customer submits the order, and the data of the recorded employee's order; another recorder is negotiating with the customer, not all data is being input (dedicated server processes are idle ). These terminals are forced to connect to the system and occupy the resources of other users.
Multi-threaded servers eliminate these shortcomings. A multi-threaded server only maintains one connection pool. When a terminal needs to talk to the system, it can be allocated a connection. You can remove it if you do not need it. In this way, the system resources are shared by multiple users.
Change the relevant parameters in the parameter file to make the system a multi-threaded server configuration (effective after restart ). In addition, the database instance must provide the same number of users as the number of users placed.
§ 2. 10 Oracle transaction processing process
Bank withdrawal process:
1. Issue the SQL statement for querying the remaining amount, such:
Select account_balance from banktable
Where account_number = '123'
And account_type = 'savings ';
L The SQL statement uses SGA to obtain the server process;
L the server process checks whether the statement exists in the Shared Pool. If no such statement exists, it is placed in the Shared Pool and ready to run;
L execute SQL statements to read the high-speed data buffer zone of SGA from the data file of the remaining data blocks;
L display results. For example, the remaining amount is $325.
2. Withdrawal $25: The SQL statement is:
Update bank_table set account_balanct = 300
Where account_number = '123'
And account_type = 'savings ';
L The client process transmits the SQL statement to the server process through SGA;
L check whether the server process has this statement and executes it (;
L analyze SQL statements and store them in the shared pool;
L execute SQL statements;
L is the data to be processed in the Data High-speed buffer? Yes to 7;
L read data blocks from data files to high-speed data buffer;
L record the original value ($325) in the rollback segment );
L generate a copy of the transaction in the redo log;
L change the balance in the Data High-speed buffer to $300;
L bank teller machines send work completion signals through SGA (submitted ):
L record completed transactions in the redo log;
L clear the Undo information in the rollback segment );
L The customer has obtained the money.
§ 2. 11 sys and system modes of the Oracle System
Sys and system are two accounts installed by default on each Oracle database system. Sys is the owner of all internal database tables, structures, process packages, and so on. It also has V $ and data dictionary views, and creates all encapsulated database roles (dBA, connect, resource ). Sys is the only user who can access a specific internal data dictionary. System is also created when oracle is installed for DBA task management.
The default password after sys is installed is change_on_install, and the default password for system is Manager. For security, after installation is complete. Use the alter user SYS identified by PASSWORD command to modify the passwords of these two privileged accounts.
§ 2. 12 Oracle system tracking File
All Oracle databases have at least one file used to record system information, errors, and major events. this file is called alertsid. log (the Sid here is the System ID of Oracle), where the storage location is initsid. the background_dump_dest parameter of the ora file is provided.
Both the background process and the user process can create their own tracking files. The background process tracking file location is given by the background_dump_dest parameter, and the user tracking file location is given by the user_dump_dest parameter. For example, the parameter file initora8.ora provides:
# Define directories to store trace and alert files
Background_dump_dest = D: \ oracle \ admin \ ora8 \ bdump
User_dump_dest = D: \ oracle \ admin \ ora8 \ udump
The background trace file is named sidproc. TRC.
§ 2. 13 ORACLE System Data Dictionary
Data dictionary is the knowledge base for all objects stored in the database. The Oracle database system uses the data dictionary to obtain object information and security information, users and DBAs use it to view database information. The data dictionary stores information about data objects and segments. Such as tables, views, indexes, packages, processes, and information about users, permissions, roles, and audits. A data dictionary is a read-only object and cannot be modified by anyone.
§ 2. 14 other data objects
In addition to the preceding data objects, Oracle also includes views, sequences, synonyms, triggers, database chains, packages, processes, and functions. The following is a description.
§ 2. 14.1 View
View is the SQL statement stored in the database for query. It is mainly for two reasons:
Security reasons: the view can hide some data, such as the Social Insurance Fund table. You can use the view to show only the name and address, but not the social insurance number and wage number, another reason is that complex queries are easy to understand and use.
§ 2. Sequence 14.2
A sequence is a database object used to generate unique digital data. A sequence is created with an initial value, an increment value, a maximum value, and a maximum of 38 integers.
§ 2. 14.3 trigger
A trigger is a special stored procedure. Its execution is not called by a program, nor is it manually started, but triggered by an event, for example, when you perform operations (insert, delete, update) on a table, it is activated for execution. Triggers are often used to enhance data integrity constraints and business rules. The trigger can be found in the dba_triggers and user_triggers data dictionary.
§ 2. 14.4 Synonyms
Synonym is a database pointer to other database tables. There are two types of synonyms: private and public ). Private synonyms are created in the specified mode and accessed only in the mode used by the creator. Public synonyms are accessed in the mode specified by public, and can be accessed by all database modes (users.
§ 2. 14.5 database chain
Database link is a storage definition for connecting to a remote database. It is used to query remote users in a distributed database environment. Because they are stored in the dba_db_links data dictionary, they can be considered as a database object type.