Export Import Using Data Pump

1. Create Directory:
SQL> CREATE DIRECTORY EXPORT_EXAMPLE AS '/var/tmp/karhmm';

Directory created.

2. Connect to database and export both the tables:

oracle@incs5vs1a>expdp VSOWNER/VSOWNER@VSDB_S1  directory=EXPORT_EXAMPLE tables=VOUCHERT DUMPFILE=VOUCHERS.DMP

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 18 May, 2013 8:15:54

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Starting "VSOWNER"."SYS_EXPORT_TABLE_01":  VSOWNER/********@VSDB_S1 directory=EXPORT_EXAMPLE tables=VOUCHERT DUMPFILE=VOUCHERS.DMP 

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

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/STATISTICS/TABLE_STATISTICS

. . exported "VSOWNER"."VOUCHERT"                        118.8 KB    3063 rows

Master table "VSOWNER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for VSOWNER.SYS_EXPORT_TABLE_01 is:

  /var/tmp/karhmm/VOUCHERS.DMP

Job "VSOWNER"."SYS_EXPORT_TABLE_01" successfully completed at 08:15:58

oracle@incs5vs1a>expdp VSOWNER/VSOWNER@VSDB_S1  directory=EXPORT_EXAMPLE tables=VOUCHERDATAT DUMPFILE=VOUCHERSDATA.DMP

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 18 May, 2013 8:19:03

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Starting "VSOWNER"."SYS_EXPORT_TABLE_01":  VSOWNER/********@VSDB_S1 directory=EXPORT_EXAMPLE tables=VOUCHERDATAT DUMPFILE=VOUCHERSDATA.DMP 

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/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/STATISTICS/TABLE_STATISTICS

. . exported "VSOWNER"."VOUCHERDATAT"                    8.335 KB       7 rows

Master table "VSOWNER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for VSOWNER.SYS_EXPORT_TABLE_01 is:

  /var/tmp/karhmm/VOUCHERSDATA.DMP

Job "VSOWNER"."SYS_EXPORT_TABLE_01" successfully completed at 08:19:06

oracle@incs5vs1a>cd /var/tmp/karhmm/

oracle@incs5vs1a>ls

export.log         VOUCHERS.DMP       VOUCHERSDATA.DMP

Now go to other server where you want to Import these two tables:

==============================================

3. Create user and assign tablespace to it

SQL> create user vsowner identified by vsowner;

User created.

SQL> alter user vsowner default tablespace users;

User altered.

SQL> alter user vsowner quota unlimited on users;

User altered.

SQL> alter user vsowner temporary tablespace temp;

User altered.

4. GRANT needful permissions to new user.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, CREATE VIEW, CREATE SYNONYM, ALTER SESSION TO VWOWNER;

Grant succeeded.

5. Create Import directory:

SQL> CREATE DIRECTORY IMPORT_EXAMPLE AS 'C:\LOGS\SQL\VOUCHERS';

Directory created.

6. Now we need to Grant permission of directory to user in which tables are created.

SQL> GRANT READ on directory imports to vsowner;

Grant succeeded.

Even after this we get following errors:

c:\Logs\SQL\Vouchers>impdp vsowner/vsowner DIRECTORY=IMPORTS TABLES=VOUCHERT DUMPFILE=VOUCHERS.DMP

Import: Release 11.2.0.1.0 - Production on Sat May 18 13:17:23 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-31631: privileges are required

This shows both read and write permission is needed on the directory. Grant the required permissions:

SQL> GRANT ALL on directory import_example to vsowner;

Grant succeeded.

After this import run successfully:

c:\Logs\SQL\Vouchers>impdp vsowner/vsowner DIRECTORY=IMPORTS TABLES=VOUCHERT DUMPFILE=VOUCHERS.DMP

Import: Release 11.2.0.1.0 - Production on Sat May 18 13:19:33 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Starting "VSOWNER"."SYS_IMPORT_TABLE_01":  vsowner/******** DIRECTORY=IMPORT_EXAMPLE TABLES=VOUCHERT DUMPFILE=VOUCHERS.DMP 

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "VSOWNER"."VOUCHERT"  

We got errors while creation of index so creating index manually:

SQL> ALTER TABLE VoucherT ADD CONSTRAINT  VoucherT_PK PRIMARY KEY (SerialNumber) USING INDEX TABLESPACE USERS INITRANS 5;

Table altered.

SQL> CREATE unique INDEX ActivationCodeI ON VoucherT(ActivationCode) TABLESPACE USERS INITRANS 5;

Index Created.

SQL> select index_name from user_indexes;

INDEX_NAME

------------------------------

VOUCHERT_PK

ACTIVATIONCODEI