##缩小表空间

select /*+ ordered use_hash(a,c) */

'alter database datafile '''||a.file_name||''' resize '

||round(a.filesize - (a.filesize - c.hwmsize-100) *0.8)||'M;',

a.filesize,

c.hwmsize

from

(

select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files

) a,

(

select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents

group by file_id) c

where a.file_id = c.file_id

and a.filesize - c.hwmsize > 100;

##查看磁盘

set linesize 150

col header_status for a15

col mount_status for a15

col mode_status for a15

col name for a15

col path for a15

col state for a15

select HEADER_STATUS,MOUNT_STATUS,MODE_STATUS,NAME,PATH,STATE from v$asm_disk;

##DROP磁盘组

......

##查看rebalance

select * from v$asm_operation;

alter diskgroup CXMGDB2DG rebalance power 11 nowait;

ITPub博客

缩小表空间，回收磁盘