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

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 on July 22, 2014 at 11:02pm

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

List Building: Reach Out to the Target Audience and Boost Your Marketing Efforts

An accurate, relevant and up-to-date database is the bedrock of a successful email marketing campaign. So it is crucial to create and maintain correct and updated mailing lists that help in reaching out to potential customers and converting them into buyers, thereby generating…

Continue

Posted by Alvaro Dee on April 25, 2016 at 10:43pm

Scope of Data Entry from Images and their Range of Service

We all know that a document's image is valuable only when the information it contains is digitized and stored in a suitable format. Scanned images data entry calls for great amount of accuracy as inaccurate data can influence the entire database. Also, image data entry is a very tedious and time consuming process. Hence, it is best to take the help…

Continue

Posted by Alvaro Dee on March 30, 2016 at 9:30pm

Packt publishing releases "Advanced Oracle PL/SQL Developer Professional Guide-Second edition"

I am pleased to inform you all that Packt publishing has released the book "Advanced Oracle PL/SQL Developer Professional Guide-Second edition" after my second stint with them. This book is for Oracle developers responsible for database management. Readers are expected to have basic knowledge of Oracle Database and the fundamentals of PL/SQL programming. Certification aspirants can use this…

Continue

Posted by Saurabh K. Gupta on February 26, 2016 at 12:57am

Routing Oracle AQ messages using Apache Camel in ServiceMix

I'm working in how to connect Oracle AQ JMS messaging with other systems, using Apache Camel in ServiceMix: https://fcosfc.wordpress.com/2016/02/06/routing-oracle-aq-messages-using-apache-camel-in-servicemix/

I hope it will be useful!

Posted by Paco Saucedo on February 13, 2016 at 2:37am

© 2016   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service