数据仓库活动总结帖

botang

第42/43次活动：2017-12-14（星期四晚上7:00-9:30）和2017-12-19（星期二晚上7:00-9:30）
文本原文：

1. 1234 BAKER      10 9999 101 102 103
   
2. 1234 JOKER      10 9999 777 888 999
   
3. 2664 YOUNG      20 2893 425 abc 102
   
4. 5321 OTOOLE     10 9999 321  55  40
   
5. 2134 FARMER     20 4555 236 456
   
6. 2414 LITTLE     20 5634 236 456  40
   
7. 6542 LEE        10 4532 102 321  14
   
8. 2849 EDDS       xx 4555     294  40
   
9. 4532 PERKINS    10 9999  40
   
10. 1244 HUNT       11 3452 665 133 456
    
11. 123 DOOLITTLE  12 9940         132
    
12. 1453 MACDONALD  25 5532     200  

复制代码

有问题的行：

1. 1234 BAKER      10 9999 101 102 103
   
2. --1234 JOKER      10 9999 777 888 999
   
3. --2664 YOUNG      20 2893 425 abc 102
   
4. 5321 OTOOLE     10 9999 321  55  40
   
5. 2134 FARMER     20 4555 236 456
   
6. 2414 LITTLE     20 5634 236 456  40
   
7. 6542 LEE        10 4532 102 321  14
   
8. --2849 EDDS       xx 4555     294  40
   
9. 4532 PERKINS    10 9999  40
   
10. 1244 HUNT       11 3452 665 133 456
    
11. 123 DOOLITTLE  12 9940         132
    
12. 1453 MACDONALD  25 5532     200  

复制代码

控制文件的写法：

1. -- Copyright (c) 1991 by Oracle Corporation
   
2. --   NAME
   
3. --     ulcase5.ctl - <one-line expansion of the name>
   
4. --   DESCRIPTION
   
5. --     <short description of component this file declares/defines>
   
6. --   RETURNS
   
7. --
   
8. --   NOTES
   
9. --     <other useful comments, qualifications, etc.>
   
10. --   MODIFIED   (MM/DD/YY)
    
11. --    ksudarsh   04/08/94 -  merge changes from branch 1.3.710.1
    
12. --    ksudarsh   02/21/94 -  quote dat file
    
13. --    ksudarsh   03/11/93 -  make filename lowercase
    
14. --    ksudarsh   11/06/92 -  infile is ulcase5
    
15. --    cheigham   08/28/91 -  Creation
    
16. --
    
17. -- $Header: ulcase5.ctl,v 1.4 1994/04/08 13:44:31 ksudarsh Exp $ case5.ctl
    
18. --
    
19. -- Loads EMP records from first 23 characters
    
20. -- Creates and loads PROJ records for each PROJO listed
    
21. -- for each employee
    
22. 
    
23. LOAD DATA
    
24. INFILE 'ulcase5.dat'
    
25. BADFILE 'ulcase5.bad'
    
26. DISCARDFILE 'ulcase5.dis'
    
27. REPLACE
    
28. 
    
29. INTO TABLE EMP
    
30.   (EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
    
31.    ENAME    POSITION(6:15)  CHAR,
    
32.    DEPTNO   POSITION(17:18) CHAR,
    
33.    MGR      POSITION(20:23) INTEGER EXTERNAL)
    
34. 
    
35. INTO TABLE PROJ
    
36. -- PROJ has two columns, both not null: EMPNO and PROJNO
    
37. WHEN PROJNO != '   '
    
38.   (EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
    
39.    PROJNO   POSITION(25:27) INTEGER EXTERNAL)   -- 1st proj
    
40. 
    
41. INTO TABLE PROJ
    
42. WHEN PROJNO != '   '
    
43.   (EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
    
44.    PROJNO   POSITION(29:31) INTEGER EXTERNAL)   -- 2nd proj
    
45. 
    
46. INTO TABLE PROJ
    
47. WHEN PROJNO != '   '
    
48.   (EMPNO    POSITION(1:4)   INTEGER EXTERNAL,
    
49.    PROJNO   POSITION(33:35) INTEGER EXTERNAL)   -- 3rd proj        

复制代码

sqlldr  hr/oracle_4U  control=ulcase5.ctl  direct=y
写不写direct=y差别很大，写了能加载进尽量多的行（多表插入的时候，表是分开来看的）。

1. select  i.index_name , i.status
   
2. from dba_indexes i
   
3. where i.table_owner='HR' and i.table_name='EMP';
   
4. 
   
5. select  * from hr.emp  order by 1;
   
6. 
   
7. select  * from dba_constraints c
   
8.   where c.table_name='EMP' and c.owner='HR';
   
9.   
   
10.   --------------
    
11.   select * from hr.proj  order  by 2;
    
12.   
    
13.   

复制代码

1Z0-052 17-29ppt里，少掉的文本：

1. 
   
2. 
   
3.                Today's Newly Hired Employees
   
4. 
   
5. Dept  Job       Manager   MgrNo  Emp Name  EmpNo  Salary/Commission
   
6. ----  --------  --------  -----  --------  -----  -----------------
   
7. 20    Salesman  Blake      7698  Shepard    8061  $1,600.00 (3%)
   
8.                                  Falstaff   8066  $1,250.00 (5%)
   
9.                                  Major      8064  $1,250.00 (14%)
   
10. 
    
11. 30    Clerk     Scott      7788  Conrad     8062  $1,100.00
    
12.                 Ford       7369  DeSilva    8063    $800.00
    
13.       Manager   King       7839  Provo      8065  $2,975.00

复制代码

1. SQL*Loader Control File (continued)
   
2. 1         -- This is a sample control file
   
3. 2 LOAD DATA
   
4. 3 INFILE ’SAMPLE.DAT’
   
5. 4 BADFILE ’sample.bad’
   
6. 5 DISCARDFILE ’sample.dsc’
   
7. 6 APPEND
   
8. 7 INTO TABLE emp
   
9. 8 WHEN (57) = ’.’
   
10. 9 TRAILING NULLCOLS
    
