
set echo off; 

set linesize 132;
set pagesize 1000;
set long 50; 
set wrap 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, '9,999') 	"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(1) - ' format a8 instance_name -
       right 'Page: ' format 999 sql.pno skip 2

spool db_revw1.rslt ;

set echo on;
/* ******************************************************************* */
/*                                                                     */
/*                >>> Database Review; R.Kupcunas <<<                  */
/*                                                                     */
/*  Name:    d:\oradata\NUCP\tuning\db_revw1.sql                       */
/*  Creator: Rick Kupcunas                                             */
/*  Created: 10-Mar-97                                                 */
/*                                                                     */
/* this script will show various database parameters required to fully */
/*    review an existing database.                                     */
/*       script will perform the following functions:                  */
/*  SECTION 1 - SGA Summary                                            */
/*  SECTION 2 - Initialization Parameters                              */
/*  SECTION 3 - Tablespace Information                                 */
/*  SECTION 4 - Rollback Segment Information                           */
/*  SECTION 5 - User Information                                       */
/*  SECTION 6 - List of Roles                                          */
/*  SECTION 7 - Granted Roles                                          */
/*  SECTION 8 - Tablespace Quotas                                      */
/*  SECTION 9 - Table Information                                      */
/*  SECTION 10 - Database Size Information                             */
/*                                                                     */
/* ******************************************************************* */

/* ****************************************** */
/*  SECTION 1 - SGA Summary                   */
/* ****************************************** */

set echo off;

col hv          noprint

select 1						"hv",
       name						"PARAMETER",
       to_char(value, '999,999,999')		"VALUE"
  from v$sga
union  
select 2						"hv",
       '           '				"PARAMETER",
       '------------'				"VALUE"
from   v$sga
union  
select 3						"hv",
       'Total Value'				"PARAMETER",
       to_char(sum(value), '999,999,999')	"VALUE"
from   v$sga
order by 1;

set echo on;    

/* ****************************************** */
/*  SECTION 2 - Initialization Parameters     */
/* ****************************************** */

set echo off;

select substr(name, 1, 35)     "NAME",
     substr(value, 1, 50)      "VALUE",
     substr(isdefault, 1, 9)   "DEFAULT?"
from v$parameter
order by 1;

set echo on;    

/* ****************************************** */
/*  SECTION 3 - Tablespace Information        */
/* ****************************************** */

set echo off;

column FileNum	    format 999	    heading 'File|Num'
column FileName     format A60      heading 'File Name' 
column Tablespace   format A30      heading 'Tblspace'
column Allocated    format 9,999    heading 'Size|(MB)' 
column div1         format A3       heading ' | '
column Used         format 9,999    heading 'Used|(MB)'
column Pct_Used     format 999.99   heading 'Used|(%)'
column div2         format A3       heading ' | ' 
column TotalFree    format 9,999    heading 'Free|(MB)'
column Pct_Free     format 999.99   heading 'Free|(%)'
column MaxFree      format 9,999    heading 'MaxFree'
column status       format A3       heading 'STA'

column oneMB noprint new_value MB

compute sum of Allocated on tablespace 
compute sum of Allocated on report 
compute sum of Used      on tablespace 
compute sum of Used      on report 
compute sum of TotalFree on tablespace 
compute sum of TotalFree on report 

select substr(to_char(x.file_id,999), 1,4)     FileNum,
       x.file_name                             FileName,
       x.tablespace_name                       Tablespace,
       x.bytes/(1024*1024)                     Allocated,
       (x.bytes-sum(y.bytes))/(1024*1024)      Used,
    /*   ((x.bytes-sum(y.bytes))/
       x.bytes)*100                            Pct_Used,
       sum(y.bytes)/(1024*1024)                TotalFree,
       (sum(y.bytes)/x.bytes)*100              Pct_Free,
       max(y.bytes)/(1024*1024)                MaxFree,  */
       substr(x.status,1,3)                    status
from sys.dba_data_files x , 
     sys.dba_free_space  y 
where x.file_id = y.file_id 
group by substr(to_char(x.file_id,999), 1,4),
         x.file_name,
         x.tablespace_name,
         x.bytes,
         x.status  
order by 1,2,3;

set echo on;    

/* ****************************************** */
/*  SECTION 4 - Rollback Segment Information  */
/* ****************************************** */

set echo off;

column SegmentName  format A10      heading 'Segment|Name' 
column Tablespace   format A15       heading 'Tblspace'
column CurrentExt   format 999      heading 'Cur |Exts' 
column InitialExt   format 999,999  heading 'Initial|Extent|(MB)' 
column NextExt      format 999,999  heading 'Next|Extent|(MB)'  
column MinExt       format 999      heading 'Min|Ext'  
column MaxExt       format 999      heading 'Max|Ext' 

