Outils personnels

Script d'audit de bases de données ORACLE : Différence entre versions

De wikiGite
 
(40 révisions intermédiaires par 2 utilisateurs non affichées)
Ligne 1 : Ligne 1 :
−
<nowiki>
+
Un script d'audit de bases de données Oracle qui produit un rapport HTML.
−
-- AUDIT BASES ORACLE
 
−
--
 
−
-- Compatible Oracle 9i, 10g
 
−
-- FSo 2005-2010
 
−
-- Changelog
 
−
-- 2005    Creation du script, regroupement d'operations manuelles repetitives.
 
−
--        Generation de rapports format TXT
 
−
-- 12/2006 Creation table HISTAUDIT et requetes associees pour comparaisons entre 2 audits
 
−
-- 11/2008 Separation en 2 fichiers 1=audit de perfs 2=environnement schemas
 
−
-- 02/2009 Modifications des requetes audits de perfs pour generer des fichiers HTML
 
−
-- 02/2010 Affichage icones "info" et "tips"
 
−
-- 06/2010 Ajout scan de l'alert.log
 
−
-- 08/2010 passage section schemas en HTML
 
  
−
-- ****** IMPORTANT ****** : le script doit etre lance par sqlplus, en tant que SYSTEM ******
+
Peaufiné amoureusement depuis des années, certainement encore perfectible, à utiliser sans modération.
  
