ORACLE表空间的管理

ORACLE中，表空间是数据管理的基本方法，所有用户的对象要存放在表空间中，也就是用户有空间的使用权，才能创建用
户对象．否则是不充许创建对象，因为就是想创建对象,如表,索引等，也没有地方存放,Oracle会提示:没有存储配额．　　
因此，在创建对象之前，首先要分配存储空间。




一、创建表空间完整的格式
CREATE [UNDO|TEMPORARY] TABLESPACE tablespace_name 
     TEMPFILE|DATAFILE 'path/filename' [SIZE INTEGER[K| M]]  [REUSE] 
     [AUTOEXTEND [OFF | ON ] ]
     [NEXT INTEGER[K | M]]
     [MAXSIZE [UNLIMITED | INTEGER[K | M]]]
     [MINIMUM EXTENT INTEGER[K | M]]
     [BLOCKSIZE integer [k]]
     [DEFAULT STORAGE storage_clause]
     [ONLINE | OFFLINE]
     [LOGGING | NOLOGGING]
     [FORCE LOGGING]
     [EXTENT MANAGEMENT [DICTIONARY | LOCAL]]
     [AUTOALLOCATE | UNIFORM [SIZE INTEGER[K | M]]]
     [SEGMENT SPACE MANAGEMENT [AUTO | MANUAL]]










/*分为四步 */
/*第1步：创建临时表空间和创建可恢复(undo)表空间  */ 


--创建临时表空间
create  temporary tablespace temp_chenanzhen
tempfile 'D:\Oracle\temp_chenanzhen.dbf'
size 50M
autoextend on
next 50M 
maxsize  unlimited
extent management local;
--创建可恢复(undo)表空间
create  undo tablespace undo_chenanzhen
datafile 'D:\Oracle\undo_chenanzhen.dbf'
size 50M
autoextend on 
next 50M 
maxsize unlimited
extent management local;;  
 
/*第2步：创建数据表空间  */


create tablespace tablespace_chenanzhen
datafile 'D:\Oracle\tablespace_chenanzhen.dbf' size 50M,
   'D:\Oracle\tablespace_chenanzhen1.dbf' size 50M
autoextend on --自动扩展空间
next 50M maxsize unlimited   --从50M到无限制
blocksize 8192 --块的大小
online      --指在创建表空间之后使该表空间立即对授权访问该表空间的用户可用
force logging   --指出表空间进入强制日志模式  
extent management local    --表空间如何管理范围:local 指的是按照本地的范围管理
segment space management auto;  --表空间段的管理方式，AUTO为系统自动管理，MANUAL为手工管理。默认为AUTO


--1) DATAFILE: 表空间数据文件存放路径


--2) SIZE: 起初设置为200M, 最大值maxsize: unlimited


--3) UNIFORM: 指定区尺寸为128k,如不指定，区尺寸默认为64k


--4) 空间名称user_data 与 数据文件名称user_data.dbf 不要求相同,可随意命名.


--5) AUTOEXTEND ON/OFF 表示启动/停止自动扩展表空间 


 
/*第3步：创建用户并指定默认表空间和临时表空间  */
 
create user username identified by password  
 
default tablespace tablespace_chenanzhen
 
temporary tablespace temp_chenanzhen;
 
/*第4步：给用户授予权限  */
 
grant connect,dba to chenanzhen;  




--5、删除表空间，同时删除数据文件：


drop tablespace tablespace_chenanzhen including contents and datafiles






二、表空间格式说明
1、UNDO
      说明系统将创建一个回滚表空间。
       在9i中数据库管理员可以不必管理回滚段，只有建立了undo表空间，系统就会自动管理回滚段的分配，回收的工作。当然,也可以创建一般的表空间,在上         面


创建回滚段.不过对于用户来说,系统管理比自己管理要好很多.如果需要自己管理,请参见回滚段管理的命令详解.
       当没有为系统指定回滚表空间时,系统将使用system系统回滚段来进行事务管理。
2、tablespace_name表空间名满足oracle的命名规范即可。
3、DATAFILE 'path/filename' [SIZE INTEGER[K| M]]  [REUSE] 
      指定一个或多个数据文件路径和文件名,[SIZE INTEGER[K| M]]用来指定表空间在数据文件中所占的空间大小，K|M是说大小的单位是K还是M。
      [REUSE] 如果创建表空间指定的数据文件已经存在，则需要使用reuse注明，否则会报错。
      ERROR at line 1:
ORA-01537: cannot add data file 'E:\oracle\oradata\10.1.0\ZRPTEST\TB_TEST1.dbf'
- file already part of database
4、[AUTOEXTEND [OFF | ON ] ]  
      禁止或允许自动扩展数据文件，默认情况下为OFF
