expdp query参数的使用
程序员文章站
2022-03-12 18:22:30
...
-- 建表
create table t_exp(id number, day date);-- 插入数据
begin
for i in 1..300
loop
insert into t_exp values(i,sysdate+i);
end loop;
commit;
end;-- exp导出2019年以后的，
create or replace directory dump as '/u02/dump/';
grant read,write on directory dump to public;-- parfile
vi par_t_exp
directory=dump
dumpfile=t_exp_%U.dmp
logfile=t_exp.log
tables=zbb.t_exp
QUERY=t_exp:"WHERE day > to_date('2018-12-31','YYYY-MM-DD')"-- expdp
expdp \'/ as sysdba\' parfile=par_t_exp-- 导出过程
[[email protected] dump]$ expdp \'/ as sysdba\' parfile=par_t_exp
Export: Release 11.2.0.4.0 - Production on Tue Jul 31 09:01:10 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" parfile=par_t_exp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ZBB"."T_EXP" 7.703 KB 148 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u02/dump/t_exp_01.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jul 31 09:02:24 2018 elapsed 0 00:00:48
[[email protected] dump]$-- 查看结果 。结果是148，和导出的结果相符
select count(*) from t_exp where day > to_date('2018-12-31','YYYY-MM-DD');
[email protected]>select count(*) from t_exp where day > to_date('2018-12-31','YYYY-MM-DD');
COUNT(*)
----------
148
[email protected]>-- 导入进去试试
create table t_imp_test(id number, day date); --其实可以不用创建表。 会自动创建表
impdp \'/ as sysdba\' directory=dump dumpfile=t_exp_01.dmp logfile=t_imp_test.log remap_table=zbb.t_exp:t_imp_test -- 注意这里remap ，remap后的不要schema，直接remap表 ，否则remap后的表是 zbb.zbb.t_imp_test .比如下面的例子
[[email protected] dump]$ impdp \'/ as sysdba\' directory=dump dumpfile=t_exp_01.dmp logfile=t_imp_test.log remap_table=zbb.t_exp:zbb.t_imp_test
Import: Release 11.2.0.4.0 - Production on Tue Jul 31 09:10:42 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=dump dumpfile=t_exp_01.dmp logfile=t_imp_test.log remap_table=zbb.t_exp:zbb.t_imp_test
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ZBB"."ZBB.T_IMP_TEST" 7.703 KB 148 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Tue Jul 31 09:11:02 2018 elapsed 0 00:00:14
[[email protected] dump]$
impdp \'/ as sysdba\' directory=dump dumpfile=t_exp_01.dmp logfile=t_imp_test.log remap_table=zbb.t_exp:t_imp_test1 -- 自动创建remap后的表
[[email protected] dump]$ impdp \'/ as sysdba\' directory=dump dumpfile=t_exp_01.dmp logfile=t_imp_test.log remap_table=zbb.t_exp:t_imp_test1
Import: Release 11.2.0.4.0 - Production on Tue Jul 31 09:18:21 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=dump dumpfile=t_exp_01.dmp logfile=t_imp_test.log remap_table=zbb.t_exp:t_imp_test1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ZBB"."T_IMP_TEST1" 7.703 KB 148 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Tue Jul 31 09:18:38 2018 elapsed 0 00:00:10
[[email protected] dump]$-- 数据比对
-- 源端
select * from zbb.t_exp WHERE day > to_date('2018-12-31','YYYY-MM-DD')
[email protected]>select * from zbb.t_exp WHERE day > to_date('2018-12-31','YYYY-MM-DD');
ID DAY
---------- ---------
153 31-DEC-18
154 01-JAN-19
155 02-JAN-19
156 03-JAN-19
157 04-JAN-19
158 05-JAN-19
159 06-JAN-19
160 07-JAN-19
161 08-JAN-19
162 09-JAN-19
163 10-JAN-19
ID DAY
---------- ---------
164 11-JAN-19
165 12-JAN-19
166 13-JAN-19
167 14-JAN-19
168 15-JAN-19
169 16-JAN-19
170 17-JAN-19
171 18-JAN-19
172 19-JAN-19
173 20-JAN-19
174 21-JAN-19
ID DAY
---------- ---------
175 22-JAN-19
176 23-JAN-19
177 24-JAN-19
178 25-JAN-19
179 26-JAN-19
180 27-JAN-19
181 28-JAN-19
182 29-JAN-19
183 30-JAN-19
184 31-JAN-19
185 01-FEB-19
ID DAY
---------- ---------
186 02-FEB-19
187 03-FEB-19
188 04-FEB-19
189 05-FEB-19
190 06-FEB-19
191 07-FEB-19
192 08-FEB-19
193 09-FEB-19
194 10-FEB-19
195 11-FEB-19
196 12-FEB-19
ID DAY
---------- ---------
197 13-FEB-19
198 14-FEB-19
199 15-FEB-19
200 16-FEB-19
201 17-FEB-19
202 18-FEB-19
203 19-FEB-19
204 20-FEB-19
205 21-FEB-19
206 22-FEB-19
207 23-FEB-19
ID DAY
---------- ---------
208 24-FEB-19
209 25-FEB-19
210 26-FEB-19
211 27-FEB-19
212 28-FEB-19
213 01-MAR-19
214 02-MAR-19
215 03-MAR-19
216 04-MAR-19
217 05-MAR-19
218 06-MAR-19
ID DAY
---------- ---------
219 07-MAR-19
220 08-MAR-19
221 09-MAR-19
222 10-MAR-19
223 11-MAR-19
224 12-MAR-19
225 13-MAR-19
226 14-MAR-19
227 15-MAR-19
228 16-MAR-19
229 17-MAR-19
ID DAY
---------- ---------
230 18-MAR-19
231 19-MAR-19
232 20-MAR-19
233 21-MAR-19
234 22-MAR-19
235 23-MAR-19
236 24-MAR-19
237 25-MAR-19
238 26-MAR-19
239 27-MAR-19
240 28-MAR-19
ID DAY
---------- ---------
241 29-MAR-19
242 30-MAR-19
243 31-MAR-19
244 01-APR-19
245 02-APR-19
246 03-APR-19
247 04-APR-19
248 05-APR-19
249 06-APR-19
250 07-APR-19
251 08-APR-19
ID DAY
---------- ---------
252 09-APR-19
253 10-APR-19
254 11-APR-19
255 12-APR-19
256 13-APR-19
257 14-APR-19
258 15-APR-19
259 16-APR-19
260 17-APR-19
261 18-APR-19
262 19-APR-19
ID DAY
---------- ---------
263 20-APR-19
264 21-APR-19
265 22-APR-19
266 23-APR-19
267 24-APR-19
268 25-APR-19
269 26-APR-19
270 27-APR-19
271 28-APR-19
272 29-APR-19
273 30-APR-19
ID DAY
---------- ---------
274 01-MAY-19
275 02-MAY-19
276 03-MAY-19
277 04-MAY-19
278 05-MAY-19
279 06-MAY-19
280 07-MAY-19
281 08-MAY-19
282 09-MAY-19
283 10-MAY-19
284 11-MAY-19
ID DAY
---------- ---------
285 12-MAY-19
286 13-MAY-19
287 14-MAY-19
288 15-MAY-19
289 16-MAY-19
290 17-MAY-19
291 18-MAY-19
292 19-MAY-19
293 20-MAY-19
294 21-MAY-19
295 22-MAY-19
ID DAY
---------- ---------
296 23-MAY-19
297 24-MAY-19
298 25-MAY-19
299 26-MAY-19
300 27-MAY-19
148 rows selected.
[email protected]>--目标端
[email protected]>select * from zbb.t_imp_test1;
ID DAY
---------- ---------
153 31-DEC-18
154 01-JAN-19
155 02-JAN-19
156 03-JAN-19
157 04-JAN-19
158 05-JAN-19
159 06-JAN-19
160 07-JAN-19
161 08-JAN-19
162 09-JAN-19
163 10-JAN-19
ID DAY
---------- ---------
164 11-JAN-19
165 12-JAN-19
166 13-JAN-19
167 14-JAN-19
168 15-JAN-19
169 16-JAN-19
170 17-JAN-19
171 18-JAN-19
172 19-JAN-19
173 20-JAN-19
174 21-JAN-19
ID DAY
---------- ---------
175 22-JAN-19
176 23-JAN-19
177 24-JAN-19
178 25-JAN-19
179 26-JAN-19
180 27-JAN-19
181 28-JAN-19
182 29-JAN-19
183 30-JAN-19
184 31-JAN-19
185 01-FEB-19
ID DAY
---------- ---------
186 02-FEB-19
187 03-FEB-19
188 04-FEB-19
189 05-FEB-19
190 06-FEB-19
191 07-FEB-19
192 08-FEB-19
193 09-FEB-19
194 10-FEB-19
195 11-FEB-19
196 12-FEB-19
ID DAY
---------- ---------
197 13-FEB-19
198 14-FEB-19
199 15-FEB-19
200 16-FEB-19
201 17-FEB-19
202 18-FEB-19
203 19-FEB-19
204 20-FEB-19
205 21-FEB-19
206 22-FEB-19
207 23-FEB-19
ID DAY
---------- ---------
208 24-FEB-19
209 25-FEB-19
210 26-FEB-19
211 27-FEB-19
212 28-FEB-19
213 01-MAR-19
214 02-MAR-19
215 03-MAR-19
216 04-MAR-19
217 05-MAR-19
218 06-MAR-19
ID DAY
---------- ---------
219 07-MAR-19
220 08-MAR-19
221 09-MAR-19
222 10-MAR-19
223 11-MAR-19
224 12-MAR-19
225 13-MAR-19
226 14-MAR-19
227 15-MAR-19
228 16-MAR-19
229 17-MAR-19
ID DAY
---------- ---------
230 18-MAR-19
231 19-MAR-19
232 20-MAR-19
233 21-MAR-19
234 22-MAR-19
235 23-MAR-19
236 24-MAR-19
237 25-MAR-19
238 26-MAR-19
239 27-MAR-19
240 28-MAR-19
ID DAY
---------- ---------
241 29-MAR-19
242 30-MAR-19
243 31-MAR-19
244 01-APR-19
245 02-APR-19
246 03-APR-19
247 04-APR-19
248 05-APR-19
249 06-APR-19
250 07-APR-19
251 08-APR-19
ID DAY
---------- ---------
252 09-APR-19
253 10-APR-19
254 11-APR-19
255 12-APR-19
256 13-APR-19
257 14-APR-19
258 15-APR-19
259 16-APR-19
260 17-APR-19
261 18-APR-19
262 19-APR-19
ID DAY
---------- ---------
263 20-APR-19
264 21-APR-19
265 22-APR-19
266 23-APR-19
267 24-APR-19
268 25-APR-19
269 26-APR-19
270 27-APR-19
271 28-APR-19
272 29-APR-19
273 30-APR-19
ID DAY
---------- ---------
274 01-MAY-19
275 02-MAY-19
276 03-MAY-19
277 04-MAY-19
278 05-MAY-19
279 06-MAY-19
280 07-MAY-19
281 08-MAY-19
282 09-MAY-19
283 10-MAY-19
284 11-MAY-19
ID DAY
---------- ---------
285 12-MAY-19
286 13-MAY-19
287 14-MAY-19
288 15-MAY-19
289 16-MAY-19
290 17-MAY-19
291 18-MAY-19
292 19-MAY-19
293 20-MAY-19
294 21-MAY-19
295 22-MAY-19
ID DAY
---------- ---------
296 23-MAY-19
297 24-MAY-19
298 25-MAY-19
299 26-MAY-19
300 27-MAY-19
148 rows selected.
[email protected]>-- 结论，数据是一致的 。
END
上一篇： docker常用命令(一)
下一篇： oracle中expdp的使用
推荐阅读