11. 10 (hiredate SYSDATE,
    
12.                  deptno POSITION(1:2) INTEGER EXTERNAL(3)
    
13.             NULLIF deptno=BLANKS,
    
14.                  job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
    
15.                  NULLIF job=BLANKS "UPPER(:job)",
    
16.                  mgr POSITION(28:31) INTEGER EXTERNAL
    
17.                  TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
    
18.             ename POSITION(34:41) CHAR
    
19.                  TERMINATED BY WHITESPACE "UPPER(:ename)",
    
20.                  empno POSITION(45) INTEGER EXTERNAL
    
21.                  TERMINATED BY WHITESPACE,
    
22.                  sal POSITION(51) CHAR TERMINATED BY WHITESPACE
    
23.                  "TO_NUMBER(:sal,’$99,999.99’)",
    
24.                  comm INTEGER EXTERNAL ENCLOSED BY ’(’ AND ’%’
    
25.                  ":comm * 100"
    
26.          )
    
27. The explanation of this sample control file (by line numbers) is as follows:
    
28. 1.        Comments can appear anywhere in the command section of the file, but they must not appear in the data. Precede any comment with two hyphens. All text to the right of the double hyphen is ignored until the end of the line.
    
29. 2.        The LOAD DATA statement indicates to SQL*Loader that this is the beginning of a new data load. If you are continuing a load that has been interrupted in progress, use the CONTINUE LOAD DATA statement.
    
30. 3.        The INFILE keyword specifies the name of a data file containing data that you want to load.
    
31. 4.        The BADFILE keyword specifies the name of a file into which rejected records are placed.
    
32. 5.        The DISCARDFILE keyword specifies the name of a file into which discarded records are placed.
    
33. 6.        The APPEND keyword is one of the options that you can use when loading data into a table that is not empty. To load data into a table that is empty, use the INSERT keyword.
    
34. 7.        The INTO TABLE keyword enables you to identify tables, fields, and data types. It defines the relationship between records in the data file and tables in the database.
    
35. 8.        The WHEN clause specifies one or more field conditions that each record must match before SQL*Loader loads the data. In this example, SQL*Loader loads the record only if the 57th character is a decimal point. That decimal point delimits dollars and cents in the field and causes records to be rejected if SAL has no value.
    
36. 9.        The TRAILING NULLCOLS clause prompts SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.
    
37. 10.        The remainder of the control file contains the field list, which provides information about column formats in the table that is being loaded.
    

复制代码

1. select * from dba_tablespaces;
   
2. 
   
3. create  directory dirorcl as 'c:\data\dirorcl';
   
4. 
   
5. select  * from database_properties;
   
6. 
   
7. create tablespace tbszhs datafile 'C:\app\Administrator\oradata\orcl\tbszhs.dbf' size 10M ;
   
8. 
   
9. create table hr.tzhs(a number ) tablespace tbszhs;
   
10. 
    
11. insert into hr.tzhs values (100 ) ;
    
12. 
    
13. alter tablespace tbszhs read only ;
    
14. 
    
15. select  * from v$transportable_platform;
    
16. 
    
17. alter tablespace tbszhs read write ;
    
18. 
    
19. -----------------------------------------------
    
20. 
    
21. select * from dba_tablespaces;
    
22. 
    
23. create  directory dirutforcl as 'c:\data\dirutforcl';
    
24. 
    
25. select  * from dba_tables t where t.tablespace_name='TBSUTF8_11G';
    
26. 
    
27. begin
    
28.   dbms_tts.transport_set_check('TBSUTF8_11G');
    
29. end;
    
30. 
    
31. 
    
32. select  * from transport_set_violations;
    
33. 
    
34. 
    
35. alter table sys.TUTF8_11G_SYS  move tablespace system ;
    
36. 
    
37. alter tablespace tbsutf8_11g read only;
    
38. 
    
39. alter tablespace tbsutf8_11g read write;
    
40. 
    
41. 
    
42. 
    
43. -----------------------------------------
    
44. 
    
45. 数据泵导出实用程序提供了一种用于在 Oracle 数据库之间传输
    
46. 数据对象的机制。该实用程序可以使用以下命令进行调用:
    
47. 
    
48.   示例: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
    
49. 
    
50. 您可以控制导出的运行方式。具体方法是: 在 'expdp' 命令后输入
    
51. 各种参数。要指定各参数, 请使用关键字:
    
52. 
    
53.   格式:  expdp KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)
    
54.   示例: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
    
55.               或 TABLES=(T11,T12), 如果 T1 是分区表
    
56. 
    
57. SERID 必须是命令行中的第一个参数。
    
58. 
    
59. -----------------------------------------------------------------------------
    
60. 
    
61. 以下是可用关键字和它们的说明。方括号中列出的是默认值。
    
62. 
    
63. TTACH
    
64. 连接到现有作业。
    
65. 例如, ATTACH=job_name。
    
66. 
    
67. OMPRESSION
    
68. 减少转储文件大小。
    
69. 有效的关键字值为: ALL, DATA_ONLY, [METADATA_ONLY] 和 NONE。
    
70. 
    
71. ONTENT
    
72. 指定要卸载的数据。
    
73. 有效的关键字值为: [ALL], DATA_ONLY 和 METADATA_ONLY。
    
74. 
    
75. ATA_OPTIONS
    
76. 数据层选项标记。
    
77. 有效的关键字值为: XML_CLOBS。
    
78. 
    
79. IRECTORY
    
80. 用于转储文件和日志文件的目录对象。
    
81. 
    
82. UMPFILE
    
83. 指定目标转储文件名的列表 [expdat.dmp]。
    
84. 例如, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。
    
85. 
    
86. NCRYPTION
    
87. 加密某个转储文件的一部分或全部。
    
88. 有效的关键字值为: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY 和 NONE
    
89. 。
    
90. 
    
91. NCRYPTION_ALGORITHM
    
92. 指定加密的方式。
    
93. 有效的关键字值为: [AES128], AES192 和 AES256。
    
94. 
    
95. NCRYPTION_MODE
    
96. 生成加密密钥的方法。
    
97. 有效的关键字值为: DUAL, PASSWORD 和 [TRANSPARENT]。
    
98. 
    
99. NCRYPTION_PASSWORD
    
