Oracle exp/imp

Oracle exp/imp：三思笔记

Export/import导出导入数据

进行数据迁移，具备跨版本，跨平台，跨字符集的数据迁移，

1 exp

Cmd 》exp，imp，文件保存在执行该命令的机器上。

1.1示例

Exp导出中，可以先获得客户端和服务器端的版本信息，还可以获得要导出的字符集跟schema

Exp导出的是一个dump的二进制文件，

使用dbca创建数据库时，会自动创建执行exp和imp所需的视图和角色，

1 如果使用create database手工创建，则需要执行cataxp.sql或catalog.sql

F:oracleproduct10.2.0client_2RDBMSADMINcatalog.sql

F:oracleproduct10.2.0db_1RDBMSADMINcatalog.sql

该脚本创建执行exp和imp所需要的数据字典和相关视图

创建EXP_FULL_DATABASE角色并授予相应权限，拥有该角色的用户能导出整个db

创建IMP_FULL_DATABASE角色并授予相应权限，拥有该角色的用户能导入整个db

1 授予权限

Grant connect to scott

Grant EXP_FULL_DATABASE to scot---一般使用有dba权限的用户进行exp和imp

尽可能用低版本的exp导出，然后用符合目标db的imp版本导入

1.2 调用方式

Exp help=y

使用参数文件形式导出

例如：创建参数文件parameter.dat

File=f:mydbexpuseredp.dmp

Indexes=n

Buffer20480000

然后通过exp username/password@orcl parfile=parameter.dat

1.3 处理模式

1 整库模式：拥有EXP_FULL_DATABASE，IMP_FULL_DATABASE权限可以进行整库导出导入，对于exp中的full参数

2表空间模式：对于exp中的tablespace参数

3用户模式：owner参数，可以同时导出多个用户

4 表模式：导出或导入指定表或表分区，对应参数tables

1.4 exp 导出

1.4.1 导出表中的某些记录

Parameter.dat

Tables=(scott.emp,scott.emp1)—多个表时，必须都满足query条件

Query=’where deptno>10 and id<>”a”‘

File=exptable.dmp

Log=exptable.log

1.4.2 导出某些schema下的某些表

Tables=(scott.emp)

Query='where deptno>10'

File=exptable.dmp

Log=exptable.log

1.4.3导出某几个schema的对象

Owner=(scott,hr)

File=exptable.dmp

Log=exptable.log

进行导出的时候，不会导出外部表（外部表的数据并不存在oracle中），外部表需要把该数据文件复制到目标路径

1.4.4全库导出

Full=y

File=exptable.dmp

Log=exptable.log

1.4.5 导出index，constraint，授权等

Exp中还有参数：grants，indexes,constraints,triggers默认都为y

这几个参数都是基于整个对象级别，如indexes，要么全要，要么全不要，

Tables=(emp)

Indexes=n

File=exptable.dmp

Log=exptable.log

1.4.6 文件太大，超出os限制

新参数filesize=500m

select sum(bytes)/1024/1024 m from user_segments---- 127.1875

File=exptable.dmp,exptable1.dmp-----多个dmp

Log=exptable.log

查询多个owner下的大小

select owner,sum(bytes)/1024/1024 m from dba_segments

where owner in('SCOTT','HR','SYS')

group by owner

1.4.7 更快导出

常规导出，直接导出

1 常规：默认方式，exp要处理的数据先经过sql select的方式提取，将数据缓存到缓冲池，经由buffer处理后返回给exp客户端，最后写入dump文件

缓存区大小=记录数组大小*记录行最大长度

Create table emp（id number,value varchar2(20)）

行最大长度=20+2+20+2=46，number类型最大长度20,2为定位符，希望缓冲区存放10000记录的话，那buffer的值10000*46

Indexes=n

Buffer=5000000—5m

Compress=n

File=exptable.dmp

Log=exptable.log

1 直接路径导出

Direct=y

Recordlength=65535

直接路径导出：必须在命令行模式或者参数模式下

导出的表中包含lob列，自动切换为常规导出

表空间模式不支持直接路径导出

直接路径导出不再指定query参数

1.5 exp常见问题

1 exp 用compress压缩空间==默认是y

2 导出提示 exp_00091错误：

该错误的通常原因是执行命令的客户端nls_lang参数未设定，或设置有误， select * from v$nls_parameters where parameter='NLS_CHARACTERSET'

NLS_CHARACTERSET ZHS16GBK

然后在exp的命令的客户端这是nls_lang变量

