Skrypty z rozdziału 6 książki ORACLE9i, podręcznik administratora. Więcej informacji w pliku readme.doc.



drop tabel dbs;
rem /*Tabela ta przechowuje informacje dotyczące instancji*/
create table DBS
(Db_Nm        VARCHAR2(8),        /*nazwa instancji*/
 Host_Nm      VARCHAR2(8),        /*nazwa hosta (serwera)*/
 Description  VARCHAR2(80))       /*opis instancji*/
tablespace CC
storage (initial 64K next 64K pctincrease 0);

drop table FILES;
rem /*Tabela ta przechowuje informacje o plikach danych*/
create table FILES
(Db_Nm        VARCHAR2(8),        /*nazwa instancji*/
 TS           VARCHAR2(30),       /*nazwa przestrzeni tabel*/
 Check_Date   DATE,               /*data pomiaru*/
 File_Nm      VARCHAR2(80)        /*nazwa pliku*/
 Blocks       NUMBER              /*rozmiar pliku*/
 Bytes        NUMBER              /*rozmiar pliku w bajtach*/
 primary key(Db_Nm, TS, Check_Date, File_Nm)) 
 tablespace CC
 storage (initial 128K next 128K pctincrease 0);

drop view FILES_TS_VIEW;

rem /*Perspektywa ta służy do grupowania informacji dotyczących plików*/

create view FILES_TS_VIEW as
select 
  DB_Nm,                         /*nazwa instancji*/
  TS,                            /*nazwa przestrzeni tabel*/
  Check_Date,                    /*data pomiaru*/
  SUM(Blocks) Sum_File_Blocks    /*suma bloków przydzielonych do przestrzeni tabel*/
  SUM(Bytes) Sum_File_Bytes    /*suma bajtów przydzielonych do przestrzeni tabel*/
from FILES
group by
  Db_Nm,
  TS,
  Check_Date;

drop table SPACES;

rem /*Tabela służąca do przechowywania informacji na temat wolnej przestrzeni*/

create table SPACES
(Db_Nm        VARCHAR2(8),        /*nazwa instancji*/
 TS           VARCHAR2(30),       /*nazwa przestrzeni tabel*/
 Check_Date   DATE,               /*data pomiaru*/
 Count_Free_Blocks NUMBER,        /*liczba wolnych obszarów*/
 Sum_Free_Blocks   NUMBER,        /*wolna przestrzeń, w blokach systemu Oracle*/
 Max_Frre_Blocks   NUMBER,        /*największy wolny obszar*/
 Sum_Free_Bytes    NUMBER,        /*wolna przestrzeń, w bajtach*/
 primary key(Db_Nm, TS, Check_Date))
 tablespace CC
 storage (initial 128K next 128K pctincrease 0);

drop table EXTENTS;
rem /*Tabela służąca do przechowywania informacji dotyczących obszarów*/
create table EXTENTS
(Db_Nm        VARCHAR2(8),        /*nazwa instancji*/
 TS           VARCHAR2(30),       /*nazwa przestrzeni tabel*/
 Seg_Owner    VARCHAR2(30),       /*właściciel segmentu*/
 Seg_Name     VARCHAR2(32),       /*nazwa segmentu*/
 Seg_Type     VARCHAR2(17),       /*typ segmentu*/
 Extents      NUMBER,             /*liczba przydzielonych obszarów*/
 Blocks        NUMBER,        /*liczba przydzielonych bloków*/
 Check_Date    DATE,          /*data pomiaru*/
 ) tablespace CC
 storage (initial 128K next 128K pctincrease 0);
 
 create index I_EXTENTS$KEYS
 on EXTENTS(Db_Nm, TS, Seg_Owner, Seg_Name, Check_Date)
 tablespace CCINDX
 storage (initial 128K next 128K pctincrease 0);


create database link CASE
connect to system identified by m99yhvasd44e
using 'case';


