oracle 表空间与数据文件详细（一）

--==============================

--Oracle表空间与数据文件

--==============================

/*

一、概念

表空间：是一个或多个数据文件的逻辑集合

表空间逻辑存储对象：永久段-->如表与索引

临时段-->如临时表数据与排序段

回滚段-->用于事物回滚或闪回内存的撤销数据

表空间分类：系统表空间(system、sysaux)，非系统表空间

一个表空间至少包含一个数据文件，一个数据文件只能属于一个表空间。

不可或缺的几个表空间：

SYSTEM --->字典表空间，不能被损坏

UNDO--->dml,dql把数据快照到此，数据提交即消失（用于恢复）

SYSAUX--->10g高并发系统繁忙时，会造成system争用，将工具放到SYSAUX,减轻system的压力，SYSAUX不影响系统（影响性能）

TEMP--->临时数据相关的内容

USERS--->10g用户数据从system拨离出来

二、Oracle的存储结构

1.Schema:用户--->创建相关对象、表、视图、序列、函数、存储过程、包等

2.逻辑结构：database数据库--->tablespace表空间---> segment段--->extent区间----> block块

逻辑结构是Oracle内部管理数据库中对象的方式

3.物理结构：OSblock --->datafile物理结构通常是一系列数据文件

4.举例描述scott用户创建对象的组织方式*/

--查看scott用户的默认表空间及数据文件

SQL>selectUSERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE

2fromdba_users

3whereusername='SCOTT';

USERNAMEDEFAULT_TABLESPACETEMPORARY_TABLESPACE

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

SCOTTUSERSTEMP

SQL>colnameformat a50

SQL>selectt1.name,t2.name--查看表空间及数据文件

2fromv$tablespace t1,v$datafile t2

3wheret1.ts#=t2.ts#;

NAMENAME

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

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)-->数据定义(位于)-->system表空间

数据(位于)-->user表空间(逻辑存储)-->表段-->区间-->内存块

-->索引段等-->区间-->内存块

user表空间(物理存储)-->user01.dbf

-->采用本地管理，包含头部信息，可用、已用等位图信息

当databuffer缓冲区满则调用dbwr进程将数据写入到物理文件当中*/

/*

三、创建表空间

--简要语法：

CREATE [BIGFILE | SMALLFILE] [TEMPORARY] TABLESPACE tablespace name

DATAFILE datafile spec | TEMPFILE tempfile spec

[MINIMUM EXTENT minimum extent size]

[BLOCKSIZE blocksize]

[[COMPRESS|NOCOMPRESS] DEFAULT STORAGE (default storage clause)]

[LOGGING|NOLOGGING]

[FORCE LOGGING]

[ONLINE|OFFLINE]

[EXTENT MANAGEMENT DICTIONARY |

LOCAL [AUTOALLOCATE|UNIFORM SIZE size]]

[SEGMENT SPACE MANAGEMENT MANUAL|AUTO]

[FLASHBACK ON|OFF]

--创建表空间的条件

1.具有create tablespace的权限，DBA角色具有该权限，sysdba,sysoper

2.创建的是bigfiel，还是smallifle超过T级别应考虑bigfile

3.新建的表空间的I/O，是否会导致磁盘I/O不够用

4.datafile后跟的路径应该具备写的权限*/

--查看缺省是BIGFILE还是SMALLFILE,当缺省为SMALLFILE,创建表空间不指定表空间类型则为SMALLFILE

SQL>selectPROPERTY_NAME,PROPERTY_VALUEfromdatabase_propertieswherePROPERTY_NAMElike'%TBS%';

PROPERTY_NAMEPROPERTY_VALUE

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

DEFAULT_TBS_TYPESMALLFILE

--修改创建表空间为大或小表空间的默认值

SQL>alterdatabasesetdefaultbigfile tablespace;

Databasealtered.

SQL>selectPROPERTY_NAME,PROPERTY_VALUEfromdatabase_propertieswherePROPERTY_NAMElike'%TBS%';

PROPERTY_NAMEPROPERTY_VALUE

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

DEFAULT_TBS_TYPEBIGFILE

--改回为缺省值

SQL>alterdatabasesetdefaultsmallfile tablespace;

Databasealtered.

--建议创建表空间时最好带上[BIGFILE | SMALLFILE]参数，以免参数设置导致创建了不合理的表空间

