活动第43/44次（2018-07-28星期六下午）

botang

1. select  * from dba_directories;
   
2. 
   
3. create directory dir1 as '/home/oracle/dir1';
   
4. 
   
5. create directory dir2 as '/home/oracle/dir2';
   
6. 
   
7. grant read,write on directory dir1 to hr;
   
8. 
   
9. grant read,write on directory dir2 to hr;
   
10. 
    
11. select  * from dba_tab_privs tp where tp.grantee='HR';

复制代码

	GRANTEE	OWNER	TABLE_NAME	GRANTOR	PRIVILEGE	GRANTABLE	HIERARCHY	COMMON	TYPE
1	HR	SYS	DBMS_STATS	SYS	EXECUTE	NO	NO	NO	PACKAGE
2	HR	SYS	DIR1	SYS	READ	NO	NO	NO	DIRECTORY
3	HR	SYS	DIR1	SYS	WRITE	NO	NO	NO	DIRECTORY
4	HR	SYS	DIR2	SYS	READ	NO	NO	NO	DIRECTORY
5	HR	SYS	DIR2	SYS	WRITE	NO	NO	NO	DIRECTORY

1. CREATE TABLE hr.extab_employees
   
2.                   (employee_id       NUMBER(4),
   
3.                    first_name        VARCHAR2(20),
   
4.                     last_name         VARCHAR2(25),
   
5.                      hire_date         DATE)
   
6. ORGANIZATION EXTERNAL
   
7.      ( TYPE ORACLE_LOADER DEFAULT DIRECTORY dir1
   
8.        ACCESS PARAMETERS
   
9.        ( records delimited by newline
   
10.          badfile dir1:'empxt%a_%p.bad'
    
11.          logfile dir1:'empxt%a_%p.log'
    
12.          fields terminated by ','
    
13.          missing field values are null
    
14.      ( employee_id, first_name, last_name,
    
15.       hire_date char date_format date mask "dd-mon-yyyy"))
    
16.      LOCATION (dir1:'empxt1.dat', dir2:'empxt2.dat') )
    
17.      PARALLEL  REJECT LIMIT UNLIMITED;
    
18.      
    
19. select * from dba_tables t where t.owner='HR';   
    
20.      

复制代码

OWNER

TABLE_NAME

TABLESPACE_NAME

CLUSTER_NAME

IOT_NAME

STATUS

PCT_FREE

PCT_USED

INI_TRANS

MAX_TRANS

INITIAL_EXTENT

NEXT_EXTENT

MIN_EXTENTS

MAX_EXTENTS

PCT_INCREASE

FREELISTS

FREELIST_GROUPS

LOGGING

BACKED_UP

NUM_ROWS

BLOCKS

EMPTY_BLOCKS

AVG_SPACE

CHAIN_CNT

AVG_ROW_LEN

AVG_SPACE_FREELIST_BLOCKS

NUM_FREELIST_BLOCKS

DEGREE

INSTANCES

CACHE

TABLE_LOCK

SAMPLE_SIZE

LAST_ANALYZED

PARTITIONED

IOT_TYPE

TEMPORARY

SECONDARY

NESTED

BUFFER_POOL

FLASH_CACHE

CELL_FLASH_CACHE

ROW_MOVEMENT

GLOBAL_STATS

USER_STATS

DURATION

SKIP_CORRUPT

MONITORING

CLUSTER_OWNER

DEPENDENCIES

COMPRESSION

COMPRESS_FOR

DROPPED

READ_ONLY

SEGMENT_CREATED

RESULT_CACHE

CLUSTERING

ACTIVITY_TRACKING

DML_TIMESTAMP

HAS_IDENTITY

CONTAINER_DATA

INMEMORY

INMEMORY_PRIORITY

INMEMORY_DISTRIBUTE

INMEMORY_COMPRESSION

INMEMORY_DUPLICATE

1

HR

SSMM

SSMM

VALID

10

40

1

255

YES

N

0

0

0

0

0

0

0

0

1

1

N

ENABLED

0

7/15/2018 9:10:56 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

NO

DEFAULT

NO

NO

NO

DISABLED

2

HR

SSMA

USERS

VALID

10

1

255

YES

N

0

0

0

0

0

0

0

0

1

1

N

ENABLED

0

7/15/2018 9:10:57 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

NO

DEFAULT

NO

NO

NO

DISABLED

3

HR

T05207_A

SSMM

VALID

80

20

5

255

YES

N

0

0

0

0

0

0

0

0

1

1

N

ENABLED

0

7/15/2018 9:10:57 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

NO

DEFAULT

NO

NO

NO

DISABLED

4

HR

EMPLOYEES

EXAMPLE

VALID

10

1

255

65536

1048576

1

2147483645

NO

N

107

5

0

0

0

69

0

0

1

1

N

ENABLED

107

7/7/2014 6:56:25 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

5

HR

EXTAB_EMPLOYEES

没有表空间

VALID

0

0

0

0

YES

N

DEFAULT

DEFAULT

N

ENABLED

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

NO

NO

DISABLED

NO

DISABLED

DISABLED

BASIC

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

6

HR

JOBS

EXAMPLE

VALID

10

1

255

65536

1048576

1

2147483645

NO

N

19

5

0

0

0

33

0

0

1

1

N

ENABLED

19

7/7/2014 6:56:25 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

7

HR

T04209_UNAME

TBSUNIFORM

VALID

10

1

255

524288

524288

1

2147483645

0

YES

N

100000

247

0

0

0

12

0

0

1

1

N

ENABLED

100000

7/15/2018 9:10:58 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

8

HR

REGIONS

EXAMPLE

VALID

10

1

255

65536

1048576

1

2147483645

NO

N

4

5

0

0

0

14

0

0

1

1

N

ENABLED

4

7/7/2014 6:56:26 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

9

HR

T_BANK_INVOICE

USERS

VALID

10

1

255

65536

1048576

1

2147483645

YES

N

1

1

N

ENABLED

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

NO

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

10

HR

JOB_HISTORY

EXAMPLE

VALID

10

1

255

65536

1048576

1

2147483645

NO

N

10

5

0

0

0

31

0

0

1

1

N

ENABLED

10

7/7/2014 6:56:25 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

11

HR

T05207_B

USERS

VALID

10

1

255

10485760

1048576

1

2147483645

YES

N

0

1006

0

0

0

0

0

0

1

1

N

ENABLED

0

7/15/2018 9:10:58 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

12

HR

COUNTRIES

VALID

0

0

0

0

N

25

0

0

15

0

1

1

N

ENABLED

25

7/7/2014 6:56:24 AM

NO

IOT

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

BASIC

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

13

HR

LOCATIONS

EXAMPLE

VALID

10

1

255

65536

1048576

1

2147483645

NO

N

23

5

0

0

0

49

0

0

1

1

N

ENABLED

23

7/7/2014 6:56:26 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

14

HR

DEPARTMENTS

EXAMPLE

VALID

10

1

255

65536

1048576

1

2147483645

NO

N

27

5

0

0

0

21

0

0

1

1

N

ENABLED

27

7/7/2014 6:56:25 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

1. select  * from dba_external_tables et
   
2. where et.owner='HR' ;

复制代码

