Introduction
On va voir aujourd'hui où trouver des informations sur les vues et tables du dictionnaire de données d'Oracle. Je prendrais comme exemple une vue fameuse, DBA_TABLES, et vous verrez que ces infos sont parfois bien cachées.


 

Points d'attention
NA.


 

Base de tests
N'importe quelle base Oracle.

 


 

Exemples
============================================================================================
Trouver les infos basiques sur les vues du dictionnaire de données
============================================================================================
La commande DESC
Les premières infos sur une vue ou une table s'obtiennent avec la commande DESC (DESCRIBE) de SQL*Plus. Celle-ci affiche le nom des colonnes, si sa valeur peut être NULL ou non et son type.

Que manque t-il? Un commentaire sur le rôle de chaque colonne, la liste des contraintes d'intégrité ( sauf NULL), la liste des valeurs possibles pour une colonne quand celles-ci sont imposées par Oracle (par exemple la colonne STATUS).
          SQL> desc dba_tables
          Name                             Null?    Type
          ------------------------------------------------------
          OWNER                            NOT NULL VARCHAR2(128)
          TABLE_NAME                       NOT NULL VARCHAR2(128)
          TABLESPACE_NAME                           VARCHAR2(30)
          CLUSTER_NAME                              VARCHAR2(128)
          IOT_NAME                                  VARCHAR2(128)
          STATUS                                    VARCHAR2(8)
          PCT_FREE                                  NUMBER
          PCT_USED                                  NUMBER
          INI_TRANS                                 NUMBER
          MAX_TRANS                                 NUMBER
          INITIAL_EXTENT                            NUMBER
          NEXT_EXTENT                               NUMBER
          MIN_EXTENTS                               NUMBER
          MAX_EXTENTS                               NUMBER
          PCT_INCREASE                              NUMBER
          FREELISTS                                 NUMBER
          FREELIST_GROUPS                           NUMBER
          LOGGING                                   VARCHAR2(3)
          BACKED_UP                                 VARCHAR2(1)
          NUM_ROWS                                  NUMBER
          BLOCKS                                    NUMBER
          EMPTY_BLOCKS                              NUMBER
          AVG_SPACE                                 NUMBER
          CHAIN_CNT                                 NUMBER
          AVG_ROW_LEN                               NUMBER
          AVG_SPACE_FREELIST_BLOCKS                 NUMBER
          NUM_FREELIST_BLOCKS                       NUMBER
          DEGREE                                    VARCHAR2(40)
          INSTANCES                                 VARCHAR2(40)
          CACHE                                     VARCHAR2(20)
          TABLE_LOCK                                VARCHAR2(8)
          SAMPLE_SIZE                               NUMBER
          LAST_ANALYZED                             DATE
          PARTITIONED                               VARCHAR2(3)
          IOT_TYPE                                  VARCHAR2(12)
          TEMPORARY                                 VARCHAR2(1)
          SECONDARY                                 VARCHAR2(1)
          NESTED                                    VARCHAR2(3)
          BUFFER_POOL                               VARCHAR2(7)
          FLASH_CACHE                               VARCHAR2(7)
          CELL_FLASH_CACHE                          VARCHAR2(7)
          ROW_MOVEMENT                              VARCHAR2(8)
          GLOBAL_STATS                              VARCHAR2(3)
          USER_STATS                                VARCHAR2(3)
          DURATION                                  VARCHAR2(15)
          SKIP_CORRUPT                              VARCHAR2(8)
          MONITORING                                VARCHAR2(3)
          CLUSTER_OWNER                             VARCHAR2(128)
          DEPENDENCIES                              VARCHAR2(8)
          COMPRESSION                               VARCHAR2(8)
          COMPRESS_FOR                              VARCHAR2(30)
          DROPPED                                   VARCHAR2(3)
          READ_ONLY                                 VARCHAR2(3)
          SEGMENT_CREATED                           VARCHAR2(3)
          RESULT_CACHE                              VARCHAR2(7)
          CLUSTERING                                VARCHAR2(3)
          ACTIVITY_TRACKING                         VARCHAR2(23)
          DML_TIMESTAMP                             VARCHAR2(25)
          HAS_IDENTITY                              VARCHAR2(3)
          CONTAINER_DATA                            VARCHAR2(3)
          INMEMORY                                  VARCHAR2(8)
          INMEMORY_PRIORITY                         VARCHAR2(8)
          INMEMORY_DISTRIBUTE                       VARCHAR2(15)
          INMEMORY_COMPRESSION                      VARCHAR2(17)
          INMEMORY_DUPLICATE                        VARCHAR2(13)
           

Le site d'Oracle
Le site d'Oracle donne des infos plus fournies que le DESC car on a un commentaire sur le rôle de chaque colonne. Pour les tables DBA_, Oracle a choisi de rediriger vers les tables ALL_ dont acte : https://docs.oracle.com/database/121/REFRN/GUID-6823CD28-0681-468E-950B-966C6F71325D.htm#REFRN20286. La description est "DBA_TABLES describes all relational tables in the database. Its columns are the same as those in ALL_TABLES".