# plik:  ins_cc1
# Skrypt ten jest wykonywany raz dziennie w celu zapisania rekordów do bazy danych CC1
# zawierających informacje dotyczące używanej przestrzeni w bazach danych, których lista
# zawarta jest w skrypcie inserts.sql. Skrypt ten wywoływany jest z bieżącego pliku.
# Nowa baza danych musi posiadać powiązanie utworzone w bazie CC1 oraz odpowiednie wpisy
# w skrypcie inserts.sql.
ORACLE_SID=cc1; export ORACLE_SID
ORAENV_ASK=NO; export ORAENV_ASK
. oraenv
cd /orasw/dba/CC1
sqlplus <<EOF
connect system/manager as SYSDBA;
start inserts
shutdown
EOF


rem
rem plik: inserts.sql
rem położenie: /orasw/dba/CC1
rem Wywoływany z pliku systemu operacyjnego o nazwie ins_cc1
rem Jeśli nowa baza danych dodawana jest do systemu monitorującego informacje o niej
rem muszą być wprowadzone do niniejszego skryptu.
rem
set verify off
@ins_all CASE
@ins_all CC1
analyze table FILES compute statistics;
analyze table SPACES compute statistics;
analyze table EXTENTS compute statistics;
@space_watcher
@extent_watcher


rem 
rem plik: ins_all.sql
rem lokalizacja : /orasw/dba/CC1
rem Używany do wprowadzania danych do tabel instancji monitorującej CC1. Skrypt jest
rem wywoływany ze skryptu inserts.sql dla każdej bazy danych. 
rem Najlepszym rozwiązaniem jest nazwanie powiązań baz danych tak samo jak
rem odpowiadających im instancji.
rem
insert into FILES
   (Db_Nm,
    TS,
    Check_Date,
    File_Nm,
    Blocks,
    Bytes)
select
    UPPER(‘&&1’),       /*tutaj wprowadzamy nazwę powiązania lub nazwę instancji*/
    Tablespace_Name,    /*nazwa przestrzeni tabel*/
    TRUNC(SysDate),     /*data wykonania zapytania*/
    File_Name,          /*pełna nazwa pliku*/
    Blocks              /*liczba bloków bazy danych w pliku*/
    Bytes               /*liczba bajtów bazy danych w pliku*/

from sys.DBA_DATA_FILE@&&1
/
commit;
rem
insert into SPACES
    (Db_Nm,
    Check_Date,
    TS,
    Count_Free_Blocks,
    Sum_Free_Blocks,
    Max_Free_Blocks,
    Max_Free_Bytes)
select
    UPPER('&&1'),       /*tutaj wprowadzamy nazwę powiązania lub nazwę instancji*/
    TRUNC(SysDate),     /*data wykonania zapytania*/
    Tablespace_Name,    /*nazwa przestrzeni tabel*/
    COUNT(Blocks),      /*liczba wolnych bloków*/
    SUM(Blocks),        /*całkowita ilość wolnego miejsca w przestrzeni tabel*/
    MAX(Blocks),        /*największy wolny obszar w przestrzeni tabel*/
    SUM(Bytes),        /*całkowita liczba wolnych bitów w przestrzeni tabel*/
from sys.DBA_FREE_SPACE@&&1
group by Tablespace_Name
/
commit;
rem
insert into EXTENTS
    (Db_Nm,
    TS,
    Seg_Owner,
    Seg_Name,
    Seg_Type,
    Extents,
    Blocks,
    Check_Date)
select
    UPPER(‘&&1’),       /*tutaj wprowadzamy nazwę powiązania lub nazwę instancji*/
    Tablespace_Name,    /*nazwa przestrzeni tabel*/
    Owner,              /*właściciel segmentu*/
    Segment_Name,       /*nazwa segmentu*/
    Segment_Type,       /*typ segmentu*/
    Extents,            /*liczba obszarów w segmencie*/
    Blocks,             /*liczba bloków bazy danych w segmencie*/
    TRUNC(SysDate),     /*data wykonania zapytania*/
