Script d'audit de bases de données ORACLE

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
Date	Texte
',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étaire	Objet	Type	Statut
',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 ligne	Erreur
',),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étaire	Index	Statut
',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étaire	Segment	Type	Taille
',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
Utilisateurs	Tablespace par défaut	Tablespace 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étaire	Type	Segment
',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étaire	Tablespace	Nombre 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)
Utilisateurs	Rô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)
Utilisateur	Total	Tables	Indexes	Autres
',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)
Utilisateur	Total (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
Utilisateur	DB Link	Utilisateur distant	Serveur
',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)
Utilisateur	Synonyme	Propriétaire	Objet 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> -->