How to Resolve In-Doubt Transactions

In-Doubt Transactions

Distributed transactions perform DML on multiple databases which is a bit more complicated task because the database must coordinate the consitency in those seperate or even perhaps between different DBMSs (like Oracle - MS SQL). To ensure the transaction atomicity, Oracle implements a 2-phase commit mechanism through which the distributed transactions undergo some phases like prepare, commit, forget, etc. This phases constitute the hand-shake mechanism of the distributed transaciton.

However, sometimes things may go wrong (due to some network, system problem or even a reconfiguration of the underlying objects) and one of the phases fails while others are ok. Here, we say that the transaction becomes in-doubt. Normallly this problem should be handled by the RECO process itself, but in some cases this cannot br performed.

 Why RECO cannot perform in some cases?

One of the databases involved in the distributed transaction might be unreachable (netowrk, system issues etc.) while the RECO was trying to resolve the problem (even when retrying to recover). (UNSTUCK)

The lookup tables of the “2-phase commit” mechanism might become inconsistent with the transaction itself.  (STUCK)

Handling UnStuck Transactions

Hopefully, there is no inconsistency between the lookup tables and the transaction and the following code resolves the problem:

To see the waiting transactions -> DBA_2PC_PENDING view.

SQL> select local_tran_id,global_tran_id, state,mixed, commit# from dba_2pc_pending; 97.33.166765     ORCL.781a8889.97.33.166765     prepared                 no    60787107482

Here,  '97.33.166765' is the transaction id of the distributed transaction, which will be used in the following commands.

If the state of the transaction is “prepared” and there is no inconsistency, the transaction can be forced to rollback, or maybe if the underlying problem which caused the in-doubt transaction is resolved the transaction can be forced to commit as follows:

SQL> ROLLBACK FORCE  '97.33.166765' /* ->replace with ur own trx_id */ or SQL> COMMIT FORCE  '97.33.166765' /* ->replace with ur own trx_id */

Note: If the command hangs, go to the “Handling Stuck DBA_2PC_PENDING” section.

If the state of the transaction is “collecting” and you execute the above command, you may see an error like:

ERROR at line 1: ORA-02058: no prepared transaction found with ID 97.33.166765

Execute the following command to purge the transaction

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('97.33.166765'); /* ->replace with ur own trx_id */ PL/SQL procedure successfully completed.

Test to confirm that the transaction has gone

SQL> SELECT * FROM DBA_2PC_PENDING; No rows returned.

The DBA_2PC_PENDING view is a lookup view, so it might be misleading sometimes. The actual transaction entry view is X$KTUXE ([K]ernel [T]ransaction [U]ndo Transa[X]tion [E]ntry).

This view gives info about the state of transactions that require transaction recovery:

X$KTUXE

COLUMN	TYPE	DESCRIPTION
ADDR	RAW	Address of this row in SGA
INDX	NUMBER	Index of this row in SGA
INST_ID	NUMBER	Instance Number
KTUXEUSN	NUMBER	Undo Segement #
KTUXESLT	NUMBER	Slot Number
KTUXESQN	NUMBER	Wrap Number
KTUXERDBF	NUMBER	Relative File
KTUXERDBB	NUMBER	Relative Block
KTUXESCNB	NUMBER	SCN Base prepare/commit
KTUXESCNW	NUMBER	SCN Wrap prepare/commit
KTUXESTA	VARCHAR2(16)	Transaction Status
KTUXECFL	VARCHAR2(24)	Transaction Flags
KTUXEUEL	NUMBER	Link to Coommit List

The concat of KTUXEUSN, KTUXESLT and KTUXESQN gives us the transacion number:

KTUXEUSN. KTUXESLT. KTUXESQN  = 97.33.166765

So, we can query the transaction view like:

SQL> SELECT * FROM X$KTUXE WHERE            KTUXEUSN=97 AND KTUXESLT=33 AND KTUXESQN =166765; No rows returned.

It should return no value, since the transaction has gone...

Handling Stuck Transactions

Our ultimate goal is not seeing the transaction in X$KTUXE table;  and ensuring that the dictionary tables like PENDING_TRANS$ to be consistent with this information.

Stuck transactions can be examined under the below conditions:

Cond 1: DBA_2PC_PENDING view have entries about our transaction but there is no transaction in reality

