【IMPDP】ORA-00959: tablespace 'YOUFEE_LOB' does not exist
来源:互联网 发布:九阴真经女号捏脸数据 编辑:程序博客网 时间:2024/06/05 06:58
今天用IMPDP基于SCHEMA导入数据库时,报错了:
[error]
ORA-39083: Object type TABLE failed tocreate with error:
ORA-00959: tablespace 'YOUFEE_LOB' does not exist
Failing sql is:
CREATE TABLE "YOUFEE"."DEPT"
因为表空间YOUFEE_LOB不存在导致DEPT表无法导入数据库
一般情况下,表会导入到和原来所在表空间名字相同的表空间下,如果target端数据库不存在这个表空间,就会导入到所属schema的默认表空间下。不会因为表空间的问题而报错。
而这里产生这个错误的原因是,DEPT这个表存在于两个表空间(例如有DEPT表有CLOB字段时,CLOB字段存在于另一个表空间)此时,如果target端没有CLOB字段所在的原同名表空间就会报错。
[solution]
1. 事先在target端建好CLOB字段所需的原名表空间
2. 事先在target端建好DEPT表结构,并在impdp导入时使用参数CONTENT=DATA_ONLY
3. 在使用impdp导入时,使用参数remap_Tablespace=YOUFEE_LOB:USERS,将本该存储到YOUFEE_LOB表空间下的数据库映射到已有的USERS表空间下
实验验证:
1. 创建两个表空间和一个用户
SQL> create tablespace youfeedatafile '/u01/app/oracle/oradata/BALLONTT/youfee.dbf'size 10m;
Tablespace created.
SQL> create tablespace youfee_lob datafile '/u01/app/oracle/oradata/BALLONTT/youfee_lob.dbf'size 10m;
Tablespace created.
SQL> create user youfee identified by oracle default tablespace youfee accountunlock;
User created.
SQL> grant resource,connect to youfee;
Grant succeeded.
SQL> conn youfee/oracle
Connected.
SQL> create table emp(id number,ename varchar2(10));
Table created.
SQL> insert into emp values(1,'cat');
1 row created.
SQL> commit;
Commit complete.
SQL> create table dept(dept_id number,infoclob) lob(info) store as (tablespace youfee_lob);
Table created.
SQL> insert into dept values(1,'tiger');
1 row created.
SQL> commit;
Commit complete.
2. 导出youfee用户下的对象
[oracle@ballontt ~]$ expdp system/oracledirectory=dir_dmp dumpfile=youfee.dmp schemas=youfee logfile=youfee.log
[oracle@ballontt ~]$ ls
youfee.dmp youfee.log
3. 删除表emp,dept和存储clob字段的表空间YOUFEE_LOB
SQL> conn youfee/oracle
Connected.
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.
SQL> conn / as sysdba
Connected.
SQL> drop tablespace youfee_lob includingcontents and datafiles;
Tablespace dropped.
4. IMP导入数据库
[oracle@ballontt ~]$ impdp system/oracledirectory=dir_dmp dumpfile=youfee.dmp schemas=youfee logfile=youfee.log
[error]
ORA-39083: Object type TABLE failed tocreate with error:
ORA-00959: tablespace 'YOUFEE_LOB' does notexist
Failing sql is:
CREATE TABLE"YOUFEE"."DEPT"
可以看到,报了上述错误。
5. 解决方法之一(使用remap_tablespace参数)
[oracle@ballontt ~]$ impdp system/oracledirectory=dir_dmp dumpfile=youfee.dmp schemas=youfee logfile=youfee.log remap_tablespace=YOUFEE_LOB:youfee
Processing object typeSCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported"YOUFEE"."DEPT" 5.273 KB 1 rows
Job"SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s)at 20:10:53
DEPT表被导入进去了
ballontt
2013/10/12
---The End---
- 【IMPDP】ORA-00959: tablespace 'YOUFEE_LOB' does not exist
- ORA-00959: tablespace 'PSAPTEMP' does not exist
- ORA-00959: tablespace '' does not exist
- ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
- oracle ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
- ORACLE IMPDP导入提示ORA-01918:user‘XX’ does not exist
- Hibernate: ORA-02289: sequence does not exist
- ORA-29807: specified operator does not exist
- ORA-01919: role 'PLUSTRACE' does not exist
- ORA-04043 object DROP_AW_ELIST_ALL does not exist
- 【ORA】ORA-00030: User session ID does not exist.
- ORA-00942:table or view does not exist
- ORA-00942: table or view does not exist
- ORA-27101: shared memory realm does not exist 一种解决方案
- ORA-00942:table or view does not exist
- ORA-27101: shared memory realm does not exist
- ORA-27101 Shared memory realm does not exist的解决方法
- ORA-00942: table or view does not exist
- MySQL:按前缀批量删除表格
- 【Android】Handler应用(三):从服务器端分页加载更新ListView
- UTF-8使用纯真IP数据库乱码问题(已解决)
- 服务器迁移到虚拟机上
- 1790. Single Round Match
- 【IMPDP】ORA-00959: tablespace 'YOUFEE_LOB' does not exist
- Thinkpad SL400安装黑苹果10.8.4全纪录
- 图像处理经典图片Lena背后的故事
- 测试strncpy与strcpy,strlen 与sizeof的区别
- MySQL:复制/同步数据库/表的简单脚本
- android是如何加载资源图片的?
- android service 生命周期
- project manage_maven_android
- 二维数组的最长递减子序列