课程第4次

1. [oracle@station79 ~]$ expdp hr/oracle_4U  directory=dir1  dumpfile=t_bank_invoice.dmp  tables=t_bank_invoice version=11.2.0.4.0         
   
2. 
   
3. Export: Release 11.2.0.4.0 - Production on Sat Aug 31 20:28:51 2019
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
8. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
   
9. and Real Application Testing options
   
10. Starting "HR"."SYS_EXPORT_TABLE_01":  hr/******** directory=dir1 dumpfile=t_bank_invoice.dmp tables=t_bank_invoice version=11.2.0.4.0
    
11. Estimate in progress using BLOCKS method...
    
12. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    
13. Total estimation using BLOCKS method: 64 KB
    
14. Processing object type TABLE_EXPORT/TABLE/TABLE
    
15. . . exported "HR"."T_BANK_INVOICE"                       10.39 KB       6 rows
    
16. Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    
17. ******************************************************************************
    
18. Dump file set for HR.SYS_EXPORT_TABLE_01 is:
    
19.   /home/oracle/dir1/t_bank_invoice.dmp
    
20. Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 31 20:28:59 2019 elapsed 0 00:00:06
    
21. 
    
22. [oracle@station79 ~]$ ls
    
23. 9isqlldr.tgz             Desktop    Downloads       Pictures                 sqlldr       update2_t04209_uname.sql
    
24. create_t04209_uname.sql  dir1       grid.rsp        Public                   Templates    update_t04209_uname.sql
    
25. db.rsp                   dir2       Music           RMasmcmd.sh              tpasswd.ctl  Videos
    
26. demo                     Documents  oradiag_oracle  select_t04209_uname.sql  tpasswd.sql
    
27. [oracle@station79 ~]$ cd dir1
    
28. [oracle@station79 dir1]$ ls
    
29. empxt000_16116.log  empxt001_25736.log  export.log             EXT_TPASSWD_16116.log  t_bank_invoice.dmp
    
30. empxt000_25734.log  empxt1.dat          EXT_TPASSWD_16116.bad  passwd.dat
    
31. [oracle@station79 dir1]$

复制代码

datapump作业，定名字，做暂停，重新开始：

1. [oracle@station79 ~]$ expdp \'sys/oracle_4U as sysdba\' directory=dir1  dumpfile=dir1:'full%U.dmp',dir2:'full%U.dmp'  parallel=4 filesize=20M   full=y  job_name=job_full
   
2. 
   
3. Export: Release 11.2.0.4.0 - Production on Sat Aug 31 20:42:09 2019
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
8. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
   
9. and Real Application Testing options
   
10. Starting "SYS"."JOB_FULL":  "sys/******** AS SYSDBA" directory=dir1 dumpfile=dir1:full%U.dmp,dir2:full%U.dmp parallel=4 filesize=20M full=y job_name=job_full
    
11. Estimate in progress using BLOCKS method...
    
12. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
    
13. Total estimation using BLOCKS method: 1.677 GB
    
14. . . exported "SH"."CUSTOMERS"                            9.853 MB   55500 rows
    
15. . . exported "PM"."ONLINE_MEDIA"                         7.854 MB       9 rows
    
16. . . exported "APEX_030200"."WWV_FLOW_PAGE_PLUGS"         5.205 MB    7416 rows
    
17. . . exported "SH"."COSTS":"COSTS_Q1_1998"                139.5 KB    4411 rows
    
18. . . exported "SH"."COSTS":"COSTS_Q1_1999"                183.5 KB    5884 rows
    
19. . . exported "SH"."COSTS":"COSTS_Q1_2000"                120.6 KB    3772 rows
    
20. . . exported "SH"."COSTS":"COSTS_Q1_2001"                227.8 KB    7328 rows
    
21. . . exported "SH"."COSTS":"COSTS_Q2_1998"                79.52 KB    2397 rows
    
22. . . exported "SH"."COSTS":"COSTS_Q2_1999"                132.5 KB    4179 rows
    
23. . . exported "SH"."COSTS":"COSTS_Q2_2000"                119.0 KB    3715 rows
    
24. . . exported "SH"."COSTS":"COSTS_Q2_2001"                184.5 KB    5882 rows
    
25. . . exported "SH"."COSTS":"COSTS_Q3_1998"                131.1 KB    4129 rows
    
26. . . exported "SH"."COSTS":"COSTS_Q3_1999"                137.3 KB    4336 rows
    
27. . . exported "SH"."COSTS":"COSTS_Q3_2000"                151.4 KB    4798 rows
    
28. . . exported "SH"."COSTS":"COSTS_Q3_2001"                234.4 KB    7545 rows
    
29. . . exported "SH"."COSTS":"COSTS_Q4_1998"                144.7 KB    4577 rows
    
30. . . exported "SH"."COSTS":"COSTS_Q4_1999"                159.0 KB    5060 rows
    
31. Processing object type DATABASE_EXPORT/TABLESPACE
    
32. . . exported "SH"."COSTS":"COSTS_Q4_2000"                160.2 KB    5088 rows
    
33. . . exported "SH"."COSTS":"COSTS_Q4_2001"                278.4 KB    9011 rows
    
34. Processing object type DATABASE_EXPORT/PROFILE
    
35. Processing object type DATABASE_EXPORT/SYS_USER/USER
    
36. Processing object type DATABASE_EXPORT/SCHEMA/USER
    
37. . . exported "SH"."SALES":"SALES_Q1_1998"                1.412 MB   43687 rows
    
38. Processing object type DATABASE_EXPORT/ROLE
    
39. . . exported "SH"."SALES":"SALES_Q1_1999"                2.071 MB   64186 rows
    
40. . . exported "SH"."SALES":"SALES_Q1_2000"                2.012 MB   62197 rows
    
41. . . exported "SH"."SALES":"SALES_Q1_2001"                1.965 MB   60608 rows
    
42. . . exported "SH"."SALES":"SALES_Q2_1998"                1.160 MB   35758 rows
    
43. . . exported "SH"."SALES":"SALES_Q2_1999"                1.754 MB   54233 rows
    
44. Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
    
45. Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
    
46. . . exported "SH"."SALES":"SALES_Q2_2000"                1.802 MB   55515 rows
    
47. . . exported "SH"."SALES":"SALES_Q2_2001"                2.051 MB   63292 rows
    
48. ^C
    
49. Export> help
    
50. ------------------------------------------------------------------------------
    
51. 
    
52. The following commands are valid while in interactive mode.
    
53. Note: abbreviations are allowed.
    
54. 
    
55. ADD_FILE
    
56. Add dumpfile to dumpfile set.
    
57. 
    
58. CONTINUE_CLIENT
    
59. Return to logging mode. Job will be restarted if idle.
    
60. 
    
61. EXIT_CLIENT
    
62. Quit client session and leave job running.
    
63. 
    
64. FILESIZE
    
65. Default filesize (bytes) for subsequent ADD_FILE commands.
    
66. 
    
67. HELP
    
68. Summarize interactive commands.
    
69. 
    
70. KILL_JOB
    
71. Detach and delete job.
    
72. 
    
73. PARALLEL
    
74. Change the number of active workers for current job.
    
75. 
    
76. REUSE_DUMPFILES
    
77. Overwrite destination dump file if it exists [N].
    
78. 
    
79. START_JOB
    
80. Start or resume current job.
    
81. Valid keyword values are: SKIP_CURRENT.
    
82. 
    
83. STATUS
    
84. Frequency (secs) job status is to be monitored where
    
85. the default [0] will show new status when available.
    
86. 
    
87. STOP_JOB
    
88. Orderly shutdown of job execution and exits the client.
    
89. Valid keyword values are: IMMEDIATE.
    
90. 
    
91. 
    
92. Export> stop_job
    
93. Are you sure you wish to stop this job ([yes]/no): yes
    
94. 
    
95. [oracle@station79 ~]$ expdp \'sys/oracle_4U as sysdba\' attach=job_full                                                                  
    
96. Export: Release 11.2.0.4.0 - Production on Sat Aug 31 20:43:19 2019
    
97. 
    
98. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
99. 
    
100. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
     
101. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
     
102. and Real Application Testing options
     
103. 
     
104. Job: JOB_FULL
     
105.   Owner: SYS                           
     
106.   Operation: EXPORT                        
     
107.   Creator Privs: TRUE                           
     
108.   GUID: 916A112E2A8E1409E0534F00A8C02185
     
109.   Start Time: Saturday, 31 August, 2019 20:42:10
     
110.   Mode: FULL                           
     
111.   Instance: orcl
     
112.   Max Parallelism: 4
     
113.   EXPORT Job Parameters:
     
114.   Parameter Name      Parameter Value:
     
115.      CLIENT_COMMAND        "sys/******** AS SYSDBA" directory=dir1 dumpfile=dir1:full%U.dmp,dir2:full%U.dmp parallel=4 filesize=20M full=y job_name=job_full
     
116.   State: STOP PENDING                  
     
117.   Bytes Processed: 1,155,383,896
     
118.   Percent Done: 97
     
119.   Current Parallelism: 4
     
120.   Job Error Count: 0
     
121.   Dump File: /home/oracle/dir1/full%u.dmp
     
122.     size: 20,971,520
     
123.   Dump File: /home/oracle/dir1/full01.dmp
     
124.     size: 20,971,520
     
125.     bytes written: 20,971,520
     
126.   Dump File: /home/oracle/dir2/full%u.dmp
     
127.     size: 20,971,520
     
128.   Dump File: /home/oracle/dir2/full01.dmp
     
129.     size: 20,971,520
     
130.     bytes written: 20,971,520
     
131.   Dump File: /home/oracle/dir1/full02.dmp
     
132.     size: 20,971,520
     
133.     bytes written: 20,971,520
     
134.   Dump File: /home/oracle/dir2/full02.dmp
     
135.     size: 20,971,520
     
136.     bytes written: 20,971,520
     
137.   Dump File: /home/oracle/dir1/full03.dmp
     
138.     size: 20,971,520
     
139.     bytes written: 20,971,520
     
140.   Dump File: /home/oracle/dir2/full03.dmp
     
141.     size: 20,971,520
     
142.     bytes written: 20,971,520
     
143.   Dump File: /home/oracle/dir1/full04.dmp
     
144.     size: 20,971,520
     
145.     bytes written: 20,971,520
     
146.   Dump File: /home/oracle/dir2/full04.dmp
     
147.     size: 20,971,520
     
148.     bytes written: 20,971,520
     
149.   Dump File: /home/oracle/dir1/full05.dmp
     
150.     size: 20,971,520
     
151.     bytes written: 20,971,520
     
152.   Dump File: /home/oracle/dir2/full05.dmp
     
153.     size: 20,971,520
     
154.     bytes written: 20,971,520
     
155.   Dump File: /home/oracle/dir1/full06.dmp
     
156.     size: 20,971,520
     
157.     bytes written: 20,971,520
     
158.   Dump File: /home/oracle/dir2/full06.dmp
     
159.     size: 20,971,520
     
160.     bytes written: 20,971,520
     
161.   Dump File: /home/oracle/dir1/full07.dmp
     
162.     size: 20,971,520
     
163.     bytes written: 20,971,520
     
164.   Dump File: /home/oracle/dir2/full07.dmp
     
165.     size: 20,971,520
     
166.     bytes written: 20,971,520
     
167.   Dump File: /home/oracle/dir1/full08.dmp
     
168.     size: 20,971,520
     
169.     bytes written: 20,971,520
     
170.   Dump File: /home/oracle/dir2/full08.dmp
     
171.     size: 20,971,520
     
172.     bytes written: 20,971,520
     
173.   Dump File: /home/oracle/dir1/full09.dmp
     
174.     size: 20,971,520
     
175.     bytes written: 20,971,520
     
176.   Dump File: /home/oracle/dir2/full09.dmp
     
177.     size: 20,971,520
     
178.     bytes written: 20,971,520
     
179.   Dump File: /home/oracle/dir1/full10.dmp
     
180.     size: 20,971,520
     
181.     bytes written: 20,971,520
     
182.   Dump File: /home/oracle/dir2/full10.dmp
     
183.     size: 20,971,520
     
184.     bytes written: 20,971,520
     
185.   Dump File: /home/oracle/dir1/full11.dmp
     
186.     size: 20,971,520
     
187.     bytes written: 20,971,520
     
188.   Dump File: /home/oracle/dir2/full11.dmp
     
189.     size: 20,971,520
     
190.     bytes written: 20,971,520
     
191.   Dump File: /home/oracle/dir1/full12.dmp
     
192.     size: 20,971,520
     
193.     bytes written: 4,096
     
194.   Dump File: /home/oracle/dir2/full12.dmp
     
195.     size: 20,971,520
     
196.     bytes written: 20,971,520
     
197.   Dump File: /home/oracle/dir1/full13.dmp
     
198.     size: 20,971,520
     
199.     bytes written: 20,971,520
     
200.   Dump File: /home/oracle/dir2/full13.dmp
     
201.     size: 20,971,520
     
202.     bytes written: 20,971,520
     
203.   Dump File: /home/oracle/dir1/full14.dmp
     
204.     size: 20,971,520
     
205.     bytes written: 20,971,520
     
206.   Dump File: /home/oracle/dir2/full14.dmp
     
207.     size: 20,971,520
     
208.     bytes written: 20,971,520
     
209.   Dump File: /home/oracle/dir1/full15.dmp
     
210.     size: 20,971,520
     
211.     bytes written: 20,971,520
     
212.   Dump File: /home/oracle/dir2/full15.dmp
     
213.     size: 20,971,520
     
214.     bytes written: 20,971,520
     
215.   Dump File: /home/oracle/dir1/full16.dmp
     
216.     size: 20,971,520
     
217.     bytes written: 20,971,520
     
218.   Dump File: /home/oracle/dir2/full16.dmp
     
219.     size: 20,971,520
     
220.     bytes written: 20,971,520
     
221.   Dump File: /home/oracle/dir1/full17.dmp
     
222.     size: 20,971,520
     
223.     bytes written: 20,971,520
     
224.   Dump File: /home/oracle/dir2/full17.dmp
     
225.     size: 20,971,520
     
226.     bytes written: 20,971,520
     
227.   Dump File: /home/oracle/dir1/full18.dmp
     
228.     size: 20,971,520
     
229.     bytes written: 20,971,520
     
230.   Dump File: /home/oracle/dir2/full18.dmp
     
231.     size: 20,971,520
     
232.     bytes written: 20,971,520
     
233.   Dump File: /home/oracle/dir1/full19.dmp
     
234.     size: 20,971,520
     
235.     bytes written: 20,971,520
     
236.   Dump File: /home/oracle/dir2/full19.dmp
     
237.     size: 20,971,520
     
238.     bytes written: 20,971,520
     
239.   Dump File: /home/oracle/dir1/full20.dmp
     
240.     size: 20,971,520
     
241.     bytes written: 20,971,520
     
242.   Dump File: /home/oracle/dir2/full20.dmp
     
243.     size: 20,971,520
     
244.     bytes written: 20,971,520
     
245.   Dump File: /home/oracle/dir1/full21.dmp
     
246.     size: 20,971,520
     
247.     bytes written: 20,971,520
     
248.   Dump File: /home/oracle/dir2/full21.dmp
     
249.     size: 20,971,520
     
250.     bytes written: 20,971,520
     
251.   Dump File: /home/oracle/dir1/full22.dmp
     
252.     size: 20,971,520
     
253.     bytes written: 20,971,520
     
254.   Dump File: /home/oracle/dir2/full22.dmp
     
255.     size: 20,971,520
     
256.     bytes written: 20,971,520
     
257.   Dump File: /home/oracle/dir1/full23.dmp
     
258.     size: 20,971,520
     
259.     bytes written: 20,971,520
     
260.   Dump File: /home/oracle/dir2/full23.dmp
     
261.     size: 20,971,520
     
262.     bytes written: 20,971,520
     
263.   Dump File: /home/oracle/dir1/full24.dmp
     
264.     size: 20,971,520
     
265.     bytes written: 20,971,520
     
266.   Dump File: /home/oracle/dir2/full24.dmp
     
267.     size: 20,971,520
     
268.     bytes written: 20,971,520
     
269.   Dump File: /home/oracle/dir1/full25.dmp
     
270.     size: 20,971,520
     
271.     bytes written: 20,971,520
     
272.   Dump File: /home/oracle/dir2/full25.dmp
     
273.     size: 20,971,520
     
274.     bytes written: 20,971,520
     
275.   Dump File: /home/oracle/dir1/full26.dmp
     
276.     size: 20,971,520
     
277.     bytes written: 20,971,520
     
278.   Dump File: /home/oracle/dir2/full26.dmp
     
279.     size: 20,971,520
     
280.     bytes written: 20,971,520
     
281.   Dump File: /home/oracle/dir1/full27.dmp
     
282.     size: 20,971,520
     
283.     bytes written: 20,971,520
     
284.   Dump File: /home/oracle/dir2/full27.dmp
     
285.     size: 20,971,520
     
286.     bytes written: 20,971,520
     
287.   Dump File: /home/oracle/dir1/full28.dmp
     
288.     size: 20,971,520
     
289.     bytes written: 20,971,520
     
290.   Dump File: /home/oracle/dir2/full28.dmp
     
291.     size: 20,971,520
     
292.     bytes written: 8,306,688
     
293.   Dump File: /home/oracle/dir1/full29.dmp
     
294.     size: 20,971,520
     
295.     bytes written: 14,995,456
     
296.   
     
297. Worker 1 Status:
     
298.   Process Name: DW00
     
299.   State: WORK WAITING                  
     
300.   
     
301. Worker 2 Status:
     
302.   Process Name: DW01
     
303.   State: WORK WAITING                  
     
304.   
     
305. Worker 3 Status:
     
306.   Process Name: DW02
     
307.   State: WORK WAITING                  
     
308.   
     
309. Worker 4 Status:
     
310.   Process Name: DW03
     
311.   State: WORK WAITING                  
     
312.   
     
313. Worker 5 Status:
     
314.   Process Name: DW04
     
315.   State: WORK WAITING                  
     
316.   
     
317. Worker 6 Status:
     
318.   Process Name: DW05
     
319.   State: WORK WAITING                  
     
320.   
     
321. Worker 7 Status:
     
322.   Process Name: DW00
     
323.   State: WORK WAITING                  
     
324. 
     
325. Export>

复制代码

1. 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'"
   

复制代码

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'

复制代码

-------补充，查表空间：

1. select  * from dba_objects o where o.owner='HR';
   
2. 
   
3. 
   
4. select  distinct s.tablespace_name from dba_segments  s where s.owner='HR';

复制代码