由于控制文件和外部表都是识别行和列，所以应该可以从控制文件推出外部表：

1. sqlldr  hr/oracle_4U  control=ccb.ctl external_table=GENERATE_ONLY log=ccb.sql
   
2. 
   
3. SQL*Loader: Release 12.1.0.2.0 - Production on Sat Jul 28 10:01:43 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Path used:      External Table
   

复制代码

看看ccb.sql:

1. drop table hr.T_BANK_INVOICE;
   
2. 
   
3. CREATE TABLE hr.T_BANK_INVOICE
   
4. (
   
5.   "ACCOUNT_NUMBER" VARCHAR2(255),
   
6.   "SUB_NUMBER" VARCHAR2(255),
   
7.   "TRANSAC_DATE" DATE,
   
8.   "TRANSAC_ADDRESS" VARCHAR2(600),
   
9.   "OUT_AMOUNT" NUMBER(20,2),
   
10.   "IN_AMOUNT" NUMBER(20,2),
    
11.   "BALANCE" NUMBER(20,2),
    
12.   "OPPOSITE_NUMBER" NUMBER,
    
13.   "OPPOSITE_USERNAME" VARCHAR2(100),
    
14.   "CURRENCY" VARCHAR2(100),
    
15.   "TRANSAC_COMMENT" VARCHAR2(600)
    
16. )
    
17. ORGANIZATION external
    
18. (
    
19.   TYPE oracle_loader
    
20.   DEFAULT DIRECTORY dir1
    
21.   ACCESS PARAMETERS
    
22.   (
    
23.     RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    
24.     BADFILE dir1:'ccb.bad'
    
25.     LOGFILE dir1:'ccb%a_%p.log'
    
26.     READSIZE 1048576
    
27.     FIELDS TERMINATED BY "," LDRTRIM
    
28.     MISSING FIELD VALUES ARE NULL
    
29.     REJECT ROWS WITH ALL NULL FIELDS
    
30.     (
    
31.       "ACCOUNT_NUMBER" CHAR(255)
    
32.         TERMINATED BY ",",
    
33.       "SUB_NUMBER" CHAR(255)
    
34.         TERMINATED BY ",",
    
35.       "TRANSAC_DATE" (1:8) CHAR(8)
    
36.         DATE_FORMAT DATE MASK "YYYYMMDD",
    
37.       "TRANSAC_ADDRESS" (10) CHAR(255)
    
38.         TERMINATED BY ",",
    
39.       "OUT_AMOUNT" CHAR(255)
    
40.         TERMINATED BY ",",
    
41.       "IN_AMOUNT" CHAR(255)
    
42.         TERMINATED BY ",",
    
43.       "BALANCE" CHAR(255)
    
44.         TERMINATED BY ",",
    
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 ","
    
53.     )
    
54.   )
    
55.   location
    
56.   (
    
57.     'ccb.dat'
    
58.   )
    
59. )REJECT LIMIT UNLIMITED;

复制代码

外部表都是只读的：


工行网银的控制文件：

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. )
    

复制代码

1. [oracle@station90 ~]$ sqlldr  hr/oracle_4U  control=icbc.ctl external_table=GENERATE_ONLY log=icbc.sql
   
2. 
   
3. SQL*Loader: Release 12.1.0.2.0 - Production on Sat Jul 28 10:29:02 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Path used:      External Table
   

复制代码

icbc.sql的内容：

1. drop table hr.T_BANK_INVOICE;
   
2. 
   
3. CREATE TABLE  hr.T_BANK_INVOICE
   
4. (
   
5.   "TRANSAC_DATE" DATE,
   
6.   "TRANSAC_ADDRESS" VARCHAR2(600),
   
7.   "TRANSAC_COMMENT" VARCHAR2(600),
   
8.   "CURRENCY" VARCHAR2(255),
   
9.   "REMIT" VARCHAR2(255),
   
10.   "IN_AMOUNT" VARCHAR2(255),
    
11.   "OUT_AMOUNT" VARCHAR2(255),
    
12.   "BALANCE" VARCHAR2(255),
    
13.   "ALL_COMMENT" VARCHAR2(2000)
    
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.     LOAD WHEN ((5: 5) = "^")
    
23.     BADFILE dir1:'icbc.bad'
    
24.     DISCARDFILE dir1:'icbc.dis'
    
25.     LOGFILE dir1:'icbc%a_%p.log'
    
26.     READSIZE 1048576
    
27.     FIELDS TERMINATED BY "^" LDRTRIM
    
28.     MISSING FIELD VALUES ARE NULL
    
29.     REJECT ROWS WITH ALL NULL FIELDS
    
30.     (           
    
31.       "TRANSAC_DATE" (6:15) CHAR(10)
    
32.         DATE_FORMAT DATE MASK "YYYY-MM-DD" ,
    
33.       "TRANSAC_ADDRESS"   char(255)
    
34.          TERMINATED BY     "^",
    
35.       "TRANSAC_COMMENT" (48) CHAR(255)
    
36.          TERMINATED BY "^",
    
37.       "CURRENCY" CHAR(255)
    
38.          TERMINATED BY "^",
    
39.       "REMIT" CHAR(255)
    
40.          TERMINATED BY "^",
    
41.       "IN_AMOUNT" CHAR(255)
    
42.          TERMINATED BY "^",
    
43.       "OUT_AMOUNT" CHAR(255)
    
44.          TERMINATED BY "^",
    
45.       "BALANCE" CHAR(255)
    
46.          TERMINATED BY "^",
    
47.       "ALL_COMMENT" CHAR(255)
    
48.          TERMINATED BY "^"
    
49.     )
    
50.   )
    
51.   location
    
52.   (
    
53.     'icbc.dat'
    
54.   )
    
55. )REJECT LIMIT UNLIMITED;
    
56. 
    

复制代码

--------------------------------------二进制进出

1. [oracle@station90 dir1]$ expdp system/oracle_4U  directory=dir1 dumpfile=full.dmp  full=y job_name=fulljob
   
2. 
   
3. Export: Release 12.1.0.2.0 - Production on Sat Jul 28 11:29:07 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   
8. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
   
