Oracle表空间专题系列

使用场景：
大数据时代，数据的体量越来越到，对数据库服务器的要求也越来越高，经常发生用户表空间不够用，需要扩展用户表空间，以及临时表空间。下面就来谈谈如何管理这两中表空间，同时也会放出楼主工作中总结的实用SQL管理语句。

临时表空间

• 临时表空间，是用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,将会把数据放入临时表空间里进行排序。像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。

• 当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。

• 临时表空间存储大规模排序操作(小规模排序操作会直接在RAM里完成，大规模排序才需要磁盘排序Disk Sort)和散列操作的中间结果.它跟永久表空间不同的地方在于它由临时数据文件（temporary files）组成的,而不是永久数据文件（datafiles）。临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生redo日志，不过会生成undo日志。

• 创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为ORACLE的临时数据文件是一类特殊的数据文件:稀疏文件（Sparse File）,当临时表空间文件创建时，它只会写入文件头部和最后块信息(only writes to the header and last block of the file)。它的空间是延后分配的.这就是你创建临时表空间或给临时表空间添加数据文件飞快的原因。

  另外，临时表空间是NOLOGGING模式以及它不保存永久类型对象，因此即使数据库损毁，做Recovery也不需要恢复Temporary Tablespace。

---

管理临时表空间

-- 查询临时表空间大小和位置select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;--增加临时表空间文件ALTER TABLESPACE TEMP ADD tempFILE'D:\APP\WINSVRUSER\ORADATA\ORCL2\TEMP10.DBF' SIZE 32000M;-- 设置临时表空间自动扩展ALTER DATABASE TEMPFILE 'D:\APP\WINSVRUSER\ORADATA\ORCL2\TEMP10.DBF' AUTOEXTEND ON NEXT 5G MAXSIZE UNLIMITED;

---

用户表空间

• 用户表空间，名称一般为USERS，在存储硬盘中以dbf格式存在（linux和windows都是），例如：’/data/u01/app/oracle/oradata/orcl/users09.dbf’
  当需要增加用户表空间时，只需要增大此文件的大小即可，最大限制是32GB，通常是设置成32000MB。
• 下面是我工作中总结的一些实用的管理表空间的命令（SQL），久经考验，只需要稍微更改下就可以使用，希望对经常管理数据库的同学有帮助。

-- 查看各个用户表空间WITH tablespace1  AS (   SELECT  tablespace_name, ROUND(SUM(bytes)/1024/1024,2) AS zj   FROM    dba_data_files   GROUP BY tablespace_name),tablespace2  AS (   SELECT  tablespace_name, ROUND(SUM(bytes)/1024/1024,2) AS wsy   FROM    dba_free_space   GROUP BY tablespace_name)SELECT tablespace1.tablespace_name,zj,zj - wsy ysy,wsy FROM   tablespace1,tablespace2WHERE  tablespace1.tablespace_name=tablespace2.tablespace_name;--索引所占空间select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_type ='INDEX' group by segment_name;--表占空间select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name;--查看用户段select * from  user_segments;-- 查看用户表select * from user_tables t;-- 查看用户表精简信息select table_name,tablespace_name, num_rows,last_analyzed from user_tables t; --分区所占空间select segment_name,sum(bytes)/1024/1024 Mbytes from user_segments where segment_type='TABLE PARTITION' group by segment_name;-- 各表占空间select OWNER,       t.segment_name,       t.segment_type,       sum(t.bytes / 1024 / 1024) mmm  from dba_segments t where t.segment_type = 'TABLE' and t.owner = 'CHANGZHOU' group by OWNER, t.segment_name, t.segment_type order by mmm desc;-- 查看表空间select * from dba_data_files;-- 查看表空间，简洁版select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name;--增加表空间文件ALTER TABLESPACE USERS ADD DATAFILE'/data/u01/app/oracle/oradata/orcl/users09.dbf' SIZE 32000M;--清除的方法如下： purge table origenal_tableName; purge index origenal_indexName; --查询垃圾信息，可以用如下SQL语句： SELECT t.object_name,t.type ,t.original_name FROM user_recyclebin t; --现在发现，原来还有这个命令： PURGE recyclebin; --删除Table 不进入Recycle的方法： drop table tableName purge;-- 同时查看多个表的数据量select 'KC21_01' ,COUNT(*) as nums from table_name tunion allselect 'shaoxingdata.KC21_01',count(*) from table_name t