DBMS_DATAPUMP PL/SQL Example SCHEMA Import across a link with TABLESPACE REMAPS

I like to automate as much as possible, so to have to type in the remapping for a Tablespace every time is tiresome. Here’s a simple example with it automated. This example either uses a table to remap the tablespaces or defaults to a specific tablespace. You may also want to check to see if the tablespace exists and accept putting objects into the same tablespace, or you may want to remap with your mapping table. An option to do this is added as well.

Here’s the table:

CREATE TABLE oradba.remap_tablespace
(
    local_tablespace VARCHAR2(30 BYTE) NOT NULL,
    remote_tablespace VARCHAR2(30 BYTE) NOT NULL,
    insert_ts TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    insert_user VARCHAR2(30 BYTE) NOT NULL,
    update_ts TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    update_user VARCHAR2(30 BYTE) NOT NULL
)

/

-- Constraints for ORADBA.REMAP_TABLESPACE

ALTER TABLE oradba.remap_tablespace
ADD CONSTRAINT rempa_tablespace_uk1 UNIQUE (local_tablespace, remote_tablespace)
USING INDEX
  TABLESPACE  users
  )
/

-- Triggers for ORADBA.REMAP_TABLESPACE

CREATE OR REPLACE TRIGGER oradba.remap_tablespace_bi
    BEFORE INSERT
    ON oradba.remap_tablespace
    REFERENCING NEW AS new OLD AS old
    FOR EACH ROW
BEGIN
    :new.insert_ts := SYSDATE;
    :new.insert_user := USER;
    :new.update_ts := SYSDATE;
    :new.update_user := USER;

END;
/

CREATE OR REPLACE TRIGGER oradba.remap_tablespace_bu
    BEFORE UPDATE
    ON oradba.remap_tablespace
    REFERENCING NEW AS new OLD AS old
    FOR EACH ROW
BEGIN
    :new.update_ts := SYSDATE;
    :new.update_user := USER;
END;
/

-- Comments for ORADBA.REMAP_TABLESPACE

COMMENT ON TABLE oradba.remap_tablespace IS 'Cross Reference to remap remote tablespaces to local tablespaces.'
/
COMMENT ON COLUMN oradba.remap_tablespace.insert_ts IS 'Insert Timestamp'
/
COMMENT ON COLUMN oradba.remap_tablespace.insert_user IS 'Insert Userstamp'
/
COMMENT ON COLUMN oradba.remap_tablespace.local_tablespace IS 'Local_Tablespace'
/
COMMENT ON COLUMN oradba.remap_tablespace.remote_tablespace IS 'Remote Tablespace'
/
COMMENT ON COLUMN oradba.remap_tablespace.update_ts IS 'Update Timestamp'
/
COMMENT ON COLUMN oradba.remap_tablespace.update_user IS 'Update Userstamp'
/

Here are the functions and procedure, I usually have these in a package. I’m just adding them in as a tool set you can arrange/modify as you want. You may have to change the calls slightly, I’ve pulled these from 2 different packages.

Put this default in the spec:

vcg_default_tablespace constant oradba.remap_tablespace.local_tablespace%type := 'USERS'; -- package global default for the default tablespace

   --------------------------------------------
    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

———