from sys.DBA_SEGMENTS@&&1
where Extents>999             /*tylko segmenty zawierające więcej niż 999 obszarów*/ 
or Segment_Type = 'ROLLBACK'  /*lub segmenty wycofania*/
/
commit;
rem
undefine 1



rem 
rem plik: space_watcher.sql
rem lokalizacja: /orasw/dba/CC1
rem Wywoływany ze skryptu inserts.sql
rem 
rem
column Db_Nm format A8
column TS format A20
column Week4 format 999 heading ”1Wk|Ago”
column Week3 format 999 heading ”2Wks|Ago”
column Week2 format 999 heading ”3Wks|Ago”
column Week1 format 999 heading ”4Wks|Ago”
column Today format 999
column Change format 999

set pagesize 60
break on Db_Nm skip 2
title center 'Przestrzenie tabel, w których ilość wolnej przestrzeni zmniejszyła się do poziomu mniejszego niż 5% w ostatnim miesiącu' skip 2

select
   SPACES.Db_Nm,
   SPACES.TS,
MAX(DECODE(SPACES.Check_Date, TRUNC(Sys_date-28),
   ROUND(100*Sum_Free_Blocks/Sum_File_blocks),0)) Week1,
MAX(DECODE(SPACES.Check_Date, TRUNC(Sys_date-21),
   ROUND(100*Sum_Free_Blocks/Sum_File_blocks),0)) Week2,
MAX(DECODE(SPACES.Check_Date, TRUNC(Sys_date-14),
   ROUND(100*Sum_Free_Blocks/Sum_File_blocks),0)) Week3,
MAX(DECODE(SPACES.Check_Date, TRUNC(Sys_date-7),
   ROUND(100*Sum_Free_Blocks/Sum_File_blocks),0)) Week4,
MAX(DECODE(SPACES.Check_Date, TRUNC(Sys_date),
   ROUND(100*Sum_Free_Blocks/Sum_File_blocks),0)) Today,
MAX(DECODE(SPACES.Check_Date, TRUNC(Sys_date),
   ROUND(100*Sum_Free_Blocks/Sum_File_blocks),0)) – 
MAX(DECODE(SPACES.Check_Date, TRUNC(Sys_date-28),
   ROUND(100*Sum_Free_Blocks/Sum_File_blocks),0)) Change
from SPACES, FILES_TS_VIEW FTV 
where SPACES.Db_Nm = FTV.Db_Nm            /*ta sama nazwa bazy danych*/
and SPACES.TS = FTV.TS                    /*ta sama nazwa przestrzeni tabel*/
and SPACES.Check_Date = frv.check_Date    /*ta sama data pomiaru*/
and exists                                /*czy istnieje?*/
   (select 'x' from spaces x
   where x.db_nm – SPACES.Db_Nm
and x.ts = SPACES.TS
and x.Check_Date = TRUNC(SysDate))
group by
   SPACES.DB_Nm,
   SPACES.TS
having           /*czy ilość pamięci w ostatnim miesiącu spadła o co najmniej 5%?*/

(MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate),
        ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) -
 MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate 28),
        ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0))>5)
or               /*czy ilość pamięci spadła poniżej 10% */ 
(MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate),
        ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) <10)
