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

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

Data Mining Can Help Your Business Get a Competitive Edge

Data mining services are a vital part of business growth. Data mining, sometimes referred as knowledge discovery in databases, is the process of digging through data to discover hidden connections, patterns, and anomalies so that future trends can be predicted. The mined data can help businesses in cutting costs, increasing revenues, reducing risks, improving customer relationships, and more.

The diversity and volume of data that is being captured by companies today is staggering. The…

Continue

Posted by Alvaro Dee on September 29, 2016 at 2:17am

7 Benefits of Amazon Product Data Entry Services

An Optimized Creation, Processing and Maintenance of your Amazon Products’ E-catalog Gives you an Edge in the Competitive eCommerce Market.

When you upload your inventory on one of the world’s biggest online marketplaces, what strategy do you employ to survive the competition put forth by popular brands? You optimize your listings to enhance the discoverability of the product. Amazon Product data entry services ensures your product is available in the virtual…

Continue

Posted by Alvaro Dee on September 6, 2016 at 3:08am

Why you Should Include Push Notifications in your Mobile App?

Push notifications are a display of your application’s icon and a message in the status bar of your device. We stress the importance of including push notifications in your mobile application for three major reasons:-

  1. Push Notifications allow direct marketing to your clients: When a customer downloads your application and enables push notification, it gives you direct…

Continue

Posted by Alvaro Dee on August 23, 2016 at 10:50pm

Amazon Virtual Assistant Services for Efficient e-store Management

Virtual assistant (VA) is a term that has been buzzing around in the business world for quite some time now. Essentially, a VA is skilled administrator who takes care of your technical/creative business support activities while working offsite. If you already have a designated eCommerce store on Amazon seller central or wish to start one, an Amazon VA will help you manage the store and update it regularly with utmost precision.

Amazon VA creates and updates the…

Continue

Posted by Alvaro Dee on August 23, 2016 at 2:56am

© 2016   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service