Set nls_lang=AMERICAN_AMERICA.ZHS16GBK

重新执行exp即可

select * from nls_database_parameters t where t.parameter='NLS_CHARACTERSET'

NLS_CHARACTERSET AL32UTF8

select * from nls_instance_parameters;

WINNT> set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

LINUX> export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

select userenv('language') from dual;

AMERICAN_AMERICA.AL32UTF8

$export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

3导出数据时报ora_00904错误

大致是，执行导出的exp的客户端的版本比服务器端的版本要高。不同版本会导致ora-06550，还可鞥含有lob字段

4执行导出时莫名停顿，但dump文件疯涨

可能有外部表的类型引起，exp导出时，只会导出定义，不会导出数据

5 IMP-00010: not a valid export file, header failed verification

导出exp跟导入imp的版本不一样，高版本导出，低版本导入,手动编辑版本号

6 ORA-01455: 转换列溢出整数数据类型

EXP-00008: 遇到 ORACLE 错误 1455ORA-01455: 转换列溢出整数数据类型EXP-00000: 导出终止失败

---低版本的exp导出高版本的

这里10.2导出12.1---报错

方法：

执行下面

1 select 'alter table '||table_name||' allocate extent;'

from user_tables WHERE SEGMENT_CREATED='NO'

2 select * from user_indexes WHERE SEGMENT_CREATED='NO';

3 select * from user_lobs where segment_created='NO';

在1得出的结果执行

alter table RANDY1 allocate extent;

alter table T allocate extent;

alter table BONUS allocate extent;

在查询上面3个语句的，返回值都是0行

然后正常执行导出。

11g后面新加的参数deferred_segment_creation=true

新建的表无记录时，是滞后分配段的，甚至连ddl定义都无法获取，所以exp无法导出。

7 EXP-00008 RA-01406

EXP-00008: ORACLE error 1406 encountered

ORA-01406: fetched column value was truncated

EXP-00000: Export terminated unsuccessfully

11g r2---10g r2

2 imp

2.1 几个问题

1 导出的dump的版本比导入的db版本要高的话，有可能会失败

编辑dump文件的版本信息

2 生成dump文件的用户及该用户拥有的角色，如果导出时是dba用户或用户全库的角色，那么导入的用户也必须要拥有相应的权限

3导出的数据是否用lob类型，分区表，分区索引等，如果有，在导入前做一些工作。。？

2.2 导入指定表到相同用户

Imp在导入表与exp的读取顺序相同

Imp scott/987064@orcl full=y file=xxxx.dmp log=xxxx.log

imp hr/9870641@orcl file=exptableemp1.dmp log=imptableemp1.log ignore=y

Scott拥有dba角色，而hr用户没有，则报错

Grant dba to hr

select * from user_role_privs;

imp hr/9870641@orcl full=y file=exptableemp1.dmp log=imptableemp1.log ignore=y

该操作是假设目标数据库的scott用户的默认表空间与要导入的表的原存储表空间名完全相同的情况下

2.3 导入指定表到不同的用户

Fromuser：源db

Touser：目标db

Imp scott/@ fromuser=hr touser=scott file=xxxx log=xxxx ignore=y

这样导入的可能会发生错误

select username,default_tablespace from user_users

SCOTT YYHHQQ

select table_name,tablespace_name from user_tables

查看被导入的表的表空间对否正确

Imp在导入数据时会先创建表结构，该表结构来于exp目标端的属性，imp在创建表时，会继承源段的属性，于是源端存储在哪个表空间，目标端默认任然会寻找并存储到那个表空间，如果能找到就存储，找不到就报错，

Conn /as sysdba

Alter user hr quota unlimited on yyhhqq;

Revoke unlimited tablespace from hr;

2.4 导入含lob类型的表，且表空间与当前默认的表空间不同

到目标db下，手动创建含lob类型的表

2.5 导入表结构到指定用户

Imp xxx rows=n

2.6 imp常见问题

1 对象已经存在

加参数 ignore=y，自动忽略已经存在的对象

2 记录不规范

来源表和目标表结构不一致，

3 Sequence序列未变

如果存在相同名字，在导入前，删除相同名字的sequence

4 表中的记录被重复导入

在imp前进行检查避免重复

重复后，通过sql手动删除重复的数据

5记录未被完全导入

执行imp指定了参数ignor=y，一旦发现导入的数据不符合当前的约束条件，导入就会报错

Not null，check约束，主键，外键，唯一约束，在日志有详细的输出

imp scott/9870641@orcl full=y file=exptableemp1.dmp log=imptableemp1.log ignore=y

