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

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

select * from dba_data_files;

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

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

from dba_data_files dba

group by dba.TABLESPACE_NAME;

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

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 ;

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

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

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

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

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

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

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

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

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

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

--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;