9. Starting "SYSTEM"."FULLJOB":  system/******** directory=dir1 dumpfile=full.dmp full=y job_name=fulljob
   
10. Estimate in progress using BLOCKS method...
    
11. Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
    
12. Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
    
13. Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
    
14. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
    
15. Total estimation using BLOCKS method: 318.7 MB
    
16. Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
    
17. Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
    
18. Processing object type DATABASE_EXPORT/TABLESPACE
    
19. Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
    
20. Processing object type DATABASE_EXPORT/PROFILE
    
21. Processing object type DATABASE_EXPORT/SYS_USER/USER
    
22. Processing object type DATABASE_EXPORT/SCHEMA/USER
    
23. Processing object type DATABASE_EXPORT/ROLE
    
24. Processing object type DATABASE_EXPORT/RADM_FPTM
    
25. Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
    
26. Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
    
27. Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
    
28. Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
    
29. Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
    
30. Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
    
31. Processing object type DATABASE_EXPORT/RESOURCE_COST
    
32. Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
    
33. Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
    
34. Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
    
35. Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
    
36. Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
    
37. Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
    
38. Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
    
39. Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
    
40. Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
    
41. Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
    
42. Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
    
43. Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
    
44. Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
    
45. Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
    
46. Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
    
47. Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
    
48. 
    
49. 这时，按了ctl+c
    
50. 
    
51. Export> help
    
52. ------------------------------------------------------------------------------
    
53. 
    
54. The following commands are valid while in interactive mode.
    
55. Note: abbreviations are allowed.
    
56. 
    
57. ADD_FILE
    
58. Add dumpfile to dumpfile set.
    
59. 
    
60. CONTINUE_CLIENT
    
61. Return to logging mode. Job will be restarted if idle.
    
62. 
    
63. EXIT_CLIENT
    
64. Quit client session and leave job running.
    
65. 
    
66. FILESIZE
    
67. Default filesize (bytes) for subsequent ADD_FILE commands.
    
68. 
    
69. HELP
    
70. Summarize interactive commands.
    
71. 
    
72. KILL_JOB
    
73. Detach and delete job.
    
74. 
    
75. PARALLEL
    
76. Change the number of active workers for current job.
    
77. 
    
78. REUSE_DUMPFILES
    
79. Overwrite destination dump file if it exists [NO].
    
80. 
    
81. START_JOB
    
82. Start or resume current job.
    
83. Valid keyword values are: SKIP_CURRENT.
    
84. 
    
85. STATUS
    
86. Frequency (secs) job status is to be monitored where
    
87. the default [0] will show new status when available.
    
88. 
    
89. STOP_JOB
    
90. Orderly shutdown of job execution and exits the client.
    
91. Valid keyword values are: IMMEDIATE.
    
92. 
    
93. 
    
94. Export> STOP_JOB
    
95. Are you sure you wish to stop this job ([yes]/no): yes
    
96. 
    
97. [oracle@station90 dir1]$ expdp system/oracle_4U  attach=fulljob
    
98. 
    
99. Export: Release 12.1.0.2.0 - Production on Sat Jul 28 11:30:38 2018
    
100. 
     
101. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
     
102. 
     
103. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
     
104. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
     
105. 
     
106. Job: FULLJOB
     
107.   Owner: SYSTEM                        
     
108.   Operation: EXPORT                        
     
109.   Creator Privs: TRUE                           
     
110.   GUID: 7207D4B3F9CF3D4AE0535A00A8C00270
     
111.   Start Time: Saturday, 28 July, 2018 11:30:40
     
112.   Mode: FULL                           
     
113.   Instance: orcl
     
114.   Max Parallelism: 1
     
115.   Timezone: +00:00
     
116.   Timezone version: 18
     
117.   Endianness: LITTLE
     
118.   NLS character set: AL32UTF8
     
119.   NLS NCHAR character set: AL16UTF16
     
120.   EXPORT Job Parameters:
     
121.   Parameter Name      Parameter Value:
     
122.      CLIENT_COMMAND        system/******** directory=dir1 dumpfile=full.dmp full=y job_name=fulljob
     
123.   State: IDLING                        
     
124.   Bytes Processed: 0
     
125.   Current Parallelism: 1
     
126.   Job Error Count: 0
     
127.   Dump File: /home/oracle/dir1/full.dmp
     
128.     bytes written: 4,096
     
129.   
     
130. Worker 1 Status:
     
131.   Instance ID: 1
     
132.   Instance name: orcl
     
133.   Host name: station90.example.com
     
134.   Process Name: DW00
     
135.   State: UNDEFINED                     
     
136. 
     
137. Export> help
     
138. ------------------------------------------------------------------------------
     
139. 
     
140. The following commands are valid while in interactive mode.
     
141. Note: abbreviations are allowed.
     
142. 
     
143. ADD_FILE
     
144. Add dumpfile to dumpfile set.
     
145. 
     
146. CONTINUE_CLIENT
     
147. Return to logging mode. Job will be restarted if idle.
     
148. 
     
149. EXIT_CLIENT
     
150. Quit client session and leave job running.
     
151. 
     
152. FILESIZE
     
153. Default filesize (bytes) for subsequent ADD_FILE commands.
     
154. 
     
155. HELP
     
156. Summarize interactive commands.
     
157. 
     
158. KILL_JOB
     
159. Detach and delete job.
     
160. 
     
161. PARALLEL
     
162. Change the number of active workers for current job.
     
163. 
     
164. REUSE_DUMPFILES
     
165. Overwrite destination dump file if it exists [NO].
     
166. 
     
167. START_JOB
     
168. Start or resume current job.
     
169. Valid keyword values are: SKIP_CURRENT.
     
170. 
     
171. STATUS
     
172. Frequency (secs) job status is to be monitored where
     
173. the default [0] will show new status when available.
     
174. 
     
175. STOP_JOB
     
176. Orderly shutdown of job execution and exits the client.
     
177. Valid keyword values are: IMMEDIATE.
     
178. 
     
179. 
     
180. Export> status
     
181. 
     
182. Job: FULLJOB
     
183.   Operation: EXPORT                        
     
184.   Mode: FULL                           
     
185.   State: IDLING                        
     
186.   Bytes Processed: 0
     
187.   Current Parallelism: 1
     
188.   Job Error Count: 0
     
189.   Dump File: /home/oracle/dir1/full.dmp
     
190.     bytes written: 4,096
     
191.   
     
192. Worker 1 Status:
     
193.   Instance ID: 1
     
194.   Instance name: orcl
     
195.   Host name: station90.example.com
     
196.   Process Name: DW00
     
197.   State: UNDEFINED                     
     
198. 
     
199. Export> start_job
     
200. 
     
201. Export>
     

复制代码

1.    
   
2. select  * from dba_datapump_jobs  dj
   
3. where dj.job_name='FULLJOB';
   
4.   
   
5. 
   
6. select  * from system.fulljob;

复制代码

作业完成主表进了dumpfile，找不到了，这是正常的：




导入的时候，主表先进来：

1. [oracle@station90 ~]$ impdp user1/oracle_4U directory=dir1 dumpfile=full.dmp  tables=hr.employees remap_schema=hr:user1
   
2. 
   
3. Import: Release 12.1.0.2.0 - Production on Sat Jul 28 11:39:28 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   
8. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
   
9. ORA-31626: job does not exist
   
10. ORA-31633: unable to create master table "USER1.SYS_IMPORT_TABLE_05"
    
11. ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
    
12. ORA-06512: at "SYS.KUPV$FT", line 1048
    
13. ORA-01031: insufficient privileges
    

复制代码

1. grant read,write on directory dir1 to user1;
   
2. alter user user1 account unlock;
   
3. 
   
4. grant resource to user1;
   
5. 
   
6. alter user user1 quota unlimited on example;

复制代码

1. [oracle@station90 ~]$ impdp user1/oracle_4U directory=dir1 dumpfile=full.dmp  tables=hr.employees remap_schema=hr:user1
   
2. 
   
3. Import: Release 12.1.0.2.0 - Production on Sat Jul 28 11:44:24 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   
8. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
   
9. Master table "USER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
   
10. Starting "USER1"."SYS_IMPORT_TABLE_01":  user1/******** directory=dir1 dumpfile=full.dmp tables=hr.employees remap_schema=hr:user1
    
11. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
    
12. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
    
13. . . imported "USER1"."EMPLOYEES"                         17.07 KB     107 rows
    
14. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    
15. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
    
16. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
    
17. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
    
18. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
    
19. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    
20. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
    
21. ORA-39083: Object type REF_CONSTRAINT:"USER1"."EMP_DEPT_FK" failed to create with error:
    
22. ORA-00942: table or view does not exist
    
23. Failing sql is:
    
24. ALTER TABLE "USER1"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "USER1"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
    
25. ORA-39083: Object type REF_CONSTRAINT:"USER1"."EMP_JOB_FK" failed to create with error:
    
26. ORA-00942: table or view does not exist
    
27. Failing sql is:
    
28. ALTER TABLE "USER1"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "USER1"."JOBS" ("JOB_ID") ENABLE
    
29. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
    
30. Processing object type DATABASE_EXPORT/STATISTICS/MARKER
    
31. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
    
32. ORA-39083: Object type TRIGGER failed to create with error:
    
33. ORA-31625: Schema SYS is needed to import this object, but is unaccessible
    
34. ORA-01031: insufficient privileges
    
35. Failing sql is:
    
36. CREATE NONEDITIONABLE TRIGGER trg05211
    
37. after update of salary on hr.employees
    
38. referencing new as new old as old
    
39. for each row
    
40. begin
    
41.   if :old.salary != :new.salary
    
42.   then
    
43.       insert into t05211_value  values( to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '||
    
44.                                                   sys_context('usere
    
45. ORA-39082: Object type TRIGGER:"USER1"."SECURE_EMPLOYEES" created with compilation warnings
    
46. ORA-39082: Object type TRIGGER:"USER1"."UPDATE_JOB_HISTORY" created with compilation warnings
    
47. Job "USER1"."SYS_IMPORT_TABLE_01" completed with 5 error(s) at Sat Jul 28 11:44:47 2018 elapsed 0 00:00:22
    
48. 
    
49. 
    

复制代码

1. [oracle@station90 ~]$ impdp user1/oracle_4U directory=dir1 dumpfile=full.dmp  tables=hr.departments remap_schema=hr:user1  remap_tablespace=example:users
   
2. 
   
3. Import: Release 12.1.0.2.0 - Production on Sat Jul 28 11:46:39 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   
8. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
   
9. Master table "USER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
   
10. Starting "USER1"."SYS_IMPORT_TABLE_01":  user1/******** directory=dir1 dumpfile=full.dmp tables=hr.departments remap_schema=hr:user1 remap_tablespace=example:users
    
11. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
    
12. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
    
13. . . imported "USER1"."DEPARTMENTS"                       7.125 KB      27 rows
    
14. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    
15. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
    
16. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
    
17. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
    
18. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    
19. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
    
20. ORA-39083: Object type REF_CONSTRAINT:"USER1"."DEPT_LOC_FK" failed to create with error:
    
21. ORA-00942: table or view does not exist
    
22. Failing sql is:
    
23. ALTER TABLE "USER1"."DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID") REFERENCES "USER1"."LOCATIONS" ("LOCATION_ID") ENABLE
    
24. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
    
25. Processing object type DATABASE_EXPORT/STATISTICS/MARKER
    
26. Job "USER1"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Sat Jul 28 11:47:02 2018 elapsed 0 00:00:21
    
27. 
    
28. [oracle@station90 ~]$
    

复制代码

1. select  * from dba_tables t where t.table_name  in ('DEPARTMENTS',
   
2. 'EMPLOYEES')
   
3. ;

复制代码

OWNER

TABLE_NAME

TABLESPACE_NAME

CLUSTER_NAME

IOT_NAME

STATUS

PCT_FREE

PCT_USED

INI_TRANS

MAX_TRANS

INITIAL_EXTENT

NEXT_EXTENT

MIN_EXTENTS

MAX_EXTENTS

PCT_INCREASE

FREELISTS

FREELIST_GROUPS

LOGGING

BACKED_UP

NUM_ROWS

BLOCKS

EMPTY_BLOCKS

AVG_SPACE

CHAIN_CNT

AVG_ROW_LEN

AVG_SPACE_FREELIST_BLOCKS

NUM_FREELIST_BLOCKS

DEGREE

INSTANCES

CACHE

TABLE_LOCK

SAMPLE_SIZE

LAST_ANALYZED

PARTITIONED

IOT_TYPE

TEMPORARY

SECONDARY

NESTED

BUFFER_POOL

FLASH_CACHE

CELL_FLASH_CACHE

ROW_MOVEMENT

GLOBAL_STATS

USER_STATS

DURATION

SKIP_CORRUPT

MONITORING

CLUSTER_OWNER

DEPENDENCIES

COMPRESSION

COMPRESS_FOR

DROPPED

READ_ONLY

SEGMENT_CREATED

RESULT_CACHE

CLUSTERING

ACTIVITY_TRACKING

DML_TIMESTAMP

HAS_IDENTITY

CONTAINER_DATA

INMEMORY

INMEMORY_PRIORITY

INMEMORY_DISTRIBUTE

INMEMORY_COMPRESSION

INMEMORY_DUPLICATE

1

HR

DEPARTMENTS

EXAMPLE

VALID

10

1

255

65536

1048576

1

2147483645

NO

N

27

5

0

0

0

21

0

0

1

1

N

ENABLED

27

7/7/2014 6:56:25 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

2

HR

EMPLOYEES

EXAMPLE

VALID

10

1

255

65536

1048576

1

2147483645

NO

N

107

5

0

0

0

69

0

0

1

1

N

ENABLED

107

7/7/2014 6:56:25 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

3

USER1

EMPLOYEES

EXAMPLE

VALID

10

1

255

65536

1048576

1

2147483645

NO

N

107

5

0

0

0

69

0

0

1

1

N

ENABLED

107

7/7/2014 6:56:25 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

4

USER1

DEPARTMENTS

USERS

VALID

10

1

255

65536

1048576

1

2147483645

NO

N

27

5

0

0

0

21

0

0

1

1

N

ENABLED

27

7/7/2014 6:56:25 AM

NO

N

N

NO

DEFAULT

DEFAULT

DEFAULT

DISABLED

YES

NO

DISABLED

YES

DISABLED

DISABLED

NO

NO

YES

DEFAULT

NO

NO

NO

DISABLED

1. [oracle@station90 ~]$ expdp system/oracle_4U directory=dir1 dumpfile=dir1:'sh_hr_%U.dmp',dir2:'sh_hr_%U.dmp'  schemas=sh,hr job_name=sh_hr1job  parallel=4
   
2. 
   
3. Export: Release 12.1.0.2.0 - Production on Sat Jul 28 11:52:35 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   
8. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
   
9. Starting "SYSTEM"."SH_HR1JOB":  system/******** directory=dir1 dumpfile=dir1:sh_hr_%U.dmp,dir2:sh_hr_%U.dmp schemas=sh,hr job_name=sh_hr1job parallel=4
   
10. Estimate in progress using BLOCKS method...
    
11. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    
12. Total estimation using BLOCKS method: 287.3 MB
    
13. . . exported "HR"."T05207_B"                                 0 KB       0 rows
    
14. Processing object type SCHEMA_EXPORT/USER
    
15. . . exported "SH"."COSTS":"COSTS_Q1_1999"                183.7 KB    5884 rows
    
16. . . exported "SH"."COSTS":"COSTS_Q1_1998"                139.6 KB    4411 rows
    
17. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    
18. . . exported "SH"."CUSTOMERS"                            10.27 MB   55500 rows
    
19. . . exported "SH"."COSTS":"COSTS_Q1_2001"                228.0 KB    7328 rows
    
20. . . exported "SH"."COSTS":"COSTS_Q1_2000"                120.7 KB    3772 rows
    
21. Processing object type SCHEMA_EXPORT/ROLE_GRANT
    
22. . . exported "SH"."COSTS":"COSTS_Q2_1998"                79.68 KB    2397 rows
    
23. . . exported "SH"."COSTS":"COSTS_Q2_1999"                132.7 KB    4179 rows
    
24. . . exported "SH"."COSTS":"COSTS_Q2_2000"                119.1 KB    3715 rows
    
25. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    
26. . . exported "SH"."COSTS":"COSTS_Q2_2001"                184.7 KB    5882 rows
    
27. . . exported "SH"."COSTS":"COSTS_Q3_1998"                131.3 KB    4129 rows
    
28. . . exported "SH"."COSTS":"COSTS_Q3_2000"                151.6 KB    4798 rows
    
29. . . exported "SH"."COSTS":"COSTS_Q3_1999"                137.5 KB    4336 rows
    
30. . . exported "SH"."COSTS":"COSTS_Q4_1999"                159.2 KB    5060 rows
    
31. . . exported "SH"."COSTS":"COSTS_Q4_1998"                144.8 KB    4577 rows
    
32. . . exported "SH"."COSTS":"COSTS_Q3_2001"                234.6 KB    7545 rows
    
33. . . exported "SH"."COSTS":"COSTS_Q4_2000"                160.4 KB    5088 rows
    
34. . . exported "SH"."COSTS":"COSTS_Q4_2001"                278.5 KB    9011 rows
    
35. . . exported "SH"."SALES":"SALES_Q1_2000"                2.012 MB   62197 rows
    
36. . . exported "SH"."SALES":"SALES_Q1_1998"                1.413 MB   43687 rows
    
37. . . exported "SH"."SALES":"SALES_Q1_1999"                2.071 MB   64186 rows
    
38. . . exported "SH"."SALES":"SALES_Q2_1998"                1.160 MB   35758 rows
    
39. . . exported "SH"."SALES":"SALES_Q1_2001"                1.965 MB   60608 rows
    
40. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    
41. . . exported "SH"."SALES":"SALES_Q2_1999"                1.754 MB   54233 rows
    
42. . . exported "SH"."SALES":"SALES_Q2_2000"                1.802 MB   55515 rows
    
43. . . exported "SH"."SALES":"SALES_Q3_1998"                1.634 MB   50515 rows
    
44. . . exported "SH"."SALES":"SALES_Q2_2001"                2.051 MB   63292 rows
    
45. . . exported "SH"."SALES":"SALES_Q3_1999"                2.166 MB   67138 rows
    
46. . . exported "SH"."SALES":"SALES_Q3_2000"                1.910 MB   58950 rows
    
47. . . exported "SH"."SALES":"SALES_Q3_2001"                2.130 MB   65769 rows
    
48. . . exported "SH"."SALES":"SALES_Q4_1998"                1.581 MB   48874 rows
    
49. . . exported "SH"."SALES":"SALES_Q4_1999"                2.014 MB   62388 rows
    
50. . . exported "SH"."SALES":"SALES_Q4_2000"                1.814 MB   55984 rows
    
51. . . exported "SH"."SALES":"SALES_Q4_2001"                2.257 MB   69749 rows
    
52. . . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS"           697.6 KB    4500 rows
    
53. . . exported "HR"."T04209_UNAME"                         1.510 MB  100000 rows
    
54. . . exported "SH"."FWEEK_PSCAT_SALES_MV"                 419.9 KB   11266 rows
    
55. . . exported "SH"."PROMOTIONS"                           59.17 KB     503 rows
    
56. . . exported "SH"."TIMES"                                381.7 KB    1826 rows
    
57. . . exported "HR"."COUNTRIES"                            6.460 KB      25 rows
    
58. . . exported "HR"."DEPARTMENTS"                          7.125 KB      27 rows
    
59. . . exported "HR"."EMPLOYEES"                            17.07 KB     107 rows
    
60. . . exported "HR"."JOBS"                                 7.109 KB      19 rows
    
61. . . exported "HR"."JOB_HISTORY"                          7.195 KB      10 rows
    
62. . . exported "HR"."LOCATIONS"                            8.437 KB      23 rows
    
63. . . exported "HR"."REGIONS"                              5.546 KB       4 rows
    
64. . . exported "SH"."CAL_MONTH_SALES_MV"                   6.382 KB      48 rows
    
65. . . exported "SH"."CHANNELS"                             7.414 KB       5 rows
    
66. . . exported "SH"."COUNTRIES"                            10.46 KB      23 rows
    
67. . . exported "SH"."DIMENSION_EXCEPTIONS"                     0 KB       0 rows
    
68. . . exported "SH"."PRODUCTS"                             26.71 KB      72 rows
    
69. . . exported "HR"."SSMA"                                     0 KB       0 rows
    
70. . . exported "HR"."SSMM"                                     0 KB       0 rows
    
71. . . exported "HR"."T05207_A"                                 0 KB       0 rows
    
72. . . exported "SH"."COSTS":"COSTS_1995"                       0 KB       0 rows
    
73. . . exported "SH"."COSTS":"COSTS_1996"                       0 KB       0 rows
    
74. . . exported "SH"."COSTS":"COSTS_H1_1997"                    0 KB       0 rows
    
75. . . exported "SH"."COSTS":"COSTS_H2_1997"                    0 KB       0 rows
    
76. . . exported "SH"."COSTS":"COSTS_Q1_2002"                    0 KB       0 rows
    
77. . . exported "SH"."COSTS":"COSTS_Q1_2003"                    0 KB       0 rows
    
78. . . exported "SH"."COSTS":"COSTS_Q2_2002"                    0 KB       0 rows
    
79. . . exported "SH"."COSTS":"COSTS_Q2_2003"                    0 KB       0 rows
    
80. . . exported "SH"."COSTS":"COSTS_Q3_2002"                    0 KB       0 rows
    
81. . . exported "SH"."COSTS":"COSTS_Q3_2003"                    0 KB       0 rows
    
82. . . exported "SH"."COSTS":"COSTS_Q4_2002"                    0 KB       0 rows
    
83. . . exported "SH"."COSTS":"COSTS_Q4_2003"                    0 KB       0 rows
    
84. . . exported "SH"."SALES":"SALES_1995"                       0 KB       0 rows
    
85. . . exported "SH"."SALES":"SALES_1996"                       0 KB       0 rows
    
86. . . exported "SH"."SALES":"SALES_H1_1997"                    0 KB       0 rows
    
87. . . exported "SH"."SALES":"SALES_H2_1997"                    0 KB       0 rows
    
88. . . exported "SH"."SALES":"SALES_Q1_2002"                    0 KB       0 rows
    
89. . . exported "SH"."SALES":"SALES_Q1_2003"                    0 KB       0 rows
    
90. . . exported "SH"."SALES":"SALES_Q2_2002"                    0 KB       0 rows
    
91. . . exported "SH"."SALES":"SALES_Q2_2003"                    0 KB       0 rows
    
92. . . exported "SH"."SALES":"SALES_Q3_2002"                    0 KB       0 rows
    
93. . . exported "SH"."SALES":"SALES_Q3_2003"                    0 KB       0 rows
    
94. . . exported "SH"."SALES":"SALES_Q4_2002"                    0 KB       0 rows
    
95. . . exported "SH"."SALES":"SALES_Q4_2003"                    0 KB       0 rows
    
96. Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
    
97. Processing object type SCHEMA_EXPORT/TABLE/TABLE
    
98. Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    
99. Processing object type SCHEMA_EXPORT/TABLE/COMMENT
    
100. Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
     
101. Processing object type SCHEMA_EXPORT/TABLE/FGA_POLICY
     
102. Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
     
103. Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
     
104. Processing object type SCHEMA_EXPORT/VIEW/VIEW
     
105. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     
106. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     
107. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     
108. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     
109. Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
     
110. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
     
111. Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     
112. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     
113. Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
     
114. Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
     
115. Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
     
116. Processing object type SCHEMA_EXPORT/DIMENSION
     
117. Master table "SYSTEM"."SH_HR1JOB" successfully loaded/unloaded
     
118. ******************************************************************************
     
119. Dump file set for SYSTEM.SH_HR1JOB is:
     
120.   /home/oracle/dir1/sh_hr_01.dmp
     
121.   /home/oracle/dir2/sh_hr_01.dmp
     
122.   /home/oracle/dir1/sh_hr_02.dmp
     
123.   /home/oracle/dir2/sh_hr_02.dmp
     
124. Job "SYSTEM"."SH_HR1JOB" successfully completed at Sat Jul 28 11:54:04 2018 elapsed 0 00:01:27
     
125. 
     
126. [oracle@station90 ~]$
     

复制代码

1. [oracle@station90 ~]$ impdp system/oracle_4U directory=dir1 dumpfile=dir1:'sh_hr_%U.dmp',dir2:'sh_hr_%U.dmp'  tables=sh.sales  parallel=4  remap_schema=sh:user1
   
2. 
   
3. Import: Release 12.1.0.2.0 - Production on Sat Jul 28 11:55:36 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   
8. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
   
9. Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
   
10. Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=dir1 dumpfile=dir1:sh_hr_%U.dmp,dir2:sh_hr_%U.dmp tables=sh.sales parallel=4 remap_schema=sh:user1
    
11. Processing object type SCHEMA_EXPORT/TABLE/TABLE
    
12. 
    
13. 
    
14. 
    
15. 
    
16. ORA-39171: Job is experiencing a resumable wait.
    
17. Resumable error: ORA-01658: unable to create INITIAL extent for segment in tablespace EXAMPLE
    
18. Resumable stmt: CREATE TABLE "USER1"."SALES" ("PROD_ID" NUMBER NOT NULL ENABLE, "CUST_ID" NUMBER NOT NULL ENABLE, "TIME_ID" DATE NOT NULL ENABLE, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "PROMO_ID" NUMBER NOT NULL ENABLE, "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE, "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS  NOLOGGING  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE"  PARTITION BY RANGE ("TIME_ID")  (PARTITION "SALES_1995"  VALUES LESS THAN (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE" , PARTITION "SALES_1996"  VALUES LESS THAN (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 40 INITRANS 1 MAX
    
19. Resumable stmt status: SUSPENDED
    
20. Resumable stmt start: 07/28/18 11:55:40 stmt suspend: 07/28/18 11:55:42
    
21. ORA-39171: Job is experiencing a resumable wait.
    
22. Resumable error: ORA-01658: unable to create INITIAL extent for segment in tablespace EXAMPLE
    
23. Resumable stmt: CREATE TABLE "USER1"."SALES" ("PROD_ID" NUMBER NOT NULL ENABLE, "CUST_ID" NUMBER NOT NULL ENABLE, "TIME_ID" DATE NOT NULL ENABLE, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "PROMO_ID" NUMBER NOT NULL ENABLE, "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE, "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS  NOLOGGING  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE"  PARTITION BY RANGE ("TIME_ID")  (PARTITION "SALES_1995"  VALUES LESS THAN (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC NOLOGGING  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE" , PARTITION "SALES_1996"  VALUES LESS THAN (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED PCTFREE 0 PCTUSED 40 INITRANS 1 MAX
    
24. Resumable stmt status: SUSPENDED
    
25. Resumable stmt start: 07/28/18 11:55:40 stmt suspend: 07/28/18 12:02:45
    
26. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    
27. . . imported "USER1"."SALES":"SALES_Q1_1998"             1.413 MB   43687 rows
    
28. . . imported "USER1"."SALES":"SALES_Q1_1999"             2.071 MB   64186 rows
    
29. . . imported "USER1"."SALES":"SALES_Q1_2000"             2.012 MB   62197 rows
    
30. . . imported "USER1"."SALES":"SALES_Q1_2001"             1.965 MB   60608 rows
    
31. . . imported "USER1"."SALES":"SALES_Q2_1998"             1.160 MB   35758 rows
    
32. . . imported "USER1"."SALES":"SALES_Q2_1999"             1.754 MB   54233 rows
    
33. . . imported "USER1"."SALES":"SALES_Q4_2001"             2.257 MB   69749 rows
    
34. . . imported "USER1"."SALES":"SALES_1995"                    0 KB       0 rows
    
35. . . imported "USER1"."SALES":"SALES_1996"                    0 KB       0 rows
    
36. . . imported "USER1"."SALES":"SALES_Q3_2000"             1.910 MB   58950 rows
    
37. . . imported "USER1"."SALES":"SALES_H1_1997"                 0 KB       0 rows
    
38. . . imported "USER1"."SALES":"SALES_H2_1997"                 0 KB       0 rows
    
39. . . imported "USER1"."SALES":"SALES_Q1_2002"                 0 KB       0 rows
    
40. . . imported "USER1"."SALES":"SALES_Q1_2003"                 0 KB       0 rows
    
41. . . imported "USER1"."SALES":"SALES_Q2_2002"                 0 KB       0 rows
    
42. . . imported "USER1"."SALES":"SALES_Q2_2003"                 0 KB       0 rows
    
43. . . imported "USER1"."SALES":"SALES_Q3_2002"                 0 KB       0 rows
    
44. . . imported "USER1"."SALES":"SALES_Q3_2003"                 0 KB       0 rows
    
45. . . imported "USER1"."SALES":"SALES_Q4_2002"                 0 KB       0 rows
    
46. . . imported "USER1"."SALES":"SALES_Q4_2003"                 0 KB       0 rows
    
47. . . imported "USER1"."SALES":"SALES_Q2_2000"             1.802 MB   55515 rows
    
48. . . imported "USER1"."SALES":"SALES_Q3_2001"             2.130 MB   65769 rows
    
49. . . imported "USER1"."SALES":"SALES_Q2_2001"             2.051 MB   63292 rows
    
50. . . imported "USER1"."SALES":"SALES_Q4_1998"             1.581 MB   48874 rows
    
51. . . imported "USER1"."SALES":"SALES_Q3_1998"             1.634 MB   50515 rows
    
52. . . imported "USER1"."SALES":"SALES_Q4_1999"             2.014 MB   62388 rows
    
53. . . imported "USER1"."SALES":"SALES_Q3_1999"             2.166 MB   67138 rows
    
54. . . imported "USER1"."SALES":"SALES_Q4_2000"             1.814 MB   55984 rows
    
55. Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    
56. Processing object type SCHEMA_EXPORT/TABLE/COMMENT
    
57. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    
58. ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_PROMO_FK" failed to create with error:
    
59. ORA-00942: table or view does not exist
    
60. Failing sql is:
    
61. ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_PROMO_FK" FOREIGN KEY ("PROMO_ID") REFERENCES "USER1"."PROMOTIONS" ("PROMO_ID") ENABLE NOVALIDATE
    
62. ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_CUSTOMER_FK" failed to create with error:
    
63. ORA-00942: table or view does not exist
    
64. Failing sql is:
    
65. ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_CUSTOMER_FK" FOREIGN KEY ("CUST_ID") REFERENCES "USER1"."CUSTOMERS" ("CUST_ID") ENABLE NOVALIDATE
    
66. ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_PRODUCT_FK" failed to create with error:
    
67. ORA-00942: table or view does not exist
    
68. Failing sql is:
    
69. ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_PRODUCT_FK" FOREIGN KEY ("PROD_ID") REFERENCES "USER1"."PRODUCTS" ("PROD_ID") ENABLE NOVALIDATE
    
70. ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_TIME_FK" failed to create with error:
    
71. ORA-00942: table or view does not exist
    
72. Failing sql is:
    
73. ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_TIME_FK" FOREIGN KEY ("TIME_ID") REFERENCES "USER1"."TIMES" ("TIME_ID") ENABLE NOVALIDATE
    
74. ORA-39083: Object type REF_CONSTRAINT:"USER1"."SALES_CHANNEL_FK" failed to create with error:
    
75. ORA-00942: table or view does not exist
    
76. Failing sql is:
    
77. ALTER TABLE "USER1"."SALES" ADD CONSTRAINT "SALES_CHANNEL_FK" FOREIGN KEY ("CHANNEL_ID") REFERENCES "USER1"."CHANNELS" ("CHANNEL_ID") ENABLE NOVALIDATE
    
78. Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
    
79. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
    
80. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    
81. Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
    
82. Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 7 error(s) at Sat Jul 28 12:06:42 2018 elapsed 0 00:11:04
    
83. 
    
84. [oracle@station90 ~]$
    

复制代码

如果不用parfile就很难把格式写清楚，

1. [oracle@station90 ~]$ expdp user1/oracle_4U directory=dir1  dumpfile=user1.dmp schemas=user1 job_name=user1job   exclude=table: "in ('sales')", function: "in ('FUNC2')" query='employees:where department_id=10'
   
2. LRM-00101: unknown parameter name 'department_id'
   
3. 
   
4. [oracle@station90 ~]$ expdp user1/oracle_4U directory=dir1  dumpfile=user1.dmp schemas=user1 job_name=user1job   exclude=table: "in ('sales')", function: "in ('FUNC2')" query='employees:where department_id=10'
   

复制代码

parfile：

1. userid=user1/oracle_4U
   
2. directory=dir1
   
3. dumpfile=user1.dmp
   
4. schemas=user1
   
5. job_name=user1job
   
6. exclude=table:" in ('SALES')",function:" in ('FUNC2')"
   
7. query="employees:where department_id=10"
   

复制代码

1. [oracle@station90 ~]$ expdp parfile=/home/oracle/parfile.ora
   
2. 
   
3. Export: Release 12.1.0.2.0 - Production on Sat Jul 28 15:02:19 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   
8. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
   
9. Starting "USER1"."USER1JOB":  user1/******** parfile=/home/oracle/parfile.ora
   
10. Estimate in progress using BLOCKS method...
    
11. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    
12. Total estimation using BLOCKS method: 128 KB
    
13. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    
14. Processing object type SCHEMA_EXPORT/TABLE/TABLE
    
15. Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    
16. Processing object type SCHEMA_EXPORT/TABLE/COMMENT
    
17. Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
    
18. Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
    
19. Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
    
20. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    
21. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    
22. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    
23. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    
24. Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
    
25. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    
26. Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
    
27. . . exported "USER1"."DEPARTMENTS"                       7.125 KB      27 rows
    
28. . . exported "USER1"."EMPLOYEES"                         9.609 KB       1 rows
    
29. Master table "USER1"."USER1JOB" successfully loaded/unloaded
    
30. ******************************************************************************
    
31. Dump file set for USER1.USER1JOB is:
    
32.   /home/oracle/dir1/user1.dmp
    
33. Job "USER1"."USER1JOB" successfully completed at Sat Jul 28 15:02:55 2018 elapsed 0 00:00:35
    
34. 
    
35. [oracle@station90 ~]$
    

复制代码

1. [oracle@station90 ~]$ impdp system/oracle_4U directory=dir1 dumpfile=user1.dmp schemas=user1 remap_schema=user1:user2

复制代码

1. select  * from dba_objects where owner='USER2';

复制代码

OWNER

OBJECT_NAME

SUBOBJECT_NAME

OBJECT_ID

DATA_OBJECT_ID

OBJECT_TYPE

CREATED

LAST_DDL_TIME

TIMESTAMP

STATUS

TEMPORARY

GENERATED

SECONDARY

NAMESPACE

EDITION_NAME

SHARING

EDITIONABLE

ORACLE_MAINTAINED

1

USER2

UPDATE_JOB_HISTORY

94097

TRIGGER

7/28/2018 3:11:43 PM

7/28/2018 3:11:57 PM

2018-07-28:15:11:57

INVALID

N

N

N

3

NONE

Y

N

2

USER2

SECURE_EMPLOYEES

94096

TRIGGER

7/28/2018 3:11:43 PM

7/28/2018 3:11:57 PM

2018-07-28:15:11:57

INVALID

N

N

N

3

NONE

Y

N

3

USER2

EMP_EMAIL_UK

94095

94095

INDEX

7/28/2018 3:11:43 PM

7/28/2018 3:11:43 PM

2018-07-28:15:11:43

VALID

N

N

N

4

NONE

N

4

USER2

DEPT_LOCATION_IX

94094

94094

INDEX

7/28/2018 3:11:43 PM

7/28/2018 3:11:43 PM

2018-07-28:15:11:43

VALID

N

N

N

4

NONE

N

5

USER2

DEPT_ID_PK

94093

94093

INDEX

7/28/2018 3:11:43 PM

7/28/2018 3:11:43 PM

2018-07-28:15:11:43

VALID

N

N

N

4

NONE

N

6

USER2

EMP_NAME_IX

94092

94092

INDEX

7/28/2018 3:11:43 PM

7/28/2018 3:11:43 PM

2018-07-28:15:11:43

VALID

N

N

N

4

NONE

N

7

USER2

EMP_MANAGER_IX

94091

94091

INDEX

7/28/2018 3:11:43 PM

7/28/2018 3:11:43 PM

2018-07-28:15:11:43

VALID

N

N

N

4

NONE

N

8

USER2

EMP_JOB_IX

94090

94090

INDEX

7/28/2018 3:11:43 PM

7/28/2018 3:11:43 PM

2018-07-28:15:11:43

VALID

N

N

N

4

NONE

N

9

USER2

EMP_DEPARTMENT_IX

94089

94089

INDEX

7/28/2018 3:11:43 PM

7/28/2018 3:11:43 PM

2018-07-28:15:11:43

VALID

N

N

N

4

NONE

N

10

USER2

EMP_EMP_ID_PK

94088

94088

INDEX

7/28/2018 3:11:43 PM

7/28/2018 3:11:43 PM

2018-07-28:15:11:43

VALID

N

N

N

4

NONE

N

11

USER2

FUNC1

94087

FUNCTION

7/28/2018 3:11:42 PM

7/28/2018 3:11:42 PM

2018-07-28:14:53:30

VALID

N

N

N

1

NONE

Y

N

12

USER2

DEPARTMENTS

94086

94086

TABLE

7/28/2018 3:11:42 PM

7/28/2018 3:11:43 PM

2018-07-28:15:11:42

VALID

N

N

N

1

NONE

N

13

USER2

EMPLOYEES

94085

94085

TABLE

7/28/2018 3:11:42 PM

7/28/2018 3:11:43 PM

2018-07-28:15:11:42

VALID

N

N

N

1

NONE

N

最后带网络链：
parfile：

1. userid=user2/oracle_4U
   
2. 
   
3. directory=dirwin
   
4. network_link=dblink1
   
5. 
   
6. version=11.2.0
   
7. 
   
8. job_name=user1job
   
9. 
   
10. exclude=table:" in ('SALES')",function:" in ('FUNC2')"
    
11. 
    
12. query="user1.employees:where department_id=10"
    
13. 
    
14. flashback_time='2018-07-28:15:22:29'
    
15. remap_schema=user1:user2
    

复制代码

在win目的地这边：
impdp parfile=c:\data\parfile.ora




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

datapump外部表：

1. CREATE TABLE ext_emp_query_results
   
2. 
   
3. ORGANIZATION EXTERNAL
   
4.   (
   
5.     TYPE ORACLE_DATAPUMP
   
6.     DEFAULT DIRECTORY dir1
   
7.     LOCATION (dir1:'emp1.exp',dir2:'emp2.exp')
   
8.   )
   
9. PARALLEL
   
10. AS
    
11. SELECT e.first_name,e.last_name,d.department_name
    
12. FROM   employees e, departments d
    
13. WHERE  e.department_id = d.department_id AND
    
14.        d.department_name in
    
15.                      ('Marketing', 'Purchasing');

复制代码

[oracle@station90 dir2]$ ls -l *exp
-rw-r----- 1 oracle oinstall 12288  7月 28 15:48 emp2.exp
[oracle@station90 dir2]$ ls -l ../dir1/*exp
-rw-r----- 1 oracle oinstall 12288  7月 28 15:48 ../dir1/emp1.exp
[oracle@station90 dir2]$

1. [oracle@station90 dir1]$ strings  emp1.exp
   
2. "HR"."U"
   
3. x86_64/Linux 2.4.xx
   
4. AL32UTF8
   
5. 12.01.00.02.00
   
6. 001:001:000001:000001
   
7. i<?xml version="1.0"?>
   
8. <ROWSET>
   
9. <ROW>
   
10.   <STRMTABLE_T>
    
11.    <VERS_MAJOR>1</VERS_MAJOR>
    
12.    <VERS_MINOR>0 </VERS_MINOR>
    
13.    <VERS_DPAPI>3</VERS_DPAPI>
    
14.    <ENDIANNESS>0</ENDIANNESS>
    
15.    <CHARSET>AL32UTF8</CHARSET>
    
16.    <NCHARSET>AL16UTF16</NCHARSET>
    
17.    <DBTIMEZONE>+00:00</DBTIMEZONE>
    
18.    <OWNER_NAME>HR</OWNER_NAME>
    
19.    <NAME>EXT_EMP_QUERY_RESULTS</NAME>
    
20.    <COL_LIST>
    
21.      <COL_LIST_ITEM>
    
22.       <COL_NUM>1</COL_NUM>
    
23.       <NAME>FIRST_NAME</NAME>
    
24.       <TYPE_NUM>1</TYPE_NUM>
    
25.       <LENGTH>20</LENGTH>
    
26.       <PRECISION_NUM>0</PRECISION_NUM>
    
27.       <SCALE>0</SCALE>
    
28.       <CHARSETID>873</CHARSETID>
    
29.       <CHARSETFORM>1</CHARSETFORM>
    
30.       <CHARLENGTH>20</CHARLENGTH>
    
31.      </COL_LIST_ITEM>
    
32.      <COL_LIST_ITEM>
    
33.       <COL_NUM>2</COL_NUM>
    
34.       <NAME>LAST_NAME</NAME>
    
35.       <TYPE_NUM>1</TYPE_NUM>
    
36.       <LENGTH>25</LENGTH>
    
37.       <PRECISION_NUM>0</PRECISION_NUM>
    
38.       <SCALE>0</SCALE>
    
39.       <CHARSETID>873</CHARSETID>
    
40.       <CHARSETFORM>1</CHARSETFORM>
    
41.       <CHARLENGTH>25</CHARLENGTH>
    
42.      </COL_LIST_ITEM>
    
43.      <COL_LIST_ITEM>
    
44.       <COL_NUM>3</COL_NUM>
    
45.       <NAME>DEPARTMENT_NAME</NAME>
    
46.       <TYPE_NUM>1</TYPE_NUM>
    
47.       <LENGTH>30</LENGTH>
    
48.       <PRECISION_NUM>0</PRECISION_NUM>
    
49.       <SCALE>0</SCALE>
    
50.       <CHARSETID>873</CHARSETID>
    
51.       <CHARSETFORM>1</CHARSETFORM>
    
52.       <CHARLENGTH>30</CHARLENGTH>
    
53.      </COL_LIST_ITEM>
    
54.    </COL_LIST>
    
55.   </STRMTABLE_T>
    
56. </ROW>
    
57. </ROWSET>
    
58. [oracle@station90 dir1]$
    

复制代码

1. CREATE TABLE ext_emp_query_results
   
2. (first_name  varchar2(20), last_name  varchar2(25), department_name varchar2(30))
   
3. ORGANIZATION EXTERNAL
   
4.   (
   
5.     TYPE ORACLE_DATAPUMP
   
6.     DEFAULT DIRECTORY dir1
   
7.     LOCATION (dir1:'emp1.exp',dir2:'emp2.exp')
   
8.   )
   
9. PARALLEL
   
10. ;
    

复制代码

1. select  * from ext_emp_query_results;

复制代码

	FIRST_NAME	LAST_NAME	DEPARTMENT_NAME
1	Shelli	Baida	Purchasing
2	Karen	Colmenares	Purchasing
3	Pat	Fay	Marketing
4	Michael	Hartstein	Marketing
5	Guy	Himuro	Purchasing
6	Alexander	Khoo	Purchasing
7	Den	Raphaely	Purchasing
8	Sigal	Tobias	Purchasing