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.

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.

EXIT from sqlplus prompt without commit results pending changes are automatically committed

It is often happen to be with anyone who has been working on oracle databases i.e. forgetting committing transaction or roll back changes that we made in current session and quitting with exit command from SQLPLUS prompt without having idea what it make an affect on db. It happens to me a couple of time but never faced any problems as commit take place automatically but things will go wrong when we supposed to rollback changes. Be careful
It always good to check whether there are any pending changes to commit or to rollback before we actually quitting from SQLPLUS prompt because when we exit from SQLPLUS without thinking of those commit and rollback issues, we end up getting those changes permanent in database as EXIT command makes pending changes are automatically committed. Be caution
Exiting by closing the SQLPLUS window by clicking cross mark at right corner of window without issuing an EXIT command will cause rollback get performed and moreover Network failures can also cause ROLLBACK to occur. Quit is similar to exit i.e. makes pending changes are automatically committed.

EXIT from sqlplus prompt without commit results pending changes are automatically committed

It is often happen to be with anyone who has been working on oracle databases i.e. forgetting committing transaction or roll back changes that we made in current session and quitting with exit command from SQLPLUS prompt without having idea what it make an affect on db. It happens to me a couple of time but never faced any problems as commit take place automatically but things will go wrong when we supposed to rollback changes. Be careful
It always good to check whether there are any pending changes to commit or to rollback before we actually quitting from SQLPLUS prompt because when we exit from SQLPLUS without thinking of those commit and rollback issues, we end up getting those changes permanent in database as EXIT command makes pending changes are automatically committed. Be caution
Exiting by closing the SQLPLUS window by clicking cross mark at right corner of window without issuing an EXIT command will cause rollback get performed and moreover Network failures can also cause ROLLBACK to occur. Quit is similar to exit i.e. makes pending changes are automatically committed.