ExportDP ImportDP

ExportDP and ImportDP
1-Export Full DB
2-Export a specific tablespace
3-Export 30% from your table
4-Create directory and grant permission
5-Export 2 or more tables
6-Export 30% from HR.Employees Table
7-create direct for expdp
8-Import tables from dumpfile
9-Import Full Database
10-display help of expdp/impdp
11-sys and system with expdp
12-export with where clause
13-Export partition table
14-Exclude table from a schema
15-Export with pararell
16-view data pump job
17-display status of oracle export job very 5 monutes
18-Estimating Disk space needed in a table-mode export
19-define size of export file (6GB-->3GB,3GB)
20-export dump file to two difference locations
21-Stop Export while it is running
22- Export with multiple schema
23-Error on Directory and FileName
24-view all object in dumpfile
25-How to import object in one schema to other schema
26-Expdp with parfile
27-Include Procedure Name start with A with your export file
28-Import with  Append

expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe

---

There are many type of export and import in oracle

1-Export Full DB by
expdp username/password Full=Y Directory=impdir Dumpfile=FileName.dmp LogFile=FileName_log.log;

 expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

after it is successfully export it shows Number of rows and size of the export file

----------------------------------------------------------------------------------------------

2-Export a specific tablespace
expdp username/password TableSpaces=TablespaceName     Directory=impdir Dumpfile=FileName1.dmp LogFile=FileName2.log;

expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tbs.dmp  TABLESPACES=tbs_4, tbs_5, tbs_6

3-Export 30% from your table
expdp hr/hr directory=X dumpfile=Xfile.dmp sample=30
expdp hr/hr directory=X dumpfile=Xfile.dmp sample="HR"."Employees":30

4-Create directory and grant permission
create or replace directory Test_dir as '/u01/app/oracle/";
grant read,write on directory Test_dir to hr;

5-Export 2 or more tables
expdp hr/123 tables=t1,t2 directory=Xdir dumpfile=Xfile.dmp logfile=Xlog.log


6-Export 30% from HR.Employees Table

expdp hr/hr directory=Data_pimp DumpFile=sample1.dmp sample=30

expdp hr/hr directory=Data_pimp DumpFile=sample1.dmp sample="HR"."EMPLOYEES":30

 7-create direct for expdp

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';

GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

8-Import tables from dumpfile
impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

9-Import Full Database
impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

 10-display help of expdp
expdp help=y
impdp help=y

11-sys and system with expdp
we can not use user sys, system with expdp

 12-export with where clause

exp scott/tiger tables=emp query="where deptno=10"

exp scott/tiger file=abc.dmp tables=abc query=\"where sex=\'f\'\" rows=yes

13-Export partition table
when you export , if your table is partition, it will export partition one by one, and it also export the index of the table also

14-Exclude table from a schema

expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

15-Export with pararell

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=expdpSCOTT.log

16-view data pump job

DBA_DATAPUMP_JOBS view can be used to monitor the current jobs

select * from dba_datapump_jobs

17-display status of oracle export job very 5 monutes
This example will export the hr and sh schemas and display the status of the export every 5 minutes (60 seconds x 5 = 300 seconds).

expdp hr/hr DIRECTORY=dpump_dir1 SCHEMAS=hr,sh STATUS=300

18-Estimating Disk space needed in a table-mode export

expdp hr directory=dir1 estimate_only=y tables=employees,departments logfile=log1.log

19-define size of export file (6GB-->3GB,3GB)

expdp hr full=y directory=dir1 dumpfile=full%u.dmp filesize=3G parallel=3 logfile=log2.log job_name=j

Break Expdp File in to small Size with order fileName

expdp CRM/CRM@RKDB Tables=CALLDETAIL directory=ExpFolder dumpFile=Exp_CallDetail%U.dmp FILESIZE=10G  logfile=Exp_callDetail.log



20-export dump file to two difference locations

expdp hr full=y dumpfile=dir1:full1%u.dmp, dir2:full2%u.dmp
filesize=3G parallel=3 logfile=log2.log job_name=j

21-Stop Export while it is running
while the export is running , press Ctrl+c
at the Export promt  issue the below command

Export > STOP_JOB=IMMEDIATE

are you sure you wish to stop this job (y/n) : y

When you are inside the session that started the expdp, then just ctrl+C will get you to the EXPDP> prompt from where you can issue KILL_JOB command to kill the session you are in. Issue help for more commands in that control utility.   kill EXPDP Job kill job when you are not in your session expdp dmcore/manager@RKDB attach=SYS_EXPORT_SCHEMA_01 expdp>kill_job impdp username/password@database attach=name_of_the_job Import>kill_job 22-Export Multiple Schema expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe 23-Error on Directory and FileName Error: ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 488 ORA-29283: invalid file operation Mean the directory you input is not correct; Please check your directory location and FileName. Example D\export -->wrong D:\export -->correct expdp hr/123 directory=export tables=hr.employees dumpfile=exp.dmp; expdp hr/123 directory=export tables=hr.employees dumpfile=exp.dmp error no comma at the end of the statements correct 24-view all object in dumpfile impdp system/123 sqlfile=mydll1.sql dumpfile=exp.dmp directory=export this command will export obj in dumpfile to sql file, there is no table created 25-How to import object in one schema to other schema impdp system/123 remap_schema=hr:system tables=departments directory=export dumpfile=exp.dmp old schema: hr--> new schema:system

26-Expdp with parfile

Parfile allow you to include export option in a file

expdp scott/tiger parfile=mypar.par

root> cat mypar.par

directory=data
logfile=t1.log
dumpfile=t1.dmp
include=table:"IN('CUSTOMER','ORDOR)"

27-Include Procedure Name start with A with your export file

In this example, we include stored procedure names that begin with A :
expdp scott/tiger parfile=mypar.par

root> cat mypar.par

directory=data
logfile=t1.log
dumpfile=t1.dmp
INCLUDE=PROCEDURE:"LIKE ‘A%’"

exp.par

--Text inside exp.par

TABLES=Mc.HUY_TEST

query=Mc.HUY_TEST:"Where TO_CHAR(transaction_Date,'yyyy-mm')=to_char(add_months(sysdate,-1),'yyyy-mm')"

directory=MWINGDUMP

expdp system/xxxxx parfile=exp.par dumpfile=HUY_TEST`date --date="1 months ago" +%m%Y`.dmp logfile=HUY_TEST_log_`date --date="1 months ago" +%m%Y`.log  

28-Append Data to existing table
impdp system/xxxx TABLES=Mc.HUY_TEST TABLE_EXISTS_ACTION=APPEND directory=MWINGDUMP dumpfile=HUY_TEST062015.dmp logfile=imp`date +%d%m%y_%H%M`.log

 

29-Export Structure only
expdp system/xxxxxx DIRECTORY=xxxxGDUMP DUMPFILE=DB_structure.dmp schemas=xxING CONTENT=metadata_only