It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

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

1 comments:

BlueHost is ultimately the best hosting provider for any hosting services you might require.

 

You Might Also Like

Related Posts with Thumbnails

Pages