100. 用于在转储文件中创建加密数据的口令密钥。
     
101. 
     
102. STIMATE
     
103. 计算作业估计值。
     
104. 有效的关键字值为: [BLOCKS] 和 STATISTICS。
     
105. 
     
106. STIMATE_ONLY
     
107. 计算作业估计值而不执行导出。
     
108. 
     
109. XCLUDE
     
110. 排除特定对象类型。
     
111. 例如, EXCLUDE=SCHEMA:"='HR'"。
     
112. 
     
113. ILESIZE
     
114. 以字节为单位指定每个转储文件的大小。
     
115. 
     
116. LASHBACK_SCN
     
117. 用于重置会话快照的 SCN。
     
118. 
     
119. LASHBACK_TIME
     
120. 用于查找最接近的相应 SCN 值的时间。
     
121. 
     
122. ULL
     
123. 导出整个数据库 [N]。
     
124. 
     
125. ELP
     
126. 显示帮助消息 [N]。
     
127. 
     
128. NCLUDE
     
129. 包括特定对象类型。
     
130. 例如, INCLUDE=TABLE_DATA。
     
131. 
     
132. OB_NAME
     
133. 要创建的导出作业的名称。
     
134. 
     
135. OGFILE
     
136. 指定日志文件名 [export.log]。
     
137. 
     
138. ETWORK_LINK
     
139. 源系统的远程数据库链接的名称。
     
140. 
     
141. OLOGFILE
     
142. 不写入日志文件 [N]。
     
143. 
     
144. ARALLEL
     
145. 更改当前作业的活动 worker 的数量。
     
146. 
     
147. ARFILE
     
148. 指定参数文件名。
     
149. 
     
150. UERY
     
151. 用于导出表的子集的谓词子句。
     
152. 例如, QUERY=employees:"WHERE department_id > 10"。
     
153. 
     
154. EMAP_DATA
     
155. 指定数据转换函数。
     
156. 例如, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO。
     
157. 
     
158. EUSE_DUMPFILES
     
159. 覆盖目标转储文件 (如果文件存在) [N]。
     
160. 
     
161. AMPLE
     
162. 要导出的数据的百分比。
     
163. 
     
164. CHEMAS
     
165. 要导出的方案的列表 [登录方案]。
     
166. 
     
167. OURCE_EDITION
     
168. 用于提取元数据的版本。
     
169. 
     
170. TATUS
     
171. 监视作业状态的频率, 其中
     
172. 默认值 [0] 表示只要有新状态可用, 就立即显示新状态。
     
173. 
     
174. ABLES
     
175. 标识要导出的表的列表。
     
176. 例如, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995。
     
177. 
     
178. ABLESPACES
     
179. 标识要导出的表空间的列表。
     
180. 
     
181. RANSPORTABLE
     
182. 指定是否可以使用可传输方法。
     
183. 有效的关键字值为: ALWAYS 和 [NEVER]。
     
184. 
     
185. RANSPORT_FULL_CHECK
     
186. 验证所有表的存储段 [N]。
     
187. 
     
188. RANSPORT_TABLESPACES
     
189. 要从中卸载元数据的表空间的列表。
     
190. 
     
191. ERSION
     
192. 要导出的对象版本。
     
193. 有效的关键字值为: [COMPATIBLE], LATEST 或任何有效的数据库版本。
     
194. 
     
195. -----------------------------------------------------------------------------
     
196. 
     
197. 下列命令在交互模式下有效。
     
198. 注: 允许使用缩写。
     
199. 
     
200. DD_FILE
     
201. 将转储文件添加到转储文件集。
     
202. 
     
203. ONTINUE_CLIENT
     
204. 返回到事件记录模式。如果处于空闲状态, 将重新启动作业。
     
205. 
     
206. XIT_CLIENT
     
207. 退出客户机会话并使作业保持运行状态。
     
208. 
     
209. ILESIZE
     
210. 用于后续 ADD_FILE 命令的默认文件大小 (字节)。
     
211. 
     
212. ELP
     
213. 汇总交互命令。
     
214. 
     
215. ILL_JOB
     
216. 分离并删除作业。
     
217. 
     
218. ARALLEL
     
219. 更改当前作业的活动 worker 的数量。
     
220. 
     
221. EUSE_DUMPFILES
     
222. 覆盖目标转储文件 (如果文件存在) [N]。
     
223. 
     
224. TART_JOB
     
225. 启动或恢复当前作业。
     
226. 有效的关键字值为: SKIP_CURRENT。
     
227. 
     
228. TATUS
     
229. 监视作业状态的频率, 其中
     
230. 默认值 [0] 表示只要有新状态可用, 就立即显示新状态。
     
231. 
     
232. TOP_JOB
     
233. 按顺序关闭作业执行并退出客户机。
     
234. 有效的关键字值为: IMMEDIATE。
     
235. 
     
236. 
     
237. 
     
238. :\Users\Administrator>expdp system/oracle_4U directory=dirorcl dumpfile=tbszhs.
     
239. mp  TRANSPORT_TABLESPACES=tbszhs
     
240. 
     
241. xport: Release 11.2.0.1.0 - Production on 星期二 12月 19 19:17:20 2017
     
242. 
     
243. opyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
     
244. 
     
245. DE-28002: 操作产生了 ORACLE 错误 28002
     
246. RA-28002: 7 天之后口令将过期
     
247. 
     
248. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
     
249. ith the Partitioning, OLAP, Data Mining and Real Application Testing options
     
250. 启动 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=dirorcl
     
251. umpfile=tbszhs.dmp TRANSPORT_TABLESPACES=tbszhs
     
252. 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
     
253. 处理对象类型 TRANSPORTABLE_EXPORT/TABLE
     
254. 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
     
255. 已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
     
256. *****************************************************************************
     
257. YSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
     
258. C:\DATA\DIRORCL\TBSZHS.DMP
     
259. *****************************************************************************
     
260. 可传输表空间 TBSZHS 所需的数据文件:
     
261. C:\APP\ADMINISTRATOR\ORADATA\ORCL\TBSZHS.DBF
     
262. 作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 19:18:01 成功完成
     
263. 
     
264. 
     
