一、查询表空间常规语句
1.查询表空间使用情况
select upper(f.tablespace_name) “表空间名”,
d.tot_grootte_mb “表空间大小(m)”,
d.tot_grootte_mb – f.total_bytes “已使用空间(m)”,
to_char(round(( d.tot_grootte_mb – f.total_bytes ) / d.tot_grootte_mb * 100, 2), ‘990.99’)
|| ‘%’ “使用比”,
f.total_bytes “空闲空间(m)”,
f.max_bytes “最大块(m)”
from (select tablespace_name,
round(sum(bytes) / ( 1024 * 1024 ), 2) total_bytes,
round(max(bytes) / ( 1024 * 1024 ), 2) max_bytes
from sys.dba_free_space
group by tablespace_name) f,
(select dd.tablespace_name,
round(sum(dd.bytes) / ( 1024 * 1024 ), 2) tot_grootte_mb
from sys.dba_data_files dd
group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name
order by 1
2、查询表空间的free space
select tablespace_name, count(*) as extends,round(sum(bytes) / 1024 / 1024, 2) as mb,sum(blocks) as blocks from dba_free_space group by tablespace_name;
3、查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as mb from dba_data_files group by 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;
二、解决表空间满实例
1、20160613处理ydjc表空间满，处理情况
1）查询表空间使用效率
select upper(f.tablespace_name) “表空间名“,
d.tot_grootte_mb “表空间大小(m)”,
d.tot_grootte_mb – f.total_bytes “已使用空间(m)”,
to_char(round((d.tot_grootte_mb – f.total_bytes) / d.tot_grootte_mb * 100,
2),
‘990.99’) || ‘%’ “使用比“,
f.total_bytes “空闲空间(m)”,
f.max_bytes “最大块(m)”
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) total_bytes,
round(max(bytes) / (1024 * 1024), 2) max_bytes
from sys.dba_free_space
group by tablespace_name) f,
(select dd.tablespace_name,
round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb
from sys.dba_data_files dd
group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name
order by 1
2）查询文件：
select * from dba_data_files d where d.tablespace_name=’tbs_master’
3）执行增加数据文件
alter tablespace tbs_master add datafile ‘+data_asm/ipsd/tbs_master16.dbf’ size 1g autoextend on next 10m maxsize unlimited
2、20160921处理临时表空间不足问题：
–新增临时表空间
select * from dba_temp_files d where d.tablespace_name=’temp’;
—新增临时表空间
alter tablespace temp add tempfile ‘/data/devgbk/devgbk/temp02.dbf’ size 1g autoextend on next 10m maxsize unlimited;
select d.*,d.user_bytes/d.bytes from dba_temp_files d where d.tablespace_name=’temp’;
—修改已经临时表空间大小
alter database tempfile ‘+data_asm/ipsd/temp03.dbf’ resize 20g;