课程第24次（2018-08-07星期二）

botang

HR:

1. create table t_bank_invoice (
   
2.     account_number  number ,
   
3.      sub_number number ,
   
4.     transac_date   date ,
   
5.       transac_address  varchar2(600),
   
6.     transac_comment    varchar2(600),
   
7.        currency    varchar2(100),
   
8.       remit   varchar2(100),
   
9.      in_amount  number(20,2)  ,
   
10.    out_amount   number(20,2),
    
11.     balance     number(20,2),
    
12.    opposite_number    number  ,
    
13.   opposite_username    varchar2(100),
    
14.     all_comment      varchar2(2000)) ;

复制代码

建行： ccb.dat (324 Bytes, 下载次数: 345)

2018-8-7 20:29 上传

点击文件名下载附件
账　　号：4367421823250099999
开户机构：福建省
币    种： 人民币
发生日期,交易地点,支出,收入,账户余额,对方账号,对方户名,币种,摘要
20100209,008350183980040,150.00,,6756.67,,,人民币,消费
20100210,,,2765.90,9522.57,,,人民币,工资/奖金
20100216,,3.00,,9519.57,,,人民币,自定义
20100217,,4859.10,,4660.47,,,人民币,自定义
图形界面创建 控制文件“模板”：



建行的控制文件：

1. LOAD DATA
   
2. characterset ZHS16GBK
   
3. INFILE 'ccb.dat'
   
4. DISCARDFILE 'ccb.dis'
   
5. append
   
6. INTO table t_bank_invoice
   
7. TRAILING NULLCOLS
   
8. (
   
9.    account_number "4367421823250099999",
   
10.    sub_number "00000",
    
11.    transac_date position(1:8)  DATE(8) "YYYYMMDD" TERMINATED BY ',',  
    
12.    transac_address   position(10)   char  TERMINATED BY ',',              
    
13.    out_amount decimal external  TERMINATED BY ','  nullif out_amount=blanks,
    
14.    in_amount decimal external  TERMINATED BY ',' nullif in_amount=blanks  ,
    
15.    balance decimal external  TERMINATED BY ',' nullif balance=blanks,
    
16.    opposite_number char  TERMINATED BY ',',     
    
17.    opposite_username char  TERMINATED BY ',',                                   
    
18.    currency char  TERMINATED BY ',',
    
19.    transac_comment  char  TERMINATED BY whitespace               
    
20. )

复制代码

[oracle@station90 ~]$ sqlldr  hr/oracle_4U  control=ccb.ctl

1. 
   
2. SQL*Loader: Release 11.2.0.3.0 - Production on Tue Aug 7 20:27:00 2018
   
3. 
   
4. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
5. 
   
6. Control File:   ccb.ctl
   
7. Character Set ZHS16GBK specified for all input.
   
8. 
   
9. Data File:      ccb.dat
   
10.   Bad File:     ccb.bad
    
11.   Discard File: ccb.dis
    
12. (Allow all discards)
    
13. 
    
14. Number to load: ALL
    
15. Number to skip: 0
    
16. Errors allowed: 50
    
17. Bind array:     64 rows, maximum of 256000 bytes
    
18. Continuation:    none specified
    
19. Path used:      Conventional
    
20. 
    
21. Table T_BANK_INVOICE, loaded from every logical record.
    
22. Insert option in effect for this table: APPEND
    
23. TRAILING NULLCOLS option in effect
    
24. 
    
25.    Column Name                  Position   Len  Term Encl Datatype
    
26. ------------------------------ ---------- ----- ---- ---- ---------------------
    
27. ACCOUNT_NUMBER                      FIRST     1           CHARACTER            
    
28.     SQL string for column : "4367421823250099999"
    
29. SUB_NUMBER                           NEXT     1           CHARACTER            
    
30.     SQL string for column : "00000"
    
31. TRANSAC_DATE                          1:8     8   ,       DATE YYYYMMDD        
    
32. TRANSAC_ADDRESS                        10     *   ,       CHARACTER            
    
33. OUT_AMOUNT                           NEXT     *   ,       CHARACTER            
    
34.     NULL if OUT_AMOUNT = BLANKS
    
35. IN_AMOUNT                            NEXT     *   ,       CHARACTER            
    
36.     NULL if IN_AMOUNT = BLANKS
    
37. BALANCE                              NEXT     *   ,       CHARACTER            
    
38.     NULL if BALANCE = BLANKS
    
