Doppelter Spaltenname in Tabelle
Das Problem
Bei einem Kunden haben wir vor einiger Zeit ORA-00600 Fehler im alert.log der Datenbank festgestellt. Diese traten immer nachts auf, wenn der automatische Statistikjob lief. Bei der Analyse stellte sich heraus, das die Statistiken einer bestimmten Tabelle nicht neu generiert werden konnten.
SQL> exec dbms_stats.gather_table_stats('BASIS','VG_XXXXXXXXXXX', method_opt=>'for all columns size 1');
BEGIN dbms_stats.gather_table_stats('BASIS','VG_XXXXXXXXXXX', method_opt=>'for all columns size 1'); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at "SYS.DBMS_STATS", line 34850
ORA-06512: at line 1
Daher haben wir zuerst versucht, die Statistiken komplett zu löschen, auch dies war nicht erfolgreich.
SQL> exec dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX');
BEGIN dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX'); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at "SYS.DBMS_STATS", line 17279
ORA-06512: at line 1
SQL> exec dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX',force=>true);
BEGIN dbms_stats.delete_table_stats('BASIS','VG_XXXXXXXXXXX',force=>true); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST) violated
ORA-06512: at "SYS.DBMS_STATS", line 17279
ORA-06512: at line 1
Also begannen wir das Problem zu analysieren und stellten fest, dass im Data Dictionary offenbar ein Spaltenname doppelt vorhanden ist. Ein "describe" zeigt jedoch keine doppelten Spalten.
SQL> desc owner.vg_xxxxxxxxxx
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
[...]
XXXXXXXXXXXXXXX_ID NUMBER(19)
XXXXXXXXXXXXXXXXX_XXX NUMBER(19)
XXXXXXXXXXXXXXXXX_ID NUMBER(19)
NAMENSZUSATZ NVARCHAR2(4000)
ENTITY_TIMESTAMP TIMESTAMP(9)
OPTLOCKVERSION NOT NULL NUMBER(19)
DATENUEBERNAHMETYP NVARCHAR2(4000)
ZUGANGGEBUCHT NOT NULL NUMBER(1)
UEBERSTELLER NOT NULL NUMBER(1)
XXXXXXXXXXXXXX NOT NULL NUMBER(1)
SQL> select column_id, column_name from dba_tab_columns where table_name='VG_XXXXXXXXXXX' and column_name like 'Z%'
COLUMN_ID COLUMN_NAME
---------- --------------------
59 ZUGANGGEBUCHT
59 ZUGANGGEBUCHT
Als nächstes haben wir direkt in der Dictionary-Tabelle nach der fraglichen Spalte gesucht, dort wiederum existieren keine doppelten Einträge.
SQL> select OBJ#, COL#, NAME from col$ where name ='ZUGANGGEBUCHT';
OBJ# COL# NAME
---------- ---------- --------------------
94177 59 ZUGANGGEBUCHT
95522 74 ZUGANGGEBUCHT
95523 68 ZUGANGGEBUCHT
95550 74 ZUGANGGEBUCHT
95556 60 ZUGANGGEBUCHT
Nebenbei, unsere fragliche Tabelle trägt die Object-ID 94177:
SQL> select object_id from dba_objects where object_NAME='VG_XXXXXXXXXX';
OBJECT_ID
----------
94177
Die Ursache musste also woanders liegen. Als nächtes haben wir uns die DBA-Views vorgenommen um deren Datenquellen zu identifizieren.
SQL> select text from dba_views where view_name='DBA_TAB_COLUMNS';
TEXT
--------------------------------------------------------------------------------
select OWNER, TABLE_NAME,
COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM, DEFAULT_ON_NULL,
IDENTITY_COLUMN, SENSITIVE_COLUMN,
EVALUATION_EDITION, UNUSABLE_BEFORE, UNUSABLE_BEGINNING
from DBA_TAB_COLS
where USER_GENERATED = 'YES'
SQL> select text from dba_views where view_name='DBA_TAB_COLS';
TEXT
--------------------------------------------------------------------------------
select
OWNER, TABLE_NAME,
COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
GLOBAL_STATS,
USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
V80_FMT_IMAGE, DATA_UPGRADED, HIDDEN_COLUMN, VIRTUAL_COLUMN,
SEGMENT_COLUMN_ID, INTERNAL_COLUMN_ID, HISTOGRAM, QUALIFIED_COL_NAME,
USER_GENERATED, DEFAULT_ON_NULL, IDENTITY_COLUMN, SENSITIVE_COLUMN,
EVALUATION_EDITION, UNUSABLE_BEFORE, UNUSABLE_BEGINNING
from dba_tab_cols_v$
DBA_TAB_COLUNMS benutzt selbst also DBA_TAB_COLS_V$. Deren nicht ganz kurzen Quelltext haben wir auf das Wesentliche reduziert, mit folgendem Ergebnis.
SQL> select
2 , sys."_CURRENT_EDITION_OBJ" o
3 , sys.hist_head$ h
4 c.col#, c.obj#, c.name, c.intcol#
5 , sys.tab$ t
6 where o.obj# = c.obj#
7 , h.obj#, h.intcol#
8 from sys.col$ c
9 or
10 (o.type# = 2 /* tables, excluding iot - overflow and nested tables */
11 and
12 , sys."_CURRENT_EDITION_OBJ" o
13 and (bitand(t.property, 512) = 512 or
14 bitand(t.property, 8192) = 8192))))
15 , sys.hist_head$ h
16 , sys.user$ u
17 , sys.coltype$ ac
18 , sys.obj$ ot
19 , sys."_BASE_USER" ut
20 , sys.tab$ t
21 where o.obj# = c.obj#
22 and o.owner# = u.user#
23 and o.obj# = t.obj#(+)
24 and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
25 and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
26 and ac.toid = ot.oid$(+)
27 and ot.type#(+) = 13
28 and ot.owner# = ut.user#(+)
29 and (o.type# in (3, 4) /* cluster, view */
30 or
31 (o.type# = 2 /* tables, excluding iot - overflow and nested tables */
32 and
33 not exists (select null
34 from sys.tab$ t
35 where t.obj# = o.obj#
36 and (bitand(t.property, 512) = 512 or
37 bitand(t.property, 8192) = 8192))))
38 and c.name='ZUGANGGEBUCHT';
COL# OBJ# NAME INTCOL# OBJ# INTCOL#
---------- ---------- -------------------- ---------- ---------- ----------
60 95556 ZUGANGGEBUCHT 60
68 95523 ZUGANGGEBUCHT 68
59 94177 ZUGANGGEBUCHT 59 94177 59 <-- Hier entsteht
59 94177 ZUGANGGEBUCHT 59 94177 59 <-- die doppelte Zeile
74 95522 ZUGANGGEBUCHT 74
74 95550 ZUGANGGEBUCHT 74
Hier ist nun tatsächlich die Spalte doppelt zu sehen. Ursache scheint der Join mit HIST_HEAD$ zu sein. Eine kurze Abfrage bestätigt das.
SQL> select COL#, BUCKET_CNT, ROW_CNT, NULL_CNT, TIMESTAMP#, SAMPLE_SIZE from hist_head$ where obj#=94177 and intcol#=59;
COL# BUCKET_CNT ROW_CNT NULL_CNT TIMESTAMP# SAMPLE_SIZE
---------- ---------- ---------- ---------- ------------------- -----------
0 1 0 35656 2017-01-05 22:00:23 5502
0 1 0 35383 2017-01-05 22:00:23 5775
Die Lösung
Eine Recherche in My Oracle Support brachte die Note "Alter Table Drop Column Failing With ORA-00600[16515] (Doc ID 2375301.1)" zum Vorschein, die das Problem beschreibt. Die eigentliche Lösung fanden wir dann in Note "DBMS_STATS.DELETE_TABLE_STATS Fails With ORA-600 [16515] (Doc ID 1233745.1)".
Zuerst haben wir die doppelten Spalten in der HIST_HEAD$ Tabelle sowie die entsprechenden ROWIDs ermittelt.
SQL> SELECT obj#,col#,intcol#,count(*) FROM hist_head$ GROUP BY obj#,col#,intcol# HAVING COUNT(*) >1 ORDER BY obj#;
OBJ# COL# INTCOL# COUNT(*)
---------- ---------- ---------- ----------
94177 0 59 2
SQL> SELECT rowid,obj#,intcol#,timestamp# FROM hist_head$ WHERE obj#=94177 and intcol#=59;
ROWID OBJ# INTCOL# TIMESTAMP#
------------------ ---------- ---------- -------------------
AAAABEAABAAAWh0AAi 94177 59 2017-01-05 22:00:23
AAAABEAABAAAWh0AAj 94177 59 2017-01-05 22:00:23
Die ältere der beiden Zeilen haben wir daraufhin gelöscht. Zu beachten ist, dass wirklich nur genau eine Zeile gelöscht wird.
QL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> SELECT rowid,obj#,intcol#,timestamp# FROM hist_head$ WHERE obj#=94177 and intcol#=59 order by timestamp#;
ROWID OBJ# INTCOL# TIMESTAMP#
------------------ ---------- ---------- -------------------
AAAABEAABAAAWh0AAj 94177 59 2017-01-05 22:00:23 <-- entsprechend der Sortierung ist ds der ältere Datensatz
AAAABEAABAAAWh0AAi 94177 59 2017-01-05 22:00:23
SQL> DELETE FROM hist_head$ WHERE ROWID='AAAABEAABAAAWh0AAj';
1 row deleted.
SQL> COMMIT;
Commit complete.
Nun ist auch der Inhalt der DBA:TAB_COLUMNS View wieder korrekt.
SQL> select column_id, column_name from dba_tab_columns where table_name='VG_XXXXXXXXXXX' and column_name like 'Z%';
COLUMN_ID COLUMN_NAME
---------- --------------------
59 ZUGANGGEBUCHT
Das nächtliche automatische Sammeln der Statistiken funktioniert seither auch wieder problemlos.
Kommentare
Keine Kommentare