Outils personnels

Script d'audit de bases de données ORACLE

De wikiGite

Révision datée du 30 novembre 2010 à 17:41 par Frank (discussion | contributions) (Page créée avec « -- AUDIT BASES ORACLE -- -- Compatible Oracle 9i, 10g -- FSo 2005-2010 -- Changelog -- 2005 Creation du script, regroupement d'operations manuelles repetitives. --... »)
(diff) ← Version précédente | Voir la version actuelle (diff) | Version suivante → (diff)
-- AUDIT BASES ORACLE

-- -- Compatible Oracle 9i, 10g -- FSo 2005-2010 -- Changelog -- 2005 Creation du script, regroupement d'operations manuelles repetitives. -- Generation de rapports format TXT -- 12/2006 Creation table HISTAUDIT et requetes associees pour comparaisons entre 2 audits -- 11/2008 Separation en 2 fichiers 1=audit de perfs 2=environnement schemas -- 02/2009 Modifications des requetes audits de perfs pour generer des fichiers HTML -- 02/2010 Affichage icones "info" et "tips" -- 06/2010 Ajout scan de l'alert.log -- 08/2010 passage section schemas en HTML

-- ****** IMPORTANT ****** : le script doit etre lance par sqlplus, en tant que SYSTEM ******

-- Exemple, sous linux, en utilisant des variables et easyconnect : -- SOURCE=/media/UDISK -- SQLP=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/scripts/sqlplus.sh -- AUDIT_ORACLE=$SOURCE/Audit_Oracle -- connection='//server_oracle:1521/ORA' -- $SQLP system/manager@$connection @$AUDIT_ORACLE/audit_complet_html

-- ****** NOTES / TIPS ****** -- * NOTE 1 : ATTENTION AUX FORMATS DE COLONNES ALPHANUMERIQUES : -- LE XX de "format aXX" DOIT TENIR COMPTE DES BALISES HTML -- de TYPE "<td bgcolor="#3399CC" align=center>" QUI SONT -- COMPTEES DANS LE TOTAL DE LA CHAINE DE CARACTERE -- -- * NOTE 2 : AUTANT QUE POSSIBLE, NE PAS S'OCCUPER DES FORMATS COL -- LAISSER PAR DEFAUT LES ALPHA ET UTILISER UN TO_CHAR(valeur,'format numérique') POUR LES NOMBRES -- -- * NOTE 3 : Le script cree (ou re-cree) 2 fonctions et une table d'historique dans TOOLS -- -- * TIP : inclure une image directement dans le code html : -- <IMG SRC="data:image/gif;base64,R0lGODdhMAAwAPAAAAAAAP///ywAAAAAMAAw AAAC8IyPqcvt3wCcDkiLc7C0qwyGHhSWpjQu5yqmCYsapyuvUUlvONmOZtfzgFz ByTB10QgxOR0TqBQejhRNzOfkVJ+5YiUqrXF5Y5lKh/DeuNcP5yLWGsEbtLiOSp a/TPg7JpJHxyendzWTBfX0cxOnKPjgBzi4diinWGdkF8kjdfnycQZXZeYGejmJl ZeGl9i2icVqaNVailT6F5iJ90m6mvuTS4OK05M0vDk0Q4XUtwvKOzrcd3iq9uis F81M1OIcR7lEewwcLp7tuNNkM3uNna3F2JQFo97Vriy/Xl4/f1cf5VWzXyym7PH hhx4dbgYKAAA7" ALT="Larry"> -- http://www.motobit.com/util/base64-decoder-encoder.asp -- -- ****** BUGS CONNUS ****** -- Incompatibilité de certaines requêtes avec des version < 9. Le script va quand même au bout. -- -- ****** TODO LIST ****** -- AMELIORATIONS GLOBALES (AU SCRIPT) -------------------------- -- TODO : INDIQUER SI L'INSTANCE EN COURS UTILISE IUN SPFILE OU UN INIT.ORA -- TODO : UTILISER DES VARIABLES EN DEBUT DE SCRIPT POUR LES SEUILS D'ALERTES ? -- TODO : affiner les stats d'opérations ASMM (variation des tailles de pools) avec v$sga_resize_ops et v$sga_dynamic_components -- select component, current_size from v$sga_dynamic_components; --select component,oper_type,oper_mode,initial_size/1024/1024 "Initial",TARGET_SIZE/1024/1024 "Target",FINAL_SIZE/1024/1024 "Final",status from v$sga_resize_ops order by component; --select component,current_size/1024/1024 "CURRENT_SIZE",min_size/1024/1024 "MIN_SIZE",user_specified_size/1024/1024 "USER_SPECIFIED_SIZE",last_oper_type "TYPE" from v$sga_dynamic_components order by component; -- TODO : Faire une table des matières (ancres HTML) -- TODO : Remplacer les fonctions de colorisation PL/SQL par du javascript ? -- Note : Javascript inséré systématiquement en haut du fichier HTML produit, réutilisable dans les requêtes ? -- TODO : ajouter chemin des archivelogs si ARCHIVELOG MODE (trouver le(s) log_archive_XXX remplis) -- TODO : coloriser les requêtes gourmandes qui prennent plus de XXX microsec ou XXX buffers -- TODO : Remplacer "Néant" par "N/A" ? ou "Aucun" ? pour éviter l'accent -- TODO : Section SCHEMAS = Liste des grants par user et par role non systeme -- TODO : Afficher N/A si 1er audit ou aucuns objets utilisateurs -- -- AMELIORATIONS LOCALES (AUX SECTIONS) ------------------------- -- TODO : Volumetrie = Insérer un graphe evolution volumétrie (attention aux creations de fichiers entre deux audits) -- TODO : AJOUTER %LIBRE SUR TABLESPACES (comment gérer les autoextend ?) -- TODO : mettre la ligne complète en rouge si aucun des datafiles n'est en autoextend, et que le "LIBRE" arrive à 90% du "TOTAL" -- TODO : fusionner Volumetrie tablespace et difference de taille (ajouter une ligne sous le titre avec 2 colonnes) -- TODO : Diff taille dernier audit = Ne pas coloriser tbs types UNDO et TEMPORARY -- Note : prob = on a pas le tbs type dans histaudit. Lier avec dba_tablespace pour detecter types TEMPORARY/UNDO ? -- Note2: Exclure aussi SYSTEM et SYSAUX de la colorisation ? -- TODO : Afficher N/A si 1er audit -- TODO : Detail UGA par utilisateur = Garder le nombre de session dans HISTAUDIT pour graphique d'evolution et moyenne -- TODO : 'volumetrie tables et index' = A la creation des données d'audit, afficher au moins le total des tables et index, -- meme s'il n'y a pas de difference à afficher -- TODO : Ajouter une colonne user, et grouper par user pour detecter d'eventuels users system oublies dans le NOT IN -- plus une ligne total global à la fin du tableau -- TODO : Intégrer le script de recompilation si le nombre d'INVALID > 0 et afficher le résultat -- TODO : "tables et index dans le meme tbs" = grouper par user et tbs et ne donner que le nbre d'objets -- TODO : 'affichage des logs' = grouper les messages et afficher une somme pour limiter le nombre de ligne ?

set pages 999 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 les séparateur décimaux et milliers en français alter session set nls_numeric_characters=", ";

-- *************************************** Creation de fonctions -- Fonction CouleurLimite renvoie ROUGE (valeur inferieure a limite+plage si "superieur" <= 0, valeur superieure sinon), -- ORANGE (valeur a l'interieur de la plage (limite +/- plage), -- VERT (valeur superieure a limite+plage si "superieur" <= 0, valeur inferieure sinon) CREATE OR REPLACE FUNCTION CouleurLimite (valeurAtester number, limite number, plage number, superieur int) RETURN varchar2 IS signe number; BEGIN if superieur <= 0 then if valeurAtester < limite then select SIGN(valeurAtester-(limite-(plage))) into signe from dual; if signe < 0 then return '#FF0000'; --ROUGE else return '#FF9900'; --ORANGE end if; else select SIGN(valeurAtester-(limite+(plage))) into signe from dual; if signe > 0 then return '#33FF33';--VERT else return '#FF9900';--ORANGE end if; end if; else if valeurAtester >= limite then select SIGN(valeurAtester-(limite+(plage))) into signe from dual; if signe > 0 then return '#FF0000'; --ROUGE else return '#FF9900'; --ORANGE end if; else select SIGN(valeurAtester-(limite-(plage))) into signe from dual; if signe < 0 then return '#33FF33';--VERT else return '#FF9900';--ORANGE end if; end if; end if; END; /

-- *************************************** INITIALISATIONS define logfile = audit define envfile = env define sysusers = ('SYS','SYSTEM','CTXSYS','DBSNMP','OUTLN','ORDSYS','ORDPLUGINS','MDSYS','DMSYS','WMSYS','WKSYS','OLAPSYS','SYSMAN','XDB','EXFSYS','TSMSYS','MGMT_VIEW','ORACLE_OCM','DIP','SI_INFORMTN_SCHEMA','ANONYMOUS') define exusers = ('SCOTT','HR','OE','PM','QS','QS_ADM','QS_CBADM','QS_CS','QS_ES','QS_OS','QS_WS','SH')

--ATTENTION : AUCUN ESPACE DANS LES LISTES, SINON LA VARIABLE EST TRONQUEE ! --prompt &sysusers --prompt &exusers --select username from dba_users where username not in &sysusers and username not in &exusers;

variable tips varchar2(4000); begin select 'iVBORw0KGgoAAAANSUhEUgAAABMAAAATCAYAAAByUDbMAAAABmJLR0QAAAAAAAD5Q7t/AAAACXBIWXMAAAsQAAALEAGtI711AAAACXZwQWcAAAATAAAAEwDxf4yuAAACDElEQVQ4y62ULXDbQBBGnzsFK2YxHzyoMJk50NBhCWugYcpCA0PDEmjosphF0DBmEqvgwSs7MS1TgSz5v9OZdmd2NKNZvfv20+4Nmqbhf8XXcy9L5xpfFpTlGlfmaPiFxCNsMiZJppgkJbF2cPzd4FjZZrNpPpZPRHySJoIxYIzgveIc5KWCXHNz/8xkMhlchGWrH81qMWd+L6SpgOxVKug281xZZcrtw4LZ7FsP7Ntcr7MmW855fhoyHIFE0WEPEVDXiMJ4LJgY3t7mxLFtOoVfOo9Wi+88zOUENDCuT4kiEBABM4LZVHhfPFI61/QwV6yxxmOtnIB8qPvsgCIgkXCdQFwXuGJDDyvyjOvxoUcdaD86IEJfm15BkX/sYL4ssFa4FCY+8k+3T4F4CN7tKQsakMusy6Gtf1qFHSyWGA26OxFovD1RZOKIxtsDdaogw3gHM0nKT7edo7o+C+xAWtfbOiUoeA/GTnawdDyjKLbKjoAHXdV1P7woEJTSQTq+2cFsOsV5Q+mUqgKtQEN9AtUKqqqFhKoFhSjFpnvKEmsHtw+vLJeKd4oGbYEVhHwEQPgctb5uQZWHrIC7+Uu/9Gd3czZtB7L7/Z3ZYa+1bAO3j4e7efbWeF88EtcF6VW7NhJBFVqzu9bu5i9/vjW6KJ1rXLGhyD/w5YaggVhiTDIhHd9g08nf3Wf/Er8BAI4wKLDf6EwAAAAfelRYdENyZWF0aW9uIFRpbWUAAHjaMzDTNzLUNzABAAb7AYwMyT+gAAAALnpUWHRTb2Z0d2FyZQAAeNrzTUwuys9NTclMVHDLLEotzy/KLlbwjVAwMjAwAQCWLgl6ZrFa0gAAAABJRU5ErkJggg==' into :tips from dual; end; /

variable info varchar2(4000); begin select 'R0lGODlhFAAUAOfAAD+JSDyVQEqTTFqTZkedQ0eeQ1CjRVKlRmScdWaccGibdVepRmSlUVqqSV2pUlytSG6jfXWjf2OxTGayS2SzSW+ueXGxVmqzWXGyb2y3TW22Vm64TnC6TXC7T3y2YX+2bXq2fna+UHu5c323gom2b3e/Unm+W3q+X328cHnBUYS8Y3rBUZ+uooe5fIO8gn3EUoHAb4W+f4DDYYPBbn/FU6OyqITBcX/GUYDGU4LEYZK+coHHVILIU4jCgI7DZYfEcZK/i6q2pYvDgae3qZXCdIPLUqi3rIrHaofJYo/EfYvHco7FgZDDkIrKXojMVYjNVYnNVZHIgpTGkZXGkYzPVqe/r7O9qZjIko/RV6DJd7O/rJrKk5vPYJzKlZTSYJPUV5PVVpPVV5/KpLLDsqPJppPWVp/MmpXWWLPEsrnDraHMpKLNo6LNpKTOpaXPpbfItp3ZYqrOs6fRornKtqnQsZ3dWq/Qr5/eWqrYeaLdZarbZLDSr6/Ss6/SubDSuMHMxcXLxbHTtaPhWsbMxbLVrLLUtsLOxbLVtrPVtqved7nXoLXWvrnVvbnXu7ranM3Rx8nUt8rSysvSyb3cn8/TyMvWuK/mbL3dna7rW8bfy9zX3sjgzd3Z4cnhzcnhzt3a4d7a4bvzYOLe47/3X+Tg5dbqw+fi5dDvq+Tk5Ojj5+Xl5cb8Y+bm5unl6Nnr3url6tvr4Orp6+7p7O7q7ezr7ezs7O7t7u7u7ubx6Ojy6vDw8PHx8fH38/f29/f5+Pr7+////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////yH+EUNyZWF0ZWQgd2l0aCBHSU1QACH5BAEKAP8ALAAAAAAUABQAAAj+AP8JHEiwoMGDCBMKlPWnShVDsxQK1FUDQR9XsBZBMLIroSkFcTZ54pUrUyc/EVodrDWATiA+uIABY4So0KEEtwyyqCBGzZpGwHyRcdOGzYghvQimAgCCiRQzcvbY6bLlyhQXAl4RBBQAQ4wehH7JBBJliRARBSIRHEPAAQoYM5LIbKHkh40LB94QRGOggYYTMo7I/IAkh4kJD+YQHLRAwoYSNJrI9PBkx4oOFCQRFMUgQwgcULzIVBGGCo8UFkgVDMLhhZMvcGT6uHOGyg0ttgqqIlEES55TMktZqlOGCKuDmnSASaTI0aVJeARlAZUQlRUumEKtGqUnDSqJtDgNPYJUidKnWBLTq1cYEAA7' into :info from dual; end; /

column bname new_value dbname noprint column hname new_value hstname noprint select name as bname from v$database; select host_name as hname from v$instance;

column bloc new_value dbloc noprint select value as bloc from v$parameter where name = 'db_block_size';

column bdate new_value dateaudit noprint select to_char(to_date(sysdate),'ddmmyy') as bdate from dual;

spool &logfile._&dbname._&hstname._&dateaudit..html prompt <!DOCTYPE public "-//w3c//dtd html 4.01 strict//en" "http://www.w3.org/TR/html4/strict.dtd"> prompt <html> prompt <head> prompt <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> prompt <meta name="description" content="Audit Oracle HTML"> prompt <title>Audit &dbname (&hstname)</title> prompt </head> prompt <BODY BGCOLOR="#003366">

-- *************************************** Entete

prompt prompt set define off prompt

--select 'Audit base '||name from v$database; prompt <b>Audit &dbname (&hstname) select ' du '||to_char(to_date(sysdate),'DD-MON-YYYY')||'</b>' as DATE_AUDIT from dual; prompt </td></tr></table> prompt

-- *************************************** Section informations

prompt
prompt
<b>SECTION INFORMATIONS</b>
prompt

-- *************************************** Versions

prompt prompt prompt prompt
-- *************************************** NLS_PARAMETERS set define off prompt prompt select '','','' from v$nls_parameters; prompt
Versions

select banner||'
' from v$version;

prompt
Paramètres NLS
ParamètreValeur
'||parameter||''||value||'

prompt
-- *************************************** NOMS

prompt prompt select '','' from v$database; select '','' from v$parameter where name='db_unique_name'; select '','' from v$instance; prompt
Noms database et instance
DB_NAME'||name||'
DB_UNIQUE_NAME'||value||'
INSTANCE_NAME'||instance_name||'

prompt


-- *************************************** General

prompt --NE PLUS INTERPRETER LE "&" par sqlplus POUR L'INSTANT, le HTML en a besoin set define off prompt prompt

--INTERPRETER A NOUVEAU LE "&" set define "&"

prompt prompt prompt prompt prompt prompt REM prompt
Informations générales
Base créée le

select CREATED from v$database;

prompt
Up depuis le

select STARTUP_TIME from v$instance;

prompt
Taille de blocs

prompt &dbloc octets

prompt
Archive log mode select decode(log_mode,'ARCHIVELOG',''||log_mode||'
','
'||log_mode||'
') from v$database; prompt

prompt

-- *************************************** Historique audit

prompt prompt

-- Creation table HISTAUDIT si necessaire

prompt prompt
Historique d'audits
Table historique

WHENEVER sqlerror EXIT sql.sqlcode DECLARE tabhist number; colmodif number;

BEGIN select count(table_name) into tabhist from dba_tables where table_name='HISTAUDIT' and owner = 'SYSTEM'; select count(column_name) into colmodif from dba_tab_columns where table_name='HISTAUDIT' AND owner='SYSTEM' AND column_name='MODIFIED';

IF tabhist = 0 THEN select count(tablespace_name) into tabhist from dba_tablespaces where tablespace_name='TOOLS'; IF tabhist = 0 THEN dbms_output.put_line('Tablespace TOOLS inexistant !
Impossible de creer la table HISTAUDIT. Sortie du script.
');

dbms_output.put_line('
');

raise_application_error(-20001,'Tablespace inexistant'); END IF; EXECUTE IMMEDIATE 'create table SYSTEM.histaudit (date_aud date, type_obj varchar2(5), obj_name varchar2(30), total number, utilis number, modified char(1) default 0) TABLESPACE TOOLS'; dbms_output.put_line('Creation table HISTAUDIT...
'); ELSE IF colmodif=0 THEN EXECUTE IMMEDIATE 'alter table system.histaudit add MODIFIED char(1) default 0'; dbms_output.put_line('Table HISTAUDIT modifiee (col MODIFIED).
'); END IF; dbms_output.put_line('Table HISTAUDIT existante.
'); END IF; END; / WHENEVER sqlerror CONTINUE; prompt </td></tr>

prompt
<b>Dernier Audit</td> prompt

select decode(max(to_date(date_aud)),,'N/A',max(to_date(date_aud))) from system.histaudit where to_date(date_aud) < trunc(sysdate);

-- *************************************** Modif dernier audit ?

prompt </td></tr>
Paramètres modifiés lors du dernier audit</td>

set define "&"

select decode(max(modified),1,'
OUI</td></tr>','NON</td></tr>') from system.histaudit

where to_date(date_aud) < trunc(sysdate); prompt </td></tr></table> prompt

-- On force la remise à 0 de modified maintenant qu'on a signalé la modif du dernier audit update system.histaudit set modified=0;

-- *************************************** SECTION STOCKAGE

prompt
prompt
SECTION STOCKAGE
prompt

-- *************************************** MISE A JOUR TABLE HISTORIQUE (TABLESPACES ET SEGMENTS) delete from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='TBS'; insert into system.histaudit ( select sysdate, 'TBS', t.tablespace_name, t.total, decode(u.utilise,,0,u.utilise), 0 from (select tablespace_name, round(sum(bytes)/(1024*1024),2) total from dba_data_files group by tablespace_name) t, (select tablespace_name, round(sum(blocks)*&dbloc/(1024*1024),2) utilise from dba_segments group by tablespace_name) u where t.tablespace_name=u.tablespace_name(+) UNION select sysdate, 'TBS', tablespace_name, total, 0, 0 from (select tablespace_name, round(sum(bytes)/(1024*1024),2) total from dba_temp_files group by tablespace_name) t); delete from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='TAB'; insert into system.histaudit ( select sysdate, 'TAB', 'Total segments tables', total, 0, 0 from (select decode(round(sum(bytes)/(1024*1024),2),NULL,0,round(sum(bytes)/(1024*1024),2)) total from dba_segments where segment_type like 'TABLE%' and owner not in &sysusers and owner not in &exusers)); delete from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='IND'; insert into system.histaudit ( select sysdate, 'IND', 'Total segments indexes', total, 0, 0 from (select decode(round(sum(bytes)/(1024*1024),2),NULL,0,round(sum(bytes)/(1024*1024),2)) total from dba_segments where segment_type like 'INDEX%' and owner not in &sysusers and owner not in &exusers)); insert into system.histaudit ( select sysdate, 'AUT', 'Total segments autres', total, 0, 0 from (select decode(round(sum(bytes)/(1024*1024),2),NULL,0,round(sum(bytes)/(1024*1024),2)) total from dba_segments where segment_type not like 'TABLE%' and segment_type not like 'INDEX%' and owner not in &sysusers and owner not in &exusers));

-- *************************************** TABLESPACES

prompt
prompt
TABLESPACES
prompt

-- *************************************** Liste tablespaces

prompt prompt prompt

prompt

select '','', '', decode(status,'ONLINE','',''),'' from dba_tablespaces

order by tablespace_name;

prompt
Liste des tablespaces
TablespaceContenuStatut
'||tablespace_name||''||contents||''||status||''||status||'

-- *************************************** Liste datafiles

prompt prompt prompt select '','','', '', decode(autoextensible,'NO','',''), '', '', ''

from DBA_DATA_FILES group by tablespace_name,file_name,autoextensible UNION

select '','','', '', decode(autoextensible,'NO','',''), '', '', ''

from DBA_TEMP_FILES group by tablespace_name,file_name,autoextensible order by 2,3;

prompt
Liste des datafiles par tablespace
TablespaceFichierTaille (Mo)AutoextensibleNextMaxSize
'||TABLESPACE_NAME||''||FILE_NAME||''||decode(round(sum(blocks)*&dbloc/(1024*1024),2),NULL,to_char('0','S99G999G990D00'),to_char(round(sum(blocks)*&dbloc/(1024*1024),2),'99G999G990D00'))||'OFFON'||to_char(round(sum(increment_by)*&dbloc/(1024*1024),2),'99G999G990D00')||''||to_char(round(sum(maxbytes)/(1024*1024),2),'99G999G990D00')||'
'||TABLESPACE_NAME||''||FILE_NAME||''||decode(round(sum(blocks)*&dbloc/(1024*1024),2),NULL,to_char('0','S99G999G990D00'),to_char(round(sum(blocks)*&dbloc/(1024*1024),2),'99G999G990D00'))||'OFFON'||to_char(round(sum(increment_by)*&dbloc/(1024*1024),2),'99G999G990D00')||''||to_char(round(sum(maxbytes)/(1024*1024),2),'99G999G990D00')||'

-- *************************************** Volumétrie tablespaces

prompt set define off prompt prompt

set define "&"

select '','' Tablespace, '',to_char(t.total,'99G999G990D00'))||'</td>' TOTAL,

'<td bgcolor="LIGHTBLUE" align=right>'||decode(u.utilise,,'0,00',to_char(u.utilise,'99G999G990D00'))||'</td>' UTILISE,

'<td bgcolor="LIGHTBLUE" align=right>'||decode(l.libre,NULL,to_char(t.total-(decode(u.utilise,,0,u.utilise)),'99G999G990D00'),to_char(l.libre,'99G999G990D00'))||'</td>' LIBRE,'' from (select tablespace_name, round(sum(bytes)/(1024*1024),2) total from dba_data_files group by tablespace_name) t, (select tablespace_name, round(sum(blocks)*&dbloc/(1024*1024),2) utilise from dba_segments group by tablespace_name) u, (select tablespace_name, round(sum(blocks)*&dbloc/(1024*1024),2) libre from dba_free_space group by tablespace_name) l where t.tablespace_name=u.tablespace_name(+) and u.tablespace_name=l.tablespace_name(+); select '<tr>','<td bgcolor="LIGHTBLUE">'||tablespace_name||'</td>', '<td bgcolor="LIGHTBLUE" align=right>'||to_char(total,'99G999G990D00')||'</td>', '<td bgcolor="LIGHTBLUE" align=right>0,00</td>' as utilise, '<td bgcolor="LIGHTBLUE" align=right>'||to_char(total,'99G999G990D00')||'</td>','</tr>' as libre from (select tablespace_name, round(sum(bytes)/(1024*1024),2) total from dba_temp_files group by tablespace_name) t; select '<tr>','<td bgcolor="WHITE">TOTAL</td>', '<td bgcolor="BLUE" align=right>'||to_char(dt.total+tt.total,'99G999G990D00')||'</td>' as total, '<td bgcolor="BLUE" align=right>'||to_char(du.utilise+tu.utilise,'99G999G990D00')||'</td>' as utilise, '<td bgcolor="BLUE" align=right>'||to_char(dl.libre+tl.libre,'99G999G990D00')||'</td>' as libre,'</tr>' from (select round(sum(bytes)/(1024*1024),2) total from dba_data_files) dt, (select round(sum(bytes)/(1024*1024),2) total from dba_temp_files) tt, (select round(sum(blocks)*&dbloc/(1024*1024),2) utilise from dba_segments) du, (select 0 utilise from dual) tu, (select round(sum(blocks)*&dbloc/(1024*1024),2) libre from dba_free_space) dl, (select round(sum(bytes)/(1024*1024),2) libre from dba_temp_files) tl; prompt </table>
-- *************************************** Diff tailles tablespaces depuis dernier audit prompt
Volumétrie
TablespaceTotal (Mo)Utilisé (Mo)Libre (Mo)
'||t.tablespace_name||''||decode(t.total,,to_char(round(l.libre,0),'99G999G990D00')||'
set define off prompt prompt

set define "&"

select '','',

decode(SIGN(a.total-h.total),

-1,'', 0,'', 1,'', NULL,''),

decode(SIGN(a.utilis-h.utilis),

-1,'', 0,'', 1,'', NULL,''),''

from (select * from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='TBS') a, (select * from system.histaudit where to_date(date_aud) like (select max(to_date(date_aud)) from system.histaudit where to_date(date_aud) < trunc(sysdate) and type_obj='TBS') and type_obj='TBS') h where a.obj_name=h.obj_name;

select '','', '' as total, '' as utilise,''

from (select * from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='TBS') a, (select * from system.histaudit where to_date(date_aud) like (select max(to_date(date_aud)) from system.histaudit where to_date(date_aud) < trunc(sysdate) and type_obj='TBS') and type_obj='TBS') h where a.obj_name=h.obj_name;

prompt
Différence de tailles depuis le dernier audit
TablespaceTotal (Mo)Utilisé (Mo)
'||a.obj_name||''||to_char(a.total-h.total,'S99G999G990D00')||''||to_char(a.total-h.total,'99G999G990D00')||''||to_char(a.total-h.total,'S99G999G990D00')||'Premier audit'||to_char(a.utilis-h.utilis,'S99G999G990D00')||''||to_char(a.utilis-h.utilis,'99G999G990D00')||''||to_char(a.utilis-h.utilis,'S99G999G990D00')||'Premier audit
TOTAL'||to_char(round(sum(a.total-h.total)),'S99G999G990D00')||''||to_char(round(sum(a.utilis-h.utilis)),'S99G999G990D00')||'

-- *************************************** Fragmentation TBS

prompt set define off prompt prompt

set define "&"

select '','', '', '' pourcentage,''

from dba_free_space_coalesced where percent_extents_coalesced <> 100;

DECLARE cnt_frag number; BEGIN select count(tablespace_name) into cnt_frag from dba_free_space_coalesced where percent_extents_coalesced <> 100; if cnt_frag=0 then

dbms_output.put_line('');

end if; end; /

prompt
prompt prompt
  <img src="data:image/gif;base64,

print tips

prompt " width="20" height="20" alt="Tips..." title="Si fragmentation > 0%, nécessite coalescing (ALTER TABLESPACE XXX COALESCE)">
fragmentation des tablespaces 
TablespaceTotalFragmentation %
'||tablespace_name||''||total_extents||''||to_char(round(percent_extents_coalesced,2),'S990D00')||'
N/AN/AN/A

-- *************************************** SEGMENTS

prompt
prompt
SEGMENTS (Objets utilisateurs)
prompt

-- *************************************** Volumétrie tables et indexes

prompt set define off prompt prompt

set define "&"

select '','', '', '',''

from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments where segment_type = 'TABLE' and owner not in &sysusers and owner not in &exusers) a, (select * from system.histaudit where to_date(date_aud) like (select max(to_date(date_aud)) from system.histaudit where to_date(date_aud) < trunc(sysdate) and type_obj='TAB') and type_obj='TAB') l;

select '','', '', '',''

from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments where segment_type = 'INDEX' and owner not in &sysusers and owner not in &exusers) a, (select * from system.histaudit where to_date(date_aud) like (select max(to_date(date_aud)) from system.histaudit where to_date(date_aud) < trunc(sysdate) and type_obj='IND') and type_obj='IND') l;

select '','', '', '',''

from (select round(sum(bytes)/(1024*1024),2) as total from dba_segments where segment_type not in ('TABLE', 'INDEX') and owner not in &sysusers and owner not in &exusers) a, (select * from system.histaudit where to_date(date_aud) like (select max(to_date(date_aud)) from system.histaudit where trunc(to_date(date_aud)) < trunc(sysdate) and type_obj='AUT') and type_obj='AUT') l;

prompt
Volumétrie segments utilisateurs
Type de segmentsTotal (Mo)Différence de taille depuis le dernier audit
TABLES'||decode(to_char(round(a.total,2),'99G999G990D00'),NULL,0)||''||decode(to_char(round(a.total-l.total,2),'S99G999G990D00'),NULL,0)||'
INDEXES'||decode(to_char(round(a.total,2),'99G999G990D00'),NULL,0)||''||decode(to_char(round(a.total-l.total,2),'S99G999G990D00'),NULL,0)||'
AUTRES (LOG SEGMENTS, LOB INDEXES, CLUSTERS)'||decode(to_char(round(a.total,2),'99G999G990D00'),NULL,0)||''||decode(to_char(round(a.total-l.total,2),'S99G999G990D00'),NULL,0)||'

-- *************************************** REDO LOG FILES

prompt
prompt
REDO LOG FILES
prompt

-- *************************************** Redo logs files

prompt prompt prompt select '','', '','','' from v$log l,v$logfile f where l.group# = f.group# order by l.group#; prompt
Liste des fichiers redo logs
GroupeFichierTaille (Mo)
'||l.group#||''||member||''||to_char(round(bytes/(1024*1024),2),'99G999G990D00')||'

-- *************************************** Statistiques switchs REDO LOGS

prompt prompt

set define "&"

prompt prompt prompt -- *************************************** Stats switchs select '','', '','','

set define off

prompt prompt
  <img src="data:image/gif;base64,

print info

prompt " width="20" height="20" alt="Tips..." title="ATTENTION : l'historique des logs peut être supprimé au fur et à mesure : ces statistiques risquent de ne pas être viables.">
Statistiques switchs REDO LOGS
StatistiqueDateValeur
Nombre de switchs par jour (depuis 30 jours)

select trunc(first_time)||'
' from v$loghist where first_time > (sysdate-30) group by trunc(first_time) order by trunc(first_time);

prompt

select count(first_time)||'
' from v$loghist where first_time > (sysdate-30) group by trunc(first_time) order by trunc(first_time);

prompt
Moyenne par jour :'||round(avg(nbc),0)||'</font></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
Nombre de switchs par mois (depuis 1 an)</td> prompt

select to_char(to_date(first_time),'mm/yyyy')||'
' 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

select count(*)||'
' 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 '
Temps MIN. entre 2 switchs :</td>', ''||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 '
Temps MAX. entre 2 switchs :</td>', ''||to_char(max(a.first_time-b.first_time)*24*3600,'99999G990')||' secondes</td>','</tr>'

from v$loghist a, v$loghist b where a.first_change#=b.switch_change#;

prompt </table>

-- *************************************** UNDO

prompt
prompt
UNDO / ROLLBACK SEGMENTS
prompt

-- *************************************** Rollback segments ********************

prompt prompt prompt select '','', '','','' from dba_rollback_segs; prompt
Rollback segments
SegmentTablespaceStatut
'||segment_name||''||tablespace_name||''||status||'

-- *************************************** Stats rollback segs

prompt prompt prompt select '','','','','','' from v$rollstat,v$rollname

where v$rollstat.usn=v$rollname.usn;

prompt
Statistiques rollback segments
Segment USNNomNombre SHRINKStaille moyenne SHRINKS
'||v$rollstat.usn||''||name||''||shrinks||''||aveshrink||'

-- *************************************** CONFLITS D'ACCES

prompt
prompt
STATISTIQUES D'ACCES DISQUE
prompt

-- *************************************** contentions de basculement

prompt set define off prompt prompt

set define "&"

select '','','','','',''

from v$session_wait where event like 'log%';

DECLARE cnt_event number := 0; BEGIN select count(sid) into cnt_event from v$session_wait where event like 'log%' and rownum = 1; if cnt_event=0 then

dbms_output.put_line('');

end if; end; /

prompt
prompt prompt
  <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écédent n'est pas fini lors d'un CPKT ou d'un switch (qui occasionne lui-même un ckpt). Augmenter la taille des fichiers redo logs, ou leur nombre si ça ne suffit pas."> prompt   <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">
Contentions de basculement redo logs 
NomEvènementWait (en secondes)Etat
'||sid||''||event||''||seconds_in_wait||''||state||'
N/AN/AN/AN/A

-- *************************************** Conflits d'acces disque

prompt set define off prompt prompt

set define "&"

select '','','','' from v$waitstat; prompt
prompt prompt
  <img src="data:image/gif;base64,

print info prompt " width="20" height="20" alt="Tips..." title="ATTENTION : ces valeurs sont cumulées depuis le dernier démarrage."> prompt   <img src="data:image/gif;base64, print tips

prompt " width="20" height="20" alt="Tips..." title="Si data block > 0 (trop de blocs modifiés dans le buffer cache) augmenter le nombre de processus DBWR.
Si segment header ou free list > 0 (multiplier les freelists en re-créant la table avec nouveau paramètre).
Si un des paramètres UNDO est supérieur à 1% ou 2% besoin de plus de rollback segments.">
Conflits d'accès disque 
ClasseNombre
'||class||''||count||'

-- *************************************** Evenements systemes

prompt set define off prompt prompt

set define "&"

select '','','','','','' from v$system_event

where event like 'log%' or event like 'db file%';

prompt
Evénements système
EvenementTotal waitsTimeoutAverage time
'||event||''||total_waits||''||total_timeouts||''||to_char(average_wait,'999G990D00')||'

-- *************************************** SECTION INSTANCE

prompt
prompt
SECTION INSTANCE
prompt

-- *************************************** POOLS MEMOIRE

prompt
prompt
SGA
prompt

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

set define off

select '','', '',''

from v$sgastat s where name != 'KGH: NO ACCESS' UNION ALL --CouleurLimite (valeurAtester number, limite number, plage number, superieur int)

select '','', '',''

from v$sgastat s where name = 'KGH: NO ACCESS' UNION ALL

select '','',

decode(to_char(round(p.value/(1024*1024),2)),

,'',''),''

from v$spparameter p where p.name = 'sga_max_size' UNION ALL

select '','', '',''

from v$parameter p where p.name = 'sga_max_size' UNION ALL

select '','', '',''

from v$parameter p where p.name = 'sga_target' UNION ALL

select '','', '',''

from v$sgastat s; set define "&"

prompt
Taille totale SGA
SGAvaleur (Mo)
Total SGA instance'||to_char(round(sum(s.bytes)/(1024*1024),2),'99G999G990D00')||'
KGH NO ACCESS (Echange db_cache/SGA si mode ASMM)'||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'))||'
sga_max_size SPFILE Par défaut'||to_char(round(p.value/(1024*1024),2),'99G999G990D00')||'
sga_max_size INSTANCE'||to_char(round(p.value/(1024*1024),2),'99G999G990D00')||'
sga_target INSTANCE'||to_char(round(p.value/(1024*1024),2),'99G999G990D00')||'
TOTAL'||to_char(round(sum(s.bytes)/(1024*1024),2),'99G999G990D00')||'

-- *************************************** MISE A JOUR TABLE HISTORIQUE delete from system.histaudit where trunc(to_date(date_aud))=trunc(sysdate) and type_obj='SGA'; insert into system.histaudit ( select sysdate,'SGA','sga_size (spfile/max_used)',total,valeur,0 from (select round(value/(1024*1024),2) total from v$parameter where name = 'sga_max_size') p, (select round(sum(bytes)/(1024*1024),2) valeur from v$sgastat) s ); insert into system.histaudit ( select sysdate, 'SGA', 'shared_pool (spfile/used)', t.Shared_pool_size, u.utilise, 0 from (select name, round(value/(1024*1024),2) Shared_pool_size from v$parameter where name='shared_pool_size') t, (select round(sum(bytes)/(1024*1024),2) Utilise from v$sgastat where pool='shared pool' and name <> 'free memory') u); insert into system.histaudit ( select sysdate,'SGA','buffer_cache',round(value/(1024*1024),2), 0, 0 from v$sga where name = 'Database Buffers');Redo


-- *************************************** Diff memoire utilisee

prompt set define off prompt prompt </tr>

set define "&"

select '','',

decode(SIGN(a.total-h.total),

-1,'', 0,'', 1,'') TOTAL,

decode(SIGN(a.utilis-h.utilis),

-1,'', 0,'', 1,'') UTILISE,''

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
Différence de tailles depuis le dernier audit
Espaces mémoireSPFILE (Mo)Utilisé (Mo)
'||a.obj_name||''||to_char(a.total-h.total,'S99G999G990D00')||''||to_char(a.total-h.total,'99G999G990D00')||''||to_char(a.total-h.total,'S99G999G990D00')||''||to_char(a.utilis-h.utilis,'S99G999G990D00')||''||to_char(a.utilis-h.utilis,'99G999G990D00')||''||to_char(a.utilis-h.utilis,'S99G999G990D00')||'

-- *************************************** Pools memoire

prompt set define off prompt prompt

set define "&"

select '','' NOM,'' valeur,'' from v$sga; prompt
Pools mémoire
Poolvaleur (Mo)
'||name||''||to_char(round(value/(1024*1024),2),'99G999G990D00')||'

-- *************************************** SHARED POOL

prompt
prompt
SHARED POOL
prompt

-- *************************************** Shared pool

prompt set define off prompt prompt

set define "&"

select '','', '', '', '',''

from (select name, round(value/(1024*1024),2) Total from v$parameter where name='shared_pool_size') t, (select round(sum(bytes)/(1024*1024),2) Utilise from v$sgastat where pool='shared pool' and name <> 'free memory') u, (select round(sum(bytes)/(1024*1024),2) libre from v$sgastat where pool='shared pool' and name = 'free memory') l; -- select name,round(bytes/(1024*1024),2) valeur -- from v$sgastat where pool='shared pool';

prompt
Shared pool area
PoolTotal (Mo)Utilisé (Mo)Libre (Mo)
'||t.name||''||to_char(t.total,'99G999G990D00')||''||to_char(u.utilise,'99G999G990D00')||''||to_char(l.libre,'99G999G990D00')||'

-- *************************************** Dictionary cache

prompt set define off prompt prompt

set define "&"

select '','','','','', '',''

from v$rowcache;

prompt
prompt prompt
  <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   <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érieur à 85%.">
Dictionary cache 
GetsGet MissesScanScan MissesRatio
'||sum(gets)||''||sum(getmisses)||''||sum(scans)||''||sum(scanmisses)||''||round((sum(gets)-sum(getmisses))/sum(gets),2)*100||' %

-- *************************************** Library cache

prompt set define off prompt prompt

set define "&"

select '','' exec, '' recharg, '' ratio,''

from v$librarycache;

-- *************************************** Stat library cache set define off

prompt prompt

set define "&"

select '','', '', '',''

from v$librarycache;

prompt
prompt prompt
  <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   <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érieur à 85%">
Library cache 
ExecutionsRechargementsRatio
'||sum(pins)||''||sum(reloads)||''||round((sum(pins)-sum(reloads))/sum(pins),2)*100||' %
prompt prompt
  <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   <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.
Augmenter SHARED_POOL_SIZE si les ratios sont inférieur à 90%. BODY et INDEX ne sont pas significatifs et peuvent être ignorés.">
Statistiques library cache par types de requêtes 
NamespaceGetHitsPinHits
'||namespace||''||round(gethitratio,2)*100||' %'||round(pinhitratio,2)*100||' %

-- *************************************** Requetes les plus gourmandes

prompt set define off prompt prompt

set define "&"

SELECT '','', '', '', '', '', '', '',''

FROM v$sqlarea WHERE executions > 99 and COMMAND_TYPE in (2,3,6,7) AND BUFFER_GETS > 100000 AND ROWNUM < 11 ORDER BY executions DESC;

DECLARE cnt_rq number := 0; BEGIN select count(address) into cnt_rq from v$sqlarea WHERE executions > 99 and COMMAND_TYPE in (2,3,6,7) AND BUFFER_GETS > 100000 and rownum = 1; if cnt_rq=0 then

dbms_output.put_line(''); dbms_output.put_line('');

end if; end; /

prompt
Requêtes les plus gourmandes en ressources
AdresseExécutionsMoy. trisMoyenne lectures disqueMoyenne cpu(µsec)Moyenne buffersRequête sql
'||address||''||executions||''||round(sorts/executions,0)||''||round(disk_reads/executions,0)||''||round(cpu_time/executions,0)||''||round(buffer_gets/executions,0)||''||sql_text||'
N/AN/AN/AN/AN/AN/AN/A

-- *************************************** AUTRES POOLS

prompt
prompt
AUTRES POOLS
prompt

-- *************************************** Large pool

prompt prompt prompt select '','', '', '', '',''

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
Large pool
NomTotal (Mo)Utilise (Mo)Libre (Mo)
'||t.name||''||to_char(decode(t.total,NULL,0,t.total),'99G999G990D00')||''||to_char(decode(u.utilise,NULL,0,t.total),'99G999G990D00')||''||to_char(decode(l.libre,NULL,0,t.total),'99G999G990D00')||'

-- *************************************** Java pool

prompt set define off prompt prompt

set define "&"

select '','', '', '', '',''

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
Java pool
NomTotal (Mo)Utilisé (Mo)Libre (Mo)
'||substr(t.name,1,30)||''||to_char(decode(t.total,NULL,0,t.total),'99G999G990D00')||''||to_char(decode(u.utilise,NULL,0,t.total),'99G999G990D00')||''||to_char(decode(l.libre,NULL,0,t.total),'99G999G990D00')||'

-- *************************************** BUFFER CACHE

prompt
prompt
BUFFER CACHE
prompt

-- *************************************** Buffer cache : Blocs lus E/S

prompt set define off prompt prompt

set define "&"

select '','','',''

from v$sysstat where name like 'table scan%' UNION ALL

select '','', '','' 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 prompt

set define "&"

select '','', '','' from v$sysstat

where name in ('db block gets','consistent gets','physical reads') UNION ALL

select '','','',''

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
prompt prompt
  <img src="data:image/gif;base64,

print tips

prompt " width="20" height="20" alt="Tips..." title="Si ce ratio est très supérieur à 10, laisser tel quel Sinon ajuster _small_table_threshold (defaut 4) pour être inférieur à 10.">
Buffer cache : Blocs lus E/S 
NomValeur
'||name||''||value||'
Ratio '||to_char(round(t1.value/(t2.value+t3.value),2),'99G990D00')||'
prompt prompt
  <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émoire en réduisant les buffers.">
Buffer cache : hit ratio 
NomValeur
'||name||''||value||'
Ratio'||round(((t1.value+t2.value)-t3.value)/(t1.value+t2.value),2)*100||' %

-- *************************************** Redo buffers ****************************

prompt
prompt
REDO BUFFERS
prompt
prompt prompt prompt select '','', '','' from v$parameter

where name='log_buffer';

prompt
Redo buffers
PoolTaille (Mo)
'||name||''||to_char(round(value/(1024*1024),2),'99G999G990D00')||'

-- *************************************** Stats redo logs (contentions)

prompt set define off prompt prompt

set define "&"

select '','','','' from v$sysstat

where name like 'redo%' UNION ALL

select '','', '',''

from v$sysstat t1, v$sysstat t2 where t1.name like 'redo wastage' and t2.name like 'redo size';

prompt
prompt prompt
  <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érieur à 80%, il y a trop de perte de place dans les fichiers redo, ce qui indique une activité trop forte du LGWR. Vérifier les checkpoints et/ou les switchs.">
Statistiques redo logs (contentions) 
NomValeur
'||name||''||value||'
Ratio wastage/size'||round(1-(t1.value/t2.value),2)*100||' %

-- *************************************** Stats latchs (contentions)

prompt set define off prompt prompt

set define "&"

select '','', '', '',''

from v$latch where name in ('redo allocation', 'redo copy') group by name;

prompt
prompt prompt
  <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ées, diminuer LOG_SMALL_ENTRY_SIZE." width=15%>
Statistiques latchs (contentions) 
NomRatio misses/getsRatio immediate misses/immediate gets
'||name||''||to_char(round(sum(misses)/(sum(gets)+0.00000000001)*100),'990D00')||' %'||to_char(round(sum(immediate_misses)/(sum(immediate_misses+immediate_gets)+0.00000000001)*100),'990D00')||' %

-- *************************************** zone de tri

prompt
prompt
SORT AREA
prompt

-- *************************************** Taille zone de tri

prompt prompt prompt select '','', '','' from v$parameter

where name='sort_area_size'; -- *************************************** Statistiques zone de tri

prompt prompt select '','', '','' from v$sysstat

where name like 'sort%' UNION ALL

select '','', '','' from v$sysstat t1, v$sysstat t2

where t1.name like 'sorts (disk)%' and t2.name like 'sorts (memory)%';

prompt
Taille zone de tri
PoolTaille (Mo)
'||name||''||to_char(round(value/(1024*1024),2),'99G999G990D00')||'
Statistiques zone de tri
NomValeur
'||name||''||value||'
Ratio'||round(1-(t1.value/t2.value),2)*100||' %

-- *************************************** PGA

prompt
prompt
PGA
prompt

-- *************************************** Statistiques PGA

prompt set define off prompt prompt

set define "&"

select '','', '', '',''

from v$process,v$parameter where name='pga_aggregate_target' group by value;

-- *************************************** Detail UGA par utilisateur set define off

prompt prompt

set define "&"

select '','', '', '',''

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 '','
Cumuls PGA
Actuel (Mo)Max alloué (Mo)PGA_AGGREGATE_TARGET (Mo)
'||to_char(round(sum(PGA_ALLOC_MEM)/1024/1024,2),'99G999G990D00')||''||to_char(round(sum(PGA_MAX_MEM)/1024/1024,2),'99G999G990D00')||''||to_char(round(to_number(value)/1024/1024,2),'99G999G990D00')||'
Détail UGA par utilisateur
NomValeurValeur (Mo)
'||username||''||name||''||to_char(round(value/(1024*1024),2),'99G999G990D00')||'
Nombre dutilisateurs'||count(*)||'</td>','</tr>'

from v$statname n, v$sesstat t, v$session s where s.sid=t.sid and n.statistic#=t.statistic# and s.type='USER' and s.username is not null and n.name='session pga memory';

select '
Total UGA (Mo)</td>',''||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 '
Total max UGA (Mo)</td>',''||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>

-- *************************************** ALERT.LOG

prompt
prompt
ALERT LOG
prompt

-- *************************************** Lecture de l'alert log prompt

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 DECLARE table_exist number; BEGIN select count(table_name) into table_exist from dba_tables where table_name='ALERT_LOG' and owner = 'SYSTEM'; IF table_exist = 0 THEN EXECUTE IMMEDIATE 'create table alert_log ( alert_date date, alert_text varchar2(&&alert_length) ) TABLESPACE TOOLS'; EXECUTE IMMEDIATE 'create index alert_log_idx on alert_log(alert_date)'; ELSE EXECUTE IMMEDIATE 'truncate table alert_log'; END IF; END; /

-- ***************************************** external table alert_log_disk DECLARE dir_exist number; BEGIN select count(DIRECTORY_NAME) into dir_exist from dba_directories where DIRECTORY_NAME='BDUMP' and owner in ('SYSTEM','SYS'); IF dir_exist <> 0 THEN EXECUTE IMMEDIATE 'drop directory BDUMP'; END IF; EXECUTE IMMEDIATE 'create directory BDUMP as &_bdump'; END; /

DECLARE table_exist number; BEGIN select count(table_name) into table_exist from dba_tables where table_name='ALERT_LOG_DISK' and owner = 'SYSTEM'; IF table_exist <> 0 THEN EXECUTE IMMEDIATE 'drop table alert_log_disk'; END IF; EXECUTE IMMEDIATE 'create table alert_log_disk (text varchar2(&&alert_length)) organization external ( type oracle_loader default directory BDUMP access parameters ( records delimited by newline nologfile nobadfile fields terminated by "&" ltrim ) location(alert_&_db..log) ) reject limit unlimited'; END; /

-- ************************************ update table alert_log from alert_log_disk prompt set serveroutput on declare isdate number := 0; start_updating number := 0; rows_total number := 0; rows_inserted number := 0; alert_date date; max_date date; alert_text alert_log_disk.text%type;

begin -- find a starting date : last audit select max(to_date(date_aud)) into max_date from system.histaudit where to_date(date_aud) < trunc(sysdate); select count(*) into rows_total from alert_log_disk;

if (max_date is null) then max_date := to_date('01-01-1980', 'dd-mm-yyyy'); end if;

for r in ( select text from alert_log_disk where text not like '%offlining%' and text not like 'ARC_:%' and text not like '%LOG_ARCHIVE_DEST_1%' and text not like '%Thread 1 advanced to log sequence%' and text not like '%Current log#%seq#%mem#%' and text not like '%Undo Segment%lined%' and text not like '%alter tablespace%back%' and text not like '%Log actively being archived by another process%' and text not like '%Committing creation of archivelog%' and text not like '%Private_strands%' and trim(text) not like '(&_db)' and text not like '%alter database backup controlfile to trace%' and text not like '%Created Undo Segment%' and text not like '%started with pid%' and text not like '%ORA-12012%' and text not like '%ORA-06512%' and text not like '%ORA-02097%' and text not like '%ORA-00439%' and text not like '%coalesce%' and text not like '%Beginning log switch checkpoint up to RBA%' and text not like '%Completed checkpoint up to RBA%' and text not like '%specifies an obsolete parameter%' and text not like '%BEGIN BACKUP%' and text not like '%END BACKUP%' and text not like '%Starting%' and text not like '%autobackup%' and text not like '%handle%' ) loop

isdate  := 0; alert_text := null;

select count(*) into isdate from dual where substr(r.text, 21) in (to_char(sysdate, 'YYYY'), to_char(sysdate-365, 'YYYY')) and r.text not like '%cycle_run_year%'; -- De 21 à 24 ça doit être une année. Si oui c'est une date, sinon, c'est un libellé quelconque. if (isdate = 1) then -- prendre à partir du mois (car.5) - forcer NLS en AMERICAN pour eviter les erreurs de conversion ? Tous les alert.log sont en AMERICAN ? -- si besoin de prendre le NLS de la base, utiliser la variable &_nlsdate à la place d'AMERICAN select to_date(substr(r.text, 5),'Mon dd hh24:mi:ss rrrr','NLS_DATE_LANGUAGE = AMERICAN') into alert_date from dual;

if (alert_date > max_date) then start_updating := 1; end if; else alert_text := r.text; end if;

if (alert_text is not null) and (start_updating = 1) then

insert into alert_log values (alert_date, substr(alert_text, 1, &&alert_length)); rows_inserted := rows_inserted + 1; commit;

end if;

end loop;

-- sys.dbms_output.put_line('Inserting after date '||to_char(max_date, 'DD/MM/RR HH24:MI:SS')); -- sys.dbms_output.put_line('Rows Inserted: '||rows_inserted);

commit;

end; /

-- ************************************ Affichage des logs prompt

prompt set define off prompt prompt

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 '','', '',''

from alert_log a, (select max(to_date(date_aud)) date_aud from system.histaudit where to_date(date_aud) < trunc(sysdate)) d where (alert_text like '%ORA-%' or alert_text like '%Checkpoint not complete%') and a.alert_date > d.date_aud order by to_date(a.alert_date);

DECLARE cnt_obj number := 0; BEGIN select count(a.alert_date) into cnt_obj from alert_log a, (select max(to_date(date_aud)) date_aud from system.histaudit where to_date(date_aud) < trunc(sysdate)) d where (alert_text like '%ORA-%' or alert_text like '%Checkpoint not complete%') and a.alert_date > d.date_aud;

if cnt_obj=0 then

dbms_output.put_line('');

end if; end; /

prompt
prompt set define "&" prompt
  <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és, voir le détail dans le fichier alert<SID>.log, ou la table ALERT_LOG (résumé), ou la table externe ALERT_LOG_DISK (qui contient tout l'alert.log).">
Erreurs alert_&_db..log (&_bdump)
DateTexte
'||to_char(a.alert_date,'DD/MM/RR HH24:MI')||''||a.alert_text||'
N/AN/A

-- ****************** SECTION SCHEMAS - INFORMATIONS GLOBALES *************************

prompt
prompt
SECTION SCHEMAS
prompt

-- *************************************** Objets invalides prompt set define off

prompt prompt prompt

set define "&"

select '','','','','','' 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('');

end if; end; /

prompt
Objets invalides
PropriétaireObjetTypeStatut
'||OWNER||''||object_name||''||object_type||''||status||'
N/AN/AN/AN/A

-- *************************************** Indexes UNUSABLE prompt set define off

prompt prompt prompt

set define "&"

select '','','','','' 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('');

end if; end; /

prompt
Indexes UNUSABLE
PropriétaireIndexStatut
'||OWNER||''||index_name||''||status||'
N/AN/AN/A

-- *************************************** Liste des segments de plus de 100M prompt

set define off

prompt prompt prompt

set define "&"

select '','', '', '', '',''

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('');

end if; end; /

prompt
Liste des segments de plus de 100Mo
PropriétaireSegmentTypeTaille
'||owner||''||segment_name||''||segment_type||''||to_char(round(bytes/(1024*1024),0),'99G999G990')||' Mo
N/AN/AN/AN/A

-- *************************************** Segments fragmentes prompt set define off

prompt prompt prompt

set define "&"

select '','', '', '','', '','' 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('');

end if; EXCEPTION when NO_DATA_FOUND then

dbms_output.put_line('');

END; /

prompt
Liste des segments fragmentés (plus de 64 extents)
PropriétaireSegmentTypeLié àNombre d'extents
'||e.owner||''||e.segment_name||''||e.segment_type||''||decode (e.segment_type, 'INDEX', i.table_name,'--')||''||max(e.extent_id)||'
N/AN/AN/AN/AN/A
N/AN/AN/AN/AN/A

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

-- set define "&"

-- select '','', '', '','', '','' 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 '','', '', '','', '','' 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('');

-- end if; -- end; -- /

-- prompt
Tables/indexes atteignant 90% de la limite MAXEXTENTS
PropriétaireSegmentTypeTableNombre d'extents courantNombre maximum d'extents
'||e.owner||''||segment_name||''||segment_type||''||max(extent_id)||''||t.max_extents||'
'||e.owner||''||segment_name||''||segment_type||''||max(extent_id)||''||i.max_extents||'
N/AN/AN/AN/AN/AN/A

-- utilisateurs et tablespaces par defaut -- ************************************** set define off

prompt prompt prompt

prompt set define "&"

select '','','', '',''

from dba_users order by username;

prompt
utilisateurs et tablespaces par défaut
UtilisateursTablespace par défautTablespace temporaire
'||username||''||default_tablespace||''||temporary_tablespace||'


-- Liste des utilisateurs systemes non listes dans les variables sysusers et exusers -- ******************************** set define off

prompt prompt

set define "&"

prompt

prompt set define "&"

select '','',''

from dba_users where username not in &sysusers and username not in &exusers;

prompt
prompt prompt
  <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èmes Oracle, afin de les éliminer des requêtes qui ne doivent prendre en compte que les schémas applicatifs. Ici sont listés pour information les utilisateurs qui ne sont pas inclus dans ces variables, afin de repérer ceux qui devraient y être ajoutés.">
Information : Liste des utilisateurs non systèmes
Utilisateur
'||username||'

-- *************************************** Utilisateurs ayant des objets dans le tablespace SYSTEM prompt -- Tables

prompt set define off prompt prompt

set define "&"

select '','', '','','' TOTAL from dba_tables

where tablespace_name = 'SYSTEM' and owner not in &sysusers and owner not in &exusers group by owner; -- Indexes

select '','', '','','' 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('');

end if; end; /

prompt
Utilisateurs ayant des objets dans le tablespace SYSTEM
PropriétaireTypeSegment
'||owner||'Tables'||count(*)||'
'||owner||'Indexes'||count(*)||'
N/AN/AN/A

-- *************************************** Tables et index dans le meme tablespace prompt

prompt set define off prompt prompt

set define "&"

select '','', '', '', '',''

from dba_tables a, dba_indexes b where a.tablespace_name=b.tablespace_name and a.table_name=b.table_name and a.owner=b.owner and a.owner not in &sysusers and a.owner not in &exusers order by 1,2;

DECLARE cnt_obj number := 0; BEGIN select count(b.index_name) into cnt_obj from dba_tables a, dba_indexes b where a.tablespace_name=b.tablespace_name and a.table_name=b.table_name and a.owner=b.owner and a.owner not in &sysusers and a.owner not in &exusers and rownum = 1; if cnt_obj=0 then

dbms_output.put_line('');

end if; end; /

prompt
Tables et indexes dans le même tablespace
PropriétaireTablespaceTableIndex
'||a.owner||''||a.tablespace_name||''||a.table_name||''||b.index_name||'
N/AN/AN/AN/A

-- Roles par utilisateurs non systemes -- *********************************** set define off

prompt prompt prompt

prompt set define "&"

select '','', decode(granted_role,NULL,'',''),''

from dba_users, dba_role_privs where username not in &sysusers and username not in &exusers and username=grantee(+) order by 1,2;

prompt
Rôles par utilisateur (non systèmes)
UtilisateursRôles
'||username||'-'||granted_role||'

-- Liste des schemas vides (aucun objets) -- ************************************* set define off

prompt prompt prompt

prompt set define "&"

select '','','' 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('');

end if; end; /

prompt
Liste des schémas vides (aucun objet)
Scémas vides
'||username||'
N/A

-- Nombres d'objets par schemas (hors schemas systemes) -- *************************************************** set define off

prompt prompt prompt

prompt set define "&"

select '','', '', '', '', '',''

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
Nombre d'objets par schéma (non systèmes)
UtilisateurTotalTablesIndexesAutres
'||t.owner||''||to_char(decode(t.total,NULL,0,t.total),'99G999G990')||''||to_char(decode(a.tables,NULL,0,a.tables),'99G999G990')||''||to_char(decode(i.indexes,NULL,0,i.indexes),'99G999G990')||''||to_char(decode(o.autres,NULL,0,o.autres),'99G999G990')||'

-- Taille utilisee par les schemas (hors schemas systemes) -- ******************************************************* set define off

prompt prompt prompt

prompt set define "&"

select '','', '', '', '', '',''

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
Taille utilisée par les schémas (non systèmes)
UtilisateurTotal (Mo)Tables (Mo)Indexes (Mo)Autres (Mo)
'||t.owner||''||to_char(decode(t.total,NULL,0,t.total),'99G999G990D00')||''||to_char(decode(a.tables,NULL,0,a.tables),'99G999G990D00')||''||to_char(decode(i.indexes,NULL,0,i.indexes),'99G999G990D00')||''||to_char(decode(o.autres,NULL,0,o.autres),'99G999G990D00')||'

-- Liste des liens de bases de données -- *********************************** set define off

prompt prompt prompt

prompt set define "&"

select '','','','', '',''

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('');

end if; end; /

prompt
Liste des liens de bases de données
UtilisateurDB LinkUtilisateur distantServeur
'||owner||''||DB_LINK||''||USERNAME||''||HOST||'
N/AN/AN/AN/A

-- Liste des synonymes non systemes -- ******************************** set define off

prompt prompt prompt

prompt set define "&"

select '','', '', '', '',''

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('');

end if; end; /

prompt
Liste des synonymes (non systèmes)
UtilisateurSynonymePropriétaireObjet cible
'||owner||''||synonym_name||''||table_owner||''||table_name||'
N/AN/AN/AN/A

prompt </body> prompt </html>

spool off exit