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$ :-)
============================================================================================
Les infos sur les structures mémoire écrites en C : les X$...
============================================================================================
Nous venons de voir les vraies tables du dictionnaire de données, intéressons-nous maintenant aux structures qui sont sous-jacentes aux V$... On appelle couremment les V$... des vues dynamique de performance mais en réalité il s'agit de synonyme. Ces synonymes pointent vers les vues V_$... qui elles même pointent vers des structures créées au redémarrage de la base et qui sont préfixées par X$. Ces structure mémoire écrites en C ne sont pas documentées par Oracle et ne sont visiblement répertoriées que dans les programmes Oracle donc dans les binaires illisibles.
Néanmoins, j'ai réussi à trouver un ficher où certaines sont définies :-)
Sous $ORACLE_HOME/rdbms/admin, lancer la commande
grep -in X\\$ *bsq | more
…
recover.bsq:82:define 'x$endBackupJobStep'
recover.bsq:119:define 'x$setBackupParams'
…
Aller dans recover.bsq mais toutes les structures mémoire X$ ne sont pas là.
define table x$kccrt
(
INST_ID NUMBER,
CON_ID NUMBER,
RTNUM NUMBER,
RTSTA NUMBER,
RTNLF NUMBER,
RTSEQ NUMBER,
RTENB VARCHAR2(16),
RTETS VARCHAR2(20),
RTDIS VARCHAR2(16),
RTDIT VARCHAR2(20)
);
>>>
define 'x$kcctir'
<<<
define table x$kcctir
(
INST_ID NUMBER,
CON_ID NUMBER,
TIRNUM NUMBER
);
>>>
On remarquera qu’il n’y a pas de commentaire sur le rôle des colonnes ni sur ces objets, contrairement aux tables genre TAB$.
