Introduction
Une base de données Oracle 11gr2 à sa création comporte à peu près 9000 tables et vues système différentes. Celles-ci composent le dictionnaire de données qui est la propriété du user SYS. Il va de soi qu'en cas de problème, trouver la bonne vue dans ce labyrinthe sera extrêmement difficile!

Prenons l'exemple d'un message d'erreur relatif à un trigger. Vous connaissez la vue DBA_TRIGGERS mais saviez-vous qu'il existe les vues DBA_INTERNAL_TRIGGERS, DBA_TRIGGER_ORDERING, DBA_TRIGGER_COLS? DICT vous aurait donné cette liste mais pas la vue LOADER_TRIGGER_INFO.

En formation DBA Oracle on vous dira peut-être que tout le dictionnaire de données est dans DICT (DICTIONARY de son vrai nom), mais, avec l'expérience, vous verrez que c'est loin, très très loin d'être le cas car d'autres vues et tables contiennent des objets inconnus de DICT.



Points d'attention
Les vues V$ étant des synonymes en grande majorité, elles apparaissent rarement dans les tables que nous allons utiliser. Il faudra alors, dans le résultat affiché, remplacer les GV$ ou GV_$ ou V_$ par V$ pour explorer ces vues. Exemple : remplacer GV$BUFFER_POOL par V$BUFFER_POOL.



Base de tests
N'importe quelle base Oracle.



 
Exemples
============================================================================================
Les tables et vues utiles pour explorer le dictionnaire de données Oracle.

============================================================================================
J'utilise une base sans aucun user/table/vue applicatifs pour éviter que ces objets ne se retrouvent dans les tables qu'on va examiner.

En premier, je vérifie qu'il n'y a que des users système dans ma base.
         SQL> select username from dba_users order by 1;
         USERNAME
         ------------------------------
         ANONYMOUS
         APEX_PUBLIC_USER
         APEX_030200
         APPQOSSYS
         CTXSYS
         DBSNMP
         DIP
         EXFSYS
         FLOWS_FILES
         MDDATA
         MDSYS
         MGMT_VIEW
         OLAPSYS
         ORACLE_OCM
         ORDDATA
         ORDPLUGINS
         ORDSYS
         OUTLN
         OWBSYS
         OWBSYS_AUDIT
         SI_INFORMTN_SCHEMA
         SPATIAL_CSW_ADMIN_USR
         SPATIAL_WFS_ADMIN_USR
         SYS
         SYSMAN
         SYSTEM
         WMSYS
         XDB
         XS$NULL
         29 ligne(s) sélectionnée(s).


Les tables pour explorer le dictionnaire de données.
Ensuite, regardons quelles sont les tables et vues à utiliser pour scruter le dictionnaire de données. J'ai volontairement exclu V$FIXED_TABLES car les noms des objets de cette vue sont incompréhensibles et ne peuvent pas servir pour une recherche.
          DICT
          DBA_ALL_TABLES
          DBA_TABLES
          DBA_VIEWS
          V$FIXED_VIEW_DEFINITION

Voyons d'abord quel est le nombre d'objets dans chaque vue; cela prouvera que tout n'est pas dans DICT.
         SQL> select count(*) from dict;
         COUNT(*)
         ----------
         2553

         SQL> select count(*) from dba_tables;
         COUNT(*)
         ----------
         2721

DBA_VIEWS comporte deux fois plus d'objets que DICT!
         SQL> select count(*) from dba_views;
         COUNT(*)
         ----------
         5028

         SQL> select count(*) from dba_all_tables;
         COUNT(*)
         ----------
         2789

         SQL> select count(*) from V$FIXED_VIEW_DEFINITION;
         COUNT(*)
         ----------
         1023

Regardons maintenant si certaines vues ont des objets qui leur sont propres et qui sont absents des autres vues; comparons avec DICT.
         SQL> select count(*) from dba_tables where table_name not in (select table_name from dict);
         COUNT(*)
         ----------
         2717

         SQL> select count(*) from dba_views where view_name not in (select table_name from dict) order by 1;
         COUNT(*)
         ----------
         3596

         SQL> select count(*) from dba_all_tables where table_name not in (select table_name from dict) order by 1;
         COUNT(*)
         ----------
         2785

Nous voyons que beaucoup d'objets ne sont pas dans DICT. En revanche DBA_ALL_TABLES est quasiment contenue dans DBA_TABLES.
         SQL> select count(*) from dba_all_tables where table_name not in (select table_name from dba_tables) order by 1;
         COUNT(*)
         ----------
         68

Dernier exemple : tout le contenu de V$FIXED_VIEW_DEFINITION est absent de DBA_TABLES.
         SQL> select count(*) from V$FIXED_VIEW_DEFINITION where view_name not in (select table_name from dba_tables) order by 1;
         COUNT(*)
         ----------
         1023

Nombre de tables et vues dans ces méta-tables.
Combien de tables et vues différentes dans les 5 objets ci-dessus?
Sur un total de 8960 tables et vues système, il y en a seulement 2553 dans DICT... soit un écart de 6407 objets; cela prouve que DICT est bien mais totalement insuffisant pour rechercher des objets.
         SELECT TABLE_NAME FROM DICT
         UNION
         SELECT TABLE_NAME FROM DBA_TABLES
         UNION
         SELECT TABLE_NAME FROM DBA_ALL_TABLES
         UNION
         SELECT VIEW_NAME FROM DBA_VIEWS
         UNION
         SELECT VIEW_NAME FROM V$FIXED_VIEW_DEFINITION
         ORDER BY 1;

         ...
         8960 ligne(s) sélectionnée(s).