5、[NEXT INTEGER[K | M]] 
      指定当需要更多盘区时分配给数据文件的磁盘空间，以K或M为单位。
6、[MAXSIZE [UNLIMITED | INTEGER[K | M]]] 
      指定允许分配给数据文件的最大磁盘空间，当为UNLIMITED时表示只有当磁盘空间满时，才不允许再扩展数据文件。
7、[MINMUM EXTENT INTEGER[K | M]]  
      指定盘区大小是指定大小的整数倍。即initial和next盘区大小应为minimum extent的整数倍，这个参数可以减小空间碎片，保证表空间的范围是这个值的整数倍。
      【注：minextents 用于指定在创建时分配给段的盘区总数。使用这个参数，即使可用空间是不连续的，在创建对象时也能分配很大的空间。默认                    


值和最小值都是1。如果minextents>1，盘区大小以next和pctincrease为基础计算。】
8、[BLOCKSIZE integer [k]]
        这个参数可以设定一个不标准的块的大小。如果要设置这个参数，必须设置db_block_size,至少一个db_nk_block_size,
        并且声明的integer的值必须等于db_nk_block_size.
       注意：在临时表空间不能设置这个参数。
9、 [DEFAULT STORAGE storage_clause]
       表示为在该表空间创建的全部对象指定缺省的存储参数，没有指定时oracle将会为所有对象指定一些系统默认的存储参数。
       storage_clause的语法格式如下：
       STORAGE（
            INITIAL integer[K|M]       --为段分配第一个区的大小
            NEXT     integer[K|M]       --为第一个扩展分区的大小
            MINEXTENTS integer|UMLIMITED      --创建段时分配的最小总区间数
            MAXEXTENTS integer|UMLIMITED     --创建段时分配的最大总区间数
            PCTINCREASE integer                           --每次扩展增量的大小
            FREELISTS integer                                  --模式对象中每一个自由列表组中自由列表的数量，表、族或索引的每个空闲列表组中的列表数量。
            FREELISTS GROUPS integer                  --指定表、族或索引的每个空闲列表组数量。
         ）
10、[ONLINE | OFFLINE]
      ONLINE是指在创建表空间之后使该表空间立即对授权访问该表空间的用户可用。
      OFFLINE是指offline以后，未完成的事物可以提交或回滚，但不能发起新的事物，也不能进行查询。OFFLINE的四种模式
       （1）NORMAL：做检查点 
                 alter tablespace users  offline;           alter tablespace users online;
       （2）TEMPORARY：可以在数据文件损坏的情况下offline tablespace
                 alter tablespace users offline temporary;   alter tablespace users online;
       （3）IMMEDIATE：不做检查点，只有在归档模式下才可以offline tablespace，online时需要recover
                 alter tablespace users offline immediate;    recover tablespace users;       alter tablespace users online;
        （4）FOR RECOVER：在归档模式下才可以offonline，在基于时间点的恢复时使用。
                 alter tablespace users offline for recover;     recover tablsespace users;    alter tablespace users online;
11、[LOGGING | NOLOGGING]
      指定日志属性，它表示将来的表、索引等是否需要进行日志处理。默认值为LOGGING
12、[FORCE LOGGING]
       使用这个子句指出表空间进入强制日志模式。此时，系统将记录表空间上对象的所有改变，除了临时段的改变。这个参数高于对象的nologging选项。
       注意：设置这个参数指数据库不可以open并且进入读写模式。而且，在临时表空间和回滚表空间中不能使用这个选项。
13、[PERMANENT | TEMPORARY]
       指出表空间的属性，是永久表空间还是临时表空间。永久表空间存放的是永久对象，临时表空间存放的是session生命期中存在的临时对象。
       这个参数生成的临时表空间创建后一直都是字典管理，不能使用extent management local选项。
       如果要创建本地管理表空间，必须使用create temporary tablespace
       注意，声明了这个参数后，不能声明block size
