ORACLE import and export

1. Introduction

EXP and IMP are client-side tools that can be used either on the client side or on the server side.

EXPDP and IMPDP are tool programs on the server side. They can only be used on the ORACLE server side, but not on the client side.

IMP only applies to EXP exported files, not EXPDP exported files; IMPDP only applies to EXPDP exported files, not EXP exported files.

2. Expimpl import and export

Note that the form of exp can not be directly exported to empty tables. It needs to be checked before exp is used.

2.1 View empty tables

[root@ifp2 admin]# su oracle
[oracle@ifp2 admin]$ sqlplus / as sysdba

SQL> show parameter deferred_segment_creation;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE

The database parameter deferred_segment_creation=true will cause the dmp backup to fail to export the empty table structure
Execute the following statement, which is valid only for subsequent tables, where no space has been allocated

SQL>  alter system set deferred_segment_creation=false;

Allocate table space for empty tables, splice SQL statements through SQL, and execute them

SQL> select 'alter table ' || table_name || ' allocate extent;' from user_tables  where segment_created = 'NO';

2.2 exp export

• Export database username HM_DEV password 123456 to / u01

[root@ifp2 admin]# exp HM_DEV/123456 owner=HM_DEV file=/u01/HM_DEV_2019_01_17.dmp  full=y;

• Export the tables inner_notify and notify_staff_relat in the database

[root@ifp2 admin]# exp HM_DEV/123456@orcl owner=HM_DEV file=/u01/HM_DEV_2019_01_17.dmp  tables=\(inner_notify, notify_staff_relat\);

• Export the field filed1 of Table 1 in the database with "00" as the starting point

[root@ifp2 admin]# exp HM_DEV/123456@orcl owner=HM_DEV file=/u01/HM_DEV_2019_01_17.dmp tables=\(table1\) query=/" where filed1 like 00%/";

The above is a commonly used export. For compression, using winzip to compress dmp files can be very good. You can also add compress=y after the above command.

2.3 imp Import

• Import file=/u01/HM_DEV_2019_01_17.dmp into database

[root@ifp2 admin]# imp HM_DEV_1/123456@orcl file=HM_DEV_2019_01_17.dmp ignore=y FULL=Y;

• Import file=/u01/HM_DEV_2019_01_17.dmp into tables in the database

[root@ifp2 admin]# imp HM_DEV_1/123456@orcl file=/u01/HM_DEV_2019_01_17.dmp　 tables=(table1);

3. Import and export of expdp and impdpd

ORACLE directory

SQL> select * from dba_directories;
SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';

/data/u01/app/oracle/admin/orcl/dpdump/ 

Export file path/data/u01/app/oracle/admin/orcl/dpdump/

[root@ifp2 admin]# expdp  EIS_DEV/123456@orcl schemas=EIS_DEV directory=data_pump_dir dumpfile=EIS_$(date +%Y%m%d)_DEV.dmp compression=all full=y cluster=n;

3.1 expdp export

• According to User Guide

[root@ifp2 admin]# expdp scott/tiger@orcl schemas=scott directory=data_pump_dir dumpfile=scott.dmp;

• parallel process

[root@ifp2 admin]# expdp scott/tiger@orcl schemas=scott directory=data_pump_dir dumpfile=scott.dmp parallel=40 job_name=scott3;

• Guide by table name

[root@ifp2 admin]# expdp scott/tiger@orcl schemas=scott directory=data_pump_dir dumpfile=expdp.dmp TABLES=emp,dept;

• According to query condition

[root@ifp2 admin]#  expdp scott/tiger@orcl schemas=scott directory=data_pump_dir dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';

• According to table space derivation

[root@ifp2 admin]# expdp system/manager schemas=scott directory=data_pump_dir dumpfile=tablespace.dmp TABLESPACES=temp,example;

• Guide the entire database

[root@ifp2 admin]# expdp system/admin@orcl dumpfile=full.dmp full=y cluster=n;

Sometimes the export will go wrong, saying you can't find the log, and so on. At this point, add cluster=n at the end.

impdp has the exist_table_action parameter, which can be set to replace. This can override imports

3.2 Import of impdpd

• Import to the specified user

For the latter scott2, the system may or may not have, if there is no system will automatically establish this user.

[root@ifp2 admin]#  impdp system/admin@orcl DUMPFILE=scott.dmp REMAP_SCHEMA=scott:scott2; (plus constant error? DIRECTORY=DATA_PUMP_DIR)

• Change the owner of the table

[root@ifp2 admin]# impdp system/manager DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;

• Import table space

[root@ifp2 admin]# impdp system/manager DIRECTORY=DATA_PUMP_DIR DUMPFILE=tablespace.dmp TABLESPACES=example;

• Import database

[root@ifp2 admin]# impdb system/manager DIRECTORY=DATA_PUMP_DIR DUMPFILE=full.dmp FULL=y;

• Additional data

[root@ifp2 admin]#  impdp system/manager DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append;

• Import to common database

[root@ifp2 admin]#   impdp EIS_DEV/123456 directory=data_pump_dir dumpfile=xxx.dmp REMAP_SCHEMA=EIS_DEV:EIS_DEV full=y;