数据库:oracle 11.2.0.4
系统:centos 7.9
问题描述:expdp导出sys用户下test表空间报错ora-31655,如下所示:
[oracle@leo ~]$ expdp \'/ as sysdba\' directory=ts_expdp dumpfile=ts.dmp logfile=expdp.log tablespaces=test
Export: Release 11.2.0.4.0 - Production on Sun Aug 7 18:11:07 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01": "/******** AS SYSDBA" directory=ts_expdp dumpfile=ts.dmp logfile=expdp.log tablespaces=test
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLESPACE_01" completed with 1 error(s) at Sun Aug 7 18:11:08 2022 elapsed 0 00:00:01
异常原因:
There is a restriction on dataPump export. It cannot export schemas like SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP and WMSYS in any mode.
The Utilities Guide indicates the restriction only on full export mode, but the restriction actually applies to all modes.
解决方案:
使用exp导出.
[oracle@leo ~]$ exp \'/ as sysdba\' file=/home/oracle/ts.dmp tablespaces=test log=logfile.log
Export: Release 11.2.0.4.0 - Production on Sun Aug 7 19:16:32 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.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 AL32UTF8 character set (possible charset conversion)
About to export selected tablespaces ...
For tablespace TEST ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.
现在实现表空间导入.
目标库:oracle 12.1.0.2
系统:rhel 6.8
SQL> create tablespace test logging datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;
Tablespace created.
SQL> create user leo identified by leo default tablespace test temporary tablespace temp;
User created.
SQL> grant connect,resource,dba to leo;
Grant succeeded.
[oracle@leo ~]$ imp \'/ as sysdba\' fromuser=sys touser=leo file=/home/oracle/ts.dmp log=/home/oracle/imp.log ignore=y
Import: Release 12.1.0.2.0 - Production on Sun Aug 7 20:42:49 2022
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
Import terminated successfully without warnings.
expdp导出sys用户下test表空间报错ora-31655
原创
©著作权归作者所有:来自51CTO博客作者Liujun_Deng的原创作品,请联系作者获取转载授权,否则将追究法律责任
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
ORA-65096: 公用用户名或角色名无效oracle oracle用户登录 ORA-65096
-
expdp导sys用户表时报错ORA-39166、ORA-31655
expdp导sys用户表时报错ORA-39166、ORA-31655
ORA-39166 ORA-31655 expdp导sys用户表 -
expdp 导出sys用户下的表报错ORA-39165 和ORA-39166
expdp 导出sys用户下的表报错ORA-39165 和ORA-39166
Oracle expdp sys tab ora-39166 oracle ide 创建表 -
执行expdp全库导出时报错ORA-25153 ORA-39126
执行expdp全库导出时报错ORA-25153 ORA-39126
oracle sql 临时表空间