Subject Re: Ghost dependencies
Author Dan Fisher
OK I think I've answered my own question (though I'm not sure I fully
understand what's going on):

The dll which runs the scripts checks whether any DDL statement have
been run, and re-applies role permissions as necessary. If I
suppress this then the problem goes away.

The lesson seems to be to not apply permissions between dropping an
object's dependencies and dropping the object itself.

I hope this helps others avoid the problem in future, and I'd be
interested if anyone wants to explain it.


> Thanks for your input, Helen.
> >
> > Running a backup from an exclusive clean connection would have
> a prerequisite to a job like this... If backup and restore really
> *is* the only way, it suggests that you might have started the
> operation on a database that had dependent garbage.
> The job is indeed being run in exclusive mode, after a backup-and-
> restore. The scripts are bing run via a script-running dll, rather
> than a propietary tool (these upgrades go out to 150 - 200 sites).
> The error occurs in all circumstances I can think of (garbage-
> collecting backup, backup-and-restore, service restart, all of the
> above, etc). The only thing that does the trick is backup-and-
> restore at the point when the eror occurs.
> > Destroying metadata in order to work around the built-in
> consistency protection is NOT a recommended way to restructure
> databases. The only operation you should *ever* do on system
> is SELECT.
> I did say I'd rather not do that :-)
> >
> > Another thing to bear in mind is that working in exclusive mode
> won't guarantee interference from other users if you have
> applications or users able to log in as SYSDBA or Owner. If that
> your situation then rename the database before you start trying to
> this restructuring.
> Exclusive mode is pretty-much garaunteed here because the users log-
> in as themselves, not SYSDBA/Owner. Besides, at the moment this
> happening in complete isolation on development and test machines.
> So if, as you say... "it suggests that you might have started the
> operation on a database that had dependent garbage" and it's not
> sorted by a backup-and-restore before the restructure, have you any
> suggestions as to what sort of thing I should be looking for?
> Regards, Dan