Quelles sont les infos disponibles? le nom de la colonne, son datatype, si elle peut être NULL ou non et un bref commentaire, donc plus que le DESC. En voici un extrait.
     Column        Datatype      NULL       Description
     ---------------------------------------------------------
     OWNER         VARCHAR2(128) NOT NULL   Owner of the table
     TABLE_NAME    VARCHAR2(128) NOT NULL   Name of the table
          ...


Les commentaires dans le dictionnaire de données
Une autre source d'informations est la vue DBA_TAB_COMMENTS et la vue DBA_COLS_COMMENTS. Ces vues stockent les commentaires associées aux tables et colonnes. Pour DBA_TABLES, les commentaires sont les mêmes que sur le site d'Oracle.
          SQL> select COMMENTS from dba_tab_comments where TABLE_NAME = 'DBA_TABLES';
          COMMENTS
          -------------------------------------------------------------------------------------------------------------------------------------
          Description of all relational tables in the database

          SQL> select column_name, COMMENTS from dba_col_comments where TABLE_NAME = 'DBA_TABLES' order by column_name;
     COLUMN_NAME                    COMMENTS
     ------------------------------ --------------------------------
     OWNER                          Owner of the table
     TABLE_NAME                     Name of the table
          ...


============================================================================================
Les infos plus détaillées mais cryptées sur les vues du dictionnaire de données
============================================================================================

Passons maintenant à d'autres techniques pour avoir d'autres infos, elles sont plus poussées mais les résultats sont plus difficiles à interpréter.

Le SELECT de l'ordre DDL de définition des vues
On l'oublie trop souvent mais les tables DBA_xxx, ALL_xxx, USERS_xxx, sont en réalité des vues et, comme telles, elles sont définies par un SELECT. Pour voir celui-ci, utilisons le package DBMS_METADATA pour avoir l'ordre DDL de définition de cette vue.

Attention, c'est du très très lourd!

Avant de vous afficher cet ordre interminable, que peut-on dire?
     - Que c'est quasiment illisible :-)
     - Que les vrais noms des colonnes sont quasi-cryptés
     - Qu'on a la liste des valeurs possibles pour plusieurs colonnes
     - Que cette vue interroge plusieurs tables du dictionnaire de données et pas seulement tab$, la vraie table du dictionnaire qui gère les tables :
            sys.user$
            sys.ts$
            sys.seg$
            sys.tab$
            sys.obj$
            x$ksppcv
            x$ksppi
            sys.deferred_stg$


