--创建测试表

SQL> create table t_dul_drop tablespace czum

  2  as

  3  select * from dba_tables;

Table created.

--备份被删除表数据，便于比较和提供测试表结构

SQL> create table t_dul_drop_bak tablespace users

  2  as select * from t_dul_drop;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> select count(*) from t_dul_drop;

  COUNT(*)

----------

      1785

SQL> drop table chf.t_dul_drop purge;

Table dropped.

SQL> alter system checkpoint;

System altered.

delete from "SYS"."OBJ$" where "OBJ#" = '68474' and "DATAOBJ#" ='68474'

and "OWNER#" = '61' and "NAME" = 'T_DUL_DROP' and "NAMESPACE" = '1' and

"SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('04-FEB-13','DD-MON-RR')

and "MTIME" = TO_DATE('04-FEB-13', 'DD-MON-RR') and "STIME" = TO_DATE('04-FEB-13', 'DD-MON-RR')

and "STATUS" ='1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0'

and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" ='61' and

"SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID ='AAAAASAABAAAPzCAAV';

--dul版本

E:\dul10>dul.exe

Data UnLoader 10.2.4.37 - Oracle Internal Only - on Mon Feb 04 23:49:50 2013

with 64-bit io functions

Copyright (c) 1994 2010 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal use Only

DUL>  ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;

Parameter altered

--扫描所属表空间

DUL> scan tablespace 6;

Scanning tablespace 6, data file 6 ...

  13 segment header and 331 data blocks

  tablespace 6, data file 6: 1279 blocks scanned

Reading EXT.dat 13 entries loaded and sorted 13 entries

Reading SEG.dat 13 entries loaded

Reading COMPATSEG.dat 0 entries loaded

Reading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries

--scan tables得到需求表(可以核对数据样例)

DUL> scan tables;

UNLOAD TABLE OBJNO68474 ( COL001 VARCHAR2(11), COL002 VARCHAR2(30), COL003 VARCHAR2(6)

        , COL004 VARCHAR2(20), COL005 VARCHAR2(30), COL006 VARCHAR2(5), COL007 NUMBER

        , COL008 NUMBER, COL009 NUMBER, COL010 NUMBER, COL011 NUMBER

        , COL012 NUMBER, COL013 NUMBER, COL014 NUMBER, COL015 CHAR

        , COL016 NUMBER, COL017 NUMBER, COL018 VARCHAR2(3), COL019 VARCHAR2(1)

        , COL020 NUMBER, COL021 NUMBER, COL022 NUMBER, COL023 NUMBER

        , COL024 NUMBER, COL025 NUMBER, COL026 NUMBER, COL027 NUMBER

        , COL028 VARCHAR2(10), COL029 VARCHAR2(10), COL030 VARCHAR2(5), COL031 VARCHAR2(7)

        , COL032 NUMBER, COL033 DATE, COL034 VARCHAR2(3), COL035 VARCHAR2(12)

        , COL036 VARCHAR2(1), COL037 VARCHAR2(1), COL038 VARCHAR2(3), COL039 VARCHAR2(7)

        , COL040 VARCHAR2(7), COL041 VARCHAR2(7), COL042 VARCHAR2(8), COL043 VARCHAR2(3)

        , COL044 VARCHAR2(2), COL045 VARCHAR2(15), COL046 VARCHAR2(8), COL047 VARCHAR2(3)

        , COL048 VARCHAR2(3), COL049 VARCHAR2(8), COL050 VARCHAR2(8), COL051 VARCHAR2(5)

        , COL052 VARCHAR2(2), COL053 VARCHAR2(2), COL054 VARCHAR2(3), COL055 VARCHAR2(7) )

    STORAGE( DATAOBJNO 68474 );

--恢复删除表(业务提供表结构)