265. :\Users\Administrator>rman target /
     
266. 
     
267. 恢复管理器: Release 11.2.0.1.0 - Production on 星期二 12月 19 19:18:24 2017
     
268. 
     
269. opyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
     
270. 
     
271. 连接到目标数据库: ORCL (DBID=1347195613)
     
272. 
     
273. MAN> convert tablespace tbszhs to platform 'Linux IA (64-bit)' format 'c:\tbszh
     
274. .dbf';
     
275. 
     
276. 启动 conversion at source 于 19-12月-17
     
277. 使用目标数据库控制文件替代恢复目录
     
278. 分配的通道: ORA_DISK_1
     
279. 通道 ORA_DISK_1: SID=20 设备类型=DISK
     
280. 通道 ORA_DISK_1: 启动数据文件转换
     
281. 输入数据文件: 文件号=00006 名称=C:\APP\ADMINISTRATOR\ORADATA\ORCL\TBSZHS.DBF
     
282. 已转换的数据文件 = C:\TBSZHS.DBF
     
283. 通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
     
284. 完成 conversion at source 于 19-12月-17
     
285. 
     
286. MAN> exit
     
287. 
     
288. 
     
289. 恢复管理器完成。
     
290. 
     
291. :\Users\Administrator>export ORACLE_SID=utforcl
     
292. export' 不是内部或外部命令，也不是可运行的程序
     
293. 或批处理文件。
     
294. 
     
295. :\Users\Administrator>set ORACLE_SID=utforcl
     
296. 
     
297. :\Users\Administrator>expdp system/oracle_4U directory=dirutforcl dumpfile=tbsu
     
298. f8_11g.dmp  TRANSPORT_TABLESPACES=tbsutf8_11g
     
299. 
     
300. xport: Release 11.2.0.1.0 - Production on 星期二 12月 19 19:30:14 2017
     
301. 
     
302. opyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
     
303. 
     
304. DE-28002: 操作产生了 ORACLE 错误 28002
     
305. RA-28002: 7 天之后口令将过期
     
306. 
     
307. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
     
308. ith the Partitioning, OLAP, Data Mining and Real Application Testing options
     
309. RA-39002: 操作无效
     
310. RA-39070: 无法打开日志文件。
     
311. RA-39087: 目录名 DIRUTFORCL 无效
     
312. 
     
313. 
     
314. 
     
315. :\Users\Administrator>expdp system/oracle_4U directory=dirutforcl dumpfile=tbsu
     
316. f8_11g.dmp  TRANSPORT_TABLESPACES=tbsutf8_11g
     
317. 
     
318. xport: Release 11.2.0.1.0 - Production on 星期二 12月 19 19:31:10 2017
     
319. 
     
320. opyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
     
321. 
     
322. DE-28002: 操作产生了 ORACLE 错误 28002
     
323. RA-28002: 7 天之后口令将过期
     
324. 
     
325. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
     
326. ith the Partitioning, OLAP, Data Mining and Real Application Testing options
     
327. 启动 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=dirutfor
     
328. l dumpfile=tbsutf8_11g.dmp TRANSPORT_TABLESPACES=tbsutf8_11g
     
329. 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
     
330. 处理对象类型 TRANSPORTABLE_EXPORT/TABLE
     
331. 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
     
332. 已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
     
333. *****************************************************************************
     
334. YSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
     
335. C:\DATA\DIRUTFORCL\TBSUTF8_11G.DMP
     
336. *****************************************************************************
     
337. 可传输表空间 TBSUTF8_11G 所需的数据文件:
     
338. C:\APP\ADMINISTRATOR\ORADATA\UTFORCL\TBSUTF8_11G01.DBF
     
339. 作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 19:31:46 成功完成
     
340. 
     
341. 
     
342. :\Users\Administrator>
     
343. 
     
344. ------------------------------------------------------------
     
345. Microsoft Windows [版本 6.1.7600]
     
346. 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
     
347. 
     
348. C:\Users\Administrator>rman target /
     
349. 
     
350. 恢复管理器: Release 11.2.0.1.0 - Production on 星期二 12月 19 19:31:28 2017
     
351. 
     
352. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
     
353. 
     
354. 连接到目标数据库: UTFORCL (DBID=454057935)
     
355. 
     
356. RMAN> convert tablespace tbszhs to platform '
     
357. 2>
     
358. 3>
     
359. 
     
360. RMAN-00571: ===========================================================
     
361. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
     
362. RMAN-00571: ===========================================================
     
363. RMAN-00558: 分析输入命令时出错
     
364. RMAN-01006: 在进行语法分析时发出出错信号
     
365. RMAN-02002: 到达意外的输入文件结尾
     
366. 
     
367. RMAN> convert tablespace tbszhs to platform 'Solaris[tm] OE (32-bit)' format 'c:
     
368. \data\tbsutf8_11g.dbf' ;
     
369. 
     
370. 启动 conversion at source 于 19-12月-17
     
371. 使用目标数据库控制文件替代恢复目录
     
372. 分配的通道: ORA_DISK_1
     
373. 通道 ORA_DISK_1: SID=142 设备类型=DISK
     
374. RMAN-00571: ===========================================================
     
375. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
     
376. RMAN-00571: ===========================================================
     
377. RMAN-03002: conversion at source 命令 (在 12/19/2017 19:32:42 上) 失败
     
378. RMAN-20202: 在恢复目录中未找到表空间
     
379. RMAN-06019: 无法转换表空间名称"TBSZHS"
     
380. 
     
381. RMAN> convert tablespace tbsutf8_11g to platform 'Solaris[tm] OE (32-bit)' forma
     
382. t 'c:\data\tbsutf8_11g.dbf' ;
     
383. 
     
384. 启动 conversion at source 于 19-12月-17
     
385. 使用通道 ORA_DISK_1
     
386. 通道 ORA_DISK_1: 启动数据文件转换
     
387. 输入数据文件: 文件号=00006 名称=C:\APP\ADMINISTRATOR\ORADATA\UTFORCL\TBSUTF8_11G
     
388. 01.DBF
     
389. 已转换的数据文件 = C:\DATA\TBSUTF8_11G.DBF
     
390. 通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
     