The condition is that; when we issue select to the dictionary views like the DBA_2PC_PENDING, PENDING_TRANS$, etc. we see the transaction, but the transaction does not exist in X$KTUXE view.

If the state of the  transaction  (in DBA_2PC_PENDING) is committed,  rollback forced or commit forced then it can be cleaned by:

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('97.33.166765');  /* ->replace with ur own trx_id */ PL/SQL procedure successfully completed.

If the state of the  transaction is prepared, we have to clean manually as follows:

SQL> DELETE FROM SYS.PENDING_TRANS$ WHERE LOCAL_TRAN_ID = '97.33.166765'; /* ->replace with ur own trx_id */ SQL> DELETE FROM SYS.PENDING_SESSIONS$ WHERE LOCAL_TRAN_ID ='97.33.166765' ; /* ->replace with ur own trx_id */ SQL> DELETE FROM SYS.PENDING_SUB_SESSIONS$ WHERE LOCAL_TRAN_ID = '97.33.166765'; /* ->replace with ur own trx_id */ SQL> COMMIT;

Note:  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY command does not purge the actual transaction in X$KTUXE view. It touches the dictionary views.

To really purge the transaction, we need a commit or rollback. In the above case, however, there is no real transaction so we don’t need to worry about rollback or commit, all we do is cleaning the dictionary...

Cond 2: DBA_2PC_PENDING view does NOT have entries about our transaction but there IS A transaction

This is something like a orphan transaction that the dictionary is not aware of.

Trying to force commit or rollback this transaction may result in error like below, since the dictionary is not aware:

SQL> ROLLBACK FORCE  '97.33.166765' /* ->replace with ur own trx_id */ ORA-02058: no prepared transaction found with ID 97.33.166765

What we need to do at this point is; recovering our transaction from being an orphan by inserting some dummy records into dictionay tables (so the views...) and then force a rollback or commit:

You do not have to change the parameters in the insert command other than the transaction id.

SQL> ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY; SQL> INSERT INTO PENDING_TRANS$ (LOCAL_TRAN_ID, GLOBAL_TRAN_FMT, GLOBAL_ORACLE_ID, STATE, STATUS, SESSION_VECTOR, RECO_VECTOR, TYPE#, FAIL_TIME,RECO_TIME) VALUES (      '97.33.166765',   /* ->replace with ur own trx_id */      306206,                 'XXXXXXX.12345.1.2.3',       'prepared','P',              hextoraw( '00000001' ),       hextoraw( '00000000' ),       0, sysdate, sysdate ); SQL> INSERT INTO PENDING_SESSIONS$ VALUES (       '97.33.166765',  /* ->replace with ur own trx_id */       1, hextoraw('05004F003A1500000104'),       'C', 0, 30258592, '',       146 );                   COMMIT;

Now, we should be able to rollback or commit.

SQL> ROLLBACK FORCE  '97.33.166765' /* ->replace with ur own trx_id */ or SQL> COMMIT FORCE  '97.33.166765' /* ->replace with ur own trx_id */

Lastly, we remove the dummy entry from the dictionary:

SQL> ALTER SYSTEM ENABLE  DISTRIBUTED RECOVERY; SQL> ALTER SYSTEM SET "_smu_debug_mode" = 4; SQL> COMMIT; SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('97.33.166765');  /* ->replace with ur own trx_id */ SQL> ALTER SYSTEM SET "_smu_debug_mode" = 0; SQL> COMMIT;

Check to see whether the transaction has gone:

SQL> SELECT * FROM X$KTUXE WHERE            KTUXEUSN=97 AND KTUXESLT=33 AND KTUXESQN =166765; No rows returned.

Cond 3: DBA_2PC_PENDING has entry and there is a transaction but COMMIT or ROLLBACK HANGS!

In the situation, where COMMIT FORCE or ROLLBACK FORCE hangs,

Trying to purge the transaction will give an error like:

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('97.33.166765');  /* ->replace with ur own trx_id */ ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SYS.DBMS_TRANSACTION", line 94 ORA-06512: at line 1

Solution: The solution is the combination of Cond1 and Cond2:

First, delete the dictionary entries.

SQL> DELETE FROM SYS.PENDING_TRANS$ WHERE LOCAL_TRAN_ID = '97.33.166765'; /* ->replace with ur own trx_id */ SQL> DELETE FROM SYS.PENDING_SESSIONS$ WHERE LOCAL_TRAN_ID ='97.33.166765' ; /* ->replace with ur own trx_id */ SQL> DELETE FROM SYS.PENDING_SUB_SESSIONS$ WHERE LOCAL_TRAN_ID = '97.33.166765'; /* ->replace with ur own trx_id */ SQL> COMMIT;

Then, insert dummy record, force commit and finally purge the transaction.

SQL> ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY; SQL> INSERT INTO PENDING_TRANS$ (LOCAL_TRAN_ID, GLOBAL_TRAN_FMT, GLOBAL_ORACLE_ID, STATE, STATUS, SESSION_VECTOR, RECO_VECTOR, TYPE#, FAIL_TIME,RECO_TIME) VALUES (      '97.33.166765',   /* ->replace with ur own trx_id */      306206,                 'XXXXXXX.12345.1.2.3',       'prepared','P',              hextoraw( '00000001' ),       hextoraw( '00000000' ),       0, sysdate, sysdate ); SQL> INSERT INTO PENDING_SESSIONS$ VALUES (       '97.33.166765',  /* ->replace with ur own trx_id */       1, hextoraw('05004F003A1500000104'),       'C', 0, 30258592, '',       146 );                   COMMIT; SQL> COMMIT FORCE  '97.33.166765' /* ->replace with ur own trx_id */ SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('97.33.166765');  /* ->replace with ur own trx_id */

Row count of all tables in a particular schema

DBA’s would have definitely come across a situation, where in, we would need the row count of all tables in schema, It could be for validation purpose after an oracle to SQL Server migration as was my case. Here is some code snippet which helped me to accomplish the requirement.

1)

DECLARE
CURSOR C1 IS SELECT owner,TABLE_NAME FROM DBA_TABLES WHERE owner=’NIKU’;
l_query varchar2(300);
l_num pls_integer;
BEGIN
FOR REC IN C1 LOOP
l_query := ‘SELECT COUNT(*) FROM ‘ || REC.owner||’.’||REC.TABLE_NAME;
EXECUTE IMMEDIATE l_query INTO l_num;
dbms_output.put_line(REC.owner||’.’||REC.TABLE_NAME||’:’||l_num);
END loop;
exception
WHEN others THEN
dbms_output.put_line(sqlerrm);
END;
/

2)

