网上Oracle表空间可用空间的查询脚本很多,但遇到包含自增数据文件表空间时,虽然可以扩展但大多都会显示已用99%……
给一个包含数据文件自增的表空间可用空间查询方法:
sqlplus / as sysdba <<EOF
--表空间巡检:
set term off
set echo off
set feedback off
set show off
set linesize 15000
col username format a30
col account_status, format a20
col dp.limit format a20
select t1.tablespace_name, round(t1.bytes / 1024 / 1024 / 1024) "used(G)",
round(t2.bytes / 1024 / 1024 / 1024) "total(G)",
round(100 * t1.bytes / decode(t2.bytes,0,1,t2.bytes), 2) ps
from sys.sm\$ts_used t1,
(select tablespace_name,
sum(decode(autoextensible, 'YES', maxbytes, 'NO', bytes, 0)) bytes
from dba_data_files
group by tablespace_name) t2
where t1.tablespace_name = t2.tablespace_name
order by 4 desc;
EOF