My Expdp and impdp practices

Use of Expdp & Impdp on Windows:


Note:We have to be in the correct path while exporting or importing using expdp and impdp

I have exported the tables using expdp from user scott and imported to test user using impdp

The steps I followed are as follows:

Step 1: Create a directory or folder by name expdtest in the path mentioned below:
C:\oracle\product\10.2.0>cd expdptest
C:\oracle\product\10.2.0\expdptest>dir
Volume in drive C has no label.
Volume Serial Number is 74A1-B8A4
Directory of C:\oracle\product\10.2.0\expdptest

Step 2:Create a directory which is logical or use by oracle for doing export or import grant the users dba role or exp_full_database or imp_full_database as per your usage.

Sqlplus ‘/as sysdba’
SQL>create directory mydir3 as ‘C:\oracle\product\10.2.0\expdptest’;
SQL> grant read,write on directory mydir1 to public;
Select *from dba_directories;
Will show:Mydir3 directory

Step 3:Now use expdp utility for exporting schema scott.Use the key words DIRECTORY and DUMPFILE in caps to get rid of below errors:
C:\oracle\product\10.2.0\expdptest>expdp system/manager directory=MYDIR3

Export: Release 10.2.0.1.0 - Production on Monday, 15 February, 2010 18:18:14
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "C:\oracle\product\10.2.0\expdptest\expdat.dmp"
ORA-27038: created file already exists
OSD-04010: option specified, file already exists

C:\oracle\product\10.2.0\expdptest>dumpfile=expdptest5.dmp schemas=scott 'dumpfile' is not recognized as an internal or external command,
operable program or batch file.

C:\oracle\product\10.2.0\expdptest>expdp system/manager DIRECTORY=MYDIR3 DUMPFILE=expschema.dmp schemas=scott

Export: Release 10.2.0.1.0 - Production on Monday, 15 February, 2010 18:20:17
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** DIRECTORY=MYDIR3 DUMP
FILE=expschema.dmp schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type  SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
C:\ORACLE\PRODUCT\10.2.0\EXPDPTEST\EXPSCHEMA.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:20:32

Step4: Making use of import:
We have to use remap_schema keyword to export from scott user to test user.

C:\oracle\product\10.2.0\expdptest>impdp system/manager DIRECTORY=MYDIR3 DUMPFILE=expschema.dmp remap_schema=scott:test

Import: Release 10.2.0.1.0 - Production on Monday, 15 February, 2010 18:24:15
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=MYDIR3 DUMPFILE=expschema.dmp remap_schema=scott:test

Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "TEST"."DEPT" 5.656 KB 4 rows
. . imported "TEST"."EMP" 7.820 KB 14 rows
. . imported "TEST"."SALGRADE" 5.585 KB 5 rows
. . imported "TEST"."BONUS" 0 KB 0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 18:24:20

Step 5:Check and confirm:

C:\oracle\product\10.2.0\expdptest>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 15 18:24:31 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: test/test
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select *from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE






Best regards,

Rafi.