2.7 更快导入

指定buffer参数40960000

参数 commit=y，默认情况下，imp会在每成功导入一张表后，提交，设置commit=y，根据buffer来提交，

Indexes=n，创建index很费时，可以后面自己创建

Indexfile参数，先导入数据，指定一个路径，后面执行脚本来创建索引

IMP-00013: only a DBA can import a file exported by another DBA

6 Imp-00024

Full=y 不能跟formuser同时使用

imp test_hongquan/test_hongquan@192.168.200.20:1521/hongquantest.oracle.com fromuser=test1 touser=test_hongquan full=y file=E:exp est1_schemas.dump log=E:exp est1schemass.log ignore=y

----出错

IMP-00009: abnormal end of export file

comit=yes feedback=1000

Dump文件有损坏？

重新导出一个dump文件

exp SOCIALDIAL/SOCIALDIAL@192.168.2.188/pdborcl.localdomain owner=SOCIALDIAL file=E:expSOCIALDIAL188s.dump log=E:expSOCIALDIAL188.log

imp SOCIALDIAL/globaldial@192.168.200.20:1521/hongquantest.oracle.com fromuser=SOCIALDIAL touser=SOCIALDIAL file=E:expSOCIALDIAL188s.dump log=E:expSOCIALDIAL188s_imp_20.log ignore=y

会导入全部记录，2份数据（前面导入过一次）

imp SOCIALDIAL/globaldial@192.168.200.20:1521/hongquantest.oracle.com full=y file=E:expSOCIALDIAL188s.dump log=E:expSOCIALDIAL188s_imp_20.log ignore=y

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)

　　FROM USER_TABLES u;

　　SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)　　

　　FROM USER_INDEXES u;　

select dbms_metadata.get_ddl('TABLE','EMP','TEST_HONGQUAN') from dual;

---获取创建表/index的语句

---删除oracle用户的对象

DECLARE

-- Local variables here

i INTEGER;

CURSOR cur_objects(obj_type VARCHAR2) IS

SELECT object_name FROM user_objects WHERE object_type IN (obj_type);

obj_name VARCHAR(200);

sql_str VARCHAR(500);

BEGIN

--drop all tables;

OPEN cur_objects('TABLE');

LOOP

FETCH cur_objects

INTO obj_name;

EXIT WHEN cur_objects%NOTFOUND;

dbms_output.put_line('delete table: ' || obj_name);

sql_str := 'drop table ' || obj_name || ' CASCADE CONSTRAINTS ';

EXECUTE IMMEDIATE sql_str;

END LOOP;

CLOSE cur_objects;

--drop all SEQUENCE;

OPEN cur_objects('SEQUENCE');

LOOP

FETCH cur_objects

INTO obj_name;

EXIT WHEN cur_objects%NOTFOUND;

dbms_output.put_line('delete SEQUENCE: ' || obj_name);

sql_str := 'drop SEQUENCE ' || obj_name;

EXECUTE IMMEDIATE sql_str;

END LOOP;

CLOSE cur_objects;

--drop all VIEW;

OPEN cur_objects('VIEW');

LOOP

FETCH cur_objects

INTO obj_name;

EXIT WHEN cur_objects%NOTFOUND;

dbms_output.put_line('delete VIEW: ' || obj_name);

sql_str := 'drop VIEW ' || obj_name || ' CASCADE CONSTRAINTS ';

EXECUTE IMMEDIATE sql_str;

END LOOP;

CLOSE cur_objects;

--drop all FUNCTION;

OPEN cur_objects('FUNCTION');

LOOP

FETCH cur_objects

INTO obj_name;

EXIT WHEN cur_objects%NOTFOUND;

dbms_output.put_line('delete FUNCTION: ' || obj_name);

sql_str := 'drop FUNCTION ' || obj_name;

EXECUTE IMMEDIATE sql_str;

END LOOP;

CLOSE cur_objects;

--drop all PROCEDURE;

OPEN cur_objects('PROCEDURE');

LOOP

FETCH cur_objects

INTO obj_name;

EXIT WHEN cur_objects%NOTFOUND;

dbms_output.put_line('delete PROCEDURE: ' || obj_name);

sql_str := 'drop PROCEDURE ' || obj_name;

EXECUTE IMMEDIATE sql_str;

END LOOP;

CLOSE cur_objects;

--drop all PACKAGE;

OPEN cur_objects('PACKAGE');

LOOP

FETCH cur_objects

INTO obj_name;