Bon, ben, c'est parti :-)

          SQL> select dbms_metadata.get_ddl('VIEW', 'DBA_TABLES') from dual;
          DBMS_METADATA.GET_DDL('VIEW','DBA_TABLES')
          --------------------------------------------------------------------------------
          CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."DBA_TABLES" ("OWNER", "TABL
          E_NAME", "TABLESPACE_NAME", "CLUSTER_NAME", "IOT_NAME", "STATUS", "PCT_FREE", "P
          CT_USED", "INI_TRANS", "MAX_TRANS", "INITIAL_EXTENT", "NEXT_EXTENT", "MIN_EXTENT
          S", "MAX_EXTENTS", "PCT_INCREASE", "FREELISTS", "FREELIST_GROUPS", "LOGGING", "B
          ACKED_UP", "NUM_ROWS", "BLOCKS", "EMPTY_BLOCKS", "AVG_SPACE", "CHAIN_CNT", "AVG_
          ROW_LEN", "AVG_SPACE_FREELIST_BLOCKS", "NUM_FREELIST_BLOCKS", "DEGREE", "INSTANC
          ES", "CACHE", "TABLE_LOCK", "SAMPLE_SIZE", "LAST_ANALYZED", "PARTITIONED", "IOT_
          TYPE", "TEMPORARY", "SECONDARY", "NESTED", "BUFFER_POOL", "FLASH_CACHE", "CELL_F
          LASH_CACHE", "ROW_MOVEMENT", "GLOBAL_STATS", "USER_STATS", "DURATION", "SKIP_COR
          RUPT", "MONITORING", "CLUSTER_OWNER", "DEPENDENCIES", "COMPRESSION", "COMPRESS_F
          OR", "DROPPED", "READ_ONLY", "SEGMENT_CREATED", "RESULT_CACHE", "CLUSTERING", "A
          CTIVITY_TRACKING", "DML_TIMESTAMP", "HAS_IDENTITY", "CONTAINER_DATA", "INMEMORY"
          , "INMEMORY_PRIORITY", "INMEMORY_DISTRIBUTE", "INMEMORY_COMPRESSION", "INMEMORY_
          DUPLICATE") AS
            select u.name, o.name,
                 decode(bitand(t.property,2151678048), 0, ts.name,
                        decode(t.ts#, 0, null, ts.name)),
                 decode(bitand(t.property, 1024), 0, null, co.name),
                 decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
                        0, null, co.name),
                 decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),
                 decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
                 decode(bitand(ts.flags, 32), 32, to_number(NULL),
                    decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
                 decode(bitand(t.property, 32), 0, t.initrans, null),
                 decode(bitand(t.property, 32), 0, t.maxtrans, null),
                 decode(bitand(t.property, 17179869184), 17179869184,
                               ds.initial_stg * ts.blocksize,
                               s.iniexts * ts.blocksize),
                 decode(bitand(t.property, 17179869184), 17179869184,
                        ds.next_stg * ts.blocksize,
                        s.extsize * ts.blocksize),
                 decode(bitand(t.property, 17179869184), 17179869184,
                        ds.minext_stg, s.minexts),
                 decode(bitand(t.property, 17179869184), 17179869184,
                        ds.maxext_stg, s.maxexts),
                 decode(bitand(ts.flags, 3), 1, to_number(NULL),
                        decode(bitand(t.property, 17179869184), 17179869184,
                                      ds.pctinc_stg, s.extpct)),
                 decode(bitand(ts.flags, 32), 32, to_number(NULL),
                   decode(bitand(o.flags, 2), 2, 1,
                          decode(bitand(t.property, 17179869184), 17179869184,
                                 decode(ds.frlins_stg, 0, 1, ds.frlins_stg),
                                 decode(s.lists, 0, 1, s.lists)))),
                 decode(bitand(ts.flags, 32), 32, to_number(NULL),
                   decode(bitand(o.flags, 2), 2, 1,
                          decode(bitand(t.property, 17179869184), 17179869184,
                                 decode(ds.maxins_stg, 0, 1, ds.maxins_stg),
                                 decode(s.groups, 0, 1, s.groups)))),
                 decode(bitand(t.property, 32+64), 0,
                          decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),
                 decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
                 t.rowcnt,
                 decode(bitand(t.property, 64), 0, t.blkcnt, null),
                 decode(bitand(t.property, 64), 0, t.empcnt, null),
                 t.avgspc, t.chncnt, t.avgrln, t.avgspc_flb,
                 decode(bitand(t.property, 64), 0, t.flbcnt, null),
                 lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
                 lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
                 lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
                 decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
                 t.samplesize, t.analyzetime,
                 decode(bitand(t.property, 32), 32, 'YES', 'NO'),
                 decode(bitand(t.property, 64), 64, 'IOT',
                         decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
                         decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null
          ))),
                 decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
                 decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
                 decode(bitand(t.property, 8192), 8192, 'YES',
                        decode(bitand(t.property, 1), 0, 'NO', 'YES')),
                 decode(bitand(o.flags, 2), 2, 'DEFAULT',
                        decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
           
                                      ds.bfp_stg, s.cachehint), 3),
                                      1, 'KEEP', 2, 'RECYCLE', 'DEFAULT')),
                 decode(bitand(o.flags, 2), 2, 'DEFAULT',
                        decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
           
                                      ds.bfp_stg, s.cachehint), 12)/4,
                                      1, 'KEEP', 2, 'NONE', 'DEFAULT')),
                 decode(bitand(o.flags, 2), 2, 'DEFAULT',
                        decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
           
                                      ds.bfp_stg, s.cachehint), 48)/16,
                                      1, 'KEEP', 2, 'NONE', 'DEFAULT')),
                 decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
                 decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
                 decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
                 decode(bitand(o.flags, 2), 0, NULL,
                    decode(bitand(t.property, 8388608), 8388608,
                           'SYS$SESSION', 'SYS$TRANSACTION')),
                 decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
                 decode(bitand(o.flags, 2), 2, 'NO',
                     decode(bitand(t.property, 2147483648), 2147483648, 'NO',
                        decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),
                 decode(bitand(t.property, 1024), 0, null, cu.name),
                 decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),
                 case when (bitand(t.property, 32) = 32) then
                   null
                 when (bitand(t.property, 17179869184) = 17179869184) then
                    decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED')
                 else
                   decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')
                 end,
                 case when (bitand(t.property, 32) = 32) then
                   null
                 when (bitand(t.property, 17179869184) = 17179869184) then
                    decode(bitand(ds.flags_stg, 4), 4,
                    case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC'
                         when bitand(ds.cmpflag_stg, 3) = 2 then 'ADVANCED'
                         else concat(decode(ds.cmplvl_stg, 1, 'QUERY LOW',
                                                           2, 'QUERY HIGH',
                                                           3, 'ARCHIVE LOW',
                                                              'ARCHIVE HIGH'),
                                     decode(bitand(ds.flags_stg, 524288), 524288,
                                            ' ROW LEVEL LOCKING', '')) end,
                         null)
                else
                   decode(bitand(s.spare1, 2048), 0, null,
                   case when bitand(s.spare1, 16777216) = 16777216
                             then 'ADVANCED'
                        when bitand(s.spare1, 100663296) = 33554432  -- 0x2000000
                             then concat('QUERY LOW',
                                         decode(bitand(s.spare1, 2147483648),
                                                2147483648, ' ROW LEVEL LOCKING', ''))
                        when bitand(s.spare1, 100663296) = 67108864  -- 0x4000000
                             then concat('QUERY HIGH',
                                         decode(bitand(s.spare1, 2147483648),
                                                2147483648, ' ROW LEVEL LOCKING', ''))
                        when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x400000
          0
                             then concat('ARCHIVE LOW',
                                         decode(bitand(s.spare1, 2147483648),
                                                2147483648, ' ROW LEVEL LOCKING', ''))
                        when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000
                             then concat('ARCHIVE HIGH',
                                         decode(bitand(s.spare1, 2147483648),
                                                2147483648, ' ROW LEVEL LOCKING', ''))
                        else 'BASIC' end)
                 end,
                 decode(bitand(o.flags, 128), 128, 'YES', 'NO'),
                 decode(bitand(t.trigflag, 2097152), 2097152, 'YES', 'NO'),
                 decode(bitand(t.property, 17179869184), 17179869184, 'NO',
                        decode(bitand(t.property, 32), 32, 'N/A', 'YES')),
                 decode(bitand(t.property,16492674416640),2199023255552,'FORCE',
                          4398046511104,'MANUAL','DEFAULT'),
                 decode(bitand(t.property, 18014398509481984), 18014398509481984,
                               'YES', 'NO'),
                 case when bitand(t.property, 1125899906842624) = 1125899906842624
                           then 'ROW ACCESS TRACKING'
                      when bitand(t.property, 2251799813685248) = 2251799813685248
                           then 'SEGMENT ACCESS TRACKING'
                  end,
                 case when bitand(t.property, 844424930131968) = 844424930131968
                           then 'ROW CREATION/MODIFICATION'
                      when bitand(t.property, 281474976710656) = 281474976710656
                           then 'ROW MODIFICATION'
                      when bitand(t.property, 562949953421312) = 562949953421312
                           then 'ROW CREATION'
                  end,
                 decode(bitand(t.property, 288230376151711744), 288230376151711744,
                        'YES', 'NO'),
                 decode(bitand(t.property/4294967296, 134217728), 134217728, 'YES', 'NO'),
           
                 -- INMEMORY
                 case when (bitand(t.property, 32) = 32) then
                   null
                 when (bitand(t.property, 17179869184) = 17179869184) then
                   -- flags/imcflag_stg (stgdef.h)
                   decode(bitand(ds.flags_stg, 6291456),
                       2097152, 'ENABLED',
                       4194304, 'DISABLED', 'DISABLED')
                 else
                   -- ktsscflg (ktscts.h)
                   decode(bitand(s.spare1, 70373039144960),
                       4294967296,     'ENABLED',
                       70368744177664, 'DISABLED', 'DISABLED')
                 end,
                 -- INMEMORY_PRIORITY
                 case when (bitand(t.property, 32) = 32) then
                   null
                 when (bitand(t.property, 17179869184) = 17179869184) then
                   decode(bitand(ds.flags_stg, 2097152), 2097152,
                          decode(bitand(ds.imcflag_stg, 4), 4,
                          decode(bitand(ds.flags_stg, 2097152), 2097152,
                          decode(bitand(ds.imcflag_stg, 7936),
                          256, 'NONE',
                          512, 'LOW',
                          1024, 'MEDIUM',
                          2048, 'HIGH',
                          4096, 'CRITICAL', 'UNKNOWN'), null),
                          'NONE'),
                          null)
                 else
                   decode(bitand(s.spare1, 4294967296), 4294967296,
                          decode(bitand(s.spare1, 34359738368), 34359738368,
                          decode(bitand(s.spare1, 61572651155456),
                          8796093022208, 'LOW',
                          17592186044416, 'MEDIUM',
                          35184372088832, 'HIGH',
                          52776558133248, 'CRITICAL', 'NONE'),
                          'NONE'),
                          null)
                 end,
                 -- INMEMORY_DISTRIBUTE
                 case when (bitand(t.property, 32) = 32) then
                   null
                 when (bitand(t.property, 17179869184) = 17179869184) then
                   decode(bitand(ds.flags_stg, 2097152), 2097152,
                          decode(bitand(ds.imcflag_stg, 1), 1,
                                 decode(bitand(ds.imcflag_stg, (16+32)),
                                        16,  'BY ROWID RANGE',
                                        32,  'BY PARTITION',
                                        48,  'BY SUBPARTITION',
                                         0,  'AUTO'),
                            null), null)
                 else
                   decode(bitand(s.spare1, 4294967296), 4294967296,
                          decode(bitand(s.spare1, 8589934592), 8589934592,
                                  decode(bitand(s.spare1, 206158430208),
                                  68719476736,   'BY ROWID RANGE',
                                  137438953472,  'BY PARTITION',
                                  206158430208,  'BY SUBPARTITION',
                                  0,             'AUTO'),
                                  'UNKNOWN'),
                            null)
                 end,
                 -- INMEMORY_COMPRESSION
                 case when (bitand(t.property, 32) = 32) then
                   null
                 when (bitand(t.property, 17179869184) = 17179869184) then
                   decode(bitand(ds.flags_stg, 2097152), 2097152,
                          decode(bitand(ds.imcflag_stg, (2+8+64+128)),
                                        2,   'NO MEMCOMPRESS',
                                        8,  'FOR DML',
                                        10,  'FOR QUERY LOW',
                                        64, 'FOR QUERY HIGH',
                                        66, 'FOR CAPACITY LOW',
                                        72, 'FOR CAPACITY HIGH', 'UNKNOWN'),
                          null)
                 else
                   decode(bitand(s.spare1, 4294967296), 4294967296,
                          decode(bitand(s.spare1, 841813590016),
                                        17179869184,  'NO MEMCOMPRESS',
                                        274877906944, 'FOR DML',
                                        292057776128, 'FOR QUERY LOW',
                                        549755813888, 'FOR QUERY HIGH',
                                        566935683072, 'FOR CAPACITY LOW',
                                        824633720832, 'FOR CAPACITY HIGH', 'UNKNOWN'),
                           null)
                 end,
                 -- INMEMORY_DUPLICATE
                 case when (bitand(t.property, 32) = 32) then
                   null
                 when (bitand(t.property, 17179869184) = 17179869184) then
                  decode(bitand(ds.flags_stg, 2097152), 2097152,
                         decode(bitand(ds.imcflag_stg, (8192+16384)),
                                        8192,   'NO DUPLICATE',
                                        16384,  'DUPLICATE',
                                        24576,  'DUPLICATE ALL',
                                        'UNKNOWN'),
                          null)
                 else
                    decode(bitand(s.spare1, 4294967296), 4294967296,
                             decode(bitand(s.spare1, 6597069766656),
                                     2199023255552, 'NO DUPLICATE',
                                     4398046511104, 'DUPLICATE',
                                     6597069766656, 'DUPLICATE ALL', 'UNKNOWN'),
                           null)
                 end
          from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
               sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi,
               sys.deferred_stg$ ds
          where o.owner# = u.user#
            and o.obj# = t.obj#
            and bitand(t.property, 1) = 0
            and bitand(o.flags, 128) = 0
            and t.bobj# = co.obj# (+)
            and t.ts# = ts.ts#
            and t.obj# = ds.obj# (+)
            and t.file# = s.file# (+)
            and t.block# = s.block# (+)
            and t.ts# = s.ts# (+)
            and t.dataobj# = cx.obj# (+)
            and cx.owner# = cu.user# (+)
            and ksppi.indx = ksppcv.indx
            and ksppi.ksppinm = '_dml_monitoring_enabled'
           

