Oracle: Import/Export, COPY, Datapump utilities expdp/impdp

Refreshing data in DEV/QA from PROD

Following methods can be used:
1. Export/Import utilities can be used. But it requires
-- First export tables into dump file
-- Drop tables in DEV/QA environment.
-- Import export dumps (see below in detail the exp/imp)
2. Oracle Data Pump in Oracle Database 10g (impdp/expdp)
3. COPY command

Export/Import:
Oracle's export (exp) and import (imp) utilities are used to perform exports and imports of data objects (such as tables) and therefore Logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database. These utilities can be used to move data between different machines, databases or schema and/or on different Oracle versions. However, as they use a proprietary binary file format, they can only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database. If the data is exported on a system with a different Oracle version then on that on which it is imported, imp must be the newer version. That means, if something needs to be exported from 10g into 9i, it must be exported with 9i's exp.

Syntax :
exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)

imp scott/tiger file=emp.dmp full=yes
imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept

Using parameter file
exp userid=scott/tiger@orcl parfile=export.txt

where export.txt is like

BUFFER=100000
FILE=account.dmp
FULL=n
OWNER=scott
GRANTS=y
COMPRESS=y

Note:From Oracle 10g, we can choose between using the old imp/exp utilities, or the newly introduced Datapump utilities, called expdp and impdp. These new utilities introduce much needed performance improvements, network based exports and imports, etc.

Link: http://www.orafaq.com/wiki/Import_Export_FAQ

Oracle Data Pump in Oracle Database 10g:

Oracle Data Pump is a newer, faster and more flexible alternative to the exp and imp utilities available in previous oracle version. In addition to basic import and export functionality, data pump provides a PL/SQL API ( DBMS_DATAPUMP API ), a mechanism to allow users to move all or parts of a database between databases, thereby, provides a server-side infrastructure for fast data and metadata movement.
New Export (expdp) and Import (impdp) utilities are available to make use of the API.

Prerequisites:
Before running Data Pump Export or Data Pump Import, a directory object must be created by a DBA or by any user with CREATE ANY DIRECTORY privilege. Create directory as mentioned below.

alter user scott identified by tiger account unlock;
grant create any directory to scott;

create or replace directory test_dir as '/u01/app/oracle/oradata/';
grant read, write on directory test_dir to scott;

Once directory is created run below scripts to export and import tables. The parameter TABLES is used to specify the tables to be exported.

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

expdp scott/tiger@db10g tables=emp,dept directory=test_dir dumpfile=emp_dept.dmp logfile=expdpemp_dept.log

impdp scott/tiger@db10g tables=emp,dept directory=test_dir dumpfile=emp_dept.dmp logfile=impdpemp_dept.log


Schema Exports/Imports:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Export/Import:

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log


Links :
http://www.orafaq.com/wiki/Datapump
http://www.oracle-base.com/articles/10g/OracleDataPump10g.php
http://www.oracle.com/technology/obe/obe10gdb/storage/datapump/datapump.htm


COPY Command:

COPY command is one of the fastest way of copying data between databases and schemas.
Format of copy command is as

copy from database to database {appendcreateinsertreplace} [(column, column, column, ...)] using ;

COPY command supports the following data types:

CHAR
DATE
LONG
NUMBER
VARCHAR2

Example:
copy from scott/tiger@local_db to scott/tiger@remote_db -
create image_table using -
select image_no, image -
from images;


copy from scott/tiger@local_db to scott/tiger@remote_db -
create tgt using -
select * from src -
where x =5