Oracle表空间管理

Oracle磁盘管理中的最高逻辑层是表空间，Oracle11g中必须创建的4个表空间是SYSTEM,SYSAUX, TEMP, UNDOTBS1。

2 SYSTEM：存储数据字典等，pl/sql代码等。

2 SYSAUX：存储与数据库选项相关的数据

2 TEMP：用于大的排序操作

2 UNDUTBS1：为读一致性和恢复的目的，存储事务信息。

表空间的下一层是段，一个段只能驻留在一个表空间中；一个或多个区可以组成一个段，每个区只能驻留在一个数据文件中；一组连续的数据块可以组成一个区。如果要查询表空间与对应的数据文件的相关信息，可以从dba_data_files数据字典中查询表空间及其包含的数据文件，举例如下：

SQL> col tablespace_name for a10;

SQL> col file_name for a50;

SQL> col bytes for 999,999,999;

SQL>Select tablespace_name,file_name,bytes from dba_data_files order by tablespace_name;

1、SYSTEM表空间

SYSTEM表空间存放内部数据和数据字典，主要存放SYS用户的各个对象和其他用户的少量对象。例如：查询USERS表空间中存放的数据对象及其类型和拥有者。

SQL>col owner for a10;

SQL>col segment_name for a30;

SQL>col segment_type for a20;

SQL>select segment_type,segment_name,ownerfrom dba_segments where tablespace_name='USERS';

2、SYSAUX表空间

SYSAUX表空间充当SYSTEM表空间的辅助表空间，主要用于存储除数据字典以外的其他数据对象。例如，查询SYSAUX表空间所存放的用户及其所拥有的对象数量：

Select owner as 用户,count(segment_name) as 对象数量 from dba_segments wheretablespace_name='SYSAUX' group by owner;

3、创建表空间

创建表空间的语法如下：

Create [smallfile | bigfile] tablespace tablespace_name

Datafile '/path/filename' size num[k|m] reuse

['/path/filename' size num[k|m]reuse]

[, …]

[autoextend [on|off] next ] num[k|m]

[maxsize [unlimited | num[k|m]]]

[mininum extent num[k|m]]

[default storage storage]

[online | offline]

[logging | nologging]

[permanent | temporary]

[extent management dictionary | local[autoallocate | uniform size num[k|m]]];

说明：

? smallfile | bigfile：表示创建的是小文件表空间还是大文件表空间

? autoextend [on|off] next：表示数据文件为自动扩展或非自动扩展，如为自动扩展则需要设置next的值。

? maxsize：表示数据文件自动扩展时，允许数据文件扩展的最大长度字节数，如果指定unlimited关键字，则不需要指定字节长度。

? minimum extent：指出在表空间的extent的最小值，这个参数可以减少空间碎片，保证在表空间的extent是这个数值的整数倍。

? online | offline：创建表空间时可以指定为在线或离线。

? permanent | temporary：指定创建表空间是永久表空间或临时表空间。默认为永久表空间。

? logging | nologging：指定该表空间内的表在加载数据时是否产生日志，默认为产生日志，即使设定为nologging，但在进行insert,update,delete操作时，oracle仍会将信息记录到redolog buffer中。

? extent management dictionary | local：指定表空间的扩展方式是使用数据字典管理还是本地化管理。默认为本地化管理。

? autoallocate | uniform size：如果采用本地化管理，在表空间扩展时，指定每次区的扩展大小是系统自动指定还是按照同等大小进行。如果设定uniform关键字，默认扩展大小为1MB。

? reuse：表示如果该文件存在，则清除该文件再重建该文件；若文件不存在，则创建该文件。

? default storage：设定以后要创建的表、索引、簇的存储参数值。

4、删除表空间

? 删除表空间，但是不删除其内容及物理文件。

droptablespace tablespace_name;

? 删除表空间及其内容。

droptablespace tablespace_name including contents;

? 删除表空间，并删除其物理文件。

droptablespace tablespace_name including contents and datafiles;

? 如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADECONSTRAINTS

droptablespace tablespace_name including contents and datafiles CASCADECONSTRAINTS;

5、创建临时表空间

? 举例：

Create temporary tablespace temp1

tempfile 'E:\ temp01.dbf' size 5M autoextend on next 128k maxsize 1000m,

'E:\ temp 02.dbf' size 5M autoextend on next 128k maxsize 1000m;

6、Oracle 11g 新特性：deferred_segment_creation

deferred_segment_creation，从字面理解含义是段延迟创建，该参数取值范围是true和false，默认是true。具体是如果deferred_segment_creation为true，当新建表并且没有向其中插入数据的时候，这个表不会立即分配extent，即不占数据空间，只有insert数据后才分配空间，这样做可以节省少量的空间。如果deferred_segment_creation为false，那就和之前版本创建表一样，创建时即分配extent。

查看deferred_segment_creation的取值的语句如下：

