Import/Export(??EXP-00091) - 数据库编程

Import/Export工具可以实现：
（1）获取数据库中对象的创建脚本
（2）备份数据（逻辑备份，小规模数据库效率高）
（3）跨平台、跨版本的迁移数据
（4）在多个数据库之间通过传输表空间特性快速复制数据

Export：

首先确保用户有权利导入导出数据，以SYS身份授权给将要执行导出的用户
SQL> show user
USER is "SYS"
SQL> grant exp_full_database to scott;

Grant succeeded.

SQL> grant imp_full_database to scott;

Grant succeeded.

1.导出表中的某些记录：
背景：
SQL> select * from scott.test;

ID
----------
1
2
3
4
5

导出id<3的，理论上只有两条记录。
测试：
[oracle@linux5 ~]$ exp scott/oracle tables=test query=\"where ID\<'3'\" file=/u01/imp_exp/test_less3.dmp log=/u01/imp_exp/test_less3.log

Export: Release 10.2.0.1.0 - Production on Wed Apr 9 00:45:11 2014

About to export specified tables via Conventional Path ...
. . exporting table TEST 2 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
id是number类型，也可以不转义
[oracle@linux5 ~]$ exp scott/oracle tables=test query=\"where ID\<3\" file=/u01/imp_exp/test_less3_2.dmp log=/u01/imp_exp/test_less3_2.log

Export: Release 10.2.0.1.0 - Production on Wed Apr 9 00:47:17 2014

About to export specified tables via Conventional Path ...
. . exporting table TEST 2 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

2.导出某个SCHEMA下的某些表
背景：
SQL> select count(*) from scott.emp;

COUNT(*)
----------
14

SQL> select count(*) from scott.dept;

COUNT(*)
----------
4
测试：
[oracle@linux5 imp_exp]$ exp scott/oracle tables=dept,emp file=/u01/imp_exp/dept_emp.dmp log=/u01/imp_exp/dept_emp.log

Export: Release 10.2.0.1.0 - Production on Wed Apr 9 00:55:37 2014

About to export specified tables via Conventional Path ...
. . exporting table DEPT 4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
也可以通过system用户导出scott用户下面的两个表：
[oracle@linux5 imp_exp]$ exp system/oracle tables=scott.dept,scott.emp file=/u01/imp_exp/dept_emp_2.dmp log=/u01/imp_exp/dept_emp_2.log

Export: Release 10.2.0.1.0 - Production on Wed Apr 9 00:57:51 2014

About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table DEPT 4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
3.导出某几个SCHEMA中的对象
背景：
SQL> conn test/oracle
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EXPFULL TABLE
BIN$8++Yu/zZ6sLgQAB/AQBXxQ==$0 TABLE
BIN$8++Yu/zc6sLgQAB/AQBXxQ==$0 TABLE
BIN$8++Yu/zd6sLgQAB/AQBXxQ==$0 TABLE
BIN$8++Yu/ze6sLgQAB/AQBXxQ==$0 TABLE
BIN$8++Yu/zi6sLgQAB/AQBXxQ==$0 TABLE
TEST TABLE

11 rows selected.

SQL> conn scott/oracle
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST TABLE
EXPFULL TABLE

6 rows selected.
测试：
[oracle@linux5 imp_exp]$ exp system/oracle owner=scott,test file=/u01/imp_exp/scott_test.dmp log=/u01/imp_exp/scott_test.log

Export: Release 10.2.0.1.0 - Production on Wed Apr 9 01:00:47 2014

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
. exporting object type definitions for user TEST
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. 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.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EXPFULL 1653 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE 5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table TEST 5 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST'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.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EXPFULL 1653 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE 5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table TEST 4 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
4.整库导出：必须是拥有DBA角色，或者拥有EXP_FULL_DATABASE角色。
[oracle@linux5 imp_exp]$ exp system/oracle full=y file=/u01/imp_exp/fulldb.dmp log=/u01/imp_exp/fulldb.log

Export: Release 10.2.0.1.0 - Production on Wed Apr 9 01:11:36 2014

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
…………………………………………………………
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.
5.控制是否导出索引、约束、授权、触发器
[oracle@linux5 imp_exp]$ exp scott file=/u01/imp_exp/scott.dmp log=/u01/imp_exp/scott.log GRANTS=N INDEXES=N constraints=n triggers=n

Export: Release 10.2.0.1.0 - Production on Wed Apr 9 01:24:26 2014

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. 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 EXPFULL 1653 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE 5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table TEST 5 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

EXP-00091:客户端执行导出时的语言环境应与服务器端的相同

SQL> select * from v$NLS_PARAMETERS where PARAMETER='NLS_CHARACTERSET';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
WE8ISO8859P1

