Import/Export工具可以实现： （1）获取数据库中对象的创建脚本 （2）备份数据（逻辑备份，小规模数据库效率高） （3）跨平台、跨版本的迁移数据 （4）在多个数据库之间通过传输表空间特性快速复制数据 Export： 首先确保用户有权利导入导出数据，以SYS身份
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.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
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=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
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.log rows=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
果真只有表结构，没有数据……