============================================================================================
Les infos sur les tables du dictionnaire de données : "vous qui entrez ici, abandonnez tout espoir"*  *Dante!
============================================================================================

On a vu que DBA_TABLES s'appuie sur plusieurs tables du dictionnaire de données dont l'une est particulièrement intéressante : tab$.

La commande DESC
Que nous donnent les techniques classiques? Pas grand chose à vrai dire... Le DESC donne le nom des colonnes dont certaines sont cryptées comme SPARE1, SPARE2... mais rien de plus.
        SQL> desc SYS.tab$
         Name                  Null?    Type
         -----------------------------------------------------------------
         OBJ#                 NOT NULL NUMBER
         DATAOBJ#                      NUMBER
         TS#                 NOT NULL NUMBER
         FILE#                 NOT NULL NUMBER
         BLOCK#              NOT NULL NUMBER
         BOBJ#                          NUMBER
         TAB#                          NUMBER
         COLS                 NOT NULL NUMBER
         CLUCOLS                      NUMBER
         PCTFREE$             NOT NULL NUMBER
         PCTUSED$             NOT NULL NUMBER
         INITRANS             NOT NULL NUMBER
         MAXTRANS             NOT NULL NUMBER
         FLAGS                 NOT NULL NUMBER
         AUDIT$              NOT NULL VARCHAR2(38)
         ROWCNT                       NUMBER
         BLKCNT                       NUMBER
         EMPCNT                       NUMBER
         AVGSPC                       NUMBER
         CHNCNT                       NUMBER
         AVGRLN                       NUMBER
         AVGSPC_FLB                      NUMBER
         FLBCNT                       NUMBER
         ANALYZETIME                  DATE
         SAMPLESIZE                      NUMBER
         DEGREE                       NUMBER
         INSTANCES                      NUMBER
         INTCOLS             NOT NULL NUMBER
         KERNELCOLS             NOT NULL NUMBER
         PROPERTY             NOT NULL NUMBER
         TRIGFLAG                      NUMBER
         SPARE1                       NUMBER
         SPARE2                       NUMBER
         SPARE3                       NUMBER
         SPARE4                       VARCHAR2(1000)
         SPARE5                       VARCHAR2(1000)
         SPARE6                       DATE
         SPARE7                       NUMBER
         SPARE8                       NUMBER
         SPARE9                       VARCHAR2(1000)
         SPARE10                      VARCHAR2(1000)
         ACDRFLAGS                      NUMBER
         ACDRTSOBJ#                      NUMBER
         ACDRDEFAULTTIME              TIMESTAMP(6)
         ACDRROWTSINTCOL#              NUMBER


