I have found duplicates in the all_tab_columns data dictionary view, where both rows have the same owner, table_name, and column_name, but have different column_id and one row shows the column as nullable and the other row shows the column as not nullable. However, only one such entry exists in the user_tab_columns data dictionary view. Is there any rational explanation for this or is corruption the only possibility?
The database in question is used for development and testing and is periodically modified to match the production database. I know that one of the DBA's has been using TOAD to do this, but I don't know what the exact process has been and he is on vacation at the moment.
This problem exists in multiple tables and multiple columns. In each case, the columns in question were formerly part of a primary key, but that has been changed so that the primary key now uses other columns.
Below is just one small example that shows the duplicates in all_tab_columns, but not in user_tab_columns.
Barbara Boehmer
SQL*Plus: Release 8.0.6.0.0 - Production on Wed Mar 12 23:57:07 2003
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL> SELECT table_name, column_name, column_id, nullable
2 FROM all_tab_columns
3 WHERE user = USER
4 AND table_name = 'ADDRESSES'
5 AND column_name = 'ADDR__EVNT_SEQ'
6 /
TABLE_NAME COLUMN_NAME COLUMN_ID N
------------------------------ ------------------------------ --------- -
ADDRESSES ADDR__EVNT_SEQ 39 Y
ADDRESSES ADDR__EVNT_SEQ 4 N
SQL> SELECT table_name, column_name, column_id, nullable
2 FROM user_tab_columns
3 WHERE user = USER
4 AND table_name = 'ADDRESSES'
5 AND column_name = 'ADDR__EVNT_SEQ'
6 /
TABLE_NAME COLUMN_NAME COLUMN_ID N
------------------------------ ------------------------------ --------- -
ADDRESSES ADDR__EVNT_SEQ 39 Y