Per MOS Note: 335498.1

SET SERVEROUTPUT ON;
DECLARE
CURSOR c_table_names IS
SELECT table_name FROM user_tables order by 1;
no_of_rows NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Table Name’ || CHR(9) || ‘Number of Rows’);
DBMS_OUTPUT.PUT_LINE(‘———-‘ || CHR(9) || ‘————–‘);
FOR tname IN c_table_names LOOP
EXECUTE IMMEDIATE ‘SELECT COUNT(*) FROM ‘ || tname.table_name INTO no_of_rows;
DBMS_OUTPUT.PUT_LINE(tname.table_name || CHR(9) || CHR(9) || no_of_rows);
END LOOP;
END;
/

3)

Per MOS Note: 335498.1 { This method is not recommended by oracle as it uses tab, which is being retained for backward compatibility.

SET SERVEROUTPUT ON;
DECLARE
no_of_rows NUMBER;
no_of_tables NUMBER;
table_name VARCHAR2(100);
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Table Name’ || CHR(9) || ‘Number of Rows’);
DBMS_OUTPUT.PUT_LINE(‘———-‘ || CHR(9) || ‘————–‘);
EXECUTE IMMEDIATE ‘SELECT COUNT(*) FROM tab’ INTO no_of_tables;
FOR table_number IN 1..no_of_tables
LOOP
EXECUTE IMMEDIATE ‘SELECT tname FROM tab WHERE ROWNUM <= ‘ || table_number || ‘ MINUS SELECT tname FROM tab WHERE ROWNUM < ‘ || table_number INTO table_name;
EXECUTE IMMEDIATE ‘SELECT COUNT(*) FROM ‘ || table_name INTO no_of_rows;
DBMS_OUTPUT.PUT_LINE(table_name || CHR(9) || CHR(9) || no_of_rows);
END LOOP;
END;
/

4)

You can also use DBA_TABLES view to check the number if rows visible to optimizer post gathering stats, because optimizer uses the data visible in dictionary rather actual data. If there is huge change in number of actual rows and the data available to optimizer then the the queries may run longer than usual.

select OWNER,TABLE_NAME,NUM_ROWS from dba_tables where owner='&sschema_name' order by 2;

——————————————————————————–

Note:
You might need to tweak it according to your needs.

Hope this helps,

Temporary Segments Are Not Being De-Allocated After a Sort (Doc ID 1039341.6)

Temporary Segments Are Not Being De-Allocated After a Sort To Bottom Description =========== When monitoring the size of the available free space in the TEMPORARY tablespace it does not increased once sorts are finished even with no client connections to the database. They may also be a large number of EXTENTS of type TEMPORARY in the temporary tablespace. Users may also experience ORA-1652 when running transactions. This error implies that there is no free space left. SMON is not cleaning up after waiting for hours and queries are failing. Scope & Application =================== If the TEMPORARY TABLESPACE is of type TEMPORARY, the space is not released until the instance is shutdown. If the TEMPORARY TABLESPACE is of type PERMANENT, then cleanup is performed by SMON after completion of the process using it. A sort segment is created by the first statement that uses a TEMPORARY tablespace for sorting, after startup. These are not released until the database is restarted. The view V$SORT_SEGMENT can be used to see how many of the currently allocated sort segments are being used. If a PERMANENT tablespace is used for sorting, then any temporary segments created are dropped by SMON on completion of the statement, and the space is released for use by other objects. Running the query: select OWNER, SEGMENT_NAME, SEGMENT_TYPE , TABLESPACE_NAME from DBA_SEGMENTS where SEGMENT_TYPE = 'TEMPORARY'; to give results similar to: OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ----- ------------ ------------ --------------- SYS 4.2 TEMPORARY TEMP Note: segment_name and tablepace_name are likely to be different. Then determine the tablespace type by running: select TABLESPACE_NAME, CONTENTS from DBA_TABLESPACES where TABLESPACE_NAME in (<list of tablespaces from above>); to give results similar to: TABLESPACE_NAME CONTENTS ------------------------------ --------- TEMP TEMPORARY TEMP1 PERMANENT The above query will indicate what type of tablespace the database has defined. Explanation =========== If the tablespace in which the TEMPORARY segment resides is of type TEMPORARY then sort segments not being released is normal behavior in 7.3+. ORA-1652 implies that the tablespace is too small. A sort segment is created by the first statement that used a TEMPORARY tablespace for sorting, after startup. A sort segment created in a TEMPOARY tablespace is only released at shutdown. The large number of EXTENTS is caused when the STORAGE clause has been incorrectly calculated. If the tablespace in which the TEMPORARY segment resides is of type PERMANENT, then check that following events are not set in the initalisation parameter file: event="10061 trace name context forever, level 10" event="10269 trace name context forever, level 10" If they are set, comment out the lines and restart database. These two events prevent SMON from cleaning up TEMP segments and coalescing. In Oracle Version 7.0, only event 10061 existed and if set prevented SMON from both cleaning up TEMP segments and coalescing. From oraus.msg: 10061, 00000, "disable SMON from cleaning temp segments 10269, 00000, "Don't do coalesces of free space in SMON" // *Cause: setting this event prevents SMON from doing free space coalesces As user sessions require space for sorting, they will be allocated temporary segment(s) from the user's temporary tablespace. If the user's temporary tablespace is of type PERMANENT and these two events are set, then the session may eventually run out of space and any new sessions requiring space in the same tablespace may fail with "ORA-1652: unable to extend temp segment by %s in tablespace %s". For performance issues, tablespaces marked as temporary allocate extents as needed, but the extents are not released or deallocated after the operation is done. Instead, the extent is simply marked as FREE or available for the next sort operation. This eliminates the need to continuously allocate and deallocate extents after every sort operation, hence gaining performance benefits. To find out if a TEMP segment is FREE or being USED and also in which tablespace the segment resides, query the new dynamic performance view V$SORT_SEGMENT. For example: select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment; >>> DURING the SORT you will see something like this: TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS ---------------- ------------ ----------- ----------- TEMP 590 590 0 >>> AFTER the SORT you will see something like this: TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS --------------- ------------ ------------ ----------- TEMP 590 0 590 This query shows us that during the sort the USED_BLOCK count increased since we are using the sort extents and after the sort is done the USED_BLOCK decreased and FREE_BLOCK count increased because the extents got released back to the sort extent pool. Use the following guidelines to specify DEFAULT STORAGE: Set INITIAL=NEXT.Since a process always writes data equal to SORT_AREA_SIZE to a temporary segment, a good value for the extent size is (n*s + b) where: n is a positive integer s is the value of SORT_AREA_SIZE initialization parameter b is the value of DB_BLOCK_SIZE initialization parameter Using this value optimizes temporary segment usage by allowing sufficient space for a header block and multiple sort run data to be stored in each extent. Specify a PCTINCREASE of zero to ensure that all extents are of the same size. The MAXEXTENTS parameter only affects a temporary segment if the tablespace is a PERMANENT tablespace. Proper calculation of the Default Storage clause parameters will increase performance and maximize use of storage. NOTE: By default when a tablespace is created its contents are set to hold "PERMANENT" objects, (i.e this includes all segments including sort segment). Sort segments residing in PERMANENT tablespace are still cleaned up by SMON as before, after the sort is completed. If a large number of sort segments have been created, SMON may take some time to drop them. To make a tablespace hold only temporary objects we need to set the contents to TEMPORARY, by issuing the following command: ALTER TABLESPACE <TABLESPACE_NAME> TEMPORARY; and verify this with the following query: SVRMGR> select TABLESPACE_NAME,CONTENTS from dba_tablespaces; TABLESPACE_NAME CONTENTS ---------------- --------- SYSTEM PERMANENT RBS PERMANENT TEMP TEMPORARY TOOLS PERMANENT CYN PERMANENT RBSTMP PERMANENT References ========== Note:65973.1 Temporary Tablespace and the Sort Extent Pool Note:61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing Note:35513.1 Removing 'stray' temporary segments Note:50592.1 Extent Sizes for Sort, Direct Load and Parallel Operations Note:102339.1 Temporary Segments: What Happens When a Sort Occurs Note:1069041.6 How to Find Creator of a SORT or TEMPORARY SEGMENT or Users Performing Sorts for Oracle8 and 9 Search Words ============ TEMP TEMPORARY SEGMENTS RELEASED CLEANED PERMANENT SMON ORA-1652 DEALLOCATED ORA-01652 TABLESPACE SORT

