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,tablespace2
WHERE  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_space
from dba_data_files
order 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 t
union all
select 'shaoxingdata.KC21_01',count(*) from table_name t