Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for the ‘Oracle Utilities’ Category

ORA-39142: incompatible version number 3.1 in dump file “/u01/oracle/dpdump/expdat.dmp”

Posted by Hendry chinnapparaj on June 29, 2011

Problem

——————-

I was exporting data from a 11.2.0.2 database and trying to import the dump into a 10.2.0.3 database

While importing the dump into a lower version database , the foll errors were thrown :

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file “/u01/oracle/dpdump/expdat.dmp”
Solution

—————-

To avoid this error, while exporting from the 11g database , add the foll parameter –  version=10.2

exdp xxx/xxx parfile=abc.par version=10.2

Then import this dump into the 10.2.0.3 database and it goes through without any errors.

 

Posted in Oracle Utilities | Leave a Comment »

11g Datapump – how fast is it?

Posted by Hendry chinnapparaj on June 20, 2011

How long to export 1 billion (yes, not a million) rows (8 columns) from a single table in the DSE?

Pick a time and scroll down for the answer….

 

 

 

9 mins & 9 seconds…         –             1,047,869,238 rows

 

 

That’s about 1.9 million rows per second.

Exports out to 48Gb of dump files on disk.

This is with 12 concurrent processes, uncompressed. (3 core LPAR)

Interestingly, doing the same, exporting out to compressed dump files finishes in 10 mins 59secs (17% slower) but the compressed dumps total 5.39Gb (89% improvement)  Not surprisingly, we’ll be using the compressed option on the 11g upgraded MSE!

Export: Release 11.2.0.2.0 – Production on Mon Jun 20 12:06:25 2011

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

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Partitioning option