Scripts related to TEMP Tablespace

Scripts related to TEMP Tablespace

— Listing of temp segments.—
===========================================================

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


— Temp segment usage per session.—
===========================================================

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

— Check the sessions that use temp tablespace.-
===========================================================

col hash_value for a40
col tablespace for a10
col username for a15
set linesize 132 pagesize 1000

SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM v$session s, v$tempseg_usage u
WHERE s.saddr=u.session_addr
order by u.blocks;


--BTW, v$sort_usage is same as v$tempseg_usage.
--However, the tempspace can be used by any open cursor in that session. The current SQL is not necessary the culprit. In that case, we can check it from v$sql:
===========================================================

col hash_value for 999999999999
select hash_value, sorts, rows_processed/executions
 from v$sql
 where hash_value in (select hash_value from v$open_cursor where sid=7448)
 and sorts > 0
 and PARSING_SCHEMA_NAME='ALEXZENG3'
 order by rows_processed/executions;


To check instance-wise total allocated, total used TEMP for both rac and non-rac
===========================================================

set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB,
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
i.inst_id=ss.inst_id;

Total Used and Total Free Blocks
===========================================================

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks  from gv$sort_segment;

Another Query to check TEMP USAGE
===========================================================

col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

Temporary Tablespace groups
===========================================================

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

select tablespace_name,contents from dba_tablespaces where tablespace_name like '%TEMP%';

select * from dba_tablespace_groups;

Block wise Check
===========================================================

select TABLESPACE_NAME, TOTAL_BLOCKS, USED_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS, FREE_BLOCKS from V$SORT_SEGMENT;

select sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP';

To Check Percentage Usage of Temp Tablespace
===========================================================

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;

To check Used Extents ,Free Extents available in Temp Tablespace
===========================================================

SELECT tablespace_name, extent_size, total_extents, used_extents,free_extents, max_used_size FROM v$sort_segment;

To list all tempfiles of Temp Tablespace
===========================================================

col file_name for a45
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files  order by file_name;