order by SPACES.Db_nm,
DECODE(MAX(DECODE(SPACES.Check_Daye,TRUNC(SysDate),
   ROUND(100*Sum_Free_Blocks(/Sum_File_Blocks),0)) - 
MAX(DECODE(SPACES.Check_Daye,TRUNC(SysDate-28),
   ROUND(100*Sum_Free_Blocks(/Sum_File_Blocks),0)),0,9999, 
MAX(DECODE(SPACES.Check_Daye,TRUNC(SysDate),
   ROUND(100*Sum_Free_Blocks(/Sum_File_Blocks),0)) – 
MAX(DECODE(SPACES.Check_Daye,TRUNC(SysDate-28),
   ROUND(100*Sum_Free_Blocks(/Sum_File_Blocks),0))),
DECODE(MAX(DECODE(SPACES.Check_Daye,TRUNC(SysDate),
   ROUND(100*Sum_Free_Blocks(/Sum_File_Blocks),0))

spool space_watcher.lst 
/
spool off
  



rem
rem plik: ext_watcher.sql
rem lokalizacja: /orasw/dba/CC1
rem Wywoływany ze skryptu inserts.sql
rem 
column Db_Nm format A8
column TS format A18
column Seg_Owner format a14
column Seg_Name format a32
column Seg_Type format a8
column Blocks format 99999999
column Week4 format 9999 heading ”1Wk|Ago”
column Week3 format 9999 heading ”2Wks|Ago”
column Week2 format 9999 heading ”3Wks|Ago”
column Week1 format 9999 heading ”4Wks|Ago”
column Today format 9999
column Change format 9999

set pagesize 60 linesize 132
break on Db_Nm skip 2 on TS skip 1 on Seg_Owner
title center 'Segmenty, których liczba obszarów jest większa niż 1000'
skip 2

select
   EXTENTS.Db_Nm,
   EXTENTS.TS,
   EXTENTS.Seg_Owner,
   EXTENTS.Seg_Name,
   EXTENTS.Seg_Type, 	
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate),
         Blocks,0)) Blocks,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-28),
         Extentss,0)) Week1,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-21),
         Extentss,0)) Week2,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-14),
         Extentss,0)) Week3,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-7),
         Extentss,0)) Week4,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate),
         Extentss,0)) Today,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate),
         Extentss,0)) – 
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-28),
         Extentss,0)) Change
from EXTENTS
where exists   /*czy segment będzie widoczny dzisiaj*/ 
   (select 'x' from EXTENTS x
   where x.DB_Nm = EXTENTS.Db_Nm
   and x.TS = EXTENTS.TS
   and x.Seg_Owner = EXTENTS.Seg_Owner
   and x.Seg_Name = EXTENTS.Seg_Name
   and x.Seg_Type = EXTENTS.Seg_Type
   and x.Check_Date = TRUNC(SysDate))
group by 
   EXTENTS.DB_Nm,
   EXTENTS.TS,
   EXTENTS.Seg_Owner,
   EXTENTS.Seg_Name,
   EXTENTS.Seg_Type
order by EXTENTS.Db_Nm, EXTENTS.TS,
   DECODE(MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate),
         Extents,0))-
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-28),
         Extents,0)),0,-9999,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate),
         Extents,0))-
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-28),
         Extents,0))) desc,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate),
         Extents,0)) desc
spool extent_watcher.lst
/
spool off



rem 
rem space_summary.sql
rem parametr 1: nazwa powiązania baz danych
rem parametr 2: data pomiaru
rem 
rem aby wywołać raport z programu SQL*Plus:
rem @space_summary link_name Check_Date 
rem 
rem Przykład:
rem @space_summary Case 27-Maj-01 
rem Powinien być generowany raz w tygodniu dla każdej bazy danych.
rem
set pagesize 60 linesize 132 verify off feedback off
set newpage 0
column TS heading 'Tablespace' format A18
column File_Nm 'File nm' format A40
column Blocks heading 'Orablocks'
column Percentfree format 999.99
column Diskblocks format 99999999
column Cfb format 9999999 heading 'NumFrExts'
column Mfb format 9999999 heading 'MaxFrExt'
column Sfb format 9999999 heading 'SumFrBl'
column Dfrb fromat 99999999 heading 'DiskFrBl'
column SumFileBy format 99999999 heading 'SumFileBy'
column Bytes heading 'Bytes'
column Maxfrpct heading 'MaxFrPct' format 9999999

break on TS
      title center 'Przestrzenie tabel w bazie danych' &&1 skip center -
      Raport wykonano : ' &&2 skip 2 center
      spool &&1._space_summary.lst
