—- 1.  Check the Existing Temp Tablespace Size and Data file List
col FILE_NAME       format a50
col TABLESPACE_NAME format a15
col BYTES           format 999,999,999,999
col MAXBYTES        format 999,999,999,999
set linesize 1000

SQL> select FILE_NAME,TABLESPACE_NAME, BYTES, MAXBYTES from  dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME            BYTES         MAXBYTES
————————————————– ————— —————- —————-
/u01/app/oracle/oradata/ORATEST/temp01.dbf         TEMP                 601,882,624   34,359,721,984

SQL>

—- 2. Reduce the Temp Tablespace Size

alter database tempfile ‘/u01/app/oracle/oradata/ORATEST/temp01.dbf’ resize 50M;

SQL> alter database tempfile ‘/u01/app/oracle/oradata/ORATEST/temp01.dbf’ resize 50M;

Database altered.

SQL>

—- 3. Add New Data file to the Temp Tablespace

alter tablespace TEMP add tempfile ‘/u01/app/oracle/oradata/ORATEST/temp02.dbf’ size 50M;

SQL> alter tablespace TEMP add tempfile ‘/u01/app/oracle/oradata/ORATEST/temp02.dbf’ size 50M;

Tablespace altered.

SQL>

—- 4. Check the Temp Tablespace Information (Data file & Size)

SQL> select FILE_NAME,TABLESPACE_NAME, BYTES, MAXBYTES from  dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME            BYTES         MAXBYTES
————————————————– ————— —————- —————-
/u01/app/oracle/oradata/ORATEST/temp01.dbf         TEMP                  52,428,800   34,359,721,984
/u01/app/oracle/oradata/ORATEST/temp02.dbf         TEMP                  52,428,800                0

SQL>

—- 5. Switch off AutoExtend off on First Datafile

SQL> alter database tempfile ‘/u01/app/oracle/oradata/ORATEST/temp01.dbf’ AUTOEXTEND OFF;

Database altered.

SQL>

—- 6. Check the Temp Tablespace Information (Data file & Size)
SQL> select FILE_NAME,TABLESPACE_NAME, BYTES, MAXBYTES from  dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME            BYTES         MAXBYTES
————————————————– ————— —————- —————-
/u01/app/oracle/oradata/ORATEST/temp01.dbf         TEMP                  52,428,800                0
/u01/app/oracle/oradata/ORATEST/temp02.dbf         TEMP                  52,428,800                0

SQL>

—- 7. Check the Temp Datafile Usage

SQL 1.
SELECT   SUM (u.blocks * blk.block_size) / 1024 / 1024 “Mb. in sort segments”,
(hwm.MAX * blk.block_size) / 1024 / 1024 “Mb. High Water Mark”
FROM   v$sort_usage u,
(SELECT   block_size
FROM   dba_tablespaces
WHERE   contents = ‘TEMPORARY’) blk,
(SELECT   segblk# + blocks MAX
FROM   v$sort_usage
WHERE   segblk# = (SELECT   MAX (segblk#) FROM v$sort_usage)) hwm
GROUP BY   hwm.MAX * blk.block_size / 1024 / 1024;
SQL 2.

SQL> select tablespace_name,extent_size,total_extents,total_blocks,max_size,max_blocks from v$sort_segment;

SQL> SELECT tablespace_name, file_id, block_id, BYTES/1024/1024, owner FROM v$temp_extent_map

SQL> SELECT s.username, s.sid,  u.TABLESPACE, u.CONTENTS, u.extents, u.blocks FROM v$session s, v$sort_usage u WHERE s.saddr = u.session_addr;

—– 8. Reference Syntex

ALTER DATABASE DATAFILE <‘data_file_name’ | data_file_number>
AUTOEXTEND <OFF | ON [NEXT SIZE <n>K|M|G|T|P|E
MAXSIZE <UNLIMITED | <n>K|M|G|T|P|E>;

ALTER DATABASE DATAFILE ‘u06/oradata/tools01.dbf’ AUTOEXTEND OFF;

ALTER DATABASE DATAFILE <datafile name with complete path> OFFLINE DROP;

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;