SELECT d.tablespace_name tablespace , d.file_name filename, d.file_id fl_id, d.bytes/1024/1024
size_m
, NVL(t.bytes_cached/1024/1024, 0) used_m, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
FROM
sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v
WHERE (t.file_id (+)= d.file_id)
AND (d.file_id = v.file#); 

To see Temp Space:
===========================================================

SELECT a.tablespace_name,ROUND((c.total_blocks*b.block_size)/1024/1024/1024,2)
"Total Size [GB]",ROUND((a.used_blocks*b.block_size)/1024/1024/1024,2) "Used_size[GB]",
ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024/1024,2) "Free_size[GB]",
ROUND((a.max_blocks*b.block_size)/1024/1024/1024,2) "Max_Size_Ever_Used[GB]",            
ROUND((a.max_used_blocks*b.block_size)/1024/1024/1024,2) "MaxSize_ever_Used_by_Sorts[GB]" ,
ROUND((a.used_blocks/c.total_blocks)*100,2) "Used Percentage"
FROM V$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)
total_blocks FROM dba_temp_files GROUP by tablespace_name) c
WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;


To see top 10 consuming process :
===========================================================

select * from
(SELECT d.tablespace_name,a.sid,a.serial#,a.program,a.module,a.action,a.username "DB Username",a.osuser,ROUND((b.blocks*d.block_size)/1024/1024,2) "Used MB",c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,dba_tablespaces d
WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value AND d.tablespace_name=b.tablespace ORDER BY b.tablespace, b.blocks DESC)
where rownum <=10


Query to check TEMP USAGE :
===========================================================

col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';


To Check Percentage Usage of Temp Tablespace:
===========================================================

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;

To find Sort Segment Usage by a particular User:
===========================================================

SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
order by u.blocks desc;

To find Total Free space in Temp Tablespace :
===========================================================

select tablespace_name , (free_blocks*8)/1024/1024  FreeSpaceInGB,
(used_blocks*8)/1024/1024  UsedSpaceInGB,
(total_blocks*8)/1024/1024  TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%'


Get 10 sessions with largest temp usage :
===========================================================

cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;

Identifying WHO is currently using TEMP Segments :
===========================================================

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
     (select block_size from dba_tablespaces where tablespace_name='TEMP') d
    WHERE b.tablespace = 'TEMP'
    and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    AND (b.blocks*d.block_size)/1048576 > 1024
    ORDER BY b.tablespace, 6 desc;


===========================================================

--Refer below link too
--http://allappsdba.blogspot.in/2012/04/scripts-related-to-temp-tablespace.html

Monitor Temp Usage

REFER below MOS Document..

How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)

CREATE OR REPLACE PROCEDURE TEMP_TEMP_SEG_USAGE_INSERT IS
BEGIN
insert into TEMP_TEMP_SEG_USAGE
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*(select block_size from dba_tablespaces where tablespace_name = b.tablespace) > 1024;
COMMIT;
END;
/

SELECT JOB FROM DBA_JOBS;

JOB
----------
19

BEGIN
DBMS_JOB.ISUBMIT(JOB => 20,
WHAT => 'TEMP_TEMP_SEG_USAGE_INSERT;',
NEXT_DATE => SYSDATE,
INTERVAL => 'SYSDATE + (5/1440)');
COMMIT;
END;
/

-- Periodically query your monitoring table <temporary tablespace name to monitor>_TEMP_SEG_USAGE. 
--    Also monitor space usage of the table as it could  grow very fast depending on job interval; delete rows or truncate table as appropriate.

select * from <temporary tablespace name to monitor>_TEMP_SEG_USAGE;

select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb
from dba_segments
where segment_name='<temporary tablespace name to monitor>_TEMP_SEG_USAGE;';

truncate table <temporary tablespace name to monitor>_TEMP_SEG_USAGE;

select * from temp_temp_seg_usage;

DATE_TIME USERNAME             SID    SERIAL
--------- --------------------- ----- ----
OS_USER                        SPACE_USED
------------------------------ ----------
SQL_TEXT
-----------------------------------------
29-JUN-07 SYS                158    13
sygaw-ca\sygaw                 768
select * from dba_objects order by object_id, object_name


select segment_name, tablespace_name, bytes/ (1024*1024) UsedMb
from dba_segments
where segment_name='TEMP_TEMP_SEG_USAGE';

SEGMENT_NAME
------------------------------------------
TABLESPACE_NAME USEDMB
------------------------------ ----------
TEMP_TEMP_SEG_USAGE
SYSTEM        .0625

truncate table temp_temp_seg_usage;

Table truncated.