Oracle表空间管理

²      表空间

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

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

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

²       TEMP：用于大的排序操作

²       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 fromdba_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,owner fromdba_segments where tablespace_name='USERS';

2、 SYSAUX表空间

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

Select owner as 用户, count(segment_name) as 对象数量 fromdba_segments where tablespace_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仍会将信息记录到redo log buffer中。

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

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

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

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

4、 删除表空间

Ø        删除空的表空间，但是不包含物理文件

drop tablespacetablespace_name;

Ø        删除非空表空间，但是不包含物理文件

drop tablespacetablespace_name including contents;

Ø        删除空表空间，包含物理文件

drop tablespace tablespace_nameincluding datafiles;

Ø        删除非空表空间，包含物理文件

drop tablespacetablespace_name including contents and datafiles;

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

drop tablespacetablespace_name including contents and datafiles CASCADE CONSTRAINTS;

5、 案例

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

Createtablespace exampletb

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

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

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

drop tablespaceexampletb;

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

Createtablespace exampletb   Datafile 'E:\ examp01.dbf'size 5M autoextendon next 128k maxsize 1000m reuse,

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

create tablescott.student

(

  id number,

  name VARCHAR2(10)

)tablespaceexampletb;

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

drop tablespaceexampletb including contents;

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

Create tablespaceexampletb

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

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

drop tablespaceexampletb including datafiles;

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

Createtablespace exampletb

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

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

create tablescott.student

(

  id number,

  name VARCHAR2(10)

)tablespaceexampletb;

drop tablespaceexampletb including contents and datafiles;

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

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

Createtablespace exampletb1

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

Createtablespace exampletb2

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

create tabletest1(mobile number(13) primary key)tablespace exampletb1;

create table test2(mobile number(13) references test1(mobile)) tablespace exampletb2;

drop tablespace exampletb1 including contents and datafiles cascade constraints;