−
-- Exemple, sous linux, en utilisant des variables et easyconnect :
+
Code partagé "as is", sans garantie, à utiliser à vos risques et périls (mais bon, [http://www.ina.fr/video/PUB3216395097 ce ne sont que quelques select, Seigneur], et quelques inserts dans une table d'historique d'audit dédiée).
−
-- SOURCE=/media/UDISK
 
−
-- SQLP=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/scripts/sqlplus.sh
 
−
-- AUDIT_ORACLE=$SOURCE/Audit_Oracle
 
−
-- connection='//server_oracle:1521/ORA'
 
−
-- $SQLP system/manager@$connection @$AUDIT_ORACLE/audit_complet_html
 
  
−
-- ******  NOTES / TIPS  ******
+
Voir aussi le [[Script_d'audit_de_bases_de_données_MySQL | script d'audit MySQL]]
−
-- * NOTE 1 : ATTENTION AUX FORMATS DE COLONNES ALPHANUMERIQUES :
 
−
--  LE XX de "format aXX" DOIT TENIR COMPTE DES BALISES HTML
 
−
--  de TYPE "<td bgcolor="#3399CC" align=center>" QUI SONT
 
−
--  COMPTEES DANS LE TOTAL DE LA CHAINE DE CARACTERE
 
−
--
 
−
-- * NOTE 2 : AUTANT QUE POSSIBLE, NE PAS S'OCCUPER DES FORMATS COL
 
−
--  LAISSER PAR DEFAUT LES ALPHA ET UTILISER UN TO_CHAR(valeur,'format numérique') POUR LES NOMBRES
 
−
--
 
−
-- * NOTE 3 : Le script cree (ou re-cree) 2 fonctions et une table d'historique dans TOOLS
 
−
--
 
−
-- * TIP : inclure une image directement dans le code html :
 
−
-- <IMG SRC="data:image/gif;base64,R0lGODdhMAAwAPAAAAAAAP///ywAAAAAMAAw AAAC8IyPqcvt3wCcDkiLc7C0qwyGHhSWpjQu5yqmCYsapyuvUUlvONmOZtfzgFz ByTB10QgxOR0TqBQejhRNzOfkVJ+5YiUqrXF5Y5lKh/DeuNcP5yLWGsEbtLiOSp a/TPg7JpJHxyendzWTBfX0cxOnKPjgBzi4diinWGdkF8kjdfnycQZXZeYGejmJl ZeGl9i2icVqaNVailT6F5iJ90m6mvuTS4OK05M0vDk0Q4XUtwvKOzrcd3iq9uis F81M1OIcR7lEewwcLp7tuNNkM3uNna3F2JQFo97Vriy/Xl4/f1cf5VWzXyym7PH hhx4dbgYKAAA7" ALT="Larry">
 
−
-- http://www.motobit.com/util/base64-decoder-encoder.asp
 
−
--
 
−
-- ******  BUGS CONNUS  ******
 
−
-- Incompatibilité de certaines requêtes avec des version < 9. Le script va quand même au bout.
 
−
--
 
−
-- ******  TODO LIST  ******
 
−
-- AMELIORATIONS GLOBALES (AU SCRIPT)
 
−
--------------------------
 
−
-- TODO : INDIQUER SI L'INSTANCE EN COURS UTILISE IUN SPFILE OU UN INIT.ORA
 
−
-- TODO : UTILISER DES VARIABLES EN DEBUT DE SCRIPT POUR LES SEUILS D'ALERTES ?
 
−
-- TODO : affiner les stats d'opérations ASMM (variation des tailles de pools) avec v$sga_resize_ops et v$sga_dynamic_components
 
−
-- select component, current_size from v$sga_dynamic_components;
 
−
--select component,oper_type,oper_mode,initial_size/1024/1024 "Initial",TARGET_SIZE/1024/1024 "Target",FINAL_SIZE/1024/1024 "Final",status from v$sga_resize_ops order by component;
 
−
--select component,current_size/1024/1024 "CURRENT_SIZE",min_size/1024/1024 "MIN_SIZE",user_specified_size/1024/1024 "USER_SPECIFIED_SIZE",last_oper_type "TYPE" from v$sga_dynamic_components order by component;
 
−
-- TODO : Faire une table des matières (ancres HTML)
 
−
-- TODO : Remplacer les fonctions de colorisation PL/SQL par du javascript ?
 
−
--    Note : Javascript inséré systématiquement en haut du fichier HTML produit, réutilisable dans les requêtes ?
 
−
-- TODO : ajouter chemin des archivelogs si ARCHIVELOG MODE (trouver le(s) log_archive_XXX remplis)
 
−
-- TODO : coloriser les requêtes gourmandes qui prennent plus de XXX microsec ou XXX buffers
 
−
-- TODO : Remplacer "Néant" par "N/A" ? ou "Aucun" ? pour éviter l'accent
 
−
-- TODO : Section SCHEMAS = Liste des grants par user et par role non systeme
 
−
-- TODO : Afficher N/A si 1er audit ou aucuns objets utilisateurs
 
−
--
 
−
-- AMELIORATIONS LOCALES (AUX SECTIONS)
 
−
-------------------------
 
−
-- TODO : Volumetrie = Insérer un graphe evolution volumétrie (attention aux creations de fichiers entre deux audits)
 
−
-- TODO : AJOUTER %LIBRE SUR TABLESPACES (comment gérer les autoextend ?)
 
−
-- TODO : mettre la ligne complète en rouge si aucun des datafiles n'est en autoextend, et que le "LIBRE" arrive à 90% du "TOTAL"
 
−
-- TODO : fusionner Volumetrie tablespace et difference de taille (ajouter une ligne sous le titre avec 2 colonnes)
 
−
-- TODO : Diff taille dernier audit = Ne pas coloriser tbs types UNDO et TEMPORARY
 
−
--      Note : prob = on a pas le tbs type dans histaudit. Lier avec dba_tablespace pour detecter types TEMPORARY/UNDO ?
 
−
--      Note2: Exclure aussi SYSTEM et SYSAUX de la colorisation ?
 
−
-- TODO : Afficher N/A si 1er audit
 
−
-- TODO : Detail UGA par utilisateur = Garder le nombre de session dans HISTAUDIT pour graphique d'evolution et moyenne
 
−
-- TODO : 'volumetrie tables et index' = A la creation des données d'audit, afficher au moins le total des tables et index,
 
−
--        meme s'il n'y a pas de difference à afficher
 
−
-- TODO : Ajouter une colonne user, et grouper par user pour detecter d'eventuels users system oublies dans le NOT IN
 
−
-- plus une ligne total global à la fin du tableau
 
−
-- TODO : Intégrer le script de recompilation si le nombre d'INVALID > 0 et afficher le résultat
 
−
-- TODO : "tables et index dans le meme tbs" = grouper par user et tbs et ne donner que le nbre d'objets
 
−
-- TODO : 'affichage des logs' = grouper les messages et afficher une somme pour limiter le nombre de ligne ?
 
  
−
set pages 999
+
09/2018 : transféré sur Github
−
set lines 200
+
https://github.com/fsoyer/auditOracleHTML
−
set echo off
 
−
set termout on
 
−
set trims on
 
−
set showmode off
 
−
set verify off
 
−
set feed off
 
−
set serveroutput on size 1000000
 
−
set head off
 
−
 
 
−
-- On force les séparateur décimaux et milliers en français
 
−
alter session set nls_numeric_characters=", ";
 
−
 
 
−
-- *************************************** Creation de fonctions
 
−
-- Fonction CouleurLimite renvoie ROUGE (valeur inferieure a limite+plage si "superieur" <= 0, valeur superieure sinon),
 
−
-- ORANGE (valeur a l'interieur de la plage (limite +/- plage),
 
−
-- VERT (valeur superieure a limite+plage si "superieur" <= 0, valeur inferieure sinon)
 
−
CREATE OR REPLACE FUNCTION CouleurLimite (valeurAtester number, limite number, plage number, superieur int)
 
−
RETURN varchar2
 
−
IS
 
−
signe number;
 
−
BEGIN
 
−
if superieur <= 0 then
 
−
    if valeurAtester < limite then
 
−
      select SIGN(valeurAtester-(limite-(plage))) into signe from dual;
 
−
      if signe < 0 then
 
−
  return '#FF0000'; --ROUGE
 
−
      else
 
−
  return '#FF9900'; --ORANGE
 
−
      end if;
 
−
    else
 
−
      select SIGN(valeurAtester-(limite+(plage))) into signe from dual;
 
−
      if signe > 0 then
 
−
  return '#33FF33';--VERT
 
−
      else
 
−
  return '#FF9900';--ORANGE
 
−
      end if;
 
−
    end if;
 
−
else
 
−
    if valeurAtester >= limite then
 
−
      select SIGN(valeurAtester-(limite+(plage))) into signe from dual;
 
−
      if signe > 0 then
 
−
  return '#FF0000'; --ROUGE
 
−
      else
 
−
  return '#FF9900'; --ORANGE
 
−
      end if;
 
−
    else
 
−
      select SIGN(valeurAtester-(limite-(plage))) into signe from dual;
 
−
      if signe < 0 then
 
−
  return '#33FF33';--VERT
 
−
      else
 
−
  return '#FF9900';--ORANGE
 
−
      end if;
 
−
    end if;
 
−
end if;
 
−
END;
 
−
/
 
−
 
 
−
-- *************************************** INITIALISATIONS
 
−
define logfile = audit
 
−
define envfile = env
 
−
define sysusers = ('SYS','SYSTEM','CTXSYS','DBSNMP','OUTLN','ORDSYS','ORDPLUGINS','MDSYS','DMSYS','WMSYS','WKSYS','OLAPSYS','SYSMAN','XDB','EXFSYS','TSMSYS','MGMT_VIEW','ORACLE_OCM','DIP','SI_INFORMTN_SCHEMA','ANONYMOUS')
 
−
define exusers = ('SCOTT','HR','OE','PM','QS','QS_ADM','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS','SH')
 
−
 
 
−
--ATTENTION : AUCUN ESPACE DANS LES LISTES, SINON LA VARIABLE EST TRONQUEE !
 
−
--prompt &sysusers
 
−
--prompt &exusers
 
−
--select username from dba_users where username not in &sysusers and username not in &exusers;
 
−
 
 
−
variable tips varchar2(4000);
 
−
begin
 
−
    select 'iVBORw0KGgoAAAANSUhEUgAAABMAAAATCAYAAAByUDbMAAAABmJLR0QAAAAAAAD5Q7t/AAAACXBIWXMAAAsQAAALEAGtI711AAAACXZwQWcAAAATAAAAEwDxf4yuAAACDElEQVQ4y62ULXDbQBBGnzsFK2YxHzyoMJk50NBhCWugYcpCA0PDEmjosphF0DBmEqvgwSs7MS1TgSz5v9OZdmd2NKNZvfv20+4Nmqbhf8XXcy9L5xpfFpTlGlfmaPiFxCNsMiZJppgkJbF2cPzd4FjZZrNpPpZPRHySJoIxYIzgveIc5KWCXHNz/8xkMhlchGWrH81qMWd+L6SpgOxVKug281xZZcrtw4LZ7FsP7Ntcr7MmW855fhoyHIFE0WEPEVDXiMJ4LJgY3t7mxLFtOoVfOo9Wi+88zOUENDCuT4kiEBABM4LZVHhfPFI61/QwV6yxxmOtnIB8qPvsgCIgkXCdQFwXuGJDDyvyjOvxoUcdaD86IEJfm15BkX/sYL4ssFa4FCY+8k+3T4F4CN7tKQsakMusy6Gtf1qFHSyWGA26OxFovD1RZOKIxtsDdaogw3gHM0nKT7edo7o+C+xAWtfbOiUoeA/GTnawdDyjKLbKjoAHXdV1P7woEJTSQTq+2cFsOsV5Q+mUqgKtQEN9AtUKqqqFhKoFhSjFpnvKEmsHtw+vLJeKd4oGbYEVhHwEQPgctb5uQZWHrIC7+Uu/9Gd3czZtB7L7/Z3ZYa+1bAO3j4e7efbWeF88EtcF6VW7NhJBFVqzu9bu5i9/vjW6KJ1rXLGhyD/w5YaggVhiTDIhHd9g08nf3Wf/Er8BAI4wKLDf6EwAAAAfelRYdENyZWF0aW9uIFRpbWUAAHjaMzDTNzLUNzABAAb7AYwMyT+gAAAALnpUWHRTb2Z0d2FyZQAAeNrzTUwuys9NTclMVHDLLEotzy/KLlbwjVAwMjAwAQCWLgl6ZrFa0gAAAABJRU5ErkJggg==' into :tips from dual;
 
−
end;
 
−
/
 
−
 
 
−
variable info varchar2(4000);
 
−
begin
 
−
    select 'R0lGODlhFAAUAOfAAD+JSDyVQEqTTFqTZkedQ0eeQ1CjRVKlRmScdWaccGibdVepRmSlUVqqSV2pUlytSG6jfXWjf2OxTGayS2SzSW+ueXGxVmqzWXGyb2y3TW22Vm64TnC6TXC7T3y2YX+2bXq2fna+UHu5c323gom2b3e/Unm+W3q+X328cHnBUYS8Y3rBUZ+uooe5fIO8gn3EUoHAb4W+f4DDYYPBbn/FU6OyqITBcX/GUYDGU4LEYZK+coHHVILIU4jCgI7DZYfEcZK/i6q2pYvDgae3qZXCdIPLUqi3rIrHaofJYo/EfYvHco7FgZDDkIrKXojMVYjNVYnNVZHIgpTGkZXGkYzPVqe/r7O9qZjIko/RV6DJd7O/rJrKk5vPYJzKlZTSYJPUV5PVVpPVV5/KpLLDsqPJppPWVp/MmpXWWLPEsrnDraHMpKLNo6LNpKTOpaXPpbfItp3ZYqrOs6fRornKtqnQsZ3dWq/Qr5/eWqrYeaLdZarbZLDSr6/Ss6/SubDSuMHMxcXLxbHTtaPhWsbMxbLVrLLUtsLOxbLVtrPVtqved7nXoLXWvrnVvbnXu7ranM3Rx8nUt8rSysvSyb3cn8/TyMvWuK/mbL3dna7rW8bfy9zX3sjgzd3Z4cnhzcnhzt3a4d7a4bvzYOLe47/3X+Tg5dbqw+fi5dDvq+Tk5Ojj5+Xl5cb8Y+bm5unl6Nnr3url6tvr4Orp6+7p7O7q7ezr7ezs7O7t7u7u7ubx6Ojy6vDw8PHx8fH38/f29/f5+Pr7+////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////yH+EUNyZWF0ZWQgd2l0aCBHSU1QACH5BAEKAP8ALAAAAAAUABQAAAj+AP8JHEiwoMGDCBMKlPWnShVDsxQK1FUDQR9XsBZBMLIroSkFcTZ54pUrUyc/EVodrDWATiA+uIABY4So0KEEtwyyqCBGzZpGwHyRcdOGzYghvQimAgCCiRQzcvbY6bLlyhQXAl4RBBQAQ4wehH7JBBJliRARBSIRHEPAAQoYM5LIbKHkh40LB94QRGOggYYTMo7I/IAkh4kJD+YQHLRAwoYSNJrI9PBkx4oOFCQRFMUgQwgcULzIVBGGCo8UFkgVDMLhhZMvcGT6uHOGyg0ttgqqIlEES55TMktZqlOGCKuDmnSASaTI0aVJeARlAZUQlRUumEKtGqUnDSqJtDgNPYJUidKnWBLTq1cYEAA7' into :info from dual;
 
−
end;
 
−
/
 
−
 
 
−
column bname new_value dbname noprint
 
−
column hname new_value hstname noprint
 
−
select name as bname from v$database;
 
−
select host_name as hname from v$instance;
 
−
 
 
−
column bloc new_value dbloc noprint
 
−
select value as bloc from v$parameter
 
−
where name = 'db_block_size';
 
−
 
 
−
column bdate new_value dateaudit noprint
 
−
select to_char(to_date(sysdate),'ddmmyy') as bdate from dual;
 
−
 
 
−
spool &logfile._&dbname._&hstname._&dateaudit..html
 
−
prompt <!DOCTYPE public "-//w3c//dtd html 4.01 strict//en" "http://www.w3.org/TR/html4/strict.dtd">
 
−
prompt <html>
 
−
prompt <head>
 
−
prompt <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
 
−
prompt <meta name="description" content="Audit Oracle HTML">
 
−
prompt <title>Audit &dbname (&hstname)</title>
 
−
prompt </head>
 
−
prompt <BODY BGCOLOR="#003366">
 
−
 
 
−
-- *************************************** Entete
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center>
 
−
--select '<font color=WHITE size=+2><b>Audit base '||name from v$database;
 
−
prompt <font color=WHITE size=+2><b>Audit &dbname (&hstname)
 
−
select ' du '||to_char(to_date(sysdate),'DD-MON-YYYY')||'</b>' as DATE_AUDIT from dual;
 
−
prompt </font></td></tr></table>
 
−
prompt <br>
 
−
 
 
−
-- *************************************** Section informations
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE">SECTION INFORMATIONS</font></b></div>
 
−
prompt <hr>
 
−
 
 
−
-- *************************************** Versions
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Versions</b></font></td></tr>
 
−
prompt <tr><td bgcolor="LIGHTBLUE" colspan=2>
 
−
select banner||'<br>' from v$version;
 
−
prompt </td></tr>
 
−
prompt <br>
 
−
-- *************************************** NLS_PARAMETERS
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Param&egrave;tres NLS</b></font></td></tr>
 
−
prompt <tr><td width=50%><b>Param&egrave;tre</b></td><td width=50%><b>Valeur</b></td>
 
−
select '<tr><td bgcolor="LIGHTBLUE">'||parameter||'</td>','<td bgcolor="LIGHTBLUE">'||value||'</td>','</tr>' from v$nls_parameters;
 
−
 
 
−
prompt </table>
 
−
prompt <br>
 
−
-- *************************************** NOMS
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Noms database et instance</b></font></td></tr>
 
−
select '<tr><td bgcolor="WHITE" width=20%>DB_NAME</td><td bgcolor="LIGHTBLUE">'||name||'</td>','</tr>' from v$database;
 
−
select '<tr><td bgcolor="WHITE" width=20%>DB_UNIQUE_NAME</td><td bgcolor="LIGHTBLUE">'||value||'</td>','</tr>' from v$parameter where name='db_unique_name';
 
−
select '<tr><td bgcolor="WHITE" width=20%>INSTANCE_NAME</td><td bgcolor="LIGHTBLUE">'||instance_name||'</td>','</tr>' from v$instance;
 
−
 
 
−
prompt </table>
 
−
prompt <br>
 
−
 
 
−
 
 
−
-- *************************************** General
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
--NE PLUS INTERPRETER LE "&" par sqlplus POUR L'INSTANT, le HTML en a besoin
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Informations g&eacute;n&eacute;rales</b></font></td></tr>
 
−
prompt <tr><td width=20%><b>Base cr&eacute;&eacute;e le</b></td>
 
−
--INTERPRETER A NOUVEAU LE "&"
 
−
set define "&"
 
−
prompt <td bgcolor="LIGHTBLUE">
 
−
select CREATED from v$database;
 
−
prompt </td></tr><tr>
 
−
prompt <td><b>Up depuis le</b> </td>
 
−
prompt <td bgcolor="LIGHTBLUE">
 
−
select STARTUP_TIME from v$instance;
 
−
prompt </td></tr><tr>
 
−
prompt <td><b>Taille de blocs</b></td>
 
−
prompt <td bgcolor="LIGHTBLUE">
 
−
prompt  &dbloc octets
 
−
prompt </td></tr>
 
−
prompt <tr><td width=20%><b>Archive log mode</b></td>
 
−
REM prompt <td bgcolor="LIGHTBLUE">
 
−
select decode(log_mode,'ARCHIVELOG','<td bgcolor="#33FF33">'||log_mode||'<br>','<td bgcolor="#FF0000">'||log_mode||'<br>') from v$database;
 
−
prompt </td></tr></table>
 
−
prompt <br>
 
−
 
 
−
-- *************************************** Historique audit
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Historique d'audits</b></font></td></tr>
 
−
-- Creation table HISTAUDIT si necessaire
 
−
prompt <tr><td width=20%><b>Table historique</b></td>
 
−
prompt <td bgcolor="LIGHTBLUE">
 
−
WHENEVER sqlerror EXIT sql.sqlcode
 
−
DECLARE
 
−
    tabhist number;
 
−
    colmodif number;
 
−
 
 
−
BEGIN
 
−
    select count(table_name) into tabhist from dba_tables
 
−
    where table_name='HISTAUDIT'
 
−
    and owner = 'SYSTEM';
 
−
    select count(column_name) into colmodif from dba_tab_columns
 
−
    where table_name='HISTAUDIT' AND owner='SYSTEM' AND column_name='MODIFIED';
 
−
 
 
−
    IF tabhist = 0 THEN
 
−
      select count(tablespace_name) into tabhist from dba_tablespaces
 
−
      where tablespace_name='TOOLS';
 
−
      IF tabhist = 0 THEN
 
−
          dbms_output.put_line('Tablespace <b>TOOLS</b> inexistant !<br> Impossible de creer la table HISTAUDIT. Sortie du script.<br>');
 
−
          dbms_output.put_line('</td></tr></table>');
 
−
          raise_application_error(-20001,'Tablespace inexistant');
 
−
      END IF;
 
−
      EXECUTE IMMEDIATE 'create table SYSTEM.histaudit
 
−
                            (date_aud  date,
 
−
                            type_obj varchar2(5),
 
−
                            obj_name varchar2(30),
 
−
                            total number,
 
−
                            utilis number,
 
−
                            modified char(1) default 0)
 
−
                          TABLESPACE TOOLS';
 
−
      dbms_output.put_line('Creation table HISTAUDIT...<br>');
 
−
    ELSE
 
−
      IF colmodif=0 THEN
 
−
          EXECUTE IMMEDIATE 'alter table system.histaudit add MODIFIED char(1) default 0';
 
−
          dbms_output.put_line('Table HISTAUDIT modifiee (col MODIFIED).<br>');
 
−
      END IF;
 
−
      dbms_output.put_line('Table HISTAUDIT existante.<br>');
 
−
    END IF;
 
−
END;
 
−
/
 
−
WHENEVER sqlerror CONTINUE;
 
−
prompt </td></tr>
 
−
prompt <tr><td width=20%><b>Dernier Audit</b></td>
 
−
prompt <td bgcolor="LIGHTBLUE">
 
−
select decode(max(to_date(date_aud)),'','N/A',max(to_date(date_aud))) from system.histaudit
 
−
        where to_date(date_aud) < trunc(sysdate);
 
−
 
 
−
-- *************************************** Modif dernier audit ?
 
−
prompt </td></tr><tr>
 
−
set define off
 
−
prompt <td><b>Param&egrave;tres modifi&eacute;s lors du dernier audit</b></td>
 
−
set define "&"
 
−
select decode(max(modified),1,'<td  bgcolor="#FF0000">OUI</td></tr>','<td bgcolor="#33FF33">NON</td></tr>') from system.histaudit
 
−
        where to_date(date_aud) < trunc(sysdate);
 
−
prompt </td></tr></table>
 
−
prompt <br>
 
−
 
 
−
-- On force la remise à 0 de modified maintenant qu'on a signalé la modif du dernier audit
 
−
update system.histaudit set modified=0;
 
−
 
 
−
-- *************************************** SECTION STOCKAGE
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE">SECTION STOCKAGE</font></b></div>
 
−
prompt <hr>
 
−
 
 
−
-- *************************************** MISE A JOUR TABLE HISTORIQUE (TABLESPACES ET SEGMENTS)
 
−
delete from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='TBS';
 
−
insert into system.histaudit (
 
−
select sysdate, 'TBS', t.tablespace_name, t.total,
 
−
          decode(u.utilise,'',0,u.utilise), 0
 
−
from (select tablespace_name,
 
−
              round(sum(bytes)/(1024*1024),2) total
 
−
      from dba_data_files
 
−
      group by tablespace_name) t,
 
−
      (select tablespace_name,
 
−
              round(sum(blocks)*&dbloc/(1024*1024),2) utilise
 
−
      from dba_segments
 
−
      group by tablespace_name) u
 
−
where t.tablespace_name=u.tablespace_name(+)
 
−
UNION
 
−
select sysdate, 'TBS', tablespace_name, total,
 
−
          0, 0
 
−
from (select tablespace_name,
 
−
              round(sum(bytes)/(1024*1024),2) total
 
−
      from dba_temp_files
 
−
      group by tablespace_name) t);
 
−
delete from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='TAB';
 
−
insert into system.histaudit (
 
−
select sysdate, 'TAB', 'Total segments tables', total,
 
−
          0, 0
 
−
from (select decode(round(sum(bytes)/(1024*1024),2),NULL,0,round(sum(bytes)/(1024*1024),2)) total
 
−
      from dba_segments
 
−
      where segment_type like 'TABLE%'
 
−
      and owner not in &sysusers and owner not in &exusers));
 
−
delete from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='IND';
 
−
insert into system.histaudit (
 
−
select sysdate, 'IND', 'Total segments indexes', total,
 
−
          0, 0
 
−
from (select decode(round(sum(bytes)/(1024*1024),2),NULL,0,round(sum(bytes)/(1024*1024),2)) total
 
−
      from dba_segments
 
−
      where segment_type like 'INDEX%'
 
−
      and owner not in &sysusers and owner not in &exusers));
 
−
insert into system.histaudit (
 
−
select sysdate, 'AUT', 'Total segments autres', total,
 
−
          0, 0
 
−
from (select decode(round(sum(bytes)/(1024*1024),2),NULL,0,round(sum(bytes)/(1024*1024),2)) total
 
−
      from dba_segments
 
−
      where segment_type not like 'TABLE%'
 
−
      and segment_type not like 'INDEX%'
 
−
      and owner not in &sysusers and owner not in &exusers));
 
−
 
 
−
-- *************************************** TABLESPACES
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE" size=2>TABLESPACES</font></b></div>
 
−
prompt <hr>
 
−
-- *************************************** Liste tablespaces
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Liste des tablespaces</b></font></td></tr>
 
−
prompt <tr><td><b>Tablespace</b></td><td><b>Contenu</b></td><td><b>Statut</b></td></tr>
 
−
prompt
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||tablespace_name||'</td>', '<td bgcolor="LIGHTBLUE">'||contents||'</td>', decode(status,'ONLINE','<td bgcolor="LIGHTBLUE">'||status||'</td>','<td bgcolor="#FF0000">'||status||'</td>'),'</tr>' from dba_tablespaces
 
−
order by tablespace_name;
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Liste datafiles
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=6><font color="WHITE"><b>Liste des datafiles par tablespace</b></font></td></tr>
 
−
prompt <tr><td><b>Tablespace</b></td><td><b>Fichier</b></td><td><b>Taille (Mo)</b></td><td><b>Autoextensible</b></td><td><b>Next</b></td><td><b>MaxSize</b></td></tr>
 
−
 
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||TABLESPACE_NAME||'</td>','<td bgcolor="LIGHTBLUE">'||FILE_NAME||'</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||decode(round(sum(blocks)*&dbloc/(1024*1024),2),NULL,to_char('0','S99G999G990D00'),to_char(round(sum(blocks)*&dbloc/(1024*1024),2),'99G999G990D00'))||'</td>', decode(autoextensible,'NO','<td bgcolor="#FF0000" align=right>OFF</td>','<td bgcolor="#33FF33" align=right>ON</td>'), '<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(sum(increment_by)*&dbloc/(1024*1024),2),'99G999G990D00')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(sum(maxbytes)/(1024*1024),2),'99G999G990D00')||'</td>', '</tr>'
 
−
from DBA_DATA_FILES
 
−
group by tablespace_name,file_name,autoextensible
 
−
UNION
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||TABLESPACE_NAME||'</td>','<td bgcolor="LIGHTBLUE">'||FILE_NAME||'</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||decode(round(sum(blocks)*&dbloc/(1024*1024),2),NULL,to_char('0','S99G999G990D00'),to_char(round(sum(blocks)*&dbloc/(1024*1024),2),'99G999G990D00'))||'</td>', decode(autoextensible,'NO','<td bgcolor="#FF0000" align=right>OFF</td>','<td bgcolor="#33FF33" align=right>ON</td>'), '<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(sum(increment_by)*&dbloc/(1024*1024),2),'99G999G990D00')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(sum(maxbytes)/(1024*1024),2),'99G999G990D00')||'</td>', '</tr>'
 
