Oracle查看表空间使用率以及爆满解决方案详解

一、查看表空间使用率
- 1.查看数据库表空间文件:
- 2.查看所有表空间的总容量：
- 3.查看数据库表空间使用率
- 4.1.查看表空间总大小、使用率、剩余空间
- 4.2.查看表空间使用率(包含temp临时表空间)
- 5.查看具体表的占用空间大小
二、扩展大小或增加表空间文件
- 1.更改表空间的dbf数据文件分配空间大小
- 2.1 为表空间新增一个数据文件(表空间满32G不能扩展则增加表空间文件)
- 2.2 如果是temp临时表新增表空间会报错:
附：查看表空间是否具有自动扩展的能力
总结

一、查看表空间使用率

1.查看数据库表空间文件:

				?

									--查看数据库表空间文件

									select * from dba_data_files;

2.查看所有表空间的总容量：

				?

									--查看所有表空间的总容量

									select dba.TABLESPACE_NAME, sum(bytes)/1024/1024 as MB  

									from dba_data_files dba 

									group by dba.TABLESPACE_NAME;

3.查看数据库表空间使用率

				?

									--查看数据库表空间使用率

									select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1-free.MB / total.MB)* 100, 2) || '%' as Used_Pct 

									from (

									select tablespace_name, sum(bytes) /1024/1024 as MB 

									from dba_free_space group by tablespace_name) free,

									(select tablespace_name, sum(bytes) / 1024 / 1024 as MB 

									from dba_data_files group by tablespace_name) total     

									where free.tablespace_name = total.tablespace_name 

									order by used_pct desc;

4.1.查看表空间总大小、使用率、剩余空间

				?

									--查看表空间总大小、使用率、剩余空间

									select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%"

									from

									(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,

									(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b

									where a.tablespace_name = b.tablespace_name

									order by a.tablespace_name

4.2.查看表空间使用率(包含temp临时表空间)

				?

									--查看表空间使用率(包含临时表空间)

									select * from (

									Select a.tablespace_name,

									(a.bytes- b.bytes) "表空间使用大小(BYTE)",

									a.bytes/(1024*1024*1024) "表空间大小(GB)",

									b.bytes/(1024*1024*1024) "表空间剩余大小(GB)",

									(a.bytes- b.bytes)/(1024*1024*1024) "表空间使用大小(GB)",

									to_char((1 - b.bytes/a.bytes)*100,'99.99999') || '%' "使用率"

									from (select tablespace_name,

									sum(bytes) bytes

									from dba_data_files

									group by tablespace_name) a,

									(select tablespace_name,

									sum(bytes) bytes

									from dba_free_space

									group by tablespace_name) b

									where a.tablespace_name = b.tablespace_name

									union all

									select c.tablespace_name,

									d.bytes_used "表空间使用大小(BYTE)",

									c.bytes/(1024*1024*1024) "表空间大小(GB)",

									(c.bytes-d.bytes_used)/(1024*1024*1024) "表空间剩余大小(GB)",

									d.bytes_used/(1024*1024*1024) "表空间使用大小(GB)",

									to_char(d.bytes_used*100/c.bytes,'99.99999') || '%' "使用率"

									from

									(select tablespace_name,sum(bytes) bytes

									from dba_temp_files group by tablespace_name) c,

									(select tablespace_name,sum(bytes_cached) bytes_used

									from v$temp_extent_pool group by tablespace_name) d

									where c.tablespace_name = d.tablespace_name

									)

									order by tablespace_name

5.查看具体表的占用空间大小

				?

									--查看具体表的占用空间大小

									select * from (

									select t.tablespace_name,t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb

									from dba_segments t

									where t.segment_type='TABLE'

									group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type

									) t

									order by t.mb desc

二、扩展大小或增加表空间文件

1.更改表空间的dbf数据文件分配空间大小

				?

									alter database datafile ‘...\system_01.dbf' autoextend on;

									alter database datafile ‘...\system_01.dbf' resize 1024M;

2.1 为表空间新增一个数据文件(表空间满32G不能扩展则增加表空间文件)

				?

									alter tablespace SYSTEM add datafile '/****' size 1000m autoextend on next 100m;

2.2 如果是temp临时表新增表空间会报错:

0RA-03217: 变更TEMPORARY TABLESPACE 无效的选项

解决方法: datafile改为tempfile

				?

									alter tablespace TEMP01 add tempfile'/****' size 1000m autoextend on next 100m;

针对temp临时表空间使用率爆满问题

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理，但有些时候我们会遇到临时段没有被释放，TEMP表空间几乎满使用率情况；

引起临时表空间增大主要使用在以下几种情况：

1、order by or group by (disc sort占主要部分)；

2、索引的创建和重创建；

3、distinct操作；

4、union & intersect & minus sort-merge joins；

5、Analyze 操作；

6、有些异常也会引起TEMP的暴涨。

解决方法一：用上述方法给temp增加表空间文件

解决方法二：在服务器资源空间有限的情况下，重新建立新的临时表空间替换当前的表空间

				?

									--1.查看当前的数据库默认表空间：

									select * from database_properties

									where property_name='DEFAULT_TEMP_TABLESPACE';

									--2.创建新的临时表空间

									create temporary tablespace TEMP01 tempfile 

									'/home/temp01.dbf' size 31G;

									--3.更改默认临时表空间

									alter database default temporary tablespace TEMP01;

									--4.删除原来的临时表空间

									drop tablespace TEMP02 including contents and datafiles;

									--如果删除原来临时表空间报错ORA-60100：由于排序段，已阻止删除表空间...

									--（说明有语句正在使用原来的临时表空间，需要将其kill掉再删除，此语句多为排序的语句）

									--查询语句

									Select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))as Space,

									tablespace,segtype,sql_text

									from v$sort_usage su,v$parameter p,v$session se,v$sql s

									where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash

									and s.address=su.sqladdr

									order by se.username,se.sid;

									--删除对应的'sid,serial#'

									alter system kill session 'sid,serial#'

附：查看表空间是否具有自动扩展的能力

				?

									--查看表空间是否具有自动扩展的能力     

									SELECT T.TABLESPACE_NAME,D.FILE_NAME,     

									D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS     

									FROM DBA_TABLESPACES T,DBA_DATA_FILES D     

									WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME     

									 ORDER BY TABLESPACE_NAME,FILE_NAME;