重庆思庄Oracle、Redhat认证学习论坛
标题: ORA-25153: Temporary Tablespace is Empty [打印本页]
作者: denglj 时间: 2022-10-24 14:46
标题: ORA-25153: Temporary Tablespace is Empty
问题描述：执行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认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |