Oracle learning notes first knowledge Oracle, oracle learning notes first Knowledge

Oracle learning notes first knowledge Oracle, oracle learning notes first Knowledge
Database Introduction

A Database is a warehouse that organizes, stores, and manages data according to the data structure. SQL is the acronym of Structured Query Language (Structured Query Language.

Definition

　　DatabaseTo put it simply, it can be regarded as an electronic file cabinet-a place for storing electronic files. Users can add, intercept, update, or delete data in files.

DatabaseIt refers to a data set that is stored together in a certain way, shared for multiple users, with the smallest possible redundancy, and independent from each other. In the daily work of economic management, it is often necessary to put some related data into such a "warehouse" and handle the data according to the management needs. For example, an enterprise or a public institution's Personnel Department often stores the basic information of its employees (employee ID, name, age, gender, nationality, salary, and resume) in the table, this table can be viewed as a database. With this "Data Warehouse", we can query the basic information of a certain employee at any time as needed, as well as the number of employees with a certain wage range. If all these tasks can be performed automatically on the computer, then our personnel management can reach an extremely high level. In addition, a large number of such databases need to be established in financial management, warehouse management, and production management so that they can use computers to achieve automatic management of finance, warehouses, and production. [From Baidu encyclopedia] Oracle Database introduction is the principle of database usage. Users send requests to database servers over the network, the database processes requests, and then return the data to users. Oracle Database Structure

　　

Oracle database structures are divided into physical and logical structures.

Physical structure: the physical component is the physical file of the operating system used by the Oracle database. We can view the files in the product \ 10.2.0 \ oradata \ orcl directory under the installation directory, put different files in different directories in the production environment, and make decisions based on the frequent user access.

　　

1) Main physical files can be divided into three types:
I. Data Files: data files are used to store database data, such as tables and index data.
Ii. Control File: the control file is a binary file that records the physical structure of the database.
Iii. Online Log File: records all changes to the database for fault recovery.
Note: if these files are lost, the database cannot continue to run and media recovery is required.

2) Non-main physical files include:
Parameter files, password files, alarm and tracking files, archived log files, and backup files.
Parameter files are divided into pfile files and spfile files, which are located in the ORACLE_HOME \ DATABASE directory. sfile is not used by 9i by default, such as spfileorcl. ora files.
Password File: sys user password, which is located in the ORACLE_HOME \ DATABASE directory, for example, pwdorcl. ora file.
Check whether archive logs exist: archive log list.
ORACLE_HOME: D: \ dev \ oracle \ product \ 10.2.0 \ db_1
Note: if these files are lost, the database can continue to run without media restoration.

　　 Logical Structure:The logical structure of a database is a logical analysis of the database.

The logical components of Oracle include: database-> tablespace-> segment-> zone-> database-> mode.

[The two paragraphs are from Ruthless articles]

Oracle instances

An Oracle instance is a collection of background processes and memory structures.

　　

 

Oracle instance memory

When a server process is started, the global zone of the program is allocated. The memory structure of Oracle includes the following two memory zones:

　　

 

　　

System global Zone

Database information is stored in SGA and shared by multiple database processes.

　　

　　Sharing pool

• The shared pool is the memory area for syntax analysis, compilation, and execution of SQL and PL/SQL programs.
• The Shared Pool consists of database cache and data dictionary cache.
• The size of the Shared Pool directly affects the database performance.

　　Data Buffer Zone

• It is used to store the data read from the disk data file, which is shared by all users.
• The server process saves the read data in the data buffer. When subsequent requests need the data, they can be found in the memory and no longer need to read the data from the disk, improving the reading speed.
• The size of the data buffer has a direct impact on the reading speed of the database.

Log Buffer

• Logs record all modification information of the database. The log information is first generated in the log buffer zone.
• When the number of log data in the log buffer reaches a certain level, the background process writes the log data to the log file.
• Relatively speaking, the log buffer has little impact on the database performance.

Program global Zone

• The program global zone (PGA) contains the data and control information required by a single server process
• PGA is automatically allocated when a user process connects to the database and creates a session. It stores the information required by each user process connected to the Oracle database.
• PGA is not a shared zone and can only be used by a single process. When a user session ends, PGA is released.

Oracle instance process

　　

The background process is set by the Oracle database to maintain optimal system performance and coordinate multiple user requests. A series of background processes are created when an Oracle instance is started.

　　

PMON process monitoring process

• Clear the faulty process.
• Release all currently suspended locks.
• Release the resources used by the faulty process.

　　SMON system monitoring process

• After the instance fails, the instance is automatically restored when the database is re-opened.
• Organize free space for data files and combine adjacent areas.
• Release temporary segments that are no longer in use.

DBWR Data Writing Process

• Manage the data buffer and keep recently used blocks in the memory.
• Write the modified buffer data to the data file.

LGWR log Writing Process

• Write the log data in the log buffer to the log file.
• The system has multiple log files. This process writes data to files cyclically.

Main Oracle components

　　

Session

• A session is a single connection between a user and an Oracle Server.
• Creates a session when a user establishes a connection with the server.
• Close the session when the user is disconnected from the server

　　

The logical structure of the Oracle logical component database is a logical analysis of the database. The logical components of Oracle include:

　　

　　

Tablespace

• A tablespace is the largest logical unit in a database. An Oracle Database contains at least one tablespace, that is, the SYSTEM tablespace named SYSTEM.
• Each tablespace is composed of one or more data files. A data file can only be associated with one tablespace.
• The tablespace size is equal to the total size of all data files that constitute the tablespace.

The syntax for creating a tablespace is:

Create tablespace tablespacename

DATAFILE 'filename' [SIZE integer [K | M]

[AUTOEXTEND [OFF | ON];

Segment

• A segment is the logical storage structure of a tablespace. A segment consists of a group of partitions.
• Based on the features of the data stored in segments, segments are divided into four types: data segments, index segments, rollback segments, and temporary segments.

Zone

• A partition allocates space for segments. It consists of consecutive data blocks.
• When all space in the section is fully used, the system automatically allocates a new area for the Section.
• A partition cannot exist across data files. It can only exist in one data file.

Data Block

• A data block is the minimum storage unit that the Oracle server can allocate, read, or write.
• The Oracle server manages the storage space of data files in data blocks.

Mode

• Mode is a general term for the database objects created by the user.
• Schema objects include tables, views, indexes, synonyms, sequences, processes, and packages.

Development Process for using Oracle databases