−
from DBA_TEMP_FILES
 
−
group by tablespace_name,file_name,autoextensible
 
−
order by 2,3;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Volumétrie tablespaces
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=4><font color="WHITE"><b>Volum&eacute;trie</b></font></td></tr>
 
−
prompt <tr><td><b>Tablespace</b></td><td><b>Total (Mo)</b></td><td><b>Utilis&eacute; (Mo)</b></td><td><b>Libre (Mo)</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||t.tablespace_name||'</td>' Tablespace,
 
−
        '<td bgcolor="LIGHTBLUE" align=right>'||decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00')||'</td>',to_char(t.total,'99G999G990D00'))||'</td>' TOTAL,
 
−
        '<td bgcolor="LIGHTBLUE" align=right>'||decode(u.utilise,'','0,00',to_char(u.utilise,'99G999G990D00'))||'</td>' UTILISE,
 
−
        '<td bgcolor="LIGHTBLUE" align=right>'||decode(l.libre,NULL,to_char(t.total-(decode(u.utilise,'',0,u.utilise)),'99G999G990D00'),to_char(l.libre,'99G999G990D00'))||'</td>' LIBRE,'</tr>'
 
−
from (select tablespace_name,
 
−
              round(sum(bytes)/(1024*1024),2) total
 
−
      from dba_data_files
 
−
      group by tablespace_name) t,
 
−
      (select tablespace_name,
 
−
              round(sum(blocks)*&dbloc/(1024*1024),2) utilise
 
−
      from dba_segments
 
−
      group by tablespace_name) u,
 
−
      (select tablespace_name,
 
−
              round(sum(blocks)*&dbloc/(1024*1024),2) libre
 
−
      from dba_free_space
 
−
      group by tablespace_name) l
 
−
where t.tablespace_name=u.tablespace_name(+)
 
−
and u.tablespace_name=l.tablespace_name(+);
 
−
 
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||tablespace_name||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(total,'99G999G990D00')||'</td>',
 
−
          '<td bgcolor="LIGHTBLUE" align=right>0,00</td>' as utilise, '<td bgcolor="LIGHTBLUE" align=right>'||to_char(total,'99G999G990D00')||'</td>','</tr>' as libre
 
−
from (select tablespace_name,
 
−
              round(sum(bytes)/(1024*1024),2) total
 
−
      from dba_temp_files
 
−
      group by tablespace_name) t;
 
−
 
 
−
select  '<tr>','<td bgcolor="WHITE">TOTAL</td>',
 
−
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||to_char(dt.total+tt.total,'99G999G990D00')||'</b></font></td>' as total,
 
−
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||to_char(du.utilise+tu.utilise,'99G999G990D00')||'</b></font></td>' as utilise,
 
−
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||to_char(dl.libre+tl.libre,'99G999G990D00')||'</b></font></td>' as libre,'</tr>'
 
−
from (select round(sum(bytes)/(1024*1024),2) total from dba_data_files) dt,
 
−
      (select round(sum(bytes)/(1024*1024),2) total from dba_temp_files) tt,
 
−
      (select round(sum(blocks)*&dbloc/(1024*1024),2) utilise from dba_segments) du,
 
−
      (select 0 utilise from dual) tu,
 
−
      (select round(sum(blocks)*&dbloc/(1024*1024),2) libre from dba_free_space) dl,
 
−
      (select round(sum(bytes)/(1024*1024),2) libre from dba_temp_files) tl;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Diff tailles tablespaces depuis dernier audit
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Diff&eacute;rence de tailles depuis le dernier audit</b></font></td></tr>
 
−
prompt <tr><td><b>Tablespace</b></td><td><b>Total (Mo)</b></td><td><b>Utilis&eacute; (Mo)</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||a.obj_name||'</td>',
 
−
decode(SIGN(a.total-h.total),
 
−
      -1,'<td bgcolor="#33FF33" align=right>'||to_char(a.total-h.total,'S99G999G990D00')||'</td>',
 
−
        0,'<td bgcolor="LIGHTBLUE" align=right>'||to_char(a.total-h.total,'99G999G990D00')||'</td>',
 
−
        1,'<td bgcolor="ORANGE" align=right>'||to_char(a.total-h.total,'S99G999G990D00')||'</td>',
 
−
        NULL,'<td bgcolor="BLUE" align=right><font color="WHITE">Premier audit</font></td>'),
 
−
decode(SIGN(a.utilis-h.utilis),
 
−
      -1,'<td bgcolor="#33FF33" align=right>'||to_char(a.utilis-h.utilis,'S99G999G990D00')||'</td>',
 
−
        0,'<td bgcolor="LIGHTBLUE" align=right>'||to_char(a.utilis-h.utilis,'99G999G990D00')||'</td>',
 
−
        1,'<td bgcolor="ORANGE" align=right>'||to_char(a.utilis-h.utilis,'S99G999G990D00')||'</td>',
 
−
        NULL,'<td bgcolor="BLUE" align=right><font color="WHITE">Premier audit</font></td>'),'</tr>'
 
−
from
 
−
(select * from system.histaudit
 
−
where trunc(to_date(date_aud))=trunc(sysdate)
 
−
        and type_obj='TBS') a,
 
−
(select * from system.histaudit
 
−
where to_date(date_aud) like
 
−
(select max(to_date(date_aud)) from system.histaudit
 
−
                where to_date(date_aud) < trunc(sysdate)
 
−
and type_obj='TBS')
 
−
and type_obj='TBS') h
 
−
where a.obj_name=h.obj_name;
 
−
 
 
−
select  '<tr>','<td bgcolor="WHITE">TOTAL</td>',
 
−
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||to_char(round(sum(a.total-h.total)),'S99G999G990D00')||'</b></font></td>' as total,
 
−
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||to_char(round(sum(a.utilis-h.utilis)),'S99G999G990D00')||'</b></font></td>' as utilise,'</tr>'
 
−
from (select * from system.histaudit
 
−
where trunc(to_date(date_aud))=trunc(sysdate)
 
−
        and type_obj='TBS') a,
 
−
(select * from system.histaudit
 
−
where to_date(date_aud) like
 
−
(select max(to_date(date_aud)) from system.histaudit
 
−
                where to_date(date_aud) < trunc(sysdate)
 
−
                and type_obj='TBS')
 
−
and type_obj='TBS') h
 
−
where a.obj_name=h.obj_name;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Fragmentation TBS
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" colspan=3>
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print tips
 
−
prompt " width="20" height="20" alt="Tips..." title="Si fragmentation > 0%, n&eacute;cessite coalescing (ALTER TABLESPACE XXX COALESCE)"></td>
 
−
prompt <td align=center><font color="WHITE"><b>fragmentation des tablespaces</b></font></td><td width=10%>&nbsp;</td></tr></table></td></tr>
 
−
prompt <tr><td><b>Tablespace</b></td><td><b>Total</b></td><td><b>Fragmentation %</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||tablespace_name||'</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||total_extents||'</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(percent_extents_coalesced,2),'S990D00')||'</td>' pourcentage,'</tr>'
 
−
from dba_free_space_coalesced
 
−
where percent_extents_coalesced <> 100;
 
−
 
 
−
DECLARE cnt_frag number;
 
−
BEGIN
 
−
    select count(tablespace_name) into cnt_frag from dba_free_space_coalesced
 
−
    where percent_extents_coalesced <> 100;
 
−
    if cnt_frag=0 then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
end;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** SEGMENTS
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE" size=2>SEGMENTS (Objets utilisateurs)</font></b></div>
 
−
prompt <hr>
 
−
 
 
−
-- *************************************** Volumétrie tables et indexes
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Volum&eacute;trie segments utilisateurs</b></font></td></tr>
 
−
prompt <tr><td><b>Type de segments</b></td><td><b>Total (Mo)</b></td><td><b>Diff&eacute;rence de taille depuis le dernier audit</b></td></tr>
 
−
set define "&"
 
−
select  '<tr>','<td bgcolor="LIGHTBLUE">TABLES</td>',
 
−
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||decode(to_char(round(a.total,2),'99G999G990D00'),NULL,0)||'</b></font></td>',
 
−
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||decode(to_char(round(a.total-l.total,2),'S99G999G990D00'),NULL,0)||'</b></font></td>','</tr>'
 
−
from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments
 
−
where segment_type = 'TABLE'
 
−
        and owner not in &sysusers and owner not in &exusers) a,
 
−
(select * from system.histaudit
 
−
where to_date(date_aud) like
 
−
(select max(to_date(date_aud)) from system.histaudit
 
−
                where to_date(date_aud) < trunc(sysdate)
 
−
                and type_obj='TAB')
 
−
and type_obj='TAB') l;
 
−
select  '<tr>','<td bgcolor="LIGHTBLUE">INDEXES</td>',
 
−
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||decode(to_char(round(a.total,2),'99G999G990D00'),NULL,0)||'</b></font></td>',
 
−
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||decode(to_char(round(a.total-l.total,2),'S99G999G990D00'),NULL,0)||'</b></font></td>','</tr>'
 
−
from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments
 
−
where segment_type = 'INDEX'
 
−
        and owner not in &sysusers and owner not in &exusers) a,
 
−
(select * from system.histaudit
 
−
where to_date(date_aud) like
 
−
(select max(to_date(date_aud)) from system.histaudit
 
−
                where to_date(date_aud) < trunc(sysdate)
 
−
                and type_obj='IND')
 
−
and type_obj='IND') l;
 
−
select  '<tr>','<td bgcolor="LIGHTBLUE">AUTRES (LOG SEGMENTS, LOB INDEXES, CLUSTERS)</td>',
 
−
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||decode(to_char(round(a.total,2),'99G999G990D00'),NULL,0)||'</b></font></td>',
 
−
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||decode(to_char(round(a.total-l.total,2),'S99G999G990D00'),NULL,0)||'</b></font></td>','</tr>'
 
−
from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments
 
−
where segment_type not in ('TABLE', 'INDEX')
 
−
        and owner not in &sysusers and owner not in &exusers) a,
 
−
(select * from system.histaudit
 
−
where to_date(date_aud) like
 
−
(select max(to_date(date_aud)) from system.histaudit
 
−
                where trunc(to_date(date_aud)) < trunc(sysdate)
 
−
                and type_obj='AUT')
 
−
and type_obj='AUT') l;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** REDO LOG FILES
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE" size=2>REDO LOG FILES</font></b></div>
 
−
prompt <hr>
 
−
-- *************************************** Redo logs files
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Liste des fichiers redo logs</b></font></td></tr>
 