39. OPPOSITE_NUMBER                      NEXT     *   ,       CHARACTER            
    
40. OPPOSITE_USERNAME                    NEXT     *   ,       CHARACTER            
    
41. CURRENCY                             NEXT     *   ,       CHARACTER            
    
42. TRANSAC_COMMENT                      NEXT     *  WHT      CHARACTER            
    
43. 
    
44. Record 1: Rejected - Error on table T_BANK_INVOICE, column ACCOUNT_NUMBER.
    
45. Multibyte character error.
    
46. Record 2: Rejected - Error on table T_BANK_INVOICE, column ACCOUNT_NUMBER.
    
47. Multibyte character error.
    
48. Record 3: Rejected - Error on table T_BANK_INVOICE, column ACCOUNT_NUMBER.
    
49. Multibyte character error.
    
50. Record 4: Rejected - Error on table T_BANK_INVOICE, column ACCOUNT_NUMBER.
    
51. Multibyte character error.
    
52. Record 9: Discarded - all columns null.
    
53. 
    
54. Table T_BANK_INVOICE:
    
55.   4 Rows successfully loaded.
    
56.   4 Rows not loaded due to data errors.
    
57.   0 Rows not loaded because all WHEN clauses were failed.
    
58.   1 Row not loaded because all fields were null.
    
59. 
    
60. 
    
61. Space allocated for bind array:                 133248 bytes(64 rows)
    
62. Read   buffer bytes: 1048576
    
63. 
    
64. Total logical records skipped:          0
    
65. Total logical records read:             9
    
66. Total logical records rejected:         4
    
67. Total logical records discarded:        1
    
68. 
    
69. Run began on Tue Aug 07 20:27:00 2018
    
70. Run ended on Tue Aug 07 20:27:00 2018
    
71. 
    
72. Elapsed time was:     00:00:00.28
    
73. CPU time was:         00:00:00.01

复制代码

工行： icbc.dat (1.08 KB, 下载次数: 326)

2018-8-7 20:30 上传

点击文件名下载附件
明细查询文件下载

    卡号: 1402029101000999999    卡别名:

    子账户序号:     子账户类别:     子账户别名:

  ^交易日期       ^交易场所                 ^业务摘要  ^币种      ^钞/汇     ^收入金额    ^支出金额    ^余额
    ^2010-05-10     ^                         ^工资      ^人民币    ^钞        ^564.94      ^            ^6,748.42            
    ^2010-05-14     ^                         ^工资      ^人民币    ^钞        ^5,989.54    ^            ^12,737.96           
    ^2010-05-26     ^金山支行营业部           ^支取      ^人民币    ^钞        ^            ^12,700.00   ^37.96               
    ^2010-06-12     ^                         ^工资      ^人民币    ^钞        ^5,811.04    ^            ^5,849.00            
    ^2010-06-21     ^                         ^息        ^人民币    ^钞        ^5.03        ^            ^5,854.03            
    ^2010-07-01     ^金山支行营业部           ^支取      ^人民币    ^钞        ^            ^5,854.00    ^0.03               
   

   #人民币合计                                                                 ^163,952.85   ^170,134.00

1. LOAD DATA
   
2. characterset ZHS16GBK
   
3. INFILE 'icbc.dat'
   
4. DISCARDFILE 'icbc.dis'
   
5. append
   
6. INTO table t_bank_invoice
   
7. when (5) ='^'
   
8. FIELDS TERMINATED BY '^'
   
9. TRAILING NULLCOLS
   
10. (
    
11.    account_number "1402029101000999999",
    
12.    sub_number "00000",
    
13.    transac_date position(6:15) DATE(10) "YYYY-MM-DD" TERMINATED BY WHITESPACE,
    
14.    transac_address TERMINATED BY WHITESPACE
    
15.     "substr(:transac_address,2)",
    
16.    transac_comment TERMINATED BY WHITESPACE
    
17.     "substr(:transac_comment,2)",
    
18.    currency TERMINATED BY WHITESPACE
    
19.     "substr(:currency,2)",
    
20.    remit TERMINATED BY WHITESPACE
    
21.     "substr(:remit,2)",
    
22.    in_amount TERMINATED BY WHITESPACE
    
23.     "TO_NUMBER(substr(:in_amount,2),'999,999,999,999.00')",
    
24.    out_amount TERMINATED BY WHITESPACE
    
25.     "TO_NUMBER(substr(:out_amount,2),'999,999,999,999.00')",
    
26.    balance TERMINATED BY WHITESPACE
    
27.     "TO_NUMBER(substr(:balance,2),'999,999,999,999.00')",
    
28.    all_comment TERMINATED BY WHITESPACE
    
29.     "substr(:all_comment,2)"
    
30. )
    

