Monday 5 December 2016

Oracle data pump Export and Import scenario with examples

What Is Data Pump Export?
Data Pump Export is a utility for unloading data and metadata into a set of operating system files called a dump file set. The dump file set can be imported only by the Data Pump Import utility. The dump file set can be imported on the same system or it can be moved to another system and loaded there.

The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set.

Because the dump files are written by the server, rather than by the client, the database administrator (DBA) must create directory objects that define the server locations to which files are written

What Is Data Pump Import?
Data Pump Import (hereinafter referred to as Import for ease of reading) is a utility for loading an export dump file set into a target system. The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set.

Import can also be used to load a target database directly from a source database with no intervening dump files. This is known as a network import.

Data Pump Import enables you to specify whether a job should move a subset of the data and metadata from the dump file set or the source database (in the case of a network import), as determined by the import mode. This is done using data filters and metadata filters, which are implemented through Import commands

When metadata is moved, Data Pump uses functionality provided by the DBMS_METADATA PL/SQL package. The DBMS_METADATA package provides a centralized facility for the extraction, manipulation, and re-creation of dictionary metadata.
The DBMS_DATAPUMP and DBMS_METADATA PL/SQL packages can be used independently of the Data Pump clients.


DataPump Export & Import Considerations:
* We have to Login as SYS user as shown below command:
$ sqlplus / as sysdba
SQL> show user
USER is "SYS"

SQL> show parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
utl_file_dir                         string
 

* Now, Set the utl_file_dir as shown below command:
SQL> alter system set utl_file_dir='/u01/oracle/admin/ora-data/dpdump' scope=spfile;
System altered.
SQL>

* Then, Shutdown the database and startup the database in nomount status:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1090519040 bytes
Fixed Size                  2020128 bytes
Variable Size             335547616 bytes
Database Buffers          738197504 bytes
Redo Buffers               14753792 bytes

SQL>
* Now, we will create the pfile from spfile as shown below command and start the database in open status:

SQL> create pfile from spfile;
File created.
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL> select name,log_mode,open_mode from v$database;
NAME      LOG_MODE     OPEN_MODE
--------- ------------ ----------
DQA3      ARCHIVELOG   READ WRITE

* Get confirm and check the value of utl_file_dir:
SQL> show parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
utl_file_dir                         string      /u01/oracle/admin/ora-data/dpdump

* Here, we will create Directory from below command:
SQL> CREATE DIRECTORY dmpdir AS '/u01/oracle/admin/ora-data/dpdump/';
Directory created.

* Then provide Permissions as shown in below command:
SQL> GRANT READ, WRITE ON DIRECTORY dmpdir TO public;
Grant succeeded.
SQL>

$ pwd/u01/oracle/admin/ora-data/dpdump
1.Table level export and import (same schema)
SQL> show user
USER is "SCOTT"
SQL>
SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE

$ expdp tables=emp DIRECTORY=dmpdir DUMPFILE=scott.emp LOGFILE=scott_emp.log

Export: Release 10.2.0.1.0 - 64bit Production on Wednesday, 21 September, 2011 8:05:27
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Username: scott
Password:****
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=emp DIRECTORY=dmpdir DUMPFILE=scott.emp
LOGFILE=scott_emp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/oracle/admin/ora-data/dpdump/scott.emp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 08:05:45

$ ls
scott.emp  scott_emp.log
$

* Verify the correctly logfile to check the process has done correctly.
* Now suppose for some reason the table has accidently dropped after some time or days,
then what should we do, we can import the table as we have already kept exported table data.
So, here we will see now how to import the table with example:
IMPORT THE ABOVE SCOTT.EMP TABLE:
* Login as SCOTT user:
$sqlplus scott/password
SQL> show user
USER is "SCOTT"
SQL>
* Details before dropping the table:
SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE

* Now, we are Dropping the EMP table:
SQL> drop table emp;
Table dropped.

* After dropping the table, we will see there is no emp table in list:
SQL> select * from cat;
TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
DEPT                           TABLE
BONUS                          TABLE
SALGRADE                       TABLE

* Now we will import the table, as shown below:
$ pwd/u01/oracle/admin/ora-data/dpdump