SQL> show parameterDEFERRED_SEGMENT_CREATION

修改deferred_segment_creation取值的语句如下：

SQL> alter system setdeferred_segment_creation=false;

或

alter session setdeferred_segment_creation=false;

注意：This new feature in not applicable to SYS and the SYSTEM users as the segmentto the table is created along with the table creation.

7、案例（deferred_segment_creation为true）

? 创建表空间，然后删除该表空间。

Create tablespace exampletb

Datafile 'E:\ examp01.dbf' size 5Mautoextend on next 128k maxsize 1000m,

'E:\ examp02.dbf' size 5M autoextend on next 128k maxsize 1000m;

说明：以上例子创建的表空间由examp01.dbf和examp02.dbf两个文件组成。创建完成后，可以发现在相应路径下增加了2个文件。

备注：Linux中语句

Create tablespace exampletb Datafile'u01\ examp01.dbf' size 5M;

drop tablespace exampletb;

? 创建表空间和表，然后删除该表空间。

Create tablespace exampletb

Datafile 'E:\ examp01.dbf' size 5M reuse autoextend on next 128k maxsize 1000m,

'E:\examp02.dbf' size 5M reuseautoextend on next 128k maxsize 1000m;

create table scott.student

(

id number,

name VARCHAR2(10)

)tablespace exampletb;

select segment_name, BYTES, BLOCKS,EXTENTS from user_segments wheresegment_name='STUDENT'; -- scott用户可执行该查询，sys用户查不到。

select segment_name, segment_type fromdba_segments where segment_name ='STUDENT' AND OWNER='SCOTT';

--用来确认未创建segment

drop tablespace exampletb;

? 创建表空间和表，并向表中插入记录，然后删除该表空间。

Create tablespace exampletb

Datafile 'E:\ examp01.dbf' size 5M reuse autoextend on next 128k maxsize 1000m,

'E:\ examp02.dbf' size 5M reuse autoextend on next 128k maxsize1000m;

create table scott.student

(

id number,

name VARCHAR2(10)

)tablespace exampletb;

Insert into scott.studentvalues(1,'lucy');

select segment_name, BYTES, BLOCKS,EXTENTS from user_segments wheresegment_name='STUDENT'; -- scott用户可执行该查询，sys用户查不到。

select segment_name, segment_type fromdba_segments where segment_name ='STUDENT' AND OWNER='SCOTT';

--用来确认创建segment

说明：向student表插入数据时，数据将存储在表空间exampletb中，而exampletb表空间拥有一个或多个数据文件，所以student数据最终存储到examp01和examp02的数据文件中。

drop tablespace exampletb including contents;

? 创建表空间，然后删除该表空间及数据文件。

Create tablespace exampletb

Datafile 'E:\ examp01.dbf' size 5Mreuse autoextend on next 128k maxsize 1000m,

'E:\examp02.dbf' size 5M reuse autoextend on next 128k maxsize 1000m;

drop tablespace exampletb including contents and datafiles;

说明：如果drop tablespace语句中含有datafiles，那datafiles之前必须有contents关键字，不然会提示错误。

? 创建两个表空间，分别在其中创建主码表和外码表，然后删除包含主码表的表空间及数据文件。

Create tablespace exampletb1

Datafile 'E:\ examp01.dbf' size 5M autoextend on next 128k maxsize 1000m;

Create tablespace exampletb2

Datafile 'E:\ examp02.dbf' size 5M autoextend on next 128k maxsize 1000m;

create table test1(mobile number(13) primary key) tablespace exampletb1;

create table test2(mobilenumber(13) references test1(mobile)) tablespaceexampletb2;

drop tablespace exampletb1 including contents and datafiles cascade constraints;

8、案例（deferred_segment_creation为false）

? 创建表空间，然后删除该表空间。

Create tablespace exampletb

Datafile 'E:\ examp01.dbf' size 5Mautoextend on next 128k maxsize 1000m,

'E:\ examp02.dbf' size 5M autoextend on next 128k maxsize 1000m;

说明：以上例子创建的表空间由examp01.dbf和examp02.dbf两个文件组成。创建完成后，可以发现在相应路径下增加了2个文件。

drop tablespace exampletb;

? 创建表空间和表，然后删除该表空间。

Create tablespace exampletb

Datafile 'E:\ examp01.dbf' size 5M reuse autoextend on next 128k maxsize 1000m,

'E:\ examp02.dbf' size 5M reuse autoextend on next 128k maxsize1000m;

create table scott.student

(

id number,

name VARCHAR2(10)

)tablespace exampletb;

select segment_name, BYTES, BLOCKS,EXTENTS from user_segments wheresegment_name='STUDENT'; -- scott用户可执行该查询，sys用户查不到。

select segment_name, segment_type fromdba_segments where segment_name ='STUDENT' AND OWNER='SCOTT';

--用来确认创建segment

drop tablespace exampletb including contents;