Overview of Oracle's overall Knowledge (1)-Concepts

Table Space :

Oracle allows different types of data to be stored separately, and Tablespace is a logical division of the database.

Data Files :

A tablespace consists of one or more files on the same disk, which are called data files.

Example :

is the software mechanism for storing and controlling databases.

When Oracle launches an instance, it needs to read the Init.ora file .

Oracle has a physical structure, a logical structure .

Physical Structure :

Made up of 4 types of files

Data files

Log file, with recovery function

Control file, library name, location of data files, and date of establishment

Parameter file

SGA: Global shared Zone

Includes: Shared pool, block buffer, log, dictionary buffer.

Oracle Storage Management :

Logical Structure:

Physical block, data block, section, table space, category

schema object relationship to table space :

A table space can contain multiple schema objects a schema object can be contained in multiple table spaces

Schema Object :

Includes tables, views, indexes, synonyms.

PCTfree, pctused parameters:

1. Specify when creating or modifying a table;

2. Create and modify indexes to specify only pctfree values;

in terms of SQL statements :

Build Table :

Bind primary KEY: Constraint Pk_xx primary key (xx field)

Oracle control number of rows using the RowNum keyword

the difference between delete and truncate :

Delete Deletes data, consumes rollback segments, does not free up space, and produces fragmentation; (Workaround: Pilot out, truncate and then import)

Truncate DELETE data while freeing up space

To create a table constraint :

Non-null integrity, Master code integrity, referential integrity, unique integrity, check integrity;

When you delete a primary table, if you specify an on delete cascade sentence from the table, the child table is deleted.

Database chain :

Table name @db chain name

Indexes: Index

Logical and physical independent of data;

is to increase the query speed by adding some auxiliary information to the physical storage structure;

A large number of additions/deletions, the index structure should be changed, so the data should be loaded first, then indexed;

Benefits :

Speed up the query;

Ensuring uniqueness characteristics

synonyms: Synonym

User name. Table name, synonym name

sequence: Sequence

Create sequence XXX increment by ten start with ten MaxValue cycle

Application: Xxx.currval, Xxx.nextval;

things:

A series of complex sequences of actions for database modifications are combined.

keywords: commit, rollback, savepoint, rollback to (savepoint)

PL/sql:

DDL/DML/DCL   

Permissions: Grant, REVOKE, set role

Common types of conversion functions:

to_char (): to_char (sysdate, ' yyyy-mm-dd HH24:mi:ss ');

to_date (); To_date (' 20140729 ', ' yyyymmdd ');

to_number ();

Control structure:

If then else

Loop for while

Goto