Le site d'Oracle
Comme il s'agit d'une vraie table interne d'Oracle (elle se termine par le caractère $), le site officiel d'Oracle ne donne aucune info sur celle-ci. Pourquoi? Parce que Oracle veut que vous utilisiez les vues sur le dictionnaire de données et pas ses tables; de la sorte, des évolutions sur les tables impactent moins les ordres manipulant ces données.


 
Le SELECT de l'ordre DDL de définition des vues
Si on récupère l'ordre DDL de cette table, on a un CREATE TABLE simple, sans plus d'infos que le DESC.     
        SQL> select dbms_metadata.get_ddl('TABLE', 'TAB$') from dual;
        DBMS_METADATA.GET_DDL('TABLE','TAB$')
        --------------------------------------------------------------------------------
          CREATE TABLE "SYS"."TAB$" SHARING=METADATA
           (    "OBJ#" NUMBER NOT NULL ENABLE,
            "DATAOBJ#" NUMBER,
            "TS#" NUMBER NOT NULL ENABLE,
            "FILE#" NUMBER NOT NULL ENABLE,
            "BLOCK#" NUMBER NOT NULL ENABLE,
            "BOBJ#" NUMBER,
            "TAB#" NUMBER,
            "COLS" NUMBER NOT NULL ENABLE,
            "CLUCOLS" NUMBER,
            "PCTFREE$" NUMBER NOT NULL ENABLE,
            "PCTUSED$" NUMBER NOT NULL ENABLE,
            "INITRANS" NUMBER NOT NULL ENABLE,
            "MAXTRANS" NUMBER NOT NULL ENABLE,
            "FLAGS" NUMBER NOT NULL ENABLE,
            "AUDIT$" VARCHAR2(38) NOT NULL ENABLE,
            "ROWCNT" NUMBER,
            "BLKCNT" NUMBER,
            "EMPCNT" NUMBER,
            "AVGSPC" NUMBER,
            "CHNCNT" NUMBER,
            "AVGRLN" NUMBER,
            "AVGSPC_FLB" NUMBER,
            "FLBCNT" NUMBER,
            "ANALYZETIME" DATE,
            "SAMPLESIZE" NUMBER,
            "DEGREE" NUMBER,
            "INSTANCES" NUMBER,
            "INTCOLS" NUMBER NOT NULL ENABLE,
            "KERNELCOLS" NUMBER NOT NULL ENABLE,
            "PROPERTY" NUMBER NOT NULL ENABLE,
            "TRIGFLAG" NUMBER,
            "SPARE1" NUMBER,
            "SPARE2" NUMBER,
            "SPARE3" NUMBER,
            "SPARE4" VARCHAR2(1000),
            "SPARE5" VARCHAR2(1000),
            "SPARE6" DATE,
            "SPARE7" NUMBER,
            "SPARE8" NUMBER,
            "SPARE9" VARCHAR2(1000),
            "SPARE10" VARCHAR2(1000),
            "ACDRFLAGS" NUMBER,
            "ACDRTSOBJ#" NUMBER,
            "ACDRDEFAULTTIME" TIMESTAMP (6),
            "ACDRROWTSINTCOL#" NUMBER
           ) CLUSTER "SYS"."C_OBJ#" ("OBJ#")