−
prompt <tr><td width=8%><b>Groupe</b></td><td width=25%><b>Fichier</b></td><td width=15%><b>Taille (Mo)</b></td></tr>
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||l.group#||'</td>', '<td bgcolor="LIGHTBLUE">'||member||'</td>','<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(bytes/(1024*1024),2),'99G999G990D00')||'</td>','</tr>' from v$log l,v$logfile f where l.group# = f.group# order by l.group#;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Statistiques switchs REDO LOGS
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" colspan=3>
 
−
set define off
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print info
 
−
prompt " width="20" height="20" alt="Tips..." title="ATTENTION : l'historique des logs peut &ecirc;tre supprim&eacute; au fur et &agrave; mesure : ces statistiques risquent de ne pas &ecirc;tre viables."></td>
 
−
prompt <td align=center><font color="WHITE"><b>Statistiques switchs REDO LOGS</b></font></td></tr></table></td></tr>
 
−
set define "&"
 
−
 
 
−
prompt <tr><td width=15%><b>Statistique</b></td><td width=15%><b>Date</b></td><td width=15%><b>Valeur</b></td></tr>
 
−
prompt <tr><td bgcolor="LIGHTBLUE" valign=top>Nombre de switchs par jour (depuis 30 jours)</td>
 
−
prompt <td bgcolor="LIGHTBLUE" align=right>
 
−
select trunc(first_time)||'<br/>' from v$loghist
 
−
where first_time > (sysdate-30)
 
−
group by trunc(first_time)
 
−
order by trunc(first_time);
 
−
prompt </td><td bgcolor="LIGHTBLUE" align=right>
 
−
select count(first_time)||'<br/>' from v$loghist
 
−
where first_time > (sysdate-30)
 
−
group by trunc(first_time)
 
−
order by trunc(first_time);
 
−
prompt </td></tr>
 
−
 
 
−
-- *************************************** Stats switchs
 
−
select '<tr>','<td bgcolor="WHITE">Moyenne par jour :</td>', '<td bgcolor="BLUE" align=right colspan=2><font color="WHITE"><b>'||round(avg(nbc),0)||'</font></b></td>','</tr>'
 
−
from (select count(*) as nbc from v$loghist a, v$loghist b
 
−
      where a.first_change#=b.switch_change#
 
−
      and to_char(a.first_time,'dd/mm/yyyy')=to_char(b.first_time,'dd/mm/yyyy')
 
−
      group by to_char(a.first_time,'dd/mm/yyyy'));
 
−
 
 
−
-- *************************************** resume par mois (depuis 1 an)
 
−
prompt <tr><td bgcolor="LIGHTBLUE">Nombre de switchs par mois (depuis 1 an)</td>
 
−
prompt <td bgcolor="LIGHTBLUE" align=right>
 
−
select to_char(to_date(first_time),'mm/yyyy')||'<br/>' from v$loghist
 
−
where first_time > (sysdate-365)
 
−
group by to_char(to_date(first_time),'mm/yyyy')
 
−
order by to_char(to_date(first_time),'mm/yyyy');
 
−
prompt <td bgcolor="LIGHTBLUE" align=right>
 
−
select count(*)||'<br/>' from v$loghist
 
−
where first_time > (sysdate-365)
 
−
group by to_char(to_date(first_time),'mm/yyyy')
 
−
order by to_char(to_date(first_time),'mm/yyyy');
 
−
prompt </td></tr>
 
−
 
 
−
-- *************************************** temps minimum entre 2 switchs
 
−
select '<tr>','<td bgcolor="WHITE">Temps MIN. entre 2 switchs :</td>', '<td bgcolor="BLUE" align=right colspan=2><font color="WHITE">'||to_char(min(a.first_time-b.first_time)*24*3600,'99999G990')||' secondes</td>','</tr>'
 
−
from v$loghist a, v$loghist b
 
−
where a.first_change#=b.switch_change#;
 
−
 
 
−
-- *************************************** temps maximum entre 2 switchs
 
−
select '<tr>','<td bgcolor="WHITE">Temps MAX. entre 2 switchs :</td>', '<td bgcolor="BLUE" align=right colspan=2><font color="WHITE">'||to_char(max(a.first_time-b.first_time)*24*3600,'99999G990')||' secondes</td>','</tr>'
 
−
from v$loghist a, v$loghist b
 
−
where a.first_change#=b.switch_change#;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** UNDO
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE" size=2>UNDO / ROLLBACK SEGMENTS</font></b></div>
 
−
prompt <hr>
 
−
-- *************************************** Rollback segments ********************
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Rollback segments</b></font></td></tr>
 
−
prompt <tr><td width=8%><b>Segment</b></td><td width=25%><b>Tablespace</b></td><td width=15%><b>Statut</b></td></tr>
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||segment_name||'</td>', '<td bgcolor="LIGHTBLUE">'||tablespace_name||'</td>','<td bgcolor="LIGHTBLUE">'||status||'</td>','</tr>' from dba_rollback_segs;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Stats rollback segs
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=4><font color="WHITE"><b>Statistiques rollback segments</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Segment USN</b></td><td width=15%><b>Nom</b></td><td width=15%><b>Nombre SHRINKS</b></td><td width=15%><b>taille moyenne SHRINKS</b></td></tr>
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||v$rollstat.usn||'</td>','<td bgcolor="LIGHTBLUE">'||name||'</td>','<td bgcolor="LIGHTBLUE" align=right>'||shrinks||'</td>','<td bgcolor="LIGHTBLUE" align=right>'||aveshrink||'</td>','</tr>' from v$rollstat,v$rollname
 
−
where v$rollstat.usn=v$rollname.usn;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** CONFLITS D'ACCES
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE" size=2>STATISTIQUES D'ACCES DISQUE</font></b></div>
 
−
prompt <hr>
 
−
 
 
−
-- *************************************** contentions de basculement
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
 
 
−
prompt <tr><td bgcolor="#3399CC" colspan=4>
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print info
 
−
prompt " width="20" height="20" alt="Tips..." title="'Checkpoint not complete' : ce message apparait si le check point pr&eacute;c&eacute;dent n'est pas fini lors d'un CPKT ou d'un switch (qui occasionne lui-m&ecirc;me un ckpt). Augmenter la taille des fichiers redo logs, ou leur nombre si &ccedil;a ne suffit pas.">
 
−
prompt &nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print tips
 
−
prompt " width="20" height="20" alt="Info..." title="voir messages 'Checkpoint not complete' dans le fichier alert<SID>.log"></td>
 
−
prompt <td align=center><font color="WHITE"><b>Contentions de basculement redo logs</b></font></td><td width=10%>&nbsp;</td></tr></table></td></tr>
 
−
prompt <tr><td width=15%><b>Nom</b></td><td width=15%><b>Ev&egrave;nement</b></td><td width=15%><b>Wait (en secondes)</b></td><td width=15%><b>Etat</b></td></tr>
 
−
 
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||sid||'</td>','<td bgcolor="LIGHTBLUE">'||event||'</td>','<td bgcolor="LIGHTBLUE">'||seconds_in_wait||'</td>','<td bgcolor="LIGHTBLUE">'||state||'</td>','</tr>'
 
−
from v$session_wait
 
−
where event like 'log%';
 
−
 
 
−
DECLARE cnt_event number := 0;
 
−
BEGIN
 
−
    select count(sid) into cnt_event from v$session_wait
 
−
    where event like 'log%'
 
−
    and rownum = 1;
 
−
    if cnt_event=0 then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
end;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Conflits d'acces disque
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" colspan=2>
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print info
 
−
prompt " width="20" height="20" alt="Tips..." title="ATTENTION : ces valeurs sont cumul&eacute;es depuis le dernier d&eacute;marrage.">
 
−
prompt &nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print tips
 
−
prompt " width="20" height="20" alt="Tips..." title="Si data block > 0 (trop de blocs modifi&eacute;s dans le buffer cache) augmenter le nombre de processus DBWR.<br> Si segment header ou free list > 0 (multiplier les freelists en re-cr&eacute;ant la table avec nouveau param&egrave;tre).<br> Si un des param&egrave;tres UNDO est sup&eacute;rieur &agrave; 1% ou 2% besoin de plus de rollback segments."></td>
 
−
prompt <td align=center><font color="WHITE"><b>Conflits d'acc&egrave;s disque</b></font></td><td width=10%>&nbsp;</td></tr></table></td></tr>
 
−
prompt <tr><td width=15%><b>Classe</b></td><td width=15%><b>Nombre</b></td></tr>
 
−
 
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||class||'</td>','<td bgcolor="'||CouleurLimite(count,10000000,9990000,1)||'" align=right>'||count||'</td>','</tr>' from v$waitstat;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Evenements systemes
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=4><font color="WHITE"><b>Ev&eacute;nements syst&egrave;me</b></font></td></tr>
 
−
prompt <tr><td><b>Evenement</b></td><td><b>Total waits</b></td><td><b>Timeout</b></td><td><b>Average time</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||event||'</td>','<td bgcolor="LIGHTBLUE" align=right>'||total_waits||'</td>','<td bgcolor="'||decode(total_timeouts,0,'LIGHTBLUE','ORANGE')||'" align=right>'||total_timeouts||'</td>','<td bgcolor="LIGHTBLUE" align=right>'||to_char(average_wait,'999G990D00')||'</td>','</tr>' from v$system_event
 
−
where event like 'log%' or event like 'db file%';
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** SECTION INSTANCE
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE">SECTION INSTANCE</font></b></div>
 
−
prompt <hr>
 
−
-- *************************************** POOLS MEMOIRE
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE" size=2>SGA</font></b></div>
 
−
prompt <hr>
 
−
 
 
−
-- *************************************** Taille SGA
 
−
 
 
−
-- TODO : ajouter TIPS KGH NO ACCESS sur la page html
 
