In Oracle 8, TOYS failed to drop a primary key when the supporting (unique) index was owned by another schema, why?
In early versions of Oracle there was a bug which meant that if a unique index was created in one schema and later used as a supporting index for a primary key in another schema you could not drop the primary key. You had to actually drop the table. Oracle [sort of] fixed this. In 9i if you do this the schema owning the primary key also needs the system privilege DROP ANY INDEX to drop (or disable) its own primary key. Not exactly intuitive! One could argue that this is no different to granting INDEX on a table to another schema and if that schema creates an index on your table you cannot drop that index you have to drop the table [assuming of course that you do not have the system privilege DROP ANY INDEX]. From 9i, TOYS gets around this problem by using the KEEP INDEX clause on the DROP [or DISABLE] primary [or unique] key commands. In versions prior Oracle 9i, if you grant anyone index privileges on your tables you had better have the system privilege DROP ANY INDEX.
