导入数据前首先要考虑的问题：

1、dump文件的数据库版本，如果dump文件版比目标数据库的版本要高，导入时容易出错。

2、生成dump文件的用户在目标数据库中是否拥有相同的角色和权限。

3、导出的dump文件中是否有LOB数据，分区表，分区索引等等。

正常的导入：

[oracle@localhost ~]$ imp scott/oracle file=/u01/exp_dir/exp_test.dmp

.

import server uses WE8ISO8859P1 character set (possible charset conversion)

. importing SCOTT's objects into SCOTT

. . importing table "EXP_TEST" 14336 rows imported

Import terminated successfully without warnings.

将导出的表导入到指定的用户：

[oracle@localhost ~]$ imp xtt/oracle fromuser=scott touser=xtt file=/u01/exp_dir/exp_test.dmp

.

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8ISO8859P1 character set (possible charset conversion)

. importing SCOTT's objects into XTT

. . importing table "EXP_TEST" 14336 rows imported

Import terminated successfully without warnings.

虽然能导入成功，但是在导入后的表所在的表空间并没有发生变化：

SQL> select username,default_tablespace from user_users;

USERNAME DEFAULT_TABLESPACE

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

XTT EXP_TEST

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

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

EXP_TEST USERS

回收xtt用户的unlimited tablespace权限

SQL> revoke unlimited tablespace from xtt;

Revoke succeeded.

SQL> alter user xtt quota unlimited on exp_test;

User altered.

SQL> conn xtt/oracle

Connected.

删除该表后再次导入：

SQL> drop table exp_test purge;

Table dropped.

[oracle@localhost ~]$ imp xtt/oracle fromuser=scott touser=xtt file=/u01/exp_dir/exp_test.dmp

SQL> conn xtt/oracle

Connected.

SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables;

TABLE_NAME TABLESPACE_NAME

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

EXP_TEST EXP_TEST

导入含有ＬＯＢ类型的数据

exp_test中添加CLOB类型的列

SQL> alter table exp_test

2 add(des clob)

3 /

Table altered.

SQL> commit;

Commit complete.

导出测试数据

[oracle@localhost ~]$ exp scott/oracle tables=exp_test file=/u01/exp_dir/exp_test.d

mp log=/u01/exp_dir/exp_test.log

导入时会报错：

[oracle@localhost ~]$ imp system/oracle fromuser=scott touser=xtt file=/u01/exp_dir/exp_test.dmp ignore=Y

ignore=Y参数会忽略当前的错误提示，继续进行操作

IMP-00017: following statement failed with ORACLE error 1536:

"CREATE TABLE "EXP_TEST" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" "

"VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM"

"" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0), "DES" CLOB) PCTFREE 10 PCTUSED 40 I"

"NITRANS 1 MAXTRANS 255 STORAGE(INITIAL 786432 FREELISTS 1 FREELIST GROUPS 1"

" BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("DES") STO"

"RE AS (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 N"

"OCACHE LOGGING STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_"

"POOL DEFAULT))"

IMP-00003: ORACLE error 1536 encountered

ORA-01536: space quota exceeded for tablespace 'USERS'

连接到xtt用户，手工的创建EXP_TEST表

[oracle@localhost ~]$ sqlplus xtt/oracle

SQL> CREATE TABLE "EXP_TEST" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0), "DES" CLOB)

2 /

Table created.

再次导入：

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8ISO8859P1 character set (possible charset conversion)

. importing SCOTT's objects into XTT

. . importing table "EXP_TEST" 14336 rows imported

Import terminated successfully without warnings.

ROES参数可以指定只导出或导入表结构

将scott的表结构导入xtt用户下：

[oracle@localhost ~]$ exp scott/oracle owner=scott file=/u01/exp_dir/scott_sheam.dmp log=/u01/exp_dir/scott_shame.log

.

. about to export SCOTT's tables via Conventional Path ...

. . exporting table BONUS 0 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table DEPT 4 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table EMP 14 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table EXP_TEST 14336 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table SALGRADE 5 rows exported

.

[oracle@localhost ~]$ imp xtt/oracle full=y file=/u01/exp_dir/scott_sheam.dmp ignore=Y rows=N

import server uses WE8ISO8859P1 character set (possible charset conversion)

. importing SCOTT's objects into XTT

About to enable constraints...

Import terminated successfully without warnings.

xtt用户登入查看一下：

[oracle@localhost ~]$ sqlplus xtt/oracle

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 21 08:55:18 2014

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select tname from tab;

TNAME

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

EXP_TEST

BONUS

DEPT

EMP

SALGRADE

SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables;

TABLE_NAME TABLESPACE_NAME

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

EXP_TEST EXP_TEST

BONUS EXP_TEST

DEPT EXP_TEST

EMP EXP_TEST

SALGRADE EXP_TEST

SQL> select count(*) from dept;

COUNT(*)

----------

0

表结构导入成功。

ITPub博客

IMP逻辑导入

最新文章