14、[EXTENT MANAGEMENT [DICTIONARY | LOCAL]]
        [AUTOALLOCATE | UNIFORM [SIZE INTEGER[K | M]]]
        这是最重要的子句，说明了表空间如何管理范围。一旦你声明了这个子句，只能通过移植的方式改变这些参数。
        如果希望表空间本地管理的话，声明local选项。本地管理表空间是通过位图管理的。autoallocate说明表空间自动分配范围，用户不能指定范围的大小。
        只有9.0以上的版本具有这个功能。uniform说明表空间的范围的固定大小，缺省是1m。
         不能将本地管理的数据库的system表空间设置成字典管理。oracle公司推荐使用本地管理表空间。
         如果没有设置extent_management_clause，oracle会给他设置一个默认值。如果初始化参数compatible小于9.0.0,那么系统创建字典管理表空间。
          如果大于9.0.0,那么按照如下设置：如果没有指定default storage_clause,oracle创建一个自动分配的本地管理表空间。
         否则，如果指定了mininum extent,那么oracle判断mininum extent 、initial、next是否相等,以及pctincrease是否=0.如果满足以上的条件，oracle创建一个本地


管理
          表空间，extent size是initial.如果不满足以上条件，那么oracle将创建一个自动分配的本地管理表空间。
          如果没有指定mininum extent。initial、那么oracle判断next是否相等,以及pctincrease是否=0。表示满足oracle创建一个本地管理表空间并指定uniform。
           否则oracle将创建一个自动分配的本地管理表空间。
          注意：本地管理表空间只能存储永久对象。如果你声明了local,将不能声明default storage_clause,mininum extent、temporary.
15、[SEGMENT SPACE MANAGEMENT [AUTO | MANUAL]]
       指定 表空间段的管理方式，AUTO为系统自动管理，MANUAL为手工管理。默认为AUTO。




三、表空间创建实例
1、创建一个本地自动分配管理的永久表空间：
      SQL> create tablespace ZRP_MAIN_TB 
  2    datafile 'E:\oracle\oradata\10.1.0\ZRPTEST\ZRP_TB\MAIN_TB1.dbf' size 200M,
  3             'E:\oracle\oradata\10.1.0\ZRPTEST\ZRP_TB\MAIN_TB2.dbf' size 200M AUTOEXTEND ON  NEXT
 1M MAXSIZE 2000M 
  4    ONLINE 
  5    LOGGING 
  6    EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
  7    SEGMENT SPACE MANAGEMENT AUTO;




Tablespace created.




SQL> create tablespace ZRP_MAIN_TB 
  2    datafile 'E:\oracle\oradata\10.1.0\ZRPTEST\ZRP_TB\MAIN_TB1.dbf' size 200M,
  3             'E:\oracle\oradata\10.1.0\ZRPTEST\ZRP_TB\MAIN_TB2.dbf' size 200M 
  4    EXTENT MANAGEMENT LOCAL AUTOALLOCATE ;




Tablespace created.




注：这里不能设置MINIMUN EXTENT 和BLOCKSIZE，否则会报错。
SQL> create tablespace ZRP_MAIN_TB 
  2    datafile 'E:\oracle\oradata\10.1.0\ZRPTEST\ZRP_TB\MAIN_TB1.dbf' size 200M,
  3             'E:\oracle\oradata\10.1.0\ZRPTEST\ZRP_TB\MAIN_TB2.dbf' size 200M AUTOEXTEND ON  NEXT
 1M MAXSIZE 2000M 
  4    MINIMUM EXTENT 32K 
  5    BLOCKSIZE 1
  6    ONLINE 
  7    LOGGING 
  8    EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
  9    SEGMENT SPACE MANAGEMENT AUTO;
create tablespace ZRP_MAIN_TB
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy




2、创建一个数据字典管理的的永久表空间：
SQL> CREATE TABLESPACE ZRP_MAIN_TB 
  2    DATAFILE 'E:\oracle\oradata\10.1.0\ZRPTEST\ZRP_TB\MAIN_TB1.dbf' size 200M,
  3             'E:\oracle\oradata\10.1.0\ZRPTEST\ZRP_TB\MAIN_TB2.dbf' size 200M 
       AUTOEXTEND ON
       NEXT 1280K MAXSIZE 2000M 
  4    MINIMUM EXTENT 128K 
  5    BLOCKSIZE 1
  6    DEFAULT STORAGE(
  7      INITIAL 1280K 
  8      NEXT    1280K 
  9      MINEXTENTS 1 
 10      MAXEXTENTS 4096 
 11      PCTINCREASE 0 
 12     )
 13    ONLINE 
 14    LOGGING 
 15    EXTENT MANAGEMENT DICTIONARY 
 16    UNIFORM SIZE 200M;
CREATE TABLESPACE ZRP_MAIN_TB
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace




错误原因：在ORACLE9i之后，系统缺省安装时为本地管理表空间（LOCAL MANAGED TABLESPACE）LMT。
注：这里不能设置SEGMENT SPACE MANAGEMENT AUTO;




