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

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

/* **************************** */
/*  Get the database block-size */
/* **************************** */

column db_block_size new_value BLOCK_SIZE
select to_char(value, '9999') db_block_size
       from v$parameter
       where name = 'db_block_size';

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

column today new_value dba_date
select to_char(sysdate, 'mm/dd/yy hh:miam') today
       from dual;

break on instance
column instance new_value instance_name
select substr(name,1,8) instance
      from v$database;

clear breaks
set termout on

set pagesize 60 linesize 132 verify off
set space 2
ttitle left 'Date: ' format a18 dba_date -
       center 'DB Review(2) - ' format a8 instance_name -
       right 'Page: ' format 999 sql.pno skip 2

set echo off;
spool db_revw2.rslt 

set echo on;
/* ******************************************************************* */
/*                                                                     */
/*                >>> Database Review; R.Kupcunas <<<                  */
/*                                                                     */
/*  Name:    d:\oradata\NUCP\tuning\db_revw2.sql                       */
/*  Creator: Rick Kupcunas                                             */
/*  Created: 10-Mar-97                                                 */
/*                                                                     */
/*                                                                     */
/* this script will show the space usage for the database in question  */
/*       script will perform the following functions:                  */
/*  SECTION 1 - show the tablespaces, size and percent used            */
/*  SECTION 2 - the total free space still available in each tablespace*/
/*  SECTION 3 - the free space blocks in each of the tablespaces       */
/*  SECTION 4 - the total free space still available in each tablespace*/
/*  SECTION 5 - the usage by each user segment, the inital extent size */
/*             and the size of the next extents                        */
/*             - ordered by segment name                               */
/*  SECTION 6 - the usage by each user segment, the inital extent size */
/*             and the size of the next extents                        */
/*             - ordered by tablespace name                            */
/*  SECTION 7 - the usage by each user segment, the inital extent size */
/*             and the size of the next extents                        */
/*             - ordered by segment size                               */
/*  SECTION 8 - show the size of the entire database                   */
/*                                                                     */
/* ******************************************************************* */

/* ****************************************** */
/*  SECTION 1 - show tablespace usage         */
/*            - via the dba_data_files        */
/* ****************************************** */

set echo off;

select  substr(D.tablespace_name, 1, 20)                           "TBL SPACE", 
  substr(to_char(sum(D.bytes)/1024/1024, '999,999.999'), 1, 10)    "TOT MBYTES",
   substr(to_char(sum(D.bytes)/(Z.VALUE), '99,999,999'), 1, 11)    "BLOCKS"
from    sys.dba_data_files D,
        v$parameter        Z
where Z.name = 'db_block_size'
group by
 D.tablespace_name,
        Z.value;
              
set echo on;    

/* ****************************************** */
/*  SECTION 2 - show tablespace usage         */
/*            - via the dba_free_space        */
/* ****************************************** */

set echo off;

select  distinct substr(tablespace_name, 1, 20)      "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;

set echo on;

/* ****************************************** */
/*  SECTION 3 - show tablespace usage         */
/*            - via the dba_free_space        */
/* ****************************************** */

set echo off;

break on "TBL SPACE";

select  substr(tablespace_name,1,20)                           "TBL SPACE",
      substr(to_char(file_id, '99'), 1, 4)                  "ID", 
    substr(to_char(count(*), '9,999'), 1, 6)              "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;

set echo on;

/* ****************************************** */
/*  SECTION 4 - show tablespace usage         */
/*            - via the dba_free_space        */
/* ****************************************** */

set echo off;

select 
     distinct substr(tablespace_name,1,20)   "TBL SPACE",
    substr(to_char(sum(blocks), '999,999,999'), 1, 12)         "SUM BLKS", 
 substr(to_char((sum(bytes)/(1024*1024)), '999,999.999'), 1, 16)      "SUM Mb"
from 
     sys.dba_free_space 
group by
     tablespace_name;

set echo on;

/* ****************************************** */
/*  SECTION 5 - show tablespace usage         */
/*            -  order by segment             */
/* ****************************************** */

set echo off;

break on "SEGMENT" on "TYPE";

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

set echo on;

/* ****************************************** */
/*  SECTION 6 - show tablespace usage         */
/*            - order by tablespace           */ 
/* ****************************************** */

set echo off;

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

select substr(tablespace_name,1,20)                            "TBL SPACE",
       substr(segment_name, 1, 25)                            "SEGMENT",
       substr(segment_type,1,7)                               "TYPE",  
       substr(to_char(sum(bytes/(1024*1024)), '999.999'), 1, 8)  "SUM Mb",       
       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/(1024*1024), '999.999'), 1, 8)       "INI Mb",
       substr(to_char(next_extent/(1024*1024), '999.999'), 1, 8)          "NXT Mb"
from    user_segments
group by segment_name,
         segment_type,
         tablespace_name,
         bytes,
         blocks,
         extents,
         initial_extent,
         next_extent
order by 1, 2;  
       
set echo on;

/* ****************************************** */
/*  SECTION 7 - show tablespace usage         */
/*            - order by Mbytes               */
/* ****************************************** */

set echo off;

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

select substr(to_char((sum(bytes)/(1024*1024)), '999,999.999'), 1, 16)  "SUM Mb",
       substr(segment_name, 1, 20)                                 "SEGMENT",
       substr(segment_type,1,7)                               "TYPE",  
       substr(tablespace_name,1,20)                                "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/(1024*1024), '999.999'), 1, 9) "INI Mb",
       substr(to_char(next_extent/(1024*1024), '999.999'), 1, 8)    "NXT Mb"
from    user_segments
group by segment_name,
         segment_type,
         tablespace_name,
         bytes,
         blocks,
         extents,
         initial_extent,
         next_extent
order by 1, 2;         

set echo on;

/* ****************************************** */
/*  SECTION 8 - show database size            */
/* ****************************************** */

set echo off;

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;
      
undefine BLOCK_SIZE;
set echo off;
spool off;
set long 80;
commit;





