三、按用户导出导入测试
这是导入导出中用的最多的一种方式，因此在使用交互方式的时候，默认就是采用了这种方式
SQL> !
[Oracle@ora10g ~]$ exp
Export: Release 10.2.0.1.0 - Production on 11 11:11:42 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: zlm
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Enter array fetch buffer size: 4096 > 8192
Export file: expdat.dmp > zlm.dmp
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > --按用户导出是默认的方式，直接回车即可
Export grants (yes/no): yes >
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified users ...
User to be exported: (RETURN to quit) > zlm
User to be exported: (RETURN to quit) > --指定完需要导出的用户zlm后，直接回车退出配置模式，开始导出
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ZLM
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ZLM
About to export ZLM's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ZLM's tables via Conventional Path ...
. . exporting table T1 201420 rows exported
. . exporting table T2 2 rows exported
. 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 post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@ora10g ~]$ exit
exit
--删除源表，并删除用户zlm
SQL> select count(*) from t1;
COUNT(*)
----------
201420
SQL> select count(*) from t2;
COUNT(*)
----------
2
SQL> drop table t1 purge;
Table dropped.
SQL> drop table t2 purge;
Table dropped.
SQL> drop user zlm cascade;
drop user zlm cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn /as sysdba
Connected.
SQL> drop user zlm cascade;
User dropped.
SQL> !
[oracle@ora10g ~]$ imp
Import: Release 10.2.0.1.0 - Production on 11 11:14:39 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: zlm
Password:
IMP-00058: ORACLE error 1017 encountered --由于zlm用户已经不存在，因此报错
ORA-01017: invalid username/password; logon deniedUsername: system --以system用户登录执行导入
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Import file: expdat.dmp > zlm.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ZLM, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no >
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no >
Username: zlm
Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done:
. importing ZLM's objects into SYSTEM
. . importing table "T1" 201420 rows imported
IMP-00015: following statement failed because the object already exists:
"CREATE TABLE "T2" ("OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(3"
"0), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19)"
", "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" "
"VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" "
"VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL"
" 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) "
"LOGGING NOCOMPRESS"
Import terminated successfully with warnings.
[oracle@ora10g ~]$ exit
exit
虽然是按照用户的方式导出的，但导入之前，还是必须要有相同的用户存在，删除用户以后，是无法进行导入的
--重新创建回zlm用户
SQL> create user zlm identified by zlm;
User created.
SQL> grant dba to zlm;
Grant succeeded.
SQL> select username,default_tablespace from dba_users where username='ZLM';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ZLM USERS --重新创建用户后，没有专门指定缺省表空间，就使用整个数据库的缺省表空间USERS
SQL> !
[oracle@ora10g ~]$ imp
Import: Release 10.2.0.1.0 - Production on 11 11:17:40 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: zlm
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Import file: expdat.dmp > zlm.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no >
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no >
Username: zlm
Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done:
. importing ZLM's objects into ZLM
. . importing table "T1" 201420 rows imported
. . importing table "T2" 2 rows imported
Import terminated successfully without warnings.
[oracle@ora10g ~]$ exit
exit
导入成功了，zlm用户的所有对象都被导入了，即完成了对用户的逻辑恢复
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> show user
USER is "SYS"
SQL> conn zlm/zlm
Connected.
SQL> select count(*) from t1;
COUNT(*)
----------
201420
SQL> select count(*) from t2;
COUNT(*)
----------
2
SQL> select username,default_tablespace from dba_users where username='ZLM';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ZLM USERS
SQL> set line 130
SQL> select owner,table_name,tablespace_name from dba_tables where owner='ZLM';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
ZLM T1 ZLM
ZLM T2 ZLM
尽管zlm用户的默认表空间是USERS，但是用imp导入后的表还是会去找原来的表空间进行恢复，即“ZLM”，那么我们把原来的表空间也删除掉，会怎么样呢？
--删除ZLM表空间后测试恢复情况
SQL> drop tablespace zlm including contents;
Tablespace dropped.
SQL> select ts#,name from v$tablespace;
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
4 USERS
6 EXAMPLE
3 TEMP
6 rows selected.
SQL> col name for a45
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ---------------------------------------------
1 /u01/app/oracle/oradata/ora10g/system01.dbf
2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf
3 /u01/app/oracle/oradata/ora10g/sysaux01.dbf
4 /u01/app/oracle/oradata/ora10g/users01.dbf
5 /u01/app/oracle/oradata/ora10g/example01.dbf
尽管没有使用including contents and datafiles，数据文件还是一起被删除了
SQL> !
[oracle@ora10g ~]$ imp
Import: Release 10.2.0.1.0 - Production on 11 11:25:57 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: zlm
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Import file: expdat.dmp > zlm.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no >
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no > yes
. importing ZLM's objects into ZLM
. . importing table "T1" 201420 rows imported
. . importing table "T2" 2 rows imported
Import terminated successfully without warnings.
[oracle@ora10g ~]$ exit
exit
SQL> select count(*) from t1;
COUNT(*)
----------
201420
SQL> select count(*) from t2;
COUNT(*)
----------
2
SQL> select owner,table_name,tablespace_name from dba_tables where owner='ZLM';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
ZLM T2 USERS
ZLM T1 USERS
当我们把原来zlm用户导出时的表空间ZLM删除以后再倒入，此时会发现imp会把用户导到数据库缺省的USERS表空间上去，基于这种原理，当我们做逻辑导入导出的时候，尽量保持源端与目标端有相同的环境，当然还包括字符集（这里没有做测试）等。否则在导入数据后，可能会产生意外地状况，比方说源库的表空间是很大的，但是由于在目标库中没有创建相应的表空间，默认放到了USERS表空间中，环境和源库产生了不同，可能导入的时候就直接报错了。当然了，字符集更是一个要注意的问题。