|
问题描述：执行expdp全库导出时报错ORA-25153、ORA-39126，如下所示： 数据库：oracle11.2.0.4 64位 系统：centos7.9 64位 异常现象. SQL>create directory empdp_dir as '/home/oracle/dumpfile'; Directorycreated. [oracle@dbserverdumpfile]$ expdp \'/ as sysdba\' directory=empdp_dir dumpfile=expdp.dmp full=yparallel=8 logfile=expdp.log Export:Release 11.2.0.4.0 - Production on Fri Oct 21 20:49:09 2022 Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction With thePartitioning, OLAP, Data Mining and Real Application Testing options Starting"SYS"."SYS_EXPORT_FULL_05": "/******** AS SYSDBA" directory=empdp_dir dumpfile=expdp.dmpfull=y parallel=8 logfile=expdp.log ORA-39126:Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS [] ORA-25153:Temporary Tablespace is Empty ORA-06512:at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512:at "SYS.KUPW$WORKER", line 9710 -----PL/SQL Call Stack ----- object line object handle number name 0x107a88990 21979 package body SYS.KUPW$WORKER 0x107a88990 9742 package body SYS.KUPW$WORKER 0x107a88990 10573 package body SYS.KUPW$WORKER 0x107a88990 1824 package body SYS.KUPW$WORKER 0x107a8b610 2 anonymous block ORA-39126:Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS [] ORA-25153:Temporary Tablespace is Empty ORA-06512:at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512:at "SYS.KUPW$WORKER", line 9710 -----PL/SQL Call Stack ----- object line object handle number name 0x107a88990 21979 package body SYS.KUPW$WORKER 0x107a88990 9742 package body SYS.KUPW$WORKER 0x107a88990 10573 package body SYS.KUPW$WORKER 0x107a88990 1824 package body SYS.KUPW$WORKER 0x107a8b610 2 anonymous block Job"SYS"."SYS_EXPORT_FULL_05" stopped due to fatal error atFri Oct 21 20:49:16 2022 elapsed 0 00:00:03 异常原因： 临时表空间为空. 处理过程： 查用户默认临时表空间. SQL> select * fromdatabase_properties where property_name ='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ------------------------------ ------------------------------------------------------------ DEFAULT_TEMP_TABLESPACE TEMP1 Name of default temporary tablespace SQL> selectdefault_tablespace,temporary_tablespace,username from dba_users; DEFAULT_TABLESPACE TEMPORARY_TABLESPACE USERNAME ------------------------------------------------------------ ------------------------------ SYSTEM TEMP1 SYS SYSTEM TEMP1 SYSTEM USERS TEMP1 LIUJUN USERS TEMP1 LEO SYSTEM TEMP1 OUTLN SYSTEM TEMP1 MGMT_VIEW SYSAUX TEMP1 FLOWS_FILES SYSAUX TEMP1 MDSYS SYSAUX TEMP1 ORDSYS SYSAUX TEMP1 EXFSYS SYSAUX TEMP1 DBSNMP DEFAULT_TABLESPACE TEMPORARY_TABLESPACE USERNAME ------------------------------------------------------------ ------------------------------ SYSAUX TEMP1 WMSYS SYSAUX TEMP1 APPQOSSYS SYSAUX TEMP1 APEX_030200 SYSAUX TEMP1 OWBSYS_AUDIT SYSAUX TEMP1 ORDDATA SYSAUX TEMP1 CTXSYS SYSAUX TEMP1 ANONYMOUS SYSAUX TEMP1 SYSMAN SYSAUX TEMP1 XDB SYSAUX TEMP1 ORDPLUGINS SYSAUX TEMP1 OWBSYS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE USERNAME ------------------------------------------------------------ ------------------------------ SYSAUX TEMP1 SI_INFORMTN_SCHEMA SYSAUX TEMP1 OLAPSYS USERS TEMP1 SCOTT USERS TEMP1 ORACLE_OCM USERS TEMP1 XS$NULL USERS TEMP1 MDDATA USERS TEMP1 DIP USERS TEMP1 APEX_PUBLIC_USER USERS TEMP1 SPATIAL_CSW_ADMIN_USR USERS TEMP1 SPATIAL_WFS_ADMIN_USR 32 rows selected. 查临时表空间状态. SQL> selecttablespace_name,block_size,status,contents from dba_tablespaces; TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS ------------------------------ ------------------- --------- SYSTEM 8192 ONLINE PERMANENT SYSAUX 8192 ONLINE PERMANENT UNDOTBS1 8192 ONLINE UNDO TEMP 8192 ONLINE TEMPORARY USERS 8192ONLINE PERMANENT UNDOTBS2 8192 ONLINE UNDO TEMP1 8192ONLINE TEMPORARY TEMP01 8192 ONLINE TEMPORARY 8 rows selected. 查临时表空间文件，未发现TEMP1的临时文件. SQL> selecttablespace_name,file_name from dba_temp_files TABLESPACE_NAME FILE_NAME -------------------------------------------------------------------------------- TEMP /data/orcl/temp01.dbf TEMP01 /data/orcl/temp02.dbf 给TEMP1添加临时文件. SQL> alter tablespace temp1 addtempfile '/data/orcl/temp03.dbf' size 10m autoextend on; Tablespace altered. SQL> selecttablespace_name,file_name from dba_temp_files; TABLESPACE_NAME FILE_NAME ------------------------------------------------------------------------------------------ TEMP /data/orcl/temp01.dbf TEMP01 /data/orcl/temp02.dbf TEMP1 /data/orcl/temp03.dbf 再次执行expdp导出作业. [oracle@dbserver~]$ expdp \'/ as sysdba\' directory=empdp_dir dumpfile=expdp.dmp full=yparallel=8 logfile=expdp.log ;;; Export:Release 11.2.0.4.0 - Production on Fri Oct 21 23:46:21 2022 Copyright(c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ;;; Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction With thePartitioning, OLAP, Data Mining and Real Application Testing options Starting"SYS"."SYS_EXPORT_FULL_06": "/******** AS SYSDBA" directory=empdp_dir dumpfile=expdp.dmpfull=y parallel=8 logfile=expdp.log Estimatein progress using BLOCKS method... Processingobject type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Totalestimation using BLOCKS method: 547.9 MB Processingobject type DATABASE_EXPORT/TABLESPACE Processingobject type DATABASE_EXPORT/PROFILE Processingobject type DATABASE_EXPORT/SYS_USER/USER Processingobject type DATABASE_EXPORT/SCHEMA/USER Processingobject type DATABASE_EXPORT/ROLE ……(省略若干) . .exported "ORDDATA"."ORDDCM_MAPPING_DOCS" 7.890 KB 1 rows Mastertable "SYS"."SYS_EXPORT_FULL_06" successfullyloaded/unloaded ****************************************************************************** Dump fileset for SYS.SYS_EXPORT_FULL_06 is: /home/oracle/dumpfile/expdp.dmp Job"SYS"."SYS_EXPORT_FULL_06" successfully completed at FriOct 21 23:50:09 2022 elapsed 0 00:03:47 结论：添加临时文件后，成功执行expdp导出作业.
| 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2022-10-24 14:46:41
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对