−
--      db_cache deborde car pas assez de place. Ajuster DB_KEEP_CACHE_SIZE (taille mini à garder) et DB_RECYCLE_CACHE_SIZE (taille
 
−
--select pool,sum(bytes) from  v$sgastat where pool is not null group by pool union select 'total SGA',sum(bytes) from  v$sgastat where pool is not null union select 'KGH NO ACCESS', sum(bytes) from  v$sgastat where name = 'KGH: NO ACCESS' union select 'total global',sum(bytes) from  v$sgastat;
 
−
 
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Taille totale SGA</b></font></td></tr>
 
−
prompt <tr><td><b>SGA</b></td><td><b>valeur (Mo)</b></td></tr>
 
−
set define off
 
−
 
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">Total SGA instance</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(sum(s.bytes)/(1024*1024),2),'99G999G990D00')||'</td>','</tr>'
 
−
from v$sgastat s where name != 'KGH: NO ACCESS'
 
−
UNION ALL
 
−
--CouleurLimite (valeurAtester number, limite number, plage number, superieur int)
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">KGH NO ACCESS (Echange db_cache/SGA si mode ASMM)</td>',
 
−
'<td bgcolor="'||decode(to_char(round(sum(s.bytes)/(1024*1024),2),'99G999G990D00'),NULL,'#33FF33',CouleurLimite(to_char(round(sum(s.bytes)/(1024*1024),2),'99G999G990D00'),0,0,1))||'" align=right>'||decode(to_char(round(sum(s.bytes)/(1024*1024),2),'99G999G990D00'),NULL,to_char('0','99G999G990D00'),to_char(round(sum(s.bytes)/(1024*1024),2),'99G999G990D00'))||'</td>','</tr>'
 
−
from v$sgastat s where name = 'KGH: NO ACCESS'
 
−
UNION ALL
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">sga_max_size SPFILE</td>',
 
−
decode(to_char(round(p.value/(1024*1024),2)),
 
−
'','<td bgcolor="LIGHTBLUE" align=right>Par d&eacute;faut</td>','<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(p.value/(1024*1024),2),'99G999G990D00')||'</td>'),'</tr>'
 
−
from v$spparameter p
 
−
where p.name = 'sga_max_size'
 
−
UNION ALL
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">sga_max_size INSTANCE</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(p.value/(1024*1024),2),'99G999G990D00')||'</td>','</tr>'
 
−
from v$parameter p
 
−
where p.name = 'sga_max_size'
 
−
UNION ALL
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">sga_target INSTANCE</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(p.value/(1024*1024),2),'99G999G990D00')||'</td>','</tr>'
 
−
from v$parameter p
 
−
where p.name = 'sga_target'
 
−
UNION ALL
 
−
select '<tr>','<td bgcolor="WHITE">TOTAL</td>',
 
−
'<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||to_char(round(sum(s.bytes)/(1024*1024),2),'99G999G990D00')||'</b></font></td>','</tr>'
 
−
from v$sgastat s;
 
−
set define "&"
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** MISE A JOUR TABLE HISTORIQUE
 
−
delete from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='SGA';
 
−
insert into system.histaudit (
 
−
select sysdate,'SGA','sga_size (spfile/max_used)',total,valeur,0 from
 
−
(select round(value/(1024*1024),2) total from v$parameter where name = 'sga_max_size') p,
 
−
(select round(sum(bytes)/(1024*1024),2) valeur from v$sgastat) s
 
−
);
 
−
insert into system.histaudit (
 
−
select sysdate, 'SGA', 'shared_pool (spfile/used)', t.Shared_pool_size, u.utilise, 0
 
−
from (select name, round(value/(1024*1024),2) Shared_pool_size
 
−
      from v$parameter where name='shared_pool_size') t,
 
−
      (select round(sum(bytes)/(1024*1024),2) Utilise
 
−
      from v$sgastat where pool='shared pool' and name <> 'free memory') u);
 
−
insert into system.histaudit (
 
−
select sysdate,'SGA','buffer_cache',round(value/(1024*1024),2), 0, 0 from v$sga
 
−
where name = 'Database Buffers');Redo
 
−
 
 
−
 
 
−
-- *************************************** Diff memoire utilisee
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Diff&eacute;rence de tailles depuis le dernier audit</b></font></td></tr>
 
−
prompt <td><b>Espaces m&eacute;moire</b></td><td><b>SPFILE (Mo)</b></td><td><b>Utilis&eacute; (Mo)</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||a.obj_name||'</td>',
 
−
decode(SIGN(a.total-h.total),
 
−
      -1,'<td bgcolor="#33FF33" align=right>'||to_char(a.total-h.total,'S99G999G990D00')||'</td>',
 
−
        0,'<td bgcolor="LIGHTBLUE" align=right>'||to_char(a.total-h.total,'99G999G990D00')||'</td>',
 
−
        1,'<td bgcolor="ORANGE" align=right>'||to_char(a.total-h.total,'S99G999G990D00')||'</td>') TOTAL,
 
−
decode(SIGN(a.utilis-h.utilis),
 
−
      -1,'<td bgcolor="#33FF33" align=right>'||to_char(a.utilis-h.utilis,'S99G999G990D00')||'</td>',
 
−
        0,'<td bgcolor="LIGHTBLUE" align=right>'||to_char(a.utilis-h.utilis,'99G999G990D00')||'</td>',
 
−
        1,'<td bgcolor="ORANGE" align=right>'||to_char(a.utilis-h.utilis,'S99G999G990D00')||'</td>') UTILISE,'</tr>'
 
−
from
 
−
(select * from system.histaudit
 
−
where trunc(to_date(date_aud))=trunc(sysdate)
 
−
and type_obj='SGA') a,
 
−
(select * from system.histaudit
 
−
where to_date(date_aud) like
 
−
(select max(to_date(date_aud)) from system.histaudit
 
−
                where to_date(date_aud) < trunc(sysdate)
 
−
                and type_obj='SGA')
 
−
and type_obj='SGA') h
 
−
where a.obj_name=h.obj_name;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Pools memoire
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Pools m&eacute;moire</b></font></td></tr>
 
−
prompt <tr><td><b>Pool</b></td><td><b>valeur (Mo)</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||name||'</td>' NOM,'<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(value/(1024*1024),2),'99G999G990D00')||'</td>' valeur,'</tr>' from v$sga;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** SHARED POOL
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE" size=2>SHARED POOL</font></b></div>
 
−
prompt <hr>
 
−
-- *************************************** Shared pool
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=4><font color="WHITE"><b>Shared pool area</b></font></td></tr>
 
−
prompt <tr><td><b>Pool</b></td><td><b>Total (Mo)</b></td><td><b>Utilis&eacute; (Mo)</b></td><td><b>Libre (Mo)</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||t.name||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(t.total,'99G999G990D00')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(u.utilise,'99G999G990D00')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(l.libre,'99G999G990D00')||'</td>','</tr>'
 
−
from (select name, round(value/(1024*1024),2) Total
 
−
      from v$parameter where name='shared_pool_size') t,
 
−
      (select round(sum(bytes)/(1024*1024),2) Utilise
 
−
      from v$sgastat where pool='shared pool' and name <> 'free memory') u,
 
−
      (select round(sum(bytes)/(1024*1024),2) libre
 
−
      from v$sgastat where pool='shared pool' and name = 'free memory') l;
 
−
-- select name,round(bytes/(1024*1024),2) valeur
 
−
-- from v$sgastat where pool='shared pool';
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Dictionary cache
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
 
 
−
prompt <tr><td bgcolor="#3399CC" colspan=5>
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print info
 
−
prompt " width="20" height="20" alt="Info..." title="GETS column represents the total number of times a process or Oracle asked for the item named in the column PARAMETER. GETMISSES column represents the number of times a request for dictionary information couldn't find that information in the dictionary cache and instead had to go to the SYSTEM tablespace to retrieve the information. SCANS column is the number of scan requests. SCANMISSES column is the times a scan failed to find the data in the cache.">
 
−
prompt &nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print tips
 
−
prompt " width="20" height="20" alt="Tips..." title="Augmenter SHARED_POOL_SIZE si les ratios (Library ET Dictionary cache) sont inf&eacute;rieur &agrave; 85%."></td>
 
−
prompt <td align=center><font color="WHITE"><b>Dictionary cache</b></font></td><td width=10%>&nbsp;</td></tr></table></td></tr>
 
−
 
 
−
prompt <tr><td width=15%><b>Gets</b></td><td width=15%><b>Get Misses</b></td><td width=15%><b>Scan</b></td><td width=15%><b>Scan Misses</b></td><td align=center><b>Ratio</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||sum(gets)||'</td>','<td bgcolor="LIGHTBLUE">'||sum(getmisses)||'</td>','<td bgcolor="LIGHTBLUE">'||sum(scans)||'</td>','<td bgcolor="LIGHTBLUE">'||sum(scanmisses)||'</td>',
 
−
'<td bgcolor="'||CouleurLimite(round((sum(gets)-sum(getmisses))/sum(gets),2)*100,85,5,0)||'" align=right>'||round((sum(gets)-sum(getmisses))/sum(gets),2)*100||' % </td>','</tr>'
 
−
from v$rowcache;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Library cache
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
 
 
−
prompt <tr><td bgcolor="#3399CC" colspan=4>
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print info
 
−
prompt " width="20" height="20" alt="Info..." title="Library Cache Misses indicate that the Shared Pool is not big enough to hold the shared SQL area for all concurrently open cursors. If you have no Library Cache misses (PINS = 0), you may get a small increase in performance by setting CURSOR_SPACE_FOR_TIME = TRUE which prevents ORACLE from deallocating a shared SQL area while an application cursor associated with it is open. For Multi-threaded server, add 1K to SHARED_POOL_SIZE per user.">
 
−
prompt &nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print tips
 
−
prompt " width="20" height="20" alt="Info..." title="Augmenter SHARED_POOL_SIZE si les ratios (Library ET Dictionary cache) est inf&eacute;rieur &agrave; 85%"></td>
 
−
prompt <td align=center><font color="WHITE"><b>Library cache</b></font></td><td width=10%>&nbsp;</td></tr></table></td></tr>
 
−
 
 
−
prompt <tr><td><b>Executions</b></td><td><b>Rechargements</b></td><td colspan=2><b>Ratio</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE" align=right>'||sum(pins)||'</td>' exec,
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||sum(reloads)||'</td>' recharg,
 
−
'<td bgcolor="'||CouleurLimite(round((sum(pins)-sum(reloads))/sum(pins),2)*100,85,5,0)||'" align=right colspan=2>'||round((sum(pins)-sum(reloads))/sum(pins),2)*100||' %</td>' ratio,'</tr>'
 
−
from v$librarycache;
 
−
 
 
−
-- *************************************** Stat library cache
 
−
set define off
 
−
 
 
−
prompt <tr><td bgcolor="#3399CC" colspan=4>
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print info
 
−
prompt " width="20" height="20" alt="Info..." title="GETS columns is the number of times a lock was requested for objects of this namespace. GETHITS column is the number of times an object's handle was found in memory. PINS column is the times a PIN was requested for objects of this namespace. PINHITS column is the number of times all of the metadata pieces of the library object were found in memory.">
 
−
prompt &nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print tips
 
−
prompt " width="20" height="20" alt="Tips..." title="Rapprocher ces statistiques des ratios dictionary et library cache.<br>Augmenter SHARED_POOL_SIZE si les ratios sont inf&eacute;rieur &agrave; 90%. BODY et INDEX ne sont pas significatifs et peuvent &ecirc;tre ignor&eacute;s."></td>
 
−
prompt <td align=center><font color="WHITE"><b>Statistiques library cache par types de requ&ecirc;tes</b></font></td><td width=10%>&nbsp;</td></tr></table></td></tr>
 
−
 
 
−
prompt <tr><td colspan=2><b>Namespace</b></td><td><b>GetHits</b></td><td><b>PinHits</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE" colspan=2>'||namespace||'</td>',
 
−
'<td bgcolor="'||CouleurLimite(round(gethitratio,2)*100,90,5,0)||'" align=right>'||round(gethitratio,2)*100||' %</td>',
 
−
'<td bgcolor="'||CouleurLimite(round(pinhitratio,2)*100,90,5,0)||'" align=right>'||round(pinhitratio,2)*100||' %</td>','</tr>'
 
−
from v$librarycache;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Requetes les plus gourmandes
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=8><font color="WHITE"><b>Requ&ecirc;tes les plus gourmandes en ressources</b></font></td></tr>
 
−
prompt <tr><td><b>Adresse</b></td><td><b>Ex&eacute;cutions</b></td><td><b>Moy. tris</b></td><td><b>Moyenne lectures disque</b></td><td><b>Moyenne cpu(&micro;sec)</b></td><td><b>Moyenne buffers</b></td><td><b>Requ&ecirc;te sql</b></td></tr>
 
−
set define "&"
 
−
SELECT '<tr>','<td bgcolor="LIGHTBLUE">'||address||'</td>',
 
−
'<td bgcolor="LIGHTBLUE"><b>'||executions||'</b></td>',
 
−
'<td bgcolor="LIGHTBLUE">'||round(sorts/executions,0)||'</td>',
 
−
'<td bgcolor="LIGHTBLUE">'||round(disk_reads/executions,0)||'</td>',
 
−
'<td bgcolor="LIGHTBLUE">'||round(cpu_time/executions,0)||'</td>',
 
−
'<td bgcolor="LIGHTBLUE">'||round(buffer_gets/executions,0)||'</td>',
 
−
'<td bgcolor="LIGHTBLUE">'||sql_text||'</td>','</tr>'
 
−
FROM  v$sqlarea
 
−
WHERE  executions > 99
 
−
and    COMMAND_TYPE in (2,3,6,7)
 
−
AND    BUFFER_GETS > 100000
 
−
AND ROWNUM < 11
 
−
ORDER BY executions DESC;
 
−
 
 
−
DECLARE cnt_rq number := 0;
 
−
BEGIN
 
−
    select count(address) into cnt_rq from v$sqlarea
 
−
    WHERE  executions > 99
 
−
    and    COMMAND_TYPE in (2,3,6,7)
 
−
    AND    BUFFER_GETS > 100000
 
−
    and rownum = 1;
 
−
    if cnt_rq=0 then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td>');
 
−
      dbms_output.put_line('<td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
end;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** AUTRES POOLS
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE" size=2>AUTRES POOLS</font></b></div>
 
−
prompt <hr>
 
−
-- *************************************** Large pool
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=4><font color="WHITE"><b>Large pool</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Nom</b></td><td width=15%><b>Total (Mo)</b></td><td width=15%><b>Utilise (Mo)</b></td><td width=15%><b>Libre (Mo)</b></td></tr>
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||t.name||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(t.total,NULL,0,t.total),'99G999G990D00')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(u.utilise,NULL,0,t.total),'99G999G990D00')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(l.libre,NULL,0,t.total),'99G999G990D00')||'</td>','</tr>'
 
−
from (select name, round(value/(1024*1024),2) total
 
−
      from v$parameter where name='large_pool_size') t,
 
−
      (select round(sum(bytes)/(1024*1024),2) utilise
 
−
      from v$sgastat where pool = 'large pool' and name <> 'free memory') u,
 
−
      (select round(sum(bytes)/(1024*1024),2) libre
 
−
      from v$sgastat where pool = 'large pool' and name = 'free memory') l;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Java pool
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=4><font color="WHITE"><b>Java pool</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Nom</b></td><td width=15%><b>Total (Mo)</b></td><td width=15%><b>Utilis&eacute; (Mo)</b></td><td width=15%><b>Libre (Mo)</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||substr(t.name,1,30)||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(t.total,NULL,0,t.total),'99G999G990D00')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(u.utilise,NULL,0,t.total),'99G999G990D00')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(l.libre,NULL,0,t.total),'99G999G990D00')||'</td>','</tr>'
 
−
from (select name, round(value/(1024*1024),2) total
 
−
      from v$parameter where name='java_pool_size') t,
 
−
      (select round(sum(bytes)/(1024*1024),2) utilise
 
−
      from v$sgastat where pool = 'java pool' and name <> 'free memory') u,
 
−
      (select round(sum(bytes)/(1024*1024),2) libre
 
−
      from v$sgastat where pool = 'java pool' and name = 'free memory') l;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** BUFFER CACHE
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE" size=2>BUFFER CACHE</font></b></div>
 
−
prompt <hr>
 
−
-- *************************************** Buffer cache : Blocs lus E/S
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
 
 
−
prompt <tr><td bgcolor="#3399CC" colspan=2>
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print tips
 
−
prompt " width="20" height="20" alt="Tips..." title="Si ce ratio est tr&egrave;s sup&eacute;rieur &agrave; 10, laisser tel quel Sinon ajuster _small_table_threshold (defaut 4) pour &ecirc;tre inf&eacute;rieur &agrave; 10."></td>
 
−
prompt <td align=center><font color="WHITE"><b>Buffer cache : Blocs lus E/S</b></font></td><td width=10%>&nbsp;</td></tr></table></td></tr>
 
−
 
 
−
prompt <tr><td width=15%><b>Nom</b></td><td width=15%><b>Valeur</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||name||'</td>','<td bgcolor="LIGHTBLUE" align=right>'||value||'</td>','</tr>'
 
−
from v$sysstat
 
−
where name like 'table scan%'
 
−
UNION ALL
 
−
select '<tr>','<td bgcolor="WHITE"> Ratio </td>', '<td bgcolor="'||CouleurLimite(round(t1.value/(t2.value+t3.value),2),15,1,1)||'" align=right>'||to_char(round(t1.value/(t2.value+t3.value),2),'99G990D00')||'</td>','</tr>' from v$sysstat t1, v$sysstat t2, v$sysstat t3
 