3、创建一个临时表空间：
格式一：
SQL> create temporary tablespace ZRP_TMP_TB1
  2  tempfile 'E:\oracle\oradata\10.1.0\ZRPTEST\ZRP_TB\TMP_TB1.dbf' size 100M
  3  autoextend on 
  4  next 1M
  5  extent management local 
  6  /




Tablespace created.
注意：这里不能用AUTOALLOCATE 、SEGMENT SPACE MANAGEMENT AUTO、LOGGING、ONLINE等标记符。




格式一：
create temporary tablespace ZRP_TMP_TB3 
tempfile 'E:\oracle\oradata\10.1.0\ZRPTEST\ZRP_TB\TMP_TB3.dbf' size 100M
autoextend on 
next 10M 
maxsize 100M
extent management local 
uniform size 99M;
注意：1、在创建表空间过程中uniform size的大小必须小于数据文件的size大小
            2、在创建表空间过程中maxsize的大小必须大于等于数据文件的size大小 








4、创建一个UNDO表空间：
create undo tablespace ZRP_UNDO_TB1 
datafile 'E:\oracle\oradata\10.1.0\ZRPTEST\ZRP_TB\UNDO_TB1.dbf' size 100M
autoextend on 
next 10M 
maxsize 100M
online 
extent management local autoallocate; 
segment space management auto;
注意：这里不能定义minimum extent、blocksize、default storage、online、logging、segment space management。






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


--------------------------------------表空间------------------------------------------- 


/*在ORACLE数据库中，所有数据从逻辑结构上看都是存放在表空间当中，当然表空间下还有段、区、块等逻辑结构。
从物理结构上看是放在数据文件中。一个表空间可由多个数据文件组成。
如下图所示，一个数据库由对应一个或多个表空间，表空间逻辑上有一个或多个段（Segment）组成，物理上由一个或多个os file组成。*/


  /*  
  1.1 基本的表空间
  系统中默认创建的几个表空间： SYSTEM, SYSAUX,USERS, UNDOTBS1,EXAMPLE,TEMP
  系统中必须的表空间有那几个？
  答案： SYSTEM、SYSAUX、TEMP、UNDO， 像USERS、EXAMPLE等表空间是可有可无的。
  1.2 表空间的分类
      永久表空间                存放永久性数据，如表，索引等。
      临时表空间                不能存放永久性对象，用于保存数据库排序，分组时产生的临时数据。
      UNDO表空间                保存数据修改前的镜象。
   1.3表空间的管理
   表空间的管理方式：
   字典管理：全库所有的空间分配都放在数据字典中。容易引起字典争用，而导致性能问题。
   本地管理：空间分配不放在数据字典，而在每个数据文件头部的第3到第8个块的位图块，来管理空间分配。
  */
   
--2.1创建临时表空间，（当然表空间下还有段、区、块等逻辑结构）
create  temporary tablespace temp_chenanzhen1
tempfile 'D:\Oracle\temp_chenanzhen1.dbf'
size 50M
autoextend on
next 50M maxsize  unlimited
extent management local;
--创建可恢复(undo)表空间
create  undo tablespace undo_chenanzhen1
datafile 'D:\Oracle\undo_chenanzhen1.dbf'
size 50M
autoextend on 
next 50M maxsize unlimited
extent management local;
--创建表空间
create tablespace tablespace_chenanzhen1
datafile 'D:\Oracle\tablespace_chenanzhen6.dbf' size 50M
   'D:\Oracle\tablespace_chenanzhen7.dbf' size 50M
autoextend on --自动扩展空间
next 50M maxsize unlimited   --从50M到无限制
blocksize 8192 --块的大小
online      --指在创建表空间之后使该表空间立即对授权访问该表空间的用户可用
force logging   --指出表空间进入强制日志模式  
extent management local    --表空间（区）如何管理范围:local 指的是按照本地的范围管理
segment space management auto;  --表空间段的管理方式，AUTO为系统自动管理，MANUAL为手工管理。默认为AUTO


/*第3步：创建用户并指定默认表空间和临时表空间  */
create  user  chenanzhen1  identified by 123456
default tablespace tablespace_chenanzhen1
temporary tablespace temp_chenanzhen1 ;


--/*第4步：给用户授予权限  */
grant connect  ,dba to chenanzhen1;




