Apps DBA or DBA SQL Queries

1. How To Monitor Temporary Tablespace Usage :

CLEAR COLUMNS

SET LINES 180

COL Per_Used FOR a10

COL Per_Free FOR a10

SELECT TABLESPACE,

       ROUND (MB_TOTAL) MB_TOTAL,

       ROUND (MB_USED) MB_USED,

       ROUND (MB_FREE) MB_FREE,

       ROUND ( ( (MB_TOTAL - MB_FREE) * 100) / MB_TOTAL) || '%' Per_Used,

       ROUND ( ( (MB_TOTAL - MB_USED) * 100) / MB_TOTAL) || '%' Per_Free

  FROM (  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);