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测试：
[oracle@linux5 ~]$ exp scott/oracle tables=test query=\where id\
export: release 10.2.0.1.0 - production on wed apr 9 00:45:11 2014
copyright (c) 1982, 2005, oracle. all rights reserved.
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)
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\
export: release 10.2.0.1.0 - production on wed apr 9 00:47:17 2014
copyright (c) 1982, 2005, oracle. all rights reserved.
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)
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
copyright (c) 1982, 2005, oracle. all rights reserved.
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)
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
copyright (c) 1982, 2005, oracle. all rights reserved.
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)
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
copyright (c) 1982, 2005, oracle. all rights reserved.
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)
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
copyright (c) 1982, 2005, oracle. all rights reserved.
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)
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.loggrants=n indexes=n constraints=n triggers=n
export: release 10.2.0.1.0 - production on wed apr 9 01:24:26 2014
copyright (c) 1982, 2005, oracle. all rights reserved.
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\
export: release 10.2.0.1.0 - production on wed apr 9 04:43:13 2014
copyright (c) 1982, 2005, oracle. all rights reserved.
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=testfile=/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
copyright (c) 1982, 2005, oracle. all rights reserved.
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(initial 65536 freelists 1 freelist groups 1 buffer_pool
default) logging nocompress
import terminated successfully with warnings.
导入表结构到指定用户：
修改test用户的默认表空间为test：
sql> create user test identified by oracle;
user created.
sql> select username,default_tablespace from user_users;
username default_tablespace
------------------------------ ------------------------------
test users（默认情况）
sql> grant dba to test;
grant succeeded.
sql> create tablespace test
2 datafile '/u01/app/oracle/oradata/orcl/tests.dbf'
3 size 50m;
tablespace created.
sql> alter user test default tablespace test
2 ;
user altered.
sql> select username,default_tablespace from user_users;
username default_tablespace
------------------------------ ------------------------------
test test
测试：
[oracle@linux5 orcl]$ export nls_lang=american_america.we8iso8859p1
[oracle@linux5 orcl]$ echo $nls_lang
american_america.we8iso8859p1
[oracle@linux5 orcl]$ 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 thu apr 10 18:14:00 2014
copyright (c) 1982, 2005, oracle. all rights reserved.
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 dept 4 rows exported
. . exporting table emp 14 rows exported
export terminated successfully without warnings.
导入：
[oracle@linux5 orcl]$ imp test/oracle fromuser=scott touser=test file=/u01/imp_exp/dept_emp.dmp log=/u01/imp_exp/scott_to_test.log
import: release 10.2.0.1.0 - production on thu apr 10 18:14:44 2014
copyright (c) 1982, 2005, oracle. all rights reserved.
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 we8iso8859p1 character set and al16utf16 nchar character set
. importing scott's objects into test
. . importing table dept 4 rows imported
. . importing table emp 14 rows imported
about to enable constraints...
import terminated successfully without warnings.
10g用户的默认表空间是users表空间：
sql> select username,default_tablespace from user_users;
username default_tablespace
------------------------------ ------------------------------
test test
sql> select table_name,tablespace_name from user_tables;
table_name tablespace_name
------------------------------ ------------------------------
emp users
dept users
sql> conn scott/oracle
connected.
sql> select table_name,tablespace_name from user_tables where table_name in ('emp','dept');
no rows selected
sql> select table_name,tablespace_name from user_tables where table_name in ('emp','dept');
table_name tablespace_name
------------------------------ ------------------------------
dept users
emp users
解决：
删除之前的导入数据，显示授权用户指定表空间的存储权限，并回收unlimited tablespace权限：
例如：授予test用户无限操作test表空间权限，并收回ut权限：
sql> conn test/oracle
connected.
sql> drop table emp purge;
table dropped.
sql> drop table dept purge;
table dropped.
sql> select * from tab;
no rows selected
sql> conn / as sysdba
connected.
sql> alter user test quota unlimited on test;
user altered.
sql> revoke unlimited tablespace from test;
revoke succeeded.
[oracle@linux5 orcl]$ imp test/oracle fromuser=scott touser=test file=/u01/imp_exp/dept_emp.dmp log=/u01/imp_exp/scott_to_test.log
import: release 10.2.0.1.0 - production on thu apr 10 18:25:24 2014
copyright (c) 1982, 2005, oracle. all rights reserved.
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 we8iso8859p1 character set and al16utf16 nchar character set
. importing scott's objects into test
. . importing table dept 4 rows imported
. . importing table emp 14 rows imported
about to enable constraints...
import terminated successfully without warnings.
sql> conn test/oracle
connected.
sql> select table_name,tablespace_name from user_tables where table_name in ('emp','dept');
table_name tablespace_name
------------------------------ ------------------------------
dept test
emp test
导入表结构到指定用户：
删除之后重新导入
[oracle@linux5 orcl]$ imp test/oracle fromuser=scott touser=test file=/u01/imp_exp/dept_emp.dmp log=/u01/imp_exp/scott_to_test.logrows=n
import: release 10.2.0.1.0 - production on thu apr 10 18:31:01 2014
copyright (c) 1982, 2005, oracle. all rights reserved.
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 we8iso8859p1 character set and al16utf16 nchar character set
. importing scott's objects into test
about to enable constraints...
import terminated successfully without warnings.
sql> conn test/oracle
connected.
sql> select * from tab;
tname tabtype clusterid
------------------------------ ------- ----------
dept table
emp table
sql> select * from emp;
no rows selected
果真只有表结构，没有数据……