/*3：表空间管理     3.1 表空间信息
      如何查看数据库有哪些表空间？如何查看表空间对应的数据文件？
     查看表空间： 查看表空间可以通过下面几个系统视图查看基本信息
     --包含数据库中所有表空间的描述信息*/
     SELECT * FROM DBA_TABLESPACES;
     --包含当前用户的表空间的描叙信息
     SELECT * FROM USER_TABLESPACES;
     --包含从控制文件中获取的表空间名称和编号信息
     SELECT * FROM V$TABLESPACE;
     --查看数据文件
     --包含数据文件以及所属的表空间的描述信息
     SELECT * FROM DBA_DATA_FILES;
     --包含临时数据文件以及所属的表空间的描述信息
     SELECT * FROM DBA_TEMP_FILES;
     --包含从控制文件中获取的数据文件的基本信息，包括它所属的表空间名称、编号等
     SELECT * FROM V$DATAFILE;
     --包含所有临时数据文件的基本信息
     SELECT * FROM V$TEMPFILE;
     --查看表在那个表空间
     SELECT *  FROM DBA_TABLES where TABLESPACE_name = 'TABLESPACE_CHENANZHEN';
   
   --查看表空间的利用率。  
    SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名称", 
            ROUND(D.AVAILB_BYTES ,2) AS "表空间大小(G)", 
           ROUND(D.MAX_BYTES,2) AS "最终表空间大小(G)", 
           ROUND((D.AVAILB_BYTES - F.USED_BYTES),2) AS "已使用空间(G)", 
            TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100, 
               2), '999.99') AS "使用比", 
           ROUND(F.USED_BYTES, 6) AS "空闲空间(G)", 
            F.MAX_BYTES AS "最大块(M)" 
     FROM (
           SELECT TABLESPACE_NAME, 
                   ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES, 
                   ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES 
          FROM SYS.DBA_FREE_SPACE 
          GROUP BY TABLESPACE_NAME) F, 
         (SELECT DD.TABLESPACE_NAME, 
                   ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES, 
               ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6) MAX_BYTES 
          FROM SYS.DBA_DATA_FILES DD 
           GROUP BY DD.TABLESPACE_NAME) D 
   WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 
   ORDER BY 4 DESC;
  
  /*修改用户的默认永久表空间*/
  --数据库级别：
  --永久表空间
  ALTER DATABASE DEFAULT TABLESPACE USER;
  --临时表空间
  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
  --用户级别
  ALTER USER USERNAME DEFAULT TABLESPACE NEW_TABLESPACE_NAME;
   
   /*删除表空间
  除了SYSTEM表空间外，数据库中的任何表空间可以删除。删除表空间时，ORACLE仅仅是在控制文件和数据字典中删除与表空
  间和数据文件相关的信息。默认情况下，ORACLE并不会在操作操作系统中删除相应的数据文件，因此在成功执行删除表空间
  的操作后，需要手动删除该表空间在操作系统中对应的数据文件。如果在删除表空间的同时要删除对应的数据文件，则必须
  显示的指定INCLUDING CONTENTS AND DATAFILES子句。*/
  DROP TABLESPACE USER01 INCLUDING CONTENTS AND DATAFILES;
  
  /*1增加数据文件。
  如果发现某个表空间存储空间不足时，可以为表空间添加新的数据文件，扩展表空间大小。但是一般建议预先估计表空间所
  需的存储空间大小，然后为它建立若干适当大小的数据文件。
  在添加新的数据文件时，如果同名的操作系统已经存在，ALTER TABLESPACE语句将失败。如果要覆盖同名的操作系统文件时，
  则必须在后面显示的指定REUSE子句*/
  ALTER TABLESPACE TBS_EDS_DAT
  ADD DATAFILE 'G:\datafile\TBS_EDS_DAT01.DBF'
  SIZE 100M
  AUTOEXTEND ON
  NEXT 10K
  MAXSIZE 5120M;
  
 /*2新增数据文件，并且允许数据文件自动增长*/
  ALTER TABLESPACE app_data ADD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
  AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
 
 /* 允许已存在的数据文件自动增长*/
  ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'
  AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
 
 /*调整数据文件大小*/
 ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf'
 RESIZE 500M;
 
 /*删除数据文件*/
 ALTER TABLESPACE TEST
 DROP DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf'
 
 /*数据文件脱机*/
 ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' OFFLINE;
 
 /*数据文件联机*/
 ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' ONLINE;
 
 /*表空间只读*/
 ALTER TABLESPACE TBS_DM_DAT  READY ONLY;
 
 /*表空间读写*/
 ALTER TABLESPACE TBS_DM_DAT READ WRITE;
 
 /*表空间改名*/
 ALTER TABLESPACE TBS_DM_DAT RENAME TO TBS_DM_DATA;
 
 /*开启自动扩展*/
 ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' AUTOEXTEND ON;
 
 /*关闭自动扩展*/
 ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' AUTOEXTEND OFF;
 
 
 ---------------------------------------------------