Les commentaires dans le dictionnaire de données
Est-ce qu'il y a des commentaires sur cette table ou ses colonnes? Et non, aucun!
          SQL> select NVL(COMMENTS, 'AUCUN') from dba_tab_comments where TABLE_NAME = 'TAB$';
          NVL(COMMENTS,'AUCUN')
          --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          AUCUN
           
          SQL> select COLUMN_NAME, NVL(COMMENTS, 'AUCUN') AS "COMMENTS" from dba_col_comments where TABLE_NAME = 'TAB$' order by column_name;
          COLUMN_NAME                    COMMENTS
          ------------------------------ ------------------------------
          ANALYZETIME                    AUCUN
          AUDIT$                         AUCUN
          AVGRLN                         AUCUN
          AVGSPC                         AUCUN
          AVGSPC_FLB                     AUCUN
          BLKCNT                         AUCUN
          BLOCK#                         AUCUN
          BOBJ#                          AUCUN
          CHNCNT                         AUCUN
          CLUCOLS                        AUCUN
          COLS                           AUCUN
          DATAOBJ#                       AUCUN
          DEGREE                         AUCUN
          EMPCNT                         AUCUN
          FILE#                          AUCUN
          FLAGS                          AUCUN
          FLBCNT                         AUCUN
          INITRANS                       AUCUN
          INSTANCES                      AUCUN
          INTCOLS                        AUCUN
          KERNELCOLS                     AUCUN
          MAXTRANS                       AUCUN
          OBJ#                           AUCUN
          PCTFREE$                       AUCUN
          PCTUSED$                       AUCUN
          PROPERTY                       AUCUN
          ROWCNT                         AUCUN
          SAMPLESIZE                     AUCUN
          SPARE1                         AUCUN
          SPARE2                         AUCUN
          SPARE3                         AUCUN
          SPARE4                         AUCUN
          SPARE5                         AUCUN
          SPARE6                         AUCUN
          TAB#                           AUCUN
          TRIGFLAG                       AUCUN
          TS#                            AUCUN
           
          37 rows selected.
          
      
