Datapump with Database Link examples

Create user "DEMO_2019_DANTEST_CHRIS"

12:51:35 SQL> spool create_user.log
12:51:35 SQL> CREATE USER "DEMO_2019_DANTEST_CHRIS"  PROFILE "DEFAULT"
12:51:35   2      IDENTIFIED BY "7dmwK!2U" DEFAULT TABLESPACE "DATA_DEMO2019DANTESTCHRIS"
12:51:35   3      TEMPORARY TABLESPACE "TEMP"
12:51:35   4      ACCOUNT UNLOCK;
User created.
Elapsed: 00:00:00.83
12:51:36 SQL> GRANT CREATE PROCEDURE TO "DEMO_2019_DANTEST_CHRIS";
Grant succeeded.
Elapsed: 00:00:00.01
12:51:36 SQL> GRANT CREATE SEQUENCE TO "DEMO_2019_DANTEST_CHRIS";
Grant succeeded.
Elapsed: 00:00:00.00
12:51:36 SQL> GRANT CREATE SESSION TO "DEMO_2019_DANTEST_CHRIS";
Grant succeeded.
Elapsed: 00:00:00.00
12:51:36 SQL> GRANT CREATE TABLE TO "DEMO_2019_DANTEST_CHRIS";
Grant succeeded.
Elapsed: 00:00:00.01
12:51:36 SQL> GRANT CREATE VIEW TO "DEMO_2019_DANTEST_CHRIS";
Grant succeeded.
Elapsed: 00:00:00.00
12:51:36 SQL> GRANT UNLIMITED TABLESPACE TO "DEMO_2019_DANTEST_CHRIS";
Grant succeeded.
Elapsed: 00:00:00.00
12:51:36 SQL> GRANT EXECUTE ON  "SYS"."DBMS_JOB" TO "DEMO_2019_DANTEST_CHRIS";
Grant succeeded.
Elapsed: 00:00:00.03
12:51:36 SQL> GRANT EXECUTE ON  "SYS"."DBMS_LOCK" TO "DEMO_2019_DANTEST_CHRIS";
Grant succeeded.
Elapsed: 00:00:00.02
12:51:36 SQL> GRANT "CONNECT" TO "DEMO_2019_DANTEST_CHRIS";
Grant succeeded.
Elapsed: 00:00:00.01
12:51:36 SQL> GRANT "RESOURCE" TO "DEMO_2019_DANTEST_CHRIS";
Grant succeeded.
Elapsed: 00:00:00.01
12:51:36 SQL> GRANT CREATE JOB TO "DEMO_2019_DANTEST_CHRIS";
Grant succeeded.
Elapsed: 00:00:00.00
12:51:36 SQL>

Grant create database link to "DEMO_2019_DANTEST_CHRIS"

SQL> grant create database link to "DEMO_2019_DANTEST_CHRIS";

Create database link 

SQL> conn "DEMO_2019_DANTEST_CHRIS"
Enter password:
Connected.

SQL> create database link DEMO2019TRAINING connect to DEMO_2019_TRAINING identified by "4canK!9P" using 'PROD_12';
Database link created.

SQL> select 1 from dual@DEMO2019TRAINING;
         1
----------
         1

Grant read, write on data pump directory in my case DEMO

SQL> grant read, write on directory DEMO to "DEMO_2019_DANTEST_CHRIS";

Grant succeeded.

Create parameter file

cat DEMO_2019_DANTEST_CHRIS_imp.par
userid=DEMO_2019_DANTEST_CHRIS/"7dmwK!2U"
directory=DEMO
logfile=DEMO_2019_DANTEST_CHRIS_imp.log
network_link=DEMO2019TRAINING
remap_schema=DEMO_2019_TRAINING:DEMO_2019_DANTEST_CHRIS
remap_tablespace=DATA_DEMO2019TRAINING:DATA_DEMO2019DANTESTCHRIS
remap_tablespace=INDX_DEMO2019TRAINING:INDX_DEMO2019DANTESTCHRIS
logtime=all
parallel=8

Initiate IMPORT