−
where t1.name like 'table scan blocks gotten%'
 
−
and t2.name like 'table scans (short tables)%'
 
−
and t3.name like 'table scans (long tables)%';
 
−
-- *************************************** Buffer cache : hit ratio
 
−
set define off
 
−
 
 
−
prompt <tr><td bgcolor="#3399CC" colspan=2>
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print tips
 
−
prompt " width="20" height="20" alt="Tips..." title="Augmenter DB_BLOCK_BUFFERS (ou DB_BLOCK_SIZE) pour que le ratio soit entre 70% et 80%. Au-dessus de 98% on peut gagner de la m&eacute;moire en r&eacute;duisant les buffers."></td>
 
−
prompt <td align=center><font color="WHITE"><b>Buffer cache : hit ratio</b></font></td><td width=10%>&nbsp;</td></tr></table></td></tr>
 
−
 
 
−
prompt <tr><td width=15%><b>Nom</b></td><td width=15%><b>Valeur</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||name||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||value||'</td>','</tr>' from v$sysstat
 
−
where name in ('db block gets','consistent gets','physical reads')
 
−
UNION ALL
 
−
select '<tr>','<td bgcolor="WHITE">Ratio</td>','<td bgcolor="'||CouleurLimite(round(((t1.value+t2.value)-t3.value)/(t1.value+t2.value),2)*100,85,5,0)||'" align=right>'||round(((t1.value+t2.value)-t3.value)/(t1.value+t2.value),2)*100||' %</td>','</tr>'
 
−
from v$sysstat t1, v$sysstat t2, v$sysstat t3
 
−
where t1.name='db block gets' and t2.name='consistent gets' and t3.name='physical reads';
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Redo buffers ****************************
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE" size=2>REDO BUFFERS</font></b></div>
 
−
prompt <hr>
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Redo buffers</b></font></td></tr>
 
−
prompt <tr><td width=15% colspan=2><b>Pool</b></td><td width=15%><b>Taille (Mo)</b></td></tr>
 
−
select '<tr>','<td bgcolor="LIGHTBLUE" colspan=2>'||name||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(value/(1024*1024),2),'99G999G990D00')||'</td>','</tr>' from v$parameter
 
−
where name='log_buffer';
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Stats redo logs (contentions)
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
 
 
−
prompt <tr><td bgcolor="#3399CC" colspan=2>
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print tips
 
−
prompt " width="20" height="20" alt="Tips..." title="augmenter LOG_BUFFER pour que REDO LOG SPACE REQUESTS soit proche de 0. Si le ratio wastage/size est inf&eacute;rieur à 80%, il y a trop de perte de place dans les fichiers redo, ce qui indique une activit&eacute; trop forte du LGWR. V&eacute;rifier les checkpoints et/ou les switchs."></td>
 
−
prompt <td align=center><font color="WHITE"><b>Statistiques redo logs (contentions)</b></font></td><td width=10%>&nbsp;</td></tr></table></td></tr>
 
−
 
 
−
prompt <tr><td width=15%><b>Nom</b></td><td width=15%><b>Valeur</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||name||'</td>','<td bgcolor="'||decode(name,'redo log space requests',CouleurLimite(value,100000,1000,1),'redo log space wait time',CouleurLimite(value,100000,1000,1),'LIGHTBLUE')||'" align=right>'||value||'</td>','</tr>' from v$sysstat
 
−
where name like 'redo%'
 
−
UNION ALL
 
−
select '<tr>','<td bgcolor="WHITE">Ratio wastage/size</td>', '<td bgcolor="'||CouleurLimite(round(1-(t1.value/t2.value),2)*100,70,5,0)||'" align=right>'||round(1-(t1.value/t2.value),2)*100||' %</td>','</tr>'
 
−
from v$sysstat t1, v$sysstat t2
 
−
where t1.name like 'redo wastage'
 
−
and t2.name like 'redo size';
 
−
 
 
−
prompt </table><br>
 
−
-- *************************************** Stats latchs (contentions)
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
 
 
−
prompt <tr><td bgcolor="#3399CC" colspan=3>
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print tips
 
−
prompt " width="20" height="20" alt="Tips..." title="si un des ratio excede 5%, les performances sont affect&eacute;es, diminuer LOG_SMALL_ENTRY_SIZE." width=15%></td>
 
−
prompt <td align=center><font color="WHITE"><b>Statistiques latchs (contentions)</b></font></td><td width=10%>&nbsp;</td></tr></table></td></tr>
 
−
 
 
−
prompt <tr><td width=15%><b>Nom</b></td><td width=15%><b>Ratio misses/gets</b></td><td width=25%><b>Ratio immediate misses/immediate gets</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||name||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(sum(misses)/(sum(gets)+0.00000000001)*100),'990D00')||' %</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(sum(immediate_misses)/(sum(immediate_misses+immediate_gets)+0.00000000001)*100),'990D00')||' %</td>','</tr>'
 
−
from  v$latch
 
−
where  name in ('redo allocation',  'redo copy')
 
−
group by name;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** zone de tri
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE" size=2>SORT AREA</font></b></div>
 
−
prompt <hr>
 
−
-- *************************************** Taille zone de tri
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Taille zone de tri</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Pool</b></td><td width=15%><b>Taille (Mo)</b></td></tr>
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||name||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(value/(1024*1024),2),'99G999G990D00')||'</td>','</tr>' from v$parameter
 
−
where name='sort_area_size';
 
−
-- *************************************** Statistiques zone de tri
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Statistiques zone de tri</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Nom</b></td><td width=15%><b>Valeur</b></td></tr>
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||name||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||value||'</td>','</tr>' from v$sysstat
 
−
where name like 'sort%'
 
−
UNION ALL
 
−
select '<tr>','<td bgcolor="WHITE">Ratio</td>', '<td bgcolor="'||CouleurLimite(round(1-(t1.value/t2.value),2)*100,85,5,0)||'" align=right>'||round(1-(t1.value/t2.value),2)*100||' %</td>','</tr>' from v$sysstat t1, v$sysstat t2
 
−
where t1.name like 'sorts (disk)%'
 
−
and t2.name like 'sorts (memory)%';
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** PGA
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE" size=2>PGA</font></b></div>
 
−
prompt <hr>
 
−
-- *************************************** Statistiques PGA
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Cumuls PGA</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Actuel (Mo)</b></td><td width=15%><b>Max allou&eacute; (Mo)</b></td><td width=15%><b>PGA_AGGREGATE_TARGET (Mo)</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(sum(PGA_ALLOC_MEM)/1024/1024,2),'99G999G990D00')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(sum(PGA_MAX_MEM)/1024/1024,2),'99G999G990D00')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(to_number(value)/1024/1024,2),'99G999G990D00')||'</td>','</tr>'
 
−
from v$process,v$parameter
 
−
where name='pga_aggregate_target'
 
−
group by value;
 
−
 
 
−
-- *************************************** Detail UGA par utilisateur
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>D&eacute;tail UGA par utilisateur</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Nom</b></td><td width=15%><b>Valeur</b></td><td width=15%><b>Valeur (Mo)</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||username||'</td>', '<td bgcolor="LIGHTBLUE">'||name||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(value/(1024*1024),2),'99G999G990D00')||'</td>','</tr>'
 
−
from v$statname n, v$sesstat t, v$session s
 
−
where s.sid=t.sid
 
−
and n.statistic#=t.statistic#
 
−
and s.type='USER'
 
−
and s.username is not null
 
−
and n.name='session pga memory';
 
−
 
 
−
select '<tr><td width=15% colspan=2><b>Nombre d''utilisateurs</b></td>','<td bgcolor="BLUE" align=right><font color="WHITE">'||count(*)||'</td>','</tr>'
 
−
from v$statname n, v$sesstat t, v$session s
 
−
where s.sid=t.sid
 
−
and n.statistic#=t.statistic#
 
−
and s.type='USER'
 
−
and s.username is not null
 
−
and n.name='session pga memory';
 
−
 
 
−
select '<tr><td width=15% colspan=2><b>Total UGA (Mo)</b></td>','<td bgcolor="BLUE" align=right><font color="WHITE">'||to_char(round(sum(value)/(1024*1024),2),'99G999G990D00')||'</td>','</tr>'
 
−
from v$statname n, v$sesstat t
 
−
where n.statistic#=t.statistic#
 
−
and n.name='session uga memory';
 
−
 
 
−
select '<tr><td width=15% colspan=2><b>Total max UGA (Mo)</b></td>','<td bgcolor="BLUE" align=right><font color="WHITE">'||to_char(round(sum(value)/(1024*1024),2),'99G999G990D00')||'</td>','</tr>'
 
−
from v$statname n, v$sesstat t
 
−
where n.statistic#=t.statistic#
 
−
and n.name='session uga memory max';
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** ALERT.LOG
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE">ALERT LOG</font></b></div>
 
−
prompt <hr>
 
−
 
 
−
-- *************************************** Lecture de l'alert log
 
−
prompt <!-- ALERT.LOG -->
 
−
 
 
−
define alert_length="2000"
 
−
column nlsdate new_value _nlsdate noprint;
 
−
column bdump  new_value _bdump noprint;
 
−
column db      new_value _db    noprint;
 
−
 
 
−
select VALUE nlsdate from NLS_DATABASE_PARAMETERS where parameter = 'NLS_DATE_LANGUAGE';
 
−
select VALUE bdump from v$parameter
 
−
    where name ='background_dump_dest';
 
−
select instance_name db from v$instance;
 
−
 
 
−
-- *************************************** creation de la table finale "alert_log"
 
−
prompt <!-- Creation des tables, si necessaire -->
 
−
DECLARE
 
−
    table_exist number;
 
−
BEGIN
 
−
    select count(table_name) into table_exist from dba_tables
 
−
    where table_name='ALERT_LOG'
 
−
    and owner = 'SYSTEM';
 
−
    IF table_exist = 0 THEN
 
−
      EXECUTE IMMEDIATE 'create table alert_log (
 
−
                              alert_date date,
 
−
                              alert_text varchar2(&&alert_length)
 
−
                          ) TABLESPACE TOOLS';
 
−
      EXECUTE IMMEDIATE 'create index alert_log_idx on alert_log(alert_date)';
 
−
    ELSE
 
−
      EXECUTE IMMEDIATE 'truncate table alert_log';
 
−
    END IF;
 
−
END;
 
−
/
 
−
 
 
−
-- *****************************************  external table alert_log_disk
 
−
DECLARE
 
−
    dir_exist number;
 
−
BEGIN
 
−
    select count(DIRECTORY_NAME) into dir_exist from dba_directories
 
−
    where DIRECTORY_NAME='BDUMP'
 
−
    and owner in ('SYSTEM','SYS');
 
−
    IF dir_exist <> 0 THEN
 
−
      EXECUTE IMMEDIATE 'drop directory BDUMP';
 
−
    END IF;
 
−
    EXECUTE IMMEDIATE 'create directory BDUMP as ''&_bdump''';
 
−
END;
 
−
/
 
−
 
 
−
DECLARE
 
−
    table_exist number;
 
−
BEGIN
 
−
    select count(table_name) into table_exist from dba_tables
 
−
    where table_name='ALERT_LOG_DISK'
 
−
    and owner = 'SYSTEM';
 
−
    IF table_exist <> 0 THEN
 
−
      EXECUTE IMMEDIATE 'drop table alert_log_disk';
 
−
    END IF;
 
−
    EXECUTE IMMEDIATE 'create table alert_log_disk (text varchar2(&&alert_length))
 
−
                          organization external (
 
−
                            type oracle_loader
 
−
                            default directory BDUMP
 
−
                            access parameters (
 
−
                                records delimited by newline nologfile nobadfile
 
−
                                fields terminated by "&" ltrim
 
−
                                )
 
−
                            location(''alert_&_db..log'')
 
−
                            )
 
−
                          reject limit unlimited';
 
−
END;
 
−
/
 
−
 
 
−
-- ************************************ update table alert_log from alert_log_disk
 
−
prompt <!-- Remplissage des tables -->
 
−
set serveroutput on
 
−
declare
 
−
  isdate        number := 0;
 
−
  start_updating number := 0;
 
−
  rows_total    number := 0;
 
−
  rows_inserted  number := 0;
 
−
  alert_date    date;
 
−
  max_date      date;
 
−
  alert_text    alert_log_disk.text%type;
 
−
 
 
−
begin
 
−
-- find a starting date : last audit
 
−
  select max(to_date(date_aud)) into max_date from system.histaudit
 
−
                where to_date(date_aud) < trunc(sysdate);
 
−
  select count(*) into rows_total from alert_log_disk;
 
−
 
 
−
  if (max_date is null) then
 
−
    max_date := to_date('01-01-1980', 'dd-mm-yyyy');
 
−
  end if;
 