Les scripts de création des tables du dictionnaire de données
Allons cette fois encore plus loin, franchissons le dernier pas, regardons le VRAI ordre de création des tables du dictionnaire de données. On a vu celui-ci avec DBMS_METADATA.GET_DDL mais, à la création de la base, c'est un autre ordre SQL qui est exécuté, depuis un script SQL; allez, on y va!

Le répertoire des scripts SQL de création d'une base est $ORACLE_HOME/rdbms/admin. Le fichier le plus important, celui appelé lors d'un CREATE DATABASE, est sql.bsq. Voici son contenu en entier : il appelle juste une liste d'autres fichiers .bsq.
         [ora1210@PPALIORACLEPA03 [TAIAS1] /opt/oracle/srv/12.1.0/rdbms/admin]$ more sql.bsq
           
          rem Copyright (c) 1990, 2012, Oracle and/or its affiliates.
          rem All rights reserved.
          rem
          rem  MODIFIED
          rem   amullick   01/05/12 - bug13549280: add dtlog.bsq
          rem   sslim      09/01/11 - add drupg.bsq
          rem   swerthei   03/15/11 - force new version on PT.RS branch
          rem   jinjche    03/10/11 - Add dpstdy.bsq
          rem   sankejai   04/11/11 - add dfba.bsq
          ...
          rem   varora     12/01/94 -  change toid in adtcol$ to type number
          rem   anori      11/17/94 -  ADT support tables and columns
          rem
          rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! IMPORTANT !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
          rem Whenever new column is created to store internal, user or kernel column
          rem number, be sure to update the structure adtDT in atb.c so that those
          rem columns will be updated properly during drop column.
          rem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
          rem
          dcore.bsq
          dsqlddl.bsq
          dmanage.bsq
          dplsql.bsq
          dtxnspc.bsq
          dfmap.bsq
          denv.bsq
          drac.bsq
          dsec.bsq
          doptim.bsq
          dobj.bsq
          djava.bsq
          dpart.bsq
          drep.bsq
          daw.bsq
          dsummgt.bsq
          dtools.bsq
          dexttab.bsq
          ddm.bsq
          dlmnr.bsq
          ddst.bsq
          dfba.bsq
          dpstdy.bsq
          drupg.bsq
          dtlog.bsq
          
Regardons maintenant le fichier dcore.bsq qui, comme son nom l'indique, crée le core du dictionnaire de données. C'est dans lui que se trouve l'ordre DDL de création de la table tab$.

Que voit-on? Que chaque colonne a un commentaire expliquant son rôle :-)  et ça, ça n'a pas de prix pour qui veut comprendre ces fameuses tables du dictionnaire de données. Par exemple comment savoir que 0x0400000 dans la colonne flags signifie "table has sub tables"?
Autre point capital, des colonnes ne sont pas reprises dans la vue DBA_TABLES. Par exemple la colonne COLS indique le nombre de colonnes de la table; cette donnée n'est pas reprise dans DBA_TABLES, il faut pour avoir cette info interroger DBA_TAB_COLS.