Et si on garde les doublons, on passe de 8960 objets à 14116 soit 5156 objets en double voir en triple ou pire dans diverses vues.
         SELECT TABLE_NAME FROM DICT
         UNION ALL
         SELECT TABLE_NAME FROM DBA_TABLES
         UNION ALL
         SELECT TABLE_NAME FROM DBA_ALL_TABLES
         UNION ALL
         SELECT VIEW_NAME FROM DBA_VIEWS
         UNION ALL
         SELECT VIEW_NAME FROM V$FIXED_VIEW_DEFINITION
         ORDER BY 1;
        
         ...
         14116 ligne(s) sélectionnée(s).


============================================================================================
Exemples de recherche sur les noms de vues et de tables.

============================================================================================

Comment fonctionne mon script? De façon très simple :-) Il suffit de lancer le .sql à partir de l'OS et, au prompt demandant de saisir une valeur, de renseigner la chaîne de caractère qu'on veut trouver dans le nom des tables et vues du dictionnaire de données.

Toutes les tables et vues affichées ne sont pas forcément utiles mais elles permettent de voir des objets dont on n'avait pas forcément connaissance.
Attention, certaines sont en doublon malgré le UNION au lieu du UNION ALL car on affiche aussi bien GV$TEMP_EXTENT_POOL que GV_$TEMP_EXTENT_POOL qui sont des synonymes.

Script TABLES.sql
          SELECT TABLE_NAME FROM DICT where UPPER(table_name) like UPPER('%&1%')
          UNION
          SELECT TABLE_NAME FROM DBA_TABLES where UPPER(table_name) like UPPER('%&1%')
          UNION
          SELECT TABLE_NAME FROM DBA_ALL_TABLES where UPPER(table_name) like UPPER('%&1%')
          UNION
          SELECT VIEW_NAME FROM DBA_VIEWS where UPPER(view_name) like UPPER('%&1%')
          UNION
          SELECT VIEW_NAME FROM V$FIXED_VIEW_DEFINITION where UPPER(view_name) like UPPER('%&1%')
          ORDER BY 1;

Exemple pour la chaîne EXTENT.
          SQL> @F:\Tables
          Entrez une valeur pour 1 : EXTENT
          ancien 1 : SELECT TABLE_NAME FROM DICT where UPPER(table_name) like UPPER('%&&1%')
          nouveau 1 : SELECT TABLE_NAME FROM DICT where UPPER(table_name) like UPPER('%EXTENT%')
          ancien 3 : SELECT TABLE_NAME FROM DBA_TABLES where UPPER(table_name) like UPPER('%&1%')
          nouveau 3 : SELECT TABLE_NAME FROM DBA_TABLES where UPPER(table_name) like UPPER('%EXTENT%')
          ancien 5 : SELECT TABLE_NAME FROM DBA_ALL_TABLES where UPPER(table_name) like UPPER('%&1%')
          nouveau 5 : SELECT TABLE_NAME FROM DBA_ALL_TABLES where UPPER(table_name) like UPPER('%EXTENT%')
          ancien 7 : SELECT VIEW_NAME FROM DBA_VIEWS where UPPER(view_name) like UPPER('%&1%')
          nouveau 7 : SELECT VIEW_NAME FROM DBA_VIEWS where UPPER(view_name) like UPPER('%EXTENT%')
          ancien 9 : SELECT NAME FROM V$FIXED_TABLE where UPPER(name) like UPPER('%&1%')
          nouveau 9 : SELECT NAME FROM V$FIXED_TABLE where UPPER(name) like UPPER('%EXTENT%')
          ancien 11 : SELECT VIEW_NAME FROM V$FIXED_VIEW_DEFINITION where UPPER(view_name) like UPPER('%&1%')
          nouveau 11 : SELECT VIEW_NAME FROM V$FIXED_VIEW_DEFINITION where UPPER(view_name) like UPPER('%EXTENT%')

         TABLE_NAME
         ------------------------------
         DBA_DMT_USED_EXTENTS
         DBA_EXTENTS
         DBA_LMT_USED_EXTENTS
         DBA_UNDO_EXTENTS
         DBMS_PARALLEL_EXECUTE_EXTENTS
         GV$LOGMNR_EXTENTS
         GV$MAP_FILE_EXTENT
         GV_$MAP_FILE_EXTENT
         GV$TEMP_EXTENT_MAP
         GV_$TEMP_EXTENT_MAP
         GV$TEMP_EXTENT_POOL
         GV_$TEMP_EXTENT_POOL
         MAP_FILE_EXTENT$
         USER_EXTENTS
         V$LOGMNR_EXTENTS
         V$MAP_FILE_EXTENT
         V_$MAP_FILE_EXTENT
         V$TEMP_EXTENT_MAP
         V_$TEMP_EXTENT_MAP
         V$TEMP_EXTENT_POOL
         V_$TEMP_EXTENT_POOL
         21 ligne(s) sélectionnée(s).

Autre test avec la chaîne HINT (je n'affiche que le résultat). Vous connaissiez la table V$SQL_HINT? Elle est apparue en 11g.
         SQL> @F:\Tables
         TABLE_NAME
         ------------------------------
         ALL_OUTLINE_HINTS
         DBA_OUTLINE_HINTS
         GV$SQL_HINT
         GV_$SQL_HINT
         OL$HINTS
         USER_OUTLINE_HINTS
         V$SQL_HINT
         V_$SQL_HINT
         WM$HINT_TABLE
         9 ligne(s) sélectionnée(s).


J'espère que ce script vous permettra de trouver dans la jungle du dictionnaire de données les tables et vues nécessaires pour bien faire votre travail de DBA Oracle ou de développeur.