We had an unusual problem with datapump this week when exporting a large schema. A couple of small 'static data' tables were consistently throwing the error:
ORA-01466: unable to read data - table definition has changed
These tables were done towards the end of the export (which ran for 3 hours by the way even with parallel 6) so i could more understand a snapshot tool old error but this was actually saying the table definition had changed. I've seen this before with datapump with 'temporary' tables that get truncated periodically but this wasn't one of those cases.
The team i spoke to were insistent that there was nothing running that could be causing this so it was all a little odd.
The next step we did was to put the tablespace in read only mode as all of the schema was located in one tablespace and was not sharing with anything else.
The error still came even after the tablespace was set to read only.......
Strange indeed, i did a quick search on metalink and found there were a couple of bugs which would throw this error but both should have been fixed in our version (11.2.0.3).
So what was going on?
Well next thing i did was to check the last_ddl_time on the objects in question to see if they genuinely had changed - and they had!
At exactly 20:00:00 (in fact in another schema at the exact same time) - this smells like a scheduled task...
So lets check them (ignoring the fact that there is 'nothing' running).
And we find something set to run every 2 hours that seems to grant select on these tables to some generic reporting user - but that can be changing the table definition can it?
Lets do a quick test.....
SQL> select object_type,last_ddl_time from dba_objects where object_name='DUAL';
OBJECT_TYPE LAST_DDL_
----------------------- ---------
TABLE 21-DEC-10
SYNONYM 21-DEC-10
SQL> grant select on dual to system;
Grant succeeded.
SQL> select object_type,last_ddl_time from dba_objects where object_name='DUAL';
OBJECT_TYPE LAST_DDL_
----------------------- ---------
TABLE 13-JUN-16
SYNONYM 21-DEC-10
Interesting so granting permission on an object changes the last ddl time and in the case of tables could mess up a running export....
As this is a metadata change to the dictionary making the table/tablespace read only does not stop it from happening..
I previously wouldn't have thought this would count as altering a table definition (but it is of course DDL) - put thinking about it another way extracting the table definition in full would include the grants so i guess it kind of is a change that should alter the last ddl time.
Comments
Post a Comment