dul实验(exp)

来源：互联网发布：二维码识别源码编辑：程序博客网时间：2024/05/17 20:39

准备数据：[oracle@sgpc146 s01]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 16 00:14:38 2011Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning and Automatic Storage Management optionsSQL> create table "000".tcyang as select * from dba_objects;Table created.Elapsed: 00:00:00.18SQL> insert into "000".tcyang select * from "000".tcyang;14358 rows created.Elapsed: 00:00:00.05SQL> /     28716 rows created.Elapsed: 00:00:00.06SQL> /57432 rows created.Elapsed: 00:00:00.10SQL> /114864 rows created.Elapsed: 00:00:00.18SQL> commit;Commit complete.Elapsed: 00:00:00.01SQL> select count(1) from "000".tcyang;  COUNT(1)----------    229728Elapsed: 00:00:00.01SQL> exp导出数据：[oracle@sgpc146 expdp]$ exp userid=\"/ as sysdba\" file=tcyang.dmp log=tcyang.log tables="000".tcyangExport: Release 11.2.0.3.0 - Production on Fri Sep 16 00:18:58 2011Copyright (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 ProductionWith the Partitioning and Automatic Storage Management optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path ...Current user changed to 000. . exporting table                         TCYANG     229728 rows exportedExport terminated successfully without warnings.[oracle@sgpc146 expdp]$ [oracle@sgpc146 s01]$ more init.dul osd_big_endian_flag=falseosd_dba_file_bits=10 osd_c_struct_alignment=32 osd_file_leader_size=0 osd_word_size = 32 dc_columns=2000000 dc_tables=10000 dc_objects=1000000 dc_users=400 dc_segments=100000 db_block_size=8192 export_mode=falsecompatible=11 BUFFER=10000000 LDR_ENCLOSE_CHAR=| [oracle@sgpc146 s01]$ [oracle@sgpc146 s01]$ ./dul Data UnLoader: 10.2.0.5.8 - Internal Only - on Fri Sep 16 00:20:38 2011with 64-bit io functionsCopyright (c) 1994 2011 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use OnlyDUL: Warning: Recreating file "dul.log"DUL> SCAN DUMP FILE  /orabak/expdp/tcyang.dmp;0: CSET: 852 (ZHS16GBK)3: SEAL EXPORT:V11.02.0020: DBA SYS26: TYPE TABLES8461: CONNECT 0008473: TABLE "TCYANG"8488: CREATE TABLE "TCYANG" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(30))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SGERP5TEST" LOGGING NOCOMPRESS9065: INSERT INTO "TCYANG" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "EDITION_NAME") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15)9368: BIND information for 15 columns col[  1] type 1 max length 30 cset 852 (ZHS16GBK) form 1 col[  2] type 1 max length 128 cset 852 (ZHS16GBK) form 1 col[  3] type 1 max length 30 cset 852 (ZHS16GBK) form 1 col[  4] type 2 max length 22 col[  5] type 2 max length 22 col[  6] type 1 max length 19 cset 852 (ZHS16GBK) form 1 col[  7] type 12 max length 7 col[  8] type 12 max length 7 col[  9] type 1 max length 19 cset 852 (ZHS16GBK) form 1 col[ 10] type 1 max length 7 cset 852 (ZHS16GBK) form 1 col[ 11] type 1 max length 1 cset 852 (ZHS16GBK) form 1 col[ 12] type 1 max length 1 cset 852 (ZHS16GBK) form 1 col[ 13] type 1 max length 1 cset 852 (ZHS16GBK) form 1 col[ 14] type 2 max length 22 col[ 15] type 1 max length 30 cset 852 (ZHS16GBK) form 1Conventional export9474: start of table data24786158: EXIT24786163: EXITDUL> unexp table "000"."TCYANG" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(30)) dump file /orabak/expdp/tcyang.dmp from 0;

DUL: Error: Zero length columnDUL: Error: Zero length columnDUL: Error: Zero length columnDUL: Error: Zero length columnDUL: Error: Zero length columnDUL: Error: Zero length columnDUL: Error: Zero length columnDUL: Error: Zero length column9474: succesful conversion      9474 bytes skipped due to conversion problemsUnloaded 229728 rows, end of table marker at 24785892DUL> exit