Starting “SYSTEM”.”AD_IVR_CALL_EVENTS”:  system/******** parfile=/u01/local/dbhk/scripts/par/AD_IVR_CALL_EVENTS.par

Estimate in progress using STATISTICS method…

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200808″  709.4 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200904″  637.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200701″  586.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201007″  578.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200907″  569.4 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200812″  569.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200412″  562.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200910″  557.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200807″  557.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200810″  555.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200912″  553.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201003″  550.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201012″  550.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201008″  546.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200607″  543.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200606″  542.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200406″  542.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200707″  540.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200903″  539.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200911″  538.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201103″  537.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200905″  536.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200603″  536.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200710″  536.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200801″  535.6 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200705″  530.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200503″  530.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200608″  529.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201006″  529.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201004″  528.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200703″  528.6 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200805″  528.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200706″  527.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200403″  525.6 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200605″  524.6 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200708″  524.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201005″  521.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200908″  521.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200512″  521.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200612″  520.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200508″  519.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200803″  519.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200809″  519.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200611″  518.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200906″  518.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200909″  517.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201011″  517.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201105″  517.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200610″  515.4 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200407″  514.7 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200804″  513.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201101″  509.6 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200404″  508.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201001″  508.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200511″  507.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201009″  506.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200601″  505.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201104″  503.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201010″  502.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200806″  500.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200501″  500.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200802″  499.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200704″  498.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200711″  496.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200409″  495.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200510″  494.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200604″  493.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200509″  491.7 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200811″  489.7 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200901″  488.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200405″  485.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200609″  483.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200504″  481.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201002″  480.5 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201102″  479.8 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200702″  477.4 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200709″  477.4 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200411″  477.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200712″  477.1 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200408″  477.0 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200602″  471.2 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200402″  469.6 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200902″  462.7 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200502″  451.7 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200507″  441.3 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200410″  439.4 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200505″  115.9 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201106″  26.43 MB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200506″      0 KB

.  estimated “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_MAXVAL”      0 KB

Total estimation using STATISTICS method: 43.72 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/AUDIT_OBJ

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200810″  604.1 MB 12939912 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201007″  632.0 MB 13474288 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200912″  606.3 MB 12892550 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200412″  616.2 MB 13085351 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200910″  606.3 MB 12986081 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200807″  616.8 MB 13260488 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200907″  631.7 MB 13574572 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200701″  641.0 MB 13655199 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200904″  694.5 MB 14862177 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200812″  634.7 MB 13592978 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201012″  605.0 MB 12850284 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201008″  599.1 MB 12726329 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200808″  771.8 MB 16531124 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201103″  590.2 MB 12535919 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201003″  603.8 MB 12834227 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200607″  591.3 MB 12653571 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200911″  586.8 MB 12556209 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200606″  591.7 MB 12651270 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200707″  589.2 MB 12603246 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200903″  600.9 MB 12868423 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200710″  595.2 MB 12773875 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200406″  592.2 MB 12637972 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200905″  597.8 MB 12800999 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200603″  586.0 MB 12493271 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200801″  594.8 MB 12746291 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200705″  579.4 MB 12348292 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200503″  582.5 MB 12377092 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201004″  579.7 MB 12321619 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201006″  578.0 MB 12330863 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200703″  576.7 MB 12292556 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200403″  575.4 MB 12247907 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200805″  574.6 MB 12309609 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200608″  578.8 MB 12340844 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200706″  575.1 MB 12293222 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200708″  573.1 MB 12214623 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200605″  573.2 MB 12220353 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200512″  569.2 MB 12137220 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201005″  571.1 MB 12138518 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200908″  580.8 MB 12437572 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201011″  567.9 MB 12065936 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201105″  566.1 MB 12021580 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200508″  566.9 MB 12093661 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201101″  559.8 MB 11891896 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200612″  569.4 MB 12133487 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200803″  575.9 MB 12336807 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200809″  564.6 MB 12093460 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200611″  566.9 MB 12083574 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200909″  575.3 MB 12320296 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200906″  574.5 MB 12347610 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200407″  560.6 MB 11976380 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201009″  555.4 MB 11801545 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201104″  554.2 MB 11769178 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200610″  562.4 MB 11988388 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201010″  550.3 MB 11696143 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200804″  571.6 MB 12245285 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200404″  556.5 MB 11840166 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201001″  556.8 MB 11841551 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200511″  554.1 MB 11818410 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200601″  552.7 MB 11788350 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200806″  554.6 MB 11926007 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200501″  548.2 MB 11651476 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200802″  555.8 MB 11909144 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200704″  546.2 MB 11640865 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200711″  551.5 MB 11823282 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200604″  539.8 MB 11507238 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200510″  538.6 MB 11502404 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201102″  526.4 MB 11181328 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200509″  535.7 MB 11438151 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200409″  543.6 MB 11565692 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200901″  543.1 MB 11636825 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200811″  532.6 MB 11412449 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200405″  530.1 MB 11287595 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201002″  526.7 MB 11198516 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200609″  528.3 MB 11264507 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200702″  520.8 MB 11101477 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200504″  528.6 MB 11240207 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_201106″  28.90 MB  615580 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200411″  522.1 MB 11119657 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200712″  531.0 MB 11379792 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200505″  127.2 MB 2705187 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200602″  514.8 MB 10981016 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200408″  523.2 MB 11144453 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200709″  521.1 MB 11109906 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200402″  514.0 MB 10943865 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200902″  503.3 MB 10782745 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200502″  495.0 MB 10525001 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200410″  480.5 MB 10239449 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200506″      0 KB       0 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_MAXVAL”      0 KB       0 rows

. . exported “MSE”.”AD_IVR_CALL_EVENTS”:”AD_IVREVE_200507″  480.7 MB 10284825 rows

Master table “SYSTEM”.”AD_IVR_CALL_EVENTS” successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.AD_IVR_CALL_EVENTS is:

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_01.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_02.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_03.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_04.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_05.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_06.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_07.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_08.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_09.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_10.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_11.dmp

/u02/oradata/export/MSEp/AD_IVR_CALL_EVENTS_12.dmp

Job “SYSTEM”.”AD_IVR_CALL_EVENTS” successfully completed at 12:15:34

Posted in Oracle Utilities | Leave a Comment »

DATAPUMP – KILL JOBS ONLY USING THE DATAPUMP TOOL

Posted by Hendry chinnapparaj on June 20, 2011

Killing an expdp or impdp process from the oslevel will not stop the job – so expdp or impdp jobs must be killed from the database level.

Example ?

Start an export job…..

Check the dba_datapump_jobs view…

SQL> select job_name,owner_name,state from dba_datapump_jobs;

JOB_NAME                               OWNER_NAME                     STATE
—————————— —————————— ——————————
SYS_EXPORT_FULL_10             OPS$ORACLE                     EXECUTING
SYS_EXPORT_FULL_01             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_05             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_03             OPS$ORACLE                     NOT RUNNING

SQL> exit

Kill the job from the oslevel …

cmi1d:/u03/oradata/export/cmi1d> ps -ef |grep expdp

oracle 26214652 18350150   0 13:27:38  pts/2  0:00 expdp / DUMPFILE=expdp_full_cmi1d_170611_132738_%U.dmp LOGFILE=expdp_full_cmi1d_170611_132738.log PARFILE=/u01/local/dbhk/scripts/par/expdp_full.par
cmi1d:/u03/oradata/export/cmi1d> kill -9 26214652
cmi1d:/u03/oradata/export/cmi1d>

Now check database again – a few minutes after the oslevel kill.

SQL> select job_name,owner_name,state from dba_datapump_jobs;

JOB_NAME                                      OWNER_NAME                     STATE
—————————— —————————— ——————————
SYS_EXPORT_FULL_10             OPS$ORACLE                     EXECUTING

View still shows the job is executing.

If you look at the export dump area – it is clear the job is still executing in the background.

-rw-r—–    1 oracle   oinstall  877879296 Jun 17  13:43 expdp_full_cmi1d_170611_132738_02.dmp
-rw-r—–    1 oracle   oinstall 1382027264 Jun 17 13:43 expdp_full_cmi1d_170611_132738_01.dmp

Therefore it is not a good idea to kill a datapump job only from the oslevel – the job will not stop.

This is the way to do it.

First attach to the job via expdp….

The job name is SYS_EXPORT_FULL_10  started by OPS$ORACLE – in the view above.

To attach to a job, you must connect as the owner of the job….therefore I am using the ” / ” for connection as ops$oracle

expdp / attach=SYS_EXPORT_FULL_10

Export: Release 11.2.0.2.0 – Production on Fri Jun 17 13:45:10 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_10
Owner: OPS$ORACLE
Operation: EXPORT
Creator Privs: TRUE
GUID: A5E2A4EB79A500E4E0430A35C8EC00E4
Start Time: Friday, 17 June, 2011 13:27:42
Mode: FULL
Instance: cmi1d
Max Parallelism: 2
EXPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        /******** DUMPFILE=expdp_full_cmi1d_170611_132738_%U.dmp LOGFILE=expdp_full_cmi1d_170611_132738.log PARFILE=/u01/local/dbhk/scripts/par/expdp_full.par
COMPRESSION           ALL
INCLUDE_METADATA      1
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 2
Job Error Count: 0
Dump File: /u02/oradata/export/expdp_full_cmi1d_170611_132738_%u.dmp
size: 8,589,934,592
Dump File: /u02/oradata/export/expdp_full_cmi1d_170611_132738_01.dmp
size: 8,589,934,592
bytes written: 4,096
Dump File: /u02/oradata/export/expdp_full_cmi1d_170611_132738_02.dmp
size: 8,589,934,592
bytes written: 15,687,680

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: EBS_GL_ARCHIVE
Object Name: GL_ACCT_BALANCES
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 894
Completed Rows: 81,746,354
Worker Parallelism: 1

Worker 2 Status:
Process Name: DW01
State: EXECUTING
Object Schema: EBS_GL_ARCHIVE
Object Name: GL_JOURNAL_LINES
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 894
Completed Rows: 91,359,564
Worker Parallelism: 1

Export>

Enter KILL_JOB to  kill job..

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes
.
Check again –

select job_name,owner_name,state from dba_datapump_jobs;

JOB_NAME                       OWNER_NAME                     STATE
—————————— —————————— ——————————
SYS_EXPORT_FULL_01             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_05             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_03             OPS$ORACLE                     NOT RUNNING

Posted in Oracle Utilities | Leave a Comment »