收縮表空間ORA-03297錯誤解決

531968912發表於2016-09-23

    最近導一個空庫到資料庫後,發現佔用的表空間非常大,執行表收縮(SHRINK SPACE CASCADE)後,發現實際佔用的空間不到1%。

但是收縮表空間大小提示錯誤
    ALTER DATABASE DATAFILE 'D:\ora_tablespace\GCOMM2.dbf' RESIZE 5000M;
    提示:ORA-03297: file contains used data beyond requested RESIZE value 

   原因1:壓縮表空間大小,最小必須是該表空間目前最大塊段的大小
   原因2:表的初始大小分配的太大


   一、徵對原因1,可以採用移動表空間的方法來減小block_id
--找出目前GCOMM2表空間最大的block_id
SELECT MAX(block_id)
  FROM dba_extents
 WHERE tablespace_name = 'GCOMM2';
MAX(BLOCK_ID)
-------------
       994816

--計算目前最大塊的段所佔用的空間(該資料庫的block大小是8092)
SQL> show parameter db_block_size ;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
 
SELECT 994816*8192/1024/1024 FROM dual;
994816*8192/1024/1024
---------------------
                 7772
因此目前該表空間只能收縮到7772M左右
ALTER DATABASE DATAFILE 'D:\ora_tablespace\GCOMM2.dbf' RESIZE 7772M;

--透過移動移動表空間來收縮表空間大小步驟
1.建立表空間用於將較大block_id的表移到此表空間
CREATE TABLESPACE "gcomm_bk" DATAFILE 'D:\ora_tablespace\gcomm_bk.dbf' SIZE 2048M 
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 
2.找出需要移動表空間的表並將這些表移到剛建立的表空間

--收縮表 


SELECT DISTINCT 'alter table ' || segment_name ||
                ' SHRINK SPACE CASCADE; '
  FROM dba_extents
 WHERE tablespace_name = 'GCOMM2'
   AND segment_type = 'TABLE'
   AND file_id = 11
   AND block_id > 30336;
 --移動表空間


SELECT DISTINCT 'alter table ' || segment_name ||
                ' move tablespace "gcomm_bk"; '
  FROM dba_extents
 WHERE tablespace_name = 'GCOMM2'
   AND file_id = 11
   AND block_id > 30336/*872704*/
   AND segment_type = 'TABLE';
移動表空間過程中出現了這個錯誤:ORA-01658: 無法為表空間 gcomm_bk 中的段建立 INITIAL 區
原因:表空間不夠。
解決:將該表空間設為自動擴充套件或者直接擴大該表空間

注:如果是smallfile 表空間無法設為自動擴充套件,則必須手動將這些表空間先加大
ALTER DATABASE DATAFILE 'D:\ora_tablespace\gcomm_bk.dbf' RESIZE 7000M;

3.重建該空間的索引
SELECT DISTINCT 'alter INDEX ' || segment_name ||
                ' REBUILD TABLESPACE "gcomm_bk"; '
  FROM dba_extents
 WHERE tablespace_name = 'GCOMM2'
   AND file_id = 11
   AND block_id > /*30336*/872704
   AND segment_type = 'INDEX';

4.再次檢視此時gcomm2表空間最大塊的段
SELECT MAX(block_id)
  FROM dba_extents
 WHERE tablespace_name = 'GCOMM2';

SELECT 30336*8192/1024/1024 FROM dual; 
5.收縮表空間(執行成功)
ALTER DATABASE DATAFILE 'D:\ora_tablespace\GCOMM2.dbf' RESIZE 500M;
6.再將表移回來
SELECT DISTINCT 'alter table ' || segment_name ||
                ' move tablespace GCOMM2; '
  FROM dba_extents
 WHERE tablespace_name = 'gcomm_bk'
   AND segment_type = 'TABLE';

7.重建索引

SELECT DISTINCT 'alter INDEX ' || segment_name ||
                ' REBUILD TABLESPACE GCOMM2; '
  FROM dba_extents
 WHERE tablespace_name = '"gcomm_bk"'
   AND file_id = 11
   AND block_id > 30336
   AND segment_type = 'INDEX';

二、徵對原因2可透過減小表或索引的初始化大小來收縮表空間
--徵對塊大的表 減小其初始分配大小
SELECT DISTINCT 'alter table ' || segment_name ||
                ' MOVE STORAGE(INITIAL 1M) ;'
  FROM dba_extents
 WHERE tablespace_name = 'GCOMM2'
   AND segment_type = 'TABLE'
   AND file_id = 11
   AND block_id > 30336;

--徵對塊大的索引減小其初始分配大小
SELECT DISTINCT 'alter index ' || segment_name ||
                ' REBUILD  tablespace GCOMM2 storage(initial 64K next 1M pctincrease 0);'
  FROM dba_extents
 WHERE tablespace_name = 'GCOMM2'
   AND segment_type = 'INDEX'
   AND file_id = 11
   AND block_id > 30336;

然後再收縮表空間
ALTER DATABASE DATAFILE 'D:\ora_tablespace\GCOMM2.dbf' RESIZE 1000M;
執行成功,原先為7G多。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25462274/viewspace-2125362/,如需轉載,請註明出處,否則將追究法律責任。

相關文章