| ORA-01466 - but no one changed the table in question [message #655679] |
Wed, 07 September 2016 16:31  |
 |
markhooper99
Messages: 19
Registered: October 2013
Location: Calgary
|
Junior Member |
|
|
So, I tried to use a flashback query today to examine the contents of a table from Sept 2 (5 days ago) and got an ORA-01466 error telling me that the table structure had changed. This dumbfounded me because I know the table structure has most definitely not changed. Today is Sept 7 - I've got flashback available to Aug 29, according to V$FLASHBACK_DATABASE_LOG
I examined the LAST_DDL_TIME for the table in question and was surprised to see that it was Sept 6 (yesterday). I quickly checked the table's structure and it is unchanged from its original definition from a few years ago. I did run a flashback query on this table using a date/time *after* the LAST_DDL_TIME and it worked fine.
I checked the indexes for the table and was surprised that the primary key index had a LAST_DDL_TIME exactly the same as the table. Hmmmm.... ok.
I don't have auditing enabled so I can't tell who or what changed the table and index but I can assure you no person did.
The only thing I can think of that might affect the LAST_DDL_TIME is statistics generation?(i.e. the automated Oracle task). The table in question sees massive inserts and deletes as its a 'logging' table for an automated process so perhaps this table gets picked up by Oracle when it does it stats calcs. I tried computing stats manually on a test table to see if the LAST_DDL_TIME got messed with and it didn't. But, if I did an 'alter index ... rebuild' for the test table it did change the LAST_DDL_TIME for the table - but stats regeneration wouldn't 'rebuild' an index would it?
|
|
|
|
| Re: ORA-01466 - but no one changed the table in question [message #655682 is a reply to message #655679] |
Thu, 08 September 2016 00:55  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Several things can change LAST_DDL_TIME without actually changing the structure of the table:
SQL> select last_ddl_time from user_objects where object_name='T';
LAST_DDL_TIME
-------------------
11/08/2016 19:40:20
1 row selected.
SQL> grant select on t to scott;
Grant succeeded.
SQL> select last_ddl_time from user_objects where object_name='T';
LAST_DDL_TIME
-------------------
08/09/2016 07:46:52
1 row selected.
SQL> alter table t move;
Table altered.
SQL> select last_ddl_time from user_objects where object_name='T';
LAST_DDL_TIME
-------------------
08/09/2016 07:52:15
1 row selected.
SQL> exec dbms_stats.gather_table_stats(user, 'T',method_opt=>'for all columns size 100');
PL/SQL procedure successfully completed.
SQL> select last_ddl_time from user_objects where object_name='T';
LAST_DDL_TIME
-------------------
08/09/2016 07:52:15 [Updated on: Thu, 08 September 2016 00:55] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