EXIT WHEN cur_objects%NOTFOUND;

dbms_output.put_line('delete PACKAGE: ' || obj_name);

sql_str := 'drop PACKAGE ' || obj_name;

dbms_output.put_line(sql_str);

EXECUTE IMMEDIATE sql_str;

END LOOP;

CLOSE cur_objects;

END;

Imp 00017 ora-1659

“IMP-00017: 由于 ORACLE 错误 1659,

导出的dmp文件中初始化分配表空间的值过大造成

1 exp命令加上compress=n

2、使用imp中的indexfile=xxx.sql参数，将导入语句生成sql

INITIAL

$ oerr ora 1658

01658, 00000, "unable to create INITIAL extent for segment in tablespace %s"

// *Cause: Failed to find sufficient contiguous space to allocate INITIAL

// extent for segment being created.

// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the

// tablespace or retry with a smaller value for INITIAL

Imp参数 12c

[oracle@hongquan ~]$ imp help=y

Import: Release 12.1.0.1.0 - Production on Wed Aug 5 02:23:34 2015

You can let Import prompt you for parameters by entering the IMP

command followed by your username/password:

Example: IMP SCOTT/TIGER

Or, you can control how Import runs by entering the IMP command followed

by various arguments. To specify parameters, you use keywords:

Format: IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N

or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default) Keyword Description (Default)

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

USERID username/password FULL import entire file (N)

BUFFER size of data buffer FROMUSER list of owner usernames

FILE input files (EXPDAT.DMP) TOUSER list of usernames

SHOW just list file contents (N) TABLES list of table names

IGNORE ignore create errors (N) RECORDLENGTH length of IO record

GRANTS import grants (Y) INCTYPE incremental import type

INDEXES import indexes (Y) COMMIT commit array insert (N)

ROWS import data rows (Y) PARFILE parameter filename

LOG log file of screen output CONSTRAINTS import constraints (Y)

DESTROY overwrite tablespace data file (N)

INDEXFILE write table/index info to specified file

SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N)

FEEDBACK display progress every x rows(0)

TOID_NOVALIDATE skip validation of specified type ids

FILESIZE maximum size of each dump file

STATISTICS import precomputed statistics (always)

RESUMABLE suspend when a space related error is encountered(N)

RESUMABLE_NAME text string used to identify resumable statement

RESUMABLE_TIMEOUT wait time for RESUMABLE

COMPILE compile procedures, packages, and functions (Y)

STREAMS_CONFIGURATION import streams general metadata (Y)

STREAMS_INSTANTIATION import streams instantiation metadata (N)

DATA_ONLY import only data (N)

VOLSIZE number of bytes in file on each volume of a file on tape

The following keywords only apply to transportable tablespaces

TRANSPORT_TABLESPACE import transportable tablespace metadata (N)

TABLESPACES tablespaces to be transported into database

DATAFILES datafiles to be transported into database

TTS_OWNERS users that own data in the transportable tablespace set

[oracle@hongquan ~]$ exp help=y

Export: Release 12.1.0.1.0 - Production on Wed Aug 5 03:10:06 2015

You can let Export prompt you for parameters by entering the EXP

command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed

by various arguments. To specify parameters, you use keywords:

Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default) Keyword Description (Default)

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

USERID username/password FULL export entire file (N)

BUFFER size of data buffer OWNER list of owner usernames

FILE output files (EXPDAT.DMP) TABLES list of table names

COMPRESS import into one extent (Y) RECORDLENGTH length of IO record

GRANTS export grants (Y) INCTYPE incremental export type--已废除

INDEXES export indexes (Y) RECORD track incr. export (Y)

DIRECT direct path (N) TRIGGERS export triggers (Y)

LOG log file of screen output STATISTICS analyze objects (ESTIMATE)

ROWS export data rows (Y) PARFILE parameter filename

CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)

OBJECT_CONSISTENT transaction set to read only during object export (N)

FEEDBACK display progress every x rows (0)

FILESIZE maximum size of each dump file

FLASHBACK_SCN SCN used to set session snapshot back to

FLASHBACK_TIME time used to get the SCN closest to the specified time

QUERY select clause used to export a subset of a table

RESUMABLE suspend when a space related error is encountered(N)

RESUMABLE_NAME text string used to identify resumable statement

RESUMABLE_TIMEOUT wait time for RESUMABLE

TTS_FULL_CHECK perform full or partial dependency check for TTS

VOLSIZE number of bytes to write to each tape volume

TABLESPACES list of tablespaces to export

TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

