Oracle Database (i)

I. Oracle functional dependency and Paradigm theory 1. Function dependency

1) Non-trivial dependencies: in the Relationship R (U), for subsets X and Y of U, if X->y,y does not belong to a subset of X, it is called X->y is a non-trivial dependency.

2) Ordinary dependence: On the contrary is the ordinary dependence.

3) Full function dependency: If x->y, for a subset of any x x ' exists x '-\>y, then the x->y is called a complete function dependency.

4) Partial function dependency: If x->y, for a subset of any x x '-\>y does not exist, then the x->y is called a complete function dependency.

5) Transfer function dependency: If X->y,y->z and y-\>x,z do not belong to X, the Z transfer function is called dependent on X.

2. Paradigm Theory

The first paradigm: if the properties of each relationship R of the relationship pattern R are indivisible, R is the pattern of the first paradigm.

The second paradigm: R is already the first paradigm, and each non-primary property of the relationship R relies entirely on the candidate key, which is called R as the second paradigm.

The third paradigm: R is already the second paradigm, and the non-principal property of the relationship R does not have a transfer function dependency on any candidate keys, then R is the third normal.

Two. Oracle database Architecture 1. Storage structure

The storage structure is divided into physical storage structure and logical storage structure.

The physical storage structure has data files, log files, control files, etc., data files store database data and indexes, etc., log files record database data or database structure modified information, control files used to store data files and log file information, is a very small binary file, Used to describe and maintain the physical structure of a database.

The logical storage structure has table spaces, segments, extents, and data blocks.

2.oracle Memory Structure

The memory structure is divided into the system global Area (SGA) and the program Global Area (PGA), the system is called the shared area, which can be shared by multiple processes in the system, consisting of the shared pool, Java pool, large pool, data buffer and log buffer, and the shared pool holds the most recent PL/SQL statements, the statements and dictionaries. Java pools are used to support the operation of the Java language, such as writing a stored procedure in the Java language, Oracle's virtual opportunity calls the Java pool to process Java stored procedures, a backup of large pools of data, a data buffer to store data read from disk, and a log buffer to store information about the database being modified.

The program global area is the private memory area that the system allocates to a process, which stores variables and arrays.

3.ORACLE process Structure

After Oracle starts, multiple Oracle background processes are launched to perform different tasks, common processes are DBWN processes, database write processes, LGWR processes, log write processes, ckpt processes, checkpoint processes, Smon processes, system monitoring processes, Pmon processes, Process monitoring process, ARCN process, archive process, reco process, recovery process.

4. Data dictionary

The data dictionary stores the knowledge base of all object information in the database, and can obtain object information and security information through the data dictionary.

Common dictionaries: Dba_tables, dba_views.

Three. Sqlplus Common commands

Describe view the structure of the table:

Desc Scott.emp used to query the EMP table structure of a Scott user

LIST: All rows of the buffer.

Save: Saves the contents of the buffer to a file. SAVE Scott_emp.sql

START: Reads and runs the file contents. START Scott_emp.sql

Four. Managing table Spaces 4.1 creating a tablespace

creat tablespace MySpace

DataFile ' c:\...\ ... '

Size 5M

Autoextend on Next 5M

MaxSize 20M;

4.2 Status properties for table spaces

1.online

2.offline

3.only Read

4.read Write

Oracle Database (i)