obj$坏块情况下exp导出单个表解决方案

在前面一篇(obj$坏块exp不能执行原因探讨)已经研究了在obj$出现坏块的情况下，导致exp导出单个表不能成功的原因，这篇给出解决方案
1.重新创建exu81javt视图

SQL> CREATE OR REPLACE view exu81javt (objid) AS
  2      SELECT  obj#
  3          FROM    sys.obj$
  4          WHERE   name = 'oracle/aurora/rdbms/DbmsJava' AND
  5                  type# = 29 AND
  6                  owner# = 0 AND
  7                  status = 1
  8   /    

View created.

SQL> GRANT SELECT ON sys.exu81javt TO PUBLIC;

Grant succeeded.

SQL>  set autot  trace
SQL> SELECT COUNT(*)      FROM   SYS.EXU81JAVT;


Execution Plan
----------------------------------------------------------
Plan hash value: 2521745379

---------------------------------------------------------------------------------------

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |        |     1 |    35 |     4   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |        |     1 |    35 |            |      |

|*  2 |   TABLE ACCESS BY INDEX ROWID| OBJ$   |     1 |    35 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | I_OBJ2 |     1 |       |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATUS"=1)
   3 - access("OWNER#"=0 AND "NAME"='oracle/aurora/rdbms/DbmsJava' AND
              "TYPE#"=29)
       filter("TYPE#"=29)

通过对这个视图的重新创建，是的原来需要对obj$表全表扫描，改为走I_OBJ2索引，从而避免了部分坏块导致的exp异常。

2.测试exp导出单表

[oracle@node1 tmp]$ exp "'/ as sysdba'" tables=chf.t_undo file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log INDEXES =n \
> COMPRESS =n CONSISTENT =n GRANTS =n STATISTICS =none TRIGGERS =n CONSTRAINTS =n

Export: Release 11.2.0.3.0 - Production on Sun Jan 15 23:39:12 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
Current user changed to CHF
. . exporting table                         T_UNDO       1636 rows exported
Export terminated successfully without warnings.

测试证明修改了exu81javt视图后，exp导出单个表成功

3.生成导出脚本

SELECT 'exp "''' || '/ as sysdba''" tables=' || U.NAME || '.' || O.NAME ||
       ' file=' || '&PATH' || U.NAME || '_' || O.NAME || '.dmp log=' ||
       '&PATH' || U.NAME || '_' || O.NAME ||
       '.log buffer=1024000  COMPRESS =N STATISTICS =NONE'
  FROM TAB$ T, OBJ$ O, USER$ U
 WHERE O.TYPE# = 2
   AND T.OBJ# = O.OBJ#
   AND U.USER# = O.OWNER#
   AND u.name IN('CHF');

Execution Plan
----------------------------------------------------------
Plan hash value: 3095026863

-----------------------------------------------------------------------------------------

| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |         |    31 |  1829 |    32   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                 |         |    31 |  1829 |    32   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                |         |    33 |  1782 |    31   (0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| USER$   |     1 |    17 |     1   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN         | I_USER1 |     1 |       |     0   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN           | I_OBJ2  |    33 |  1221 |    30   (0)| 00:00:01 |

|   6 |   TABLE ACCESS CLUSTER        | TAB$    |     1 |     5 |     1   (0)| 00:00:01 |

|*  7 |    INDEX UNIQUE SCAN          | I_OBJ#  |     1 |       |     0   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

4.补充说明
1)并非所有的obj$坏块都可以通过该方法，使得exp导出单个表正常
2)在系统确实无救，有不想使用dul/odu的情况下，可以尝试这种方法抢救数据。