Outils personnels

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

De wikiGite

Ligne 36 : Ligne 36 :
 
--        corr. bug "erreurs sur objet" limit champ dba_errors."text" à 240 car. pour concatenations.
 
--        corr. bug "erreurs sur objet" limit champ dba_errors."text" à 240 car. pour concatenations.
 
-- 07/2015 v3.0.1 affichage tablespace de la table HISTAUDIT (SYSTEM ou TOOLS)
 
-- 07/2015 v3.0.1 affichage tablespace de la table HISTAUDIT (SYSTEM ou TOOLS)
--   corr. bug div/0 si executions=0 dans v$sqlarea
+
--       corr. bug div/0 si executions=0 dans v$sqlarea
 
-- 08/2015 v3.0.2 affichage des valeurs de divers paramètres d'initialisation.
 
-- 08/2015 v3.0.2 affichage des valeurs de divers paramètres d'initialisation.
 
--   corr. bug total segments (TABLE% et INDEX%). Incohérence entre les sommes conservées dans Histaudit et les tailles réelles.
 
--   corr. bug total segments (TABLE% et INDEX%). Incohérence entre les sommes conservées dans Histaudit et les tailles réelles.
 
-- 09/2015 v3.0.3 ajout version et parametres d'init dans histaudit. Test si version ou paramètre a changé depuis dernier audit
 
-- 09/2015 v3.0.3 ajout version et parametres d'init dans histaudit. Test si version ou paramètre a changé depuis dernier audit
 
--        v3.0.4 corr bug affichage version; ajout liste des patchs appliqués
 
--        v3.0.4 corr bug affichage version; ajout liste des patchs appliqués
-- 09/2015 v3.1 ajout informations hôte. Le script est stable avant de s'occuper des TODOS "moins" importants => passage en 3.1.
+
-- 11/2015 v3.1 Nettoyage du code, suppression de lignes obsolètes, suppressions de concatenations par "||",
 +
--        correction bug "ORA-01489" dans l'affichage des objets en erreur.
 
--
 
--
 
-- ******  UTILISATION  ******
 
-- ******  UTILISATION  ******
Ligne 80 : Ligne 81 :
 
--
 
--
 
-- ******  BUGS CONNUS  ******
 
-- ******  BUGS CONNUS  ******
-- Volumétrie de tablespaces : si les fichiers mixent autoextend et no autoextend, affichage en doublon. A corriger.
+
-- Volumétrie de tablespaces : si les fichiers mixent autoextend et no autoextend, affichage en doublon.
 
--      A valider : peut-être idem si les fichiers mixent standard et bigfile, à retester.
 
--      A valider : peut-être idem si les fichiers mixent standard et bigfile, à retester.
-- A valider : le calcul de l'alerte avec Couleurlimite n'est peut-être pas bon non plus dans ces cas.
+
--     A valider : le calcul de l'alerte avec Couleurlimite n'est peut-être pas bon non plus dans ces cas.
 
-- Incompatibilité de certaines requêtes avec des versions < 10. Le script va quand même au bout.
 
-- Incompatibilité de certaines requêtes avec des versions < 10. Le script va quand même au bout.
 
--  
 
--  
Ligne 88 : Ligne 89 :
 
-- AMELIORATIONS GLOBALES (AU SCRIPT)
 
-- AMELIORATIONS GLOBALES (AU SCRIPT)
 
-------------------------------------
 
-------------------------------------
-- TODO : SUPPRIMER les "||" de concatenation par des virgules. Mais ATTENTION aux "UNION" qui réclament le même nombre de colonnes, et aux "decode" qui interprêtent les virgules !
+
-- TODO : remplacer les "N/A" par un bgcolor=LIGHTGREY
 
-- TODO : UTILISER DES VARIABLES EN DEBUT DE SCRIPT POUR LES SEUILS D'ALERTES ?
 
-- TODO : UTILISER DES VARIABLES EN DEBUT DE SCRIPT POUR LES SEUILS D'ALERTES ?
 
-- TODO : Supprimer l'affichage dans la console pendant l'exécution du script, n'afficher que des infos de type "audit de la base XXX" avec dbms_output.
 
-- TODO : Supprimer l'affichage dans la console pendant l'exécution du script, n'afficher que des infos de type "audit de la base XXX" avec dbms_output.
Ligne 99 : Ligne 100 :
 
-- Histaudit :
 
-- Histaudit :
 
-- TODO : nettoyer historique > 1 an
 
-- TODO : nettoyer historique > 1 an
 +
-- Host :
 +
-- BUG : la table v$osstat n'a pas toujours la stat "NUM_CPU_CORES". ligne vide renvoyée. A voir !
 
-- Versions :
 
-- Versions :
 
-- TODO : Ajouter la liste des patches (select * from sys.registry$history;)
 
-- TODO : Ajouter la liste des patches (select * from sys.registry$history;)
 +
-- Liste des tablespaces et fichiers :
 +
-- Mettre en ORANGE les nouveaux tbs et fichiers, par rapport à la liste de HISTAUDIT précédente
 
-- Volumetrie :
 
-- Volumetrie :
 
-- TODO : Afficher un graphe evolution volumétrie (attention aux creations de fichiers entre deux audits)
 
-- TODO : Afficher un graphe evolution volumétrie (attention aux creations de fichiers entre deux audits)
Ligne 114 : Ligne 119 :
 
-- Requetes les plus gourmandes
 
-- Requetes les plus gourmandes
 
-- TODO : Passer en orange les requêtes > 1s
 
-- TODO : Passer en orange les requêtes > 1s
 +
-- Logs
 +
-- Si le nombre de msg à afficher de l'alert.log est trop grand depuis le dernier audit, erreur :
 +
--  declare * ERREUR à la ligne 1 : ORA-01653: impossible d'étendre la table SYSTEM.ALERT_LOG de 128 dans le tablespace TOOLS ORA-06512: à ligne 77
 +
--    TODO : intercepter l'erreur pour n'afficher que les X dernières lignes ?
 +
-- TODO : affichage : grouper les messages si identiques sur plusieurs lignes consécutives et afficher la somme pour limiter le nombre de ligne ?
 
-- Schemas :
 
-- Schemas :
 
-- TODO : Section SCHEMAS = Liste des grants par user et par role non systeme
 
-- TODO : Section SCHEMAS = Liste des grants par user et par role non systeme
 
-- TODO : Afficher N/A si 1er audit ou aucuns objets utilisateurs
 
-- TODO : Afficher N/A si 1er audit ou aucuns objets utilisateurs
 
-- 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 : 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 : 'affichage des logs' = grouper les messages si identiques et afficher une somme pour limiter le nombre de ligne ?
 
 
-- TODO : "mises à jour automatiques des stats" : en 11i afficher état "auto optimizer stats collection" mais table n'existe pas en 10g (voir commentaire dans la section)
 
-- TODO : "mises à jour automatiques des stats" : en 11i afficher état "auto optimizer stats collection" mais table n'existe pas en 10g (voir commentaire dans la section)
 
-- Segments :
 
-- Segments :
Ligne 244 : Ligne 253 :
 
prompt <table border=1 width=100% bgcolor="WHITE">
 
prompt <table border=1 width=100% bgcolor="WHITE">
 
prompt <tr><td bgcolor="#3399CC" align=center>  
 
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)
 
prompt <font color=WHITE size=+2><b>Audit &dbname (&hstname)
select ' du '||to_char(to_date(sysdate),'DD-MON-YYYY',N'NLS_DATE_LANGUAGE = AMERICAN')||'</b>' as DATE_AUDIT from dual;
+
select ' du ',to_char(to_date(sysdate),'DD-MON-YYYY',N'NLS_DATE_LANGUAGE = AMERICAN'),'</b>' as DATE_AUDIT from dual;
 
prompt </font></td></tr></table>
 
prompt </font></td></tr></table>
 
prompt <br>
 
prompt <br>
Ligne 342 : Ligne 350 :
 
set define off
 
set define off
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=5><font color="WHITE"><b>H&ocirc;te</b></font></td></tr>
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=5><font color="WHITE"><b>H&ocirc;te</b></font></td></tr>
prompt <tr><td bgcolor="LIGHTBLUE"><b>Nom</b></td><td bgcolor="LIGHTBLUE"><b>OS</b></td><td bgcolor="LIGHTBLUE"><b>CPUs</b></td><td bgcolor="LIGHTBLUE"><b>Cores/CPU</b></td><td bgcolor="LIGHTBLUE"><b>RAM</b></td>
+
prompt <tr><td bgcolor="LIGHTBLUE"><b>Host</b></td><td bgcolor="LIGHTBLUE"><b>OS</b></td><td bgcolor="LIGHTBLUE"><b>CPUs</b></td><td bgcolor="LIGHTBLUE"><b>Cores/CPU</b></td><td bgcolor="LIGHTBLUE"><b>RAM</b></td>
 
set define "&"
 
set define "&"
 
prompt <tr><td bgcolor="LIGHTBLUE" width=20%>&hstname</td>
 
prompt <tr><td bgcolor="LIGHTBLUE" width=20%>&hstname</td>
Ligne 370 : Ligne 378 :
 
  and to_date(date_aud) = (select max(to_date(date_aud)) from histaudit where type_obj = 'VERS');
 
  and to_date(date_aud) = (select max(to_date(date_aud)) from histaudit where type_obj = 'VERS');
  
select banner||'<br>' from v$version;
+
select banner,'<br>' from v$version;
 
set define "&"
 
set define "&"
 
prompt </td></tr>
 
prompt </td></tr>
Ligne 393 : Ligne 401 :
 
   select count(ACTION_TIME) into cnt_patch from sys.registry$history;
 
   select count(ACTION_TIME) into cnt_patch from sys.registry$history;
 
   if cnt_patch=0 then
 
   if cnt_patch=0 then
       dbms_output.put_line('<tr><td bgcolor="LIGHTBLUE">&nbsp;</td><td bgcolor="LIGHTBLUE">&nbsp;</td><td bgcolor="LIGHTBLUE">&nbsp;</td><td bgcolor="LIGHTBLUE">&nbsp;</td><td bgcolor="LIGHTBLUE">&nbsp;</td></tr>');
+
       dbms_output.put_line('<tr><td bgcolor="LIGHTGREY">&nbsp;</td><td bgcolor="LIGHTGREY">&nbsp;</td><td bgcolor="LIGHTGREY">&nbsp;</td><td bgcolor="LIGHTGREY">&nbsp;</td><td bgcolor="LIGHTGREY">&nbsp;</td></tr>');
 
   end if;
 
   end if;
 
end;
 
end;
Ligne 413 : Ligne 421 :
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Param&egrave;tres NLS Instance</b></font></td></tr>
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Param&egrave;tres NLS Instance</b></font></td></tr>
 
prompt <tr><td width=50%><b>Param&egrave;tre</b></td><td width=50%><b>Valeur</b></td>
 
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;
+
select '<tr><td bgcolor="LIGHTBLUE">',parameter,'</td>','<td bgcolor="LIGHTBLUE">',value,'</td>','</tr>' from v$nls_parameters;
  
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Param&egrave;tres NLS database</b></font></td></tr>
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Param&egrave;tres NLS database</b></font></td></tr>
 
prompt <tr><td width=50%><b>Param&egrave;tre</b></td><td width=50%><b>Valeur</b></td>
 
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 nls_database_parameters;
+
select '<tr><td bgcolor="LIGHTBLUE">',parameter,'</td>','<td bgcolor="LIGHTBLUE">',value,'</td>','</tr>' from nls_database_parameters;
  
 
-- *************************************** AUTRES PARAMETRES D'INIT
 
-- *************************************** AUTRES PARAMETRES D'INIT
Ligne 425 : Ligne 433 :
 
prompt <td bgcolor="#3399CC" align=center><font color="WHITE"><b>Autres param&egrave;tres d'initialisation (instance)</b></font></td></tr></table></td></tr>
 
prompt <td bgcolor="#3399CC" align=center><font color="WHITE"><b>Autres param&egrave;tres d'initialisation (instance)</b></font></td></tr></table></td></tr>
 
prompt <tr><td width=50%><b>Param&egrave;tre</b></td><td width=50%><b>Valeur</b></td>
 
prompt <tr><td width=50%><b>Param&egrave;tre</b></td><td width=50%><b>Valeur</b></td>
select '<tr><td bgcolor="LIGHTBLUE">'||name||'</td>','<td bgcolor="LIGHTBLUE">'||value||'</td>','</tr>' from v$parameter where name in ('open_cursors','processes','compatible','remote_login_passwordfile','session','utl_file_dir','undo_retention') order by 1;
+
select '<tr><td bgcolor="LIGHTBLUE">',name,'</td>','<td bgcolor="LIGHTBLUE">',value,'</td>','</tr>' from v$parameter where name in ('open_cursors','processes','compatible','remote_login_passwordfile','session','utl_file_dir','undo_retention') order by 1;
  
 
-- *************************************** Modifies lors du dernier audit ?
 
-- *************************************** Modifies lors du dernier audit ?
Ligne 466 : Ligne 474 :
 
prompt <table border=1 width=100% bgcolor="WHITE">
 
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>
 
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_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%>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;
+
select '<tr><td bgcolor="WHITE" width=20%>INSTANCE_NAME</td><td bgcolor="LIGHTBLUE">',instance_name,'</td>','</tr>' from v$instance;
  
 
prompt </table>
 
prompt </table>
Ligne 494 : Ligne 502 :
 
-- Archive log mode
 
-- Archive log mode
 
prompt <tr><td width=20%><b>Archive log mode</b></td>
 
prompt <tr><td width=20%><b>Archive log mode</b></td>
select decode(log_mode,'ARCHIVELOG','<td bgcolor="#33FF33">'||log_mode||'<br>','<td bgcolor="#FF9900">'||log_mode||'<br>') from v$database;
+
select decode(log_mode,'ARCHIVELOG','<td bgcolor="#33FF33">',log_mode,'<br>','<td bgcolor="#FF9900">',log_mode,'<br>') from v$database;
 
prompt </td></tr>
 
prompt </td></tr>
 
set define off
 
set define off
 
prompt <tr><td width=20%><b>Archive log destination</b></td><td bgcolor="LIGHTBLUE">
 
prompt <tr><td width=20%><b>Archive log destination</b></td><td bgcolor="LIGHTBLUE">
select distinct decode(d.log_mode,'ARCHIVELOG',p.name||' = '||p.value||'<br/>', 'N/A') from v$database d,v$parameter p where (p.name like 'log_archive_dest_%' or p.name = 'log_archive_dest') and p.name not like '%state%' and p.value is not NULL;
+
select distinct decode(d.log_mode,'ARCHIVELOG',p.name,' = ',p.value,'<br/>', 'N/A') from v$database d,v$parameter p where (p.name like 'log_archive_dest_%' or p.name = 'log_archive_dest') and p.name not like '%state%' and p.value is not NULL;
  
 
DECLARE cnt_dest number := 0;
 
