Oracle Data Pump is a newer, faster and more flexible alternative to the traditional "exp" and "imp" utilities used in previous Oracle versions. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.
Before going to export/import in oracle database make sure directory exist, if not we will have to create it first.
The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file system of the database server.
SQL> CREATE OR REPLACE DIRECTORY test_dir AS '/oracle/oradata/';
SQL> GRANT READ, WRITE ON DIRECTORY APPS_DIR TO apps;
Query ALL_DIRECTORIES/dba_directories view to display all existing direcotiries.
SQL> select owner,directory_name,directory_path from all_directories;
Table Exports/Imports:-
TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax.
Parameter tables allow to mention more than one table..
expdp apps/apps@UAT tables=EMP_POLICY_MASTER directory=APPS_DIR dumpfile=EMP_POLICY_MASTER.dmp logfile=expdpEMP_POLICY_MASTER.log
impdp apps/apps@UAT tables=EMP_POLICY_MASTER directory=APPS_DIR dumpfile=EMP_POLICY_MASTER.dmp logfile=impdpEMP_POLICY_MASTER.log
The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.
Schema Exports/Imports:-
The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax.
expdp apps/apps@UAT schemas=apps directory=APPS_DIR dumpfile=apps.dmp logfile=expdpAPPS.log
impdp apps/apps@UAT schemas=apps directory=APPS_DIR dumpfile=apps.dmp logfile=impdpAPPS.log
Database Exports/Imports:-
The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax.
expdp system/password@UAT full=Y directory=APPS_DIR dumpfile=DB11g.dmp logfile=expdpDB11g.log
impdp system/password@UAT full=Y directory=APPS_DIR dumpfile=DB11g.dmp logfile=impdpDB11g.log
How import table in oracle database...
Before import the table . we will drop the table EMP_POLICY_MASTER.
SQL>drop table EMP_POLICY_MASTER.
$impdp apps/apps tables=EMP_POLICY_MASTER directory=apps_dir dumpfile=EMP_POLICY_MASTER1.dmp file=expdpEMP_POLICY_MASTER1.log
Import: Release 11.2.0.4.0 - Production on Sat Feb 20 15:43:15 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=expdpEMP_POLICY_MASTER1.log" Location: Command Line, Replaced with: "dumpfile=expdpEMP_POLICY_MASTER1.log"
Master table "APPS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "APPS"."SYS_IMPORT_TABLE_01": apps/******** tables=EMP_POLICY_MASTER directory=apps_dir dumpfile=EMP_POLICY_MASTER1.dmp dumpfile=expdpEMP_POLICY_MASTER1.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "APPS"."EMP_POLICY_MASTER" 146.5 KB 1310 rows
Job "APPS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Feb 20 15:43:16 2021 elapsed 0 00:00:01
We can see successfully imported the table....
SQL> select count(*) from EMP_POLICY_MASTER;
COUNT(*)
----------
1310
THANK YOU.........
