處理ORA-01578錯時有用的分割槽操作

在遇到下述錯時，如果是index,直接drop掉index重建就ok,

如果是table一般有兩種解決方法 (關於判斷塊所在對像的型別,見另一個貼子)

[@more@]

Wed Nov 18 15:16:17 2009
Errors in file /oracle/app/admin/diag/rdbms/start/start10/trace/start10_p004_13490.trc (incident=2152710):
ORA-01578: ORACLE 鏁版嵁鍧楁崯鍧?(鏂囦歡鍙?5782, 鍧楀彿 85299)
ORA-01110: 鏁版嵁鏂囦歡 5782: '+NL02/start/datafile/isdh03m200907090'
ORA-26040: 鏁版嵁鍧楁槸浣跨敤 NOLOGGING 閫夐」鍔犺澆鐨?
Incident details in: /oracle/app/admin/diag/rdbms/start/start10/incident/incdir_2152710/start10_p004_13490_i2152710.trc

1) 從備份中restore df,然後recover df;

最近一次level 0備份存在, 利用Block Media Recovery 只recover壞塊

2) ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10'; --掃描表時會skip 壞塊

Execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('',''); --可以標記壞塊，跳過去

跳過壞塊後,接下來就是將壞塊對應的分割槽裡的資料select出來導到一張普通表, 然後將有壞塊的分割槽drop掉,再將其下一個分割槽split成2個,將普通表與split出來的第一個分割槽exchange

#從將普通表"insert"到分割槽表中間的過程演示如下

CREATE TABLE test_o
( object_id ,
object_name,
owner
)noLOGGING
PARTITION BY RANGE ( object_id)
( PARTITION test_o_p1 VALUES LESS THAN ( 1001 ),
PARTITION test_o_p2 VALUES LESS THAN ( 20001 ),
PARTITION test_o_p3 VALUES LESS THAN ( 100001 ),
PARTITION test_o_p4 VALUES LESS THAN ( 1000001 )
)ENABLE ROW MOVEMENT
as
select object_id,object_name,owner
from dba_objects;

SQL> set linesize 1000
SQL> col PARTITION_NAME for a20
SQL> col HIGH_VALUE for a10
SQL> select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION from dba_tab_partitions where TABLE_NAME='TEST_O';
PARTITION_NAME HIGH_VALUE PARTITION_POSITION
-------------------- ---------- ------------------
TEST_O_P1 1001 1
TEST_O_P2 20001 2
TEST_O_P3 100001 3
TEST_O_P4 1000001 4

create table test_f
( object_id ,
object_name,
owner
)noLOGGING
as select object_id,object_name,owner
from TEST_O where object_id between 2000 and 10000;

SQL> select count(*) from test_f;

COUNT(*)
----------
7784

SQL> alter table TEST_O drop partition TEST_O_P2;

Table altered.

SQL> alter table TEST_O add partition TEST_O_P2 values less than ( 20001 );
alter table TEST_O add partition TEST_O_P2 values less than ( 20001 )
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

SQL> alter table TEST_O add partition TEST_O_P2 values less than ( maxvalue );

Table altered.

SQL> select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION from dba_tab_partitions where TABLE_NAME='TEST_O';

PARTITION_NAME HIGH_VALUE PARTITION_POSITION
-------------------- ---------- ------------------
TEST_O_P1 1001 1
TEST_O_P3 100001 2
TEST_O_P4 1000001 3
TEST_O_P2 MAXVALUE 4

SQL> select count(*) from TEST_O partition(TEST_O_P2);

COUNT(*)
----------
0
ALTER TABLE stocks
EXCHANGE PARTITION p3 WITH TABLE stock_table_3;

SQL> alter table TEST_O exchange partition TEST_O_P2 with table test_f;
alter table TEST_O exchange partition TEST_O_P2 with table test_f
*
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition

#drop p2
SQL> alter table TEST_O drop partition TEST_O_P2;

Table altered.
SQL> select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION from dba_tab_partitions where TABLE_NAME='TEST_O';

PARTITION_NAME HIGH_VALUE PARTITION_POSITION
-------------------- ---------- ------------------
TEST_O_P1 1001 1
TEST_O_P3 100001 2
TEST_O_P4 1000001 3

#split part
SQL> alter table TEST_O split partition
2 TEST_O_P3 at (20001) into (
3 partition TEST_O_P2 ,PARTITION TEST_O_P3);

Table altered.

SQL> select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION from dba_tab_partitions where TABLE_NAME='TEST_O';

PARTITION_NAME HIGH_VALUE PARTITION_POSITION
-------------------- ---------- ------------------
TEST_O_P1 1001 1
TEST_O_P2 20001 2
TEST_O_P3 100001 3
TEST_O_P4 1000001 4

SQL> SQL> alter table TEST_O exchange partition TEST_O_P2 with table test_f;

Table altered.
SQL> select count(*) from TEST_O partition(TEST_O_P2);

COUNT(*)
----------
7784

也可以truncate掉分割槽再exchange,這樣不必split 分割槽