DECLARE cnt_dest number := 0;
Ligne 527 : Ligne 535 :
 
prompt <td align=center><font color="WHITE"><b>Initialisation : spfile ou init.ora ?</b></font></td></tr></table></td></tr>
 
prompt <td align=center><font color="WHITE"><b>Initialisation : spfile ou init.ora ?</b></font></td></tr></table></td></tr>
 
prompt <tr><td width=15%><b>SPFILE</b></td>
 
prompt <tr><td width=15%><b>SPFILE</b></td>
SELECT decode(value,'','<td bgcolor="#FF0000" width=15%>NON</td>','<td bgcolor="#33FF33" width=15%>OUI</td>'), decode(value,'','<td>N/A</td></tr>','<td>'||value||'</td></tr>') FROM v$parameter WHERE name like 'spfile' ;
+
SELECT decode(value,'','<td bgcolor="#FF0000" width=15%>NON</td>','<td bgcolor="#33FF33" width=15%>OUI</td>'), decode(value,'','<td>N/A</td></tr>','<td>',value,'</td></tr>') FROM v$parameter WHERE name like 'spfile' ;
 
set define "&"
 
set define "&"
  
Ligne 613 : Ligne 621 :
 
-- prompt <tr><td><b>Tablespace</b></td><td><b>Contenu</b></td><td><b>Statut</b></td></tr>
 
-- prompt <tr><td><b>Tablespace</b></td><td><b>Contenu</b></td><td><b>Statut</b></td></tr>
 
-- prompt  
 
-- 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
+
-- 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;
 
-- order by tablespace_name;
 
-- prompt </table><br>
 
-- prompt </table><br>
Ligne 627 : Ligne 635 :
  
 
select '<tr>','<td bgcolor="LIGHTBLUE">'||df.TABLESPACE_NAME||'</td>','<td bgcolor="LIGHTBLUE">'||df.FILE_NAME||'</td>',
 
select '<tr>','<td bgcolor="LIGHTBLUE">'||df.TABLESPACE_NAME||'</td>','<td bgcolor="LIGHTBLUE">'||df.FILE_NAME||'</td>',
--'<td bgcolor="'||decode (CONTENTS,'UNDO','#33FF33',decode(autoextensible,'NO','#33FF33',CouleurLimite(sum(blocks)*&dbloc,sum(maxbytes),sum(maxbytes)*0.10,1)))||'" 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="#FF9900" 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>'
 
--'<td bgcolor="'||decode (CONTENTS,'UNDO','#33FF33',decode(autoextensible,'NO','#33FF33',CouleurLimite(sum(df.blocks)*&dbloc,(sum(df.maxbytes)-(sum(df.maxbytes)*0.20)),(sum(df.maxbytes)-(sum(df.maxbytes)*0.20))*0.10,1)))||'" align=right>'||decode(round(sum(df.bytes)/(1024*1024),2),NULL,to_char('0','S99G999G990D00'),to_char(round(sum(df.bytes)/(1024*1024),2),'99G999G990D00'))||'</td>','<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(BIGFILE,'YES',round(df.bytes/(1024*1024*1024),2),round(df.bytes/(1024*1024),2)),'99G999G990D00')||'</td>', decode(autoextensible,'NO','<td bgcolor="#FF9900" 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),'99G999G990')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(BIGFILE,'YES',round(sum(df.maxbytes)/(1024*1024*1024),2),round(sum(df.maxbytes)/(1024*1024),2)),'99G999G990')||'</td>', '</tr>'
 
 
'<td bgcolor="'||decode (CONTENTS,'UNDO','#33FF33',decode(autoextensible,'NO','#33FF33',CouleurLimite(sum(df.blocks)*&dbloc,(sum(df.maxbytes)-(sum(df.maxbytes)*0.20)),(sum(df.maxbytes)-(sum(df.maxbytes)*0.20))*0.10,1)))||'" align=right>'||decode(round(sum(df.bytes)/(1024*1024),2),NULL,to_char('0','S99G999G990D00'),to_char(round(sum(df.bytes)/(1024*1024),2),'99G999G990D00'))||'</td>', decode(autoextensible,'NO','<td bgcolor="#FF9900" 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),'99G999G990')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(BIGFILE,'YES',round(sum(df.maxbytes)/(1024*1024*1024),2),round(sum(df.maxbytes)/(1024*1024),2)),'99G999G990')||'</td>', '</tr>'
 
'<td bgcolor="'||decode (CONTENTS,'UNDO','#33FF33',decode(autoextensible,'NO','#33FF33',CouleurLimite(sum(df.blocks)*&dbloc,(sum(df.maxbytes)-(sum(df.maxbytes)*0.20)),(sum(df.maxbytes)-(sum(df.maxbytes)*0.20))*0.10,1)))||'" align=right>'||decode(round(sum(df.bytes)/(1024*1024),2),NULL,to_char('0','S99G999G990D00'),to_char(round(sum(df.bytes)/(1024*1024),2),'99G999G990D00'))||'</td>', decode(autoextensible,'NO','<td bgcolor="#FF9900" 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),'99G999G990')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(BIGFILE,'YES',round(sum(df.maxbytes)/(1024*1024*1024),2),round(sum(df.maxbytes)/(1024*1024),2)),'99G999G990')||'</td>', '</tr>'
 
from DBA_DATA_FILES df, DBA_TABLESPACES dt
 
from DBA_DATA_FILES df, DBA_TABLESPACES dt
Ligne 654 : Ligne 660 :
 
set define "&"
 
set define "&"
 
-- TABLESPACES DATAS
 
-- TABLESPACES DATAS
select '<tr>','<td bgcolor="LIGHTBLUE">'||t.tablespace_name||'</td>' Tablespace, '<td bgcolor="'||decode(BIGFILE,'YES','#FF9900','LIGHTBLUE')||'" align=center>'||maxt.bigfile||'</td>', '<td bgcolor="LIGHTBLUE">'||maxt.contents||'</td>', decode(maxt.status,'ONLINE','<td bgcolor="LIGHTBLUE">'||maxt.status||'</td>','<td bgcolor="#FF0000">'||maxt.status||'</td>'),
+
select '<tr>','<td bgcolor="LIGHTBLUE">',t.tablespace_name,'</td>', '<td bgcolor="',decode(BIGFILE,'YES','#FF9900','LIGHTBLUE'),'" align=center>',maxt.bigfile,'</td>', '<td bgcolor="LIGHTBLUE">',maxt.contents,'</td>', decode(maxt.status,'ONLINE','<td bgcolor="LIGHTBLUE">','<td bgcolor="#FF0000">'),maxt.status,'</td>',
       '<td bgcolor="LIGHTBLUE" align=right>'||decode(t.autoextensible,'NO',decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(t.total,'99G999G990D00')),decode(maxt.maxtotal,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(maxt.maxtotal,'99G999G990D00')))||'</td>' TOTAL,
+
       '<td bgcolor="LIGHTBLUE" align=right>',decode(t.autoextensible,'NO',decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(t.total,'99G999G990D00')),decode(maxt.maxtotal,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(maxt.maxtotal,'99G999G990D00'))),'</td>' TOTAL,
       '<td bgcolor="LIGHTBLUE" align=right>'||decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(t.total,'99G999G990D00'))||'</td>' TOTAL_CURRENT,
+
       '<td bgcolor="LIGHTBLUE" align=right>',decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(t.total,'99G999G990D00')),'</td>' TOTAL_CURRENT,
       '<td bgcolor="LIGHTBLUE" align=right>'||decode(u.utilise,'','0,00',to_char(u.utilise,'99G999G990D00'))||'</td>' UTILISE,
+
       '<td bgcolor="LIGHTBLUE" align=right>',decode(u.utilise,'','0,00',to_char(u.utilise,'99G999G990D00')),'</td>' UTILISE,
       '<td bgcolor="'||decode(t.autoextensible,'NO',decode(u.utilise,'', '#33FF33',CouleurLimite(u.utilise,t.total-(t.total*0.20),t.total*0.10,1)),decode(u.utilise,'', '#33FF33', CouleurLimite(u.utilise,maxt.maxtotal-(maxt.maxtotal*0.20),maxt.maxtotal*0.10,1)))||'" 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>',
+
       '<td bgcolor="',decode(t.autoextensible,'NO',decode(u.utilise,'', '#33FF33',CouleurLimite(u.utilise,t.total-(t.total*0.20),t.total*0.10,1)),decode(u.utilise,'', '#33FF33', CouleurLimite(u.utilise,maxt.maxtotal-(maxt.maxtotal*0.20),maxt.maxtotal*0.10,1))),'" 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>','<td bgcolor="WHITE" colspan=2 align=right><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td>',
 
 
decode(SIGN(a.total-h.total),
 
decode(SIGN(a.total-h.total),
 
       -1,'<td bgcolor="#33FF33" align=right>'||to_char(a.total-h.total,'S99G999G990D00')||'</td>',
 
       -1,'<td bgcolor="#33FF33" align=right>'||to_char(a.total-h.total,'S99G999G990D00')||'</td>',
Ligne 669 : Ligne 674 :
 
       0,'<td bgcolor="LIGHTBLUE" align=right>'||to_char(a.utilis-h.utilis,'99G999G990D00')||'</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>',
 
       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>' --,
+
       NULL,'<td bgcolor="BLUE" align=right><font color="WHITE">Premier audit</font></td>'),'</tr>'
--      '<tr><td bgcolor="WHITE" colspan=6 height=3>
 
--      <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td></tr>'
 
 
from (select tablespace_name, autoextensible,
 
from (select tablespace_name, autoextensible,
 
             round(sum(bytes)/(1024*1024),2) total
 
             round(sum(bytes)/(1024*1024),2) total
Ligne 708 : Ligne 711 :
  
 
-- TABLESPACE UNDO
 
-- TABLESPACE UNDO
select '<tr>','<td bgcolor="LIGHTBLUE">'||t.tablespace_name||'</td>' Tablespace, '<td bgcolor="'||decode(BIGFILE,'YES','#FF9900','LIGHTBLUE')||'" align=center>'||maxt.bigfile||'</td>', '<td bgcolor="LIGHTBLUE">'||maxt.contents||'</td>', decode(maxt.status,'ONLINE','<td bgcolor="LIGHTBLUE">'||maxt.status||'</td>','<td bgcolor="#FF0000">'||maxt.status||'</td>'),
+
select '<tr>','<td bgcolor="LIGHTBLUE">',t.tablespace_name,'</td>' Tablespace, '<td bgcolor="',decode(BIGFILE,'YES','#FF9900','LIGHTBLUE'),'" align=center>',maxt.bigfile,'</td>', '<td bgcolor="LIGHTBLUE">',maxt.contents,'</td>', decode(maxt.status,'ONLINE','<td bgcolor="LIGHTBLUE">',maxt.status,'</td>','<td bgcolor="#FF0000">',maxt.status,'</td>'),
       '<td bgcolor="LIGHTBLUE" align=right>'||decode(t.autoextensible,'NO',decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(t.total,'99G999G990D00')),decode(maxt.maxtotal,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(maxt.maxtotal,'99G999G990D00')))||'</td>' TOTAL,
+
       '<td bgcolor="LIGHTBLUE" align=right>',decode(t.autoextensible,'NO',decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(t.total,'99G999G990D00')),decode(maxt.maxtotal,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(maxt.maxtotal,'99G999G990D00'))),'</td>' TOTAL,
       '<td bgcolor="LIGHTBLUE" align=right>'||decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(t.total,'99G999G990D00'))||'</td>' TOTAL_CURRENT,
+
       '<td bgcolor="LIGHTBLUE" align=right>',decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(t.total,'99G999G990D00')),'</td>' TOTAL_CURRENT,
       '<td bgcolor="LIGHTBLUE" align=right>'||decode(u.utilise,'','0,00',to_char(u.utilise,'99G999G990D00'))||'</td>' UTILISE,
+
       '<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,'<td bgcolor="LIGHTGREY" align=center><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td><td bgcolor="LIGHTGREY" align=center><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td></tr>'
+
       '<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,'<td bgcolor="LIGHTGREY" align=center><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td><td bgcolor="LIGHTGREY" align=center><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td></tr>'
 
