#15

[Oracle] Import/Export as dump

As sys/sysdba :

CREATE OR REPLACE DIRECTORY tmp AS '/tmp';
GRANT READ, WRITE ON DIRECTORY tmp TO <user>;
GRANT IMP_FULL_DATABASE to <user>;

Export :

expdp <user>/<password> directory=tmp dumpfile=export.dmp LOGFILE=export.log

Import :

impdp <user>/<password> directory=tmp dumpfile=export.dmp LOGFILE=import.log TABLE_EXISTS_ACTION=REPLACE

Export Options :
To change the export version :

VERSION={COMPATIBLE | LATEST | version_string}

version_string example : 10.2

Import Options :
To change destination tablespace add :

REMAP_TABLESPACE=<old_tablespace_name>:<new_tablespace_name>

(in this case, the following grant should be performed : grant imp_full_database to YOURUSER;)

In case of multi schema :
REMAP_TABLESPACE=(src1:dst1,src2:dst1,src3:dst1,src4:dst1)

To change destination schema add :

REMAP_SCHEMA=<source_schema>:<target_schema>

If tables already exist in the destination schema :

TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}

Content could be excluded :

EXCLUDE=TABLE:\"like 'IMG_%%'\" EXCLUDE=TABLE:\"IN \(\'EMP\',\'DEPT\'\)\"

Add filter :

QUERY=CSIMESSAGE:\"where MODIFIEDON \< sysdate-90\"

To import only structure (no data)

CONTENT=METADATA_ONLY

Old fashion import :

imp <user>/<password> file=data.dmp log=imp.log full=yes BUFFER=100000 COMMIT=Y FEEDBACK=50000

backup, export, import, oracle

Selenium Hub/Grid shell scripts [Find] How-to

6 thoughts on “[Oracle] Import/Export as dump”

Olivier says:

21/07/2016 at 08:31

To generate SQL file based on dump file :
```
impdp scott/tiger directory=exp_dir dumpfile=scott.dmp sqlfile=script.sql
```
Reply
Olivier says:

01/03/2018 at 13:38

Impdb as sysdba :
```
impdp \"root/password AS sysdba\"
```
Reply
Olivier says:

10/10/2019 at 15:42

to speed up impdp, we can do parallelism :
```
PARALLEL=4
```
Oracle recommends setting “parallel” equal to cpu_count*2 and to tune the parallelism from there.

Reply
Olivier says:

10/10/2019 at 15:56

for large import, increase the “next extent” to avoid lot of IO operations
```
ALTER DATABASE DATAFILE  '/path/to/file.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
```
Reply

Olivier says:

14/10/2019 at 10:15

Usefull SQL to see impdp status :

See current wait :

select v.status, v.sid,v.serial#,io.block_changes,event
from v$sess_io io, v$session v
where io.sid = v.sid
and v.saddr in (
    select saddr
    from dba_datapump_sessions
) order by sid;

See current operation :

select s.sid, s.module, s.state,
       substr(s.event, 1, 21) as event,
       s.seconds_in_wait as secs,
       substr(sql.sql_text, 1, 30) as sql_text
from v$session s
join v$sql sql on sql.sql_id = s.sql_id
where s.module like 'Data Pump%'
order by s.module, s.sid;

OLIVIER COMBE says:

25/02/2021 at 16:22

ORA-31626: job does not exist
ORA-31633: unable to create master table “XXX.SYS_EXPORT_FULL_05”
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT”, line 863
ORA-01031: insufficient privileges
```
grant create table to XXX;
grant unlimited tablespace to XXX;
```
Reply

[Oracle] Import/Export as dump

Related Links :

6 thoughts on “[Oracle] Import/Export as dump”

Leave a Reply to Olivier Cancel reply