Alter database datafile resize extents

转自  http://space.itpub.net/8750110/viewspace-167602

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' resize721m;