Home » RDBMS Server » Server Administration » ORA-01466 - but no one changed the table in question (Oracle Enterprise Edition, 11.2.0.3.4, Oracle Linux)
ORA-01466 - but no one changed the table in question [message #655679] Wed, 07 September 2016 16:31 Go to next message
markhooper99
Messages: 16
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
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.
but not gathering the statistics:
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

Re: ORA-01466 - but no one changed the table in question [message #655690 is a reply to message #655679] Thu, 08 September 2016 01:55 Go to previous message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
ThisQuote:
I've got flashback available to Aug 29, according to V$FLASHBACK_DATABASE_LOG
is not relevant. Flashback logs relate to Database Flashbsck, not to Flashback Query. You would need an undo tablespace the size of Jupiter to flashback five days.
Previous Topic: Login in DATABASE From Server
Next Topic: install oracle 11g r2 on aix with san storage
Goto Forum:
  


Current Time: Fri Oct 19 09:03:13 CDT 2018