/*

注意：

大表文件（bigfile)最大可以存放个T的容量。头文件的大小达到了G－－＞block,普通的头文件大小为M---->block。

create bigfile tablespace <> datafile <>........;

好处：减少了数据文件的个数，管理方便，大的对象的存放得到了优化。减少了control文件的信息，控制文件定义了datafile的个数。

bigfile只能存在一个数据文件，所以要保证分配的的磁盘具有足够的空间。*/

--演示创建表空间

SQL>createtablespace TBS1 datafile'/u01/app/oracle/oradata/orcl/tbs1.dbf'size100m;

Tablespace created.

SQL>colnameformat a50

SQL>selectt1.name,t2.name

2fromv$tablespace t1,v$datafile t2

3wheret1.ts#=t2.ts#;

NAMENAME

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

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

TBS1/u01/app/oracle/oradata/orcl/tbs1.dbf

/*

创建临时表空间：

创建临时表空间，不能使用非标准数据块，另临时表空间不能存放永久对象。*/

CREATETMEPORARY TABLESPACE tablespace_name

TEMPFILE'...'SIZEn

CREATETEMPORARY TABLESPACE T2 TEMPFILE'/opt/oracle/oradata/orcl/t2.dbf'

size100m;

/*

临时表空间具有以下特征：

临时数据文件不能置为只读

临时数据文件不能重命名

监时数据文件的日志方式总是NOLOGGING

临时表空间扩容：*/

--(1)重置临时文件大小

alterdatabasetempfile'/opt/oracle/oradata/orcl/t2.dbf'resize 150m;

--(2)让临时文件能自动扩展

alterdatabasetempfile'/opt/oracle/oradata/orcl/t2.dbf'

autoextendonnext 10mmaxsize50m;

--(3)增加临时文件

altertablespace t2addtempfile'/disk8/oracle/t2b.dbf'size50m;

--查看临时文件

selectTABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "SIZE(MB)"

fromdba_temp_files;

--默认临时表空间：

select*fromdatabase_properties;

--修改默认临时表空间：

ALTERDATABASEDEFAULTTEMPORARY TABLESPACE T2;

/*

默认临时表空间最好要指定一下，如果没有指定默认的临时表空间，那么将使用system表空间作为排

序区

默认临时表空间有一定的限制：

默认临时表空间不能删除

默认临时表空间不能脱机

获得创建表空间的语句：*/

SELECTdbms_metadata.get_ddl('TABLESPACE','SYSTEM')FROMdual;

/*

临时表空间组：

10G引入的，是一组临时表空间，只能由临时表空间组成。组名不能与临时表空间同名

临时表空间不能显示的创建和删除，当把第一个临时表空间分配给某个临时表空间组的时候，自动创建

临时表空间组，将最后一个临时表空间，删除时，组也将自动删除。

创建临时表空间组：

1、创建临时表空间的时候*/

CREATETEMPORARY TABLESPACE tablespace_name TEMPFILE'...'SIZEn

TABLESPACEGROUPgroup_name;

ALTERTABLESPACE tablespace_name TABLESPACEGROUPgroup_name;

ALTERTABLESPACE T3 TABLESPACEGROUPTEMP_GRP;

--查看临时表空间组的信息:

dba_tablespace_groups

SELECT*FROMDBA_TABLESPACE_GROUPS;

--将临时表空间组成员移除：

ALTERTABLESPACE tablespace_name TABLESPACEGROUP'';

--可将系统默认的临时表空间设为临时表空间组：

ALTERDATABASEDEFAULTTEMPORARY TABLESPACE tablespace_name;

--删除临时表空间：

--将所有成员全移出去，便自动删除

--创建UNDO表空间（还原表空间）：

/*

创建语法：*/

CREATEUNDO TABLESPACE tablespace_name DATAFILE'...'SIZEn

SQL>CREATEUNDO TABLESPACE undo2

DATAFILE'/opt/oracle/oradata/orcl/undo2_01.dbf'SIZE200M,

'/opt/oracle/oradata/orcl/undo2_02.dbf'size100m;

--修改当前系统的UNDO表空间:

ALTERSYSTEMSETUNDO_TABLESPACE=tablespace_name

/*

UNDO表空间扩容：

（）重置DATAFILE大小

（）让DATAFILE能AUTOEXTEND

（）添加数据文件

删除UNDO表空间*/

DROPTABLESPACE tablespace_name

/*

注：当前的UNDO表空间不能删除

系统只能使用一个UNDO表空间*/

--创建表空间的详细语法：http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7003.htm#SQLRF01403

/*

转载于：点击打开链接

原文链接：http://blog.csdn.net/robinson_0612/article/details/5611738