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

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