Introduction to Oracle Table spaces and data files

First, the concept

Tablespace: is a logical collection of one or more data files

Table Space Logical Storage objects: Permanent segment--> such as tables and indexes

Temporary segment--> such as temporary table data and sorted segments

Rollback segment--> The undo data used for things to roll back or flash back memory

Table Space Categories: system table spaces (systems, sysaux), non-system table spaces

A table space contains at least one data file, and a data file can belong to only one table space.

A few of the necessary table spaces:

SYSTEM---> Dictionary table space, cannot be corrupted

UNDO--->dml,dql data snapshot to this, data submission disappears (for recovery)

Sysaux--->10g high concurrent systems are busy, causing system contention, putting tools to sysaux, reducing system pressure, sysaux not impacting systems (impacting performance)

Temp---> Temporary data-related content

Users--->10g user data from System dial out

Second, Oracle storage structure

1.Schema: User---> Create related objects, tables, views, sequences, functions, stored procedures, packages, etc.

2. Logical Structure: Database Databases--->tablespace table space---> Segment segment--->extent interval----> block

Logical structure is how Oracle internally manages objects in the database

3. Physical structure: OS block--->datafile physical structure is usually a series of data files

4. Describe the organization of the Scott user-created object.

--View the user's default table space and data files for Scott

Sql> Select Username,default_tablespace,temporary_tablespace

2 from Dba_users

3 WHERE username = ' SCOTT ';

USERNAME Default_tablespace Temporary_tablespace

------------------------------ ------------------------------ ------------------------------

SCOTT USERS TEMP

sql> Col name format A50

Sql> Select T1.name,t2.name--View tablespace and data files

2 from V$tablespace t1,v$datafile T2

3 where t1.ts# = t2.ts#;

Name Name

-------------------------------------------------- --------------------------------------------------

System/u01/app/oracle/oradata/orcl/system01.dbf

Undotbs1/u01/app/oracle/oradata/orcl/undotbs01.dbf

Sysaux/u01/app/oracle/oradata/orcl/sysaux01.dbf

Users/u01/app/oracle/oradata/orcl/users01.dbf

Example/u01/app/oracle/oradata/orcl/example01.dbf

/*

Scott-->emp (table)--> data definition (located)-->system table space

Data (located)-->user table space (logical storage)--> table segment--> interval--> memory block

--> Index segment--> interval--> memory block

User table space (physical storage)-->user01.dbf

--> using local management, including header information, available, used bitmap information

Call the DBWR process to write data to the physical file when the DataBuffer buffer is full * *

/*

Third, create a table space

--Brief syntax:

CREATE [Bigfile | Smallfile] [temporary] tablespace tablespace name

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/