Expdp & Impdp Related

Export/Import Status:

To see the status of Import: press ctrl+c
import>status
To see the status of Export: press ctrl+c
export>status

Unlike the original exp and imp utilities all data pump ".dmp" and ".log" files are created on the Oracle server, not the client machine.

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

Different Options for Exports/Imports:

expdp help=y

To estimate size without actually taking the dump:
expdp prod7/thword7 tables=OLD_DATA directory=DUMPS_NAHAR estimate_only=y
=>estimated "PROD7"."OLD_DATA" 156.8 GB

Directory creation
SQL> col DIRECTORY_PATH format a60
SQL> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

SQL> Create or replace directory DUMPS_NAHAR3 As '/oracle';
SQL> grant read,write on directory DUMPS_NAHAR3 to prod7;

EXPORT
expdp single table
expdp prod/pass tables=LOGIN directory=DBDUMPS dumpfile=LOGIN_20100620.dmp logfile=LOGIN_20100620_LOG.log

expdp Multiple tables, starting with different name
expdp prod/pass tables=LOGIN_CLIENTS,TAC_FORM_SUMMARY directory=DUMPS_TAHSEEN dumpfile=DUMPS_TAHSEEN_20100301.dmp logfile=DUMPS_TAHSEEN_EXPORT_LOG_20100301.log

expdp Multiple tables, starting with same name - TMS
expdp prod/pass DIRECTORY=DUMPS_NAHAR_NEPTUNE SCHEMAS=prod INCLUDE=TABLE:"like'TR_%'" DUMPFILE=DUMPS_ZAHIDUL_20100308.dmp logfile=DUMPS_ZAHIDUL_EXPORT_LOG_20100308.log

Example - Exclude tables
expdp prod/pass DIRECTORY=DUMPS_TERMINUS_PROD7_20100708 SCHEMAS=PROD EXCLUDE=TABLE:\"IN \(\'OLD_DATA\',\'USER_ACTIVITY\'\)\" DUMPFILE=DUMPS_TERMINUS_PROD_20100708.dmp logfile=DUMPS_TERMINUS_PROD_20100708_LOG_20100308.log

exclude=TABLE:\"='TABLE_NAME'\"
EXCLUDE=TABLE:\"IN \(\'TEMP\',\'TEMP1\'\)\"

Full schema
expdp atiq2/pass schemas=atiq2 directory=DUMPS_NAHAR3 dumpfile=atiq2_nd_20100907.dmp logfile=exp_atiq2_nd_20100907.log

In multiple file
expdp prod7/pass SCHEMAS=prod7 DIRECTORY=data_pump_dir DUMPFILE=saturn_prod7_%U.dmp FILESIZE=5G LOGFILE=saturn_prod7_exp.log


IMPORT
1: TABLE_EXISTS_ACTION=replace
impdp prod/pass DIRECTORY=DUMPS_NAHAR_NEPTUNE SCHEMAS=prod DUMPFILE=dbserver_scema_date.dmp TABLE_EXISTS_ACTION=replace PARALLEL=3

Full schema
impdp prod/pass DIRECTORY=DUMPS_NAHAR_NEPTUNE DUMPFILE=df01_prod_flexdoc_smaruf_nahar_20101012.dmp TABLE_EXISTS_ACTION=replace PARALLEL=3

2: REMAP_SCHEMA
impdp system/pass DIRECTORY=DUMPS_NAHAR4 DUMPFILE=atiq2_nd_20100907.dmp REMAP_SCHEMA=atiq2:states PARALLEL=3

-- REMAP_TABLE: We can't do it in 10g. This is only available in 11g.

3: CONTENT=DATA_ONLY/ METADATA_ONLY
impdp system/pass DIRECTORY=DUMPS_NAHAR CONTENT=DATA_ONLY DUMPFILE=OLD_DATA_20100705.dmp REMAP_SCHEMA=PROD7:NAHAR PARALLEL=3

4: Import on a fresh a schema
impdp states/pass DIRECTORY=DUMPS_NAHAR_NEPTUNE SCHEMAS=states DUMPFILE=states_dump_20100919.dmp PARALLEL=3

--wrong
expdp prod/pass DIRECTORY=DUMPS_TERMINUS_PROD_20100708 SCHEMAS=PROD DUMPFILE=DUMPS_TERMINUS_PROD_20100708.dmp logfile=DUMPS_TERMINUS_PROD_20100708_LOG_20100308.log

--If I wanted to take dump from different directory then I should use like:
DUMPFILE=datadir1:schema1%U.dmp,datadir2:schema2%U.dmp

How to zip & unzip
zip: nahar@mf01:~$ gzip df01_prod7_flexdoc_smaruf_nahar_201001012.dmp
zip: nahar@mf01:~$ gunzip df01_prod7_flexdoc_smaruf_nahar_20101012.zip

More:
www.oracle-dba-online.com
http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

Working Example:

I will export dump from Estern's (db server) prod (db user) and will import this dump in juptr's (db server) nahar (db user).

I have to pass following steps-
Directory Creation -> Export the dump file to that directory of Estern -> Copy the dump file to another machine -> Make a directory to that location where I create the dump -> Impdp .dmp file to that directory of juptr

Step-1: I am searching where the space available to export?

oracle@Estern:~$ df -h

I need to make a physical directory-
oracle@Estern:~$ cd /dump
oracle@Estern:/dump$ mkdir dumps_nahar_20091015

Step-2: Create Directory

oracle@Estern:~$ sqlplus
Enter user-name: s as sysdba

SQL> Create or replace directory dumps_nahar As '/dump/dumps_nahar_20091015';

Testing the view is really created or not-
SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from DBA_DIRECTORIES where DIRECTORY_NAME like '%NAHAR%';

SQL> grant read,write on directory dumps_nahar to prod;

Step-3: Taking the Dump or Export the Dump

expdp prod/password DIRECTORY=dumps_nahar SCHEMAS=prd7 DUMPFILE=nahar_from_Estern_20091015_%U.dmp

Step-4: copy dump from Estern to Jupiter

Now I am coping the dump file from Estern to Juptr's /backup1/dumps_nahar_20091015 location.

bash-3.00$ scp nahar_from_Estern_20091015_01.dmp oracle@juptr:/backup1/dumps_nahar_20091015

--Export from Estern and Copy the dump in juptr done.
--Now I will Import the dump in Juptr's nahar user.

oracle@juptr:~$ sqlplus s as sysdba
Step-5: Want to import the dump at nahar user

If nahar does not exists-
SQL> create user nahar identified by nahar
default tablespace users quota unlimited on users;

SQL> grant dba to nahar;

Step-6: Create Directory where the dump I copied

SQL> Create or replace directory dumps_nahar As '/backup1/dumps_nahar_20091015';

Testing the view is really created or not-
SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from DBA_DIRECTORIES where DIRECTORY_NAME like '%NAHAR%';

SQL> grant read,write on directory dumps_nahar to nahar;

Step-7: Import the dump

impdp system/password DIRECTORY=dumps_nahar SCHEMAS=prd7 DUMPFILE=nahar_from_Estern_20091015_01.dmp REMAP_SCHEMA=prd7:nahar PARALLEL=3

Database Status

How large is the database?

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;

Database Size: 436 GB
Used space: 355 GB
Free space: 81 GB

How many Table's under "Nahar" user?

select count(*) from dba_objects do where do.owner = 'NAHAR'
and lower(object_type)=lower('Table');

Total tables: 490