Blog d'un DBA sur le SGBD Oracle et SQL

02 juin 2019

Plus de MCD, plus de MPD... : comment trouver la colonne et la table où est stocké un libellé? - Find the column of this label

 

Introduction
Il arrive que dans un SI les développeurs ne sachent plus associer un écran d'une application à une table de la base : application d'un éditeur tiers ayant fait faillite donc accès au code source impossible, crash du disque dur contenant la documentation, départ des développeurs experts sans passage de témoin, etc etc...

On va voir alors comment, pour un écran et un champ précis, retrouver la fameuse table en nous aidant du dictionnaire de données d'Oracle.


 
Points d'attention
Aucun.



Base de tests
Une base Oracle 12.



Exemples
J'utilise le schéma HR d'Oracle, livré en standard sur toute base de test dont voici les tables de ma base.
     SQL> select * from cat where table_type = 'TABLE' order by table_name;
     TABLE_NAME                                                             TABLE_TYPE
     ------------------------------------------------------------------------- ---------
     COUNTRIES                                                             TABLE
     DEPARTMENTS                                                             TABLE
     EMPLOYEES                                                             TABLE
     JOBS                                                                 TABLE
     JOB_HISTORY                                                             TABLE
     LOCATIONS                                                             TABLE
     LOG_SPEED                                                             TABLE
     REGIONS                                                              TABLE
     
     8 rows selected.
    
Le problème est donc le suivant : nous avons un écran où le libellé TEXAS apparaît et nous voulons savoir à quelle table et à quelle colonne ce champ est rattaché. Les indices sont : un libellé de type chaîne de caractère et, selon le client, le user propriétaire des données applicatives est HR.

On construit donc un ordre SQL pour générer des SELECT concernant les colonnes et tables du user HR correspondant à ces critères. Le résultat va dans un fichier sql qu'on va exécuter ensuite.

On peut aussi filtrer sur d'autres users et ajouter CHAR pour le type de la colonne. Il est possible aussi
, via DBA_TAB_COLS, d'utiliser la colonne DATA_LENGTH pour limiter les colonnes à sélectionner. Par exemple, on remplit le champ à son maximum avec un libellé genre 'ABCD...', on compte le nombre de caractères et on filtre ensuite sur la taille max de ce champ pour récupérer moins de colonnes. 

Attention à bien filtrer sur les objets de type TABLE car dans DBA_TAB_COLS, nous avons à la fois les colonnes des tables MAIS aussi les colonnes des vues, contrairement à ce qu'indique le nom de l'objet.
     SQL> spool search.sql

     SQL> select
             'SELECT count(*),
             ''' || T.table_name || ''',
             ''' || T.column_name || '''' ||
             ' FROM ' || O.object_name ||
             ' WHERE upper(' || T.column_name || ') = ''TEXAS''
             HAVING count(*) > 0;'
         FROM dba_objects O, dba_tab_cols T
         WHERE     T.table_name = O.object_name and
                 O.object_type = 'TABLE' and
                 T.owner = 'HR' and
                 T.data_type = 'VARCHAR2'
         ORDER BY T.table_name,
                  T.column_name ;         

Vous constaterez que j'ai deux fois la même requête, sans arriver à éliminer le doublon. Impossible en effet en SQL de faire un DISTINCT avec un ORDER BY. Soit je trie soit j'élimine les doublons mais pas les deux...
Voilà le résultat de cet ordre SQL.