Import: Release 12.2.0.1.0 - Production on Tue Apr 23 13:52:51 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
23-APR-19 13:52:53.476: Starting "DEMO_2019_DANTEST_CHRIS"."SYS_IMPORT_SCHEMA_01":  DEMO_2019_DANTEST_CHRIS/******** parfile=DEMO_2019_DANTEST_CHRIS_imp.par
23-APR-19 13:52:54.075: Estimate in progress using BLOCKS method...
23-APR-19 13:53:04.685: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
23-APR-19 13:53:06.375: Total estimation using BLOCKS method: 8.156 GB
23-APR-19 13:53:07.193: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
23-APR-19 13:53:16.042: Processing object type SCHEMA_EXPORT/TABLE/TABLE
23-APR-19 13:55:22.251: . . imported "DEMO_2019_DANTEST_CHRIS"."ENROLLMENT_ROLLUP"  485237 rows
23-APR-19 13:55:22.253: . . imported "DEMO_2019_DANTEST_CHRIS"."BEN_GAPS"       2247146 rows
23-APR-19 13:55:23.303: . . imported "DEMO_2019_DANTEST_CHRIS"."CHASE_VISIT"     903479 rows
23-APR-19 13:55:23.336: . . imported "DEMO_2019_DANTEST_CHRIS"."RX_IN_20190404031035" 1131979 rows
23-APR-19 13:55:24.267: . . imported "DEMO_2019_DANTEST_CHRIS"."ENROLLMENT"      278306 rows
23-APR-19 13:55:26.494: . . imported "DEMO_2019_DANTEST_CHRIS"."VISIT_IN_20190404031035" 3388950 rows
23-APR-19 13:55:26.610: . . imported "DEMO_2019_DANTEST_CHRIS"."MEMBER_SUMMARY_GRP"  151913 rows
23-APR-19 13:55:27.012: . . imported "DEMO_2019_DANTEST_CHRIS"."MEMBER_SUMMARY"  151913 rows

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

Data Pump impdp/expdp : Extract DDL and DML from dump file using SQLFILE option

Using data pump IMPDP utility we can generate SQL or DDL/DML from the dump file using SQLFILE option. When you execute IMPDP with SQLFILE option it won’t import the data into the actual tables or into the schema. Suppose if you wanted to generate some particular DDL's from the database you can use this option. Please find the example below with all syntax's.

PARFILE

userid=system/pwd

directory=DATAPUMP

dumpfile=TEST.dmp

logfile=TEST.log

SCHEMAS=TEST

$ expdp parfile=TEST.par

;;;

Export: Release 11.2.0.3.0 - Production on Tue Dec 15 16:28:57 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02":  system/******** parfile=TEST.par 

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 4.621 GB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "TEST"."TEST_AAA"               153.7 MB 1644172 rows

. . exported "TEST"."TEST_BBB"               142.0 MB 1571767 rows

. . exported "TEST"."TEST_CCC"               116.7 MB 1107338 rows

. . exported "TEST"."TEST_DDD"               118.2 MB 1459331 rows

. . exported "TEST"."TEST_EEE"               75.05 MB  771091 rows

. . exported "TEST"."TEST_FFF"               50.99 MB  487267 rows

. . exported "TEST"."TEST_GGG"               51.08 MB  616332 rows

. . exported "TEST"."TEST_HHH"               49.66 MB  591722 rows

. . exported "TEST"."TEST_III"               45.65 MB  514947 rows

. . exported "TEST"."TEST_JJJ"               43.33 MB  577712 rows

. . exported "TEST"."TEST_KKK"               41.89 MB  563463 rows

. . exported "TEST"."TEST_LLL"               40.29 MB  498193 rows

. . exported "TEST"."TEST_MMM"               40.73 MB  470865 rows

. . exported "TEST"."TEST_NNN"               39.70 MB  428088 rows

. . exported "TEST"."TEST_OOO"      32.45 MB  313492 rows

. . exported "TEST"."TEST_PPP"               35.83 MB  450797 rows

. . exported "TEST"."TEST_QQQ"               33.22 MB  438805 rows

. . exported "TEST"."TEST_RRR"               32.91 MB  427978 rows

. . exported "TEST"."TEST_SSS"               32.46 MB  352338 rows

. . exported "TEST"."TEST_TTT"               31.09 MB  372510 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:

  /media/mnt05/DATAPUMP/TEST.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at 16:36:46

Do the import with IMPDP utility using SQLFILE option.

$ impdp system/pwd dumpfile=TEST.dmp directory=DATAPUMP sqlfile=DDL.sql logfile=DDL.log

;;;

Import: Release 11.2.0.3.0 - Production on Thu Dec 17 15:57:49 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/******** dumpfile=TEST.dmp directory=DATAPUMP sqlfile=DDL.sql logfile=DDL.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 15:58:28

The output of the DDL.sql file would be 

-- CONNECT SYSTEM

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: SCHEMA_EXPORT/USER

 CREATE USER "TEST" IDENTIFIED BY VALUES 'S:793F36205EF4B329188DDEE462889143B8A321D748374746A322B82EAE0F;1ACC9700A60AE94C'

      DEFAULT TABLESPACE "APP_DATA01"

      TEMPORARY TABLESPACE "TEMP002";

-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

GRANT CREATE JOB TO "TEST";

GRANT CREATE PROCEDURE TO "TEST";

GRANT CREATE SEQUENCE TO "TEST";

GRANT CREATE VIEW TO "TEST";

GRANT CREATE TABLE TO "TEST";

GRANT UNLIMITED TABLESPACE TO "TEST";

GRANT CREATE SESSION TO "TEST";

CREATE TABLE "TEST"."TEST_AAA"

   (    "ID" NUMBER,

        "MEM_NBR" VARCHAR2(75 BYTE),

        "MEMBER_DATE" DATE,

        "COMPLIANCE" VARCHAR2(6 BYTE),

        "RNK" NUMBER,

        "RVAL" CHAR(12 BYTE)

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS NOLOGGING

  TABLESPACE "APP_DATA01" ;

CREATE TABLE "TEST"."TEST_CCC"

   (    "ID" NUMBER,

        "MEM_NBR" VARCHAR2(75 BYTE),

        "MEMBER_DATE" DATE,

        "COMPLIANCE" VARCHAR2(6 BYTE),

        "RNK" NUMBER,

        "RVAL" CHAR(7 BYTE)

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS NOLOGGING

  TABLESPACE "APP_DATA01" ;

CREATE TABLE "TEST"."TEST_BBB"

   (    "ID" NUMBER,

        "MEM_NBR" VARCHAR2(75 BYTE),

        "MEMBER_DATE" DATE,

        "COMPLIANCE" VARCHAR2(6 BYTE),

        "RNK" NUMBER,

        "RVAL" CHAR(7 BYTE)

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS NOLOGGING

  TABLESPACE "APP_DATA01" ;

Oracle DataPump “INCLUDE” parameter and limit of 4000 characters

Today I am trying to export certain tables (202 in count) using expdp, but I got “UDE-00014: invalid value for parameter, ‘include'”:
expdp SYSTEM/PASSWORD SCHEMAS=TEST_2016_S DIRECTORY=FULLBACKUPS
DUMPFILE=TEST_2016_S.DMP LOGFILE=TEST_2016_S_.LOG
INCLUDE=TABLE:"IN('AAA','BBB','CCC','DDD',
......
'ZZZ','AAAA','BBBB','CCCC')" COMPRESSION=ALL PARALLEL=8
“include” parameter accepts only 4000 characters. A simple workaround is to use a table to hold the names of the tables/objects and use this table with include parameter:
CREATE TABLE tables_list(table_name VARCHAR2(30) );
INSERT INTO table_list ( 'AAA' );
INSERT INTO table_list ( 'BBB' );
INSERT INTO table_list ( 'CCC' );
INSERT INTO table_list ( 'CCC' );
...
...
INSERT INTO table_list ( 'ZZZ' );
INSERT INTO table_list ( 'AAAA' );
INSERT INTO table_list ( 'BBBB' );
INSERT INTO table_list ( 'CCCC' );
COMMIT;

expdp SYSTEM/PASSWORD SCHEMAS=TEST_2016_S DIRECTORY=FULLBACKUPS
DUMPFILE=TEST_2016_S.DMP LOGFILE=TEST_2016_S_.LOG include=TABLE:"IN (SELECT table_name FROM table_list)" 

ORA-00959: tablespace 'XXX' does not exist - imp or impdp

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing XXX_2016's objects into XXX_2016
. . importing table                "AAA"       4231 rows imported
. . importing table           "BBB"        429 rows imported
. . importing table            "CCC"         10 rows imported
. . importing table               "DDD"        504 rows imported
. . importing table           "EEE"      12053 rows imported
. . importing table     "FFF"        443 rows imported
. . importing table               "FFF1"        504 rows imported
. . importing table          "FFF2"      23544 rows imported
. . importing table            "FFF3"          1 rows imported
. . importing table            "FFF4"      10984 rows imported
. . importing table             "FFF5"     382879 rows imported
. . importing table                 "FFF6"         12 rows imported
. . importing table             "FFF7"        423 rows imported
. . importing table                     "FFF8"     382879 rows imported
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE TABLE "XXX" ("REQUEST_ID" VARCHAR2(20) NOT NULL ENABLE, "CR_ID" NUMBER NOT NULL ENABLE, "GLOBAL__ID" VARCHAR2(50), "CREATION_S"
 "TATUS" CLOB)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL"
 " 786432 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL"
 " DEFAULT) TABLESPACE "TEST_TBL" LOGGING NOCOMPRESS LOB ("CREATION_STATU"
 "S") STORE AS BASICFILE  (TABLESPACE "TEST_TBL" ENABLE STORAGE IN ROW CH"
 "UNK 8192 RETENTION  NOCACHE LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MIN"
 "EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TEST_TBL' does not exist
. . importing table               "FFF9"      10787 rows imported
. . importing table            "FFF10"     653765 rows imported
. . importing table                   "FFF11"       2826 rows imported
. . importing table                   "FFF12"       3171 rows imported
. . importing table                   "FFF13"       3268 rows imported
. . importing table                   "FFF14"       1888 rows imported
. . importing table                   "FFF15"       1139 rows imported
. . importing table                   "FFF16"       3530 rows imported
. . importing table              "FFF17"         91 rows imported
. . importing table                    "USAGE"        360 rows imported
IMP-00033: Warning: Table "SAMPLE_1" not found in export file
IMP-00033: Warning: Table "SAMPLE_2" not found in export file
IMP-00033: Warning: Table "SAMPLE_4" not found in export file
IMP-00033: Warning: Table "SAMPLE_5" not found in export file
Import terminated successfully with warnings.

Solution:

impdp or imp will return a ORA-00959 when a table definition specifies multiple tablespaces (i.e. a CLOB column stored in a separate tablespace.  In these cases, the solution is to pre-create the table (punching the DDL with dbms_metadata) and use impdp or imp with ignore=y.

Kill, restart or resume datapump expdp/impdp jobs

Find datapump jobs

SELECT owner_name, job_name, operation, job_mode, 

state, attached_sessions 

FROM dba_datapump_jobs 

WHERE job_name NOT LIKE 'BIN$%' 

ORDER BY 1,2

  Killing or stopping datapump jobs

The difference between Kill and Stop is simple to explain. When killing a job, you won’t be able to resume or start it again. Also logs and dumpfiles will be removed!

When exporting (or importing), press Ctrl-c to show the datapump prompt and type KILL_JOB orSTOP_JOB[=IMMEDIATE]. You will be prompted to confirm if you are sure.

Adding ‘=IMMEDIATE‘ to STOP_JOB will not finish currently running ‘sub-job’ and must be redone when starting it again.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]
Export> KILL_JOB
..or..
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes

Resuming a stopped job

Identify your job using above query

> expdp system ATTACH=SYS_EXPORT_SCHEMA_01

Start/restart the job with START_JOB, use ‘=SKIP_CURRENT‘ if you want to skip
the current job. To show progress again, type CONTINUE_CLIENT (Job will be restarted if idle).

Export> START_JOB[=SKIP_CURRENT]


Export> CONTINUE_CLIENT