VIEW: SYS.DBA_DATAPUMP_JOBS

Source

View Text - Preformatted

SELECT  j.owner_name, j.job_name, j.operation, j.job_mode, j.state,
                j.workers,
                NVL((SELECT    COUNT(*)
                     FROM      SYS.GV$DATAPUMP_SESSION s
                     WHERE     j.job_id = s.job_id AND
                               s.type = 'DBMS_DATAPUMP'
                     GROUP BY  s.job_id), 0),
                NVL((SELECT    COUNT(*)
                     FROM      SYS.GV$DATAPUMP_SESSION s
                     WHERE     j.job_id = s.job_id
                     GROUP BY  s.job_id), 0)
        FROM    SYS.GV$DATAPUMP_JOB j
        WHERE   j.msg_ctrl_queue IS NOT NULL
      UNION ALL                               /* Not Running - Master Tables */
        SELECT u.name, o.name,
               SUBSTR (c.comment$, 24, 30), SUBSTR (c.comment$, 55, 30),
               'NOT RUNNING', 0, 0, 0
        FROM sys.obj$ o, sys.user$ u, sys.com$ c
        WHERE SUBSTR (c.comment$, 1, 22) = 'Data Pump Master Table' AND
              RTRIM (SUBSTR (c.comment$, 24, 30)) IN
                ('EXPORT','ESTIMATE','IMPORT','SQL_FILE','NETWORK') AND
              RTRIM (SUBSTR (c.comment$, 55, 30)) IN
                ('FULL','SCHEMA','TABLE','TABLESPACE','TRANSPORTABLE') AND
              o.obj# = c.obj# AND
              o.type# = 2 AND
              BITAND(o.flags, 128) <> 128 AND
              u.user# = o.owner# AND
              NOT EXISTS (SELECT 1
                          FROM   SYS.GV$DATAPUMP_JOB
                          WHERE  owner_name = u.name AND
                                 job_name = o.name)

View Text - HTML Formatted

SELECT J.OWNER_NAME
, J.JOB_NAME
, J.OPERATION
, J.JOB_MODE
, J.STATE
, J.WORKERS
, NVL((SELECT COUNT(*)
FROM SYS.GV$DATAPUMP_SESSION S
WHERE J.JOB_ID = S.JOB_ID AND S.TYPE = 'DBMS_DATAPUMP' GROUP BY S.JOB_ID)
, 0)
, NVL((SELECT COUNT(*)
FROM SYS.GV$DATAPUMP_SESSION S
WHERE J.JOB_ID = S.JOB_ID GROUP BY S.JOB_ID)
, 0)
FROM SYS.GV$DATAPUMP_JOB J
WHERE J.MSG_CTRL_QUEUE IS NOT NULL UNION ALL /* NOT RUNNING - MASTER TABLES */ SELECT U.NAME
, O.NAME
, SUBSTR (C.COMMENT$
, 24
, 30)
, SUBSTR (C.COMMENT$
, 55
, 30)
, 'NOT RUNNING'
, 0
, 0
, 0
FROM SYS.OBJ$ O
, SYS.USER$ U
, SYS.COM$ C
WHERE SUBSTR (C.COMMENT$
, 1
, 22) = 'DATA PUMP MASTER TABLE' AND RTRIM (SUBSTR (C.COMMENT$
, 24
, 30)) IN ('EXPORT'
, 'ESTIMATE'
, 'IMPORT'
, 'SQL_FILE'
, 'NETWORK') AND RTRIM (SUBSTR (C.COMMENT$
, 55
, 30)) IN ('FULL'
, 'SCHEMA'
, 'TABLE'
, 'TABLESPACE'
, 'TRANSPORTABLE') AND O.OBJ# = C.OBJ# AND O.TYPE# = 2 AND BITAND(O.FLAGS
, 128) <> 128 AND U.USER# = O.OWNER# AND NOT EXISTS (SELECT 1
FROM SYS.GV$DATAPUMP_JOB
WHERE OWNER_NAME = U.NAME AND JOB_NAME = O.NAME)