select  substr(owner,1,10)                	"OWNER",
	  substr(segment_name,1,10)	  		SegmentName,
        substr(tablespace_name,1,15)      	Tablespace,
        min_extents - 1	                  	CurrentExt,
        initial_extent/(1024*1024)	 	InitialExt,
        next_extent/(1024*1024)			NextExt,
        MIN_EXTENTS			  		MinExt,                     
        MAX_EXTENTS 			  	MaxExt,                  
	  substr(status,1,8)                	"STA"
from dba_rollback_segs
order by 1, 2,3,4,5;

set echo on;    

/* ****************************************** */
/*  SECTION 5 - User Information              */
/* ****************************************** */

set echo off;

select 	substr(username,1,12)		"USERNAME",
	substr(user_id,1,12)				"USER ID",
	substr(default_tablespace,1,12)		"TBL SPC",
	substr(temporary_tablespace,1,12)		"TEMP SPC",
	created					"CREATED",
	substr(profile,1,12)				"ROLE"
from 	dba_users 
order by 1 asc;

set echo on;    

/* ****************************************** */
/*  SECTION 6 - List of Roles                 */
/* ****************************************** */

set echo off;

select * from dba_roles
order by 1;

set echo on;    

/* ****************************************** */
/*  SECTION 7 - Granted Roles                 */
/* ****************************************** */

set echo off;

break on "ROLE" on "PRIVILEGE";

select 	substr(role,1,20)		"ROLE",
	substr(privilege,1,30)	"PRIVILEGE",
	ADMin_option
from 	role_sys_privs 
order by 1 asc, 2 asc;

set echo on;    

/* ****************************************** */
/*  SECTION 7a - Individual Roles             */
/* ****************************************** */

set echo off;

break on "USER" on "ROLE";

select substr(GRANTEE,1,10)		"USER",
       substr(GRANTED_ROLE,1,20)	"ROLE",
       substr(ADMIN_OPTION,1,3)		"ADM"
from dba_role_privs
order by 1,2;
                                            
set echo on;    

/* ****************************************** */
/*  SECTION 8 - Tablespace Quotas             */
/* ****************************************** */

set echo off;

 col  username          format A12
 col  tablespace_name   format A14  heading Tablespace
 col  bytes             format 999,999,999
 col  blocks            format 999,999
 col  max_bytes         format 999,999,999
 col  max_blocks        format 999,999


compute sum of bytes  on tablespace_name
compute sum of blocks on tablespace_name
compute sum of max_bytes  on tablespace_name
compute sum of max_blocks on tablespace_name

break on tablespace_name skip 1

select tablespace_name, 
     username, 
     bytes, 
     max_bytes,
     blocks,
     max_blocks
from sys.dba_ts_quotas
order by tablespace_name, 
     username;

set echo on;    

/* ****************************************** */
/*  SECTION 9 - Table Information             */
/* ****************************************** */

set echo off;

column TableName    format A35      heading 'Table|Name' 
column Tablespace   format A20       heading 'Tblspace'
column NextExt      format 999.999  heading 'Next|Extent|(GB)'  
column MinExt       format 999      heading 'Min|Ext'  
column MaxExt       format 999      heading 'Max|Ext' 
column Pct_Free     format 999      heading 'Free|(%)'
column Pct_Used     format 999      heading 'Used|(%)'
column InitialExt   format 999.999  heading 'Initial|Extent|(GB)' 
column PctIncrease  format 999.99   heading 'Pct|Incr'
column NumRows      format 999,999,999  heading 'Num of|Rows'  

column CurrentExt   format 999      heading 'Cur |Exts' 


select  substr(OWNER,1,8)		"Owner",
        TABLE_NAME		        TableName,
        substr(TABLESPACE_NAME,1,20)	Tablespace,
        PCT_FREE			Pct_Free,
        PCT_USED			Pct_Used,
       INITIAL_EXTENT/(1024*1024*1024)	InitialExt,
        NEXT_EXTENT/(1024*1024*1024)	NextExt
    /*    MIN_EXTENTS		        MinExt,
        MAX_EXTENTS		        MaxExt,
        PCT_INCREASE			PctIncrease,
        NUM_ROWS			NumRows */
from  dba_tables
where owner != 'SYS' and
      owner != 'SYSTEM'
order by 1,2,3;


set echo on;

/* ****************************************** */
/*  SECTION 10 - Database Size Information    */
/* ****************************************** */

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;
undefine MB;

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