数据库:oracle 11.2.0.4标签:expdp,ts,character,31655,leo,test,报错,oracle From: https://blog.51cto.com/u_12991611/5848003
系统: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.