[oracle@sgpc146 s01]$ more 000_TCYANG.ctl load datainfile '000_TCYANG.dat'insertinto table "000"."TCYANG"fields terminated by whitespace(  "OWNER"                            CHAR(30) enclosed by X'7C'       ,"OBJECT_NAME"                      CHAR(30) enclosed by X'7C'       ,"SUBOBJECT_NAME"                   CHAR(27) enclosed by X'7C'       ,"OBJECT_ID"                        CHAR(5) enclosed by X'7C'        ,"DATA_OBJECT_ID"                   CHAR(5) enclosed by X'7C'        ,"OBJECT_TYPE"                      CHAR(18) enclosed by X'7C'       ,"CREATED"                          DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C' ,"LAST_DDL_TIME"                    DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C' ,"TIMESTAMP"                        CHAR(19) enclosed by X'7C'       ,"STATUS"                           CHAR(5) enclosed by X'7C'        ,"TEMPORARY"                        CHAR(1) enclosed by X'7C'        ,"GENERATED"                        CHAR(1) enclosed by X'7C'        ,"SECONDARY"                        CHAR(1) enclosed by X'7C'        ,"NAMESPACE"                        CHAR(2) enclosed by X'7C'        ,"EDITION_NAME"                     CHAR(1) enclosed by X'7C'        "UNEXP_STATUS"                     FILLER CHAR(3) enclosed by X'7C'))[oracle@sgpc146 s01]$ tail -1 000_TCYANG.dat |9959| |TB201109_ILS| || |15040| |15040| |TABLE| |15-SEP-2012 AD 00:28:31| |15-SEP-2012 AD 00:39:45| |2012-09-15:00:28:31| |VALID| |N| |N| |N| |1| || |   |[oracle@sgpc146 s01]$

重新导入进行验证：

SQL>truncate table "000".tcyang;

[oracle@sgpc146 s01]$ sqlldr userid=\"/ as sysdba\" control=/s01/000_TCYANG.ctl log=/s01/tcyang.logCommit point reached - logical record count 229222Commit point reached - logical record count 229286Commit point reached - logical record count 229350Commit point reached - logical record count 229414Commit point reached - logical record count 229478Commit point reached - logical record count 229542Commit point reached - logical record count 229606Commit point reached - logical record count 229670Commit point reached - logical record count 229728

[oracle@sgpc146 s01]$ more tcyang.log SQL*Loader: Release 11.2.0.3.0 - Production on Fri Sep 16 00:49:43 2011Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Control File:   /s01/000_TCYANG.ctlData File:      000_TCYANG.dat  Bad File:     /s01/000_TCYANG.bad  Discard File:  none specified  (Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array:     64 rows, maximum of 256000 bytesContinuation:    none specifiedPath used:      ConventionalTable "000"."TCYANG", loaded from every logical record.Insert option in effect for this table: TRUNCATE   Column Name                  Position   Len  Term Encl Datatype------------------------------ ---------- ----- ---- ---- ---------------------"OWNER"                             FIRST    30  WHT   |  CHARACTER            "OBJECT_NAME"                        NEXT    30  WHT   |  CHARACTER            "SUBOBJECT_NAME"                     NEXT    27  WHT   |  CHARACTER            "OBJECT_ID"                          NEXT     5  WHT   |  CHARACTER            "DATA_OBJECT_ID"                     NEXT     5  WHT   |  CHARACTER            "OBJECT_TYPE"                        NEXT    18  WHT   |  CHARACTER            "CREATED"                            NEXT     *  WHT   |  DATE DD-MON-YYYY AD HH24:MI:SS"LAST_DDL_TIME"                      NEXT     *  WHT   |  DATE DD-MON-YYYY AD HH24:MI:SS"TIMESTAMP"                          NEXT    19  WHT   |  CHARACTER            "STATUS"                             NEXT     5  WHT   |  CHARACTER            "TEMPORARY"                          NEXT     1  WHT   |  CHARACTER            "GENERATED"                          NEXT     1  WHT   |  CHARACTER            "SECONDARY"                          NEXT     1  WHT   |  CHARACTER            "NAMESPACE"                          NEXT     2  WHT   |  CHARACTER            "EDITION_NAME"                       NEXT     1  WHT   |  CHARACTER            Table "000"."TCYANG":  229728 Rows successfully loaded.  0 Rows not loaded due to data errors.  0 Rows not loaded because all WHEN clauses were failed.  0 Rows not loaded because all fields were null.Space allocated for bind array:                  44544 bytes(64 rows)Read   buffer bytes: 1048576Total logical records skipped:          0Total logical records read:        229728Total logical records rejected:         0Total logical records discarded:        0Run began on Fri Sep 16 00:49:43 2011Run ended on Fri Sep 16 00:49:49 2011Elapsed time was:     00:00:05.77CPU time was:         00:00:01.61[oracle@sgpc146 s01]$