−
 
 
−
  for r in (
 
−
      select text from alert_log_disk
 
−
      where text not like '%offlining%'
 
−
        and text not like 'ARC_:%'
 
−
        and text not like '%LOG_ARCHIVE_DEST_1%'
 
−
        and text not like '%Thread 1 advanced to log sequence%'
 
−
        and text not like '%Current log#%seq#%mem#%'
 
−
        and text not like '%Undo Segment%lined%'
 
−
        and text not like '%alter tablespace%back%'
 
−
        and text not like '%Log actively being archived by another process%'
 
−
        and text not like '%Committing creation of archivelog%'
 
−
        and text not like '%Private_strands%'
 
−
        and trim(text) not like '(&_db)'
 
−
        and text not like '%alter database backup controlfile to trace%'
 
−
        and text not like '%Created Undo Segment%'
 
−
        and text not like '%started with pid%'
 
−
        and text not like '%ORA-12012%'
 
−
        and text not like '%ORA-06512%'
 
−
        and text not like '%ORA-02097%'
 
−
        and text not like '%ORA-00439%'
 
−
        and text not like '%coalesce%'
 
−
        and text not like '%Beginning log switch checkpoint up to RBA%'
 
−
        and text not like '%Completed checkpoint up to RBA%'
 
−
        and text not like '%specifies an obsolete parameter%'
 
−
        and text not like '%BEGIN BACKUP%'
 
−
        and text not like '%END BACKUP%'
 
−
        and text not like '%Starting%'
 
−
        and text not like '%autobackup%'
 
−
        and text not like '%handle%'
 
−
  )
 
−
  loop
 
−
 
 
−
    isdate    := 0;
 
−
    alert_text := null;
 
−
 
 
−
    select count(*) into isdate 
 
−
      from dual
 
−
      where substr(r.text, 21) in
 
−
      (to_char(sysdate, 'YYYY'), to_char(sysdate-365, 'YYYY'))
 
−
        and r.text not like '%cycle_run_year%';
 
−
-- De 21 à 24 ça doit être une année. Si oui c'est une date, sinon, c'est un libellé quelconque.
 
−
    if (isdate = 1) then
 
−
-- prendre à partir du mois (car.5) - forcer NLS en AMERICAN pour eviter les erreurs de conversion ? Tous les alert.log sont en AMERICAN ?
 
−
-- si besoin de prendre le NLS de la base, utiliser la variable &_nlsdate à la place d'AMERICAN
 
−
      select to_date(substr(r.text, 5),'Mon dd hh24:mi:ss rrrr','NLS_DATE_LANGUAGE = AMERICAN')
 
−
        into alert_date
 
−
        from dual;
 
−
 
 
−
      if (alert_date > max_date) then
 
−
        start_updating := 1;
 
−
      end if;
 
−
    else
 
−
      alert_text := r.text;
 
−
    end if;
 
−
 
 
−
    if (alert_text is not null) and (start_updating = 1) then
 
−
     
 
−
      insert into alert_log values (alert_date, substr(alert_text, 1, &&alert_length));
 
−
      rows_inserted := rows_inserted + 1;
 
−
      commit;
 
−
 
 
−
    end if;
 
−
 
 
−
  end loop;
 
−
 
 
−
--  sys.dbms_output.put_line('Inserting after date '||to_char(max_date, 'DD/MM/RR HH24:MI:SS'));
 
−
--  sys.dbms_output.put_line('Rows Inserted: '||rows_inserted);
 
−
 
 
−
  commit;
 
−
 
 
−
end;
 
−
/
 
−
 
 
−
-- ************************************ Affichage des logs
 
−
prompt <!-- Affichage des logs -->
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=2>
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print info
 
−
prompt " width="20" height="20" alt="Info..." title="Messages d'erreurs depuis le dernier audit. Si des messages sont affich&eacute;s, voir le d&eacute;tail dans le fichier alert<SID>.log, ou la table ALERT_LOG (r&eacute;sum&eacute;), ou la table externe ALERT_LOG_DISK (qui contient tout l'alert.log)."></td>
 
−
set define "&"
 
−
prompt <td align=center><font color="WHITE"><b>Erreurs alert_&_db..log (&_bdump)</b></font></td></tr></table></td></tr>
 
−
prompt <tr><td width=20%><b>Date</b></td><td width=80%><b>Texte</b></td></tr>
 
−
 
 
−
rem http://www.adp-gmbh.ch/ora/admin/scripts/read_alert_log.html
 
−
rem http://www.adp-gmbh.ch/ora/admin/read_alert/index.html
 
−
rem http://www.dba-oracle.com/t_writing_alert_log_message.htm
 
−
 
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||to_char(a.alert_date,'DD/MM/RR HH24:MI')||'</td>', '<td bgcolor="LIGHTBLUE">'||a.alert_text||'</td>','</tr>'
 
−
  from alert_log a,
 
−
        (select max(to_date(date_aud)) date_aud from system.histaudit
 
−
                where to_date(date_aud) < trunc(sysdate)) d
 
−
where (alert_text like '%ORA-%'
 
−
  or alert_text like '%Checkpoint not complete%')
 
−
  and a.alert_date > d.date_aud
 
−
order by to_date(a.alert_date);
 
−
 
 
−
DECLARE cnt_obj number := 0;
 
−
BEGIN
 
−
    select count(a.alert_date) into cnt_obj
 
−
    from alert_log a,
 
−
        (select max(to_date(date_aud)) date_aud from system.histaudit
 
−
                where to_date(date_aud) < trunc(sysdate)) d
 
−
    where (alert_text like '%ORA-%'
 
−
    or alert_text like '%Checkpoint not complete%')
 
−
    and a.alert_date > d.date_aud;
 
−
 
 
−
    if cnt_obj=0  then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
end;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- ****************** SECTION SCHEMAS - INFORMATIONS GLOBALES *************************
 
−
prompt <hr>
 
−
prompt <div align=center><b><font color="WHITE">SECTION SCHEMAS</font></b></div>
 
−
prompt <hr>
 
−
 
 
−
-- *************************************** Objets invalides
 
−
prompt <!-- Objets invalides -->
 
−
set define off
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=4><font color="WHITE"><b>Objets invalides</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Propri&eacute;taire</b></td><td width=15%><b>Objet</b></td><td width=15%><b>Type</b></td><td width=15%><b>Statut</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||OWNER||'</td>','<td bgcolor="LIGHTBLUE">'||object_name||'</td>','<td bgcolor="LIGHTBLUE">'||object_type||'</td>','<td bgcolor="LIGHTBLUE">'||status||'</td>','</tr>' from dba_objects where status <> 'VALID' and object_name not like 'BIN$%';
 
−
 
 
−
DECLARE cnt_obj number := 0;
 
−
BEGIN
 
−
    select count(object_name) into cnt_obj from dba_objects
 
−
    where status <> 'VALID'
 
−
    and rownum = 1;
 
−
    if cnt_obj=0 then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
end;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Indexes UNUSABLE
 
−
prompt <!-- Indexes unusable -->
 
−
set define off
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Indexes UNUSABLE</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Propri&eacute;taire</b></td><td width=15%><b>Index</b></td><td width=15%><b>Statut</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||OWNER||'</td>','<td bgcolor="LIGHTBLUE">'||index_name||'</td>','<td bgcolor="LIGHTBLUE">'||status||'</td>','</tr>' from dba_indexes where status not in ('VALID','N/A');
 
−
 
 
−
DECLARE cnt_obj number := 0;
 
−
BEGIN
 
−
    select count(index_name) into cnt_obj from dba_indexes
 
−
    where status not in ('VALID','N/A')
 
−
    and rownum = 1;
 
−
    if cnt_obj=0 then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
end;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Liste des segments de plus de 100M
 
−
prompt <!-- Segments de plus de 100M -->
 
−
 
 
−
set define off
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=4><font color="WHITE"><b>Liste des segments de plus de 100Mo</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Propri&eacute;taire</b></td><td width=15%><b>Segment</b></td><td width=15%><b>Type</b></td><td width=15%><b>Taille</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||owner||'</td>', '<td bgcolor="LIGHTBLUE">'||segment_name||'</td>', '<td bgcolor="LIGHTBLUE">'||segment_type||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(bytes/(1024*1024),0),'99G999G990')||' Mo</td>','</tr>'
 
−
from dba_segments
 
−
where (segment_type like 'TABLE%' OR segment_type like 'INDEX%' OR segment_type like 'LOB%')
 
−
and bytes/1024/1024 >100
 
−
and owner not in &sysusers and owner not in &exusers
 
−
order by bytes desc;
 
−
 
 
−
DECLARE cnt_obj number := 0;
 
−
BEGIN
 
−
    select count(segment_name) into cnt_obj from dba_segments
 
−
    where (segment_type like 'TABLE%' OR segment_type like 'INDEX%' OR segment_type like 'LOB%')
 
−
    and bytes/1024/1024 >100
 
−
    and owner not in &sysusers and owner not in &exusers
 
−
    and rownum = 1;
 
−
 
 
−
    if cnt_obj=0 then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
end;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Segments fragmentes
 
−
prompt <!-- Segments fragmentes -->
 
−
set define off
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=5><font color="WHITE"><b>Liste des segments fragment&eacute;s (plus de 64 extents)</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Propri&eacute;taire</b></td><td width=15%><b>Segment</b></td><td width=15%><b>Type</b></td><td width=15%><b>Li&eacute; &agrave;</b></td><td width=15%><b>Nombre d'extents</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||e.owner||'</td>', '<td bgcolor="LIGHTBLUE">'||e.segment_name||'</td>', '<td bgcolor="LIGHTBLUE">'||e.segment_type||'</td>','<td bgcolor="LIGHTBLUE">'||decode (e.segment_type, 'INDEX', i.table_name,'--')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||max(e.extent_id)||'</td>','</tr>' from dba_extents e,dba_indexes i
 
−
where e.segment_type <> 'TEMPORARY'
 
−
and e.segment_name=i.index_name(+)
 
−
and e.owner not in &sysusers and e.owner not in &exusers
 
−
--and rownum <= 10
 
−
group by e.segment_name,e.segment_type,i.table_name,e.owner
 
−
having max(e.extent_id) > 64
 
−
order by max(e.extent_id) desc;
 
−
 
 
−
-- ***** Parfois cnt_obj contient 0, parfois NULL. A debugguer. Affichage de "N/A" si =0, "Neant" si EXCEPTION (=NULL) *********
 
−
DECLARE cnt_obj number := 0;
 
−
BEGIN
 
−
    select count(segment_name) into cnt_obj from dba_extents
 
−
    where segment_type <> 'TEMPORARY'
 
−
    and owner not in &sysusers and owner not in &exusers
 
−
    and extent_id > 64
 
−
    and rownum = 1;
 
−
    if cnt_obj=0 or cnt_obj is null then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
EXCEPTION
 
−
    when NO_DATA_FOUND then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
END;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** tables/indexes atteignant 90% de la limite MAXEXTENTS
 
−
-- MIS EN REMARQUE - PLUS NECESSAIRE EN 10 SI LE TABLESPACE EST EN MANAGEMENT LOCAL
 
−
-- set define offStatistiques switchs REDO LOGS
 
−
-- prompt <table border=1 width=100% bgcolor="WHITE">
 
−
-- prompt <tr><td bgcolor="#3399CC" align=center colspan=6><font color="WHITE"><b>Tables/indexes atteignant 90% de la limite MAXEXTENTS</b></font></td></tr>
 
−
-- prompt <tr><td width=15%><b>Propri&eacute;taire</b></td><td width=15%><b>Segment</b></td><td width=15%><b>Type</b></td><td width=15%><b>Table</b></td><td width=15%><b>Nombre d'extents courant</b></td><td width=15%><b>Nombre maximum d'extents</b></td></tr>
 
−
-- set define "&"
 
−
-- select '<tr>','<td bgcolor="LIGHTBLUE">'||e.owner||'</td>', '<td bgcolor="LIGHTBLUE">'||segment_name||'</td>', '<td bgcolor="LIGHTBLUE">'||segment_type||'</td>','<td bgcolor="LIGHTBLUE">'||max(extent_id)||'</td>', '<td bgcolor="LIGHTBLUE">'||t.max_extents||'</td>','</tr>' from dba_extents e, dba_tables t
 
−
-- where segment_type = 'TABLE'
 
−
-- and e.owner not in &sysusers and e.owner not in &exusers
 
−
-- and e.segment_name=t.table_name
 
−
-- and e.owner=t.owner
 
−
-- group by segment_name, segment_type,e.owner, t.max_extents
 
−
-- having max(extent_id) > t.max_extents*0.9
 
−
-- order by segment_name;
 
−
-- select '<tr>','<td bgcolor="LIGHTBLUE">'||e.owner||'</td>', '<td bgcolor="LIGHTBLUE">'||segment_name||'</td>', '<td bgcolor="LIGHTBLUE">'||segment_type||'</td>','<td bgcolor="LIGHTBLUE">'||max(extent_id)||'</td>', '<td bgcolor="LIGHTBLUE">'||i.max_extents||'</td>','</tr>' from dba_extents e, dba_indexes i
 
−
-- where segment_type = 'INDEX'
 
−
-- and e.owner not in &sysusers and e.owner not in &exusers
 
−
-- and e.segment_name=i.index_name
 
−
-- and e.owner=i.owner
 
−
-- group by segment_name, segment_type,e.owner, i.max_extents
 
−
-- having max(extent_id) > i.max_extents*0.9
 
−
-- order by segment_name, segment_type DESC;
 
−
 
 
−
-- DECLARE
 
−
--    cnt_obj_t number := 0;
 
−
--    cnt_obj_i number := 0;
 
−
-- BEGIN
 
−
--    select count(extent_id) into cnt_obj_t from dba_extents e,dba_tables t
 
−
--    where segment_type = 'TABLE'
 
−
--    and extent_id > t.max_extents*0.9
 
−
--    and e.owner not in &sysusers and e.owner not in &exusers;
 
−
--
 
−
--    select count(extent_id) into cnt_obj_i from dba_extents e,dba_indexes i
 
−
--    where segment_type = 'INDEX'
 
−
--    and extent_id > i.max_extents*0.9
 
−
--    and e.owner not in &sysusers and e.owner not in &exusers;
 
−
--    if cnt_obj_t=0 and cnt_obj_i=0 then
 
−
--      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
--    end if;
 
−
-- end;
 
