ora-03297处理：oracle 表空间resize收缩释放高水位线

set linesize   1000
col resize_sql for a70
col datafile_total_size for a20
select 'alter database datafile ''' || a.file_name || ''' resize ' ||
       round(a.filesize - (a.filesize - c.hwmsize) * 0.8) || 'M;' as "resize_sql",
       a.filesize || 'M' as "datafile_total_size",
       c.hwmsize || 'M' as "datafile_shiji_used_size"
  from (select file_id, file_name, round(bytes / 1024 / 1024) as filesize
          from dba_data_files) a,
       (select file_id, round(max(block_id) * 8 / 1024) as HWMsize
          from dba_extents	
         group by file_id) c
 where a.file_id = c.file_id
    and  a.file_id=5;

以上sql可以查找数据文件最大resize的大小，如果想指定到resize的大小，比如resize到2G，就需要用官网提供的脚本找到2G到3G占的数据对象并迁移走：

How to Resize a Datafile (Doc ID 1029252.6)

--以下内容不需要修改，直接用

-- FINDEXT.SQL

-- This script lists all the extents contained in that datafile,
-- the block_id where the extent starts,
-- and how many blocks the extent contains.
-- It also shows the owner, segment name, and segment type.

-- Input: FILE_ID from DBA_DATA_FILES or FILE# from V$DATAFILE

SET ECHO OFF
SET PAGESIZ 25

column file_name format a50
select file_name, file_id from dba_data_files order by 2;

ttitle -
center 'Segment Extent Summary' skip 2

col ownr format a8 heading 'Owner' justify c
col type format a8 heading 'Type' justify c trunc
col name format a30 heading 'Segment Name' justify c
col exid format 990 heading 'Extent#' justify c
col fiid format 9990 heading 'File#' justify c
col blid format 999999 heading 'Block#' justify c
col blks format 999,990 heading 'Blocks' justify c

select owner ownr, segment_name name, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blks
from dba_extents
where file_id = &file_id
order by block_id
/



- SHRINK_DATAFILE.SQL

-- This script lists the object names and types that must be moved in order to resize a datafile to a specified smaller size

-- Input: FILE_ID from DBA_DATA_FILES or FILE# from V$DATAFILE
-- Size in bytes that the datafile will be resized to


SET SERVEROUTPUT ON

DECLARE
     V_FILE_ID NUMBER;
     V_BLOCK_SIZE NUMBER;
     V_RESIZE_SIZE NUMBER;
BEGIN
     V_FILE_ID := &FILE_ID;
     V_RESIZE_SIZE := &RESIZE_FILE_TO;

     SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID;

     DBMS_OUTPUT.PUT_LINE('.');
     DBMS_OUTPUT.PUT_LINE('.');
     DBMS_OUTPUT.PUT_LINE('.');
     DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' BYTES');
     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS');
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in (
          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
          FROM DBA_EXTENTS
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE 
          AND FILE_ID = V_FILE_ID
          AND SEGMENT_TYPE NOT LIKE '%PARTITION%'
          ORDER BY 1) LOOP
               DBMS_OUTPUT.PUT_LINE(my_record.ONAME); 
     END LOOP;

     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS');
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in (
          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
          FROM DBA_EXTENTS
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE
          AND FILE_ID = V_FILE_ID 
          AND SEGMENT_TYPE LIKE '%PARTITION%'
          ORDER BY 1) LOOP 
               DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
     END LOOP;

END;
/

--测试用例：

@SHRINK_DATAFILE.SQL

Enter value for file_id: 5
old 6: V_FILE_ID := &FILE_ID;        
new 6: V_FILE_ID := 5;                     --数据文件号
Enter value for resize_file_to: 10567680   --需要resize的目标大小
old 7: V_RESIZE_SIZE := &RESIZE_FILE_TO;
new 7: V_RESIZE_SIZE := 10567680;
.
.
.
Objects in File 5 that must move in order to resize the file to 10567680 BYTES
=================================================
TEST.TEST18 - OBJECT TYPE = TABLE

以上内容表示你需要resize 到10567680 需要迁移的数据对象，下面是迁移对象的常用语句：

--普通表

alter  table  scott.t1   move  tablespace  XXX parallel 10;


--需要移动的分区表数据
select   DISTINCT 'alter table '|| TABLE_OWNER||'.'|| TABLE_NAME || ' move partition '|| partition_name || ' tablespace TBS_CDB  parallel 10;'   from dba_tab_partitions where TABLE_NAME='FQ_EPAY_MERCHT_TX_DETAIL';


--重建普通索引
set linesize  250
set pagesize  0
select  DISTINCT 'alter index '|| TABLE_OWNER||'.'|| index_name || ' rebuild tablespace TBS_CDB parallel 10;'  from dba_indexes where   status='UNUSABLE';

--重建分区索引
set linesize  250
set pagesize  0
select DISTINCT 'alter index '|| INDEX_OWNER||'.'|| index_name || ' rebuild partition '|| partition_name || ' tablespace TBS_RDB parallel 10;' from dba_ind_partitions where  status='UNUSABLE';