/* ********************************************* */
/*  c:\oracle\sql\utlbstat.sql                   */
/* ********************************************* */

spool utlbstat.rslt 

set linesize 80;
set pagesize 1000;
set long 50;
set echo on;

break on "TBL SPACE" on "TOTAL BYTES";

/* ******************************************************************* */
/*                                                                     */
/* this script will show the space usage for the database in question  */
/*       script will perform the following functions:                  */
/*          1. show the tablespaces, size and percent used             */
/*          2. the total free space still available in each tablespace */
/*          3. the free space blocks in each of the tablespaces        */
/*          4. the total free space still available in each tablespace */
/*          5. the usage by each user segment, the inital extent size  */
/*             and the size of the next extents                        */
/*             - ordered by segment name                               */
/*          6. the usage by each user segment, the inital extent size  */
/*             and the size of the next extents                        */
/*             - ordered by tablespace name                            */
/*          7. the usage by each user segment, the inital extent size  */
/*             and the size of the next extents                        */
/*             - ordered by segment size                               */
/*          8. show the size of the entire database                    */
/*                                                                     */
/* ******************************************************************* */

/* **************************** */
/*  show date & user_id         */
/* **************************** */

select sysdate,
       user 
from dual;

/* **************************** */
/*  show tablespace usage 1     */
/* **************************** */

select  substr(D.tablespace_name, 1, 10)                                "TBL SPACE", 
	substr(to_char(sum(D.bytes), '999,999,999,999'), 1, 16)         "TOTAL BYTES",
	substr(to_char(sum(D.bytes)/1024/1024, '999,999.9'), 1, 10)     "TOT MBYTES",
	substr(to_char(sum(D.bytes)/2048, '99,999,999'), 1, 11)	        "BLOCKS",
	substr(to_char(sum(E.blocks), '99,999,999'), 1, 11)	                "TOT BLKS",
	substr(to_char(round(sum(E.blocks)/(sum(D.bytes)/2048),4)* 100, '99.99'), 1, 6) "PCT"
from
	sys.dba_extents    E,
	sys.dba_data_files D
where
	D.file_id = E.file_id (+)
group by
	D.tablespace_name;
	
/* **************************** */
/*  show tablespace usage 2     */
/* **************************** */

select  distinct substr(tablespace_name, 1, 10) 	"TBL SPACE",  
	substr(to_char(sum(blocks), '999,999,999'), 1, 12)	        "TOT BLKS",
	substr(to_char(sum(bytes), '999,999,999,999'), 1, 16)	        "TOT BYTES"
from    sys.dba_free_space 
group by 
	tablespace_name;

/* **************************** */
/*  show tablespace usage 3     */
/* **************************** */

select  substr(tablespace_name,1,10)  		              "TBL SPACE",
	substr(to_char(file_id, '99'), 1, 4)                  "ID", 
	substr(to_char(count(*), '99'), 1, 3)                 "PCS", 
	substr(to_char(max(blocks), '9,999,999'), 1, 10)      "MAX BLKS", 
	substr(to_char(min(blocks), '9,999,999'), 1, 10)      "MIN BLKS", 
	substr(to_char(avg(blocks), '9,999,999.9'), 1, 12)    "AVG BLKS", 
	substr(to_char(sum(blocks), '9,999,999'), 1,10)       "SUM BLKS", 
	substr(to_char(sum(bytes), '99,999,999,999'), 1, 15)  "SUM BYTES"
from 
	sys.dba_free_space 
group by 
	tablespace_name,file_id
order by 1, 4;

/* **************************** */
/*  show tablespace usage 4     */
/* **************************** */

select 
	distinct substr(tablespace_name,1,10)  	"TBL SPACE",
	substr(to_char(sum(blocks), '999,999,999'), 1, 12)         "SUM BLKS", 
	substr(to_char(sum(bytes), '999,999,999,999'), 1, 16)      "SUM BYTES"
from 
	sys.dba_free_space 
group by
	tablespace_name;

/* **************************** */
/*  show tablespace segments 1  */
/*    - order by segment        */
/* **************************** */

break on "SEGMENT" on "TYPE";

select substr(segment_name, 1, 15)                          "SEGMENT",
       substr(segment_type,1,7)                             "TYPE",
       substr(tablespace_name,1,8)                          "TBL SPACE",
       substr(to_char(sum(bytes), '99,999,999,999'), 1, 15) "SUM BYTES",
       substr(to_char(sum(blocks), '999,999'), 1, 8)         "SUM BLKS", 
       substr(to_char(extents, '999'), 1, 4)              "EXTENTS",
       substr(to_char(initial_extent, '999,999'), 1, 8)      "INI EXT",
       substr(to_char(next_extent, '999,999'), 1, 8)         "NXT EXT"
from    user_segments
group by segment_name,
         segment_type,
         tablespace_name,
         bytes,
         blocks,
         extents,
         initial_extent,
         next_extent
order by 1, 2;

/* **************************** */
/*  show tablespace segments 2  */
/*    - order by tablespace     */ 
/* **************************** */

break on "TBL SPACE" on "SEGMENT" on "TYPE";

select substr(tablespace_name,1,8)                            "TBL SPACE",
       substr(segment_name, 1, 20)                            "SEGMENT",
    /*   substr(segment_type,1,7)                               "TYPE",  */
       substr(to_char(sum(bytes), '999,999,999,999'), 1, 16)  "SUM BYTES",       
       substr(to_char(sum(blocks), '9,999,999'), 1, 10)       "SUM BLKS", 
       substr(to_char(extents, '999'), 1, 4)                  "EXTENTS",
       substr(to_char(initial_extent, '999,999'), 1, 8)       "INI EXT",
       substr(to_char(next_extent, '999,999'), 1, 8)          "NXT EXT"
from    user_segments
group by segment_name,
         segment_type,
         tablespace_name,
         bytes,
         blocks,
         extents,
         initial_extent,
         next_extent
order by 1, 2;  
       
/* **************************** */
/*  show tablespace segments 3  */
/*  - order by bytes            */
/* **************************** */

break on "SUM BYTES" on "SEGMENT" on "TYPE";

select substr(to_char(sum(bytes), '999,999,999,999'), 1, 16)  "SUM BYTES",
       substr(segment_name, 1, 20)                            "SEGMENT",
    /*   substr(segment_type,1,7)                               "TYPE",  */
       substr(tablespace_name,1,8)                            "TBL SPACE",
       substr(to_char(sum(blocks), '9,999,999'), 1, 10)       "SUM BLKS", 
       substr(to_char(extents, '999'), 1, 4)                  "EXTENTS",
       substr(to_char(initial_extent, '999,999'), 1, 8)        "INI EXT",
       substr(to_char(next_extent, '999,999'), 1, 8)           "NXT EXT"
from    user_segments
group by segment_name,
         segment_type,
         tablespace_name,
         bytes,
         blocks,
         extents,
         initial_extent,
         next_extent
order by 1, 2;         

/* **************************** */
/*  show size of database       */
/* **************************** */

select  substr(to_char(sum(bytes)/1024/1024, '999,999,999.99'), 1, 15)  "TOT MBYTES",
        substr(to_char(sum(bytes)/1024/1024/1024, '999,999.99'), 1, 11) "TOT GBYTES"
from    sys.dba_data_files;
	

set echo off;
spool off;
set long 80;
commit;