select 
    Ts,                     /*nazwa przestrzeni tabel*/
    File_Nm,                /*nazwa pliku*/
    Blocks,                 /*liczba bloków bazy danych w pliku*/
    Bytes                   /*liczba bajtów w pliku*/
 from FILES
Where Check_Date = '&&2'
  And Db_Nm = UPPER('&&1')
Order by TS, File_Nm
/
title center 'Statystyki dotyczące wolnej przestrzeni w bazie danych' &&1 –
skip center '(rozmiary obszarów podane w blokach systemu Oracle)' skip center –
      'Raport wykonano : ' &&2 skip 2
select 
   SPACES.TS,                      /*nazwa przestrzeni tabel*/
   SPACES.Count_Free_Blocks Cfb    /*liczba wolnych obszarów*/
   SPACES.Max_Free_Blocks Mfb      /*najdłuższy wolny obszar*/
   SPACES.Sum_Free_Blocks Sfb      /*suma wolnej przestrzeni*/
   ROUND(100*Sum_Free_Blocks/Sum_File_Blocks, 2)
   Percentfree                     /*procent wolnego miejsca w przestrzeni tabel*/
   ROUND(100*Max_Free_Blocks/Sum_Free_Blocks, 2)
   MaxFrpct            /*stosunek największego obszaru do sumy wolnej przestrzeni*/
   SPACES.Sum_Free_Bytes Dfrb,    /*wolne bajty na dysku*/
   Sum_File_Bytes SumFileBy       /*przydzielone bajty na dysku*/
 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
  and SPACES.Db_Nm = UPPER('&&1')
  and SPACES.Check_Date = '&&2'
/
spool off
undefine 1
unedfine 2
undefine 3




delete from FILES
 where Check_Date < sysdate-60;
commit;

delete from SPACES
 where Check_Date < sysdate-60;
commit;

delete from EXTENTS
 where Check_Date < sysdate-60;
commit;




clear columns
clear breaks
column Drive format A5
column File_Name format A30
column Blocks_Read format 99999999
column Blocks_Written format 99999999
column Total_IOs format 99999999
set linesize 80 pagesize 60 newpage 0 feedback off
ttitle skip center "Informacje dotyczące operacji wejścia-wyjścia na plikach bazy danych" skip 2
break on report
compute sum of Blocks_Read on report
compute sum of Blocks_Written on report
compute sum of Total_IOs on report

select substr(DF.Name,1,5) Drive,
       SUM(FS.Phyblkrd+FS.Phyblkwrt) Total_IOs,
       SUM(FS.Phyblkrd) Blocks_Read,
       SUM(FS.Phyblkwrt) Blocks_Written
  from V$FILESTAT FS, V$DATAFILE DF
 where DF.File#=FS.File#
 group by substr(DF.Name,1,5)
 order by Total_IOs desc;




clear breaks
clear computes
break on Drive skip 1 on report
compute sum of Blocks_Read on Drive
compute sum of Blocks_Written on Drive
compute sum of Total_IOs on Drive
compute sum of Blocks_Read on Report
compute sum of Blocks_Written on Report
compute sum of Total_IOs on Report
ttitle skip center "Operacje wejścia-wyjścia na plikach bazy danych. Podział na -dyski" skip 2

select substr(DF.Name,1,5) Drive,
       DF.Name File_Name,
       FS.Phyblkrd+FS.Phyblkwrt Total_IOs,
       FS.Phyblkrd Blocks_Read,
       FS.Phyblkwrt Blocks_Written
  from V$FILESTAT FS, V$DATAFILE DF
 where DF.File#=FS.File#
 order by Drive, File_Name desc;



rem
rem plik over_extended.sql
rem parametry: nazwa powiązania bazy danych (nazwa instancji), współczynnik
rem
rem Wartość współczynnika powinna być zawsze większa od 1 
rem Przykład: W celu określenia, które segmenty zbliżyły się co najmniej o 20%
rem do wartości swego maksymalnego rozszerzenia, ustawia się wartość współczynnika na 
rem 1,2.
rem Przykład wywołania:
rem @over_extended CASE 1.2
rem

