Home » RDBMS Server » Server Utilities » Tracking a Data Pump job (11.1.0.7, Slaris 2.10)
Tracking a Data Pump job [message #541152] Sat, 28 January 2012 05:26
John Watson
Messages: 4683
Registered: January 2010
Location: Global Village
Senior Member
I'm running a Data Pump import, and tracking what it is doing. Right now, this is the last few lines of the log file:
. . imported "WK_TEST"."WK$STATISTIC"                    6.984 KB       0 rows
. . imported "WK_TEST"."WK$URL"                          12.30 KB       0 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/WITH_GRANT_OPTION/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

It is easy enough to find out what it is actually doing with this query:
  1* select sql_text from v$sql where sql_text like 'CREATE%' and users_executing=1
SQL> /

SQL_TEXT
--------------------------------------------------------------------------------
CREATE INDEX "PROV"."AK_NPF_RUN_EVENT" ON "PROV"."NPF_RUN_EVENT" ("NPF_RUN_ID")

but that only works because I guessed it is creating something. What I want to run is this,
select sql_text from v$sql join v$session using (sql_id) where program like '%DW%';
which should give me the exact SQL that is being run by my Data Pump worker right now, no matter what it is. This doesn't work until 12.1 because of "Null SQL_ID For Active Sessions In V$Session [ID 1343503.1]
" and "Bug 13068790 - the value of v$session.sql_id of active session is null [ID 13068790.8]"
Does anyone have a query that will let me see easily what the worker is doing, without having to guess?
Previous Topic: data import from production to test
Next Topic: export/import xml records in oracle 11gR2
Goto Forum:
  


Current Time: Thu Oct 23 03:26:09 CDT 2014

Total time taken to generate the page: 0.06621 seconds