FUNCTION fn_remap_tablespaces(p_remote_tablespace IN VARCHAR2, -- The "FROM" tablespace at the remote database
                                                                  p_bool_always_remap IN BOOLEAN DEFAULT FALSE, -- always remap and don't use the local name if it exists, but still use default if not found on remap
                                                                                                               p_local_tablespace IN OUT VARCHAR2 -- The "TO" tablespace here on the local database
                                                                                                                                                 )
        RETURN NUMBER
    IS
        /*


            Purpose: Does a lookup to determine a cross reference for tablespace changes
                     Uses the default tablespace name when no match is found.

            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/21/2012    Initial Build

        */
        -- Lookup remap value
        CURSOR c_get_tablespace_remaps(cv_remote_tablespace IN oradba.remap_tablespace.remote_tablespace%TYPE)
        IS
            SELECT a.local_tablespace to_tbs
              FROM oradba.remap_tablespace a
             WHERE remote_tablespace = cv_remote_tablespace;

        -- Check for local match
        CURSOR c_get_existing_tablespaces(cv_remote_tablespace IN oradba.remap_tablespace.remote_tablespace%TYPE)
        IS
            SELECT tablespace_name
              FROM dba_tablespaces
             WHERE tablespace_name = cv_remote_tablespace;

        v_line_no INTEGER := 0; -- debug line no
        v_sqlcode NUMBER; -- sqlcode error
    BEGIN
        v_line_no := 100; -- debug line no

        -- look up local tablespace - skip if this is true and use remap values instead
        IF NVL(p_bool_always_remap, FALSE) = FALSE
        THEN
            v_line_no := 200; -- debug line no

            OPEN c_get_existing_tablespaces(p_remote_tablespace);

            FETCH c_get_existing_tablespaces INTO p_local_tablespace;

            CLOSE c_get_existing_tablespaces;

        END IF;

        -- When not found then look for a remap value
        IF p_local_tablespace IS NULL
        THEN

            -- Look up a match for the local tablespace
            OPEN c_get_tablespace_remaps(p_remote_tablespace);

            FETCH c_get_tablespace_remaps INTO p_local_tablespace;

            CLOSE c_get_tablespace_remaps;

        END IF;

        -- set to default if still null
        IF p_local_tablespace IS NULL
        THEN
            p_local_tablespace := vcg_default_tablespace;
        END IF;

        RETURN 0;
    EXCEPTION
        WHEN OTHERS
        THEN
            v_sqlcode := SQLCODE;
            DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
            RETURN v_sqlcode;
    END fn_remap_tablespaces; -- Function fn_remap_tablespaces

—-