上面是服务器端的环境变量，接下来设置客户端
[oracle@linux5 ~]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
[oracle@linux5 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1

背景：
SQL> create table testexp as select * from emp;

Table created.
SQL> select count(*) from testexp;

COUNT(*)
----------
14

[oracle@linux5 ~]$ exp scott/oracle tables=testexp query=\"where rownum\<5\"file=/u01/imp_exp/empless5.dmp log=/u01/imp_exp/empless5.log

Export: Release 10.2.0.1.0 - Production on Wed Apr 9 04:43:13 2014

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table TESTEXP 4 rows exported
Export terminated successfully without warnings.
这次没有报EXP-00091，如果还出错就是表的问题。

Import:
导入指定表到其他用户下面：
[oracle@linux5 imp_exp]$ imp test/oracle fromuser=scott touser=test file=/u01/imp_exp/scott.dmp log=/u01/imp_exp/scott_to_test.log

Import: Release 10.2.0.1.0 - Production on Wed Apr 9 19:31:10 2014

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

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)
IMP-00015: following statement failed because the object already exists:
"CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER"
", "COMM" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI"
"TIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) "
" LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME" VARCHAR2(14), "LOC" VAR"
"CHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 6"
"5536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) "
"LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCH"
"AR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUM"
"BER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"
"S 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAU"
"LT) LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLE "EXPFULL" ("PROCESS_ORDER" NUMBER, "DUPLICATE" NUMBER, "DUMP_F"
"ILEID" NUMBER, "DUMP_POSITION" NUMBER, "DUMP_LENGTH" NUMBER, "DUMP_ALLOCATI"
"ON" NUMBER, "COMPLETED_ROWS" NUMBER, "ERROR_COUNT" NUMBER, "ELAPSED_TIME" N"
"UMBER, "OBJECT_TYPE_PATH" VARCHAR2(200), "OBJECT_PATH_SEQNO" NUMBER, "OBJEC"
"T_TYPE" VARCHAR2(30), "IN_PROGRESS" CHAR(1), "OBJECT_NAME" VARCHAR2(500), ""
"OBJECT_LONG_NAME" VARCHAR2(4000), "OBJECT_SCHEMA" VARCHAR2(30), "ORIGINAL_O"
"BJECT_SCHEMA" VARCHAR2(30), "PARTITION_NAME" VARCHAR2(30), "SUBPARTITION_NA"
"ME" VARCHAR2(30), "FLAGS" NUMBER, "PROPERTY" NUMBER, "COMPLETION_TIME" DATE"
", "OBJECT_TABLESPACE" VARCHAR2(30), "SIZE_ESTIMATE" NUMBER, "OBJECT_ROW" NU"
"MBER, "PROCESSING_STATE" CHAR(1), "PROCESSING_STATUS" CHAR(1), "BASE_PROCES"
"S_ORDER" NUMBER, "BASE_OBJECT_TYPE" VARCHAR2(30), "BASE_OBJECT_NAME" VARCHA"
"R2(30), "BASE_OBJECT_SCHEMA" VARCHAR2(30), "ANCESTOR_PROCESS_ORDER" NUMBER,"
" "DOMAIN_PROCESS_ORDER" NUMBER, "PARALLELIZATION" NUMBER, "UNLOAD_METHOD" N"
"UMBER, "GRANULES" NUMBER, "SCN" NUMBER, "GRANTOR" VARCHAR2(30), "XML_CLOB" "
"CLOB, "NAME" VARCHAR2(30), "VALUE_T" VARCHAR2(4000), "VALUE_N" NUMBER, "IS_"
"DEFAULT" NUMBER, "FILE_TYPE" NUMBER, "USER_DIRECTORY" VARCHAR2(4000), "USER"
"_FILE_NAME" VARCHAR2(4000), "FILE_NAME" VARCHAR2(4000), "EXTEND_SIZE" NUMBE"
"R, "FILE_MAX_SIZE" NUMBER, "PROCESS_NAME" VARCHAR2(30), "LAST_UPDATE" DATE,"
" "WORK_ITEM" VARCHAR2(30), "OBJECT_NUMBER" NUMBER, "COMPLETED_BYTES" NUMBER"
", "TOTAL_BYTES" NUMBER, "METADATA_IO" NUMBER, "DATA_IO" NUMBER, "CUMULATIVE"
"_TIME" NUMBER, "PACKET_NUMBER" NUMBER, "OLD_VALUE" VARCHAR2(4000), "SEED" N"
"UMBER, "LAST_FILE" NUMBER, "USER_NAME" VARCHAR2(30), "OPERATION" VARCHAR2(3"
"0), "JOB_MODE" VARCHAR2(30), "CONTROL_QUEUE" VARCHAR2(30), "STATUS_QUEUE" V"
"ARCHAR2(30), "REMOTE_LINK" VARCHAR2(4000), "VERSION" NUMBER, "DB_VERSION" V"
"ARCHAR2(30), "TIMEZONE" VARCHAR2(64), "STATE" VARCHAR2(30), "PHASE" NUMBER,"
" "GUID" RAW(16), "START_TIME" DATE, "BLOCK_SIZE" NUMBER, "METADATA_BUFFER_S"
"IZE" NUMBER, "DATA_BUFFER_SIZE" NUMBER, "DEGREE" NUMBER, "PLATFORM" VARCHAR"
"2(101), "ABORT_STEP" NUMBER, "INSTANCE" VARCHAR2(60)) PCTFREE 10 PCTUSED 4"
"0 INITRANS 10 MAXTRANS 255 STORAGE(INITIAL 393216 FREELISTS 1 FREELIST GROU"
"PS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("XML_C"
"LOB") STORE AS (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVER"
"SION 10 NOCACHE LOGGING STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS "
"1 BUFFER_POOL DEFAULT))"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLE "SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER) P"
"CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIST"
"S 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) LOGGING NOCOM"
"PRESS"
IMP-00015: following statement failed because the object already exists:
"CREATE TABLE "TEST" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
"XTRANS 255 STORAGE

Import/Export(??EXP-00091)(一)