Blog d'un DBA sur le SGBD Oracle et SQL

03 mai 2019

Changer le propriétaire d'une table en hackant le dictionnaire de donnés - Change the owner of a table by hacking Oracle


Introduction
Oracle ne permet pas de changer le propriétaire d'un objet dans sa base de données : point!
Pour quelles raisons? Peut-être que c'est trop complexe, qu'à cause des dépendances avec d'autres objets cela devient ingérable...
     
Mais si on est obligé de le faire, il existe des solutions de contournement.
1) Datapump : export/import dans un nouveau schéma avec le paramètre REMAP_SCHEMA.
Par exemple, on exporte le schéma hr et on le réimporte sur la même base dans le schéma scott.
     Linux> expdp system SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp
     Linux> impdp system DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott
     
Avec les anciens programmes exp et imp, il fallait utiliser les paramètres FROMUSER et TOUSER lors de l'import, mais cela est maintenant de l'histoire ancienne.
    
2) CTAS : Create Table As Select.
Il suffit de créer une table dans un nouveau schéma en reprenant les données de la table de l'autre schéma. Attention, les grants, indexes, sequences, synonymes, contraintes d'intégrité autre que NOT NULL et Check (à vérifier) doivent être recréés. Les vues et procédures stockées doivent être modifiés au cas où on aurait préfixé le nom de l'objet par son ancien propriétaire.
     SQL> CREATE TABLE USER2.tab1 as select * from USER1.tab1;
     
3) Hacker le dictionnaire de données d'Oracle.
C'est l'objet de cet article :-)


 
Points d'attention
ATTENTION : à ne faire QUE dans une base de test, le risque étant que la table hackée ne soit plus accessible, ni en lecture ni en modification. Dans le pire des cas, c'est votre base en entier qui pourrait ne plus fonctionner...
Une sauvegarde RMAN de votre table ET de votre base est INDISPENSABLE avant de faire cette opération.


Il va de soi que cet article est là à but purement théorique, pour essayer d'approfondir nos connaissances d'Oracle, son fonctionnement interne mais ce n'est certainement pas une procédure à utiliser dans un contexte professionnel.



Base de tests
Une base Oracle 12.



Exemples
============================================================================================
Génération de la trace 10046
============================================================================================

La base de test est une 12.2, en mode CDB et mon user est SYS.
     SQL> select * from v$version;
     BANNER                                                                                 CON_ID
     -------------------------------------------------------------------------------- ----------
     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
     PL/SQL Release 12.2.0.1.0 - Production                                                      0
     CORE    12.2.0.1.0    Production                                                              0
     TNS for Linux: Version 12.2.0.1.0 - Production                                              0
     NLSRTL Version 12.2.0.1.0 - Production                                                      0
     
     SQL> select cdb from v$database;
     CDB
     ---
     YES
     
     SQL> show user
     USER is "SYS"

J'utilise la trace 10046 pour identifier quelles tables du dictionnaire de données sont concernées par un CREATE TABLE.
     SQL> ALTER SESSION SET timed_statistics = TRUE;
     SQL> ALTER SESSION SET max_dump_file_size = unlimited;
     SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'zztest_change_table01.trc';
     SQL> ALTER SESSION SET events '10046 trace name context forever, level 4';
     
L'ordre SQL à tracer : création d'une table de 3 colonnes avec une Primary Key et donc un index.
     SQL> CREATE TABLE SYSTEM.ZZTEST_CHANGE (ID NUMBER primary key, NOM VARCHAR2(50), PRENOM VARCHAR2(50));
     
Ne pas oublier de désactiver la trace.
     SQL> ALTER SESSION SET events '10046 trace name context off';
              
Identifier le fichier de trace généré et le rendre plus lisible avec tkprof. A noter que Oracle ajoute un libellé devant le nom que j'ai saisi.
     SQL> SELECT value FROM v$diag_info WHERE  name = 'Default Trace File';
     VALUE
     -----------------------------------------------------------------------------------------------
     /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_4960_zztest_change_table01.trc

     SQL> ! tkprof /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_4960_zztest_change_table01.trc /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_4960_zztest_change_table01.txt

La table est bien dans le schéma SYSTEM.  
     SQL> desc system.ZZTEST_CHANGE;
     Name                    Null?        Type
     --------------------------------------------------
     ID                        NOT NULL     NUMBER
     NOM                                    VARCHAR2(50)
     PRENOM                                 VARCHAR2(50)
     
     SQL> select owner from dba_tables where table_name = 'ZZTEST_CHANGE';
     OWNER
     -----------
     SYSTEM
     
     SQL> select * from system.ZZTEST_CHANGE;
     no rows selected
     
     SQL> insert into system.ZZTEST_CHANGE values(1, 'TEST', 'TEST');
     1 row created.
     
     SQL> commit;
     Commit complete.


