Oracle knowledge point summary (1)

Oracle knowledge point summary (1) architecture: the database architecture refers to the composition, working process and principles of the database, and the organization and management mechanism of data in the database. The architecture includes: instance, database, user process, server process ), and other files (such as parameter files, password files, and archive log files ). The Oracle server consists of database files and database instances. Database instances include SGA (a set of memory structures) and background processes for database management. Database files include data files, control files, and redo log files ). The three main memory areas in Oracle are:-system global area (SGA)-When the database instance starts, it will first be allocated-program global area (PGA) -The user global area (UGA) SGA memory structure includes: Shared Pool and Database Buffer Cache ), redo Log Buffer Cache ). The role of the Shared Pool: The Shared Pool consists of the database cache and data dictionary cache. Cache and sharing SQL or PL/SQL code; data buffer function: stores data read from data files to improve query speed; redo log buffer function: before and after the change, the data is written to the redo log buffer before the data buffer is written. In this way, Oracle knows which data needs to be rolled back during data recovery. Log Data is first generated in the redo log buffer. When the number of log data in the redo log buffer reaches a certain level, the log write process LGWR writes the log data to the redo log file. PGA is the memory area that stores private data for individual server processes. It only provides one PGA for each server process. PGA can only be accessed by their server processes. UGA is the memory area used to store session status. The location of UGA depends on whether the server is running in the Shared Server mode or the dedicated server mode. In dedicated server mode, UGA is allocated in PGA and can only be accessed by server processes. Oracle processes mainly include user processes, server processes, and background processes. A user process is a program that requires interaction with the Oracle server. The server process is like an intermediary that completes various data service requests of the user and sends the data returned by the database server to the client. Background processes are mainly used to coordinate system performance. It mainly includes process monitoring processes (PMON, server process management and maintenance), system monitoring processes (SMON, instance recovery when the database fails), Database writing processes (DBWR, it is mainly responsible for writing data in the data buffer zone to the data file), redo log write process (LGWR, mainly responsible for writing the data in the redo log buffer to the redo log file) and Checkpoint Process (CKPT, it ensures that all modified database buffers are written to database files ). The preceding five processes must also be started during database startup. Database startup and shutdown three statuses of database startup: 1: NOMOUNT only open database instance 2: MOUNT open instance and read control file 3: OPEN Database start database CLOSE in order of 123 three states: 1: CLOSE shut down database 2: DISMOUNT 3: SHUTDOWN shut down database instance transaction concept: A transaction is a logical unit of work. It consists of one or more SQL statements. Four features of transactions: atomicity, consistency, isolation, and persistence transaction control: commit and rollback SQL. SQL languages include Data Definition Language (DDL) and data manipulation language (DML) and Data Control Language (DCL ). DDL is used to define the creation and revocation operations of SQL modes, basic tables, views, and indexes. Common Commands include CREATE, ALTER, and DROP. DML is used to insert, modify, delete, and query data. Common Commands include INSERT, UPDATE, DELETE, and SELECT. DCL is used to authorize basic tables, describe integrity rules, and control transactions. Common Commands include GRANT, REVOKE, COMMIT, SAVEPOINT, and ROLLBACK. The difference between Char and varchar2: in the database, char (n) represents a string with a fixed length of n. When the actual data is insufficient to define the length, spaces are used to complete the right-side insufficiency, when the actual data length is greater than its fixed length, Oracle will not allow data to be stored in the corresponding column or variable and throw an exception. varchar2 (n) is a variable length string, it means that the maximum length of the column or variable is not greater than n, but when the actual data is less than n, spaces are not filled in the right end. The difference between delete and truncate. Delete is a DML statement. When deleting data, redo information must be recorded, and the tablespace is not released after the data is deleted, which is slow. Truncate is a data definition language. After execution, the data is directly deleted and the tablespace is released, without logging information, which is fast. Description of PL/SQL: PL/SQL is a block-structured language that stores a group of statements in one block. The basic unit that constitutes a PL/SQL program is a logical block (such as a process, function, or anonymous block). Each Logical Block corresponds to the problem or subproblem to be solved. PL/SQL blocks are divided into three parts: declarative, executable, and exception handling. Attribute data types include % TYPE and % ROWTYPE. 1.% TYPE provides variables or columns of a data TYPE to other variables. The syntax format is var1 table_name.column_name % TYPE. The variable var1 is declared, and its data TYPE is the same as that of the column_name field in the table_name table. 2.% ROWTYPE indicates the record type of a row in the table. The syntax format is var1 table_name % ROWTYPE. The variable var1 is declared and can be used to store a record extracted from the table_name table. Process, function, package process, and function are different: the process does not return a value, but the function has a return value. A package encapsulates objects such as related procedures, functions, variables, cursors, and exceptions. A package consists of a specification and a subject. The specification can exist without the package owner, and the owner cannot exist without the package specification. Four attributes of the cursor and trigger cursor: % Isopen, % found, % notfound, and % rowcount. Four steps to use a cursor: Define the cursor (declare), open the cursor (open), fetch the data (fetch), and close the cursor (close ). Differences between explicit and implicit cursors: explicit and implicit cursors are defined when a cursor is displayed. Implicit cursors are not needed. By default, implicit cursors are used for dml statements in the context. Trigger definition: A trigger is a code block automatically executed when a specific event occurs. The difference between a trigger and a process: the process is explicitly called by a user, application, or even a trigger, and the trigger is implicitly activated by Oracle Based on the event, it cannot be directly called for execution. Trigger type and running time: Oracle triggers include DML triggers, mode (DDL or user event) triggers, and database-level triggers. 1: DML trigger: executed when a DML statement occurs in the table. DML statements include the following three types: a. Statement-Level Trigger: the trigger is activated only once regardless of the number of lines affected by the trigger statement. B. Row-level triggers: A Row-Level Trigger is activated whenever the trigger statement affects the table. A Row-Level Trigger is executed once on each modified row. C. INSTEAD trigger: allows users to modify views that cannot be modified using DML statements. The INSTEAD trigger can only be used for views and cannot be used for tables. 2: mode (DDL or user event) trigger: activated when DDL statements are executed in database mode. 3: Database (System Event) triggers: in the case of LOGOFF, LOGON, STARTUP, SHUTDOWN, and SERVERERROR) and other system events. Definition of synonyms of database objects (synonyms, sequences, views, indexes): an alias of an existing object. Synonym: 1: Simplified SQL statements. 2: Hide the Object Name and owner. 3: provide public access to the object. Two types of synonyms: 1: public synonyms. CREATE public synonym emp_syn for scott. emp; 2: Private synonym. CREATE synonym emp for scott. emp; public synonyms can only be defined under the Super User. A sequence is a database object used to generate unique and continuous integers. Use sequence in the Table: 1. create sequence: create sequence sequence_name [start with startnum] [increment by step]; 2. sequence used for TABLE creation: create table table_name (uid smallint not null ,...); 3. insert into table_name VALUES (sequence_name.NEXTVAL ,...); A view is a virtual table constructed from several basic tables and (or) other views. The difference between a view and a table is that when creating a view, you only store the definition of its view in the data dictionary, instead of storing the data corresponding to the view, and the table stores the corresponding data. View purpose and function: Filter table data, simplify query statements, and protect the table. Note: adding the with read only option does not allow DML operations on The View.