Outils personnels

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

De wikiGite

Ligne 6 : Ligne 6 :
  
 
Voir aussi le [[Script_d'audit_de_bases_de_données_MySQL | script d'audit MySQL]]
 
Voir aussi le [[Script_d'audit_de_bases_de_données_MySQL | script d'audit MySQL]]
 +
 +
09/2018 : transféré sur Github
 +
https://github.com/fsoyer/auditOracleHTML
 +
 +
<!-- ARCHIVE
  
 
<syntaxhighlight lang="sql" enclose="div">
 
<syntaxhighlight lang="sql" enclose="div">
Ligne 2 213 : Ligne 2 218 :
 
exit
 
exit
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
-->

Version du 20 septembre 2018 à 08:55

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

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

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

Voir aussi le script d'audit MySQL

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


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

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%'
 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('');
  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).">
&_bdump/alert_&_db..log
DateTexte
',to_char(a.alert_date,'DD/MM/RR HH24:MI'),'',a.alert_text,'
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20>

-- *************************************** Nettoyage de la table "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';
  EXECUTE IMMEDIATE 'truncate table alert_log';

END; /

-- ****************** 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,'
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20>

-- *************************************** Objets en erreur

prompt set define off prompt prompt

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,'' text -- Num erreur PL/SQL
       from err
     union all
     select distinct -1 n, owner, name, type, line, ''||type||' '||owner||'.'||name||' line '||line||'
' --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></tr>' text -- Num erreur PL/SQL
       from err
     union all
     select distinct -1 n, owner, name, type, line, ''||type||' '||owner||'.'||name||' line '||line||'
' --Num ligne from err union all select 0 n, owner, name, type, line, '"'||substr(TRIM(text),0,240)||'" : ' -- 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('');
  end if;

end; /

set define "&"

prompt
Erreurs sur les objets utilisateurs (dba_errors)
Objet, numéro et texte de la ligneErreur
',),text
 from (
select sequence n, owner,name, type,line, (position-1)||'
'||text||'
'||text||'
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20>

-- *************************************** 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,'
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20>

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

print info

prompt " width="20" height="20" alt="Info..." title="L'espace utilisé correspond aux blocs alloués au segment, qu'ils soient vides (préallocation de blocs ou suppressions de données) ou remplis.">
Liste des segments de plus de 100Mo
PropriétaireSegmentTypeTaille
',owner,'',segment_name,'',segment_type,'',to_char(round(bytes/(1024*1024),0),'99G999G990'),' Mo
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20>

-- 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(*),'
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20>

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

end; /

prompt
Tables et indexes dans le même tablespace
PropriétaireTablespaceNombre d'objets
',a.owner,'',a.tablespace_name,'',count(a.table_name),'
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20>

-- 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,'<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20>'||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)
Schémas vides
',username,'
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20>

-- 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,'
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20>

-- 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,'
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" width=20>

prompt </body> prompt </html>

spool off exit </syntaxhighlight> -->