Datapump PL/SQL Example for Job Status Monitoring

There are 5 pieces in the ku$_STATUS1120 object, this one covers only the job_status.

TYPE ku$_Status1120 IS OBJECT
(
mask NUMBER, -- Status types present
wip ku$_LogEntry1010, -- Work in progress
job_description ku$_JobDesc1020, -- Complete job description
job_status ku$_JobStatus1120,-- Detailed job status
error ku$_LogEntry1010 -- Multi-level context errors
)

I’m sure you some of you could take this and remove all, but percent complete. Although some of you will want it all.

create 
PROCEDURE p_job_status(p_job_name          IN     dba_datapump_jobs.job_name%TYPE, -- Job Name - from dba_datapump_job
                           p_job_owner         IN     dba_datapump_jobs.owner_name%TYPE, -- job_owner from dba_datapump_job
                           p_bool_job_status   IN OUT BOOLEAN, -- true when job status has data
                           p_operation         IN OUT VARCHAR2, -- operation
                           p_job_mode          IN OUT VARCHAR2, -- job_mode
                           p_bytes_processed   IN OUT NUMBER, -- bytes_processed
                           p_total_bytes       IN OUT NUMBER, -- total_bytes
                           p_percent_done      IN OUT NUMBER, -- percent_done
                           p_degree            IN OUT NUMBER, -- degree
                           p_error_count       IN OUT NUMBER, -- error_count
                           p_state             IN OUT VARCHAR2, -- state
                           p_phase             IN OUT NUMBER, -- phase
                           p_restart_count     IN OUT NUMBER, -- restart_count
                           p_Worker_Status_list in out ku$_WorkerStatusList1120, -- worker status list
                           p_file_list in out  ku$_DumpFileSet1010 -- file list
                                                            )
    IS
        /*

                   Purpose: attaches and returns the job status structure
                   Parses the bit

                   MODIFICATION HISTORY
                   Person      Date        Comments
                   ---------   ------      -------------------------------------------
                   dcox        2/20/2013    Initial Build

                   */

        v_line_no INTEGER := 0; -- debug line no
        v_handle NUMBER; -- job handle
        v_status ku$_status; -- job status
        v_sqlcode NUMBER; -- sqlcode
        vc_indefinate_wait_timeout CONSTANT INTEGER := -1; -- specify an indefinate wait
        v_job_state VARCHAR2(40); -- Job state from job_status procedure
    -- vc_timeout constant integer := 5; -- specify a xx seconds(s) timeout to wait for status ( Uncomment and use if necessary)
    BEGIN
        v_line_no := 100; -- debug line no
        -- Attach to a datapump session
        v_handle := DBMS_DATAPUMP.attach(job_name => p_job_name, job_owner => p_job_owner);
        v_line_no := 200; -- debug line no

        -- Monitor status
        DBMS_DATAPUMP.get_status(handle => v_handle,
                                 mask => DBMS_DATAPUMP.ku$_status_job_status,
                                 timeout => vc_indefinate_wait_timeout,
                                 job_state => v_job_state,
                                 status => v_status);

        -- Decode the bit masks
        -- job status
        IF BITAND(v_status.mask, DBMS_DATAPUMP.ku$_status_job_status) != 0
        THEN
            p_bool_job_status := TRUE;
        ELSE
            p_bool_job_status := FALSE;
        END IF;

        -- Detach from handle
        BEGIN
            DBMS_DATAPUMP.detach(handle => v_handle);
        EXCEPTION
            WHEN OTHERS
            THEN
                NULL;
        END;

        IF p_bool_job_status
        THEN
            -- Return data
            p_operation := v_status.job_status.operation; -- operation
            p_job_mode := v_status.job_status.job_mode; -- job_mode
            p_bytes_processed := v_status.job_status.bytes_processed; -- bytes_processed
            p_total_bytes := v_status.job_status.total_bytes; -- total_bytes
            p_percent_done := v_status.job_status.percent_done; -- percent_done
            p_degree := v_status.job_status.degree; -- degree
            p_error_count := v_status.job_status.error_count; -- error_count
            p_state := v_status.job_status.state; -- state
            p_phase := v_status.job_status.phase; -- phase
            p_restart_count := v_status.job_status.restart_count; -- restart_count

            -- Get Worker Status List
            p_Worker_Status_list := v_status.job_status.worker_status_list; -- worker status list

            -- Get dump Files List
            p_file_list := v_status.job_status.files; -- files list


        END IF;
    EXCEPTION
        WHEN OTHERS
        THEN
            v_sqlcode := sqlcode;
            dbms_output.put_line(substr('Value of v_sqlcode='||to_char(v_sqlcode),1,255));
            DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));

            BEGIN
                DBMS_DATAPUMP.detach(handle => v_handle);
            EXCEPTION
                WHEN OTHERS
                THEN
                    NULL;
            END;

            RAISE;

    END p_job_status; -- end procedure p_job_status
    /

FUNCTION fn_get_sid
        RETURN VARCHAR2
    IS
        /*


            Purpose: Get's  the SID for the database

            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        10/19/2012    Initial Build

        */
        v_sid VARCHAR2(1000); -- sid for the database
    BEGIN

        SELECT name INTO v_sid FROM v$database;

        RETURN v_sid;
    EXCEPTION
        WHEN OTHERS
        THEN
            RETURN NULL;
    END fn_get_sid; -- Function fn_get_sid

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s