On peut dire un grand merci aux développeurs qui ont laissé dans ces fichiers des infos d'aussi grande valeur pour les DBA.
          [ora1210@PPALIORACLEPA03 [TAIAS1] /opt/oracle/srv/12.1.0/rdbms/admin]$ more dcore.bsq
          create table tab$                                             /* table table */
          ( obj#          number not null,                            /* object number */
            /* DO NOT CREATE INDEX ON DATAOBJ#  AS IT WILL BE UPDATED IN A SPACE
             * TRANSACTION DURING TRUNCATE */
            dataobj#      number,                          /* data layer object number */
            ts#           number not null,                        /* tablespace number */
            file#         number not null,               /* segment header file number */
            block#        number not null,              /* segment header block number */
            bobj#         number,                /* base object number (cluster / iot) */
            tab#          number,    /* table number in cluster, NULL if not clustered */
            cols          number not null,                        /* number of columns */
            clucols       number,/* number of clustered columns, NULL if not clustered */
            pctfree$      number not null, /* minimum free space percentage in a block */
            pctused$      number not null, /* minimum used space percentage in a block */
            initrans      number not null,            /* initial number of transaction */
            maxtrans      number not null,            /* maximum number of transaction */
            flags         number not null, /* 0x00     = unmodified since last backup
                                              0x01     = modified since then
                                              0x02     = DML locks restricted to <= SX
                                              0x04     = DML locks <= SX not acquired
                                              0x08     = CACHE
                                              0x10     = table has been analyzed
                                              0x20     = table has no logging
                                              0x40     = 7.3 -> 8.0 data object
                                                         migration required
                                              0x0080   = current summary dependency
                                              0x0100   = user-specified stats
                                              0x0200   = global stats
                                              0x0800   = table has security policy
                                              0x020000 = Move Partitioned Rows
                                             0x0400000 = table has sub tables
                                            0x00800000 = row dependencie s enabled */
                          /* 0x10000000 = this IOT has a  physical rowid mapping table */
                          /* 0x20000000 = mapping table of an IOT(with physical rowid) */
            audit$        varchar2("S_OPFL") not null,             /* auditing options */
            rowcnt        number,                                    /* number of rows */
            blkcnt        number,                                  /* number of blocks */
            empcnt        number,                            /* number of empty blocks */
            avgspc        number,       /* average available free space/iot ovfl stats */
            chncnt        number,                            /* number of chained rows */
            avgrln        number,                                /* average row length */
            avgspc_flb    number,       /* avg avail free space of blocks on free list */
            flbcnt        number,                             /* free list block count */
            analyzetime   date,                        /* timestamp when last analyzed */
            samplesize    number,                 /* number of rows sampled by Analyze */
          /*
          * Legal values for degree, instances:
          *     NULL (used to represent 1 on disk/dictionary and implies noparallel), or
          *     2 thru EB2MAXVAL-1 (user supplied values), or
          *     EB2MAXVAL (implies use default value)
          */
            degree        number,      /* number of parallel query slaves per instance */
            instances     number,        /* number of OPS instances for parallel query */
          /* <intcols> => the number of dictionary columns => the number of columns
          * that have dictionary meta-data associated with them. This is a superset of
          * <usercols> and <kernelcols>.
          *    <intcols> = <kernelcols> + <number_of_virtual_columns>
          */
            intcols       number not null,               /* number of internal columns */
          /* <kernelcols> => the number of REAL columns (ie) columns that actually
          * store data.
          */
            kernelcols    number not null,          /* number of REAL (kernel) columns */
            property      number not null,            /* table properties (bit flags): */
                                        /* 0x01 = typed table, 0x02 = has ADT columns, */
                           /* 0x04 = has nested-TABLE columns, 0x08 = has REF columns, */
                                /* 0x10 = has array columns, 0x20 = partitioned table, */
                         /* 0x40 = index-only table (IOT), 0x80 = IOT w/ row OVerflow, */
                       /* 0x100 = IOT w/ row CLustering, 0x200 = IOT OVeRflow segment, */
                         /* 0x400 = clustered table, 0x800 = has internal LOB columns, */
                  /* 0x1000 = has primary key-based OID$ column, 0x2000 = nested table */
                              /* 0x4000 = View is Read Only, 0x8000 = has FILE columns */
                 /* 0x10000 = obj view's OID is system-gen, 0x20000 = used as AQ table */
                                             /* 0x40000 = has user-defined lob columns */
                                         /* 0x00080000 = table contains unused columns */
                                      /* 0x100000 = has an on-commit materialized view */
                                       /* 0x200000 = has system-generated column names */
                                                /* 0x00400000 = global temporary table */
                                      /* 0x00800000 = session-specific temporary table */
                                                  /* 0x08000000 = table is a sub table */
                                                  /*   0x20000000 = pdml itl invariant */
                                                    /* 0x80000000 = table is external  */
                                    /* PFLAGS2: 0x400000000 = delayed segment creation */
            /* PFLAGS2: 0x20000000000 = result cache mode FORCE enabled on this table  */
            /* PFLAGS2: 0x40000000000 = result cache mode MANUAL enabled on this table */
            /* PFLAGS2: 0x80000000000 = result cache mode AUTO enabled on this table   */
            /* PFLAGS2: 0x400000000000000 = has identity column                        */
            trigflag      number,   /* first two bytes for trigger flags, the rest for */
                             /* general use, check tflags_kqldtvc in kqld.h for detail */
                                                      /* 0x00000001 deferred RPC Queue */
                                                            /* 0x00000002 snapshot log */
                                                  /* 0x00000004 updatable snapshot log */
                                                       /* 0x00000008 = context trigger */
                                              /* 0x00000010 = synchronous change table */
                                                       /* 0x00000020 = Streams trigger */
                                                  /* 0x00000040 = Content Size Trigger */
                                                   /* 0x00000080 = audit vault trigger */
                                     /* 0x00000100 = Streams Auxiliary Logging trigger */
                               /* 0x00010000 = server-held key encrypted columns exist */
                                 /* 0x00020000 = user-held key encrypted columns exist */
                                                    /* 0x00200000 = table is read only */
                                               /* 0x00400000 = lobs use shared segment */
                                                           /* 0x00800000 = queue table */
                                             /* 0x10000000 = streams unsupported table */
                                                      /* enabled at some point in past */
                                      /* 0x80000000 = Versioning enabled on this table */
            spare1        number,                       /* used to store hakan_kqldtvc */
            spare2        number,         /* committed partition # used by drop column */
            spare3        number,                           /* summary sequence number */
            spare4        varchar2(1000),         /* committed RID used by drop column */
            spare5        varchar2(1000),      /* summary related information on table */
            spare6        date                                  /* flashback timestamp */
          )
          cluster c_obj#(obj#)
          /


Ca vous a plu? Vous en redemandez? Alors je vous laisse regarder les autres tables : sys.user$, sys.ts$, sys.seg$, sys.obj$, sys.user$, x$ksppcv, x$ksppi, sys.deferred_stg$ :-)