Oracle 表空间详解

目录

• 目录
• 表空间概述
  • 表空间的分类
  • 默认表空间
    • 查看默认的永久表空间
    • 查看默认的TEMP表空间
    • 查看默认的表空间类型
  • 逻辑结构到物理结构的映射
• 对表空间的操作
  • 查看表空间使用情况
  • 查看数据库拥有的表空间
  • 查看表空间中的数据文件
  • 查看用户拥有的表空间
  • 创建表空间
  • 修改表空间
    • 修改用户的默认和临时表空间
    • 修改表空间的状态
    • 修改表空间的数据文件
  • 删除表空间
• 用户表空间限额

表空间概述

Oracle的表空间属于Oracle中的存储结构，是一种用于存储数据库对象(如：数据文件)的逻辑空间，是Oracle中信息存储的最大逻辑单元，其下还包含有段、区、数据块等逻辑数据类型。表空间是在数据库中开辟的一个空间，用于存放数据库的对象，一个数据库可以由多个表空间组成。可以通过表空间来实现对Oracle的调优。（Oracle数据库独特的高级应用）

表空间的分类

• 永久表空间：存储数据库中需要永久化存储的对象，比如二维表、视图、存储过程、索引。
• 临时表空间：存储数据库的中间执行过程，如：保存order by数据库排序，分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空间是通用的，所的用户都使用TEMP作为临时表空间。一般只有tmp一个临时表空间，如果还需要别的临时表空间时，可以自己创建。
• UNDO表空间：保存数据修改前的副本。存储事务所修改的旧址，即被修改之前的数据。当我们对一张表中的数据进行修改的同时会对修改之前的信息进行保存，为了对数据执行回滚、恢复、撤销的操作。

查看system用户的默认表空间和临时表空间：

SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM';

DEFAULT_TABLESPACE         TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSTEM                 TEM

默认表空间

用户在登陆后创建数据库对象时，如果没有指定表空间，那么这些数据就会存储到默认表空间。

查看默认的永久表空间

注意：如果创建用户时，不指定其永久表空间，则会使用默认的表空间。

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE 
2 FROM DATABASE_PROPERTIES;

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------
DEFAULT_TEMP_TABLESPACE              TEMP

DEFAULT_PERMANENT_TABLESPACE         USERS

查看默认的TEMP表空间

系统管理员：

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'
4 ;

PROPERTY_NAME                        PROPERTY_VALUE
------------------------------ ----------------------------
DEFAULT_TEMP_TABLESPACE                  TEMP

普通用户

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS

查看默认的表空间类型

注意：如果不指定表空间类型，就会默认使用DEFAULT_TBS_TYPE参数指定的表空间类型。

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME='DEFAULT_TBS_TYPE';

PROPERTY_NAME                    PROPERTY_VALUE
------------------------------ ------------------

DEFAULT_TBS_TYPE                     SMALLFILE

逻辑结构到物理结构的映射

表空间与数据文件：表空间实际上是由若干个数据文件来构成的，数据文件的位置和大小可以由我们自己来决定。数据被存放在表空间中的数据文件中。

注意：表空间是一种逻辑数据结构，其下最小的数据块会映射到物理结构的磁盘块中，一个数据块由若干个磁盘块组成。

或者由下图来理解：表空间逻辑上有若干个段组成，物理上由多个数据文件组成。

对表空间的操作

表空间的操作方式：

• 字典管理：Oracle中的数据字典可以存储所有的表空间分配信息，但是如果数据库中所有的空间分配都放在数据字典中。容易引起字典争用，而导致性能问题，这是Oracle调优的方向之一。
• 本地管理：表空间分配不放在数据字典，而在每个数据文件头部的第3到第8个块的位图块，来管理空间分配。

查看表空间使用情况

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

查看数据库拥有的表空间

可以通过下面几个系统视图来查看表空间的基本信息：

#1.包含数据库中所有表空间的描述信息
SELECT * FROM DBA_TABLESPACES;  

#2.包含当前用户的表空间的描叙信息
SELECT * FROM USER_TABLESPACES; 

#3.包含从控制文件中获取的表空间名称和编号信息
SELECT * FROM V$TABLESPACE;     

查看表空间中的数据文件

#1.包含数据文件以及所属的表空间的描述信息(永久表空间/UNDO表空间)
SELECT * FROM DBA_DATA_FILES;   

#2.包含临时数据文件以及所属的表空间的描述信息
SELECT * FROM DBA_TEMP_FILES;
SELECT * FROM V$TEMPFILE;

#3.包含从控制文件中获取的数据文件的基本信息，包括它所属的表空间名称、编号等
SELECT * FROM V$DATAFILE;

#4.包含所有临时数据文件的基本信息
SELECT * FROM V$TEMPFILE;  

查看用户拥有的表空间

不同的用户会使用不同的表空间：

• 系统管理员用户使用dba_tablespaces数据字典来查看。

