DATAFILE的extents的回收resize

 

DATAFILE的extents的回收resize

ORACLE Alter database datafile resize extents

 日月明王blog: http://sunmoonking.spaces.live.com

       LMT TABLESPACE，有时候虽然里面的数据不是很多，但是RESIZE DATAFILE的时候会失败，因为一些OBJECT的EXTENTS已经扩展到DATAFILE的边缘（最大的地方）。下面的SQL可以让我们找到前5个最边缘的OBJECT

select *

  from (

select owner, segment_name,

       segment_type, block_id

  from dba_extents

 where file_id =

   ( select file_id

       from dba_data_files

      where file_name = :FILE )   --用你的DATAFILE代替

 order by block_id desc

       )

 where rownum <= 5

       结果是行如下面的五行

OWNER

--------------------------------------------------------

SEGMENT_NAME

--------------------------------------------------------

SEGMENT_TYPE                           BLOCK_ID

------------------------------------ ----------

PERFSTAT

STATS$PARAMETER

TABLE                                     30345

       讲得到的这些OBJECT通过 ALTER TABLE MOVE或ALTER INDEX REBUILD转移到其他 DATAFILE中去，依次类推，直到满足你要RESIZE的空间为止。

       下面的SQL可以得到ALTER DATABASE DATAFILE RESIZE字句，自动判断可以RESIZE到什么地方。

column value new_val blksize

select value

  from v$parameter

 where name = 'db_block_size'

/

 

select 'alter database datafile ''' ||

 file_name || ''' resize ' ||

 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )

 || 'm;' cmd

from dba_data_files a,

     ( select file_id,

         max(block_id+blocks-1) hwm

         from dba_extents

        group by file_id ) b

where a.file_id = b.file_id(+)

  and

 ceil(blocks*&&blksize/1024/1024)-

      ceil((nvl(hwm,1)*

      &&blksize)/1024/1024 ) > 0

/

3:  ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )

3:  ceil( (nvl(hwm,1)*8192)/1024/1024 )

12:  ceil(blocks*&&blksize/1024/1024)-

12:  ceil(blocks*8192/1024/1024)-

14:       &&blksize)/1024/1024 ) > 0

14:       8192)/1024/1024 ) > 0

 

CMD

--------------------------------------------------------------------------------

alter database datafile 'E:/ORACLE/ORADATA/SIDDB/SYSTEM01.DBF' resize 413m;

alter database datafile 'E:/ORACLE/ORADATA/SIDDB/UNDOTBS01.DBF' resize 75m;

alter database datafile 'E:/ORACLE/ORADATA/SIDDB/CWMLITE01.DBF' resize 10m;

alter database datafile 'E:/ORACLE/ORADATA/SIDDB/DRSYS01.DBF' resize 10m;

alter database datafile 'E:/ORACLE/ORADATA/SIDDB/ODM01.DBF' resize 10m;

alter database datafile 'E:/ORACLE/ORADATA/SIDDB/TOOLS01.DBF' resize 7m;

alter database datafile 'E:/ORACLE/ORADATA/SIDDB/USERS01.DBF' resize 239m;

alter database datafile 'E:/ORACLE/ORADATA/SIDDB/XDB01.DBF' resize 38m;

alter database datafile 'E:/ORACLE/ORADATA/SIDDB/DATA01.DBF' resize 720m;

alter database datafile 'E:/ORACLE/ORADATA/SIDDB/WWM.DBF' resize 1m;

alter database datafile 'D:/ORACLE/ORADATA/SIDDB/DATA02.DBF' resize 1m;

alter database datafile 'D:/ORACLE/ORADATA/SIDDB/DATA04.DBF' resize 1m;

alter database datafile 'D:/ORACLE/ORADATA/SIDDB/DATA03.DBF' resize 1m;

alter database datafile 'E:/ORACLE/ORADATA/SIDDB/INDX01.DBF' resize 1m;

 

       测试下

SQL> alter database datafile 'E:/ORACLE/ORADATA/SIDDB/INDX01.DBF' resize 1m;

       执行成功

 

       或者换个写法直接用一下命令整体察看并得到结果

SELECT

   a.file_id,

   a.file_name

   file_name,

   CEIL( ( NVL( hwm,1 ) * blksize ) / 1024 / 1024 ) smallest,

   CEIL( blocks * blksize / 1024 / 1024 ) currsize,

   CEIL( blocks * blksize / 1024 / 1024 ) -

   CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) savings,

   'alter database datafile ''' || file_name || ''' resize ' ||

   CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 )  || 'm;' cmd

FROM

   DBA_DATA_FILES a,

   (

      SELECT   file_id, MAX( block_id + blocks - 1 ) hwm

      FROM     DBA_EXTENTS

      GROUP BY file_id

   ) b,

   (

      SELECT  TO_NUMBER( value ) blksize

      FROM    V$PARAMETER

      WHERE   name = 'db_block_size'

   )

WHERE

   a.file_id = b.file_id(+)

AND

   CEIL( blocks * blksize / 1024 / 1024 ) - CEIL( ( NVL( hwm, 1 ) * blksize ) / 1024 / 1024 ) > 0

ORDER BY 5 desc

/

   FILE_ID

----------

FILE_NAME

--------------------------------------------------------------------------------

  SMALLEST   CURRSIZE    SAVINGS

---------- ---------- ----------

CMD

--------------------------------------------------------------------------------

        11

E:/ORACLE/ORADATA/SIDDB/DATA01.DBF

       721       3000       2279

alter database datafile 'E:/ORACLE/ORADATA/SIDCDB/DATA01.DBF' resize 721m;