oracle datapump - ghdrako/doc_snipets GitHub Wiki

https://support.dbagenesis.com/oracle-database/oracle-data-pump-expdp-impdp

Prepare

mkdir -p /u02/dp_exp_dir
create directory datapump as '/u02/dp_exp_dir';
grant read,write on directory datapump to scott; 
select * from dba_directories;

Export/Import table

expdp directory=datapump dumpfile=emp_bkp.dmp logfile=emp_bkp.log tables='SCOTT.EMP'

impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP'
impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:HR'  -- to other schema
impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_schema='SCOTT:HR' remap_tablespace='USERS:MYTBS' -- to other tablespace
impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' remap_table='SCOTT.EMP:HR.EMPLOYEE' -- rename table during import
impdp directory=datapump dumpfile=emp_bkp.dmp logfile=imp_emp.log tables='EMP' content=DATA_ONLY -- import only data to existing table

EKSPORT/Import SCHEMA

expdp directory=datapump dumpfile=scott_bkp.dmp logfile=scott_bkp.log schemas='SCOTT'
expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

impdp system/oracle DIRECTORY=dmpdir DUMPFILE=scott.dmp
impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema='SCOTT:SCOTT'  -- import do tego samego schematu
impdp directory=datapump dumpfile=scott_bkp.dmp logfile=imp_schema.log remap_schema='SCOTT:HR'     -- import do innego schematu

Import/Export Rows

expdp directory=datapump dumpfile=emprows_bkp.dmp logfile=emprows_bkp.log tables='SCOTT.EMP' query=\"where deptno=10\"

impdp directory=datapump dumpfile=emprows_bkp.dmp logfile=imp_emprows.log tables='SCOTT.EMP'

Import/Export Full Database

expdp directory=datapump dumpfile=fullprod.dmp logfile=fullprod.log full=y

Import full database

-- On source
SQL> select name from v$tablespace;

-- On target
SQL> select name from v$tablespace;

-- Create missing tablespaces on target
-- Make sure target tablespace has enough free space
-- Drop all non-oracle schemas (done during refresh)
-- DROP USER <username> CASCADE;

$ impdp directory=datapump dumpfile=fullprod.dmp logfile=imp_fullprod.log full=y

Data Pump Import Over Network

Use dblink with Oracle datapump to move data from one oracle database to another.
Note: when you use datapump with dblink, there are no dumpfiles created on source. The data is transferred from one database to another over network

Network in source system:

Add source database TNS entry into tnsnames.ora of the target database

devdb =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = devdb)
 )
)

On target, we need to create a database link using the TNS entry created above

create database link SOURCE_DB connect to scott identified by tiger  using 'devdb';

import source schema on target database via db link. Run below command on target database to start import

create or replace directory MY_DUMP_DIR as '/u01/dump_files';

impdp directory=MY_DUMP_DIR LOGFILE=dblink_transfer.log network_link=SOURCE_DB remap_schema=scott:hr

To import multiple schemas, make sure to use a DBA user (sys) to perform this action

impdp sys directory=MY_DUMP_DIR LOGFILE=dblink_transfer.log network_link=SOURCE_DB schemas=IJS,scott,hr

Example

SQL> CREATE DATABASE LINK hr_source
     2  CONNECT TO hr IDENTIFIED BY manager
     3  USING 'MITP';

impdp hr/hr directory=hr_exp network_link=hr_source logfile=hrimp_link.log schemas=hr

Data Pump Performance Tuning

You can always use DIRECT=y parameter to perform faster exports and imports. You can also use PARALLEL parameter to start multiple export and import process for faster performance.
Make sure to use %U with the dumpfile name so multiple dumpfiles can be read/write simultaneously

expdp directory=DUMP_DIR DIRECT=y dumpfile=SCOTT_%U.dmp logfile=expdp_SCOTT.log schemas=SCOTT parallel=4
impdp directory=DUMP_DIR DIRECT=y dumpfile=SCOTT_%U.dmp logfile=impdp_SCOTT.log schemas=SCOTT parallel=4

create database link old_scott connect to scott identified by tiger  using 'orcl.oracle.com';

in target system:

impdp new_scott/tiger DIRECTORY=dmpdir NETWORK_LINK=old_scott remap_schema=scott:new_scott

PL/SQL API

The Data Pump command-line clients expdp and impdp use the DBMS_DATAPUMP and DBMS_METADATA PL/SQL packages.

DECLARE
  hand NUMBER;
BEGIN
  hand := Dbms_DataPump.Open(operation => 'EXPORT',
                              job_mode => 'FULL',
                              job_name => 'FULLEXPJOB',
                               version => 'COMPATIBLE');
  Dbms_DataPump.Add_File(handle => hand,
                       filename => 'expdp_plsql.log',
                      directory => 'DMPDIR',
                       filetype => 3);
  Dbms_DataPump.Add_File(handle => hand,
                       filename => 'expdp_plsql.dmp',
                      directory => 'DMPDIR',
                       filetype => 1);
  -- Dbms_DataPump.Set_Parameter(handle => hand,
  --                               name => 'ESTIMATE',
  --                              value => 'STATISTICS');
  Dbms_DataPump.Start_Job(hand);
