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