Oracle表空间管理

一、相关概念

创建语句

CREATE TABLESPACE         --创建表空间

CREATE TEMPORARY TABLESPACE         --创建临时表空间

CREATE UNDO TABLESPACE          --创建UNDO表空间

CREATE BIFILE TABLESPACE         --创建大文件表空间

 

语法

CREATE [UNDO]  TABLESPACE  tablespace_name          

[DATAFILE  datefile_spec1 [,datefile_spec2] ......   

[{MININUM EXTENT  integer [k|m]   

|BLOCKSIZE  integer [k]   

|logging clause | FORCE LOGGING   

|DEFAULT  {data_segment_compression} storage_clause   

|[online|offline]   

|[PERMANENT|TEMPORARY]   

|extent_manager_clause   

|segment_manager_clause}]

 

 

临时表空间

第一步：创建用户之前要创建"临时表空间"，若不创建则默认的临时表空间为temp。

SQL> CREATE TEMPORARY TABLESPACE DB_TEMP

         TEMPFILE '/u01/app/oracle/oradata/tabfile/shiyan01_tmp.dbf'

         SIZE 32M

         AUTOEXTEND ON

         NEXT 32M MASIZE UNLIMITED

         EXTENT MANAGEMENT LOCAL;

 

 

修改数据库的默认表空间

ALTER   DATABASE   DEFAULT   TEMPORARY   TABLESPACE   tablespace_name;

 

 

创建大文件临时表空间

CREATE   BIGFILE   TEMPORARY   TABLESPACE ...

 

 

临时文件组

有两种创建方式：一种是通过在创建临时表时指定文件组（通过关键字TABLESPACE   GROUP），一种是通过ALTER   TABLESPACE来修改

 

例：

CREATE   TEMPORARY   TABLESPACE   lmtemp3   TEMPFILE   '/u02/oracle/data/lmtemp301.dbf'

SIZE 25M

TABLESPACE   GROUP   group1;         --创建时指定临时文件组

 

ALTER   TABLESPACE   lmtemp2   TABLESPACE   GROUP   group2;         --通过ALTER关键字修改临时表的文件组

 

ALTER   TABLESPACE   lmtemp3   TABLESPACE   GROUP '';         --移除文件组

表空间

第二步：创建用户之前先要创建数据表空间，若没有创建则默认永久性表空间是system。

SQL>  create tablespace shiyan01_data datafile '/u01/app/oracle/oradata/tabfile/shiyan01_data.dbf' 

           size 32M 

           autoextend on 

           next 32M maxsize unlimited 

           extent management local;

 

 

 

参数说明

(一).EXTENT   MANAGEMENT   LOCAL [ AUTOALLOCATE  | UNIFORM]        --打开表空间本地管理

           EXTENT   MANAGEMENT   DICTIONARY        --打开数据字典管理

关键字EXTENT   MANAGEMENT   LOCAL 指定这是一个本地化管理的表空间。更细的管理方式：是AUTOALLOCATE 还是 UNIFORM.。若为AUTOALLOCATE，则表明让Oracle来决定区块的使用办法；若选择了UNIFORM，则还可以详细指定每个区块的大小，若不加指定，则默认为每个区使用1M大小。

 

实例

创建名为tbsp的表空间

CREATE   TABLESPACE   tbsp   DATAFILE   '/u02/oracle/data/tbsp.dbf'   SIZE   50M

EXTENT   MANAGEMENT   LOCAL   AUTOALLOCATE;         --管理方式为 AUTOALLOCATE

 

CREATE   TABLESPACE   tbsp   DATAFILE   '/u02/oracle/data/tbsp.dbf'   SIZE   50M

EXTENT   MANAGEMENT   LOCAL   UNIFORM   SIZE   128K;         --管理方式为UNIFORM

 

 

（二）.SEGMENT   SPACE   MANAGEMENT   AUTO         --打开段自动管理

       SEGMENT   SPACE   MANAGEMENT   MANUAL        --打开手动管理

在Orale 9i以前，表的剩余空间的管理与分配都是由链接列表freelist来完成的，因为freelist存在串行的问题因此容易引起往往容易引起段头的争用与空间的浪费（其实这一点并不明显），最主要的还是因为需要DBA 花费大量的精力去管理这些争用并监控表的空间利用。  

            自动段空间管理（ASSM），它首次出现在Oracle 920里。有了ASSM，链接列表freelist被位图所取代，它是一个二进制的数组，能够迅速有效地管理存储扩展和剩余区块（free block），因此能够改善分段存储本质，ASSM表空间上创建的段还有另外一个称呼叫Bitmap Managed Segments（BMB段）。  

让我们看看位图freelist是如何实现的。从使用区段空间管理自动参数创建tablespace开始：  
create tablespace demo  
datafile '/ora01/oem/demo01.dbf '  
size 5m  
EXTENT MANAGEMENT LOCAL -- Turn on LMT  
SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM;  

一旦你定义好了tablespace，那么表和索引就能够使用各种方法很容易地被移动到新的tablespace里，带有ASSM的本地管理tablespace会略掉任何为PCTUSED、NEXT和FREELISTS所指定的值。  

            当表格或者索引被分配到这个tablespace以后，用于独立对象的PCTUSED的值会被忽略，而Oracle9i会使用位图数组来自动地管理tablespace里表格和索引的freelist。对于在LMT的tablespace内部创建的表格和索引而言，这个NEXT扩展子句是过时的，因为由本地管理的tablespace会管理它们。但是，INITIAL参数仍然是需要的，因为Oracle不可能提前知道初始表格加载的大小。对于ASSM而言，INITIAL最小的值是三个块。  

            新的管理机制用位图来跟踪或管理每个分配到对象的块，每个块有多少剩余空间根据位图的状态来确定，如>75%,50%-75%,25%-50%和<25%，也就是说位图其实采用了四个状态位来代替以前的pctused，什么时候该利用该数据块则由设定的pctfree来确定。  

            使用ASSM的一个巨大优势是，位图freelist肯定能够减轻缓冲区忙等待（buffer busy wait）的负担，这个问题在Oracle9i以前的版本里曾是一个严重的问题 。 

            在没有多个freelist的时候，每个Oracle表格和索引在表格的头部都曾有一个数据块，用来管理对象所使用的剩余区块，并为任何SQL插入声明所创建的新数据行提供数据块。当数据缓冲内的数据块由于被另一个DML事务处理锁定而无法使用的时候，缓冲区忙等待就会发生。当你需要将多个任务插入到同一个表格里的时候，这些任务就被强制等待，而同时Oracle会在同时分派剩余的区块，一次一个。  

            有了ASSM之后，Oracle宣称显著地提高了DML并发操作的性能，因为（同一个）位图的不同部分可以被同时使用，这样就消除了寻找剩余空间的串行化。根据Oracle的测试结果，使用位图freelist会消除所有分段头部（对资源）的争夺，还能获得超快的并发插入操作 

尽管ASSM显示出了令人激动的特性并能够简化Oracle DBA的工作，但是Oracle9i的位图分段管理还是有一些局限性的：  
            1. 一旦DBA被分配之后，它就无法控制tablespace内部的独立表格和索引的存储行为。  
            2. 大型对象不能够使用ASSM，而且必须为包含有LOB数据类型的表格创建分离的tablespace。  
            3. 你不能够使用ASSM创建临时的tablespace。这是由排序时临时分段的短暂特性所决定的。  
            4. 只有本地管理的tablespace才能够使用位图分段管理。  
            5· 使用超高容量的DML（例如INSERT、UPDATE和DELETE等）的时候可能会出现性能上的问题。

 

 

 

（三）.表空间的默认压缩属性

    创建表空间时指定表空间的默认压缩属性可以节省磁盘的空间

    语法：

        CREATE    TABLESPACE ...   DEFAULT   ROW   STORE   COMPRESS   ADVANCED ... ;         --行压缩

        CREATE TABLESPACE ... DEFAULT INDEX COMPRESS ADVANCED HIGH ... ;         --索引压缩，级别为HIGH

 

 

（四）.加密表空间

 

        加密表空间可以指定相应加密算法，也可以不指定，默认为AES128。

        例：

          CREATE   TABLESPACE   securespace   DATAFILE   '/u01/app/oracle/oradata/orcl/secure01.dbf'   SIZE   100M   ENCRYPTION   ENCRYPT;         --使用默认算法

          CREATE   TABLESPACE   securespace  DATAFILE   '/u01/app/oracle/oradata/orcl/secure01.dbf'   SIZE   100M  ENCRYPTION   USING   'AES256'  ENCRYPT;         --指定AES256算法

        

 

（五）修改表空间的状态

           修改表空间的状态脱机、联机（注：不能让系统、uodo以及临时表空间脱机）

          语法：ALTER   TABLESPACE   tbs_name   OFFLINE   [ NORMAL | TEMPORARY  |  IMMEDIATE ] 

                      参数NORMAL是默认值，当系统报错时就会下线失败，TEMPORARY不管有没有报错都会下线，IMMEDIATE即强制下线。

                      一般在noramal参数失败后才会选择temporary参数，在前两者都失败后才会选择immediate参数。

                     例：

                      ALTER   TABLESPACE   tbs_test01   OFFLINE   NORMAL;         --让 tbs_test01表空间脱机

                      ALTER   TABLESPACE   tbs_test01   ONLINE;         --让tbs_test01联机   

 

          修改表空间为只读

         undo、系统表空间以及临时表空间不能被修改为只读

         语法：ALTER   TABLESPACE   tbs_name  READ   ONLY;

         例

          ALTER   TABLESPACE   tbs_test02   READ   ONLY;         --修改表空间tbs_test02为只读

          ALTER   TABLESPACE   tbs_test02   READ WRITE;         --修改表空间tbs_test02为可读写

 

 

（六）表空间的管理

        表空间的管理主要有以下几方面

         1.扩展表空间：增大数据文件的或者增加数据文件；

         2.修改本地表空间的属性：可以修改为只读、可读写、重命名、禁用或启用自动扩展；

         3.修改大文件表空间的属性：更改数据表空间的大小或者让它自动扩张；

         4.对于临时表空间：增加临时文件、让临时表空间脱机或联机；

 

          例：

         ALTER   TABLESPACE   lmtbsb   ADD   DATAFILE   '/u02/oracle/data/lmtbsb02.dbf'   SIZE   1M;         --往表空间添加一个新的数据文件

        ALTER   TABLESPACE   bigtbs   RESIZE   80G;         --更改大文件表空间的大小

        ALTER   TABLESPACE   bigtbs   AUTOEXTEND   ON   NEXT   20G;         --让大文件表空间自动扩张    

        ALTER   TABLESPACE   lmtemp  ADD   TEMPFILE   '/u02/oracle/data/lmtemp02.dbf'   SIZE   18M   REUSE;         --往临时表空间添加数据文件

 

        ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;         --让临时表空间脱机

        ALTER TABLESPACE lmtemp TEMPFILE ONLINE;         --让临时表空间联机    

         等同于上面两个（用alter database的方式）

        ALTER   DATABASE   TEMPFILE   '/u02/oracle/data/lmtemp02.dbf'   OFFLINE;

        ALTER   DATABASE   TEMPFILE   '/u02/oracle/data/lmtemp02.dbf'   ONLINE;

        

         ALTER   DATABASE   TEMPFILE   '/u02/oracle/data/lmtemp02.dbf'   RESIZE   18M;        --更改文件的大小

 

        ALTER   DATABASE   TEMPFILE   '/u02/oracle/data/lmtemp02.dbf'   DROP  INCLUDING   DATAFILES;         --删除数据文件

        

         收缩表空间、文件

        ALTER   TABLESPACE   lmtemp1   SHRINK   SPACE   KEEP   20M;         --收缩表空间

        ALTER   TABLESPACE   lmtemp2   SHRINK   TEMPFILE   '/u02/oracle/data/lmtemp02.dbf';         --收缩数据文件

 

        重命名表空间

        ALTER   TABLESPACE   users   RENAME   TO   usersts;         --重命名表空间

 

 

 

（七）删除表空间

         语法：DROP   TABLESPACE ...

        当一些表空间不需要时可以对其进行删除，被删除的表空间不能有任何的活动段，例如一些重做数据需要进行事务回滚等；如果删除的表空间是空的（没有表、视图等）则不需要加 INCLUDING  CONTENTS参数，使用 CASCADE  CONSTRAINTS参数则可以删除掉所有来自其他表空间的外键约束。添加参数 INCLUDING  CONTENTS  AND  DATAFILES可以删除与表空间相关联的数据文件。

        例：

         DROP   TABLESPACE   users   INCLUDING   CONTENTS   AND   DATAFILES;