How to Export and Import table in Oracle Database using Datapump (expdp,impdp) #exportimport #oracle

Показать описание

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.

CREATE OR REPLACE DIRECTORY test_dir AS '/oracle/oradata/';

GRANT READ, WRITE ON DIRECTORY APPS_DIR TO apps;

Query ALL_DIRECTORIES/dba_directories view to display all existing direcotiries.

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..

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.

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.

How import table in oracle database...

Before import the table . we will drop the table EMP_POLICY_MASTER.

drop table EMP_POLICY_MASTER.

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:

Master table "APPS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

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....

select count(*) from EMP_POLICY_MASTER;

COUNT(*)

----------

1310

#oracle #oracledatabase #datapump #exportdata #export #import #exp #imp #exporttable