处理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 分区

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94384/viewspace-1028832/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94384/viewspace-1028832/