391. 完成 conversion at source 于 19-12月-17
     
392. 
     
393. RMAN>

复制代码

1. [oracle@station90 ~]$ rman target /
   
2. 
   
3. Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 19 19:54:09 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. connected to target database: ORCL (DBID=1343950367)
   
8. 
   
9. RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;
   
10. 
    
11. using target database control file instead of recovery catalog
    
12. old RMAN configuration parameters:
    
13. CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
    
14. new RMAN configuration parameters:
    
15. CONFIGURE DEFAULT DEVICE TYPE TO DISK;
    
16. new RMAN configuration parameters are successfully stored
    
17. 
    
18. RMAN> convert datafile '/u01/app/oracle/oradata/orcl/tbsutf8_11g.dbf' from platform 'Solaris[tm] OE (32-bit)' format '/u01/app/oracle/oradata/orcl/tbsutf8_11g2.dbf';
    
19. 
    
20. Starting conversion at target at 2017-12-19:19:54:18
    
21. allocated channel: ORA_DISK_1
    
22. channel ORA_DISK_1: SID=73 device type=DISK
    
23. allocated channel: ORA_DISK_2
    
24. channel ORA_DISK_2: SID=130 device type=DISK
    
25. allocated channel: ORA_DISK_3
    
26. channel ORA_DISK_3: SID=202 device type=DISK
    
27. allocated channel: ORA_DISK_4
    
28. channel ORA_DISK_4: SID=15 device type=DISK
    
29. allocated channel: ORA_DISK_5
    
30. channel ORA_DISK_5: SID=68 device type=DISK
    
31. allocated channel: ORA_DISK_6
    
32. channel ORA_DISK_6: SID=139 device type=DISK
    
33. allocated channel: ORA_DISK_7
    
34. channel ORA_DISK_7: SID=204 device type=DISK
    
35. allocated channel: ORA_DISK_8
    
36. channel ORA_DISK_8: SID=14 device type=DISK
    
37. channel ORA_DISK_1: starting datafile conversion
    
38. input file name=/u01/app/oracle/oradata/orcl/tbsutf8_11g.dbf
    
39. converted datafile=/u01/app/oracle/oradata/orcl/tbsutf8_11g2.dbf
    
40. channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
    
41. Finished conversion at target at 2017-12-19:19:54:21
    
42. 
    
43. RMAN>

复制代码

1. impdp system/oracle_4U directory=dir1 dumpfile=tbsutf8_11g.dmp  TRANSPORT_DATAFILES=/u01/app/oracle/oradata/orcl/tbsutf8_11g2.dbf
   
2. 
   
3.     Import: Release 11.2.0.3.0 - Production on Tue Dec 19 19:55:00 2017
   
4. 
   
5.     Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7.     UDI-28002: operation generated ORACLE error 28002
   
8.     ORA-28002: the password will expire within 7 days
   
9. 
   
10.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
11.     With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
12.     and Real Application Testing options
    
13.     Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
    
14.     Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=dir1 dumpfile=tbsutf8_11g.dmp TRANSPORT_DATAFILES=/u01/app/oracle/oradata/orcl/tbsutf8_11g2.dbf
    
15.     Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
    
16.     Processing object type TRANSPORTABLE_EXPORT/TABLE
    
17.     Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
    
18.     Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 19:55:02

复制代码

1.     impdp system/oracle_4U directory=dir1 dumpfile=tbsutf8_11g.dmp  TRANSPORT_DATAFILES=/u01/app/oracle/oradata/orcl/tbsutf8_11g2.dbf
   
2. 
   
3.     Import: Release 11.2.0.3.0 - Production on Tue Dec 19 19:55:00 2017
   
4. 
   
5.     Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7.     UDI-28002: operation generated ORACLE error 28002
   
8.     ORA-28002: the password will expire within 7 days
   
9. 
   
10.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
11.     With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
12.     and Real Application Testing options
    
13.     Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
    
14.     Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=dir1 dumpfile=tbsutf8_11g.dmp TRANSPORT_DATAFILES=/u01/app/oracle/oradata/orcl/tbsutf8_11g2.dbf
    
15.     Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
    
16.     Processing object type TRANSPORTABLE_EXPORT/TABLE
    
17.     Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
    
18.     Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 19:55:02
    

复制代码

1. [oracle@station90 dir1]$ impdp system/oracle_4U  directory=dir1 dumpfile=tbszhs2.dmp  remap_tablespace=tbszhs:example
   
2. 
   
3. Import: Release 11.2.0.3.0 - Production on Tue Dec 19 20:02:23 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. UDI-28002: operation generated ORACLE error 28002
   
8. ORA-28002: the password will expire within 7 days
   
9. 
   
10. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
11. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
12. and Real Application Testing options
    
13. Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    
14. Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dir1 dumpfile=tbszhs2.dmp remap_tablespace=tbszhs:example
    
15. Processing object type TABLE_EXPORT/TABLE/TABLE
    
16. Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    
17. . . imported "HR"."TZHS"                                     5 KB       1 rows
    
18. Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 20:02:25
    
19. 
    

复制代码

1. select * from dba_directories;
   
2. 
   
3. create directory dir1 as '/home/oracle/dir1';
   
4. 
   
5. select  *  from dba_tablespaces;
   
6. 
   
7. select  * from v$transportable_platform;
   
8. 
   
9. select  *  from dba_tablespaces;
   
10. 
    
11. alter tablespace tbsutf8_11g read write ;
    
12. 
    
13. select  * from hr.tutf8_11g;
    
14. 
    
15. insert into  hr.tutf8_11g values ( 1) ;
    
16. 
    
17. select  * from hr.tzhs;
    
18. 
    
19. select  tablespace_name from dba_tables t where t.table_name='TZHS';
    
20. 
    
21. create directory dir2 as '/home/oracle/dir2';

复制代码

1. [oracle@station90 dir1]$ expdp system/oracle_4U directory=dir1 dumpfile=dir1:dir1_%U.dmp,dir2:dir2_%U.dmp  parallel=4 full=y job_name=fulljob
   
2. 
   
3. Export: Release 11.2.0.3.0 - Production on Tue Dec 19 20:10:12 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. UDE-28002: operation generated ORACLE error 28002
   
