dul实验(exp)

最新推荐文章于 2019-08-25 03:22:53 发布

rgb_rgb

最新推荐文章于 2019-08-25 03:22:53 发布

阅读量2k

点赞数

分类专栏： oracle 文章标签： character object oracle table date file

本文链接：https://blog.csdn.net/rgb_rgb/article/details/7983483

版权

oracle 专栏收录该内容

90 篇文章 0 订阅

订阅专栏

准备数据：

[oracle@sgpc146 s01]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 16 00:14:38 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> create table "000".tcyang as select * from dba_objects;

Table created.

Elapsed: 00:00:00.18
SQL> insert into "000".tcyang select * from "000".tcyang;

14358 rows created.

Elapsed: 00:00:00.05
SQL> /     

28716 rows created.

Elapsed: 00:00:00.06
SQL> /

57432 rows created.

Elapsed: 00:00:00.10
SQL> /

114864 rows created.

Elapsed: 00:00:00.18
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> select count(1) from "000".tcyang;

  COUNT(1)
----------
    229728

Elapsed: 00:00:00.01
SQL> 

exp导出数据：

[oracle@sgpc146 expdp]$ exp userid=\"/ as sysdba\" file=tcyang.dmp log=tcyang.log tables="000".tcyang

Export: Release 11.2.0.3.0 - Production on Fri Sep 16 00:18:58 2011

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 and Automatic Storage Management options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to 000
. . exporting table                         TCYANG     229728 rows exported
Export terminated successfully without warnings.
[oracle@sgpc146 expdp]$ 


[oracle@sgpc146 s01]$ more init.dul 
osd_big_endian_flag=false
osd_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=false
compatible=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 2011
with 64-bit io functions

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

 Strictly Oracle Internal Use Only


DUL: Warning: Recreating file "dul.log"
DUL> SCAN DUMP FILE  /orabak/expdp/tcyang.dmp;
0: CSET: 852 (ZHS16GBK)
3: SEAL EXPORT:V11.02.00
20: DBA SYS
26: TYPE TABLES
8461: CONNECT 000
8473: 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 NOCOMPRESS
9065: 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 1
Conventional export
9474: start of table data
24786158: EXIT
24786163: EXIT
DUL> 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 column
DUL: Error: Zero length column
DUL: Error: Zero length column
DUL: Error: Zero length column
DUL: Error: Zero length column
DUL: Error: Zero length column
DUL: Error: Zero length column
DUL: Error: Zero length column
9474: succesful conversion      9474 bytes skipped due to conversion problems
Unloaded 229728 rows, end of table marker at 24785892
DUL> exit

[oracle@sgpc146 s01]$ more 000_TCYANG.ctl 
load data
infile '000_TCYANG.dat'
insert
into 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.log
Commit point reached - logical record count 229222
Commit point reached - logical record count 229286
Commit point reached - logical record count 229350
Commit point reached - logical record count 229414
Commit point reached - logical record count 229478
Commit point reached - logical record count 229542
Commit point reached - logical record count 229606
Commit point reached - logical record count 229670
Commit 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 2011

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

Control File:   /s01/000_TCYANG.ctl
Data File:      000_TCYANG.dat
  Bad File:     /s01/000_TCYANG.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table "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: 1048576

Total logical records skipped:          0
Total logical records read:        229728
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Sep 16 00:49:43 2011
Run ended on Fri Sep 16 00:49:49 2011

Elapsed time was:     00:00:05.77
CPU time was:         00:00:01.61
[oracle@sgpc146 s01]$