复制代码

[oracle@station90 ~]$ sqlldr  hr/oracle_4U  control=icbc.ctl  direct=true

1. 
   
2. SQL*Loader: Release 11.2.0.3.0 - Production on Tue Aug 7 20:39:58 2018
   
3. 
   
4. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
5. 
   
6. Control File:   icbc.ctl
   
7. Character Set ZHS16GBK specified for all input.
   
8. 
   
9. Data File:      icbc.dat
   
10.   Bad File:     icbc.bad
    
11.   Discard File: icbc.dis
    
12. (Allow all discards)
    
13. 
    
14. Number to load: ALL
    
15. Number to skip: 0
    
16. Errors allowed: 50
    
17. Continuation:    none specified
    
18. Path used:      Direct
    
19. 
    
20. Table T_BANK_INVOICE, loaded when 5:5 = 0X5e(character '^')
    
21. Insert option in effect for this table: APPEND
    
22. TRAILING NULLCOLS option in effect
    
23. 
    
24.    Column Name                  Position   Len  Term Encl Datatype
    
25. ------------------------------ ---------- ----- ---- ---- ---------------------
    
26. ACCOUNT_NUMBER                      FIRST     *   ^       CHARACTER            
    
27.     SQL string for column : "1402029101000999999"
    
28. SUB_NUMBER                           NEXT     *   ^       CHARACTER            
    
29.     SQL string for column : "00000"
    
30. TRANSAC_DATE                         6:15    10  WHT      DATE YYYY-MM-DD      
    
31. TRANSAC_ADDRESS                      NEXT     *  WHT      CHARACTER            
    
32.     SQL string for column : "substr(:transac_address,2)"
    
33. TRANSAC_COMMENT                      NEXT     *  WHT      CHARACTER            
    
34.     SQL string for column : "substr(:transac_comment,2)"
    
35. CURRENCY                             NEXT     *  WHT      CHARACTER            
    
36.     SQL string for column : "substr(:currency,2)"
    
37. REMIT                                NEXT     *  WHT      CHARACTER            
    
38.     SQL string for column : "substr(:remit,2)"
    
39. IN_AMOUNT                            NEXT     *  WHT      CHARACTER            
    
40.     SQL string for column : "TO_NUMBER(substr(:in_amount,2),'999,999,999,999.00')"
    
41. OUT_AMOUNT                           NEXT     *  WHT      CHARACTER            
    
42.     SQL string for column : "TO_NUMBER(substr(:out_amount,2),'999,999,999,999.00')"
    
43. BALANCE                              NEXT     *  WHT      CHARACTER            
    
44.     SQL string for column : "TO_NUMBER(substr(:balance,2),'999,999,999,999.00')"
    
45. ALL_COMMENT                          NEXT     *  WHT      CHARACTER            
    
46.     SQL string for column : "substr(:all_comment,2)"
    
47. 
    
48. Record 1: Discarded - failed all WHEN clauses.
    
49. Record 2: Discarded - failed all WHEN clauses.
    
50. Record 3: Discarded - failed all WHEN clauses.
    
51. Record 4: Discarded - failed all WHEN clauses.
    
52. Record 5: Discarded - failed all WHEN clauses.
    
53. Record 6: Discarded - failed all WHEN clauses.
    
54. Record 7: Discarded - failed all WHEN clauses.
    
55. Record 14: Discarded - failed all WHEN clauses.
    
56. Record 15: Discarded - failed all WHEN clauses.
    
57. Record 16: Discarded - failed all WHEN clauses.
    
58. 
    
59. Table T_BANK_INVOICE:
    
60.   6 Rows successfully loaded.
    
61.   0 Rows not loaded due to data errors.
    
62.   10 Rows not loaded because all WHEN clauses were failed.
    
63.   0 Rows not loaded because all fields were null.
    
64. 
    
65.   Date cache:
    
66.    Max Size:      1000
    
67.    Entries :         6
    
68.    Hits    :         0
    
69.    Misses  :         0
    
70. 
    
71. Bind array size not used in direct path.
    
72. Column array  rows :    5000
    
73. Stream buffer bytes:  256000
    
74. Read   buffer bytes: 1048576
    
75. 
    
76. Total logical records skipped:          0
    
