(表空间缩容脚本)]
获取需要释放空间的表空间信息(包含oracle database自有表空间)
drop table systemtbs_detail;
create table systemtbs_detail as select
atablespace_name
abytes// "Sum_MB"
(abytesbbytes)// "used_MB"
bbytes// "free_MB"
round(((abytesbbytes)/abytes)*) "percent_used"
from
(select tablespace_namesum(bytes) bytes from dba_data_files group by tablespace_name) a
(select tablespace_namesum(bytes) bytesmax(bytes) largest from dba_free_space group by tablespace_name) b
where atablespace_name=btablespace_name
order by ((abytesbbytes)/abytes) desc;
select * from systemtbs_detail order by "Sum_MB" desc"free_MB" desc;
获取需要释放空间的应用表空间数据文件使用情况
drop table systemdatafile_space;
create table systemdatafile_space as
select aTABLESPACE_NAME
aFILE_NAME
aBYTES / / total
bsum_free / / free
from dba_data_files a
(select file_id sum(bytes) sum_free
from dba_free_space
group by file_id) b
where aFILE_ID = bfile_id
and aTABLESPACE_NAME in (select tablespace_name
from systemtbs_detail
where (tablespace_name like %CQLT% or
tablespace_name like %CQST%
or tablespace_name like TS% or tablespace_name like IDX%
or tablespace_name like %HX%)
and "Sum_MB" > );
select * from systemdatafile_space;
生成数据文件大小重置脚本在每个数据文件当前实际使用空间大小基础上增加 m 空间
select alter database datafile || file_name || resize ||
round(to_number(total free + )) || M;
from systemdatafile_space;
查看 ASM 磁盘组使用情况
sqlplus / as sysdba <
set feed off
set linesize
set pagesize
set echo off
spool /home/oracle/check_log/chktbslog append
select namestatetypetotal_mbfree_mb from v$asm_diskgroup;
spool off
quit
EOF