from (select tablespace_name, autoextensible,
 
from (select tablespace_name, autoextensible,
 
             round(sum(bytes)/(1024*1024),2) total
 
             round(sum(bytes)/(1024*1024),2) total
Ligne 737 : Ligne 740 :
  
 
-- TABLESPACE TEMP
 
-- TABLESPACE TEMP
select '<tr>','<td bgcolor="LIGHTBLUE">'||t.tablespace_name||'</td>','<td bgcolor="LIGHTGREY" align=center>TEMP</td>' as bigfile, '<td bgcolor="LIGHTBLUE">'||contents||'</td>', decode(status,'ONLINE','<td bgcolor="LIGHTBLUE">'||status||'</td>','<td bgcolor="#FF0000">'||status||'</td>'),
+
select '<tr>','<td bgcolor="LIGHTBLUE">',t.tablespace_name,'</td>','<td bgcolor="LIGHTGREY" align=center>TEMP</td>' as bigfile, '<td bgcolor="LIGHTBLUE">',contents,'</td>', decode(status,'ONLINE','<td bgcolor="LIGHTBLUE">',status,'</td>','<td bgcolor="#FF0000">',status,'</td>'),
         '<td bgcolor="LIGHTBLUE" align=right>'||to_char(total,'99G999G990D00')||'</td>' as total,  
+
         '<td bgcolor="LIGHTBLUE" align=right>',to_char(total,'99G999G990D00'),'</td>' as total,  
         '<td bgcolor="LIGHTBLUE" align=right>'||to_char(total,'99G999G990D00')||'</td>' as fake,  
+
         '<td bgcolor="LIGHTBLUE" align=right>',to_char(total,'99G999G990D00'),'</td>' as fake,  
 
         '<td bgcolor="LIGHTBLUE" align=right>0,00</td>' as utilise,
 
         '<td bgcolor="LIGHTBLUE" align=right>0,00</td>' as utilise,
         '<td bgcolor="LIGHTBLUE" align=right>'||to_char(total,'99G999G990D00')||'</td>' as libre,'<td bgcolor="LIGHTGREY" align=center><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td><td bgcolor="LIGHTGREY" align=center><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td></tr>'
+
         '<td bgcolor="LIGHTBLUE" align=right>',to_char(total,'99G999G990D00'),'</td>' as libre,'<td bgcolor="LIGHTGREY" align=center><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td><td bgcolor="LIGHTGREY" align=center><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td></tr>'
 
from (select df.tablespace_name, dt.contents, dt.status,
 
from (select df.tablespace_name, dt.contents, dt.status,
 
             round(sum(bytes)/(1024*1024),2) total
 
             round(sum(bytes)/(1024*1024),2) total
Ligne 749 : Ligne 752 :
  
 
select  '<tr>','<td bgcolor="WHITE" colspan=4>TOTAL</td>',
 
select  '<tr>','<td bgcolor="WHITE" colspan=4>TOTAL</td>',
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||to_char(decode(dmty.total,'',0,dmty.total)+decode(dmtn.total,'',0,dmtn.total)+tt.total,'99G999G990D00')||'</b></font></td>' as maxtotal,  
+
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',to_char(decode(dmty.total,'',0,dmty.total)+decode(dmtn.total,'',0,dmtn.total)+tt.total,'99G999G990D00'),'</b></font></td>' as maxtotal,  
         '<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(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(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>'
+
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',to_char(dl.libre+tl.libre,'99G999G990D00'),'</b></font></td>' as libre
 
from (select round(sum(df.maxbytes)/(1024*1024*1024),2) total
 
from (select round(sum(df.maxbytes)/(1024*1024*1024),2) total
 
       from dba_data_files df, dba_tablespaces dt
 
       from dba_data_files df, dba_tablespaces dt
Ligne 770 : Ligne 773 :
 
     (select round(sum(bytes)/(1024*1024),2) libre from dba_temp_files) tl;
 
     (select round(sum(bytes)/(1024*1024),2) libre from dba_temp_files) tl;
 
      
 
      
select '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||to_char(round(sum(a.total-h.total)),'S99G999G990D00')||'</b></font></td>' as total,  
+
select '<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 colspan=4><font color="WHITE"><b>'||to_char(round(sum(a.utilis-h.utilis)),'S99G999G990D00')||'</b></font></td>' as utilise,'</tr>'
+
         '<td bgcolor="BLUE" align=right colspan=4><font color="WHITE"><b>',to_char(round(sum(a.utilis-h.utilis)),'S99G999G990D00'),'</b></font></td>' as utilise,'</tr>'
 
from (select * from system.histaudit
 
from (select * from system.histaudit
 
where trunc(to_date(date_aud))=trunc(sysdate)
 
where trunc(to_date(date_aud))=trunc(sysdate)
Ligne 800 : Ligne 803 :
 
set define "&"
 
set define "&"
 
select  '<tr>','<td bgcolor="LIGHTBLUE">TABLES</td>',
 
select  '<tr>','<td bgcolor="LIGHTBLUE">TABLES</td>',
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||decode(a.total, NULL, '0,00', to_char(round(a.total,2),'99G999G990D00'))||'</b></font></td>',
+
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',decode(a.total, NULL, '0,00', to_char(round(a.total,2),'99G999G990D00')),'</b></font></td>',
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||decode(a.total, NULL, to_char(round(-l.total,2),'S99G999G990D000'), to_char(round(a.total-l.total,2),'S99G999G990D00'))||'</b></font></td>','</tr>'
+
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',decode(a.total, NULL, to_char(round(-l.total,2),'S99G999G990D000'), to_char(round(a.total-l.total,2),'S99G999G990D00')),'</b></font></td>','</tr>'
 
from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments
 
from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments
 
where segment_type like 'TABLE%'
 
where segment_type like 'TABLE%'
Ligne 812 : Ligne 815 :
 
and type_obj='TAB') l;
 
and type_obj='TAB') l;
 
select  '<tr>','<td bgcolor="LIGHTBLUE">INDEXES</td>',
 
select  '<tr>','<td bgcolor="LIGHTBLUE">INDEXES</td>',
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||decode(a.total, NULL, '0,00', to_char(round(a.total,2),'99G999G990D00'))||'</b></font></td>',  
+
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',decode(a.total, NULL, '0,00', to_char(round(a.total,2),'99G999G990D00')),'</b></font></td>',  
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||decode(a.total, NULL, to_char(round(-l.total,2),'S99G999G990D00'), to_char(round(a.total-l.total,2),'S99G999G990D00'))||'</b></font></td>','</tr>'
+
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',decode(a.total, NULL, to_char(round(-l.total,2),'S99G999G990D00'), to_char(round(a.total-l.total,2),'S99G999G990D00')),'</b></font></td>','</tr>'
 
from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments
 
from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments
 
where segment_type like 'INDEX%'
 
where segment_type like 'INDEX%'
Ligne 824 : Ligne 827 :
 
and type_obj='IND') l;
 
and type_obj='IND') l;
 
select DISTINCT '<tr>','<td bgcolor="LIGHTBLUE">AUTRES (LOB SEGMENTS, LOB INDEXES, CLUSTERS,...)</td>',
 
select DISTINCT '<tr>','<td bgcolor="LIGHTBLUE">AUTRES (LOB SEGMENTS, LOB INDEXES, CLUSTERS,...)</td>',
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||decode(a.total, NULL, '0,00', to_char(round(a.total,2),'99G999G990D00'))||'</b></font></td>',  
+
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',decode(a.total, NULL, '0,00', to_char(round(a.total,2),'99G999G990D00')),'</b></font></td>',  
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||decode(a.total, NULL, to_char(round(-l.total,2),'S99G999G990D00'), to_char(round(a.total-l.total,2),'S99G999G990D00'))||'</b></font></td>','</tr>'
+
         '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',decode(a.total, NULL, to_char(round(-l.total,2),'S99G999G990D00'), to_char(round(a.total-l.total,2),'S99G999G990D00')),'</b></font></td>','</tr>'
 
from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments
 
from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments
 
where segment_type not like 'TABLE%' and segment_type not like 'INDEX%'
 
where segment_type not like 'TABLE%' and segment_type not like 'INDEX%'
Ligne 878 : Ligne 881 :
 
prompt <tr><td bgcolor="LIGHTBLUE" valign=top>Nombre de switchs par jour (depuis 30 jours)</td>
 
prompt <tr><td bgcolor="LIGHTBLUE" valign=top>Nombre de switchs par jour (depuis 30 jours)</td>
 
prompt <td bgcolor="LIGHTBLUE" align=right>
 
prompt <td bgcolor="LIGHTBLUE" align=right>
select trunc(first_time)||'<br/>' from v$loghist
+
select trunc(first_time),'<br/>' from v$loghist
 
where first_time > (sysdate-30)
 
where first_time > (sysdate-30)
 
group by trunc(first_time)
 
group by trunc(first_time)
 
order by trunc(first_time);
 
order by trunc(first_time);
 
prompt </td><td bgcolor="LIGHTBLUE" align=right>
 
prompt </td><td bgcolor="LIGHTBLUE" align=right>
select count(first_time)||'<br/>' from v$loghist
+
select count(first_time),'<br/>' from v$loghist
 
where first_time > (sysdate-30)
 
where first_time > (sysdate-30)
 
group by trunc(first_time)
 
group by trunc(first_time)
Ligne 890 : Ligne 893 :
  
 
-- *************************************** Stats switchs
 
-- *************************************** 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>'
+
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
 
from (select count(*) as nbc from v$loghist a, v$loghist b
 
       where a.first_change#=b.switch_change#
 
       where a.first_change#=b.switch_change#
Ligne 899 : Ligne 902 :
 
prompt <tr><td bgcolor="LIGHTBLUE">Nombre de switchs par mois</td>
 
prompt <tr><td bgcolor="LIGHTBLUE">Nombre de switchs par mois</td>
 
prompt <td bgcolor="LIGHTBLUE" align=right>
 
prompt <td bgcolor="LIGHTBLUE" align=right>
select to_char(to_date(first_time),'mm/yyyy')||'<br/>' from v$loghist
+
select to_char(to_date(first_time),'mm/yyyy'),'<br/>' from v$loghist
 
where first_time > (sysdate-365)
 
where first_time > (sysdate-365)
 
group by to_char(to_date(first_time),'mm/yyyy')
 
group by to_char(to_date(first_time),'mm/yyyy')
 
order 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>
 
prompt <td bgcolor="LIGHTBLUE" align=right>
select count(*)||'<br/>' from v$loghist
+
select count(*),'<br/>' from v$loghist
 
where first_time > (sysdate-365)
 
where first_time > (sysdate-365)
 
group by to_char(to_date(first_time),'mm/yyyy')
 
group by to_char(to_date(first_time),'mm/yyyy')
Ligne 911 : Ligne 914 :
  
 
-- *************************************** temps minimum entre 2 switchs
 
-- *************************************** 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>'
+
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
 
from v$loghist a, v$loghist b
 
where a.first_change#=b.switch_change#;
 
where a.first_change#=b.switch_change#;
  
 
-- *************************************** temps maximum entre 2 switchs
 
-- *************************************** 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>'
+
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
 
from v$loghist a, v$loghist b
 
where a.first_change#=b.switch_change#;
 
where a.first_change#=b.switch_change#;
  
 
-- *************************************** temps moyen entre 2 switchs
 
-- *************************************** temps moyen entre 2 switchs
select '<tr>','<td bgcolor="WHITE">Temps MOY. entre 2 switchs :</td>', '<td bgcolor="BLUE" align=right colspan=2><font color="WHITE">'||to_char((sum(a.first_time-b.first_time)*24*3600)/count(a.first_time),'99999G990')||' secondes</td>','</tr>'
+
select '<tr>','<td bgcolor="WHITE">Temps MOY. entre 2 switchs :</td>', '<td bgcolor="BLUE" align=right colspan=2><font color="WHITE">',to_char((sum(a.first_time-b.first_time)*24*3600)/count(a.first_time),'99999G990'),' secondes</td>','</tr>'
 
from v$loghist a, v$loghist b
 
from v$loghist a, v$loghist b
 
where a.first_change#=b.switch_change#;
 
where a.first_change#=b.switch_change#;
Ligne 935 : Ligne 938 :
 
--prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Rollback segments</b></font></td></tr>
 
--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>
 
--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;
+
--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>
 
--prompt </table><br>
Ligne 943 : Ligne 946 :
 
--prompt <tr><td bgcolor="#3399CC" align=center colspan=4><font color="WHITE"><b>Statistiques rollback segments</b></font></td></tr>
 
--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>
 
--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
+
--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;
 
--where v$rollstat.usn=v$rollname.usn;
  
Ligne 968 : Ligne 971 :
  
 
set define "&"
 
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>'
+
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
 
from v$session_wait
 
where event like 'log%';
 
where event like 'log%';
Ligne 998 : Ligne 1 001 :
  
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">'||class||'</td>','<td bgcolor="'||CouleurLimite(count,10000000,9990000,1)||'" align=right>'||count||'</td>','</tr>' from v$waitstat;
+
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>
 
prompt </table><br>
Ligne 1 016 : Ligne 1 019 :
  
 
--select
 
--select
--'<tr>','<td bgcolor="LIGHTBLUE">'||f.tablespace_name||'</td>',
+
--'<tr>','<td bgcolor="LIGHTBLUE">',f.tablespace_name,'</td>',
--'<td bgcolor="LIGHTBLUE">'||f.file_name||'</td>',
+
--'<td bgcolor="LIGHTBLUE">',f.file_name,'</td>',
--'<td bgcolor="LIGHTBLUE" align=right>'||(v.phyrds+v.phywrts)||'</td>',
+
--'<td bgcolor="LIGHTBLUE" align=right>',(v.phyrds+v.phywrts),'</td>',
--'<td bgcolor="'||CouleurLimite(ROUND(100*(v.phyrds+v.phywrts)/(v.phyblkrd+v.phyblkwrt),0),60,20,0)||'" align=right>'||TO_CHAR(DECODE(v.phyblkrd,0,null,ROUND(100*(v.phyrds+v.phywrts)/(v.phyblkrd+v.phyblkwrt),0)))||'%</td>',
+
--'<td bgcolor="',CouleurLimite(ROUND(100*(v.phyrds+v.phywrts)/(v.phyblkrd+v.phyblkwrt),0),60,20,0),'" align=right>',TO_CHAR(DECODE(v.phyblkrd,0,null,ROUND(100*(v.phyrds+v.phywrts)/(v.phyblkrd+v.phyblkwrt),0))),'%</td>',
 
--'</tr>'
 
--'</tr>'
 
--from DBA_data_files f, v$filestat v
 
--from DBA_data_files f, v$filestat v
Ligne 1 040 : Ligne 1 043 :
  
 
select
 
select
'<tr>','<td bgcolor="LIGHTBLUE">'||f.tablespace_name||'</td>',
+
'<tr>','<td bgcolor="LIGHTBLUE">',f.tablespace_name,'</td>',
'<td bgcolor="LIGHTBLUE">'||f.file_name||'</td>',
+
'<td bgcolor="LIGHTBLUE">',f.file_name,'</td>',
'<td bgcolor="LIGHTBLUE" align=right>'||v.phyrds||'</td>',
+
'<td bgcolor="LIGHTBLUE" align=right>',v.phyrds,'</td>',
'<td bgcolor="LIGHTBLUE" align=right>'||v.phyblkrd||'</td>',
+
'<td bgcolor="LIGHTBLUE" align=right>',v.phyblkrd,'</td>',
'<td bgcolor="'||CouleurLimite(ROUND(100*(v.phyrds/v.phyblkrd),0),20,5,0)||'" align=right>'||TO_CHAR(DECODE(v.phyblkrd,0,null,ROUND(100*(v.phyrds/v.phyblkrd),0)))||'%</td>',
+
'<td bgcolor="',CouleurLimite(ROUND(100*(v.phyrds/v.phyblkrd),0),20,5,0),'" align=right>',TO_CHAR(DECODE(v.phyblkrd,0,null,ROUND(100*(v.phyrds/v.phyblkrd),0))),'%</td>',
--'<td bgcolor="LIGHTBLUE" align=right>'||v.phywrts||'</td>',
 
--'<td bgcolor="LIGHTBLUE" align=right>'||v.phyblkwrt||'</td>',
 
--'<td bgcolor="'||CouleurLimite(ROUND(100*(v.phywrts/v.phyblkwrt),0),60,20,0)||'" align=right>'||TO_CHAR(DECODE(v.phyblkwrt,0,null,ROUND(100*(v.phywrts/v.phyblkwrt),0)))||'%</td>',
 
 
'</tr>'
 
'</tr>'
 
from DBA_data_files f, v$filestat v
 
from DBA_data_files f, v$filestat v
Ligne 1 075 : Ligne 1 075 :
 
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>
 
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 "&"
 
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,'999999990D00')||'</td>','</tr>' from v$system_event
+
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,'999999990D00'),'</td>','</tr>' from v$system_event
 
where event like 'log%' or event like 'db file%';
 
where event like 'log%' or event like 'db file%';
  
Ligne 1 185 : Ligne 1 185 :
 
prompt <td><b>Espaces m&eacute;moire</b></td><td><b>SPFILE (Mo)</b></td><td><b>Utilis&eacute; (Mo)</b></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 "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">'||a.obj_name||'</td>',
+
select '<tr>','<td bgcolor="LIGHTBLUE">',a.obj_name,'</td>',
 
decode(SIGN(a.total-h.total),
 
decode(SIGN(a.total-h.total),
 
       -1,'<td bgcolor="#33FF33" align=right>'||to_char(a.total-h.total,'S99G999G990D00')||'</td>',
 
       -1,'<td bgcolor="#33FF33" align=right>'||to_char(a.total-h.total,'S99G999G990D00')||'</td>',
Ligne 1 215 : Ligne 1 215 :
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>vue V$SGAINFO (>=10g)</b></font></td></tr>
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>vue V$SGAINFO (>=10g)</b></font></td></tr>
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">'||name||'</td>' NOM,'<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(bytes/(1024*1024),2),'99G999G990D00')||'</td>' total,'</tr>' from v$sgainfo;
+
select '<tr>','<td bgcolor="LIGHTBLUE">',name,'</td>' NOM,'<td bgcolor="LIGHTBLUE" align=right>',to_char(round(bytes/(1024*1024),2),'99G999G990D00'),'</td>' total,'</tr>' from v$sgainfo;
 
-- Pour compatibilite avec 9i :
 
-- Pour compatibilite avec 9i :
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>vue V$SGA (toutes versions)</b></font></td></tr>
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>vue V$SGA (toutes versions)</b></font></td></tr>
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;
+
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>
 
prompt </table><br>
  
Ligne 1 231 : Ligne 1 231 :
 
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>
 
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 "&"
 
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>'
+
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 (select name, round(value/(1024*1024),2) Total
 
       from v$parameter where name='shared_pool_size') t,
 
       from v$parameter where name='shared_pool_size') t,
Ligne 1 238 : Ligne 1 238 :
 
     (select round(sum(bytes)/(1024*1024),2) libre
 
     (select round(sum(bytes)/(1024*1024),2) libre
 
       from v$sgastat where pool='shared pool' and name = 'free memory') l;
 
       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>
 
prompt </table><br>
Ligne 1 258 : Ligne 1 256 :
 
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>
 
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 "&"
 
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>',
+
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>'
+
'<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;
 
from v$rowcache;
  
Ligne 1 279 : Ligne 1 277 :
 
prompt <tr><td><b>Executions</b></td><td><b>Rechargements</b></td><td colspan=2><b>Ratio</b></td></tr>
 
prompt <tr><td><b>Executions</b></td><td><b>Rechargements</b></td><td colspan=2><b>Ratio</b></td></tr>
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE" align=right>'||sum(pins)||'</td>' exec,
+
select '<tr>','<td bgcolor="LIGHTBLUE" align=right>',sum(pins),'</td>' exec,
'<td bgcolor="LIGHTBLUE" align=right>'||sum(reloads)||'</td>' recharg,
+
'<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>'
+
'<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;
 
from v$librarycache;
  
Ligne 1 298 : Ligne 1 296 :
 
prompt <tr><td colspan=2><b>Namespace</b></td><td><b>GetHits</b></td><td><b>PinHits</b></td></tr>
 
prompt <tr><td colspan=2><b>Namespace</b></td><td><b>GetHits</b></td><td><b>PinHits</b></td></tr>
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE" colspan=2>'||namespace||'</td>',
+
select '<tr>','<td bgcolor="LIGHTBLUE" colspan=2>',namespace,'</td>',
'<td bgcolor="'||CouleurLimite(round(gethitratio,2)*100,70,10,0)||'" align=right>'||round(gethitratio,2)*100||' %</td>',
+
'<td bgcolor="',CouleurLimite(round(gethitratio,2)*100,70,10,0),'" align=right>',round(gethitratio,2)*100,' %</td>',
'<td bgcolor="'||CouleurLimite(round(pinhitratio,2)*100,70,10,0)||'" align=right>'||round(pinhitratio,2)*100||' %</td>','</tr>'
+
'<td bgcolor="',CouleurLimite(round(pinhitratio,2)*100,70,10,0),'" align=right>',round(pinhitratio,2)*100,' %</td>','</tr>'
 
from v$librarycache;
 
from v$librarycache;
  
Ligne 1 310 : Ligne 1 308 :
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=8><font color="WHITE"><b>Requ&ecirc;tes les plus gourmandes en ressources (moyennes par ex&eacute;cution)</b></font></td></tr>
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=8><font color="WHITE"><b>Requ&ecirc;tes les plus gourmandes en ressources (moyennes par ex&eacute;cution)</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>
 
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>
SELECT '<tr>','<td bgcolor="LIGHTBLUE">'||sqla.address||'</td>',
+
SELECT '<tr>','<td bgcolor="LIGHTBLUE">',sqla.address,'</td>',
'<td bgcolor="LIGHTBLUE">'||sqla.executions||'</td>',
+
'<td bgcolor="LIGHTBLUE">',sqla.executions,'</td>',
'<td bgcolor="LIGHTBLUE">'||round(sqla.sorts/sqla.executions,0)||'</td>',
+
'<td bgcolor="LIGHTBLUE">',round(sqla.sorts/sqla.executions,0),'</td>',
'<td bgcolor="LIGHTBLUE">'||round(sqla.disk_reads/sqla.executions,0)||'</td>',
+
'<td bgcolor="LIGHTBLUE">',round(sqla.disk_reads/sqla.executions,0),'</td>',
'<td bgcolor="LIGHTBLUE"><b>'||round(sqla.cpu_time/sqla.executions,0)||'</b></td>',
+
'<td bgcolor="LIGHTBLUE"><b>',round(sqla.cpu_time/sqla.executions,0),'</b></td>',
'<td bgcolor="LIGHTBLUE">'||round(sqla.buffer_gets/sqla.executions,0)||'</td>',
+
'<td bgcolor="LIGHTBLUE">',round(sqla.buffer_gets/sqla.executions,0),'</td>',
'<td bgcolor="LIGHTBLUE">'||replace(replace(sqla.sql_text, '<', '&lt;'), '>', '&gt;') ||'</td>','</tr>'
+
'<td bgcolor="LIGHTBLUE">',replace(replace(sqla.sql_text, '<', '&lt;'), '>', '&gt;') ,'</td>','</tr>'
 
FROM  (select * from v$sqlarea where executions > 1) sqla
 
FROM  (select * from v$sqlarea where executions > 1) sqla
 
WHERE  sqla.cpu_time > 1000
 
WHERE  sqla.cpu_time > 1000
 
and    sqla.COMMAND_TYPE in (2,3,6,7)
 
and    sqla.COMMAND_TYPE in (2,3,6,7)
--AND    sqla.executions > 1 -- exclude our own and occasionnal requests
 
 
AND    sqla.BUFFER_GETS/sqla.executions > 100
 
AND    sqla.BUFFER_GETS/sqla.executions > 100
 
AND ROWNUM < 11
 
AND ROWNUM < 11
Ligne 1 351 : Ligne 1 348 :
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=4><font color="WHITE"><b>Large pool</b></font></td></tr>
 
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>
 
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>'
+
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 (select name, round(value/(1024*1024),2) total
 
       from v$parameter where name='large_pool_size') t,
 
       from v$parameter where name='large_pool_size') t,
Ligne 1 367 : Ligne 1 364 :
 
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>
 
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 "&"
 
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>'
+
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 (select name, round(value/(1024*1024),2) total
 
       from v$parameter where name='java_pool_size') t,
 
       from v$parameter where name='java_pool_size') t,
Ligne 1 428 : Ligne 1 425 :
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Redo buffers</b></font></td></tr>
 
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>
 
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
+
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';
 
where name='log_buffer';
  
Ligne 1 467 : Ligne 1 464 :
 
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>
 
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 "&"
 
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>'
+
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
 
from  v$latch
 
where  name in ('redo allocation',  'redo copy')
 
where  name in ('redo allocation',  'redo copy')
Ligne 1 482 : Ligne 1 479 :
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Taille zone de tri</b></font></td></tr>
 
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>
 
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
+
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';
 
where name='sort_area_size';
 
-- *************************************** Statistiques zone de tri
 
-- *************************************** Statistiques zone de tri
Ligne 1 506 : Ligne 1 503 :
 
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>
 
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 "&"
 
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>'
+
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
 
from v$process,v$parameter
 
where name='pga_aggregate_target'
 
where name='pga_aggregate_target'
Ligne 1 516 : Ligne 1 513 :
 
prompt <tr><td width=15% colspan=2><b>Sch&eacute;ma</b></td><td width=15%><b>Nombre de sessions par sch&eacute;ma</b></td></tr>
 
prompt <tr><td width=15% colspan=2><b>Sch&eacute;ma</b></td><td width=15%><b>Nombre de sessions par sch&eacute;ma</b></td></tr>
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE" colspan=2>'||username||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||count(*)||'</td>','</tr>'
+
select '<tr>','<td bgcolor="LIGHTBLUE" colspan=2>',username,'</td>', '<td bgcolor="LIGHTBLUE" align=right>',count(*),'</td>','</tr>'
 
from v$statname n, v$sesstat t, v$session s
 
from v$statname n, v$sesstat t, v$session s
 
where s.sid=t.sid
 
where s.sid=t.sid
Ligne 1 525 : Ligne 1 522 :
 
group by username;
 
group by username;
  
select '<tr><td width=15% colspan=2><b>Nombre d''utilisateurs au moment de l''audit</b></td>','<td bgcolor="BLUE" align=right><font color="WHITE"><b>'||count(*)||'</b></td>','</tr>'
+
select '<tr><td width=15% colspan=2><b>Nombre d''utilisateurs au moment de l''audit</b></td>','<td bgcolor="BLUE" align=right><font color="WHITE"><b>',count(*),'</b></td>','</tr>'
 
from v$statname n, v$sesstat t, v$session s
 
from v$statname n, v$sesstat t, v$session s
 
where s.sid=t.sid
 
where s.sid=t.sid
Ligne 1 533 : Ligne 1 530 :
 
and n.name='session pga memory';
 
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>'
+
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
 
from v$statname n, v$sesstat t
 
where n.statistic#=t.statistic#
 
where n.statistic#=t.statistic#
 
and n.name='session uga memory';
 
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>'
+
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
 
from v$statname n, v$sesstat t
 
where n.statistic#=t.statistic#
 
where n.statistic#=t.statistic#
Ligne 1 665 : Ligne 1 662 :
 
       and text not like '%Private_strands%'
 
       and text not like '%Private_strands%'
 
       and trim(text) not like '(&_db)'
 
       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 '%Created Undo Segment%'
 
       and text not like '%started with pid%'
 
       and text not like '%started with pid%'
Ligne 1 679 : Ligne 1 675 :
 
       and text not like '%END BACKUP%'
 
       and text not like '%END BACKUP%'
 
       and text not like 'ALTER DATABASE BACKUP CONTROLFILE%'
 
       and text not like 'ALTER DATABASE BACKUP CONTROLFILE%'
 +
      and text not like 'ALTER DATABASE backup controlfile%'
 +
      and text not like 'alter database backup controlfile%'
 
       and text not like '%Starting%'
 
       and text not like '%Starting%'
 
       and text not like '%autobackup%'
 
       and text not like '%autobackup%'
Ligne 1 714 : Ligne 1 712 :
 
     end if;
 
     end if;
 
   end loop;
 
   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;
 
   commit;
Ligne 1 739 : Ligne 1 734 :
 
rem http://www.dba-oracle.com/t_writing_alert_log_message.htm
 
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>'
+
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,
 
   from alert_log a,
 
       (select max(to_date(date_aud)) date_aud from system.histaudit
 
       (select max(to_date(date_aud)) date_aud from system.histaudit
Ligne 1 796 : Ligne 1 791 :
 
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>
 
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 "&"
 
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$%';
+
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;
 
DECLARE cnt_obj number := 0;
Ligne 1 815 : Ligne 1 810 :
 
set define off
 
set define off
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Erreurs sur les objets utilisateurs (dba_errors)</b></font></td></tr>
 
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Erreurs sur les objets utilisateurs (dba_errors)</b></font></td></tr>
prompt <tr><td><b>Objet, numéro et texte de la ligne</b></td><td><b>Erreur</b></td></tr>
+
prompt <tr><td><b>Objet, num&eacute;ro et texte de la ligne</b></td><td><b>Erreur</b></td></tr>
  
 
with err as (
 
with err as (
   select distinct owner, name, type, line, position, sequence, text
+
   select distinct owner, name, type, line, position, sequence, substr(TRIM(text),0,240) as text
 
   from
 
   from
 
       dba_errors
 
       dba_errors
 
   where sequence=1
 
   where sequence=1
 
)
 
)
SELECT decode(n,-1,'<tr><td bgcolor="LIGHTBLUE">','')||substr(TRIM(text),0,240)
+
SELECT decode(n,-1,'<tr><td bgcolor="LIGHTBLUE">',''),text
 
   from (
 
   from (
       select sequence n, owner,name, type,line, lpad(' ',position-1,' ')||'</td><td bgcolor="LIGHTBLUE">'||substr(TRIM(text),0,240)||'</td></tr>' text -- Num erreur PL/SQL
+
       select sequence n, owner,name, type,line, (position-1)||'</td><td bgcolor="LIGHTBLUE">'||text||'</td></tr>' text -- Num erreur PL/SQL
 
         from err
 
         from err
 
       union all
 
       union all
Ligne 1 831 : Ligne 1 826 :
 
         from err
 
         from err
 
       union all
 
       union all
       select 0 n, owner, name, type, line, '"'||substr(TRIM(text),0,240)||'"&nbsp;:&nbsp;' -- code PL/SQL
+
       select 0 n, owner, name, type, line, '"'||substr(TRIM(text),0,240)||'" : pos. ' -- code PL/SQL
 
         from dba_source
 
         from dba_source
 
       where (owner,name,type,line) in (select owner, name, type, line from err)
 
       where (owner,name,type,line) in (select owner, name, type, line from err)
Ligne 1 840 : Ligne 1 835 :
 
BEGIN
 
BEGIN
 
with err as (
 
with err as (
   select distinct owner, name, type, line, position, sequence, text
+
   select distinct owner, name, type, line, position, sequence, substr(TRIM(text),0,240) as text
 
   from
 
   from
 
       dba_errors
 
       dba_errors
Ligne 1 847 : Ligne 1 842 :
 
SELECT count(n) into cnt_err
 
SELECT count(n) into cnt_err
 
   from (
 
   from (
       select sequence n, owner,name, type,line, lpad(' ',position-1,' ')||'<b>'||substr(TRIM(text),0,240)||'</b></td></tr>' text -- Num erreur PL/SQL
+
       select sequence n, owner,name, type,line, (position-1)||'</td><td bgcolor="LIGHTBLUE">'||text||'</td></tr>' text -- Num erreur PL/SQL
      from err
+
        from err
 
       union all
 
       union all
       select distinct -1 n, owner, name, type, line, type||' '||owner||'.'||name||' line '||line||'<br/>' --Num ligne
+
       select distinct -1 n, owner, name, type, line, '<b>'||type||' '||owner||'.'||name||' line '||line||'</b><br/>' --Num ligne
 
         from err
 
         from err
 
       union all
 
       union all
Ligne 1 873 : Ligne 1 868 :
 
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>
 
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 "&"
 
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');
+
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;
 
DECLARE cnt_obj number := 0;
Ligne 1 900 : Ligne 1 895 :
 
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>
 
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 "&"
 
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>'
+
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
 
from dba_segments
 
where (segment_type like 'TABLE%' OR segment_type like 'INDEX%' OR segment_type like 'LOB%')
 
where (segment_type like 'TABLE%' OR segment_type like 'INDEX%' OR segment_type like 'LOB%')
Ligne 2 012 : Ligne 2 007 :
 
set define "&"
 
set define "&"
  
select '<tr>','<td bgcolor="LIGHTBLUE">'||username||'</td>','<td bgcolor="LIGHTBLUE">'||default_tablespace||'</td>', '<td bgcolor="LIGHTBLUE">'||temporary_tablespace||'</td>','</tr>'
+
select '<tr>','<td bgcolor="LIGHTBLUE">',username,'</td>','<td bgcolor="LIGHTBLUE">',default_tablespace,'</td>', '<td bgcolor="LIGHTBLUE">',temporary_tablespace,'</td>','</tr>'
 
from dba_users
 
from dba_users
 
order by username;
 
order by username;
Ligne 2 033 : Ligne 2 028 :
 
prompt  
 
prompt  
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">'||username||'</td>','</tr>'
+
select '<tr>','<td bgcolor="LIGHTBLUE">',username,'</td>','</tr>'
 
from dba_users
 
from dba_users
 
where username not in &sysusers and username not in &exusers;
 
where username not in &sysusers and username not in &exusers;
Ligne 2 047 : Ligne 2 042 :
 
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>
 
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 "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">'||owner||'</td>', '<td bgcolor="LIGHTBLUE">Tables</td>','<td bgcolor="LIGHTBLUE">'||count(*)||'</td>','</tr>' TOTAL from dba_tables
+
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'
 
where tablespace_name = 'SYSTEM'
 
and owner not in &sysusers and owner not in &exusers
 
and owner not in &sysusers and owner not in &exusers
 
group by owner;
 
group by owner;
 
-- Indexes
 
-- Indexes
select '<tr>','<td bgcolor="LIGHTBLUE">'||owner||'</td>', '<td bgcolor="LIGHTBLUE">Indexes</td>','<td bgcolor="LIGHTBLUE">'||count(*)||'</td>','</tr>' TOTAL from dba_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'
 
where tablespace_name = 'SYSTEM'
 
and owner not in &sysusers and owner not in &exusers
 
and owner not in &sysusers and owner not in &exusers
Ligne 2 084 : Ligne 2 079 :
 
prompt <tr><td width=15%><b>Propri&eacute;taire</b></td><td width=15%><b>Tablespace</b></td><td width=15%><b>Nombre d'objets</b></td></tr>
 
prompt <tr><td width=15%><b>Propri&eacute;taire</b></td><td width=15%><b>Tablespace</b></td><td width=15%><b>Nombre d'objets</b></td></tr>
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">'||a.owner||'</td>', '<td bgcolor="LIGHTBLUE">'||a.tablespace_name||'</td>', '<td bgcolor="LIGHTBLUE">'||count(a.table_name)||'</td>','</tr>'
+
select '<tr>','<td bgcolor="LIGHTBLUE">',a.owner,'</td>', '<td bgcolor="LIGHTBLUE">',a.tablespace_name,'</td>', '<td bgcolor="LIGHTBLUE">',count(a.table_name),'</td>','</tr>'
 
from dba_tables a, dba_indexes b
 
from dba_tables a, dba_indexes b
 
where a.tablespace_name=b.tablespace_name
 
where a.tablespace_name=b.tablespace_name
Ligne 2 117 : Ligne 2 112 :
 
prompt  
 
prompt  
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">'||username||'</td>', decode(granted_role,NULL,'<td bgcolor="LIGHTBLUE">-</td>','<td bgcolor="LIGHTBLUE">'||granted_role||'</td>'),'</tr>'
+
select '<tr>','<td bgcolor="LIGHTBLUE">',username,'</td>', decode(granted_role,NULL,'<td bgcolor="LIGHTGREY"></td>','<td bgcolor="LIGHTBLUE">'||granted_role||'</td>'),'</tr>'
 
from dba_users, dba_role_privs
 
from dba_users, dba_role_privs
 
where username not in &sysusers and username not in &exusers
 
where username not in &sysusers and username not in &exusers
Ligne 2 133 : Ligne 2 128 :
 
prompt  
 
prompt  
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">'||username||'</td>','</tr>' from dba_users
+
select '<tr>','<td bgcolor="LIGHTBLUE">',username,'</td>','</tr>' from dba_users
 
where username not in (select owner from dba_segments)
 
where username not in (select owner from dba_segments)
 
and username not in &sysusers and username not in &exusers;
 
and username not in &sysusers and username not in &exusers;
Ligne 2 158 : Ligne 2 153 :
 
prompt  
 
prompt  
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">'||t.owner||'</td>',
+
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(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(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(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>'
+
'<td bgcolor="LIGHTBLUE" align=right>',to_char(decode(o.autres,NULL,0,o.autres),'99G999G990'),'</td>','</tr>'
 
from  (select owner, count(*) total
 
from  (select owner, count(*) total
 
       from dba_segments
 
       from dba_segments
Ligne 2 194 : Ligne 2 189 :
 
prompt  
 
prompt  
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">'||t.owner||'</td>',
+
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(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(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(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>'
+
'<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  (select owner, round(sum(bytes)/(1024*1024),2) total
 
       from dba_segments
 
       from dba_segments
Ligne 2 230 : Ligne 2 225 :
 
prompt  
 
prompt  
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">'||owner||'</td>','<td bgcolor="LIGHTBLUE">'||DB_LINK||'</td>','<td bgcolor="LIGHTBLUE">'||USERNAME||'</td>',
+
select '<tr>','<td bgcolor="LIGHTBLUE">',owner,'</td>','<td bgcolor="LIGHTBLUE">',DB_LINK,'</td>','<td bgcolor="LIGHTBLUE">',USERNAME,'</td>',
       '<td bgcolor="LIGHTBLUE">'||HOST||'</td>','</tr>'
+
       '<td bgcolor="LIGHTBLUE">',HOST,'</td>','</tr>'
 
from dba_db_links
 
from dba_db_links
 
order by OWNER,DB_LINK;
 
order by OWNER,DB_LINK;
Ligne 2 254 : Ligne 2 249 :
 
prompt  
 
prompt  
 
set define "&"
 
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">'||owner||'</td>', '<td bgcolor="LIGHTBLUE">'||synonym_name||'</td>', '<td bgcolor="LIGHTBLUE">'||table_owner||'</td>',
+
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>'
+
       '<td bgcolor="LIGHTBLUE">',table_name,'</td>','</tr>'
 
from dba_synonyms
 
from dba_synonyms
 
where table_owner not in &sysusers and table_owner not in &exusers;
 
where table_owner not in &sysusers and table_owner not in &exusers;

Version du 4 novembre 2015 à 16:28

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édiée).

Voir aussi le script d'audit MySQL

-- AUDIT BASES ORACLE
-- v3.1
-- Compatible Oracle 9i, 10g, 11g
-- FSoyer 2005-2015
-- Changelog :
-- 2005 v.1.0 Creation du script, regroupement d'operations manuelles repetitives.
--            Generation de rapports format TXT
-- 12/2006 v1.1 Creation table HISTAUDIT et requetes associees pour comparaisons entre 2 audits
-- 11/2008 v1.2 Separation en 2 fichiers 1=audit de perfs 2=environnement schemas
-- 02/2009 v2.0 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
-- 10/2010 Mise en paramètre des listes de USERS sysusers et exusers
-- 11/2010 Ajout résultat NLS_PARAMETERS
-- ... Petites modifs diverses non documentées pendant un certain temps ...
-- 03/2013 v2.1 Ajout file efficiency
-- 06/2013 file efficiency ne donne pas d'info pertinente. Remplace par detection des FULL SCANs
-- 08/2013 Ajout vue V$SGAINFO plutôt que simplement V$SGA pour les bases >=10g
-- 09/2013 Ajout moyenne temps entre 2 switchs redo logs. Le min et max ne donnent pas d'info pertinente.
-- 06/2014 suppression stats UNDO (gestion automatique depuis 10g)
-- 09/2014 v2.2 Regroupement tableaux volumetrie tablespaces et diff de tailles depuis dernier audit
-- 12/2014 Prendre date du jour si premier audit (pas de date d'audit précédent) pour afficher les tableaux à zéro
-- 12/2014 tablespace UNDO placé en fin de tableau volumétrie et suppression calcul diff taille depuis dernier audit
-- 02/2015 ajout affichage dba_errors+dba_sources
-- 04/2015 corr. bug histaudit type_obj='AUT' dupliqué si script lancé plusieurs fois.
--         corr. bug "erreurs sur objet" limit champ dba_errors."text" à 240 car. pour concatenations.
-- 07/2015 v3.0.1 affichage tablespace de la table HISTAUDIT (SYSTEM ou TOOLS)
--	       corr. bug div/0 si executions=0 dans v$sqlarea
-- 08/2015 v3.0.2 affichage des valeurs de divers paramètres d'initialisation.
--	   corr. bug total segments (TABLE% et INDEX%). Incohérence entre les sommes conservées dans Histaudit et les tailles réelles.
-- 09/2015 v3.0.3 ajout version et parametres d'init dans histaudit. Test si version ou paramètre a changé depuis dernier audit
--         v3.0.4 corr bug affichage version; ajout liste des patchs appliqués
-- 11/2015 v3.1 Nettoyage du code, suppression de lignes obsolètes, suppressions de concatenations par "||",
--         correction bug "ORA-01489" dans l'affichage des objets en erreur.
--
-- ******  UTILISATION  ******
-- ****** IMPORTANT : le script doit etre lance par sqlplus, en tant que SYSTEM ******
-- ****** IMPORTANT : un tablespace "TOOLS" doit exister dans la base pour la table d'audit ******
-- TNS : sqlplus system/manager@ORCL /repertoire/audit_complet_html > audit.html
-- Easyconnect : sqlplus system/manager@//server_oracle:1521/ORCL /repertoire/audit_complet_html > audit.html
--
-- Exemple, sous linux, en utilisant des variables et Easyconnect :
-- SQLP=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/scripts/sqlplus.sh
-- AUDIT_SCRIPT=/scripts/Audit_Oracle/audit_complet_html
-- CONNEXION='//server_oracle:1521/ORCL'
-- RAPPORT=audit.html
-- $SQLP system/manager@$CONNEXION @$AUDIT_SCRIPT > $RAPPORT
--
-- Pour info, script sqlplus.sh adapté pour linux (UTF8 et coupures de lignes)
-- nécessite l'outil rlwrap
--     #!/bin/bash
--     export LD_LIBRARY_PATH=/<rép. Oracle Client>/OraHome_1
--     export ORACLE_HOME=/<rép. Oracle Client>/OraHome_1
--     export EDITOR=vi
--     NLS_LANG=FRENCH_FRANCE.UTF8 rlwrap -m $ORACLE_HOME/bin/sqlplus $1 $2 $3 $4 $5
--
-- ******  NOTES / TIPS  ******
-- * 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 : pour 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
-- * TIP : trouver si la version est inférieure ou supérieure/égale à 10 :
-- select decode(sign(to_number(substr(VERSION,1,instr(VERSION,'.')-1))-10), -1, 'INF', 'SUP-EQ') from product_component_version where PRODUCT like 'Oracle%';
--
-- ******  BUGS CONNUS  ******
-- Volumétrie de tablespaces : si les fichiers mixent autoextend et no autoextend, affichage en doublon.
--      A valider : peut-être idem si les fichiers mixent standard et bigfile, à retester.
--      A valider : le calcul de l'alerte avec Couleurlimite n'est peut-être pas bon non plus dans ces cas.
-- Incompatibilité de certaines requêtes avec des versions < 10. Le script va quand même au bout.
-- 
-- ******   TODO LIST   ******
-- AMELIORATIONS GLOBALES (AU SCRIPT)
-------------------------------------
-- TODO : remplacer les "N/A" par un bgcolor=LIGHTGREY
-- TODO : UTILISER DES VARIABLES EN DEBUT DE SCRIPT POUR LES SEUILS D'ALERTES ?
-- TODO : Supprimer l'affichage dans la console pendant l'exécution du script, n'afficher que des infos de type "audit de la base XXX" avec dbms_output.
-- TODO : Faire une table des matières (ancres HTML). Frames ou menu javascript ?
-- TODO : Remplacer les fonctions stockées de colorisation PL/SQL par du javascript ?
--        Javascript inséré systématiquement en haut du fichier HTML produit, réutilisable dans les requêtes ?
--
-- AMELIORATIONS LOCALES (AUX SECTIONS)
---------------------------------------
-- Histaudit :
-- TODO : nettoyer historique > 1 an
-- Host :
-- BUG : la table v$osstat n'a pas toujours la stat "NUM_CPU_CORES". ligne vide renvoyée. A voir !
-- Versions :
-- TODO : Ajouter la liste des patches (select * from sys.registry$history;)
-- Liste des tablespaces et fichiers :
-- Mettre en ORANGE les nouveaux tbs et fichiers, par rapport à la liste de HISTAUDIT précédente
-- Volumetrie :
-- TODO : Afficher un graphe evolution volumétrie (attention aux creations de fichiers entre deux audits)
--        comment faire en pur HTML, sans PHP ou autre ?
-- TODO : coloriser les requêtes gourmandes qui prennent plus de XXX microsec ou XXX buffers
-- TODO : Etudier V$TEMPSEG_USAGE preformances tablespace temporaire
-- Memoire :
-- TODO : garder le nombre de sessions dans HISTAUDIT pour graphique d'evolution et moyenne ?
-- 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;
-- Requetes les plus gourmandes
-- TODO : Passer en orange les requêtes > 1s
-- Logs
-- Si le nombre de msg à afficher de l'alert.log est trop grand depuis le dernier audit, erreur :
--  declare * ERREUR à la ligne 1 : ORA-01653: impossible d'étendre la table SYSTEM.ALERT_LOG de 128 dans le tablespace TOOLS ORA-06512: à ligne 77
--    TODO : intercepter l'erreur pour n'afficher que les X dernières lignes ?
-- TODO : affichage : grouper les messages si identiques sur plusieurs lignes consécutives et afficher la somme pour limiter le nombre de ligne ?
-- Schemas :
-- TODO : Section SCHEMAS = Liste des grants par user et par role non systeme
-- TODO : Afficher N/A si 1er audit ou aucuns objets utilisateurs
-- 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 : "mises à jour automatiques des stats" : en 11i afficher état "auto optimizer stats collection" mais table n'existe pas en 10g (voir commentaire dans la section)
-- Segments :
-- TODO : Analyze de la fragmentation des indexes ?
-- Logs :
-- TODO : fusionner les lignes identiques successives (ex.: "Checkpoint not complete") en ajoutant juste le nombre d'occurences successives par jour
-- TODO : Mettre en orange des messages critiques ? (autres que checkpoint, arrêt normal de base, backups,...)
--
-- ******   A ETUDIER   ******
-- requêtes intéressante sur TEMP et SORT segments https://forums.oracle.com/thread/2527064
--
-- *********************************************** SCRIPT **************************************************
-- *************************************** Initialisation SQLPlus
set pages 999
set lines 200
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 quelques formats
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ", ";
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'FRENCH';

-- *************************************** Creation de fonctions
-- Fonction CouleurLimite renvoie :
-- ROUGE (si valeur en dehors de limite +/- plage en fonction de "superieur" (voir "VERT", calcul inverse)),
-- ORANGE (si valeur a l'interieur de la plage (limite +/- plage),
-- VERT (si valeur superieure à (limite + plage) ET "superieur" <= 0, valeur inferieure si "superieur" > 0)
-- soit :
--      | limite - plage      | valeur limite |      limite + plage |
-- si "superieur" <= 0 :
-- < ALERT (ROUGE) |                WARNING               | OK (VERT) >
-- si "superieur" > 0 :
-- < OK (VERT)     |                WARNING               | ALERT (ROUGE) >

CREATE OR REPLACE FUNCTION CouleurLimite (valeurAtester number, limite number, plage number, superieur int)
RETURN varchar2
IS
signe number;
BEGIN
if superieur <= 0 then -- on teste si la valeur tombe EN-DESSOUS de limite+/-plage (DOIT etre superieure)
   if valeurAtester < limite then --  deja inferieure a limite, mais est-on encore dans limite-plage ?
      select SIGN(valeurAtester-(limite-(plage))) into signe from dual;
      if signe < 0 then -- la valeur a tester est inferieure a limite-plage : ALERT
	 return '#FF0000'; --ROUGE
      else -- la valeur a tester est entre limite-plage et limite : WARN
	 return '#FF9900'; --ORANGE
      end if;
   else --  superieure a limite, mais est-on deja dans la limite+plage ?
      select SIGN(valeurAtester-(limite+(plage))) into signe from dual;
      if signe > 0 then -- la valeur a tester est superieure a limite+plage : OK
	 return '#33FF33';--VERT
      else -- la valeur a tester est deja tombee dans limite+plage : WARN
	 return '#FF9900';--ORANGE
      end if;
   end if;
else -- on teste si la valeur DEPASSE limite+/-plage (DOIT etre inferieure)
   if valeurAtester >= limite then -- on est deja au-dessus de la limite, est-on encore dans limite+plage ?
      select SIGN(valeurAtester-(limite+(plage))) into signe from dual;
      if signe > 0 then -- on a depasse limite+plage : ALERT
	 return '#FF0000'; --ROUGE
      else -- on est encore dans la plage : WARN
	 return '#FF9900'; --ORANGE
      end if;
   else -- on est encore en-dessous de limite, mais est-on deja dans la plage ?
      select SIGN(valeurAtester-(limite-(plage))) into signe from dual;
      if signe < 0 then -- on est encore en-dessous : OK
	 return '#33FF33';--VERT
      else -- on est deja dans la plage : WARN
	 return '#FF9900';--ORANGE
      end if;
   end if;
end if;
END;
/

-- *************************************** Variables et constantes
define logfile = ORACLE
define envfile = env
-- ATTENTION : AUCUN ESPACE DANS LES LISTES, SINON LA VARIABLE EST TRONQUEE !
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')
-- Icones (base64)
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

-- *************************************** Entete
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">
prompt <table border=1 width=100% bgcolor="WHITE">
prompt <tr><td bgcolor="#3399CC" align=center> 
prompt <font color=WHITE size=+2><b>Audit &dbname (&hstname)
select ' du ',to_char(to_date(sysdate),'DD-MON-YYYY',N'NLS_DATE_LANGUAGE = AMERICAN'),'</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>

-- *************************************** 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;
   tabtools number;
   colmodif number;
   colval 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';
   select count(column_name) into colval from dba_tab_columns
   where table_name='HISTAUDIT' AND owner='SYSTEM' AND column_name='VALEUR';
   select count(tablespace_name) into tabtools from dba_tablespaces
   where tablespace_name='TOOLS';
   IF tabhist = 0 THEN
      IF tabtools = 0 THEN
         dbms_output.put_line('<td bgcolor="#FF0000">Creation table HISTAUDIT tablespace SYSTEM...<br>');
--         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');
         EXECUTE IMMEDIATE 'create table SYSTEM.histaudit
                           (date_aud  date,
                            type_obj varchar2(5),
                            obj_name varchar2(30),
                            total number,
                            utilis number,
                            VALEUR varchar2(255))';
      ELSE
         dbms_output.put_line('<td bgcolor="#33FF33">Creation table HISTAUDIT tablespace TOOLS...<br>');
         EXECUTE IMMEDIATE 'create table SYSTEM.histaudit
                           (date_aud  date,
                            type_obj varchar2(5),
                            obj_name varchar2(30),
                            total number,
                            utilis number,
                            VALEUR varchar2(255))
                          TABLESPACE TOOLS';
      END IF;
   ELSE
      IF colval=0 THEN
         IF colmodif>0 THEN
            EXECUTE IMMEDIATE 'alter table system.histaudit drop column MODIFIED';
         END IF;
         EXECUTE IMMEDIATE 'alter table system.histaudit add VALEUR varchar2(255)';
         dbms_output.put_line('<td bgcolor="#FF0000">Modification table HISTAUDIT (col VALEUR)');
      ELSE
         dbms_output.put_line('<td bgcolor="#33FF33">Table HISTAUDIT existante ');
      END IF;
      IF tabtools = 0 THEN
         dbms_output.put_line('(tablespace SYSTEM)<br>');
      ELSE
         dbms_output.put_line('(tablespace TOOLS)<br>');
      END IF;
   END IF;
END;
/
WHENEVER sqlerror CONTINUE;

prompt </td></tr>
set define off
prompt <tr><td width=20%><b>Pr&eacute;c&eacute;dent 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);
set define "&"

variable last_audit varchar2(20);
begin
      select decode(max(to_date(date_aud)),'','N/A',to_char(max(to_date(date_aud)),'DD-MON-YYYY',N'NLS_DATE_LANGUAGE = AMERICAN')) into :last_audit from system.histaudit
      where to_date(date_aud) < trunc(sysdate);
end;
/
print last_audit
prompt </td></tr></table><br>

-- *************************************** Hote
prompt <table border=1 width=100% bgcolor="WHITE">
set define off
prompt <tr><td bgcolor="#3399CC" align=center colspan=5><font color="WHITE"><b>H&ocirc;te</b></font></td></tr>
prompt <tr><td bgcolor="LIGHTBLUE"><b>Host</b></td><td bgcolor="LIGHTBLUE"><b>OS</b></td><td bgcolor="LIGHTBLUE"><b>CPUs</b></td><td bgcolor="LIGHTBLUE"><b>Cores/CPU</b></td><td bgcolor="LIGHTBLUE"><b>RAM</b></td>
set define "&"
prompt <tr><td bgcolor="LIGHTBLUE" width=20%>&hstname</td>
select '<td bgcolor="LIGHTBLUE" width=20%>',PLATFORM_NAME,'</td><td bgcolor="LIGHTBLUE" width=20%>',cpu.VALUE,'</td><td bgcolor="LIGHTBLUE" width=20%>',core.VALUE,'</td><td bgcolor="LIGHTBLUE" width=20% align=right>', to_char(round(ram.VALUE/(1024*1024),2),'99G999G990D00'), ' Mo'
from v$database, v$osstat cpu, v$osstat core, v$osstat ram
where cpu.STAT_NAME = 'NUM_CPUS'
and core.STAT_NAME = 'NUM_CPU_CORES'
and ram.STAT_NAME = 'PHYSICAL_MEMORY_BYTES';

prompt </td></tr>
prompt </table>
prompt <br>

-- *************************************** Versions
delete from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='VERS';
prompt <table border=1 width=100% bgcolor="WHITE">
prompt <tr><td bgcolor="#3399CC" align=center colspan=5><font color="WHITE"><b>Versions</b></font></td></tr>
set define off

-- Add <td> if no rows are returned (first audit)
select decode(count(valeur), 0, '<tr><td bgcolor="LIGHTBLUE" colspan=5>')
 from histaudit
 where obj_name like 'Oracle Database%';
-- else change bg color if version has changed
select decode(banner, valeur, '<tr><td bgcolor="LIGHTBLUE" colspan=5>','<tr><td bgcolor="#FF0000" colspan=5>Version modifi&eacute;e depuis le dernier audit<br><br>') from v$version,histaudit where banner like 'Oracle Database%'
 and obj_name like 'Oracle Database%'
 and to_date(date_aud) = (select max(to_date(date_aud)) from histaudit where type_obj = 'VERS');

select banner,'<br>' from v$version;
set define "&"
prompt </td></tr>

-- *************************************** MISE A JOUR TABLE HISTORIQUE (VERSION)
insert into system.histaudit (
select sysdate, 'VERS', 'Oracle Database', 0, 0, banner
from v$version
  where banner like 'Oracle Database%');

-- *************************************** Patchs installes
set define off
prompt <tr><td bgcolor="#3399CC" align=center colspan=5><font color="WHITE"><b>Patchs install&eacute;s</b></font></td></tr>
prompt <tr><td bgcolor="LIGHTBLUE"><b>Date</b></td><td bgcolor="LIGHTBLUE"><b>Action</b></td><td bgcolor="LIGHTBLUE"><b>Version</b></td><td bgcolor="LIGHTBLUE"><b>ID</b></td><td bgcolor="LIGHTBLUE"><b>Description</b></td>

select '<tr><td bgcolor="LIGHTBLUE">',to_char(ACTION_TIME,'DD/MM/YYYY'), '</td><td bgcolor="LIGHTBLUE">', ACTION, '</td><td bgcolor="LIGHTBLUE">', VERSION, '</td><td bgcolor="LIGHTBLUE">', ID, '</td><td bgcolor="LIGHTBLUE">', COMMENTS,'</td></tr>'
   from sys.registry$history
   order by 1;

DECLARE cnt_patch number := 0;
BEGIN
   select count(ACTION_TIME) into cnt_patch from sys.registry$history;
   if cnt_patch=0 then
      dbms_output.put_line('<tr><td bgcolor="LIGHTGREY">&nbsp;</td><td bgcolor="LIGHTGREY">&nbsp;</td><td bgcolor="LIGHTGREY">&nbsp;</td><td bgcolor="LIGHTGREY">&nbsp;</td><td bgcolor="LIGHTGREY">&nbsp;</td></tr>');
   end if;
end;
/

set define "&"

-- *************************************** Options installees
set define off
prompt <tr><td bgcolor="#3399CC" align=center colspan=5><font color="WHITE"><b>Options install&eacute;es</b></font></td></tr>
set define "&"
SELECT DISTINCT '<tr><td bgcolor="LIGHTBLUE" colspan=5>',PARAMETER,'</td>','</tr>' FROM V$OPTION where VALUE = 'TRUE' order by 1;
prompt </table>
prompt <br>

-- *************************************** NLS_PARAMETERS
prompt <table border=1 width=100% bgcolor="WHITE">
set define off
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Param&egrave;tres NLS Instance</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 <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Param&egrave;tres NLS database</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 nls_database_parameters;

-- *************************************** AUTRES PARAMETRES D'INIT
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><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="Les principaux param&egrave;tres d'initialisation sont indiqu&eacute;s dans les diff&eacute;rentes sections correspondant &agrave; leur champ d'action"></td>
prompt <td bgcolor="#3399CC" align=center><font color="WHITE"><b>Autres param&egrave;tres d'initialisation (instance)</b></font></td></tr></table></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">',name,'</td>','<td bgcolor="LIGHTBLUE">',value,'</td>','</tr>' from v$parameter where name in ('open_cursors','processes','compatible','remote_login_passwordfile','session','utl_file_dir','undo_retention') order by 1;

-- *************************************** Modifies lors du dernier audit ?
prompt <tr>
set define off
prompt <td><b>Param&egrave;tres modifi&eacute;s lors du dernier audit</b></td>
set define "&"

DECLARE cnt_init number := 0;
BEGIN
  select count(H1.obj_name) into cnt_init from system.histaudit H1, system.histaudit H2
  where H1.obj_name = H2.obj_name
  and H1.type_obj = 'INIT'
  and H2.type_obj = 'INIT'
  and H1.valeur <> H2.valeur
  and trunc(to_date(H1.date_aud)) = trunc(sysdate)
  and to_date(H2.date_aud) = (select max(to_date(date_aud)) from system.histaudit
                           where to_date(date_aud) < trunc(sysdate));
   if cnt_init=0 then
      dbms_output.put_line('<td bgcolor="#33FF33">AUCUN');
   else
      dbms_output.put_line('<td bgcolor="ORANGE">');
   end if;
end;
/

select H1.obj_name, ' (', H2.valeur, ' -> ', H1.valeur, ')<br>'
from system.histaudit H1, system.histaudit H2
  where H1.obj_name = H2.obj_name
  and H1.type_obj = 'INIT'
  and H2.type_obj = 'INIT'
  and H1.valeur <> H2.valeur
  and trunc(to_date(H1.date_aud)) = trunc(sysdate)
  and to_date(H2.date_aud) = (select max(to_date(date_aud)) from system.histaudit
                           where to_date(date_aud) < trunc(sysdate));
prompt </td></tr></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>

-- *************************************** Informations Generales
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>
-- Archive log mode
prompt <tr><td width=20%><b>Archive log mode</b></td>
select decode(log_mode,'ARCHIVELOG','<td bgcolor="#33FF33">',log_mode,'<br>','<td bgcolor="#FF9900">',log_mode,'<br>') from v$database;
prompt </td></tr>
set define off
prompt <tr><td width=20%><b>Archive log destination</b></td><td bgcolor="LIGHTBLUE">
select distinct decode(d.log_mode,'ARCHIVELOG',p.name,' = ',p.value,'<br/>', 'N/A') from v$database d,v$parameter p where (p.name like 'log_archive_dest_%' or p.name = 'log_archive_dest') and p.name not like '%state%' and p.value is not NULL;

DECLARE cnt_dest number := 0;
BEGIN
   select count(name) into cnt_dest from v$parameter
   where (name like 'log_archive_dest_%' or name = 'log_archive_dest') and name not like '%state%' and value is not NULL;
   if cnt_dest=0 then
      dbms_output.put_line('N/A');
   end if;
end;
/

prompt </td></tr>
prompt <tr><td width=20%><b>Archive log format</b></td><td bgcolor="LIGHTBLUE">
select decode(d.log_mode,'ARCHIVELOG',p.value, 'N/A') from v$database d,v$parameter p where p.name like 'log_archive_format';
prompt </td></tr></table>
prompt <br>
set define "&"

-- *************************************** SPFILE ou init.ora ?
set define off
prompt <table border=1 width=100% bgcolor="WHITE">
prompt <tr><td bgcolor="#3399CC" align=center 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="Si l'instance est lanc&eacute;e avec un fichier SPFILE le chemin de celui-ci est affich&eacute;. Dans le cas contraire on affiche seulement 'NON' car le chemin du fichier texte init.ora n'est pas disponible dans les tables syst&egrave;me."></td>
prompt <td align=center><font color="WHITE"><b>Initialisation : spfile ou init.ora ?</b></font></td></tr></table></td></tr>
prompt <tr><td width=15%><b>SPFILE</b></td>
SELECT decode(value,'','<td bgcolor="#FF0000" width=15%>NON</td>','<td bgcolor="#33FF33" width=15%>OUI</td>'), decode(value,'','<td>N/A</td></tr>','<td>',value,'</td></tr>') FROM v$parameter WHERE name like 'spfile' ;
set define "&"

prompt </table>
prompt <br>

-- *************************************** MISE A JOUR TABLE HISTORIQUE (PARAMETRES INIT)
delete from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='INIT';
insert into system.histaudit (
select sysdate, 'INIT', substr(name,1,30), 0, 0, value
from v$parameter
  where ISDEFAULT='FALSE');

-- *************************************** 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 df.tablespace_name,
             round(sum(df.bytes)/(1024*1024),2) total
      from dba_data_files df, dba_tablespaces dt
      where df.tablespace_name = dt.tablespace_name
      and dt.contents not in ('UNDO')
      group by df.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 df.tablespace_name,
             round(sum(df.bytes)/(1024*1024),2) total
      from dba_temp_files df, dba_tablespaces dt
      where df.tablespace_name = dt.tablespace_name
      and dt.contents in ('UNDO')
      group by df.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));

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));
delete from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='AUT';
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">
set define off
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>Utilisé</b></td><td><b>Autoext.</b></td><td><b>Next</b></td><td><b>MaxSize</b></td></tr>
prompt <tr><td><b>Tablespace</b></td><td><b>Fichier</b></td><td><b>Taille (Mo)</b></td><td><b>Autoext.</b></td><td><b>Next</b></td><td><b>MaxSize</b></td></tr>
set define "&"

select '<tr>','<td bgcolor="LIGHTBLUE">'||df.TABLESPACE_NAME||'</td>','<td bgcolor="LIGHTBLUE">'||df.FILE_NAME||'</td>',
'<td bgcolor="'||decode (CONTENTS,'UNDO','#33FF33',decode(autoextensible,'NO','#33FF33',CouleurLimite(sum(df.blocks)*&dbloc,(sum(df.maxbytes)-(sum(df.maxbytes)*0.20)),(sum(df.maxbytes)-(sum(df.maxbytes)*0.20))*0.10,1)))||'" align=right>'||decode(round(sum(df.bytes)/(1024*1024),2),NULL,to_char('0','S99G999G990D00'),to_char(round(sum(df.bytes)/(1024*1024),2),'99G999G990D00'))||'</td>', decode(autoextensible,'NO','<td bgcolor="#FF9900" 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),'99G999G990')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(BIGFILE,'YES',round(sum(df.maxbytes)/(1024*1024*1024),2),round(sum(df.maxbytes)/(1024*1024),2)),'99G999G990')||'</td>', '</tr>'
from DBA_DATA_FILES df, DBA_TABLESPACES dt
where df.tablespace_name=dt.tablespace_name(+)
group by df.tablespace_name, df.file_name, autoextensible, contents, bigfile
UNION
select '<tr>','<td bgcolor="LIGHTBLUE">'||df.TABLESPACE_NAME||'</td>','<td bgcolor="LIGHTBLUE">'||FILE_NAME||'</td>',
'<td bgcolor="#33FF33" align=right>'||decode(round(sum(df.blocks)*&dbloc/(1024*1024),2),NULL,to_char('0','S99G999G990D00'),to_char(round(sum(df.blocks)*&dbloc/(1024*1024),2),'99G999G990D00'))||'</td>', decode(autoextensible,'NO','<td bgcolor="#FF9900" 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),'99G999G990')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(decode(BIGFILE,'YES',round(sum(df.maxbytes)/(1024*1024*1024),2),round(sum(df.maxbytes)/(1024*1024),2)),'99G999G990')||'</td>', '</tr>'
from DBA_TEMP_FILES df, DBA_TABLESPACES dt
where df.tablespace_name=dt.tablespace_name(+)
group by df.tablespace_name,df.file_name, autoextensible, bigfile
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=10><font color="WHITE"><b>Volum&eacute;trie actuelle + diff&eacute;rence de tailles depuis le dernier audit (
print last_audit
prompt )</b></font></td></tr>

prompt <tr><td><b>Tablespace</b></td><td><b>Bigfile ou<br/>Temporaire</b></td><td><b>Contenu</b></td><td><b>Statut</b></td><td width=10%><b>Taille max. totale (Mo)</b></td><td width=10%><b>Total actuel (Mo)</b></td><td width=10%><b>Utilis&eacute; (Mo)</b></td><td width=10%><b>Libre actuel (Mo)</b></td><td width=10%><b>Total sur disque depuis dernier audit (Mo)</b></td><td width=10%><b>Utilis&eacute; depuis dernier audit (Mo)</b></td></tr>

set define "&"
-- TABLESPACES DATAS
select '<tr>','<td bgcolor="LIGHTBLUE">',t.tablespace_name,'</td>', '<td bgcolor="',decode(BIGFILE,'YES','#FF9900','LIGHTBLUE'),'" align=center>',maxt.bigfile,'</td>', '<td bgcolor="LIGHTBLUE">',maxt.contents,'</td>', decode(maxt.status,'ONLINE','<td bgcolor="LIGHTBLUE">','<td bgcolor="#FF0000">'),maxt.status,'</td>',
       '<td bgcolor="LIGHTBLUE" align=right>',decode(t.autoextensible,'NO',decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(t.total,'99G999G990D00')),decode(maxt.maxtotal,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(maxt.maxtotal,'99G999G990D00'))),'</td>' TOTAL,
       '<td bgcolor="LIGHTBLUE" align=right>',decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(t.total,'99G999G990D00')),'</td>' TOTAL_CURRENT,
       '<td bgcolor="LIGHTBLUE" align=right>',decode(u.utilise,'','0,00',to_char(u.utilise,'99G999G990D00')),'</td>' UTILISE,
       '<td bgcolor="',decode(t.autoextensible,'NO',decode(u.utilise,'', '#33FF33',CouleurLimite(u.utilise,t.total-(t.total*0.20),t.total*0.10,1)),decode(u.utilise,'', '#33FF33', CouleurLimite(u.utilise,maxt.maxtotal-(maxt.maxtotal*0.20),maxt.maxtotal*0.10,1))),'" align=right>',decode(l.libre,NULL,to_char(t.total-(decode(u.utilise,'',0,u.utilise)),'99G999G990D00'),to_char(l.libre,'99G999G990D00')),'</td>' LIBRE,
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 tablespace_name, autoextensible,
             round(sum(bytes)/(1024*1024),2) total
      from dba_data_files
      group by tablespace_name, autoextensible) t,
     (select df.tablespace_name, df.autoextensible, dt.contents, dt.status,
             bigfile,
             decode(BIGFILE,'YES',round(sum(df.maxbytes)/(1024*1024*1024),2),round(sum(df.maxbytes)/(1024*1024),2)) maxtotal
      from dba_data_files df, dba_tablespaces dt
      where df.tablespace_name=dt.tablespace_name(+)
      group by df.tablespace_name, df.autoextensible, dt.contents, dt.status, BIGFILE) maxt,
     (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,
      (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 decode(max(to_date(date_aud)),NULL,trunc(sysdate),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 t.tablespace_name=u.tablespace_name(+)
and u.tablespace_name=l.tablespace_name(+)
and t.tablespace_name=maxt.tablespace_name(+)
and a.obj_name=h.obj_name
and a.obj_name=t.tablespace_name
and maxt.contents not in ('UNDO')
order by t.tablespace_name;

-- TABLESPACE UNDO
select '<tr>','<td bgcolor="LIGHTBLUE">',t.tablespace_name,'</td>' Tablespace, '<td bgcolor="',decode(BIGFILE,'YES','#FF9900','LIGHTBLUE'),'" align=center>',maxt.bigfile,'</td>', '<td bgcolor="LIGHTBLUE">',maxt.contents,'</td>', decode(maxt.status,'ONLINE','<td bgcolor="LIGHTBLUE">',maxt.status,'</td>','<td bgcolor="#FF0000">',maxt.status,'</td>'),
       '<td bgcolor="LIGHTBLUE" align=right>',decode(t.autoextensible,'NO',decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(t.total,'99G999G990D00')),decode(maxt.maxtotal,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(maxt.maxtotal,'99G999G990D00'))),'</td>' TOTAL,
       '<td bgcolor="LIGHTBLUE" align=right>',decode(t.total,'',to_char(round(l.libre,0),'99G999G990D00'),to_char(t.total,'99G999G990D00')),'</td>' TOTAL_CURRENT,
       '<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,'<td bgcolor="LIGHTGREY" align=center><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td><td bgcolor="LIGHTGREY" align=center><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td></tr>'
from (select tablespace_name, autoextensible,
             round(sum(bytes)/(1024*1024),2) total
      from dba_data_files
      group by tablespace_name, autoextensible) t,
     (select df.tablespace_name, df.autoextensible, dt.contents, dt.status,
             bigfile,
             decode(BIGFILE,'YES',round(sum(df.maxbytes)/(1024*1024*1024),2),round(sum(df.maxbytes)/(1024*1024),2)) maxtotal
      from dba_data_files df, dba_tablespaces dt
      where df.tablespace_name=dt.tablespace_name(+)
      group by df.tablespace_name, df.autoextensible, dt.contents, dt.status, BIGFILE) maxt,
     (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(+)
and t.tablespace_name=maxt.tablespace_name(+)
and maxt.contents in ('UNDO');

-- TABLESPACE TEMP
select '<tr>','<td bgcolor="LIGHTBLUE">',t.tablespace_name,'</td>','<td bgcolor="LIGHTGREY" align=center>TEMP</td>' as bigfile, '<td bgcolor="LIGHTBLUE">',contents,'</td>', decode(status,'ONLINE','<td bgcolor="LIGHTBLUE">',status,'</td>','<td bgcolor="#FF0000">',status,'</td>'),
         '<td bgcolor="LIGHTBLUE" align=right>',to_char(total,'99G999G990D00'),'</td>' as total, 
         '<td bgcolor="LIGHTBLUE" align=right>',to_char(total,'99G999G990D00'),'</td>' as fake, 
         '<td bgcolor="LIGHTBLUE" align=right>0,00</td>' as utilise,
         '<td bgcolor="LIGHTBLUE" align=right>',to_char(total,'99G999G990D00'),'</td>' as libre,'<td bgcolor="LIGHTGREY" align=center><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td><td bgcolor="LIGHTGREY" align=center><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td></tr>'
from (select df.tablespace_name, dt.contents, dt.status,
             round(sum(bytes)/(1024*1024),2) total
      from dba_temp_files df, dba_tablespaces dt
      where df.tablespace_name = dt.tablespace_name
      group by df.tablespace_name, dt.contents, dt.status) t;

select  '<tr>','<td bgcolor="WHITE" colspan=4>TOTAL</td>',
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',to_char(decode(dmty.total,'',0,dmty.total)+decode(dmtn.total,'',0,dmtn.total)+tt.total,'99G999G990D00'),'</b></font></td>' as maxtotal, 
        '<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
from (select round(sum(df.maxbytes)/(1024*1024*1024),2) total
      from dba_data_files df, dba_tablespaces dt
      where df.tablespace_name=dt.tablespace_name(+)
      and BIGFILE='YES'
      and dt.contents not in ('UNDO')) dmty,
     (select round(sum(df.maxbytes)/(1024*1024),2) total
      from dba_data_files df, dba_tablespaces dt
      where df.tablespace_name=dt.tablespace_name(+)
      and BIGFILE='NO'
      and dt.contents not in ('UNDO')) dmtn,
     (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, -- considere que temp est toujours 100% libre
     (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;
     
select '<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 colspan=4><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 decode(max(to_date(date_aud)),NULL,trunc(sysdate),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>

-- *************************************** 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 (
print last_audit
prompt )</b></td></tr>

set define "&"
select  '<tr>','<td bgcolor="LIGHTBLUE">TABLES</td>',
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',decode(a.total, NULL, '0,00', to_char(round(a.total,2),'99G999G990D00')),'</b></font></td>',
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',decode(a.total, NULL, to_char(round(-l.total,2),'S99G999G990D000'), to_char(round(a.total-l.total,2),'S99G999G990D00')),'</b></font></td>','</tr>'
from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments
	where segment_type like 'TABLE%'
        and owner not in &sysusers and owner not in &exusers) a,
(select * from system.histaudit
	where to_date(date_aud) like
	(select decode(max(to_date(date_aud)),NULL,trunc(sysdate),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(a.total, NULL, '0,00', to_char(round(a.total,2),'99G999G990D00')),'</b></font></td>', 
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',decode(a.total, NULL, to_char(round(-l.total,2),'S99G999G990D00'), to_char(round(a.total-l.total,2),'S99G999G990D00')),'</b></font></td>','</tr>'
from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments
	where segment_type like 'INDEX%'
        and owner not in &sysusers and owner not in &exusers) a,
(select * from system.histaudit
	where to_date(date_aud) like
	(select decode(max(to_date(date_aud)),NULL,trunc(sysdate),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 DISTINCT '<tr>','<td bgcolor="LIGHTBLUE">AUTRES (LOB SEGMENTS, LOB INDEXES, CLUSTERS,...)</td>',
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',decode(a.total, NULL, '0,00', to_char(round(a.total,2),'99G999G990D00')),'</b></font></td>', 
        '<td bgcolor="BLUE" align=right><font color="WHITE"><b>',decode(a.total, NULL, to_char(round(-l.total,2),'S99G999G990D00'), to_char(round(a.total-l.total,2),'S99G999G990D00')),'</b></font></td>','</tr>'
from (select round(sum(bytes)/(1024*1024),2) as 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) a,
(select * from system.histaudit
	where to_date(date_aud) like
	(select decode(max(to_date(date_aud)),NULL,trunc(sysdate),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>

-- *************************************** Mise à jour automatique des statistiques
-- 10g : 'GATHER_STATS_JOB' dans DBA_SCHEDULER_JOBS
-- 11g : 'MGMT_STATS_CONFIG_JOB' dans DBA_SCHEDULER_JOBS dit seulement que OCM est actif. Mais il faut aussi regarder dans DBA_AUTOTASK_WINDOW_CLIENTS (fenetres de lancement des taches auto) et DBA_AUTOTASK_CLIENT (jobs lancés).
-- Ces 2 tables n'existent pas en 10. Comment afficher "auto optimizer stats collection"  de DBA_AUTOTASK_CLIENT si version 11, plutot que simplement "MGMT_STATS_CONFIG_JOB" qui ne dit pas si les stats sont aussi "enabled" ?

prompt <table border=1 width=100% bgcolor="WHITE">
set define off
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Mise &agrave; jour automatique des statistiques</b></font></td></tr>
prompt <tr><td><b>JOB</b></td><td><b>Automatiques (O/N)</b></td></tr>
set define "&"
select  '<tr>','<td bgcolor="LIGHTBLUE" align=left>',JOB_NAME,'</td>','<td bgcolor="BLUE" align=right><font color="WHITE"><b>',STATE,'</b></font></td>','</tr>'
FROM DBA_SCHEDULER_JOBS 
WHERE JOB_NAME in ('GATHER_STATS_JOB','MGMT_STATS_CONFIG_JOB');
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=4><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=5%><b>Statut</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="',decode(f.status, 'STALE', 'ORANGE">', 'INVALID', '#FF0000">', '#33FF33">OK'),f.status,'</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 LOG
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</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#;

-- *************************************** temps moyen entre 2 switchs
select '<tr>','<td bgcolor="WHITE">Temps MOY. entre 2 switchs :</td>', '<td bgcolor="BLUE" align=right colspan=2><font color="WHITE">',to_char((sum(a.first_time-b.first_time)*24*3600)/count(a.first_time),'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 -- inutile depuis 10g gestion auto
--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%';
   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. Si segment header ou free list > 0 (multiplier les freelists en re-cr&eacute;ant la table avec nouveau param&egrave;tre). 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>

-- *************************************** Efficacite d'acces aux fichiers
-- EDIT 06/2013 : pas sur que ces informations servent vraiment... Il vaut mieux regarder les full scans
--prompt <table border=1 width=100% bgcolor="WHITE">
--set define off
--prompt <tr><td bgcolor="#3399CC" align=center colspan=4>
--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="L'efficacit&eacute; d'acc&egrave;s indique le rapport entre les lectures/&eacute;critures et la r&eacute;partition des donn&eacute;es sur disque. Un ratio trop bas indique des acc&eacute;s concurrents trop fr&eacute;quents, soit des index et tables m&eacute;lang&eacute;es, ou des tables non tri&eacute;es acc&eacute;d&eacute;es fr&eacute;quemment."></td>

--prompt <td align=center><font color="WHITE"><b>Efficacit&eacute; des acc&egrave;s aux fichiers</b></font></td></tr></table></td></tr>
--prompt <tr><td><b>Tablespace</b></td><td><b>Fichier</b></td><td><b>Reads / Writes</b></td><td><b>Efficacit&eacute; (%)</b></td></tr>
--set define "&"

--select
--'<tr>','<td bgcolor="LIGHTBLUE">',f.tablespace_name,'</td>',
--'<td bgcolor="LIGHTBLUE">',f.file_name,'</td>',
--'<td bgcolor="LIGHTBLUE" align=right>',(v.phyrds+v.phywrts),'</td>',
--'<td bgcolor="',CouleurLimite(ROUND(100*(v.phyrds+v.phywrts)/(v.phyblkrd+v.phyblkwrt),0),60,20,0),'" align=right>',TO_CHAR(DECODE(v.phyblkrd,0,null,ROUND(100*(v.phyrds+v.phywrts)/(v.phyblkrd+v.phyblkwrt),0))),'%</td>',
--'</tr>'
--from DBA_data_files f, v$filestat v
--where f.file_id=v.file#
--ORDER BY 1,file#;

--prompt </table><br>

-- *************************************** FULL SCANS
prompt <table border=1 width=100% bgcolor="WHITE">
set define off
prompt <tr><td bgcolor="#3399CC" align=center colspan=5>
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="La d&eacute;tection des FULL SCANS est faite par le rapport entre les requêtes de lectures et les lectures des donn&eacute;es sur disque (les blocs). Un ratio > 50% signifie qu'un petit nombre de requêtes lisent un grand nombre de blocs, ce qui indique que les fichiers sont lus en entier trop fr&eacute;quemment."></td>

prompt <td align=center><font color="WHITE"><b>D&eacute;tection des FULL SCAN sur disque</b></font></td></tr></table></td></tr>
prompt <tr><td><b>Tablespace</b></td><td><b>Fichier</b></td><td><b>Read requests</b></td><td><b>Blocks read</b></td><td><b>ratio (%)</b></td></tr>
set define "&"

select
'<tr>','<td bgcolor="LIGHTBLUE">',f.tablespace_name,'</td>',
'<td bgcolor="LIGHTBLUE">',f.file_name,'</td>',
'<td bgcolor="LIGHTBLUE" align=right>',v.phyrds,'</td>',
'<td bgcolor="LIGHTBLUE" align=right>',v.phyblkrd,'</td>',
'<td bgcolor="',CouleurLimite(ROUND(100*(v.phyrds/v.phyblkrd),0),20,5,0),'" align=right>',TO_CHAR(DECODE(v.phyblkrd,0,null,ROUND(100*(v.phyrds/v.phyblkrd),0))),'%</td>',
'</tr>'
from DBA_data_files f, v$filestat v
where f.file_id=v.file#
ORDER BY f.tablespace_name,v.file#;

prompt </table><br>

--TIPS : Pour trouver les tables souvent lues s&eacute;quentiellement (connexion SYS obligatoire !) :
--set head on
--set pages 0
--col object_name format a40
--col object_type format a15
--col owner format a15 
--SELECT o.object_name, o.object_type, o.owner
--FROM dba_objects o,x$bh x
--WHERE x.obj=o.object_id
--AND o.object_type='TABLE'
--AND sys.standard.bitand(x.flag,524288)>0
--AND o.owner<>'SYS'
--GROUP BY o.object_name,o.object_type,o.owner;

-- *************************************** 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,'999999990D00'),'</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
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(round(sum(s.bytes)/(1024*1024),2),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>Non initialis&eacute;</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 SPFILE</td>',
decode(to_char(round(p.value/(1024*1024),2)),
'','<td bgcolor="LIGHTBLUE" align=right>Non initialis&eacute;</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_target'
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="LIGHTBLUE">memory_max_target SPFILE</td>',
decode(to_char(round(p.value/(1024*1024),2)),
'','<td bgcolor="LIGHTBLUE" align=right>Non initialis&eacute;</td>','<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(p.value/(1024*1024),2),'99G999G990D00')||'</td>'),'</tr>' 
from v$spparameter p
where p.name = 'memory_max_target'
UNION ALL
select '<tr>','<td bgcolor="LIGHTBLUE">memory_max_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 = 'memory_max_target'
UNION ALL
select '<tr>','<td bgcolor="LIGHTBLUE">memory_target SPFILE</td>',
decode(to_char(round(p.value/(1024*1024),2)),
'','<td bgcolor="LIGHTBLUE" align=right>Non initialis&eacute;</td>','<td bgcolor="LIGHTBLUE" align=right>'||to_char(round(p.value/(1024*1024),2),'99G999G990D00')||'</td>'),'</tr>' 
from v$spparameter p
where p.name = 'memory_target'
UNION ALL
select '<tr>','<td bgcolor="LIGHTBLUE">memory_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 = 'memory_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');


-- *************************************** 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 (
print last_audit
prompt )</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>SGA Infos</b></font></td></tr>
prompt <tr><td><b>Nom</b></td><td><b>Valeur (Mo)</b></td></tr>
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>vue V$SGAINFO (>=10g)</b></font></td></tr>
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">',name,'</td>' NOM,'<td bgcolor="LIGHTBLUE" align=right>',to_char(round(bytes/(1024*1024),2),'99G999G990D00'),'</td>' total,'</tr>' from v$sgainfo;
-- Pour compatibilite avec 9i :
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>vue V$SGA (toutes versions)</b></font></td></tr>
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;

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,70,10,0),'" align=right>',round(gethitratio,2)*100,' %</td>',
'<td bgcolor="',CouleurLimite(round(pinhitratio,2)*100,70,10,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 (moyennes par ex&eacute;cution)</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>
SELECT '<tr>','<td bgcolor="LIGHTBLUE">',sqla.address,'</td>',
'<td bgcolor="LIGHTBLUE">',sqla.executions,'</td>',
'<td bgcolor="LIGHTBLUE">',round(sqla.sorts/sqla.executions,0),'</td>',
'<td bgcolor="LIGHTBLUE">',round(sqla.disk_reads/sqla.executions,0),'</td>',
'<td bgcolor="LIGHTBLUE"><b>',round(sqla.cpu_time/sqla.executions,0),'</b></td>',
'<td bgcolor="LIGHTBLUE">',round(sqla.buffer_gets/sqla.executions,0),'</td>',
'<td bgcolor="LIGHTBLUE">',replace(replace(sqla.sql_text, '<', '&lt;'), '>', '&gt;') ,'</td>','</tr>'
FROM   (select * from v$sqlarea where executions > 1) sqla
WHERE  sqla.cpu_time > 1000
and    sqla.COMMAND_TYPE in (2,3,6,7)
AND    sqla.BUFFER_GETS/sqla.executions > 100
AND ROWNUM < 11
ORDER BY round(sqla.cpu_time/sqla.executions,0) DESC;
set define "&"

DECLARE cnt_rq number := 0;
BEGIN
   select count(sqla.address) into cnt_rq from (select * from v$sqlarea where executions > 1) sqla
   WHERE  sqla.cpu_time > 1000
   AND    sqla.executions > 1
   AND    sqla.COMMAND_TYPE in (2,3,6,7)
   AND    sqla.BUFFER_GETS/sqla.executions > 100
   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 (pas ajustable). 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 (scans blocks / (scans short tables + scans long tables))</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 ((db blocks gets+consistent gets)-physical gets)/(db blocks gets+consistent gets)</td>','<td bgcolor="'||CouleurLimite(round(((t1.value+t2.value)-t3.value)/(t1.value+t2.value),2)*100,70,10,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 &agrave; 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 (1 - (sorts disk / sorts memory))</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% colspan=2><b>Sch&eacute;ma</b></td><td width=15%><b>Nombre de sessions par sch&eacute;ma</b></td></tr>
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE" colspan=2>',username,'</td>', '<td bgcolor="LIGHTBLUE" align=right>',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'
group by username;

select '<tr><td width=15% colspan=2><b>Nombre d''utilisateurs au moment de l''audit</b></td>','<td bgcolor="BLUE" align=right><font color="WHITE"><b>',count(*),'</b></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;
   tabtools 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
      select count(tablespace_name) into tabtools from dba_tablespaces
      where tablespace_name='TOOLS';
      IF tabtools = 0 THEN
         EXECUTE IMMEDIATE 'create table alert_log (
                             alert_date date,
                             alert_text varchar2(&&alert_length)
                         )';
      ELSE
         EXECUTE IMMEDIATE 'create table alert_log (
                             alert_date date,
                             alert_text varchar2(&&alert_length)
                         ) TABLESPACE TOOLS';
      END IF;
      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 '%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 'ALTER DATABASE BACKUP CONTROLFILE%'
       and text not like 'ALTER DATABASE backup controlfile%'
       and text not like 'alter database backup controlfile%'
       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;
  
  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>&_bdump/alert_&_db..log</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%'
  or alert_text like '%CREATE%' or alert_text like '%DROP%' or alert_text like '%ALTER%')
  and alert_text not like 'ALTER SYSTEM ARCHIVE LOG'
  and alert_text not like 'Completed:%'
  and a.alert_date > d.date_aud
order by 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%'
     or alert_text like '%CREATE%' or alert_text like '%DROP%' or alert_text like '%ALTER%')
     and alert_text not like 'ALTER SYSTEM ARCHIVE LOG'
     and alert_text not like 'Completed:%'
     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>

-- *************************************** Nettoyage de la table "alert_log"
prompt <!-- Nettoyage table alert_log -->
DECLARE
   table_exist number;
BEGIN
   select count(table_name) into table_exist from dba_tables
   where table_name='ALERT_LOG'
   and owner = 'SYSTEM';
   EXECUTE IMMEDIATE 'truncate table alert_log';
END;
/

-- ****************** 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>

-- *************************************** Objets en erreur
prompt <table border=1 width=100% bgcolor="WHITE">
set define off
prompt <tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Erreurs sur les objets utilisateurs (dba_errors)</b></font></td></tr>
prompt <tr><td><b>Objet, num&eacute;ro et texte de la ligne</b></td><td><b>Erreur</b></td></tr>

with err as (
   select distinct owner, name, type, line, position, sequence, substr(TRIM(text),0,240) as text
   from
      dba_errors
   where sequence=1
)
SELECT decode(n,-1,'<tr><td bgcolor="LIGHTBLUE">',''),text
  from (
      select sequence n, owner,name, type,line, (position-1)||'</td><td bgcolor="LIGHTBLUE">'||text||'</td></tr>' text -- Num erreur PL/SQL
        from err
      union all
      select distinct -1 n, owner, name, type, line, '<b>'||type||' '||owner||'.'||name||' line '||line||'</b><br/>' --Num ligne
        from err
      union all
      select 0 n, owner, name, type, line, '"'||substr(TRIM(text),0,240)||'" : pos. ' -- code PL/SQL
        from dba_source
      where (owner,name,type,line) in (select owner, name, type, line from err)
      order by owner,name, type,line, n
);

DECLARE cnt_err number := 0;
BEGIN
with err as (
   select distinct owner, name, type, line, position, sequence, substr(TRIM(text),0,240) as text
   from
      dba_errors
   where sequence=1
)
SELECT count(n) into cnt_err
  from (
      select sequence n, owner,name, type,line, (position-1)||'</td><td bgcolor="LIGHTBLUE">'||text||'</td></tr>' text -- Num erreur PL/SQL
        from err
      union all
      select distinct -1 n, owner, name, type, line, '<b>'||type||' '||owner||'.'||name||' line '||line||'</b><br/>' --Num ligne
        from err
      union all
      select 0 n, owner, name, type, line, '"'||substr(TRIM(text),0,240)||'"&nbsp;:&nbsp;' -- code PL/SQL
        from dba_source
      where (owner,name,type,line) in (select owner, name, type, line from err)
);
   if cnt_err=0 then
      dbms_output.put_line('<tr><td bgcolor="#33FF33" colspan=2><i>N/A</i></td></tr>');
   end if;
end;
/

set define "&"
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>
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="L'espace utilis&eacute; correspond aux blocs allou&eacute;s au segment, qu'ils soient vides (pr&eacute;allocation de blocs ou suppressions de donn&eacute;es) ou remplis."></td>
prompt <td align=center><font color="WHITE"><b>Liste des segments de plus de 100Mo</b></font></td></tr></table></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=3><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>Nombre d'objets</b></td></tr>
set define "&"
select '<tr>','<td bgcolor="LIGHTBLUE">',a.owner,'</td>', '<td bgcolor="LIGHTBLUE">',a.tablespace_name,'</td>', '<td bgcolor="LIGHTBLUE">',count(a.table_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
group by a.owner,a.tablespace_name
order by a.owner,a.tablespace_name;

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></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="LIGHTGREY"></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>Sch&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