$ impdp dumpfile=scott.emp logfile=imp_emp_scott.log directory=dmpdir tables=emp
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 21 September, 2011 8:38:07
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Username: scott
Password:****

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** dumpfile=scott.emp logfile=imp_emp_scott.log
directory=dmpdir tables=emp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP"                               7.820 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 08:38:13

* Now, we will check the details after import:
SQL> conn scott
Enter password:****
Connected.

SQL> show user
USER is "SCOTT"
SQL> select * from cat;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
BONUS                          TABLE
SALGRADE                       TABLE
EMP                            TABLE

SQL> select count(*) from emp;
  COUNT(*)
----------
        14

2. SCENARIO:
Table level export and import from one schema to another schema [scott to peter] Export from scott :


SQL> show user
USER is "SCOTT"
SQL>
SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE

$ expdp dumpfile=scott_salgrade_peter.dmp logfile=scott_peter.log directory=dmpdir exclude=grant tables=salgrade

Export: Release 10.2.0.1.0 - 64bit Production on Wednesday, 21 September, 2011 11:15:21

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Username: scott
Password:****


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** dumpfile=scott_salgrade_peter.dmp

logfile=scott_peter.log directory=dmpdir exclude=grant tables=salgrade
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/oracle/admin/ora-data/dpdump/scott_salgrade_peter.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 11:15:29

* Now, we will Import salgrade table from scott to peter
* Details before Import:
SQL> show user
USER is "PETER"
SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
TEST                           TABLE
BOOKSTORE                      TABLE
STUDENT                        TABLE

$ impdp dumpfile=scott_salgrade_peter.dmp logfile=scott_peter.log directory=dmpdir remap_schema=scott:peter
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 21 September, 2011 11:17:25
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Username: peter
Password:****

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "PETER"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "PETER"."SYS_IMPORT_FULL_01":  peter/******** dumpfile=scott_salgrade_peter.dmp
logfile=scott_peter.log directory=dmpdir remap_schema=scott:peter
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PETER"."SALGRADE"                          5.585 KB       5 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "PETER"."SYS_IMPORT_FULL_01" successfully completed at 11:17:33

* Details after Import:
SQL> show user
USER is "PETER"
SQL>
SQL> select * from cat;
TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
SALGRADE                       TABLE
TEST                           TABLE
BOOKSTORE                      TABLE
STUDENT                        TABLE

In above query, we can see SALGRADE table has been imported successfully.

For any query, Please contact us either to ora-data.blogspot.com or comment below in comment box.

5 comments:

  1. I’ve been browsing online greater than three hours today, yet I never found
    any fascinating article like yours. It’s pretty price sufficient for me.
    Personally, if all website owners and bloggers made good content as you probably
    did, the internet will likely be much more helpful
    than ever before. Ahaa, its fastidious discussion concerning this post at
    this place at this weblog, I have read all that, so now me also
    commenting here. I’ll right away clutch your rss as I can not
    to find your email subscription link or e-newsletter service.
    Do you have any? Please allow me understand so that
    I may subscribe. Thanks. http://foxnews.net/

    ReplyDelete
  2. WOW just what I was searching for. Came here by searching for agen bola

    ReplyDelete
  3. It is vitally easy stated than executed to create an internet
    site and use the Internet marketing methods to promote a enterprise.

    The web design service supplier should be enabled with expertise in using
    optimization tools and methods. One thing you could know that
    the backup instruments or plugins, though whatever should be
    containing the features and the main goal should be the overall webpage safety through a correct backup and security
    processes. Benchmark might be performed manually or with the use of cell application testing instruments.
    Use hierarchical structures to offer a sense of aesthetic hierarchy to
    delineate the content material type and importance.
    Boston webpage development companies frequently italicize
    along with bold words in paragraphs for making websites simple to have a look at by so customers can simply located
    desired content material. With Wordpress web site development companies now it has change into easier
    to get the utmost advantages in minimal time.
    Whereas website designs experts want to adjust great plan with
    velocity and effectiveness for the web. If you are on the lookout for a great web design firm that gives excessive end net designs for web site homeowners, you need to do a
    little analysis work. This makes the web site open faster.

    ReplyDelete
  4. importer database Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info.

    ReplyDelete

Thank you for your comments and suggestions. It's good to talk you.