8. ORA-28002: the password will expire within 7 days
   
9. 
   
10. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
11. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
12. and Real Application Testing options
    
13. Starting "SYSTEM"."FULLJOB":  system/******** directory=dir1 dumpfile=dir1:dir1_%U.dmp,dir2:dir2_%U.dmp parallel 4 full=y job_name=fulljob
    
14. Estimate in progress using BLOCKS method...
    
15. Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
    
16. Total estimation using BLOCKS method: 165.6 MB
    
17. Processing object type DATABASE_EXPORT/TABLESPACE
    
18. Processing object type DATABASE_EXPORT/PROFILE
    
19. Processing object type DATABASE_EXPORT/SYS_USER/USER
    
20. Processing object type DATABASE_EXPORT/SCHEMA/USER
    
21. Processing object type DATABASE_EXPORT/ROLE
    
22. Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
    
23. Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
    
24. Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
    
25. Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
    
26. Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
    
27. Processing object type DATABASE_EXPORT/RESOURCE_COST
    
28. Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
    
29. Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
    
30. Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
    
31. Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
    
32. Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
    
33. Processing object type DATABASE_EXPORT/CONTEXT
    
34. 
    
35. Export> help
    
36. ------------------------------------------------------------------------------
    
37. 
    
38. The following commands are valid while in interactive mode.
    
39. Note: abbreviations are allowed.
    
40. 
    
41. ADD_FILE
    
42. Add dumpfile to dumpfile set.
    
43. 
    
44. CONTINUE_CLIENT
    
45. Return to logging mode. Job will be restarted if idle.
    
46. 
    
47. EXIT_CLIENT
    
48. Quit client session and leave job running.
    
49. 
    
50. FILESIZE
    
51. Default filesize (bytes) for subsequent ADD_FILE commands.
    
52. 
    
53. HELP
    
54. Summarize interactive commands.
    
55. 
    
56. KILL_JOB
    
57. Detach and delete job.
    
58. 
    
59. PARALLEL
    
60. Change the number of active workers for current job.
    
61. 
    
62. REUSE_DUMPFILES
    
63. Overwrite destination dump file if it exists [N].
    
64. 
    
65. START_JOB
    
66. Start or resume current job.
    
67. Valid keyword values are: SKIP_CURRENT.
    
68. 
    
69. STATUS
    
70. Frequency (secs) job status is to be monitored where
    
71. the default [0] will show new status when available.
    
72. 
    
73. STOP_JOB
    
74. Orderly shutdown of job execution and exits the client.
    
75. Valid keyword values are: IMMEDIATE.
    
76. 
    
77. 
    
78. Export> stop_job
    
79. Are you sure you wish to stop this job ([yes]/no): yes
    
80. 
    
81. [oracle@station90 dir1]$ expdp system/oracle_4U attach=fulljob
    
82. 
    
83. Export: Release 11.2.0.3.0 - Production on Tue Dec 19 20:12:36 2017
    
84. 
    
85. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
86. 
    
87. UDE-28002: operation generated ORACLE error 28002
    
88. ORA-28002: the password will expire within 7 days
    
89. 
    
90. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
91. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
92. and Real Application Testing options
    
93. 
    
94. Job: FULLJOB
    
95.   Owner: SYSTEM                        
    
96.   Operation: EXPORT                        
    
97.   Creator Privs: TRUE                           
    
98.   GUID: 60B159517E2477ACE0535A00A8C0110D
    
99.   Start Time: Tuesday, 19 December, 2017 20:12:37
    
100.   Mode: FULL                           
     
101.   Instance: orcl
     
102.   Max Parallelism: 1
     
103.   EXPORT Job Parameters:
     
104.   Parameter Name      Parameter Value:
     
105.      CLIENT_COMMAND        system/******** directory=dir1 dumpfile=dir1:dir1_%U.dmp,dir2:dir2_%U.dmp parallel 4 full=y job_name=fulljob
     
106.   State: IDLING                        
     
107.   Bytes Processed: 0
     
108.   Current Parallelism: 1
     
109.   Job Error Count: 0
     
110.   Dump File: /home/oracle/dir1/dir1_%u.dmp
     
111.   Dump File: /home/oracle/dir1/dir1_01.dmp
     
112.     bytes written: 4,096
     
113.   Dump File: /home/oracle/dir2/dir2_01.dmp
     
114.     bytes written: 4,096
     
115.   Dump File: /home/oracle/dir2/dir2_%u.dmp
     
116.   Dump File: /home/oracle/dir1/parallel.dmp
     
117.     bytes written: 4,096
     
118.   Dump File: /home/oracle/dir1/4.dmp
     
119.     bytes written: 4,096
     
120.   
     
121. Worker 1 Status:
     
122.   Process Name: DW00
     
123.   State: UNDEFINED                     
     
124. 
     
125. Export> help
     
126. ------------------------------------------------------------------------------
     
127. 
     
128. The following commands are valid while in interactive mode.
     
129. Note: abbreviations are allowed.
     
130. 
     
131. ADD_FILE
     
132. Add dumpfile to dumpfile set.
     
133. 
     
134. CONTINUE_CLIENT
     
135. Return to logging mode. Job will be restarted if idle.
     
136. 
     
137. EXIT_CLIENT
     
138. Quit client session and leave job running.
     
139. 
     
140. FILESIZE
     
141. Default filesize (bytes) for subsequent ADD_FILE commands.
     
142. 
     
143. HELP
     
144. Summarize interactive commands.
     
145. 
     
146. KILL_JOB
     
147. Detach and delete job.
     
148. 
     
149. PARALLEL
     
150. Change the number of active workers for current job.
     
151. 
     
152. REUSE_DUMPFILES
     
153. Overwrite destination dump file if it exists [N].
     
154. 
     
155. START_JOB
     
156. Start or resume current job.
     
157. Valid keyword values are: SKIP_CURRENT.
     
158. 
     
159. STATUS
     
160. Frequency (secs) job status is to be monitored where
     
161. the default [0] will show new status when available.
     
162. 
     
163. STOP_JOB
     
164. Orderly shutdown of job execution and exits the client.
     
165. Valid keyword values are: IMMEDIATE.
     
166. 
     
167. 
     
168. Export> START_JOB
     
169. 
     
170. Export> status
     
171. 
     
172. Job: FULLJOB
     
173.   Operation: EXPORT                        
     
174.   Mode: FULL                           
     
175.   State: EXECUTING                     
     
176.   Bytes Processed: 0
     
177.   Current Parallelism: 1
     
178.   Job Error Count: 0
     
179.   Dump File: /home/oracle/dir1/dir1_%u.dmp
     
180.   Dump File: /home/oracle/dir1/dir1_01.dmp
     
181.     bytes written: 937,984
     
182.   Dump File: /home/oracle/dir2/dir2_01.dmp
     
183.     bytes written: 4,096
     
184.   Dump File: /home/oracle/dir2/dir2_%u.dmp
     
185.   Dump File: /home/oracle/dir1/parallel.dmp
     
186.     bytes written: 4,096
     
187.   Dump File: /home/oracle/dir1/4.dmp
     
188.     bytes written: 4,096
     
189.   
     
190. Worker 1 Status:
     
191.   Process Name: DW00
     
192.   State: EXECUTING                     
     
193.   Object Type: DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
     
194.   Completed Objects: 4
     
195.   Total Objects: 4
     
196.   Worker Parallelism: 1
     
197. 
     
198. Export> status
     
199. 
     
200. Job: FULLJOB
     
201.   Operation: EXPORT                        
     
202.   Mode: FULL                           
     
203.   State: EXECUTING                     
     
204.   Bytes Processed: 0
     
205.   Current Parallelism: 1
     
206.   Job Error Count: 0
     
207.   Dump File: /home/oracle/dir1/dir1_%u.dmp
     
208.   Dump File: /home/oracle/dir1/dir1_01.dmp
     
209.     bytes written: 937,984
     
210.   Dump File: /home/oracle/dir2/dir2_01.dmp
     
211.     bytes written: 4,096
     
212.   Dump File: /home/oracle/dir2/dir2_%u.dmp
     
213.   Dump File: /home/oracle/dir1/parallel.dmp
     
214.     bytes written: 4,096
     
215.   Dump File: /home/oracle/dir1/4.dmp
     
216.     bytes written: 4,096
     
217.   
     
218. Worker 1 Status:
     
219.   Process Name: DW00
     
220.   State: EXECUTING                     
     
221.   Object Schema: OLAPSYS
     
222.   Object Name: CWM2$STOREDDIMLVLTPLS
     
223.   Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE
     
224.   Completed Objects: 223
     
225.   Worker Parallelism: 1
     
226. 
     
227. Export>

复制代码

1.     [oracle@station90 dir1]$ expdp system/oracle_4U attach=fulljob
   
2. 
   
3.     Export: Release 11.2.0.3.0 - Production on Tue Dec 19 20:20:47 2017
   
4. 
   
5.     Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7.     UDE-28002: operation generated ORACLE error 28002
   
8.     ORA-28002: the password will expire within 7 days
   
9. 
   
10.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
11.     With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
12.     and Real Application Testing options
    
13. 
    
14.     Job: FULLJOB
    
15.       Owner: SYSTEM                        
    
16.       Operation: EXPORT                        
    
17.       Creator Privs: TRUE                           
    
18.       GUID: 60B175B5B0920533E0535A00A8C07D0D
    
19.       Start Time: Tuesday, 19 December, 2017 20:18:09
    
20.       Mode: FULL                           
    
21.       Instance: orcl
    
22.       Max Parallelism: 8
    
23.       EXPORT Job Parameters:
    
24.       Parameter Name      Parameter Value:
    
25.          CLIENT_COMMAND        system/******** directory=dir1 dumpfile=dir1:dir1_%U.dmp,dir2:dir2_%U.dmp parallel=8 full=y job_name=fulljob
    
26.       State: STOP PENDING                  
    
27.       Bytes Processed: 123,975,896
    
28.       Percent Done: 99
    
29.       Current Parallelism: 8
    
30.       Job Error Count: 0
    
31.       Dump File: /home/oracle/dir1/dir1_%u.dmp
    
32.       Dump File: /home/oracle/dir1/dir1_01.dmp
    
33.         bytes written: 15,011,840
    
34.       Dump File: /home/oracle/dir2/dir2_%u.dmp
    
35.       Dump File: /home/oracle/dir2/dir2_01.dmp
    
36.         bytes written: 25,686,016
    
37.       Dump File: /home/oracle/dir1/dir1_02.dmp
    
38.         bytes written: 13,152,256
    
39.       Dump File: /home/oracle/dir2/dir2_02.dmp
    
40.         bytes written: 31,580,160
    
41.       Dump File: /home/oracle/dir1/dir1_03.dmp
    
42.         bytes written: 21,139,456
    
43.       Dump File: /home/oracle/dir2/dir2_03.dmp
    
44.         bytes written: 12,341,248
    
45.       Dump File: /home/oracle/dir1/dir1_04.dmp
    
46.         bytes written: 6,176,768
    
47.       Dump File: /home/oracle/dir2/dir2_04.dmp
    
48.         bytes written: 4,096
    
49.       
    
50.     Worker 1 Status:
    
51.       Process Name: DW00
    
52.       State: WORK WAITING                  
    
53.       
    
54.     Worker 2 Status:
    
55.       Process Name: DW01
    
56.       State: WORK WAITING                  
    
57.       
    
58.     Worker 3 Status:
    
59.       Process Name: DW02
    
60.       State: WORK WAITING                  
    
61.       
    
62.     Worker 4 Status:
    
63.       Process Name: DW03
    
64.       State: WORK WAITING                  
    
65.       
    
66.     Worker 5 Status:
    
67.       Process Name: DW04
    
68.       State: WORK WAITING                  
    
69.       
    
70.     Worker 6 Status:
    
71.       Process Name: DW05
    
72.       State: WORK WAITING                  
    
73.       
    
74.     Worker 7 Status:
    
75.       Process Name: DW06
    
76.       State: WORK WAITING                  
    
77.       
    
78.     Worker 8 Status:
    
79.       Process Name: DW07
    
80.       State: WORK WAITING                  
    
81.       
    
82.     Worker 9 Status:
    
83.       Process Name: DW08
    
84.       State: WORK WAITING                  
    
85. 
    
86.     Export>
    

复制代码

带并行度的作业不要去ctl+c中断。
parfile选择：

1.     directory=dirutforcl
   
2.     dumpfile=schema.dmp  
   
3.     schemas=hr
   
4.     job_name=schemajob  
   
5.     exclude=table:"in ('T05217')", procedure:"in ('SECURE_DML')"  
   
6.     query='employees:where department_id=90'
   
7.     flashback_time='2017-12-19:20:43:36'

复制代码

1. [oracle@station90 ~]$ expdp system/oracle_4U parfile=parfile.ora
   
2. 
   
3. Export: Release 11.2.0.3.0 - Production on Tue Dec 19 20:47:21 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. UDE-28002: operation generated ORACLE error 28002
   
8. ORA-28002: the password will expire within 7 days
   
9. 
   
10. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
11. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
12. and Real Application Testing options
    
13. Starting "SYSTEM"."SCHEMAJOB":  system/******** parfile=parfile.ora
    
14. Estimate in progress using BLOCKS method...
    
15. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    
16. Total estimation using BLOCKS method: 960 KB
    
17. Processing object type SCHEMA_EXPORT/USER
    
18. Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    
19. Processing object type SCHEMA_EXPORT/ROLE_GRANT
    
20. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    
21. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    
22. Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
    
23. Processing object type SCHEMA_EXPORT/TABLE/TABLE
    
24. Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    
25. Processing object type SCHEMA_EXPORT/TABLE/COMMENT
    
26. Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
    
27. Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
    
28. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    
29. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    
30. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    
31. Processing object type SCHEMA_EXPORT/VIEW/VIEW
    
32. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    
33. Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
    
34. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    
35. . . exported "HR"."PLAN_TABLE1"                          22.73 KB       9 rows
    
36. . . exported "HR"."COUNTRIES"                            6.367 KB      25 rows
    
37. . . exported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
    
38. . . exported "HR"."DEPT2"                                    7 KB      27 rows
    
39. . . exported "HR"."EMP2"                                 16.80 KB     107 rows
    
40. . . exported "HR"."EMPLOYEES"                            9.445 KB       3 rows
    
41. . . exported "HR"."IOTEMP"                               7.976 KB       0 rows
    
42. . . exported "HR"."JOBS"                                 6.992 KB      19 rows
    
43. . . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
    
44. . . exported "HR"."LOCATIONS"                            8.273 KB      23 rows
    
45. . . exported "HR"."REGIONS"                              5.476 KB       4 rows
    
46. . . exported "HR"."T16949"                                   5 KB       1 rows
    
47. . . exported "HR"."TUTF8_11G"                            5.007 KB       1 rows
    
48. . . exported "HR"."TZHS"                                     5 KB       1 rows
    
49. Master table "SYSTEM"."SCHEMAJOB" successfully loaded/unloaded
    
50. ******************************************************************************
    
51. Dump file set for SYSTEM.SCHEMAJOB is:
    
52.   /home/oracle/dir1/schema.dmp
    
53. Job "SYSTEM"."SCHEMAJOB" successfully completed at 20:47:52
    
54. 
    
55. [oracle@station90 ~]$

复制代码

network link 建义用公共数据库链
源头用户user1目的地也要用user1进行：

1.     [oracle@station90 ~]$ expdp system/oracle_4U parfile=parfile.ora
   
2. 
   
3.     Export: Release 11.2.0.3.0 - Production on Tue Dec 19 20:47:21 2017
   
4. 
   
5.     Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7.     UDE-28002: operation generated ORACLE error 28002
   
8.     ORA-28002: the password will expire within 7 days
   
9. 
   
10.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
11.     With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
12.     and Real Application Testing options
    
13.     Starting "SYSTEM"."SCHEMAJOB":  system/******** parfile=parfile.ora
    
14.     Estimate in progress using BLOCKS method...
    
15.     Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    
16.     Total estimation using BLOCKS method: 960 KB
    
17.     Processing object type SCHEMA_EXPORT/USER
    
18.     Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    
19.     Processing object type SCHEMA_EXPORT/ROLE_GRANT
    
20.     Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    
21.     Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    
22.     Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
    
23.     Processing object type SCHEMA_EXPORT/TABLE/TABLE
    
24.     Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    
25.     Processing object type SCHEMA_EXPORT/TABLE/COMMENT
    
26.     Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
    
27.     Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
    
28.     Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    
29.     Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    
30.     Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    
31.     Processing object type SCHEMA_EXPORT/VIEW/VIEW
    
32.     Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    
33.     Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
    
34.     Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    
35.     . . exported "HR"."PLAN_TABLE1"                          22.73 KB       9 rows
    
36.     . . exported "HR"."COUNTRIES"                            6.367 KB      25 rows
    
37.     . . exported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
    
38.     . . exported "HR"."DEPT2"                                    7 KB      27 rows
    
39.     . . exported "HR"."EMP2"                                 16.80 KB     107 rows
    
40.     . . exported "HR"."EMPLOYEES"                            9.445 KB       3 rows
    
41.     . . exported "HR"."IOTEMP"                               7.976 KB       0 rows
    
42.     . . exported "HR"."JOBS"                                 6.992 KB      19 rows
    
43.     . . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
    
44.     . . exported "HR"."LOCATIONS"                            8.273 KB      23 rows
    
45.     . . exported "HR"."REGIONS"                              5.476 KB       4 rows
    
46.     . . exported "HR"."T16949"                                   5 KB       1 rows
    
47.     . . exported "HR"."TUTF8_11G"                            5.007 KB       1 rows
    
48.     . . exported "HR"."TZHS"                                     5 KB       1 rows
    
49.     Master table "SYSTEM"."SCHEMAJOB" successfully loaded/unloaded
    
50.     ******************************************************************************
    
51.     Dump file set for SYSTEM.SCHEMAJOB is:
    
52.       /home/oracle/dir1/schema.dmp
    
53.     Job "SYSTEM"."SCHEMAJOB" successfully completed at 20:47:52
    
54. 
    
55.     [oracle@station90 ~]$
    

复制代码