============================================================================================
Les INSERTs dans le dictionnaire de données Oracle lors d'un CREATE TABLE
============================================================================================

Que voit-on dans la trace? Pour une base CDB, le CREATE TABLE a généré neuf INSERTs dans les tables du dictionnaire de données (je ne parle pas ici des INSERT des blocs PL/SQL qui ne me semblent pas être indispensables au CREATE TABLE).
     insert into obj$
     insert into deferred_stg$
     insert into con$
     insert into icol$
     insert into ind$
     insert into tab$
     insert into col$
     insert into ccol$
     insert into cdef$
     
Voici l'ordre SQL complet de chacun des INSERTs, sans la valeur des bind variables.              
     SQL> ! view /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_4960_zztest_change_table01.txt

     insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,
     remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2,spare3,
     signature, spare7, spare8, spare9, dflcollid, creappid, creverid, modappid,
     modverid, crepatchid, modpatchid)
     values
     (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18, :19,:20,:21,
     :22,decode(:23,0,null,:23),:24,:25,:26,:27,:28,:29)
     
       
     insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,
     extsize,extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1,
     scanhint, bitmapranges)
     values
     (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0,NULL,:17),:18,:19)
       
       
     insert into con$(owner#,name,con#,spare1)
     values
     (:1,:2,:3,:4)
     
     
     insert into icol$(obj#,bo#,intcol#,pos#,segcol#,segcollength,offset,col#,spare1,spare2,spare3)
     values
     (:1,:2,:3,:4,0,0,0,:5,:6,:7,:8)
     
     
     insert into ind$(bo#,obj#,ts#,file#,block#,intcols,type#,flags,property,
     pctfree$,initrans,maxtrans,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,
     cols,analyzetime,samplesize,dataobj#,degree,instances,rowcnt,pctthres$,
     indmethod#,trunccnt,spare1,spare4,spare2,spare6,evaledition#,
     unusablebefore#,unusablebeginning#)
     values
     (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,
     :22,decode(:23,1,null,:23),decode(:24,1,null,:24),:25, :32*256+:26,:27,:28,
     :29,:30,:31,:33,decode(:34,1,null,:34),decode(:35,0,null,:35),decode(:36,0,
     null,:36))
     
       
     insert into tab$(obj#,ts#,file#,block#,bobj#,tab#,intcols,kernelcols,clucols,
     audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,
     avgspc,chncnt,avgrln,analyzetime,samplesize,cols,property,degree,instances,
     dataobj#,avgspc_flb,flbcnt,trigflag,spare1,spare6,acdrflags,acdrtsobj#,
     acdrdefaulttime,acdrrowtsintcol#)
     values
     (:1,:2,:3,:4,decode(:5,0,null,:5),decode(:6,0,null,:6),:7,:8,decode(:9,0,null,
     :9),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,
     decode(:26,1,null,:26),decode(:27,1,null,:27),:28,:29,:30,:31,:32,:33,:34,
     :35,:36,:37)
       
       
     insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,
     null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,
     charsetid,charsetform,spare1,spare2,spare3,evaledition#,unusablebefore#,
     unusablebeginning#,collid,collintcol#,acdrrescol#, spare7)
     values
     (:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,
     null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,
     180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),
     :13,:14,:15,:16,:17,:18,:19,:20,decode(:21,1,null,:21),decode(:22,0,null,
     :22),decode(:23,0,null,:23),decode(:24,0,null,:24),decode(:25,0,null,:25),
     decode(:26,0,null,:26),:27)
       
     
     insert into ccol$(con#,obj#,intcol#,pos#,col#,spare1)
     values
     (:1,:2,:3,decode(:4,0,null,:4),:5, :6)
     
     
     insert into cdef$(obj#,con#,type#,intcols,condlength,condition,robj#,rcon#,
     match#,refact,enabled,cols,defer,mtime,spare1,spare2,spare3)
     values
     (:1,:2,:3,decode(:4,0,null,:4),decode(:5,0,null,:5),:6,decode(:7,0,null,:7),
     decode(:8,0,null,:8),decode(:9,0,null,:9),decode(:10,0,null,:10),
     (:11,0,null,:11),:12, decode(:13,0,null,:13),:14,:15,:16,:17)


============================================================================================
Changement de propriétaire de la table : attention, deux colonnes à changer dans obj$ sinon la table aura deux propriétaires!
============================================================================================

Parmi les neufs INSERTs ci-dessus, combien de tables ont une colonne OWNER? Deux seulement : obj$ et con$. C'est donc ces tables qui vont nous intéresser.

Quel est l'id des deux users qui m'intéressent, SYSTEM et HR (qui sera le nouveau propriétaire)?  
     SQL> select username, user_id from dba_users where username in ('SYSTEM', 'HR') order by username;
     USERNAME        USER_ID
     -------------------------
     HR                128
     SYSTEM              9
     
     SQL> select name, user#  from user$ where name in ('SYSTEM', 'HR') order by name
     NAME       USER#
     ---------- ------
     HR         128
     SYSTEM         9
     
obj$ est la table des objets Oracle, c'est celle qui nous intéresse en premier lieu.
     SQL> select OBJ#, OWNER# from obj$ where name = 'ZZTEST_CHANGE';
     OBJ#     OWNER#
     ---------- ------
     81265        9
     
Changement de propriétaire : on remplace SYSTEM par HR dans les deux tables, celle des objets et celle des contraintes.
     SQL> update obj$ set owner#=128 where obj#=81265;
     1 row updated.
     
     SQL> select CONSTRAINT_NAME from dba_constraints where table_name = 'ZZTEST_CHANGE';
     CONSTRAINT_NAME
     ------------------------
     SYS_C0013096
     
     SQL> select OWNER#, con# from con$ where name = 'SYS_C0013096';              
     OWNER#        CON#         
     ---------- ------
     9            13096
     
     SQL> update con$ set owner#=128 where con#=13096;
     1 row updated.
     
     SQL> commit;
     Commit complete.

Alors, quel est le résultat? Hé bien c'est très très BIZARRE, la table semble présente dans les deux schémas...
     SQL> desc system.ZZTEST_CHANGE;
     Name                              Null?    Type
     -----------------------------------------------------------------------------------
     ID                                NOT NULL NUMBER
     NOM                                        VARCHAR2(50)
     PRENOM                                     VARCHAR2(50)
      
     SQL> desc HR.ZZTEST_CHANGE
     Name                              Null?    Type
     -----------------------------------------------------------------------------------
     ID                                NOT NULL NUMBER
     NOM                                        VARCHAR2(50)
     PRENOM                                     VARCHAR2(50)
     
     SQL> select * from SYSTEM.ZZTEST_CHANGE;
         ID NOM     PRENOM
     ---------- ----------
          1 TEST    TEST
     
     SQL> select * from HR.ZZTEST_CHANGE;
         ID NOM     PRENOM
     ---------- ----------
          1 TEST    TEST
    
Dans les vues, le owner est encore SYSTEM. Mais pas dans les tables du dictionnaire de données où là on a bien HR (owner id 128).    
     SQL> select owner from dba_objects where object_name = 'ZZTEST_CHANGE';
     OWNER
     ---------------
     SYSTEM
     
     SQL> select owner from dba_tables where table_name = 'ZZTEST_CHANGE';
     OWNER
     ----------
     SYSTEM
     
     SQL> select OBJ#, OWNER# from obj$ where name = 'ZZTEST_CHANGE';
       OBJ#     OWNER#
     ---------- ----------
      81265      128
     
     
============================================================================================
Résolution du problème : une table peut avoir deux propriétaires dans le dictionnaire de données!
============================================================================================

Regardons maintenant TOUTE la table obj$, peut-être qu'il y a une info qui m'a échappé...

Tiens tiens, j'ai un 9 dans la colonne SPARE3; c'est l'id du user SYSTEM :-)
     SQL> select * from obj$ where OBJ#=81265;
           OBJ#   DATAOBJ#      OWNER#
     ---------- ---------- ----------
     NAME                                                                  NAMESPACE
     ------------------------------------------------------------------------------------
     SUBNAME                                                                   TYPE# CTIME
     ------------------------------------------------------------------------------------------
     MTIME      STIME     STATUS
     --------- --------- ----------
     REMOTEOWNER
     --------------------------------------------------------------------------------------------
     LINKNAME                                                                  FLAGS
     --------------------------------------------------------------------------------------------
     OID$                     SPARE1    SPARE2       SPARE3
     -------------------------------- ---------- ---------- ----------
     SPARE4
     --------------------------------------------------------------------------------------------
     SPARE5
     --------------------------------------------------------------------------------------------
     SPARE6      SIGNATURE                   SPARE7      SPARE8     SPARE9  DFLCOLLID     CREAPPID   CREVERID CREPATCHID       MODAPPID   MODVERID
     --------- -------------------------------- ---------- ---------- ---------- ---------- -----
     MODPATCHID    SPARE10     SPARE11
     ---------- ---------- ----------
     SPARE12
     ----------------------------------------------------------------------------------------------
     SPARE13
     ---------------------------------------------------------------------------------------------
     SPARE14
     ---------------------------------------------------------------------------
          81265    81265         128
     ZZTEST_CHANGE                                                                  1
                                                                           2 01-MAY-19
     01-MAY-19 01-MAY-19         1
                                                                           0
                           6         1        9
           41D05BB314F8D9A2123B128D7E77872B        0           0      0     16382
         
         
     SQL> select spare3 from obj$ where OBJ#=81265;
         SPARE3
     ----------
          9

Dans le fichier $ORACLE_HOME/rdbms/admin/dcore.bsq, on a la définition de obj$. Et que voit-on? Que le champ spare3 correspond à "base user#".
Mon analyse est que le propriétaire d'une table est stocké à deux endroits dans obj# : owner# et spare3 et qu'il faut donc updater les deux colonnes.
     create table obj$                                            /* object table */
     ( obj#          number not null,                            /* object number */
       dataobj#      number,                          /* data layer object number */
       owner#        number not null,                        /* owner user number */
       name          varchar2("M_IDEN") not null,                  /* object name */
       namespace     number not null,         /* namespace of object (see KQD.H): */
      /* 1 = TABLE/PROCEDURE/TYPE, 2 = BODY, 3 = TRIGGER, 4 = INDEX, 5 = CLUSTER, */
                                                       /* 8 = LOB, 9 = DIRECTORY, */
       /* 10 = QUEUE, 11 = REPLICATION OBJECT GROUP, 12 = REPLICATION PROPAGATOR, */
                                          /* 13 = JAVA SOURCE, 14 = JAVA RESOURCE */
                                                      /* 58 = (Data Mining) MODEL */
       subname       varchar2("M_IDEN"),               /* subordinate to the name */
       type#         number not null,                 /* object type (see KQD.H): */
       /* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */
                  /* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */
                   /* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */
           /* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */
                                                  /* 23 = DIRECTORY , 24 = QUEUE, */
         /* 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */
         /* 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */
                      /* 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */
                                                       /* 35 = INDEX SUBPARTITION */
                                                      /* 82 = (Data Mining) MODEL */
                                     /* 92 = OLAP CUBE DIMENSION,  93 = OLAP CUBE */
                        /* 94 = OLAP MEASURE FOLDER, 95 = OLAP CUBE BUILD PROCESS */
       ctime         date not null,                       /* object creation time */
       mtime         date not null,                      /* DDL modification time */
       stime         date not null,          /* specification timestamp (version) */
       status        number not null,            /* status of object (see KQD.H): */
                                          /* 1 = VALID/AUTHORIZED WITHOUT ERRORS, */
                               /* 2 = VALID/AUTHORIZED WITH AUTHORIZATION ERRORS, */
                                 /* 3 = VALID/AUTHORIZED WITH COMPILATION ERRORS, */
                              /* 4 = VALID/UNAUTHORIZED, 5 = INVALID/UNAUTHORIZED */
       remoteowner   varchar2("M_IDEN"),     /* remote owner name (remote object) */
       linkname      varchar2("M_XDBI"),             /* link name (remote object) */
       flags         number,               /* 0x01 = extent map checking required */
                                           /* 0x02 = temporary object             */
                                           /* 0x04 = system generated object      */
                                           /* 0x08 = unbound (invoker's rights)   */
                                           /* 0x10 = secondary object             */
                                           /* 0x20 = in-memory temp table         */
                                           /* 0x80 = dropped table (RecycleBin)   */
                                           /* 0x100 = synonym VPD policies        */
                                           /* 0x200 = synonym VPD groups          */
                                           /* 0x400 = synonym VPD context         */
                                           /* 0x4000 = nested table partition     */
       oid$          raw(16),        /* OID for typed table, typed view, and type */
       spare1        number,                      /* sql version flag: see kpul.h */
       spare2        number,                             /* object version number */
       spare3        number,                                        /* base user# */
       spare4        varchar2(1000),
       spare5        varchar2(1000),
       spare6        date,
       signature     raw(16),                      /* object signature hash value */
       spare7        number,                                        /* future use */
       spare8        number,
       spare9        number,
       dflcollid     number,                   /* unit-level default collation id */
       creappid      number,         /* App ID of Application that created object */
       creverid      number,        /* Version of Application that created object */
       crepatchid    number,       /* Patch ID of Application that created object */
       modappid      number,   /* App ID of Application that last modified object */
       modverid      number,  /* Version of Application that last modified object */
       modpatchid    number, /* Patch ID of Application that last modified object */
       spare10       number,
       spare11       number,
       spare12       varchar2(1000),
       spare13       varchar2(1000),
       spare14       timestamp
     )
       storage (initial 10k next 100k maxextents unlimited pctincrease 0)
     /

Allez, on y retourne :-)
     SQL> update obj$ set spare3=128 where obj#=81265;
     1 row updated.
     
     SQL> commit;
     Commit complete.
    
GAGNE!!!!!!!!!!!!!!!!!
     SQL> select owner from dba_objects where object_name = 'ZZTEST_CHANGE';
     OWNER
     ----------------------------------
     HR
     
     SQL> select owner from dba_tables where table_name = 'ZZTEST_CHANGE';
     OWNER
     ----------------------------------
     HR
     
Il n'y a maintenant plus qu'une table ZZTEST_CHANGE dans la base, dans le schéma HR.
     SQL> desc SYSTEM.ZZTEST_CHANGE
     ERROR:
     ORA-04043: object SYSTEM.ZZTEST_CHANGE does not exist
    
     SQL> desc HR.ZZTEST_CHANGE
     Name                Null?        Type
     ------------------------------------------------------------------------------------------------
     ID                    NOT NULL     NUMBER
     NOM                                VARCHAR2(50)
     PRENOM                             VARCHAR2(50)
     
     SQL> select * from HR.ZZTEST_CHANGE;
     ID NOM                              PRENOM
     ---------- --------------------------------------
     1 TEST                           TEST
     
Voyons voir si les SELECTs et mises à jour sont OK une fois la modif faite... OUI!
La mise à jour du dictionnaire de données n'a pas cassé la table :-)
     SQL> insert into HR.ZZTEST_CHANGE values(2, 'TEST2', 'TEST2')
     1 row created.
     
     SQL> commit;
     Commit complete.
     
     SQL> select * from HR.ZZTEST_CHANGE;
         ID NOM                              PRENOM
     ---------- --------------------------------------------
          1 TEST                           TEST
          2 TEST2                          TEST2
     
        
Maintenant, je vous laisse faire des tests plus approfondis avec RMAN, Datapump pour voir si cette solution est pleinement opérationnelle.
ATTENTION : si vous avez des synonymes, vues, procédures, fonctions... qui pointaient sur l'ancien objet avec le nom du propriétaire original, ils sont certainement invalides et il faudra soit les modifier soit les recréer.

Posté par David DBA à 16:31 - - Permalien [#]
Tags : ,


22 avril 2019

Historique du nb de lignes d'une table : table AWR SYS.WRI$_OPTSTAT_TAB_HISTORY - History of the row number of a table


Introduction
Par défaut, il n'est pas possible sous Oracle de voir l'historique du nombre de lignes d'une table. Cela peut-être assez gênant si on veut expliquer pourquoi un SELECT prends dix fois plus de temps que le mois précédent; surtout si les développeurs et chefs de projet jurent, la main sur le coeur, que non, la taille de la table n'a pas changé et que donc le problème est forcément sur la base :-)

Pour leur prouver que cette table a grossi, fortement même, il faut utiliser des moyens détournés, par exemple requêter une des tables du référentiel AWR, non externalisée sous forme de vue : SYS.WRI$_OPTSTAT_TAB_HISTORY.




Points d'attention
N/A.



Base de tests
N'importe quelle base Oracle avec AWR
 



Exemples
============================================================================================
Le référentiel AWR

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

Les vraies tables du référentiel AWR, une des composantes du dictionnaire de données, sont, pour une 12.2, au nombre de 364. Beaucoup ne sont malheureusement pas externalisées par Oracle dans des vues DBA_HIST...
     
Nombre d'objets dans le référentiel AWR : 1016.
     SQL> select count(*) from dba_objects where object_name like 'WR%$%';
     COUNT(*)
     ----------
     1016

Si on regarde par type d'objets maintenant : 364 tables.
     SQL> select object_type, count(*)
     from dba_objects
     where object_name like 'WR%$%'
     group by object_type
     order by 1;

     OBJECT_TYPE          COUNT(*)
     ----------------------- ----------
     INDEX                   321
     INDEX PARTITION         79
     SEQUENCE            23
     SYNONYM             28
     TABLE                   364
     TABLE PARTITION         91
     TABLE SUBPARTITION        32
     TYPE                41
     TYPE BODY            38
     9 rows selected.

On peut même les classer en cinq grandes familles. Attention, les tables WRP* et WRR* semblent récentes car je n'ai pas trouvé d'infos dessus avec Google.
         - The wrm$ tables store metadata information for the Workload Repository
         - The wrh$ tables store historical data or snapshots
         - The wri$ tables: These tables store data related to advisory functions


    
SQL> select substr(object_name, 1, 3), count(*) from dba_objects where object_name like 'WR%$%' and object_type = 'TABLE' group by substr(object_name, 1, 3) order by 1;

     SUBSTR(OBJEC   COUNT(*)
     ------------ ----------
     WRH            189
     WRI            105
     WRM             16
     WRP              4
     WRR             50

Un exemple du nom de ces tables.    
     SQL> select distinct object_name from dba_objects where object_name like 'WRH$%' and object_type = 'TABLE' and rownum < 11 order by 1;
     OBJECT_NAME
     --------------------------------------------------------------------------------
     WRH$_COMP_IOSTAT
     WRH$_DATAFILE
     WRH$_FILESTATXS
     WRH$_FILESTATXS_BL
     WRH$_IOSTAT_FILETYPE
     WRH$_IOSTAT_FILETYPE_NAME
     WRH$_IOSTAT_FUNCTION
     WRH$_IOSTAT_FUNCTION_NAME
     WRH$_TEMPFILE
     WRH$_TEMPSTATXS
     10 rows selected.
    
Oracle ne dispose que de 151 vues pour interroger le référentiel AWR, ce qui signifie que beaucoup d'informations ne sont pas accessibles facilement car les vraies tables du dictionnaire ne sont pas documentées par Oracle.
     SQL> select count(*) from dba_objects where object_name like 'DBA_HIST%' and object_type and object_type = 'VIEW';
     COUNT(*)
     ----------
     151
           
Exemples de ces vues.
     SQL> select object_name from dba_objects where object_name like 'DBA_HIST%' and object_type = 'VIEW' and rownum < 11;
     OBJECT_NAME
     --------------------------------------------------------------------------------
     DBA_HIST_SNAP_ERROR
     DBA_HIST_REPORTS_TIMEBANDS
     DBA_HIST_BASELINE_TEMPLATE
     DBA_HIST_REPORTS
     DBA_HIST_REPORTS_DETAILS
     DBA_HIST_REPORTS_CONTROL
     DBA_HIST_DATABASE_INSTANCE
     DBA_HIST_SNAPSHOT
     DBA_HIST_COLORED_SQL
     DBA_HIST_BASELINE_METADATA
     10 rows selected.

La configuration d'AWR est la suivante, classique : 31 jours de rétention, un snapshot toutes les heures.
     SQL> select * from DBA_HIST_WR_CONTROL;
     DBID        SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID
     ---------- -------------------- ------------------------------ ----------
      768045447    +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT         0


============================================================================================
La table SYS.WRI$_OPTSTAT_TAB_HISTORY

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

La vue qui nous intéresse est SYS.WRI$_OPTSTAT_TAB_HISTORY, accessible par tout user ayant accès au dictionnaire de données.
      SQL> desc SYS.WRI$_OPTSTAT_TAB_HISTORY
      Name                       Null?    Type
      ----------------------------------------- -------- ----------------------------
      OBJ#                       NOT NULL NUMBER
      SAVTIME                        TIMESTAMP(6) WITH TIME ZONE
      FLAGS                            NUMBER
      ROWCNT                         NUMBER
      BLKCNT                         NUMBER
      AVGRLN                         NUMBER
      SAMPLESIZE                        NUMBER
      ANALYZETIME                        DATE
      CACHEDBLK                        NUMBER
      CACHEHIT                        NUMBER
      LOGICALREAD                        NUMBER
      SPARE1                         NUMBER
      IM_IMCU_COUNT                        NUMBER
      IM_BLOCK_COUNT                     NUMBER
      SCANRATE                        NUMBER
      SPARE2                         NUMBER
      SPARE3                         NUMBER
      SPARE4                         VARCHAR2(1000)
      SPARE5                         VARCHAR2(1000)
      SPARE6                         TIMESTAMP(6) WITH TIME ZONE
     
Pour une 12.2, voici sa définition prise dans $ORACLE_HOME/rdbms/admin/doptim.bsq, ce qui nous permet d'avoir le rôle des colonnes.
Une colonne sympa est rowcnt : number of rows :-)
     Rem Table to store optimizer statistics history
     Rem for table and table partition objects
     create table wri$_optstat_tab_history
     ( obj#           number not null,                           /* object number */
       savtime        timestamp with time zone,     /* timestamp when stats saved */
       flags          number,
       rowcnt         number,                                   /* number of rows */
       blkcnt         number,                                 /* number of blocks */
       avgrln         number,                               /* average row length */
       samplesize     number,                /* number of rows sampled by Analyze */
       analyzetime    date,                       /* timestamp when last analyzed */
       cachedblk      number,                           /* blocks in buffer cache */
       cachehit       number,                                  /* cache hit ratio */
       logicalread    number,                          /* number of logical reads */
       spare1         number,                          /*  number of chained rows */
       im_imcu_count  number,              /* number of IMCUs for inmemory object */
       im_block_count number,          /* number of IM blocks for inmemory object */
       scanrate       number,                                /* scan rate in MB/S */
       spare2         number,
       spare3         number,
       spare4         varchar2(1000),
       spare5         varchar2(1000),
       spare6         timestamp with time zone
     ) tablespace sysaux
     pctfree 1
     enable row movement


============================================================================================
Comment la table SYS.WRI$_OPTSTAT_TAB_HISTORY est-elle renseignée?

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

Nous allons voir que la mise à jour de cette table n'est pas simple, et qu'elle est liée au calcul des stats plus que à la génération des snapshots AWR.

Table de tests

On crée une table de tests et on insère 1000 lignes.
     SQL> create table zztest(id number primary key, date01 date);
    
     SQL> insert into zztest select level, systimestamp from dual connect by level < 1001;
     1000 rows created.
     
     SQL> commit;
      
Par défaut, la table n'a aucune info sur ma table de test!
     SQL> select SAVTIME, ROWCNT
     from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
     WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
     OBJ.OWNER = 'HR' AND
     OBJECT_NAME = 'ZZTEST'
     ORDER BY 1, 2;
     
     SAVTIME                                      ROWCNT
     ---------------------------------------- ----------
     20-APR-19 07.50.59.258718 AM -04:00

Générer les stats pour remplir la table     
On génère les stats : bingo!
     SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');
     PL/SQL procedure successfully completed.
     
     SQL> select num_rows from dba_tab_statistics where table_name = 'ZZTEST';
     NUM_ROWS
     ----------
           1000
    
     SQL> select SAVTIME, ROWCNT
     from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
     WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
     OBJ.OWNER = 'HR' AND
     OBJECT_NAME = 'ZZTEST'
     ORDER BY 1, 2;
     SAVTIME                                      ROWCNT
     ---------------------------------------- ----------
     20-APR-19 07.50.59.258718 AM -04:00
     20-APR-19 08.09.20.514543 AM -04:00               1000
     
Cette table semble être mise à jour dès que les stats sont générées MAIS, après plusieurs tests, non, c'est plus complexe que ça; ce serait trop simple...
Je recrée 1000 lignes et zut, la vue a encore l'ancien nombre de lignes. Tiens, pourtant on a bien une nouvelle ligne, correspondant à l'exécution de dbms_stats.gather_table_stats mais la valeur n'est pas 2000... et cependant on a généré les stats comme précédemment...
     SQL> insert into zztest select level+2000, systimestamp from dual connect by level < 1001;
     1000 rows created.
     
     SQL> commit;
    
     SQL> select count(*) from zztest;
       COUNT(*)
     ----------
           2000

     SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');
     PL/SQL procedure successfully completed.
     
     SQL> select num_rows from dba_tab_statistics where table_name = 'ZZTEST';
     NUM_ROWS
     ----------
           2000
     
     SQL> select SAVTIME, ROWCNT
     from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
     WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
     OBJ.OWNER = 'HR' AND
     OBJECT_NAME = 'ZZTEST'
     ORDER BY 1, 2;
     
     SAVTIME                      ROWCNT
     ---------------------------------------- ----------
     20-APR-19 07.50.59.258718 AM -04:00
     20-APR-19 08.09.20.514543 AM -04:00           1000
     20-APR-19 08.15.35.768188 AM -04:00           1000
     
Est-ce qu'il faut que le snapshot AWR soit généré? On va créer manuellement un snapshot AWR.
     SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
     
Et non, KO...
     SQL> select SAVTIME, ROWCNT
     from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
     WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
     OBJ.OWNER = 'HR' AND
     OBJECT_NAME = 'ZZTEST'
     ORDER BY 1, 2;
     
     SAVTIME                      ROWCNT
     ---------------------------------------- ----------
     20-APR-19 07.50.59.258718 AM -04:00
     20-APR-19 08.09.20.514543 AM -04:00           1000
     20-APR-19 08.15.35.768188 AM -04:00           1000
     
Allez, on continue :-)
     SQL> insert into zztest select level+3000, systimestamp from dual connect by level < 2001;
     2000 rows created.
     
     SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');
     
Tiens tiens, j'ai l'impression qu'Oracle stocke le nombre de lignes concernées par une opération MAIS pas le total : 2 INSERT de 1000 et 1 de 2000 rows.
En réalité, comme on le verra plus loin, la table est mise à jour de façon décalée; la dernière ligne est le nb de lignes dans la table après les deux INSERTs de 1000 lignes.
     SQL> select SAVTIME, ROWCNT
     from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
     WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
     OBJ.OWNER = 'HR' AND
     OBJECT_NAME = 'ZZTEST'
     ORDER BY 1, 2;
     
     SAVTIME                      ROWCNT
     ---------------------------------------- ----------
     20-APR-19 07.50.59.258718 AM -04:00
     20-APR-19 08.09.20.514543 AM -04:00           1000
     20-APR-19 08.15.35.768188 AM -04:00           1000
     20-APR-19 12.33.19.012796 PM -04:00           2000
     
Et si on DELETE? Et si on UPDATE?
     SQL> delete from zztest where rownum < 501;
     500 rows deleted.
     
     SQL> commit;
    
Alors là, j'y comprends plus rien... Je supprime 500 enregistrements et Oracle me dit qu'il y a 4000 lignes dans la table au lieu de 3500... J'ai l'impression qu'il y a un décalage entre la modification des données et la mise à jour de sys.WRI$_OPTSTAT_TAB_HISTORY ou bien qu'il faille générer deux fois les stats pour avoir les bonnes données.
     SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');

     SQL> select SAVTIME, ROWCNT
     from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
     WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
     OBJ.OWNER = 'HR' AND
     OBJECT_NAME = 'ZZTEST'
     ORDER BY 1, 2;
     
     SAVTIME                      ROWCNT
     ---------------------------------------- ----------
     20-APR-19 07.50.59.258718 AM -04:00
     20-APR-19 08.09.20.514543 AM -04:00           1000
     20-APR-19 08.15.35.768188 AM -04:00           1000
     20-APR-19 12.33.19.012796 PM -04:00           2000
     20-APR-19 12.37.07.359142 PM -04:00           4000
     
     SQL> select count(*) from zztest;
       COUNT(*)
     ----------
           3500
     
Bon, juste un pb de temps, maintenant c'est OK mais j'ai du mal à comprendre la logique de mise à jour de cette table... On a bien dans ROWCNT le nombre de lignes dans la table et pas le nombre de lignes concernées par l'opération : par exemple on ne voit pas une ligne de 500 DELETEs. Cette table est donc bien un historique des lignes par table, et elle est mise à jour quand il y a une opération DML suivie d'un calcul des stats APRES que quelques minutes se soient écoulées.
     SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');
     
     SQL> select SAVTIME, ROWCNT
     from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
     WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
     OBJ.OWNER = 'HR' AND
     OBJECT_NAME = 'ZZTEST'
     ORDER BY 1, 2;
     
     SAVTIME                      ROWCNT
     ---------------------------------------- ----------
     20-APR-19 07.50.59.258718 AM -04:00
     20-APR-19 08.09.20.514543 AM -04:00           1000
     20-APR-19 08.15.35.768188 AM -04:00           1000
     20-APR-19 12.33.19.012796 PM -04:00           2000
     20-APR-19 12.37.07.359142 PM -04:00           4000
     20-APR-19 12.40.00.087303 PM -04:00           3500
     6 rows selected.
      
Maintenant j'UPDATE.
     SQL> update zztest set id = (select max(id) from zztest) + rownum where rownum < 11;
     10 rows updated.
     
     SQL> commit;
     
     SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');
     
Bon ben cela ne change rien, normal puisque le nombre de lignes dans la table reste le même :-)
     SQL> select SAVTIME, ROWCNT
     from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
     WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
     OBJ.OWNER = 'HR' AND
     OBJECT_NAME = 'ZZTEST'
     ORDER BY 1, 2;
     
     SAVTIME                      ROWCNT
     ---------------------------------------- ----------
     20-APR-19 07.50.59.258718 AM -04:00
     20-APR-19 08.09.20.514543 AM -04:00           1000
     20-APR-19 08.15.35.768188 AM -04:00           1000
     20-APR-19 12.33.19.012796 PM -04:00           2000
     20-APR-19 12.37.07.359142 PM -04:00           4000
     20-APR-19 12.40.00.087303 PM -04:00           3500
     20-APR-19 12.48.52.092688 PM -04:00           3500
     
     7 rows selected.
     
Un dernier test, pour montrer que cette table est mise à jours lors du calcul des stats MAIS, attention, on a vu que parfois la colonne ROWCNT était mise à jour en décalé...
     SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');

     SQL> select SAVTIME, ROWCNT
          from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
          WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
          OBJ.OWNER = 'HR' AND
          OBJECT_NAME = 'ZZTEST'
          ORDER BY 1, 2;

     SAVTIME                                     ROWCNT
     --------------------------------------------------------------------------- ----------
     20-APR-19 07.50.59.258718 AM -04:00
     20-APR-19 08.09.20.514543 AM -04:00                          1000
     20-APR-19 08.15.35.768188 AM -04:00                          1000
     20-APR-19 12.33.19.012796 PM -04:00                          2000
     20-APR-19 12.37.07.359142 PM -04:00                          4000
     20-APR-19 12.40.00.087303 PM -04:00                          3500
     20-APR-19 12.48.52.092688 PM -04:00                          3500
     22-APR-19 06.27.56.352836 AM -04:00                          3500

     8 rows selected.
    
Malheureusement les autres données de la table ne sont pas très utiles pour identifier les différentes opérations : INSERT, DELETE...
     SQL> select
          flags,
          spare2,
          spare3,
          spare4,
          spare5,
          spare6
         from SYS.wri$_optstat_tab_history WRI, DBA_OBJECTS OBJ
         WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
        OBJ.OWNER = 'HR' AND
        OBJECT_NAME = 'ZZTEST'
        ORDER BY 1, 2
     
     FLAGS     SPARE2      SPARE3 SPARE4     SPARE5     SPARE6
     ---------- ---------- ---------- ---------- ---------- ----------
          0
         10
         10
         10
         10
         10
         10
         10
     8 rows selected.



Voilà, article fini; cette table est bien pratique MAIS difficile à apprivoiser :-)



Posté par David DBA à 12:33 - - Permalien [#]
Tags : , ,