space_summary.sql

介绍oracle dba常用的报表代码,已经异常查找等

select Ts, /*tablespace name*/
       File_Nm, /*file name*/
       Blocks, /*Oracle blocks in the file*/
       blocks * &&3 Bytes /*bytes in the file*/  ----&&3块大小
  from FILES
 where Check_Date = &&2                          ----&&2 数据日期
   and Db_Nm = UPPER('&&1')											 ----'&&1'事例名
 order by TS, File_Nm;
/
select SPACES.TS, /*tablespace name*/
       SPACES.Count_Free_Blocks Cfb, /*number of free extents*/
       SPACES.Max_Free_Blocks Mfb, /*lgst free extent*/
       SPACES.Sum_Free_Blocks Sfb, /*sum of free space*/
       ROUND(100 * Sum_Free_Blocks / Sum_File_Blocks, 2) Percentfree, /*percent free in TS*/
       ROUND(100 * Max_Free_Blocks / Sum_Free_Blocks, 2) Maxfrpct, /*ratio of largest extent to sum*/
       SPACES.Sum_Free_Blocks*&&3 Dfrb ,--SPACES.Sum_Free_Bytes Dfrb, /*disk bytes free*/   ---&&3块大小
       Sum_File_Blocks*&&3--Sum_File_Bytes SumFileBy /*disk bytes allocated*/
  from SPACES, FILES_TS_VIEW FTV
 where SPACES.Db_Nm = FTV.Db_Nm
   and SPACES.TS = FTV.TS
   and SPACES.Check_Date = FTV.Check_Date    ----'&&1'事例名
   and SPACES.Db_Nm = UPPER('&&1')
   and SPACES.Check_Date = &&2 ;             ----&&2 数据日期
/