收缩表空间（ORA-03297）

 

 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;