−
-- /
 
−
 
 
−
-- prompt </table><br>
 
−
 
 
−
-- utilisateurs et tablespaces par defaut
 
−
-- **************************************
 
−
set define off
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>utilisateurs et tablespaces par d&eacute;faut</b></font></td></tr>
 
−
prompt <tr><td><b>Utilisateurs</b></td><td><b>Tablespace par d&eacute;faut</b></td><td><b>Tablespace temporaire</b></td></tr>
 
−
prompt
 
−
set define "&"
 
−
 
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||username||'</td>','<td bgcolor="LIGHTBLUE">'||default_tablespace||'</td>', '<td bgcolor="LIGHTBLUE">'||temporary_tablespace||'</td>','</tr>'
 
−
from dba_users
 
−
order by username;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
 
 
−
-- Liste des utilisateurs systemes non listes dans les variables sysusers et exusers
 
−
-- ********************************
 
−
set define off
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
 
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=2>
 
−
prompt <table border=0 width=100%><tr><td width=10%>&nbsp;&nbsp;<img src="data:image/gif;base64,
 
−
print info
 
−
prompt " width="20" height="20" alt="Info..." title="Les variables sysusers et exusers listent les utilisateurs syst&egrave;mes Oracle, afin de les &eacute;liminer des requ&ecirc;tes qui ne doivent prendre en compte que les sch&eacute;mas applicatifs. Ici sont list&eacute;s pour information les utilisateurs qui ne sont pas inclus dans ces variables, afin de rep&eacute;rer ceux qui devraient y &ecirc;tre ajout&eacute;s."></td>
 
−
prompt <td align=center><font color="WHITE"><b>Information : Liste des utilisateurs non syst&egrave;mes</b></font></td></tr></table></td></tr>
 
−
set define "&"
 
−
prompt <tr><td><b>Utilisateur</b></td></tr>
 
−
prompt
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||username||'</td>','</tr>'
 
−
from dba_users
 
−
where username not in &sysusers and username not in &exusers;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Utilisateurs ayant des objets dans le tablespace SYSTEM
 
−
prompt <!-- Segments utilisateurs dans le tablespace SYSTEM -->
 
−
-- Tables
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Utilisateurs ayant des objets dans le tablespace SYSTEM</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Propri&eacute;taire</b></td><td width=15%><b>Type</b></td><td width=15%><b>Segment</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||owner||'</td>', '<td bgcolor="LIGHTBLUE">Tables</td>','<td bgcolor="LIGHTBLUE">'||count(*)||'</td>','</tr>' TOTAL from dba_tables
 
−
where tablespace_name = 'SYSTEM'
 
−
and owner not in &sysusers and owner not in &exusers
 
−
group by owner;
 
−
-- Indexes
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||owner||'</td>', '<td bgcolor="LIGHTBLUE">Indexes</td>','<td bgcolor="LIGHTBLUE">'||count(*)||'</td>','</tr>' TOTAL from dba_indexes
 
−
where tablespace_name = 'SYSTEM'
 
−
and owner not in &sysusers and owner not in &exusers
 
−
group by owner;
 
−
 
 
−
DECLARE
 
−
    cnt_obj_t number := 0;
 
−
    cnt_obj_i number := 0;
 
−
BEGIN
 
−
    select count(*) into cnt_obj_t from dba_tables
 
−
where tablespace_name = 'SYSTEM'
 
−
and owner not in &sysusers and owner not in &exusers
 
−
        and rownum = 1;
 
−
    select count(*) into cnt_obj_i from dba_indexes
 
−
where tablespace_name = 'SYSTEM'
 
−
and owner not in &sysusers and owner not in &exusers
 
−
        and rownum = 1;
 
−
    if cnt_obj_t=0 and cnt_obj_i=0 then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
end;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- *************************************** Tables et index dans le meme tablespace
 
−
prompt <!-- Tables et index dans le meme tablespace -->
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
set define off
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=4><font color="WHITE"><b>Tables et indexes dans le m&ecirc;me tablespace</b></font></td></tr>
 
−
prompt <tr><td width=15%><b>Propri&eacute;taire</b></td><td width=15%><b>Tablespace</b></td><td width=15%><b>Table</b></td><td width=15%><b>Index</b></td></tr>
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||a.owner||'</td>', '<td bgcolor="LIGHTBLUE">'||a.tablespace_name||'</td>', '<td bgcolor="LIGHTBLUE">'||a.table_name||'</td>', '<td bgcolor="LIGHTBLUE">'||b.index_name||'</td>','</tr>'
 
−
from dba_tables a, dba_indexes b
 
−
where a.tablespace_name=b.tablespace_name
 
−
and a.table_name=b.table_name
 
−
and a.owner=b.owner
 
−
and a.owner not in &sysusers and a.owner not in &exusers
 
−
order by 1,2;
 
−
 
 
−
DECLARE cnt_obj number := 0;
 
−
BEGIN
 
−
    select count(b.index_name) into cnt_obj from dba_tables a, dba_indexes b
 
−
      where a.tablespace_name=b.tablespace_name
 
−
      and a.table_name=b.table_name
 
−
      and a.owner=b.owner
 
−
      and a.owner not in &sysusers and a.owner not in &exusers
 
−
      and rownum = 1;
 
−
    if cnt_obj=0  then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
end;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- Roles par utilisateurs non systemes
 
−
-- ***********************************
 
−
set define off
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>R&ocirc;les par utilisateur (non syst&egrave;mes)</b></font></td></tr>
 
−
prompt <tr><td><b>Utilisateurs</b></td><td><b>R&ocirc;les</b></td></tr>
 
−
prompt
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||username||'</td>', decode(granted_role,NULL,'<td bgcolor="LIGHTBLUE">-</td>','<td bgcolor="LIGHTBLUE">'||granted_role||'</td>'),'</tr>'
 
−
from dba_users, dba_role_privs
 
−
where username not in &sysusers and username not in &exusers
 
−
and username=grantee(+)
 
−
order by 1,2;
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- Liste des schemas vides (aucun objets)
 
−
-- *************************************
 
−
set define off
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center><font color="WHITE"><b>Liste des sch&eacute;mas vides (aucun objet)</b></font></td></tr>
 
−
prompt <tr><td><b>Sc&eacute;mas vides</b></td></tr>
 
−
prompt
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||username||'</td>','</tr>' from dba_users
 
−
where username not in (select owner from dba_segments)
 
−
and username not in &sysusers and username not in &exusers;
 
−
 
 
−
DECLARE cnt_sch number;
 
−
BEGIN
 
−
    select count(username) into cnt_sch from dba_users
 
−
    where username not in (select owner from dba_segments)
 
−
and username not in &sysusers and username not in &exusers;
 
−
    if cnt_sch=0 then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
end;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- Nombres d'objets par schemas (hors schemas systemes)
 
−
-- ***************************************************
 
−
set define off
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=5><font color="WHITE"><b>Nombre d'objets par sch&eacute;ma (non syst&egrave;mes)</b></font></td></tr>
 
−
prompt <tr><td><b>Utilisateur</b></td><td><b>Total</b></td><td><b>Tables</b></td><td><b>Indexes</b></td><td><b>Autres</b></td></tr>
 
−
prompt
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||t.owner||'</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(t.total,NULL,0,t.total),'99G999G990')||'</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(a.tables,NULL,0,a.tables),'99G999G990')||'</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(i.indexes,NULL,0,i.indexes),'99G999G990')||'</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(o.autres,NULL,0,o.autres),'99G999G990')||'</td>','</tr>'
 
−
from  (select owner, count(*) total
 
−
      from dba_segments
 
−
      where owner not in &sysusers and owner not in &exusers
 
−
      group by owner) t,
 
−
      (select owner, count(*) tables
 
−
      from dba_segments
 
−
      where owner not in &sysusers and owner not in &exusers
 
−
      and segment_type='TABLE'
 
−
      group by owner) a,
 
−
      (select owner, count(*) indexes
 
−
      from dba_segments
 
−
      where owner not in &sysusers and owner not in &exusers
 
−
      and segment_type='INDEX'
 
−
      group by owner) i,
 
−
      (select owner, count(*) autres
 
−
      from dba_segments
 
−
      where owner not in &sysusers and owner not in &exusers
 
−
      and segment_type not in ('TABLE','INDEX')
 
−
      group by owner) o
 
−
where t.owner=a.owner(+) and t.owner=i.owner(+) and t.owner=o.owner(+);
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- Taille utilisee par les schemas (hors schemas systemes)
 
−
-- *******************************************************
 
−
set define off
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=5><font color="WHITE"><b>Taille utilis&eacute;e par les sch&eacute;mas (non syst&egrave;mes)</b></font></td></tr>
 
−
prompt <tr><td><b>Utilisateur</b></td><td><b>Total (Mo)</b></td><td><b>Tables (Mo)</b></td><td><b>Indexes (Mo)</b></td><td><b>Autres (Mo)</b></td></tr>
 
−
prompt
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||t.owner||'</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(t.total,NULL,0,t.total),'99G999G990D00')||'</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(a.tables,NULL,0,a.tables),'99G999G990D00')||'</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(i.indexes,NULL,0,i.indexes),'99G999G990D00')||'</td>',
 
−
'<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(o.autres,NULL,0,o.autres),'99G999G990D00')||'</td>','</tr>'
 
−
from  (select owner, round(sum(bytes)/(1024*1024),2) total
 
−
      from dba_segments
 
−
      where owner not in &sysusers and owner not in &exusers
 
−
      group by owner) t,
 
−
      (select owner, round(sum(bytes)/(1024*1024),2) tables
 
−
      from dba_segments
 
−
      where owner not in &sysusers and owner not in &exusers
 
−
      and segment_type='TABLE'
 
−
      group by owner) a,
 
−
      (select owner, round(sum(bytes)/(1024*1024),2) indexes
 
−
      from dba_segments
 
−
      where owner not in &sysusers and owner not in &exusers
 
−
      and segment_type='INDEX'
 
−
      group by owner) i,
 
−
      (select owner, round(sum(bytes)/(1024*1024),2) autres
 
−
      from dba_segments
 
−
      where owner not in &sysusers and owner not in &exusers
 
−
      and segment_type not in ('TABLE','INDEX')
 
−
      group by owner) o
 
−
where t.owner=a.owner(+) and t.owner=i.owner(+) and t.owner=o.owner(+);
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- Liste des liens de bases de donn&eacute;es
 
−
-- ***********************************
 
−
set define off
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=5><font color="WHITE"><b>Liste des liens de bases de donn&eacute;es</b></font></td></tr>
 
−
prompt <tr><td><b>Utilisateur</b></td><td><b>DB Link</b></td><td><b>Utilisateur distant</b></td><td><b>Serveur</b></td></tr>
 
−
prompt
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||owner||'</td>','<td bgcolor="LIGHTBLUE">'||DB_LINK||'</td>','<td bgcolor="LIGHTBLUE">'||USERNAME||'</td>',
 
−
        '<td bgcolor="LIGHTBLUE">'||HOST||'</td>','</tr>'
 
−
from dba_db_links
 
−
order by OWNER,DB_LINK;
 
−
 
 
−
DECLARE cnt_dbl number;
 
−
BEGIN
 
−
    select count(owner) into cnt_dbl from dba_db_links;
 
−
    if cnt_dbl=0 then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
end;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
-- Liste des synonymes non systemes
 
−
-- ********************************
 
−
set define off
 
−
prompt <table border=1 width=100% bgcolor="WHITE">
 
−
prompt <tr><td bgcolor="#3399CC" align=center colspan=5><font color="WHITE"><b>Liste des synonymes (non syst&egrave;mes)</b></font></td></tr>
 
−
prompt <tr><td><b>Utilisateur</b></td><td><b>Synonyme</b></td><td><b>Propri&eacute;taire</b></td><td><b>Objet cible</b></td></tr>
 
−
prompt
 
−
set define "&"
 
−
select '<tr>','<td bgcolor="LIGHTBLUE">'||owner||'</td>', '<td bgcolor="LIGHTBLUE">'||synonym_name||'</td>', '<td bgcolor="LIGHTBLUE">'||table_owner||'</td>',
 
−
        '<td bgcolor="LIGHTBLUE">'||table_name||'</td>','</tr>'
 
−
from dba_synonyms
 
−
where table_owner not in &sysusers and table_owner not in &exusers;
 
−
 
 
−
DECLARE cnt_syn number;
 
−
BEGIN
 
−
    select count(owner) into cnt_syn from dba_synonyms
 
−
where table_owner not in &sysusers and table_owner not in &exusers;
 
−
    if cnt_syn=0 then
 
−
      dbms_output.put_line('<tr><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td><td bgcolor="#33FF33"><i>N/A</i></td></tr>');
 
−
    end if;
 
−
end;
 
−
/
 
−
 
 
−
prompt </table><br>
 
−
 
 
−
prompt </body>
 
−
prompt </html>
 
−
 
 
−
spool off
 
−
exit
 
−
</nowiki>
 

Version actuelle datée du 20 septembre 2018 à 08:56

Un script d'audit de bases de données Oracle qui produit un rapport HTML.

Peaufiné amoureusement depuis des années, certainement encore perfectible, à utiliser sans modération.

Code partagé "as is", sans garantie, à utiliser à vos risques et périls (mais bon, ce ne sont que quelques select, Seigneur, et quelques inserts dans une table d'historique d'audit dédiée).

Voir aussi le script d'audit MySQL

09/2018 : transféré sur Github https://github.com/fsoyer/auditOracleHTML

Récupérée de « https://wiki.kogite.fr/index.php?title=Script_d%27audit_de_bases_de_données_ORACLE&oldid=6635 »