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 :-)



Thanx For Reading My Blog Give Comment's & Suggesstions At The End of The Blog :-)
---------------------------------------------------------------------------------
SEND ME SUGGESTIONS & YOUR ORACLE 9I,10G ERROR'S AND SNAPSHOT'S AT MY
 EMAIL ID = rohitkhurana.k@gmail.com , rohitkhurana.oracle@gmail.com


Regards - Rohit khurana

( ORACLE CERTIFIED ASSOCIATE )

Views: 18769

Tags: 10G, A, EXPORT, HOW, IN, ORACLE, TABLE, TO

Comment by Bhopinder Singh on May 1, 2012 at 3:08am

nice article man

Comment by Madhava Verma Dantuluri on February 21, 2014 at 9:09am

Wonderfully explained putting all steps in order.

Comment by Md. Khorshed Alam yesterday

Nice explanation. Please if possible share about "Migration database along with constraints and all other objects from SQL server 2008 R2 to Oracle 11g R2 Database".

Comment

You need to be a member of Oracle Community to add comments!

Join Oracle Community

Oracle Community On

I'm not a fan of advertising, and so I will not be including any advertisements on OracleCommunity.net. However, managing this community does not come without cost! If you are willing to donate to help pay for the monthly community fees and domain services I accept Bitcoin and PayPal donations.

Donate Bitcoins

Badge

Loading…

Blog Posts

Verify and Fix Corruption in Oracle Databases, Efficiently

Oracle database system stores massive amounts of data, aiming for secure data storage and management. With a number of predefined queries, this database system lets you insert and retrieve data that is stored in multiple tables inside it. For smoother functioning of a database system and its respective databases, your machine must be free of every single issue that is supposed to cause adverse effects on the data. For…

Continue

Posted by Mark Willium on June 3, 2014 at 11:00pm

esProc Enables Oracle JDBC to Speed Up Retrieve Process

Recently, a performance issue occurs in a big detail report, where the original data volume in the two data tables as it involves has exceeded 3 million rows; the data volume that the report can retrieve after filtering remains at round 2 million rows. As we expected before, by using paged retrieve method, this report would present a much higher performance. However, for the fact that the user also needs to…

Continue

Posted by Jim King on May 29, 2014 at 7:00am

Oracle DBaaS in Private Database Cloud workshop

Designing Database as a Service (DBaaS) in own Database Cloud is very comprehensive topic and it requires a lot of things: clear concept and strategy, solid planning, important architecture decisions, considerable involvement of different stakeholders, extra budget, etc. To start talking about this topic I’ll give my own definition of DBaaS which is a managed database service, hosted and offered by DBaaS provider on pay-per-usage basis, which provides access to own database resource with…

Continue

Posted by Kirill Loifman on May 5, 2014 at 12:37am

© 2014   Created by Steve Karam.

Badges  |  Report an Issue  |  Terms of Service