END;
/

Odczytywanie informacji z pliku dumpu

CREATE OR REPLACE PROCEDURE dumpfile_details (
  verzeichnis VARCHAR2 DEFAULT 'DATA_PUMP_DIR',’
  dateiname VARCHAR2 DEFAULT 'EXPDAT.DMP') 
AS 
  v_dir          all_directories.directory_path%type := '?';

v_filetype     NUMBER;
  v_info_table   sys.ku$_dumpfile_info;
  type vartype   IS VARRAY(23) OF VARCHAR2(2048);
  v_values       vartype := vartype();
  v_dateiversion VARCHAR2(15);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Verzeichnis: '||verzeichnis);
  DBMS_OUTPUT.PUT_LINE('Dateiname:   '||dateiname);
  SELECT directory_path INTO v_dir FROM all_directories
    WHERE directory_name = verzeichnis OR directory_name = UPPER(verzeichnis);
  DBMS_OUTPUT.PUT_LINE('Pfad:        '||v_dir);
  DBMS_DATAPUMP.GET_DUMPFILE_INFO(
    filename=>dateiname, directory=>UPPER(verzeichnis),
    info_table=>v_info_table, filetype=>v_filetype);
  v_values.EXTEND(23);
  FOR x IN 1 .. 23 LOOP
    BEGIN
      SELECT value INTO v_values(x) FROM TABLE(v_info_table)
        WHERE item_code = x;
    EXCEPTION WHEN OTHERS THEN v_values(x) := '';
    END;
  END LOOP;
  IF v_filetype = 1 THEN
    DBMS_OUTPUT.PUT_LINE('Dateityp:    Data Pump Export');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
    BEGIN
      SELECT v_values(5) || ' (' || nls_charset_name(v_values(5)) || ')' 
        INTO v_values(5) FROM dual;
    EXCEPTION WHEN OTHERS THEN null;
    END;  
    SELECT SUBSTR(v_values(1), 1, 15) INTO v_dateiversion FROM dual;
       SELECT DECODE(v_values(1),
                    '0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
                    '1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
                    '2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
                    '3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
                    '4.1', '4.1 (Oracle12c Release 1: 12.1.0.x)',
        v_values(1)) INTO v_values(1) FROM dual;
      SELECT DECODE(v_values(2), '0', '0 (Nein)', '1', '1 (Ja)',
        v_values(2)) INTO v_values(2) FROM dual;
      SELECT DECODE(v_values(14), '0', '0 (Nein)', '1', '1 (Ja)',
v_filetype     NUMBER;
  v_info_table   sys.ku$_dumpfile_info;
  type vartype   IS VARRAY(23) OF VARCHAR2(2048);
  v_values       vartype := vartype();
  v_dateiversion VARCHAR2(15);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Verzeichnis: '||verzeichnis);
  DBMS_OUTPUT.PUT_LINE('Dateiname:   '||dateiname);
  SELECT directory_path INTO v_dir FROM all_directories
    WHERE directory_name = verzeichnis OR directory_name = UPPER(verzeichnis);
  DBMS_OUTPUT.PUT_LINE('Pfad:        '||v_dir);
  DBMS_DATAPUMP.GET_DUMPFILE_INFO(
    filename=>dateiname, directory=>UPPER(verzeichnis),
    info_table=>v_info_table, filetype=>v_filetype);
  v_values.EXTEND(23);
  FOR x IN 1 .. 23 LOOP
    BEGIN
      SELECT value INTO v_values(x) FROM TABLE(v_info_table)
        WHERE item_code = x;
    EXCEPTION WHEN OTHERS THEN v_values(x) := '';
    END;
  END LOOP;
  IF v_filetype = 1 THEN
    DBMS_OUTPUT.PUT_LINE('Dateityp:    Data Pump Export');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------');
    BEGIN
      SELECT v_values(5) || ' (' || nls_charset_name(v_values(5)) || ')' 
        INTO v_values(5) FROM dual;
    EXCEPTION WHEN OTHERS THEN null;
    END;  
    SELECT SUBSTR(v_values(1), 1, 15) INTO v_dateiversion FROM dual;
       SELECT DECODE(v_values(1),
                    '0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
                    '1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
                    '2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
                    '3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
                    '4.1', '4.1 (Oracle12c Release 1: 12.1.0.x)',
        v_values(1)) INTO v_values(1) FROM dual;
      SELECT DECODE(v_values(2), '0', '0 (Nein)', '1', '1 (Ja)',
        v_values(2)) INTO v_values(2) FROM dual;
      SELECT DECODE(v_values(14), '0', '0 (Nein)', '1', '1 (Ja)',        v_values(14)) INTO v_values(14) FROM dual;
      SELECT DECODE(v_values(18), '0', '0 (Nein)', '1', '1 (Ja)',
        v_values(18)) INTO v_values(18) FROM dual;
      SELECT DECODE(v_values(19), '0', '0 (Nein)', '1', '1 (Ja)',
        v_values(19)) INTO v_values(19) FROM dual;
      SELECT DECODE(v_values(20), '0', '0 (Nein)', '1', '1 (Ja)',
        v_values(20)) INTO v_values(20) FROM dual;
      SELECT DECODE(v_values(21), '0', '0 (Nein)', '1', '1 (Ja)',
        v_values(21)) INTO v_values(21) FROM dual;
      SELECT DECODE(v_values(22),
                    '1', '1 (Unknown)',
                    '2', '2 (None)',
                    '3', '3 (Password)',
                    '4', '4 (Password and Wallet)',
                    '5', '5 (Wallet)',
        v_values(22)) INTO v_values(22) FROM dual;
      SELECT DECODE(v_values(23),
                    '2', '2 (None)',
                    '3', '3 (Basic)',
                    '4', '4 (Low)',
                    '5', '5 (Medium)',
                    '6', '6 (High)',
        v_values(23)) INTO v_values(23) FROM dual; 
      DBMS_OUTPUT.PUT_LINE('Database Job-Version..........: 
' || v_values(15));
      DBMS_OUTPUT.PUT_LINE('Interne Dateiversion..........: 
' || v_values(1));
      DBMS_OUTPUT.PUT_LINE('Erstellungsdatum..............: 
' || v_values(6));
      DBMS_OUTPUT.PUT_LINE('Dateinummer im Set............: 
' || v_values(4));
      DBMS_OUTPUT.PUT_LINE('Master im Dumpfile............: 
' || v_values(2));
      DBMS_OUTPUT.PUT_LINE('Betriebssystem der Datenbank..: 
' || v_values(9));
      DBMS_OUTPUT.PUT_LINE('Zeichensatz der Datenbank.....: 
' || v_values(5));
      DBMS_OUTPUT.PUT_LINE('Sprache.......................: 
' || v_values(11));
      DBMS_OUTPUT.PUT_LINE('Jobname.......................: 
' || v_values(8));
      DBMS_OUTPUT.PUT_LINE('GUID..........................: 
' || v_values(3));
      DBMS_OUTPUT.PUT_LINE('Blockgröße der Datei (Bytes)..: 
' || v_values(12));
      DBMS_OUTPUT.PUT_LINE('Metadaten komprimiert.........: 
' || v_values(14));
      IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
DBMS_OUTPUT.PUT_LINE('Daten 
komprimiert.............: ' || v_values(18));
        DBMS_OUTPUT.PUT_LINE('Metadaten 
verschüsselt........: ' || v_values(19));
        DBMS_OUTPUT.PUT_LINE('Tabellen 
verschlüsselt........: ' || v_values(20));
        DBMS_OUTPUT.PUT_LINE('Spalten 
verschlüsselt.........: ' || v_values(21));
      END IF;
  ELSE
    DBMS_OUTPUT.PUT_LINE('Dateityp:    Dateityp unbekannt');
  END IF;
END;
/

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> BEGIN
  2    dumpfile_details('EXP', 'ssb.dmp');
  3  END;
  4  /
Verzeichnis: EXP
Dateiname:   ssb.dmp
Pfad:        c:\temp
Dateityp:    Data Pump Export
--------------------------------------------
Database Job-Version..........: 19.00.00.00.00
Interne Dateiversion..........: 5.1
Erstellungsdatum..............: Tue May 12 11:46:47 2020
Dateinummer im Set............: 1
Master im Dumpfile............: 1 (Ja)
Betriebssystem der Datenbank..: IBMPC/WIN_NT64-9.1.0
Zeichensatz der Datenbank.....: 873 (AL32UTF8)
Sprache.......................: AL32UTF8
Jobname.......................: "SYS"."SYS_EXPORT_SCHEMA_02"
GUID..........................: 
26E14BFEA39D4FCB8679CC169360331F
Blockgröße der Datei (Bytes)..: 4096
Metadaten komprimiert.........: 1 (Ja)
Daten komprimiert.............: 0 (Nein)
Metadaten verschüsselt........: 0 (Nein)
Tabellen verschlüsselt........: 0 (Nein)
Spalten verschlüsselt.........: 0 (Nein)
PL/SQL-Prozedur erfolgreich abgeschlossen.

oracle datapump - ghdrako/doc_snipets GitHub Wiki

Prepare

Export/Import table

EKSPORT/Import SCHEMA

Import/Export Rows

Import/Export Full Database

Data Pump Import Over Network

Network in source system:

Data Pump Performance Tuning

PL/SQL API

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️