77. Total logical records read:            16
    
78. Total logical records rejected:         0
    
79. Total logical records discarded:       10
    
80. Total stream buffers loaded by SQL*Loader main thread:        1
    
81. Total stream buffers loaded by SQL*Loader load thread:        0
    
82. 
    
83. Run began on Tue Aug 07 20:39:58 2018
    
84. Run ended on Tue Aug 07 20:39:58 2018
    
85. 
    
86. Elapsed time was:     00:00:00.12
    
87. CPU time was:         00:00:00.01

复制代码

-----LOADER外部表：
[oracle@station90 ~]$ sqlldr  hr/oracle_4U  control=ccb.ctl  external_table=GENERATE_ONLY log=ccb.sql

1. CREATE TABLE "T_BANK_INVOICe2"
   
2. (
   
3.   "ACCOUNT_NUMBER" VARCHAR(4000),
   
4.   "SUB_NUMBER" VARCHAR(4000),
   
5.   "TRANSAC_DATE" DATE,
   
6.   "TRANSAC_ADDRESS" VARCHAR2(600),
   
7.   "OUT_AMOUNT" NUMBER(20,2),
   
8.   "IN_AMOUNT" NUMBER(20,2),
   
9.   "BALANCE" NUMBER(20,2),
   
10.   "OPPOSITE_NUMBER" NUMBER,
    
11.   "OPPOSITE_USERNAME" VARCHAR2(100),
    
12.   "CURRENCY" VARCHAR2(100),
    
13.   "TRANSAC_COMMENT" VARCHAR2(600)
    
14. )
    
15. ORGANIZATION external
    
16. (
    
17.   TYPE oracle_loader
    
18.   DEFAULT DIRECTORY DIR1
    
19.   ACCESS PARAMETERS
    
20.   (
    
21.     RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    
22.     BADFILE DIR1:'ccb.bad'
    
23.     DISCARDFILE DIR1:'ccb.dis'
    
24.     LOGFILE 'ccb.log'
    
25.     READSIZE 1048576
    
26.     FIELDS LDRTRIM
    
27.     MISSING FIELD VALUES ARE NULL
    
28.     REJECT ROWS WITH ALL NULL FIELDS
    
29.     (
    
30.       "ACCOUNT_NUMBER" CHAR(1),
    
31.       "SUB_NUMBER" CHAR(1),
    
32.       "TRANSAC_DATE" (1:8) CHAR(8)
    
33.         DATE_FORMAT DATE MASK "YYYYMMDD",
    
34.       "TRANSAC_ADDRESS" (10) CHAR(255)
    
35.         TERMINATED BY ",",
    
36.       "OUT_AMOUNT" CHAR(255)
    
37.         TERMINATED BY ","
    
38.         NULLIF ("OUT_AMOUNT" = BLANKS),
    
39.       "IN_AMOUNT" CHAR(255)
    
40.         TERMINATED BY ","
    
41.         NULLIF ("IN_AMOUNT" = BLANKS),
    
42.       "BALANCE" CHAR(255)
    
43.         TERMINATED BY ","
    
44.         NULLIF ("BALANCE" = BLANKS),
    
45.       "OPPOSITE_NUMBER" CHAR(255)
    
46.         TERMINATED BY ",",
    
47.       "OPPOSITE_USERNAME" CHAR(255)
    
48.         TERMINATED BY ",",
    
49.       "CURRENCY" CHAR(255)
    
50.         TERMINATED BY ",",
    
51.       "TRANSAC_COMMENT" CHAR(255)
    
52.         TERMINATED BY WHITESPACE
    
53.     )
    
54.   )
    
55.   location
    
56.   (
    
57.     'ccb.dat'
    
58.   )
    
59. )parallel REJECT LIMIT UNLIMITED;
    

复制代码

datapump最大的用户是system：
[oracle@station90 ~]$ expdp system/oracle_4U  directory=dir1  dumpfile=full.dmp  full=y  job_name=fulljob  

Export: Release 11.2.0.3.0 - Production on Tue Aug 7 21:21:43 2018

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

UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."FULLJOB":  system/******** directory=dir1 dumpfile=full.dmp full=y job_name=fulljob
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 145.4 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE

Export> help
------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

ADD_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.


Export> STOP_JOB
Are you sure you wish to stop this job ([yes]/no): yes

[oracle@station90 ~]$


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

1. select * from dba_datapump_jobs;

复制代码

	OWNER_NAME	JOB_NAME	OPERATION	JOB_MODE	STATE	DEGREE	ATTACHED_SESSIONS	DATAPUMP_SESSIONS
1	SYSTEM	FULLJOB	EXPORT	FULL	NOT RUNNING	0	0	0

附主表：

1. select  * from system.fulljob;

复制代码

------------------------------
[oracle@station90 ~]$ expdp system/oracle_4U attach=FULLJOB

Export: Release 11.2.0.3.0 - Production on Tue Aug 7 21:28:43 2018

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

UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Job: FULLJOB
  Owner: SYSTEM                        
  Operation: EXPORT                        
  Creator Privs: TRUE                           
  GUID: 72D9466B781F3BB5E0535A00A8C0BDDF
  Start Time: Tuesday, 07 August, 2018 21:28:44
  Mode: FULL                           
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=dir1 dumpfile=full.dmp full=y job_name=fulljob
  State: IDLING                        
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /home/oracle/full.dmp
    bytes written: 4,096
  
Worker 1 Status:
  Process Name: DW00
  State: UNDEFINED                     

Export> start_job

Export>

1. select * from dba_datapump_jobs;

复制代码

	OWNER_NAME	JOB_NAME	OPERATION	JOB_MODE	STATE	DEGREE	ATTACHED_SESSIONS	DATAPUMP_SESSIONS
1	SYSTEM	FULLJOB	EXPORT	FULL	EXECUTING	1	1	3

expdp hr/oracle_4U directory=dir1 dumpfile=my.dmp  schemas=hr job_name=my_job  exclude=table:\"in \(\'T_BANK_INVOICE\'\)\", procedure:\" in \(\'SECURE_DML\'\)\"  "query='employees:where department_id=90'"

expdp  hr/oracle_4U parfile=myfile.txt

myfile.txt:

1. directory=dir1
   
2. dumpfile=my2.dmp  
   
3. schemas=hr
   
4. job_name=my2_job  
   
5. exclude=table:"in ('T_BANK_INVOICE')", procedure:"in ('SECURE_DML')"  
   
6. query='employees:where department_id=90'
   
7. flashback_time='2018-08-07:21:41:17'

复制代码

[oracle@station90 ~]$ expdp hr/oracle_4U parfile=myfile.txt

Export: Release 11.2.0.3.0 - Production on Tue Aug 7 21:45:23 2018

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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "HR"."MY2_JOB":  hr/******** parfile=myfile.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . exported "HR"."EMPLOYEES"                            9.445 KB       3 rows
. . exported "HR"."JOBS"                                 6.992 KB      19 rows
. . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . exported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . exported "HR"."REGIONS"                              5.476 KB       4 rows
Master table "HR"."MY2_JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.MY2_JOB is:
  /home/oracle/my2.dmp
Job "HR"."MY2_JOB" successfully completed at 21:45:54


----建个用户导入

1. create user hr2 identified by  oracle_4U;
   
2. 
   
3. grant connect, resource to hr2;
   
4. 
   
5. grant read,write on directory dir1 to hr2;
   

复制代码

[oracle@station90 ~]$ impdp  system/oracle_4U directory=dir1  dumpfile=my2.dmp  remap_schema=hr:hr2

Import: Release 11.2.0.3.0 - Production on Tue Aug 7 21:49:29 2018

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

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dir1 dumpfile=my2.dmp remap_schema=hr:hr2
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR2"."COUNTRIES"                           6.367 KB      25 rows
. . imported "HR2"."DEPARTMENTS"                         7.007 KB      27 rows
. . imported "HR2"."EMPLOYEES"                           9.445 KB       3 rows
. . imported "HR2"."JOBS"                                6.992 KB      19 rows
. . imported "HR2"."JOB_HISTORY"                         7.054 KB      10 rows
. . imported "HR2"."LOCATIONS"                           8.273 KB      23 rows
. . imported "HR2"."REGIONS"                             5.476 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (HR2.DEPT_MGR_FK) - parent keys not found
Failing sql is:
ALTER TABLE "HR2"."DEPARTMENTS" ADD CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR2"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (HR2.JHIST_EMP_FK) - parent keys not found
Failing sql is:
ALTER TABLE "HR2"."JOB_HISTORY" ADD CONSTRAINT "JHIST_EMP_FK" FOREIGN KEY ("EMPLOYEE_ID") REFERENCES "HR2"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"HR2"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR2"."SECURE_EMPLOYEES" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 4 error(s) at 21:49:35

[oracle@station90 ~]$