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

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

Streamline Your Business Process with Real Estate Data Entry Services

People in the real estate business have to deal with huge volumes of data on a regular basis. It includes the data that is generated from legal documents, property valuation statements, latest commercial and non-commercial property information, etc. This database should be regularly updated and processed properly to help you in your business procedures. With access to relevant real-time data, your business organization can thrive in the commercial real estate market. In…

Continue

Posted by Alvaro Dee on March 27, 2017 at 2:48am

Augment the Speed and Efficiency of Website Development with Joomla

Over the past few years, Joomla has emerged out as one of the greatest platforms for designing, developing, and deploying websites and web applications worldwide. Joomla scales up extremely well, uses less memory and gives a brilliant performance. Hire skilled and experienced Joomla developers to use Joomla CMS for developing simple and complex corporate websites. You can even enhance the functioning of your existing website by using this open source platform.

By outsourcing Joomla…

Continue

Posted by Alvaro Dee on March 20, 2017 at 4:00am

Top 10 Interview Questions & Answers for Java Developers

An organization hiring a Java developer is looking for someone who can code the applications flawlessly. For the period after Java training, the following is a very important list of ten Java interview questions. Make sure you tap the benefits of this comprehensive list of questions shared by our Java counterparts to crack your next interview.

1. What is immutable…

Continue

Posted by Michael Warne on March 17, 2017 at 3:00am

Get the Best Website for Your Business in a Set Time Frame

If you sell products online, you must be knowing how time-consuming and mundane it is to upload products. There are chances of you looking for someone to handle your product data entry tasks. A company that has a wealth of experience and is known for providing its customers with data entry services for a variety of eCommerce solutions can deal efficiently with high volumes of work without any hassle.…

Continue

Posted by Alvaro Dee on March 17, 2017 at 2:47am

© 2017   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service