Quickfix for Oracle TEMP tablespace full issues

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. Sometimes the TEMP gets really big in size and as a quick fix and to buy time to check for the real reason behind this issue we can drop the existing TEMP and recreate the same and assign to the user for use. Even in few cases while importing any big dump into database, the oracle temp tablespace full error shows up like below in logs:

ORA-39002: invalid operation
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS [SELECT old_value FROM "TECHPASTE"."SYS_IMPORT_FULL_01" WHERE process_order = :1]
ORA-01652: unable to extend temp segment by 65536 in tablespace TEMP

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164

----- PL/SQL Call Stack -----
object line object
handle number name
0x9169f3c0 19028 package body SYS.KUPW$WORKER
0x9169f3c0 8191 package body SYS.KUPW$WORKER
0x9169f3c0 8980 package body SYS.KUPW$WORKER
0x9169f3c0 1651 package body SYS.KUPW$WORKER
0x86509390 2 anonymous block

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS [SELECT old_value FROM "TECHPASTE"."SYS_IMPORT_FULL_01" WHERE process_order = :1]
ORA-01652: unable to extend temp segment by 65536 in tablespace TEMP

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164

----- PL/SQL Call Stack -----
object line object
handle number name
0x9169f3c0 19028 package body SYS.KUPW$WORKER
0x9169f3c0 8191 package body SYS.KUPW$WORKER
0x9169f3c0 8980 package body SYS.KUPW$WORKER
0x9169f3c0 1651 package body SYS.KUPW$WORKER
0x86509390 2 anonymous block

Below are the simple steps which can be followed to create a backup oracle temp tablespace and clear the old temp and assign the new one as default to fix errors seen:

You can try to shrink the tablespace but many times we have seen it fails, in that case you can follow dropping TEMP and recreating it.

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

Recreating the TEMP:

sqlplus /nolog

conn /as sysdba;

Check the table spaces in use:

select distinct(tablespace_name) from dba_tablespaces;

Create temp tablespace(Named temp2):

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/data/temp02.dbf' SIZE 1024M REUSE AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Drop current temp space:

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Recreate temp1 as the temp table space:

CREATE TEMPORARY TABLESPACE temp TEMPFILE '/data/temp01.dbf' SIZE 1024M REUSE AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M;

Remap temp1 as the temp tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Drop backup temp space:

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.