TEMPLATE template name which invokes iAS mode export

11.2.0.2.0—cloudapi------- 10.2.0.1.0

Imp到

导入schemas

1 初始准备

select count(*) from user_objects------ 1080

1 查看用户所拥有的表空间

select distinct tablespace_name from dba_segments where owner='GRSV5';

2 查看表空间的大小

select sum(BYTES) /1024/1024 M FROM DBA_data_files where tablespace_name='GRSV5' or tablespace_name='STARHUB' ;

3 查看表空间的创建语句

4 查看用户的权限与默认的表空间

select * from user_role_privs

select * from database_properties;

5 查看用户的创建

CREATE TABLESPACE CLOUDAPI DATAFILE

'/u01/app/oracle/oradata/grs/cloudapi.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

CREATE USER CLOUDAPI

IDENTIFIED BY cloudapi_2014

DEFAULT TABLESPACE CLOUDAPI

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

-- 2 Roles for GRSV5

GRANT RESOURCE TO CLOUDAPI WITH ADMIN OPTION;

GRANT CONNECT TO CLOUDAPI WITH ADMIN OPTION;

ALTER USER CLOUDAPI DEFAULT ROLE ALL;

-- 1 System Privilege for GRSV5

GRANT CREATE JOB TO CLOUDAPI WITH ADMIN OPTION;

-- 1 Tablespace Quota for GRSV5

ALTER USER CLOUDAPI QUOTA UNLIMITED ON CLOUDAPI;

grant create view to CLOUDAPI WITH ADMIN OPTION;

imp CLOUDAPI/cloudapi_2014@grs full=y file=cloudapi_schemas.dump log=cloudapi_schemas1.log ignore=y

select * from user_role_privs;

select * from database_properties;

select distinct tablespace_name from dba_segments where owner='CLOUDAPI';

[oracle@localhost u01]$ imp rcs/Rcs_2013@10.0.1.103:1521/grs full=y file=rcs_schemas.dump log=rcs_schemas_inp.log ignore=y

Import: Release 10.2.0.1.0 - Production on Tue Jul 28 11:14:51 2015

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

IMP-00010: not a valid export file, header failed verification

IMP-00000: Import terminated unsuccessfully

Gnum prod data

-----把压缩的文件，解压

/u01/app/oracle/product/10.2.0/client_1/bin/exp SOCIALDIAL/globaldial@socialsgprod.coha8j0ssdjc.ap-southeast-1.rds.amazonaws.com/socialsg owner=SOCIAL

DIAL file=/home/oracle/gnum_pd_bk/oracledb_`date +%F`.dump log=/home/oracle/gnum_pd_bk/oracledb_`date +%F`.log Buffer=10240000 Direct=y Recordlength

=65535 INDEXES=N CONSTRAINTS=N

[oracle@oracle12c ~]$ tar -xvf 20150415-0000.dump.tgz

tar: Removing leading `/' from member names

/home/oracle/gnum_pd_bk/oracledb_2015-04-15.dump

tar czvfP $ARCHIVE $DUMPFILE >> $LOGFILE 2>&1

tar xvfP 20150415-0000.dump.tar

[oracle@oracle12c ~]$ tar -zxvf 20150415-0000.dump.tgz

tar: Removing leading `/' from member names

/home/oracle/gnum_pd_bk/oracledb_2015-04-15.dump

[oracle@oracle12c ~]$ file 20150415-0000.dump.tgz

20150415-0000.dump.tgz: gzip compressed data, from Unix, last modified: Tue Apr 14 17:01:02 2015

[oracle@oracle12c ~]$ gunzip 20150415-0000.dump.tgz

[root@oracle12c oracle]# tar xvf 20150415-0000.dump.tar

tar: Removing leading `/' from member names

/home/oracle/gnum_pd_bk/oracledb_2015-04-15.dump

[root@oracle12c oracle]# tar xvfP 20150415-0000.dump.tar

/home/oracle/gnum_pd_bk/oracledb_2015-04-15.dump

[root@oracle12c oracle]# cd gnum_pd_bk/

[root@oracle12c gnum_pd_bk]# ll

total 96448

-rw-r--r-- 1 oracle oinstall 98761245 Apr 14 17:01 oracledb_2015-04-15.dump

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

imp SOCIALDIAL/SOCIALDIAL@192.168.2.188:1521/pdborcl.localdomain fromuser=SOCIALDIAL touser=SOCIALDIAL file=oracledb_2015-04-15.dump log=oracledb_2015-04-15gimp.log ignore=y