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

CREDIT TO: Rohit Khurana (February 7, 2012) http://www.oraclecommunity.net/profiles/blogs/how-to-export-a-table-in-oracle-10g

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

CONNECT WITH YOUR DATABASE

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 6 23:14:43 2012

Copyright (c) 1982, 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

STEP -1)

####### CREATE A FOLDER IN YOUR SYSTEM DRIVE WHERE YOU CAN DEFINE THE PATH OF DIRECTORY #######

CREATE A DIRECTORY

SQL> CREATE DIRECTORY EXPORT_EXAMPLE AS 'C:\ROHIT_BLOG\';

Directory created.

SQL> SELECT * FROM DBA_DIRECTORIES;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------
SYS EXPORT_EXAMPLE C:\rohit_blog\
SYS SUBDIR D:\oracle\product\10.2.0\db_1\demo\schema\order_entry\/2002/Se
SYS XMLDIR D:\oracle\product\10.2.0\db_1\demo\schema\order_entry\
SYS MEDIA_DIR D:\oracle\product\10.2.0\db_1\demo\schema\product_media\
SYS LOG_FILE_DIR D:\oracle\product\10.2.0\db_1\demo\schema\log\
SYS WORK_DIR C:\ADE\aime_10.2_nt_push\oracle/work
SYS DATA_FILE_DIR D:\oracle\product\10.2.0\db_1\demo\schema\sales_history\
SYS DATA_PUMP_DIR D:\oracle\product\10.2.0\admin\orcl\dpdump\
SYS ADMIN_DIR C:\ADE\aime_10.2_nt_push\oracle/md/admin

9 rows selected.

SQL> SHOW USER
USER is "SYS"

STEP -2) CONNECT WITH THE USER IN WHICH YOU WANT TO CREATE A TABLE

SQL> CONN HR/HR
Connected.

SQL> CREATE TABLE ORACLE_MASTER
2 AS
3 SELECT * FROM HR.EMPLOYEES;

Table created.


SQL> INSERT INTO ORACLE_MASTER SELECT * FROM HR.EMPLOYEES;

107 rows created.

SQL> COMMIT;

Commit complete.

######## WE CREATE A TABLE ORACLE_MASTER IN HR SCHEMA #########

CHECK OUT IT EXISTENSE

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
JOY TABLE
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
CORRUPTION TABLE
ORACLE_MASTER TABLE

11 rows selected.


STEP -3) OPEN THE CMD (COMMAND PROMT)

NOW I'LL TAKE A EXPORT OF A TABLE NAMED CALLED ORACLE_MASTER WHICH EXIST IN HR USER
I'LL GIVE A NAMED CALLED ORM TO MY DUMPFILE WHICH ACTUALLY KEEP THE BACKUP OF ORACLE_MASTER TABLE

SYNTAX FOR EXPORTING A TABLE
EXPDP USERNAME/PASSWORD@SERVICE DIRECTORY=DIRECTORY_NAME TABLES=TABLE_NAME DUMPFILE=ANY_NAME.DMP

EXPDP = >MEANS YOU WANT TO IMPORT
USER_NAME => ASSIGN THE USER NAME
SERVICE_NAME => NOT NECESSARY BUT IF YOU HAVE MYLTIPLE DATABASE ON SERVER THEN IT'S BECOME MANDATORY
DIRECTORY => DIRECTORY IT'S A LOGICAL PART WHICH YOU CREATE IN YOUR DATABASE
TABLES => WHICH TABLE YOU WANT TO EXPORT
DUMPFILE => YOU CAN GIVE ANY WORTHFULL NAME TO YOUR EXPORT FILE .... (.DMP ) IS THE EXTENSION WHICH IS ALSO MANDATORY


C:\> EXPDP HR/HR DIRECTORY=EXPORT_EXAMPLE TABLES=ORACLE_MASTER DUMPFILE=ORM.DMP;

Export: Release 10.2.0.1.0 - Production on Monday, 06 February, 2012 23:46:08

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "HR"."SYS_EXPORT_TABLE_01": HR/******** DIRECTORY=EXPORT_EXAMPLE TABLE
S=ORACLE_MASTER DUMPFILE=ORM.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
. . exported "HR"."ORACLE_MASTER" 23.32 KB 214 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
C:\ROHIT_BLOG\ORM.DMP;
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 23:46:21


STEP -4) AFTER TAKING A EXPORT OF A TABLE OUR NEXT STEP WILL BE DROP THE EXISTNG TABLE NAMED ORACLE_MASTER

SQL> DROP TABLE ORACLE_MASTER PURGE;

Table dropped.

NOW CHECK THE ORACLE_MASTER TABLE IN UR SCHEMA IS IT EXIST OR NOT ??

SQL> SELECT * FROM ORACLE_MASTER;
SELECT * FROM ORACLE_MASTER
*
ERROR at line 1:
ORA-00942: table or view does not exist

NOPES IT DOESNT EXISTING ANY MORE

STEP -5) OPEN THE CMD (COMMAND PROMT)

NOW I'LL IMPORT THE FILE ORM.DMP WHICH EXIST IN MY C:\rohit_blog\orm.dmp

SYNTAX FOR IMPORTING A TABLE
IMPDP USERNAME/PASSWORD@SERVICE DIRECTORY=DIRECTORY_NAME TABLES=TABLE_NAME DUMPFILE=ANY_NAME.DMP

IMP = MEANS YOU WANT TO IMPORT
USER_NAME = ASSIGN THE USER NAME
SERVICE_NAME = NOT NECESSARY BUT IF YOU HAVE MYLTIPLE DATABASE ON SERVER THEN IT'S BECOME MANDATORY
DIRECTORY = DIRECTORY IT'S A LOGICAL PART WHICH YOU CREATE IN YOUR DATABASE
TABLES = WHICH TABLE YOU WANT TO IMPORT
DUMPFILE = SELECT THE DUMPFILE WHO HAVE THE EXPORT OF YOUR TABLE (.DMP ) IS THE EXTENSION WHICH IS ALSO MANDATORY

C:\> IMPDP HR/HR DIRECTORY=EXPORT_EXAMPLE TABLES=ORACLE_MASTER DUMPFILE=ORM.DMP;

Import: Release 10.2.0.1.0 - Production on Monday, 06 February, 2012 23:47:55

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Master table "HR"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": HR/******** DIRECTORY=EXPORT_EXAMPLE TABLE
S=ORACLE_MASTER DUMPFILE=ORM.DMP;
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."ORACLE_MASTER" 23.32 KB 214 rows
Job "HR"."SYS_IMPORT_TABLE_01" successfully completed at 23:47:58


STEP -6) NOW CHECK TABLE NAMED ORACLE_MASTER TABLE IN THE HR SCHEMA HAD WE RECOVER IT OR NOT ?

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
JOY TABLE
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
CORRUPTION TABLE
ORACLE_MASTER TABLE

11 rows selected.

SQL> SELECT COUNT(*) FROM ORACLE_MASTER;

COUNT(*)
----------
214

####### We Recover The ORACLE_MASTER Table Successfully ##########

DONE :-)