Oracle logical Storage Structure

Data Block-"area-" segment-"tablespace-" Database

I. tablespace

1. tablespaces are divided into system tablespaces and non-system tablespaces.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/210T53238-0.jpg "alt =" 2012070821320748.jpg"/>

Small file tablespace: the default System tablespace. System tablespace, and sysaux tablespace.

Large file tablespace: designed for ultra-large databases and only contains one big data file. The file size is 128 kb <size <TB.

Data file management can be simplified, SGA needs can be reduced, and control files can be reduced.

Table space management includes:

Create a tablespace

Modify tablespace

Tablespace backup

Management of large file tablespaces

2.Extended tablespaceIn what ways?

1). Add data files to the tablespace

SQL> ALTERTABLESPACEORCLTBS1ADDDATAFILE 'D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ ORCLTBS1_2.DBF 'size10m;

Add a new 10 mb data file for the tablespace name ORCLTBS1 in the orcl database.

2). Change the data file size

SQL> ALTERDATABASEDATAFILE 'D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ ORCLTBS1_2.DBF'RESIZE20M;

Increase ORCLTBS1_2.DBF to 20 MB.

3). Allow automatic expansion of data files

SQL> ALTERDATABASEDATAFILE 'D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ ORCLTBS1_2.DBF' AUTOEXTENDONNEXT5MMAXSIZE100M;

3. Modify Table space availability

SQL> ALTERTABLESPACE... OFFLINE | ONLINE;

SQL> ALTERTABLESPACEORCLTBS1OFFLINE;

Set tablespace ORCLTBS1 to OFFLINE status

SQL> ALTERTABLESPACEORCLTBS1ONLINE;

Set tablespace ORCLTBS1 to ONLINE

4. Rename the tablespace:

ALTERTABLESPACEORCLTBS1RENAMETOORCLTBS1_NEW;

5. tablespace backup

SQL> ALTERTABLESPACEORCLTBS1BEGINBACKUP;

Copy ORCLTBS1 tablespace data files ORCLTBS1_1.dbf and ORCLTBS1_2.dbf to the target location.

SQL> ALTERTABLESPACEORCLTBS1ENDBACKUP;

6. delete a tablespace

1). Except system tablespace and sysaux tablespace, other tablespaces can be deleted.

2). Once the tablespace is deleted, all data in the tablespace will be permanently lost.

3) if the data in the tablespace is being used or the tablespace contains rollback information for uncommitted transactions, the tablespace cannot be deleted.

SQL> DROPTABLESPACEORCLTBS1INCLUDINGCONTENTS;

7. Large file tablespace operations

Since a large file tablespace contains only one data file, you can operate on the table space to perform transparent operations on the data file.

Change the size or scalability of a tablespace. Change the size and scalability of a data file.

1). Change the data file size

SQL> ALTERTABLESPACEORCLTBS1RESIZE20M;

Increase ORCLTBS1_2.DBF to 20 MB.

2). Allow automatic expansion of data files

SQL> ALTERTABLESPACEORCLTBS1AUTOEXTENDONNEXT5MMAXSIZEUNLIMITED;

Ii. Data blocks

Data Block: 1 ).Input and OutputMinimum unit

2) It is the smallest logical storage unit in the database and the smallest unit for the database to perform input/output operations. It consists of one or more operating system blocks.

3. Zone

ZONE: a logical storage unit consisting of a series of continuous data blocks.Storage space allocation and recoveryThe smallest unit.

Zone management mainly refers to: Allocation and recovery.

When creating a database object, oracle allocates several partitions for the object to form a segment to provide the initial storage space for the object.

1. Zone allocation

1). Auto scaling is used for zone allocation.

SQL> CREATETABLESPACEORACLTBS6DATAFILE 'D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ ORCLTBS1_2.DBF 'size20m EXTENTMANAGEMENTLOCALAUTOALLOCATE;

Local management and automatic Scaling

2). Fixed area allocation size, 5 MB for each area

SQL> CREATETABLESPACEORACLTBS7DATAFILE 'D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ ORCLTBS1_2.DBF 'size10m EXTENTMANAGEMENTUNIFORMSIZE5M;

2. zone recovery

Generally, the partition allocated to a segment is kept in the segment, regardless of whether the data block in the partition is used. only when the object to which the segment belongs is deleted will all the segments be recycled.

Iv. Section

A segment is a logical storage unit consisting of one or more consecutive or discontinuous zones. A segment isUnit of the tablespaceIndicates the data storage structure of a specific data type.

UsuallyA database object has only one segment,A segment must contain at least one partition..

1). Data Segment

2). Index segment

3). Temporary Section

4). rollback segment: Used for the value before the data is modified.

Supports transaction rollback, database recovery, database read consistency, and Flash-back queries.