SQL> desc dba_tablespaces               #Oracle管理员级别的数据字典中记录了管理员级别用户所使用的表空间名称、默认表空间和临时表空间。 TABLESPACE_NAME
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME               NOT NULL VARCHAR2(30)
 BLOCK_SIZE                NOT NULL NUMBER
 INITIAL_EXTENT                     NUMBER
 NEXT_EXTENT                        NUMBER
 MIN_EXTENTS                   NOT NULL NUMBER
 MAX_EXTENTS                        NUMBER
 PCT_INCREASE                       NUMBER
 MIN_EXTLEN                     NUMBER
 STATUS                         VARCHAR2(9)
 CONTENTS                       VARCHAR2(9)
 LOGGING                        VARCHAR2(9)
 FORCE_LOGGING                      VARCHAR2(3)
 EXTENT_MANAGEMENT                  VARCHAR2(10)
 ALLOCATION_TYPE                    VARCHAR2(9)
 PLUGGED_IN                     VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT               VARCHAR2(6)
 DEF_TAB_COMPRESSION                    VARCHAR2(8)
 RETENTION                      VARCHAR2(11)
 BIGFILE                        VARCHAR2(3)

SQL> select tablespace_name from dba_tablespaces;             #查看管理员用户使用的表空间名称列表

TABLESPACE_NAME
------------------------------
SYSTEM                             #系统表空间，是永久系统表空间，用于存储SYS用户的表、视图、存储过程对象。
UNDOTBS1                           #存储撤销信息的undo表空间
SYSAUX                             #作为EXAMPLE的辅助表空间
TEMP                               #临时表空间，用户存储SQL语句处理的表示索引信息
USERS                              #永久表空间，存储数据库用户创建的数据库对象
EXAMPLE                            #安装Oracle数据库示例的表空间

• 普通用户使用user_tablespaces数据字典来查看。

SQL> desc user_tablespaces
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME               NOT NULL VARCHAR2(30)
 BLOCK_SIZE                NOT NULL NUMBER
 INITIAL_EXTENT                     NUMBER
 NEXT_EXTENT                        NUMBER
 MIN_EXTENTS                   NOT NULL NUMBER
 MAX_EXTENTS                        NUMBER
 PCT_INCREASE                       NUMBER
 MIN_EXTLEN                     NUMBER
 STATUS                         VARCHAR2(9)
 CONTENTS                       VARCHAR2(9)
 LOGGING                        VARCHAR2(9)
 FORCE_LOGGING                      VARCHAR2(3)
 EXTENT_MANAGEMENT                  VARCHAR2(10)
 ALLOCATION_TYPE                    VARCHAR2(9)
 SEGMENT_SPACE_MANAGEMENT               VARCHAR2(6)
 DEF_TAB_COMPRESSION                    VARCHAR2(8)
 RETENTION                      VARCHAR2(11)
 BIGFILE                        VARCHAR2(3)

SQL> connect scott/tiger    #连接到SCOTT用户
Connected.
SQL> show user;
USER is "SCOTT"
SQL> select tablespace_name from dba_tablespaces;
select tablespace_name from dba_tablespaces
                            *
ERROR at line 1:
ORA-00942: table or view does not exist             #SCOTT用户没有权限去查看系统表空间dba_tablespaces

SQL> select tablespace_name from user_tablespaces;  #但是SCOTT用户可以查看用户表空间user_tablespaces

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE

6 rows selected.

创建表空间

基本语法：

Create [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'fileName.dbf' size xx
 #TEMPFILE|DATAFILE 指的是我们存储的数据文件的类型和名字。
 #size 表示数据文件的大小
 #[TEMPORARY] 如果我们创建的是临时表空间，那么需要在create关键字后加上TEMPORARY的关键字，而且使用TEMPFILE关键字。

注意：如果没有指定数据文件存放的路径，默认会将数据文件存放到Oracle的安装目录下

Example：

SQL> show user;
USER is "SYSTEM"
SQL> create tablespace test1_tablespace datafile 'test1file.dbf' size 10m;

Tablespace created.

SQL> create temporary tablespace temptest1_tablespace tempfile 'tempfile.dbf' size 10m;

Tablespace created.

查看表空间数据文件的存放路径：表空间数据文件都存放在永久性表空间中

SQL> desc dba_data_files;    #数据字典dba_data_file用于存放数据文件的属性。
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                      VARCHAR2(513)
 FILE_ID                        NUMBER
 TABLESPACE_NAME                    VARCHAR2(30)
 BYTES                          NUMBER
 BLOCKS                         NUMBER
 STATUS                         VARCHAR2(9)
 RELATIVE_FNO                       NUMBER
 AUTOEXTENSIBLE                     VARCHAR2(3)
 MAXBYTES                       NUMBER
 MAXBLOCKS                      NUMBER
 INCREMENT_BY                       NUMBER
 USER_BYTES                     NUMBER
 USER_BLOCKS                        NUMBER
 ONLINE_STATUS                      VARCHAR2(7)

SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oracle/dbs/test1file.dbf

修改表空间

修改用户的默认和临时表空间

为了方便以后数据的备份和恢复，仅仅将有价值的数据做备份可以提高效率和节省空间。
基本语法：

ALTER USER username defalut|temporary TABLESPACE tablespace_name;

注意：普通用户没有修改默认表空间的权限，但是可以通过授权来实现普通用户也能够修改默认表空间。

Example：

SQL> show user;
USER is "SYSTEM"

SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM';

DEFAULT_TABLESPACE         TEMPORARY_TABLESPACE
------------------------------ ------------------------------

USERS               TEMP

修改表空间的状态

• 设置表空间的联机或脱机状态：表空间的默认状态是联机状态，如果表空间是脱机状态，那么我们就不能够使用这个表空间了。

基本语法：

ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;

Example：

SQL> alter tablespace test1_tablespace offline;

Tablespace altered.

SQL> select STATUS from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';     // tablespace_name需要使用大些字母

STATUS
---------
OFFLINE

• 设置表空间的只读|可读写状态：表空间默认为可读写状态

注意：如果表空间状态中拥有可read，那么表空间就必须是联机状态的。
基本语法：

ALTER TABLESPACE tablespace_name [read only][read write];

Example：

SQL> alter tablespace test1_tablespace read only;

Tablespace altered.

SQL> select STATUS from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

STATUS
---------
READ ONLY

修改表空间的数据文件

• 增加表空间中的数据文件
  基本语法：

ALTER TABLESPACE tablespace_name ADD DATAFILE'xx.dbf' SIZE xx;

Example：

SQL> alter tablespace test1_tablespace add datafile 'test2_file.dbf' size 10m;

Tablespace altered.

SQL> select file_name from dba_data_fileS where tablespace_name='TEST1_TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oracle/dbs/test1file.dbf
/u01/oracle/dbs/test2_file.dbf

注意：如果发现某个表空间存储空间不足时，可以为表空间添加新的数据文件，扩展表空间大小

SQL> ALTER TABLESPACE TBS_TR_IND ADD DATAFILE '/oradata/rTBS_TR_IND_002.dbf' SIZE 32G AUTOEXTEND OFF

SQL> ALTER TABLESPACE TBS_EDS_DAT
2 ADD DATAFILE 'G:datafileTBS_EDS_DAT01.DBF'
3 SIZE 100M
4 AUTOEXTEND ON
5 NEXT 10M
6 MAXSIZE 20480M;

SQL> ALTER TABLESPACE temp01
2 ADD TMPFILE 'D:ORACLEDATA	emp01_02.dbf' SIZE 10M REUSE;

注意：在添加新的数据文件时，如果同名的操作系统已经存在，ALTER TABLESPACE语句将失败。如果要覆盖同名的操作系统文件时，则必须在后面显示的指定REUSE子句。

• 调整数据文件的大小

ALTER DATABASE DATAFILE '/database/oracle/oradata/gsp/tbs_dm_data_002.dbf' RESIZE 500M;

• 删除数据文件

注意：不能够删除表空间中的第一个数据文件，如果将第一个数据文件删除的话，相当于删除了整个表空间。
基本语法：

ALTER TABLESPACE tablespace_name DROP DATAFILE'filename.dbf';

Example：

SQL> alter tablespace test1_tablespace drop datafile 'test2_file.dbf';

Tablespace altered.

SQL> select file_name from dba_data_fileS where tablespace_name='TEST1_TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
/u01/oracle/dbs/test1file.dbf

删除表空间

基本语法：

DROP TABLESPAC tablespace_name [INCLUDING CONTENTS];

注意：如果你希望在删除表空间的同时将表空间中的数据文件一同删除时，需要加上[INCLUDING CONTENTS]
Example：

SQL> drop tablespace test1_tablespace including contents;

Tablespace dropped.

SQL> select file_name from dba_data_fileS where tablespace_name='TEST1_TABLESPACE';

no rows selected

用户表空间限额

表空间存储限制是用户在某一个表空间中可以使用的存储空间总数。
在创建或修改用户时，可以由参数quota指出。若用户在向表空间存储数据时，超出了此限额，则会产生错误。
错误信息：ORA-01536:space quota exceeded for tablespace tablespacename..’。
可以通过查询字典dba_ts_quotas查看表空间限额信息。

查看用户的表空间配额

#查看所有用户表空间的配额情况 
SELECT * FROM DBA_TS_QUOTAS


#查看当前用户表空间的配额情况   
SELECT * FROM USER_TS_QUOTAS


SQL> DESC DBA_TS_QUOTAS

Name            Type         Nullable Default Comments                                         
--------------- ------------ -------- ------- ------------------------------------------------ 
TABLESPACE_NAME VARCHAR2(30)                  Tablespace name                                  
USERNAME        VARCHAR2(30)                  User with resource rights on the tablespace      
BYTES           NUMBER       Y                Number of bytes charged to the user              
MAX_BYTES       NUMBER       Y                User's quota in bytes.  NULL if no limit         
BLOCKS          NUMBER       Y                Number of ORACLE blocks charged to the user      
MAX_BLOCKS      NUMBER       Y                User's quota in ORACLE blocks.  NULL if no limit 
DROPPED         VARCHAR2(3)  Y                Whether the tablespace has been dropped  

注意：若MAX_BYTES=-1表示没有配额限制