目录一、查看表空间使用率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) bwhere a.tablespace_name = b.tablespace_nameorder 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) bytesfrom dba_data_filesgroup by tablespace_name) a,(select tablespace_name,sum(bytes) bytesfrom dba_free_spacegroup by tablespace_name) bwhere a.tablespace_name = b.tablespace_nameunion allselect 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) bytesfrom dba_temp_files group by tablespace_name) c,(select tablespace_name,sum(bytes_cached) bytes_usedfrom v$temp_extent_pool group by tablespace_name) dwhere 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) mbfrom dba_segments twhere t.segment_type=\’TABLE\’group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type) torder 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_propertieswhere 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_textfrom v$sort_usage su,v$parameter p,v$session se,v$sql swhere p.name=\’db_block_size\’ and su.session_addr=se.saddr and s.hash_value=su.sqlhashand s.address=su.sqladdrorder 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;
总结
到此这篇关于Oracle查看表空间使用率以及爆满解决方案的文章就介绍到这了,更多相关Oracle查看表空间使用率内容请搜索悠久资源以前的文章或继续浏览下面的相关文章希望大家以后多多支持悠久资源！
