//
you're reading...
Database, IT-Center, Oracle

EXPORT / IMPORT ORACLE (ORACLE DUMP)

Create database directories

Execute the following commands to create a database directory. This directory must point to a valid directory on the same server as the database:

Login SQLPlus
1. Start Menu > run > type "cmd" <enter>
2. c:\>set oracle_sid=<oracle_sid>
3. c:\>sqlplus system/<pwd>
SQL> CREATE DIRECTORY dmpdir AS '/opt/oracle';
Directory created.
SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;
Grant succeeded.

PS: Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used:

SQL> SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/app/oracle/product/10.2.0/rdbms/log/

A. EXPORT
=================================================================
example export 1 schema

> expdp hr/hr DIRECTORY=dpump_dir2 DUMPFILE=expfull.dmp FULL=y NOLOGFILE=y
example export 1 schema with log
> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp LOGFILE=hr_export.log
example export multiple schema
> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe

example export single schema without set Directory:
> expdp hr/hr schemas=hr dumpfile=exp.dmp
result export file will be create at oracle/home/admin/db_name/dpdump/
example : D:\oracle\product\10.2.0\admin\orcl\dpdump

example export full service (all schema)
> exp system/manager full=y file=c:\dump.dmp

B. IMPORT
==================================================================
import without set schema (use default schema)
> impdp hr/hr SCHEMAS=HR DIRECTORY=dpump_dir2 LOGFILE=imp.log
DUMPFILE=dpump_dir1:expfull.dmp

import with new shcemas name
> impdp hr/hr SCHEMAS=hr,oe DIRECTORY=dpump_dir1 LOGFILE=schemas.log
DUMPFILE=expdat.dmp

import single schema without set Directory:
> impdp hr/hr schemas=hr dumpfile=exp.dmp
dmp file location must be at oracle/home/admin/db_name/dpdump/
example : D:\oracle\product\10.2.0\admin\orcl\dpdump

C. EXP/IMP different with SID
==================================================================

SQLPLUS /  as sysdba

SQL-S1> CREATE DIRECTORY datapump AS ‘C:\user\datafile\datapump’;
SQL-S1> commit;
SQL-S1> exit

exp data in::comp1 with sid=a with USER=hr

> expdp hr/hr DIRECTORY=dpump DUMPFILE=expdat.dmp SCHEMAS=hr LOGFILE=hr_export.log

imp data in::comp2 with sid=b with USER=hr2

> impdp hr2/hr2 REMAP_SCHEMA=hr:hr2 DIRECTORY=dpump LOGFILE=schemas.log DUMPFILE=expdat.dmp

See Also : http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

One thought on “EXPORT / IMPORT ORACLE (ORACLE DUMP)

  1. mantabsss gan info2 nya (^_^)

    Posted by cyberhigh | February 29, 2012, 2:35 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

December 2011
M T W T F S S
« Nov   Jan »
 1234
567891011
12131415161718
19202122232425
262728293031  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: