首先，我们在一个常用的数据库用户里导出数据
该用户只具有基本的CONNECT,RESOURCE角色
再创建一张测试表，并导出
[oracle@centos5 ~]$ sqlplus imptest/imptest SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 30 15:26:51 2013 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table imp_test as select * from all_objects; Table created. SQL> select count(*) from all_objects; COUNT(*) ---------- 40779 SQL> select tablespace_name,table_name from user_tables; TABLESPACE_NAME TABLE_NAME ------------------------------ ------------------------------ TS_IMPTEST IMP_TEST SQL> select * from user_role_privs; USERNAME GRANTED_ROLE ADM DEF OS_ ------------------------------ ------------------------------ --- --- --- IMPTEST CONNECT NO YES NO IMPTEST RESOURCE NO YES NO SQL> select * from user_sys_privs; USERNAME PRIVILEGE ADM ------------------------------ ---------------------------------------- --- IMPTEST UNLIMITED TABLESPACE NO SQL> select * from session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION UNLIMITED TABLESPACE CREATE TABLE CREATE CLUSTER CREATE SEQUENCE CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE 10 rows selected. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@centos5 ~]$ exp imptest/imptest file=imp_test20130530.dmp tables='imp_test'; Export: Release 10.2.0.4.0 - Production on Thu May 30 15:32:44 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses ZHS16GBK character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table IMP_TEST 40779 rows exported Export terminated successfully without warnings.
创建一个全新用户表空间，测试导入
SQL> create tablespace ts_oa datafile '/home/oracle/oradata/osa/ts_oa.dbf' size 50m; Tablespace created. SQL> create user ts_oa identified by ts_oa default tablespace ts_oa temporary tablespace temp; User created. SQL> grant connect,resource to ts_oa; Grant succeeded.
导入测试
[oracle@centos5 ~]$ imp ts_oa/ts_oa file=imp_test20130530.dmp fromuser=imptest touser=ts_oa tables=imp_test Import: Release 10.2.0.4.0 - Production on Thu May 30 15:50:27 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by IMPTEST, not by you import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing IMPTEST's objects into TS_OA . . importing table "IMP_TEST" 40779 rows imported Import terminated successfully without warnings. [oracle@centos5 ~]$ dba SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 30 15:50:39 2013 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select table_name,tablespace_name from dba_tables where table_name='IMP_TEST'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ IMP_TEST TS_IMPTEST
虽然是导入了，可是确导入了不正确的表空间
[oracle@centos5 ~]$ sqlplus ts_oa/ts_oa SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 30 15:54:32 2013 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from imp_test; COUNT(*) ---------- 40779
之所以去错表空间一个是因为exp数据中有指定表空间信息
另外一个原因是用户具有UNLIMITED TABLESPACE 权限
SQL> drop table imp_test; Table dropped. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@centos5 ~]$ dba SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 30 15:56:10 2013 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select table_name,tablespace_name from dba_tables where table_name='IMP_TEST'; no rows selected SQL>
去掉用户的UNLIMITED TABLESPACE 权限看看
[oracle@centos5 ~]$ dba SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 30 15:56:10 2013 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> revoke unlimited tablespace from ts_oa; Revoke succeeded.
确认用户的unlimited tablespace权限拿掉了
测试导入
[oracle@centos5 ~]$ imp ts_oa/ts_oa file=imp_test20130530.dmp fromuser=imptest touser=ts_oa tables=imp_test Import: Release 10.2.0.4.0 - Production on Thu May 30 16:00:49 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by IMPTEST, not by you import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing IMPTEST's objects into TS_OA IMP-00017: following statement failed with ORACLE error 1950: "CREATE TABLE "IMP_TEST" ("OWNER" VARCHAR2(30) NOT NULL ENABLE, "OBJECT_NAME" "" VARCHAR2(30) NOT NULL ENABLE, "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" " "NUMBER NOT NULL ENABLE, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19)" ", "CREATED" DATE NOT NULL ENABLE, "LAST_DDL_TIME" DATE NOT NULL ENABLE, "TI" "MESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENE" "RATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRAN" "S 1 MAXTRANS 255 STORAGE(INITIAL 5242880 FREELISTS 1 FREELIST GROUPS 1 BUFF" "ER_POOL DEFAULT) LOGGING NOCOMPRESS" IMP-00003: ORACLE error 1950 encountered ORA-01950: no privileges on tablespace 'TS_OA' Import terminated successfully with warnings.
提示在表空间TS_OA上没权限
[oracle@centos5 ~]$ dba SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 30 16:01:50 2013 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter user ts_oa quota 0 on users; --拿掉用户在用户表空间的unlimited权限 User altered. SQL> alter user ts_oa quota unlimited on ts_oa; --给予用于在ts_on表空间的unlimited权限 User altered. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@centos5 ~]$ imp ts_oa/ts_oa file=imp_test20130530.dmp fromuser=imptest touser=ts_oa tables=imp_test Import: Release 10.2.0.4.0 - Production on Thu May 30 16:11:11 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by IMPTEST, not by you import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing IMPTEST's objects into TS_OA . . importing table "IMP_TEST" 40779 rows imported Import terminated successfully without warnings. [oracle@centos5 ~]$ dba SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 30 16:16:04 2013 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select table_name,tablespace_name from dba_tables where table_name='IMP_TEST'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ IMP_TEST TS_OA
数据被正确的导入到了用户表空间
在某些情况可能还是会出现无法导入提示表空间错误
可以vi备份文件，将原表空间名称、用户名称替换成新表空间名称用户名称后再导入
学习是一件很让人兴奋的事情！
浙公网安备 33010602011771号