DUL> unload table t_dul_drop(

  2  OWNER                              VARCHAR2(30),

  3  TABLE_NAME                         VARCHAR2(30),

  4  TABLESPACE_NAME                                    VARCHAR2(30),

  5  CLUSTER_NAME                                       VARCHAR2(30),

  6  IOT_NAME                                           VARCHAR2(30),

  7  STATUS                                             VARCHAR2(8) ,

  8  PCT_FREE                                           NUMBER      ,

  9  PCT_USED                                           NUMBER      ,

 10  INI_TRANS                                          NUMBER      ,

 11  MAX_TRANS                                          NUMBER      ,

 12  INITIAL_EXTENT                                     NUMBER      ,

 13  NEXT_EXTENT                                        NUMBER      ,

 14  MIN_EXTENTS                                        NUMBER      ,

 15  MAX_EXTENTS                                        NUMBER      ,

 16  PCT_INCREASE                                       NUMBER      ,

 17  FREELISTS                                          NUMBER      ,

 18  FREELIST_GROUPS                                    NUMBER      ,

 19  LOGGING                                            VARCHAR2(3) ,

 20  BACKED_UP                                          VARCHAR2(1) ,

 21  NUM_ROWS                                           NUMBER      ,

 22  BLOCKS                                             NUMBER      ,

 23  EMPTY_BLOCKS                                       NUMBER      ,

 24  AVG_SPACE                                          NUMBER      ,

 25  CHAIN_CNT                                          NUMBER      ,

 26  AVG_ROW_LEN                                        NUMBER      ,

 27  AVG_SPACE_FREELIST_BLOCKS                          NUMBER      ,

 28  NUM_FREELIST_BLOCKS                                NUMBER      ,

 29  DEGREE                                             VARCHAR2(20),

 30  INSTANCES                                          VARCHAR2(20),

 31  CACHE                                              VARCHAR2(10),

 32  TABLE_LOCK                                         VARCHAR2(8) ,

 33  SAMPLE_SIZE                                        NUMBER      ,

 34  LAST_ANALYZED                                      DATE        ,

 35  PARTITIONED                                        VARCHAR2(3) ,

 36  IOT_TYPE                                           VARCHAR2(12),

 37  TEMPORARY                                          VARCHAR2(1) ,

 38  SECONDARY                                          VARCHAR2(1) ,

 39  NESTED                                             VARCHAR2(3) ,

 40  BUFFER_POOL                                        VARCHAR2(7) ,

 41  FLASH_CACHE                                        VARCHAR2(7) ,

 42  CELL_FLASH_CACHE                                   VARCHAR2(7) ,

 43  ROW_MOVEMENT                                       VARCHAR2(8) ,

 44  GLOBAL_STATS                                       VARCHAR2(3) ,

 45  USER_STATS                                         VARCHAR2(3) ,

 46  DURATION                                           VARCHAR2(15),

 47  SKIP_CORRUPT                                       VARCHAR2(8) ,

 48  MONITORING                                         VARCHAR2(3) ,

 49  CLUSTER_OWNER                                      VARCHAR2(30),

 50  DEPENDENCIES                                       VARCHAR2(8) ,

 51  COMPRESSION                                        VARCHAR2(8) ,

 52  COMPRESS_FOR                                       VARCHAR2(12),

 53  DROPPED                                            VARCHAR2(3) ,

 54  READ_ONLY                                          VARCHAR2(3) ,

 55  SEGMENT_CREATED                                    VARCHAR2(3) ,

 56  RESULT_CACHE                                       VARCHAR2(7))

 57   STORAGE( DATAOBJNO 68474 );

. unloading table                T_DUL_DROP

DUL: Warning: Recreating file "T_DUL_DROP.ctl"

    1785 rows unloaded

SQL> create table t_dul_drop as select * from t_dul_drop_bak where 1=0;

Table created.

e:\dul10>sqlldr chf/xifenfei control=T_DUL_DROP.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Mon Feb 4 23:35:57 2013

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

Commit point reached - logical record count 64

Commit point reached - logical record count 128

Commit point reached - logical record count 192

Commit point reached - logical record count 256

Commit point reached - logical record count 320

Commit point reached - logical record count 384

Commit point reached - logical record count 448

Commit point reached - logical record count 512

Commit point reached - logical record count 576

Commit point reached - logical record count 640

Commit point reached - logical record count 704

Commit point reached - logical record count 768

Commit point reached - logical record count 832

Commit point reached - logical record count 896

Commit point reached - logical record count 960

Commit point reached - logical record count 1024

Commit point reached - logical record count 1088

Commit point reached - logical record count 1152

Commit point reached - logical record count 1216

Commit point reached - logical record count 1280

Commit point reached - logical record count 1344

Commit point reached - logical record count 1408

Commit point reached - logical record count 1472

Commit point reached - logical record count 1536

Commit point reached - logical record count 1600

Commit point reached - logical record count 1664

Commit point reached - logical record count 1728

Commit point reached - logical record count 1785

SQL> select count(*) from t_dul_drop;

  COUNT(*)

----------

      1785

SQL> select owner,table_name from t_dul_drop where rownum<10;

OWNER                          TABLE_NAME

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

SYS                            IDL_CHAR$

SYS                            IDL_UB2$

SYS                            IDL_SB4$

SYS                            ERROR$

SYS                            SETTINGS$

SYS                            NCOMP_DLL$

SYS                            PROCEDUREJAVA$

SYS                            PROCEDUREC$

SYS                            PROCEDUREPLSQL$

9 rows selected.