select 
   Owner,               /*właściciel obiektu*/
   Segment_Name,        /*nazwa segmentu*/
   Segment_Type,        /*typ segmentu*/
   Extents              /*liczba aktualnie przydzielonych obszarów*/
   Blocks               /*liczba aktualnie przydzielonych bloków*/
from DBA_SEGMENT@&&1 s
where                   /*dla segmentów klastrowych*/
(S.Segment_Type = 'CLUSTER' and exists
(select 'x' from DBA_CLUSTERS@&&1 c
where C.Owner = S.Owner
and C.Cluster_Name = S.Segment_Name
and C.Max_Extents <= S.Extents*&&2))
or                      /*dla segmentów tabel*/
(S.Segment_Type = 'TABLE' and exists
(select 'x' from DBA_TABLES@&&1 T
where T.Owner = S.Owner
and T.Cluster_Name = S.Segment_Name
and T.Max_Extents <= S.Extents*&&2))
or                      /*dla segmentów indeksowych
(S.Segment_Type = 'INDEX' and exists
(select 'x' from DBA_INDRXRS@&&1 i
where I.Owner = S.Owner
and I.Cluster_Name = S.Segment_Name
and I.Max_Extents <= S.Extents*&&2))
or                      /*dla segmentów wycofania*/
(S.Segment_Type = 'ROLLBACK' and exists
(select 'x' from DBA_ROLLBACK_SEGS@&&1 r
where R.Owner = S.Owner
and R.Cluster_Name = S.Segment_Name
and R.Max_Extents <= S.Extents*&&2))
order by 1,2

spool &&1._over_extended.lst
/
spool off
undefine 1
undefine 2




select Owner, Segment_Name, Segment_Type 
  from DBA_SEGMENTS
 where Next_Extent >
(select SUM(Bytes) from DBA_FREE_SPACE
  where Tablespace_Name = DBA_SEGMENTS.Tablespace_Name);




select Owner, Segment_Name, Segment_Type 
  from DBA_SEGMENTS
 where Next_Extent>
(select MAX(Bytes) from DBA_FREE_SPACE
  where Tablespace_Name = DBA_SEGMENTS.Tablespace_Name);




column Owner format A12
column Table_Name format A20
column Empty format 99999
column Pctusd format 999.99

select T.Owner,
       T.Table_Name, 
       S.Segment_Type, 
       T.Blocks Used,
       T.Empty_Blocks Empty, 
       S.Blocks Allocated, 
       T.Blocks/S.Blocks Pctusd
  from DBA_TABLES T, DBA_SEGMENTS S
 where T.Owner = S.Owner
   and T.Table_Name = S.Segment_Name
   and S.Segment_Type = 'TABLE'
   and T.Blocks/S.Blocks > 0.95
 order by 7  desc




column Owner format A12
column Table_Name format A20
column Empty format 99999
column Pctusd format 999.99

select T.Owner,
       T.Table_Name, 
       S.Segment_Type, 
       T.Blocks Used,
       T.Empty_Blocks Empty, 
       S.Blocks Allocated, 
       T.Blocks/S.Blocks Pctusd
  from DBA_TABLES T, DBA_SEGMENTS S
 where T.Owner = S.Owner
   and T.Table_Name = S.Segment_Name
   and S.Segment_Type = 'TABLE'
   and T.Blocks/S.Blocks > 0.95
union all
select I.Owner,
       I.Index_Name, 
       S.Segment_Type, 
       I.Leaf_Blocks Used,
       S.Blocks-1-I.Leaf_Blocks Empty, 
       S.Blocks Allocated, 
       I.Leaf_Blocks/S.Blocks Pctusd
  from DBA_INDEXES I, DBA_SEGMENTS S
 where I.Owner = S.Owner
   and I.Index_Name = S.Segment_Name
   and S.Segment_Type = 'INDEX'
   and I.Leaf_Blocks/S.Blocks > 0.95
order by 7 desc, 2, 1




