确定不同文件进行收缩的空间

确定不同文件进行收缩的空间[@more@]

确定不同文件进行收缩的空间
col name for a50
col resizecmd for a80
select a.file#, a.name, a.bytes / 1024 / 1024 CurrentMB,
2 ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
3 (a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
4 'alter database datafile ''' || a.name || ''' resize ' ||
5 ceil ( HWM * a.block_size) / 1024 / 1024 || 'M;' ResizeCmd
6 from v$datafile a,
7 (select file_id, MAX(block_id + blocks - 1 ) HWM
8 FROM DBA_EXTENTS
9 GROUP BY file_id) b
10 where a.file# = b.file_id(+)
11 and ( a.bytes - HWM * a.block_size) >0
12 order by 5
13 /

FILE# NAME CURRENTMB RESIZETO RELEASEMB
---------- -------------------------------------------------- ---------- ---------- ----------
RESIZECMD
--------------------------------------------------------------------------------
9 /home/oracle/tom01.dbf 10 9.0625 .9375
alter database datafile '/home/oracle/tom01.dbf' resize 9.0625M;

8 /backup/db_backup/emrep/aq_data_01.dbf 548 547.0625 .9375
alter database datafile '/backup/db_backup/emrep/aq_data_01.dbf' resize 547.0625
M;

7 /backup/db_backup/emrep/perfstat01.dbf 559 558.0625 .9375
alter database datafile '/backup/db_backup/emrep/perfstat01.dbf' resize 558.0625
M;

4 /backup/db_backup/emrep/users01.dbf 5 2.1875 2.8125
alter database datafile '/backup/db_backup/emrep/users01.dbf' resize 2.1875M;

3 /backup/db_backup/emrep/sysaux01.dbf 130 125.0625 4.9375
alter database datafile '/backup/db_backup/emrep/sysaux01.dbf' resize 125.0625M;

1 /backup/db_backup/emrep/system01.dbf 430 421.0625 8.9375
alter database datafile '/backup/db_backup/emrep/system01.dbf' resize 421.0625M;

5 /backup/db_backup/emrep/mgmt.dbf 2140 2130.0625 9.9375
alter database datafile '/backup/db_backup/emrep/mgmt.dbf' resize 2130.0625M;

6 /backup/db_backup/emrep/mgmt_ecm_depot1.dbf 200 120.0625 79.9375
alter database datafile '/backup/db_backup/emrep/mgmt_ecm_depot1.dbf' resize 120
.0625M;

10 /home/oracle/tom02.dbf 100 3.0625 96.9375
alter database datafile '/home/oracle/tom02.dbf' resize 3.0625M;

2 /backup/db_backup/emrep/undotbs01.dbf 350 219.25 130.75
alter database datafile '/backup/db_backup/emrep/undotbs01.dbf' resize 219.25M;

10 rows selected.

根据查询结果进行数据文件的收缩：
alter database datafile 'XXXXX' resize XXXXM;

resize对于文件中间的自由空间无能为力，为了释放更多的空间，可以尝试将文件末尾的对象移走，使得自由空间调整到
文件末尾，从而可以通过Resize释放这些空间，此非常类似于OS的空间整理与分区调整。以下SQL查询出每个数据文件最末
端存储的对象：
"afiedt.buf" 4L, 170C written

1 select /*+ rule */ owner, segment_name, segment_type from dba_extents
2 where ( file_id, block_id) in
3* ( select file_id, max( block_id) from dba_extents group by file_id)
> /

OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------ --------------------
SYS TEST TABLE
SYS _SYSSMU22$ TYPE2 UNDO
SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
KYO1 TEST1 TABLE
SYSMAN SYS_C005740 INDEX
SYSMAN SYS_LOB0000016203C00007$$ LOBSEGMENT
PERFSTAT STATS$EVENT_HISTOGRAM_PK INDEX
TEST TEST_DATA_TB_NAME_IDX INDEX
SYS TT2 TABLE
SYS TT2 TABLE

10 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9523925/viewspace-1037607/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9523925/viewspace-1037607/