@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display Sort Segment usage
*  Parameters : 1 - tablespace_name (Default Value - %, Use % as wildcard)
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  03-Jul-12  Vishal Gupta  Added Max columns in the output
*  02-Jul-12  Vishal Gupta  Added tablespace name as input parameter
*  05-Aug-04  Vishal Gupta  First Draft
*/

/************************************
*  INPUT PARAMETERS
************************************/
DEFINE tablespace_name="&&1"  

COLUMN  _TABLESPACE_NAME NEW_VALUE TABLESPACE_NAME NOPRINT

set term off
SELECT UPPER(DECODE('&&TABLESPACE_NAME','','%','&&TABLESPACE_NAME'))   "_TABLESPACE_NAME"
FROM DUAL;
set term on

/************************************
*  CONFIGURATION PARAMETERS
************************************/
DEFINE BYTES_LARGE_FORMAT="99,999,999"
DEFINE BYTES_FORMAT="99,999,999"
--DEFINE BYTES_HEADING="KB"
--DEFINE BYTES_DIVIDER="1024"
DEFINE BYTES_HEADING="MB"
DEFINE BYTES_DIVIDER="1024/1024"
--DEFINE BYTES_HEADING="GB"
--DEFINE BYTES_DIVIDER="1024/1024/1024"

DEFINE ROUND_PRECISION=0
DEFINE STAR=4   --Defines how much %usage is represented by one star on Graph



PROMPT ******************************************************************************
PROMPT * S O R T    S E G M E N T 
PROMPT *
PROMPT * Input Parameters 
PROMPT *  - Tablespace Name = '&&TABLESPACE_NAME'
PROMPT ******************************************************************************

COLUMN seperator            HEADING "!|!|!|!"                              FORMAT a1
COLUMN tablespace_name      HEADING "TablespaceName"                       FORMAT a20
COLUMN inst_id              HEADING "I#"                                   FORMAT 99 
COLUMN current_users        HEADING "Current|Users|(#)"                    FORMAT 9999
COLUMN total_size           HEADING "Total|(&&BYTES_HEADING)"              FORMAT &&BYTES_FORMAT
COLUMN used_size            HEADING "Used|(&&BYTES_HEADING)"               FORMAT &&BYTES_FORMAT
COLUMN free_size            HEADING "Free|(&&BYTES_HEADING)"               FORMAT &&BYTES_FORMAT
COLUMN percent_free         HEADING "%Free"                                FORMAT 999.00
COLUMN percent_used         HEADING "%Used"                                FORMAT 999.00
COLUMN max_total_size       HEADING "Max|Total|(&&BYTES_HEADING)"          FORMAT &&BYTES_FORMAT
COLUMN max_used_size        HEADING "Max|Used|(&&BYTES_HEADING)"           FORMAT &&BYTES_FORMAT
COLUMN max_sort_used_size   HEADING "Max|Used|(BySort)|(&&BYTES_HEADING)"  FORMAT &&BYTES_FORMAT
COLUMN largest_sort_size    HEADING "Largest|Sort|(&&BYTES_HEADING)"       FORMAT &&BYTES_FORMAT
COLUMN max_percent_used     HEADING "Max||%Used"                           FORMAT 999.00
COLUMN max_percent_free     HEADING "Max||%Free"                           FORMAT 999.00
COLUMN free_requests        HEADING "Free|Requests|(#)"                    FORMAT 999,999
COLUMN added                HEADING "Space|Added|(&&BYTES_HEADING)"        FORMAT &&BYTES_LARGE_FORMAT
COLUMN freed                HEADING "Space|Freed|(&&BYTES_HEADING)"        FORMAT &&BYTES_LARGE_FORMAT

COLUMN UsageGraphWidth   NOPRINT old_value UGWidth
COLUMN alloc               HEADING "Alloc|(&&BYTES_HEADING)"    FORMAT 9,999,999
COLUMN used                HEADING "Used&&BYTES_HEADING)"       FORMAT 999,999
COLUMN free                HEADING "Free|(&&BYTES_HEADING)"     FORMAT 999,999
COLUMN pct_Used            HEADING "%Used"                      FORMAT 999
COLUMN pct_Free            HEADING "%Free"                      FORMAT 999
COLUMN MAXSIZE             HEADING "MaxSize|(&&BYTES_HEADING)"  FORMAT 9,999,999
COLUMN Maxfree             HEADING "MaxFree|(&&BYTES_HEADING)"  FORMAT 9,999,999
COLUMN Max_pct_Used        HEADING "MAX|%Used"                  FORMAT 999
COLUMN Max_pct_Free        HEADING "MAX|%Free"                  FORMAT 999
COLUMN "Usage Graph"                                            FORMAT a27
COLUMN "MaxUsage Graph"                                         FORMAT a27


BREAK  ON TABLESPACE_NAME SKIP 2 ON ALLOC ON FREE ON MAXFREE 
COMPUTE SUM LABEL 'Total' OF total_size  used_size  free_size  FORMAT &&BYTES_FORMAT  ON TABLESPACE_NAME