FUNCTION fn_tbs_remaps_for_link(p_remote_link   IN VARCHAR2, -- remote link - database data to be imported via link
                                    p_handle        IN NUMBER, -- handle opened for import
                                    p_schema_name   IN VARCHAR2, -- schema to be imported
                                    p_table_name    IN VARCHAR2 DEFAULT NULL -- table or object to be imported
                                                                            )
        RETURN NUMBER
    IS
        /*

            Purpose: This function will add the remap lines for tablespaces associated
                     with a given schema to be loaded over a database link

                     For example, if the remote tablespace is called DATA, and the only
                     tablespace locally is USERS.  Users can be defined as the default tablespace.

                     OR

                     Multiple tablespaces can be mapped in the local table on how the tablespaces should be remapped
                     (table name: REMAP_TABLESPACE)

            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/21/2012    Initial Build

        */

        TYPE t_cur_tablespaces IS REF CURSOR; --Create type as ref cursor

        vrefcur_tablespace t_cur_tablespaces; -- create reference cursor variable
        v_remote_tablespace oradba.remap_tablespace.remote_tablespace%TYPE; -- remote tablespace
        v_local_tablespace oradba.remap_tablespace.local_tablespace%TYPE; -- local tablespace
        v_select VARCHAR2(32767);
        v_return NUMBER; -- return value from function
        v_message VARCHAR2(32767); -- error message
        v_sqlcode NUMBER; -- sqlcode error
    BEGIN

        -- write select statement
        v_select := 'select distinct tablespace_name
                from dba_segments@' || p_remote_link || '
                where owner = ''' || p_schema_name || '''';

        -- Loop through tablespaces and write remap statements
        IF p_table_name IS NOT NULL
        THEN
            v_select := v_select || ' and segment_name = (''' || p_table_name || ''',''%'')';
        END IF;
        dbms_output.put_line(substr('Value of v_select='||to_char(v_select),1,255));
        -- open ref cursor
        OPEN vrefcur_tablespace FOR v_select;

        LOOP

            FETCH vrefcur_tablespace INTO v_remote_tablespace;
            EXIT WHEN vrefcur_tablespace%NOTFOUND;

            -- Get remap value
            v_return :=
                fn_remap_tablespaces(p_remote_tablespace => v_remote_tablespace, p_bool_always_remap => FALSE, p_local_tablespace => v_local_tablespace);

            IF v_return < 0
            THEN
                v_message := 'Failure in fn_remap_tablespaces, cannot remap remote tablespace:' || v_remote_tablespace;
                raise_application_error(-20001, v_message);
            END IF;

            DBMS_OUTPUT.put_line(SUBSTR('Value of v_remote_tablespace=' || TO_CHAR(v_remote_tablespace), 1, 255));
            DBMS_OUTPUT.put_line(SUBSTR('Value of v_local_tablespace=' || TO_CHAR(v_local_tablespace), 1, 255));
            DBMS_OUTPUT.put_line('-----------');
            DBMS_DATAPUMP.metadata_remap(p_handle,
                                         'REMAP_TABLESPACE',
                                         v_remote_tablespace,
                                         v_local_tablespace);


        END LOOP;

        RETURN 0;
    EXCEPTION
        WHEN OTHERS
        THEN
            v_sqlcode := SQLCODE;

            RETURN v_sqlcode;
    END fn_tbs_remaps_for_link; --function fn_tbs_remaps_for_link

——————–

PROCEDURE p_imp_schema_vialink2(p_schema_name IN VARCHAR2, -- schema to load
                                                             p_remote_link IN VARCHAR2 -- DB Link to source database
                                                                                      )
    IS
        /*


            Purpose: Import a schema - This version automatically remaps the tablespaces
            based on either a default value or a mapping in a table (see fn_tbs_remaps_for_link for details)

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

        */
        v_sid VARCHAR2(200) := fn_get_sid; -- sid for this databaes
        v_handle NUMBER; -- job handle
        v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
        v_start_time DATE; -- start time for log file
        v_logfile_name VARCHAR2(200); -- logfile name
        v_dumpfile_name VARCHAR2(200); -- logfile name
        v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
        v_degree INTEGER := 1; -- degree of parallelism
        v_line_no INTEGER := 0; -- debug line no
        v_sqlcode NUMBER; -- sqlcode
        v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
        vc_job_mode CONSTANT VARCHAR2(30) := 'SCHEMA'; -- Job mode
        vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
        v_message VARCHAR2(4000); -- error message to be raised
        v_job_name VARCHAR2(4000); -- job name created
        v_return NUMBER; -- function return error code
    BEGIN
        v_line_no := 100; -- debug line no

        -- Create the log and dumpfile names
        IF v_compatible = 'COMPATIBLE'
        THEN
            v_logfile_name := vc_operation || '_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
        ELSE
            v_logfile_name := vc_operation || '_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.log';
        END IF;

        v_line_no := 200; -- debug line no

        -- Open the job
        v_job_name := vc_operation || '_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI_SS');

        BEGIN
            v_handle :=
                DBMS_DATAPUMP.open(operation => vc_operation,
                                   job_mode => vc_job_mode,
                                   remote_link => p_remote_link,
                                   job_name => v_job_name,
                                   version => v_compatible);
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
                RAISE;
        END;

        v_line_no := 300; -- debug line no
        -- Add a logfile
        DBMS_DATAPUMP.add_file(handle => v_handle,
                               filename => v_logfile_name,
                               directory => v_default_dir,
                               filetype => DBMS_DATAPUMP.ku$_file_type_log_file);

        v_line_no := 500; -- debug line no
        -- Filter for schema
        DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
        v_line_no := 600; -- debug line no

        -- Remap tablespace if needed - using function
        v_return := oradba.util_imp_exp.fn_tbs_remaps_for_link(p_remote_link => p_remote_link, p_handle => v_handle, p_schema_name => p_schema_name);

        -- check for errors
        IF v_return < 0
        THEN
            v_message := 'Tablespace Remaps failed for remote link: ' || p_remote_link || ' Job Name:' || v_job_name;
            raise_application_error(-20001, v_message);
        END IF;

        v_line_no := 710; -- debug line no
        -- Start the job
        DBMS_DATAPUMP.start_job(handle => v_handle);
        v_line_no := 720; -- debug line no
        DBMS_DATAPUMP.detach(handle => v_handle);
        v_line_no := 800; -- debug line no
    EXCEPTION
        WHEN OTHERS
        THEN

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

            DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
            RAISE;
    END p_imp_schema_vialink2; -- procedure p_imp_schema_vialink2

Oracle Datapump PL/SQL Import Table Example

CREATE 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
/

--------------------------------------

PROCEDURE p_imp_table(p_schema_name IN VARCHAR2, -- schema for talbe
                                                p_table_name IN VARCHAR2, -- table to load
                                                                         p_dumpfile_name IN VARCHAR2 -- dumpfile_name to load
                                                                                                    )
IS
    /*


        Purpose: Import a table

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

    */
    v_sid VARCHAR2(200) := fn_get_sid; -- sid for this databaes
    v_handle NUMBER; -- job handle
    v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
    v_start_time DATE; -- start time for log file
    v_logfile_name VARCHAR2(200); -- logfile name
    v_dumpfile_name VARCHAR2(200); -- logfile name
    v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
    v_degree INTEGER := 1; -- degree of parallelism
    v_line_no INTEGER := 0; -- debug line no
    v_sqlcode NUMBER; -- sqlcode
    v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
    vc_job_mode CONSTANT VARCHAR2(30) := 'TABLE'; -- Job mode
    vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
BEGIN
    v_line_no := 100; -- debug line no

    -- Create the log and dumpfile names
    IF v_compatible = 'COMPATIBLE'
    THEN
        v_logfile_name := vc_operation || '_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
    ELSE
        v_logfile_name := vc_operation || '_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.log';
    END IF;

    v_line_no := 200; -- debug line no

    -- Open the job
    BEGIN
        v_handle :=
            DBMS_DATAPUMP.open(operation => vc_operation,
                               job_mode => vc_job_mode,
                               job_name => vc_operation || '_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI_SS'),
                               version => v_compatible);
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
            RAISE;
    END;

    v_line_no := 300; -- debug line no
    -- Add a logfile
    DBMS_DATAPUMP.add_file(handle => v_handle,
                           filename => v_logfile_name,
                           directory => v_default_dir,
                           filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
    v_line_no := 400; -- debug line no
    -- Add a datafile
    DBMS_DATAPUMP.add_file(handle => v_handle,
                           filename => p_dumpfile_name,
                           directory => v_default_dir,
                           filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
    v_line_no := 500; -- debug line no
    -- Filter for schema
    DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
    v_line_no := 600; -- debug line no
    -- Filter for table
    DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_name || '''');
    v_line_no := 710; -- debug line no
    -- Start the job
    DBMS_DATAPUMP.start_job(handle => v_handle);
    v_line_no := 720; -- debug line no
    DBMS_DATAPUMP.detach(handle => v_handle);
    v_line_no := 800; -- debug line no
