How to release the temporary tablespace in Oracle?

In Oracle databases, temporary tablespace is used to store temporary data, usually for sorting, joining, grouping operations, etc. The release of temporary tablespace can be done through the following steps:

1. current session
2. The sorting usage in the system.

SELECT s.sid, s.serial#, s.username, u.tablespace_name
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr;

1. Terminate a session within the system.

ALTER SYSTEM KILL SESSION '123,123';

1. transaction value

SELECT * FROM v$transaction;

If there are any pending transactions, you can try rolling back or committing them.

1. If there are no active sessions using temporary tablespace and no pending transactions, you can perform the following steps to release the temporary tablespace:

1. First, switch to the tablespace where the temporary tablespace is located.

ALTER TABLESPACE temp;

1. Next, use the ALTER DATABASE DATAFILE statement to shrink or delete the data files of the temporary tablespace. For example, shrink the data file to a specified size:

ALTER DATABASE DATAFILE '/path/to/tempfile.dbf' RESIZE 100M;

Alternatively, delete the data files.

ALTER DATABASE DATAFILE '/path/to/tempfile.dbf' OFFLINE DROP;

1. Finally, you can use the ALTER TABLESPACE statement to delete the temporary tablespace.

DROP TABLESPACE temp INCLUDING CONTENTS;

Note: Before performing the above operation, make sure to back up the database to prevent data loss or damage.