Oracle-表空间

一、表空间概念

       表空间是Oracle数据库中的逻辑结构。是组织数据和进行空间分配的逻辑结构，可以将表空间看成是数据库对象的容器。

       物理上，表空间由一个或者多个数据文件组成。

       一个数据库通常包括 SYSTEM       SYSAUX    TEMP三个默认表空间，一个或多个临时表空间，一个撤销表空间和几个应用程序使用的表空间。

 1、表空间的类型

    系统表空间：

    包括SYSTEM   SYSAUX表空间，是所有数据库必需的，且自动创建，一般存放oracle的数据字典表及相应数据。

    永久表空间：

    保存永久性数据，如系统数据、应用数据。

    每个用户都会被分配一个永久表空间来保存其相关数据。

    临时表空间：

    主要是在查询带有排序算法时使用，用完后立即释放，一般安装后只有一个TEMP表空间。

    大文件表空间和小文件表空间：

     大文件表空间(bigfile tablespace)是为超大型数据库设计的，当数据库有上千个数据文件时，更新数据文件头部信息的操作可能会话费很长时间，使用大文件表空间可以减少数据文件的数量，从而减少更新时间。

     小文件表空间是默认的创建表空间的类型，最多可以放置1022个数据文件，一个数据库最多可以放置64k个数据文件。

 2、表空间管理

1、创建表空间

create tablespace ts1               --指定表空间名称
  datafile 'c:/tablespace/ts1.dbf'  --数据文件
  size 100M                         --初始大小
  autoextend on                     --自动增长
  next 10M                          --每次自动增长大小
  maxsize 2048M                     --数据文件最大容量
  extent management local           --表空间采用本地表空间管理

 

 2、表空间数据文件

 

--增加数据文件
alter tablespace ts1
  add datafile 'c:/tablespace/ts2.dbf'
  size 100M
  autoextend on
  next 10M
  maxsize 2048M
--删除数据文件
alter tablespace ts1
  drop datafile 'c:/tablespace/ts2.dbf'
--移动数据文件
  
1.1：连接数据库  
     SQL> conn sysdba/manage as sysdba  
          已连接。  
1.2：把要移动数据文件的表空间脱机
     SQL> ALTER TABLESPACE TBS_EDS_DAT OFFLINE NORMAL ;
          表空间已更改。
1.3：移动物理数据文件。
1.4：重命名文件
     SQL> ALTER DATABASE  RENAME FILE 'C:\TABLESPACES\TS1.DBF' TO 'G:\TABLESPACES\TS1.DBF';
          数据库已更改。
1.5：表空间联机
     SQL> ALTER TABLESPACE TBS_DM_DAT ONLINE;
          表空间已更改。
1.6：如果要删除以前的数据文件，则必须先关闭数据库，手动删除文件，否则会报错。
     SQL> shutdow immediate
          数据库已经关闭。
            已经卸载数据库。
            ORACLE 例程已经关闭。
     SQL> startup
          ORACLE 例程已经启动。
            Total System Global Area 612368384 bytes
          Fixed Size 1250428 bytes
          Variable Size 104860548 bytes
          Database Buffers 499122176 bytes
          Redo Buffers 7135232 bytes
          数据库装载完毕。
            数据库已经打开。

3、 查看表空间和数据文件（转载）

 

--查看表空间--
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          --包含所有临时数据文件的基本信息

--查看数据库默认的TEMP表空间--
SELECT PROPERTY_NAME, PROPERTY_VALUE  FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'

--查看数据库默认的永久表空间，如果不为用户指定永久表空间，则使用默认的表空间
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES;

--查看默认的表空间类型--
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TBS_TYPE'

--查看用户默认的表空间--
SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS

--查看表空间情况--
SELECT * FROM DBA_TABLESPACES

--查看表空间的数据文件--
SELECT * FROM DBA_DATA_FILES;   --永久表空间/UNDO表空间
SELECT * FROM V$TEMPFILE        --临时表空间

--查看表空间使用情况
SELECT A.TABLESPACE_NAME                AS TABLESPACE_NAME, 
   ROUND(A.BYTES/(1024*1024*1024),2)    AS "TOTAL(G)"     , 
   ROUND(B.BYTES/(1024*1024*1024),2)    AS "USED(G)"      , 
   ROUND(C.BYTES/(1024*1024*1024),2)    AS "FREE(G)"      , 
   ROUND((B.BYTES * 100) / A.BYTES,2)   AS "% USED"       , 
   ROUND((C.BYTES * 100) / A.BYTES,2)   AS "% FREE" 
   FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C 
   WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME 
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

--计算表空间使用情况(考虑了数据文件自动增长情况)  
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

4、删除表空间

 

--删除表空间
drop tablespace ts1                --删除
  including contents and datafiles  --指定同时删除数据文件

5、表空间状态

--查看表空间状态--
SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES;
--脱机
alter tablespace ts1 offline;
--联机
alter tablespace ts1 online;
--只读
alter tablespace ts1 read only;
--读写
alter tablespace ts1 read write;

 

6、关于默认表空间

创建数据库用户时，如果没有指定默认的表空间，则使用数据库默认配置的表空间：

     Oralce 9i:          永久表空间  SYSTEM，临时表空间 TEMP

     Oracle 10/11g：永久表空间 USERS，  临时表空间  TEMP

ORACLE允许使用自定义的表空间作为默认永久性表空间：

      数据库级别：
              永久表空间
              SQL>ALTER DATABASE DEFAULT TABLESPACE USER;
              临时表空间
              SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
      用户级别
              SQL>ALTER USER USERNAM DEFAULT TABLESPACE NEW_TABLESPACE_NAME
      查看用户对应的默认表空间
              SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS

 

 

 参考：http://www.cnblogs.com/kerrycode/p/3418694.html

补充：

1、表空间的管理方式主要有两种：
      字典管理  在数据字典中管理表空间的空间分配
      本地管理  在每个数据文件中使用位图来管理空间的分配，表空间中所有区的分配信息都保存在该表空间对应的数据文件的头部。
      oracle 8i以前只有字典管理，之后引入本地管理
      本地管理的表空间的优点：
             速度快，存储空间的分配和回收只是简单地改变数据文件中的位图，而不像字典管理方式还需要修 改数据库。
             改善了存储管理的性能。某些在字典管理方式下的存储分配有时会产生递归操作，从而影响了系统的性能，使用本地管理方式不会产生递归操作。
             无碎片。
             更易于DBA维护。

2、设置脱机状态，可以使用下面4个参数来控制脱机方式

      NORMAL 该参数表示将表空间以正常方式切换到脱机状态，在进入脱机状态过程中，ORACLE会执行一次检查点， 将SGA区中与该表空间相关的脏缓存块写入数据文件中，然后再关闭表空间的所有数据文件。如果在这过程中没有发生任何错误，则可以使用NORMAL参数，这也是默认的方式。

      TEMPORARY 该参数将表空间以临时方式切换到脱机状态。这时ORACLE在执行检查点时并不会检查各个数据文件的状态，即使某些数据文件处于不可用状态，ORACLE也会忽略这些错误。这样将表空间设置为联机状态时，可能需要进行数据恢复。

       IMMEDIATE 该参数将表空间以立即方式切换到脱机状态，这时ORACLE不会执行检查点，也不会检查数据文件是否可用。而是直接将属于表空间的数据文件设置为脱机状态。下一次将表空间恢复为联机状态时必须进行数据库恢复。

       FOR RECOVER 该参数将表空间以用于恢复方式切换到脱机状态，如果要对表空间进行基于时间的恢复，可以使用这个参数将表空间切换到脱机状态。