Wikipedia

Search results

Expdp Impdp Tutorial Part -1


Objective – Export table dbatest.EMP_TAB from SOURCEDB to TARGETDB using network link .
Import using network , wont create any dumpfile, it will directly do the import using db_link
Add tns entry of sourcedb in TARGETDB tnsnames.ora file [ TARGETDB]
Create a DB_LINK on TARGETDB pointing to SOURCEDB.

Now Run impdp on TARGETDB using NETWORK_LINK=<>
impdp directory=DUNNING logfile=test.log tables=dbatest.EMP_TAB network_link=SOURCE

Starting "DBATEST"."SYS_IMPORT_TABLE_01":  dbatest/******** directory=DUNNING logfile=test.log tables=dbatest.EMP_TAB network_link=SOURCE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 23 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "DBATEST"."EMP_TAB"                         175340 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
;;; Import> exit
Job "DBATEST"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jan 23 18:12:05 2017 elapsed 0 00:07:51




2. QUERY CLAUSE in EXPDP

Requirement – Export dump of a table from emp_tab WHERE created > sysdate -40 . Filter can be added on any column depending upon the requirement.

SQL> select count(*) from “DBACLASS”.”EMP_TAB” WHERE created > sysdate -40;

COUNT(*)
———-
1384

Create a parfile with query clause:



cat expdp_query.par


dumpfile=test.dmp

logfile=test1.log

directory=TEST

tables=dbaclass.EMP_TAB

QUERY=dbaclass.EMP_TAB:"WHERE created > sysdate -40"



Now run the expdp command with parfile. We can see, 1384 rows will be exported.
expdp parfile=expdp_query.par


Export: Release 12.1.0.2.0 - Production on Mon Jan 23 14:52:07 2017


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


Username: / as sysdba


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA parfile=expdp_query.par

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 29 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "DBACLASS"."EMP_TAB"                        199.4 KB    1384 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /export/home/oracle/test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jan 23 14:53:02 2017 elapsed 0 00:00:23

3. SQLFILE parameter in impdp:
It can be used, only with impdp. This helps in generating the DDLs from a dumpfile.
Suppose i have dump file of table DBACLASS.DEP_TAB . If you need the DDL of the table, then use sqlfile with impdp command as below.
Parfile:



dumpfile=test.dmp
logfile=test1.log
directory=TEST
tables=DBACLASS.DEP_TAB
sqlfile=emp_tab.sql

impdp parfile=impdp_sqlfile.par
Import: Release 12.1.0.2.0 - Production on Mon Jan 23 15:14:16 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_TABLE_01":  /******** AS SYSDBA parfile=impdp_sqlfile.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_SQL_FILE_TABLE_01" successfully completed at Mon Jan 23 15:14:22 2017 elapsed 0 00:00:01


Now check the generated sql_file:



cat emp_tab.sql
-- CONNECT SYS
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/TABLE/TABLE
CREATE TABLE "DBACLASS"."DEP_TAB"
   (    "OWNER" VARCHAR2(128 BYTE),
        "OBJECT_NAME" VARCHAR2(128 BYTE),
        "SUBOBJECT_NAME" VARCHAR2(128 BYTE),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(23 BYTE),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19 BYTE),
        "STATUS" VARCHAR2(7 BYTE),
        "TEMPORARY" VARCHAR2(1 BYTE),
        "GENERATED" VARCHAR2(1 BYTE),
        "SECONDARY" VARCHAR2(1 BYTE),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(128 BYTE),
        "SHARING" VARCHAR2(13 BYTE),
        "EDITIONABLE" VARCHAR2(1 BYTE),
        "ORACLE_MAINTAINED" VARCHAR2(1 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- new object type path: SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path: SCHEMA_EXPORT/STATISTICS/MARKER

4. TABLE_EXISTS_ACTION option in IMPDP:
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
TABLE_EXISTS_ACTION=APPEND:
With this option ,while importing the table, if table exists in the database, then it will append the data on top the existing data in table.


select count(*) from "DBATEST"."EMP_TAB";
  COUNT(*)
----------
    175340
impdp dumpfile=emp_tab.dmp logfile=emp_tab.log directory=VEN  table_exists_action=APPEND
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA dumpfile=emp_tab.dmp logfile=emp_tab.log directory=VEN table_exists_action=APPEND
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "DBATEST"."EMP_TAB" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DBATEST"."EMP_TAB"                         19.16 MB  175340 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Tue Jan 24 08:57:09 2017 elapsed 0 00:01:18
SQL[SYS@TCRMDB01]SQL>>]select count(*) from "DBATEST"."EMP_TAB";
  COUNT(*)
----------
    350680


TABLE_EXISTS_ACTION=TRUNCATE:

While importing the table, if the table exists in database, it will truncate the table and load the data.

6. DATA_OPTION=SKIP_CONSTRAINT_ERRORS
DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS is a very useful parameter mostly used with table_exists_action=APPEND .
In general, if you are appending data ( table_exists_action=APPEND) to an existing table with unique constaint and if the new rows violating the unique constraint,
then the import job will fail( even if only one row violates the constraint)
DATA_OPTION=SKIP_CONSTRAINT_ERRORS parameter will help in skipping the duplicate row and import the rest of the rows. It is similar to IGNORE=Y option of classic exp/imp
Here i tried to append data with table_exists_action=APPEND, it failed due to unique key violation.
impdp dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPEND


With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPEND
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "DBATEST"."GNSRT00" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "DBATEST"."GNSRT00" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (DBATEST.RRN_PK_GNSRT00) violated
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Jan 24 10:31:28 2017 elapsed 0 00:00:11



Now lets try with SKIP_CONSTRAINT_ERRORS option:

impdp dumpfile=unique.dmp directory=EXPDIR table_exists_action=APPEND data_options=SKIP_CONSTRAINT_ERRORS


8. Views_as_tables Parameter In Datapump Of Oracle 12c

VIEWS_AS_TABLES parameter has been introduced in datapump of 12c. With this we can export a view at source database as a table  and import as table in target database.




9. TRANSFORM=DISABLE_ARCHIVE_LOGGING – Oracle 12c new feature

A new feature has been added in datapump of oracle 12c. We can import data with nologgin option i.e without generating logs. We sometimes face issue while importing big tables, as it generates lot of archive logs.

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y – This parameter can be used to make the impdp nologging.


Posted by at
Labels

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)