WITH tbs AS
(
SELECT c.tablespace_name
     , c.alloc
     , c.used
     , c.free
     , c.pct_used
     , c.pct_free
     , c.MAXSIZE
     , c.maxfree
     , c.max_pct_used
     , c.max_pct_free
FROM 
( SELECT /*+ CHOOSE */ 
             ts.tablespace_name
           --, ROUND((ts.bytes_used + ts.bytes_free) / &&BYTES_DIVIDER , &&ROUND_PRECISION)                      alloc 
           , ROUND( tf.bytes / &&BYTES_DIVIDER , &&ROUND_PRECISION)                                              alloc 
           , ROUND(ts.bytes_used / &&BYTES_DIVIDER , &&ROUND_PRECISION)                                          used
           , ROUND(ts.bytes_free / &&BYTES_DIVIDER , &&ROUND_PRECISION)                                          free
           , ROUND(( ts.bytes_used  / (ts.bytes_used + ts.bytes_free)) 
                     * 100, &&ROUND_PRECISION)                                                                   pct_Used
           , 100 - ROUND(( ts.bytes_used  / (ts.bytes_used + ts.bytes_free)) * 100, &&ROUND_PRECISION)           pct_Free
           , ROUND( tf.max_bytes / &&BYTES_DIVIDER , &&ROUND_PRECISION)                                          MaxSize
           , ROUND( ( ts.bytes_free  + (tf.max_bytes - (ts.bytes_used + ts.bytes_free) )  
                    ) / &&BYTES_DIVIDER , &&ROUND_PRECISION)                                                     Maxfree
           , ROUND( ( ts.bytes_used /tf.max_bytes) * 100, &&ROUND_PRECISION)                   Max_pct_Used
           , 100  - ROUND( ( ts.bytes_used  /tf.max_bytes )* 100, &&ROUND_PRECISION      )      Max_pct_Free
      FROM  (SELECT tablespace_name
                   , sum(bytes_used) bytes_used
                   , sum(bytes_free) bytes_free
                FROM v$temp_space_header
               WHERE tablespace_name LIKE '&&tablespace_name' 
               GROUP BY tablespace_name
             ) ts
           , (SELECT tablespace_name
                   , SUM(bytes) bytes
                   , SUM(DECODE(autoextensible, 'YES',GREATEST(maxbytes,bytes),'NO', bytes)) max_bytes
                FROM dba_temp_files 
               WHERE tablespace_name LIKE '&&tablespace_name' 
               GROUP BY tablespace_name
             )tf
           , (SELECT ss.tablespace_name
                   , sum(ss.free_blocks * t.block_size) free_bytes
                FROM gv$sort_segment ss
                   , dba_tablespaces t
               WHERE ss.tablespace_name = t.tablespace_name
                 AND ss.tablespace_name LIKE '&&tablespace_name' 
               GROUP BY ss.tablespace_name
              ) ss
           , dba_tablespaces t   
      WHERE  t.tablespace_name = ts.tablespace_name
        AND  t.tablespace_name = tf.tablespace_name
        AND  t.tablespace_name = ss.tablespace_name (+)
        AND  t.tablespace_name LIKE '&&tablespace_name' 
        ) c
)
SELECT s.tablespace_name
     , t.alloc
     --, t.used
     , t.free
     --, t.MAXSIZE
     , t.maxfree
     ,  '|'                                                                 seperator
     , s.inst_id
     , s.current_users                                                      current_users
     ,  '|'                                                                 seperator
     , (s.total_blocks * p.value)/&&BYTES_DIVIDER                           total_size
     , (s.free_blocks  * p.value)/&&BYTES_DIVIDER                           free_size
     , (s.used_blocks  * p.value)/&&BYTES_DIVIDER                           used_size
     , ROUND( (s.used_blocks/s.total_blocks) * 100 , 2)                     percent_used
     --, ROUND( (s.free_blocks/s.total_blocks) * 100 , 2)                   percent_free
     ,  '|'                                                                 seperator
     , (s.max_blocks * p.value)/&&BYTES_DIVIDER                             max_used_size
     , (s.max_used_blocks  * p.value)/&&BYTES_DIVIDER                       max_sort_used_size
     , (s.max_sort_blocks  * p.value)/&&BYTES_DIVIDER                       largest_sort_size
     ,  '|'                                                                 seperator
     , s.free_requests
     , (s.freed_extents * extent_size * p.value)/&&BYTES_DIVIDER            freed
     , (s.added_extents * extent_size * p.value)/&&BYTES_DIVIDER            added
     ,  '|'                                                                 seperator
  FROM gv$sort_segment s
     , v$parameter p
     , tbs t
 WHERE p.name = 'db_block_size'
   AND s.tablespace_name = t.tablespace_name
   AND s.total_blocks <> 0
   AND s.tablespace_name like '&&tablespace_name'
ORDER BY s.tablespace_name
       , s.inst_id
/  

UNDEFINE tablespace_name

@@footer