本篇文章给大家带来了关于oracle的相关知识，在日常的oralce使用中最长遇到的问题就是oralce的表空间满了,数据无法写入报错，下面一起来看一下，希望对大家有帮助。
推荐教程：《oracle视频教程》
一、查看表空间使用率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查看表空间使用率以及实例解决爆满问题的详细内容。