Solution for Unable to extend temp segment by 128 in table space

Solution for Unable to extend temp segment by 128 in table space:

At least once in a time, A DBA or oracle professional might have faced this error in his/her experience. Here are the workarounds and solutions that we feel work and that can be employed

. Check the temp table space usage using following query

select * From v$temp_space_header;

Dynamic view to list free and used space

If BYTES_FREE column data is 0 then increase the tablespace size (associated db files size)

Check for tablespace in v$tablespace view and either add new file or increase existing file using one of the following statement.

ALTER TABLESPACE TEMP ADD TEMPFILE '..FILEPATH' SIZE XM;

OR

ALTER DATABASE TEMPFILE ' ....' RESIZE XM;

IF one see the same problem happening again and again even after doing resize/add things couple of times continuosuly one can go for below steps ( adding unlimited space to temporary tablespace will not help):

. Create new temporary tablespace with new name

CREATE TEMPORARY TABLESPACE TBLSPACENAME '..' SIZE xM;

CREATE TEMPORARY TABLESPACE temp02 TEMPFILE '/ temp02.dbf' SIZE 5M;

. Make it as a default temporary tablespace using

alter database default temporary tablespace TBLSPCNAME;

Wait for some time till no transactions are using old temporary table space and bring old temp files offline.

ALTER DATABASE TEMPFILE ' ' OFFLINE;

ALTER TABLESPACE temp TEMPFILE OFFLINE

Finally

Drop temp tablespace using

DROP TEMPORARY TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

ALTER DATABASE TEMPFILE 'temp01.dbf' DROP INCLUDING DATAFILES
drop tablespace temp INCLUDING CONTENTS AND DATAFILES;

In summary:

CREATE TEMPORARY TABLESPACE temp TEMPFILE 'temp01.dbf' SIZE 1g;
alter database default temporary tablespace temp;
ALTER TABLESPACE TEMP02 TEMPFILE OFFLINE;
drop tablespace TEMP02 INCLUDING CONTENTS AND DATAFILES;

If still errors, I guess one need to contact on meta-link.