oracle 表空间操作详解

　我们知道了oarcle数据库真正存放数据的是数据文件（data files），Oarcle表空间（tablespaces）实际上是一个逻辑的概念，他在物理上是并不存在的，那么把一组data files 捻在一起就成为一个表空间。说明了表空间是从逻辑上在磁盘上划分了一个区域，用来存储数据文件的。

1 为什么创建表空间

既然表空间是一个逻辑概念，那么它为什么存在？

答：

1.在数据库，为了防止用户存储数量的增加，导致磁盘耗竭，所以通过表空间来限制最大存储容量

2.通过使用表空，可以将不同表放在不同的表空间，那么有益于DBA进行数据的备份和恢复操作

3.提供临时表空间，为排序提供空间，提高排序性能。

等等....

下面的表格显示的是oracle11g自动创建的表空间名称及其说明

名称	分类	说明
EXAMPLE	非系统表空间	示例表空间，存放示例数据库的方案对象信息及其培训资料
SYSAUX	系统表空间	辅助系统表空间，用于减少system表空间的负荷，提高系统的工作效率。是oracle 10g新增加的表空间
SYSTEM	系统表空间	系统表空间，存放关于表空间名称、控制文件、数据文件等管理信息，存放着方案对象（如表，索引，同义词，序列）的定义信息，存放着所有pl/sql 程序（如过程，函数，包，触发器）的源代码，是oracle数据库中最重要的表空间。它属于SYS和SYSTEM方案，仅被SYS和SYSTEM或其他具有足够权限的用户使用。即使是SYS 和SYSTEM用户也不能删除或重命名该空间。他是用户的默认表空间，即当用户在创建一个对象时，如果没有指定特定的表空间，该对象的数据也会被保存在SYSTEM表空间中。
TEMP	系统表空间	临时表空间，存放临时表和临时数据，用于排序。每个数据库都应该有一个（或创建一个）临时表空间，以便在创建用户时将其分配给用户，否则就会将TEMP表空间作为临时表空间。
UNDOTBS1	非系统表空间	重做表空间，存放数据库的有关重做的相关信息和数据
USERS	非系统表空间	用户表空间，存放永久性的用户对象的数据和私有信息，因此也被称为数据表空间。每个数据库都应该有一个（或创建一个）用户表空间，以便在创建用户时将其分配给用户，否则将会使用SYSTEM表空间来保存数据，而这种做法是不好的。一般讲，系统用户使用SYSTEM表空间，而非系统用户使用USERS表空间

2 创建表空间

基本语法：

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

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.

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

解释：从上面创建表空间可以看出，创建表空间就是创建一个数据文件并设置该数据文件能用的磁盘空间最大值。为什么表空间是逻辑概念呢？表空间可以包含一个及以上的数据文件，所以创建的数据文件越多，那么表空间对应的值也越大。所以表空间的大小即所有数据文件的可占用磁盘空间最大值之和。

3 查看表空间数据文件的存放路径

SQL> select file_name,tablespace_name from dba_data_files;
　　
　　FILE_NAME                                      TABLESPACE_NAME
　　--------------------------------------------------------------------------------------------------------------
　　/ora10/product/oradata/ora10/users01.dbf            USERS        　　　#USERS--->用户表空间    
　　/ora10/product/oradata/ora10/sysaux01.dbf           SYSAUX
　　/ora10/product/oradata/ora10/undotbs01.dbf          UNDOTBS1          #UNDO--->dml,dql把数据快照到此，数据提交即消失（用于恢复）
   /ora10/product/oradata/ora10/system01.dbf            SYSTEM           #SYSTEM--->字典表空间，不能被损坏

4 计算表空间使用情况(考虑了数据文件自动增长情况)

    SELECT tbs 表空间名,                                    
    sum(totalM) 总共大小M,                                    
    sum(usedM) 已使用空间M,                                    
    sum(remainedM) 剩余空间M,                                    
    sum(usedM)/sum(totalM)*100 已使用百分比,                            
    sum(remainedM)/sum(totalM)*100 剩余百分比                            
    FROM(                                            
     SELECT b.file_id ID,                                    
     b.tablespace_name tbs,                                    
     b.file_name name,                                    
     b.bytes/1024/1024 totalM,                                    
     (b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM,                        
     sum(nvl(a.bytes,0)/1024/1024) remainedM,                            
     sum(nvl(a.bytes,0)/(b.bytes)*100),                                
     (100 - (sum(nvl(a.bytes,0))/(b.bytes)*100))                            
     FROM dba_free_space a,dba_data_files b                            
     WHERE a.file_id = b.file_id                                
     GROUP BY b.tablespace_name,b.file_name,b.file_id,b.bytes                    
     ORDER BY b.tablespace_name                                
    )                                            
    GROUP BY tbs                                        
                   

4.1 计算数据库表空间使用情况（简化版）

1.查看各表空间分配情况。

select tablespace_name, sum(bytes) / 1024 / 1024  from dba_data_files  

 group by tablespace_name;  

2.查看各表空间空闲情况。

select tablespace_name, sum(bytes) / 1024 / 1024  from dba_free_space  group by tablespace_name;  

5  如果发现某个表空间存储空间不足时

5.1 表空间添加新的数据文件，扩展表空间大小

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:\datafile\TBS_EDS_DAT01.DBF'
3 SIZE 100M
4 AUTOEXTEND ON
5 NEXT 10M
6 MAXSIZE 20480M;

SQL> ALTER TABLESPACE temp01
2 ADD TMPFILE 'D:\ORACLEDATA\temp01_02.dbf' SIZE 10M REUSE;  --增加现有数据文件

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

5.2 调整数据文件的大小

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

总结：从上面的表空间的不足，我们可以知道：

1.表空间表空间的大小是固定不变的，不具有自增长能力的。有自增长能力的是数据文件。

2.所以当表空间不足。解决方法：

• 让对应的数据文件具有增长能力。
• 因设置增长能力时，也会给数据文件设置一个增长上限。若数据文件增长到了最大值(即：没有增长空间了)，我们就需要调整该数据文件大小或者增加一个数据文件。

6 用户表空间限额

表空间存储限制是用户在某一个表空间中可以使用的存储空间总数。 在创建或修改用户时，可以由参数quota指出。

若用户在向表空间存储数据时，超出了此限额，则会产生错误。 错误信息：ORA-01536:space quota exceeded for tablespace tablespacename..’。 

可以通过查询字典dba_ts_quotas查看表空间限额信息。

5.1 查看用户的表空间配额

#查看所有用户表空间的配额情况 
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表示没有配额限制