'SELECTCOUNT(*),'''||T.TABLE_NAME||''','''||T.COLUMN_NAME||''''||'FROM'||O.OBJECT_NAME||'WHEREUPPER('||T.COLUMN_NAME||')=''TEXAS''HAVINGCOUNT(*)>0;'
------------------------------------------------------------------------------------------------------------------------------------------
     SELECT count(*), 'COUNTRIES', 'COUNTRY_NAME' FROM COUNTRIES WHERE upper(COUNTRY_NAME) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'COUNTRIES', 'COUNTRY_NAME' FROM COUNTRIES WHERE upper(COUNTRY_NAME) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'DEPARTMENTS', 'DEPARTMENT_NAME' FROM DEPARTMENTS WHERE upper(DEPARTMENT_NAME) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'DEPARTMENTS', 'DEPARTMENT_NAME' FROM DEPARTMENTS WHERE upper(DEPARTMENT_NAME) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'EMPLOYEES', 'EMAIL' FROM EMPLOYEES WHERE upper(EMAIL) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'EMPLOYEES', 'EMAIL' FROM EMPLOYEES WHERE upper(EMAIL) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'EMPLOYEES', 'FIRST_NAME' FROM EMPLOYEES WHERE upper(FIRST_NAME) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'EMPLOYEES', 'FIRST_NAME' FROM EMPLOYEES WHERE upper(FIRST_NAME) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'EMPLOYEES', 'JOB_ID' FROM EMPLOYEES WHERE upper(JOB_ID) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'EMPLOYEES', 'JOB_ID' FROM EMPLOYEES WHERE upper(JOB_ID) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'EMPLOYEES', 'LAST_NAME' FROM EMPLOYEES WHERE upper(LAST_NAME) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'EMPLOYEES', 'LAST_NAME' FROM EMPLOYEES WHERE upper(LAST_NAME) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'EMPLOYEES', 'PHONE_NUMBER' FROM EMPLOYEES WHERE upper(PHONE_NUMBER) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'EMPLOYEES', 'PHONE_NUMBER' FROM EMPLOYEES WHERE upper(PHONE_NUMBER) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'JOBS', 'JOB_ID' FROM JOBS WHERE upper(JOB_ID) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'JOBS', 'JOB_ID' FROM JOBS WHERE upper(JOB_ID) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'JOBS', 'JOB_TITLE' FROM JOBS WHERE upper(JOB_TITLE) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'JOBS', 'JOB_TITLE' FROM JOBS WHERE upper(JOB_TITLE) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'JOB_HISTORY', 'JOB_ID' FROM JOB_HISTORY WHERE upper(JOB_ID) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'JOB_HISTORY', 'JOB_ID' FROM JOB_HISTORY WHERE upper(JOB_ID) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'LOCATIONS', 'CITY' FROM LOCATIONS WHERE upper(CITY) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'LOCATIONS', 'CITY' FROM LOCATIONS WHERE upper(CITY) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'LOCATIONS', 'POSTAL_CODE' FROM LOCATIONS WHERE upper(POSTAL_CODE) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'LOCATIONS', 'POSTAL_CODE' FROM LOCATIONS WHERE upper(POSTAL_CODE) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'LOCATIONS', 'STATE_PROVINCE' FROM LOCATIONS WHERE upper(STATE_PROVINCE) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'LOCATIONS', 'STATE_PROVINCE' FROM LOCATIONS WHERE upper(STATE_PROVINCE) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'LOCATIONS', 'STREET_ADDRESS' FROM LOCATIONS WHERE upper(STREET_ADDRESS) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'LOCATIONS', 'STREET_ADDRESS' FROM LOCATIONS WHERE upper(STREET_ADDRESS) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'REGIONS', 'REGION_NAME' FROM REGIONS WHERE upper(REGION_NAME) = 'TEXAS' HAVING count(*) > 0;
     SELECT count(*), 'REGIONS', 'REGION_NAME' FROM REGIONS WHERE upper(REGION_NAME) = 'TEXAS' HAVING count(*) > 0;
     
     30 rows selected.
    
     SQL> spool off;
     
On exécute le fichier sql généré.
BINGO : le libellé "TEXAS" est dans la table LOCATIONS, colonne STATE_PROVINCE :-)

Petit problème, le résultat est pollué par les lignes des SELECTs ne rapportant aucun enregistrement.
     SQL> @search
     SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
     SP2-0734: unknown command beginning "'SELECTCOU..." - rest of line ignored.
     ...
     no rows selected
     no rows selected
     no rows selected
     ...
       COUNT(*) 'LOCATION 'STATE_PROVINC
     ---------- --------- --------------
          1 LOCATIONS STATE_PROVINCE
     
       COUNT(*) 'LOCATION 'STATE_PROVINC
     ---------- --------- --------------
          1 LOCATIONS STATE_PROVINCE
     ...
     no rows selected
     no rows selected
     no rows selected
     no rows selected
     ...
     SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
    
Éliminons donc ces lignes parasites "no rows selected" avec le paramètre FEEDBACK de SQL*Plus à OFF.
     SQL> set FEED off
     
     SQL> @search
     SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
     SP2-0734: unknown command beginning "'SELECTCOU..." - rest of line ignored.
     
       COUNT(*) 'LOCATION 'STATE_PROVINC
     ---------- --------- --------------
          1 LOCATIONS STATE_PROVINCE
     
       COUNT(*) 'LOCATION 'STATE_PROVINC
     ---------- --------- --------------
          1 LOCATIONS STATE_PROVINCE
     SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.


Posté par David DBA à 14:38 - - Permalien [#]
Tags : , ,


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.

DERNIER POINT : avant de changer le propriétaire de la table, il faut être sur de TOUTES les tables qui ont un champ OWNER à modifier. Pour cela, on récupère l'ordre DDL de création de la table, on active la trace 10046, on crée la même table MAIS avec un nom différent et on regarde s'il n'y a pas eu un INSERT en plus que ceux vus ci-dessus. Par exemple, quid d'une table partitionnée? Est-ce qu'elle ne génère pas un INSERT avec une colonne OWNER? Etc etc.



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