EXCEPTION
    WHEN OTHERS
    THEN

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

        DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
        RAISE;
END p_imp_table; -- Procedure p_imp_table

Oracle Datapump PL/SQL Example of a Schema Import

CREATE 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
/

---------------------------

CREATE PROCEDURE p_imp_schema(p_schema_name IN VARCHAR2, -- schema to load
                                                 p_dumpfile_name IN VARCHAR2 -- dumpfile_name to load
                                                                            )
IS
    /*


        Purpose: Import a schema

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

    */
    v_sid VARCHAR2(200) := fn_get_sid; -- sid for this databaes
    v_handle NUMBER; -- job handle
    v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
    v_start_time DATE; -- start time for log file
    v_logfile_name VARCHAR2(200); -- logfile name
    v_dumpfile_name VARCHAR2(200); -- logfile name
    v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
    v_degree INTEGER := 1; -- degree of parallelism
    v_line_no INTEGER := 0; -- debug line no
    v_sqlcode NUMBER; -- sqlcode
    v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
    vc_job_mode CONSTANT VARCHAR2(30) := 'SCHEMA'; -- Job mode
    vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
BEGIN
    v_line_no := 100; -- debug line no

    -- Create the log and dumpfile names
    IF v_compatible = 'COMPATIBLE'
    THEN
        v_logfile_name := vc_operation || '_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
    ELSE
        v_logfile_name := vc_operation || '_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.log';
    END IF;

    v_line_no := 200; -- debug line no

    -- Open the job
    BEGIN
        v_handle :=
            DBMS_DATAPUMP.open(operation => vc_operation,
                               job_mode => vc_job_mode,
                               job_name => vc_operation || '_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI_SS'),
                               version => v_compatible);
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
            RAISE;
    END;

    v_line_no := 300; -- debug line no
    -- Add a logfile
    DBMS_DATAPUMP.add_file(handle => v_handle,
                           filename => v_logfile_name,
                           directory => v_default_dir,
                           filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
    v_line_no := 400; -- debug line no
    -- Add a datafile
    DBMS_DATAPUMP.add_file(handle => v_handle,
                           filename => p_dumpfile_name,
                           directory => v_default_dir,
                           filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
    v_line_no := 500; -- debug line no
    -- Filter for schema
    DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
    v_line_no := 710; -- debug line no
    -- Start the job
    DBMS_DATAPUMP.start_job(handle => v_handle);
    v_line_no := 720; -- debug line no
    DBMS_DATAPUMP.detach(handle => v_handle);
    v_line_no := 800; -- debug line no
EXCEPTION
    WHEN OTHERS
    THEN

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

        DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
        RAISE;
END p_imp_schema; -- Procedurep_imp_schema
/

Datapump PL/SQL Example for Job Descripton Monitoring

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

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
)

    PROCEDURE p_job_description(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_description   IN OUT BOOLEAN, -- true when Jobs status is true
                                p_operation              IN OUT VARCHAR2, -- operation
                                p_guid                   IN OUT VARCHAR, -- guid
                                p_job_mode               IN OUT VARCHAR2, -- job_mode
                                p_remote_link            IN OUT VARCHAR2, -- remote_link
                                p_platform               IN OUT VARCHAR2, -- platform
                                p_exp_platform           IN OUT VARCHAR2, -- exp_platform
                                p_global_name            IN OUT VARCHAR2, -- global_name
                                p_exp_global_name        IN OUT VARCHAR2, -- exp_global_name
                                p_instance               IN OUT VARCHAR2, -- instance
                                p_db_version             IN OUT VARCHAR2, -- db_version
                                p_exp_db_version         IN OUT VARCHAR2, -- exp_db_version
                                p_scn                    IN OUT NUMBER, -- scn
                                p_creator_privs          IN OUT VARCHAR2, -- creator_privs
                                p_start_time             IN OUT DATE, -- start_time
                                p_exp_start_time         IN OUT DATE, -- exp_start_time
                                p_term_reason            IN OUT NUMBER, -- term_reason
                                p_max_degree             IN OUT NUMBER, -- max_degree
                                p_log_file               IN OUT VARCHAR2, -- log_file
                                p_sql_file               IN OUT VARCHAR2, -- sql_file
                                p_parameters             IN OUT ku$_paramvalues1010 -- Parameters
                                                                                   )
    IS
        /*

                   Purpose: attaches and returns the 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_desc,
                                 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_desc) != 0
        THEN
            p_bool_job_description := TRUE;
        ELSE
            p_bool_job_description := FALSE;
        END IF;

        v_line_no := 400; -- debug line no

        IF p_bool_job_description
        THEN
            v_line_no := 401; -- debug line no
            -- Return data
            p_operation := v_status.job_description.operation; -- operation
            p_guid := v_status.job_description.guid; -- guid
            p_job_mode := v_status.job_description.job_mode; -- job_mode
            p_remote_link := v_status.job_description.remote_link; -- remote_link
            p_platform := v_status.job_description.platform; -- platform
            p_exp_platform := v_status.job_description.exp_platform; -- exp_platform
            p_global_name := v_status.job_description.global_name; -- global_name
            p_exp_global_name := v_status.job_description.exp_global_name; -- exp_global_name
            p_instance := v_status.job_description.instance; -- instance
            p_db_version := v_status.job_description.db_version; -- db_version
            p_exp_db_version := v_status.job_description.exp_db_version; -- exp_db_version
            p_scn := v_status.job_description.scn; -- scn
            p_creator_privs := v_status.job_description.creator_privs; -- creator_privs
            p_start_time := v_status.job_description.start_time; -- start_time
            p_exp_start_time := v_status.job_description.exp_start_time; -- exp_start_time
            p_term_reason := v_status.job_description.term_reason; -- term_reason
            p_max_degree := v_status.job_description.max_degree; -- max_degree
            p_log_file := v_status.job_description.log_file; -- log_file
            p_sql_file := v_status.job_description.sql_file; -- sql_file

            v_line_no := 500; -- debug line no
            -- Get parameters
            p_parameters := v_status.job_description.params; -- Parameter list

        END IF;

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

        v_line_no := 510; -- debug line no
    EXCEPTION
        WHEN OTHERS
        THEN
            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_description; -- end procedure p_job_description

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

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

Example Datapump PL/SQL Script to Export a Table with a Where Clause

Try this with the following parameters

Schema: SCOTT
TABLE: DEPT
TABLE_FILTER: where deptno = '10'

create PROCEDURE exp_tables_w_qfilter
(
p_schema_name IN VARCHAR2, -- schema name
p_table_name IN VARCHAR2, -- source tables
p_table_filter IN VARCHAR2 DEFAULT NULL -- table predicate to filter data (where clause)
)
    IS
        /*


            Purpose: Export tables

            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/15/2012    Initial Build

        */
        v_sid VARCHAR2(200) := fn_get_sid; -- sid for this databaes
        v_handle NUMBER; -- job handle
        v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
        v_start_time DATE; -- start time for log file
        v_logfile_name VARCHAR2(200); -- logfile name
        v_dumpfile_name VARCHAR2(200); -- logfile name
        v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
        v_line_no INTEGER := 0; -- debug line no
        v_sqlcode NUMBER; -- sqlcode
        v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
        vc_job_mode CONSTANT VARCHAR2(200) := 'TABLE'; -- Job mode
   
    BEGIN
        v_line_no := 100; -- debug line no

        -- Ceate the log and dumpfile names
        IF v_compatible = 'COMPATIBLE'
        THEN
            v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
            v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.dmp';
        ELSE
            v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.log';
            v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.dmp';
        END IF;

        v_line_no := 150; -- debug line no

        -- Open the job
        BEGIN
            v_handle :=
                DBMS_DATAPUMP.open(operation => 'EXPORT',
                                   job_mode => vc_job_mode,
                                   job_name => 'EXPORT_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI'),
                                   version => v_compatible);
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
                RAISE;
        END;

        v_line_no := 200; -- debug line no

        -- Add a logfile
        DBMS_DATAPUMP.add_file(handle => v_handle,
                               filename => v_logfile_name,
                               directory => v_default_dir,
                               filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
        v_line_no := 400; -- debug line no
        -- Add a datafile
        DBMS_DATAPUMP.add_file(handle => v_handle,
                               filename => 'dp' || '_%U_' || v_dumpfile_name,
                               directory => v_default_dir,
                               filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
        v_line_no := 500; -- debug line no


        -- Filter for the schemma
        DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
        v_line_no := 550; -- debug line no

        --Filter for the table
        DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_name || '''');

        v_line_no := 570; -- debug line no

        -- Add a subquery
        DBMS_DATAPUMP.data_filter(handle => v_handle, name => 'SUBQUERY', VALUE => p_table_filter);

        v_line_no := 600; -- debug line no

        -- Get the start time
        v_start_time := SYSDATE;

        -- Add a start time to the log file
        DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);

        v_line_no := 700; -- debug line no
        -- Start the job
        DBMS_DATAPUMP.start_job(handle => v_handle);
        DBMS_DATAPUMP.detach(handle => v_handle);
        v_line_no := 800; -- debug line no
    EXCEPTION
        WHEN OTHERS
        THEN

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

            DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
            RAISE;
    END exp_tables_w_qfilter; -- Procedure exp_tables_w_qfilter
    /

Oracle Datapump PL/SQL Export of a Single Schema Example

Example of exporting a single schema with Data Pump API:

--------------------------------------------
    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

    ---------------

    PROCEDURE exp_schema(p_schema_name IN VARCHAR2 -- schema name
                                                  )
    IS
        /*


            Purpose: Export schema

            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/15/2012    Initial Build

        */
        v_sid VARCHAR2(200) := fn_get_sid; -- sid for this databaes
        v_handle NUMBER; -- job handle
        v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
        v_start_time DATE; -- start time for log file
        v_logfile_name VARCHAR2(200); -- logfile name
        v_dumpfile_name VARCHAR2(200); -- logfile name
        v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
        v_degree INTEGER := 1; -- degree of parallelism
        v_line_no INTEGER := 0; -- debug line no
        v_sqlcode NUMBER; -- sqlcode
        v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
        vc_job_mode CONSTANT VARCHAR2(200) := 'SCHEMA'; -- Job mode
        v_filter_name VARCHAR2(30); -- filter name
        v_filter_value NUMBER; -- filter value
    BEGIN
        v_line_no := 100; -- debug line no

        IF v_compatible = 'COMPATIBLE'
        THEN
            v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
            v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.dmp';
        ELSE
            v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.log';
            v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.dmp';
        END IF;

        -- Open the job
        BEGIN
            v_handle :=
                DBMS_DATAPUMP.open(operation => 'EXPORT',
                                   job_mode => vc_job_mode,
                                   job_name => 'EXPORT_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI_SS'),
                                   version => v_compatible);
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
                RAISE;
        END;

        v_line_no := 200; -- debug line no

        -- Set the degree
        -- DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);

        -- Add a logfile
        DBMS_DATAPUMP.add_file(handle => v_handle,
                               filename => v_logfile_name,
                               directory => v_default_dir,
                               filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
        v_line_no := 400; -- debug line no
        -- Add a datafile
        DBMS_DATAPUMP.add_file(handle => v_handle,
                               filename => 'dp' || '_%U_' || v_dumpfile_name,
                               directory => v_default_dir,
                               filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
        v_line_no := 500; -- debug line no

        DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
        v_line_no := 600; -- debug line no

        -- Get the start time
        v_start_time := SYSDATE;

        v_line_no := 700; -- debug line no

        -- Add a start time to the log file
        DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);

        v_line_no := 710; -- debug line no
        -- Start the job
        DBMS_DATAPUMP.start_job(handle => v_handle);
        DBMS_DATAPUMP.detach(handle => v_handle);
        v_line_no := 800; -- debug line no
    EXCEPTION
        WHEN OTHERS
        THEN

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

            DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
            RAISE;
    END exp_schema; -- Procedure exp_schema

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

Oracle Datapump a Single Table Export Example

Example of datapump for a single table Export. These should go into a package.

Don’t forget to change the html versions of “less than” and “greater than” back to the correct characters.

Try varying the parallel parameter. I really like the SAMPLE parameter for sub-setting data.

    --------------------------------------------
    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

    -----------------------------------------------------

    PROCEDURE exp_table(p_schema_name    IN VARCHAR2, -- schema name
                        p_table_name     IN VARCHAR2, -- Table_name
                        p_filter_name    IN VARCHAR2 DEFAULT NULL, -- Options NAME (VALUES) 'INCLUDE_ROWS' (0=No, non-zero=Yes),
                        -- OR 'SAMPLE' (% of rows to sample) - specify a value below
                        p_filter_value   IN NUMBER DEFAULT NULL -- included value corresponding to filter name from p_filter_name
                                                               )
    IS
        /*


            Purpose: Export tables

            MODIFICATION HISTORY
            Person      Date        Comments
            ---------   ------      -------------------------------------------
            dcox        2/15/2012    Initial Build

        */
        v_sid VARCHAR2(200) := fn_get_sid; -- sid for this databaes
        v_handle NUMBER; -- job handle
        v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
        v_start_time DATE; -- start time for log file
        v_logfile_name VARCHAR2(200); -- logfile name
        v_dumpfile_name VARCHAR2(200); -- logfile name
        v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
        v_degree INTEGER := 1; -- degree of parallelism
        v_line_no INTEGER := 0; -- debug line no
        v_sqlcode NUMBER; -- sqlcode
        v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
        vc_job_mode CONSTANT VARCHAR2(200) := 'TABLE'; -- Job mode
        v_filter_name VARCHAR2(30) := p_filter_name; -- filter name
        v_filter_value NUMBER := p_filter_value; -- filter value
    BEGIN
        v_line_no := 100; -- debug line no

        IF v_compatible = 'COMPATIBLE'
        THEN
            v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.log';
            v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '.dmp';
        ELSE
            v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.log';
            v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '.dmp';
        END IF;

        -- Open the job
        BEGIN
            v_handle :=
                DBMS_DATAPUMP.open(operation => 'EXPORT',
                                   job_mode => vc_job_mode,
                                   job_name => 'EXPORT_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI'),
                                   version => v_compatible);
        EXCEPTION
            WHEN OTHERS
            THEN
                DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
                RAISE;
        END;

        v_line_no := 200; -- debug line no

        -- Set the degree
        -- DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);

        -- Add a logfile
        DBMS_DATAPUMP.add_file(handle => v_handle,
                               filename => v_logfile_name,
                               directory => v_default_dir,
                               filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
        v_line_no := 400; -- debug line no
        -- Add a datafile
        DBMS_DATAPUMP.add_file(handle => v_handle,
                               filename => 'dp' || '_%U_' || v_dumpfile_name,
                               directory => v_default_dir,
                               filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
        v_line_no := 500; -- debug line no

        DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
        v_line_no := 600; -- debug line no
        DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_name || '''');

        v_line_no := 700; -- debug line no
        -- filter for table name
        v_filter_name := NVL(v_filter_name, 'INCLUDE_ROWS');
        v_filter_value := NVL(v_filter_value, '100');
        DBMS_DATAPUMP.data_filter(handle => v_handle,
                                  name => v_filter_name,
                                  VALUE => v_filter_value,
                                  table_name => p_table_name,
                                  schema_name => p_schema_name);

        v_line_no := 600; -- debug line no
        -- Get the start time
        v_start_time := SYSDATE;
        -- Add a start time to the log file
        DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);

        v_line_no := 700; -- debug line no
        -- Start the job
        DBMS_DATAPUMP.start_job(handle => v_handle);
        DBMS_DATAPUMP.detach(handle => v_handle);
        v_line_no := 800; -- debug line no
    EXCEPTION
        WHEN OTHERS
        THEN

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

            DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
            RAISE;
    END exp_table; -- Procedure exp_table

Oracle Datapump via PL/SQL with Parallelism

I like the stuff we can do with the PL/SQL datapump package:

Don’t forget to change the html versions of “less than” and “greater than” back to the correct characters.

PROCEDURE        p_simple_export_full
    IS
        /*


            Purpose: Perform an full database export

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

        */
        v_handle NUMBER; -- job handle
        v_logfile_name VARCHAR2(200) := 'expdp_plsql_' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI') || '.log'; -- logfile name
        v_dumpfile_name VARCHAR2(200) := 'expdp_plsql_' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI') || '.dmp'; -- logfile name
        v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
        v_degree INTEGER := 1; -- degree of parallelism
    BEGIN
        -- Open the job
        v_handle :=
            DBMS_DATAPUMP.open(operation => 'EXPORT'
                              ,job_mode => 'FULL'
                              ,job_name => 'FULLEXPJOB'
                              ,version => 'COMPATIBLE');
        -- Set the degree
        DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);

        -- Add a logfile
        DBMS_DATAPUMP.add_file(handle => v_handle
                              ,filename => v_logfile_name
                              ,directory => v_default_dir
                              ,filetype => DBMS_DATAPUMP.ku$_file_type_log_file);

        -- Add a datafile
        DBMS_DATAPUMP.add_file(handle => v_handle
                              ,filename => 'dp' || '_%U_' || v_dumpfile_name
                              ,directory => v_default_dir
                              ,filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);

        -- Dbms_DataPump.Set_Parameter(handle => hand,
        --                               name => 'ESTIMATE',
        --                              value => 'STATISTICS');

        -- Start the job
        DBMS_DATAPUMP.start_job(v_handle);
    END p_simple_export_full;