You must specify some clause after table. To enable or disable triggers, the triggers must be in your schema or you must have the ALTER ANY TRIGGER system privilege. You need these privileges because Oracle Database creates an index on the columns of the unique or primary key in the schema containing the table. When a partitioning operation cascades to reference-partitioned child tables, privileges are not required on the reference-partitioned child tables.Īdditional Prerequisites for Constraints and Triggers To enable a unique or primary key constraint, you must have the privileges necessary to create an index on the table. You must also have space quota in the tablespace in which space is to be acquired in order to use the add_table_partition, modify_table_partition, move_table_partition, and split_table_partition clauses. This actually led to another workaround – if your recyclebin is on, then you can first drop the table without specifying the purge keyword (so the table is just “moved” to the recyclebin) and then purge the table.The table must be in your own schema, or you must have ALTER object privilege on the table, or you must have ALTER ANY TABLE system privilege.Īdditional Prerequisites for Partitioning Operations If you are not the owner of the table, then you need the DROP ANY TABLE privilege in order to use the drop_table_partition or truncate_table_partition clause. Thanks to Jonathan Lewis, Liron Amitzi, Friedhold Matz and Franck Pachot, who repeated my tests in their environments, I found the flaw in my original post and fixed it. And I used the DROP TABLE statement without the PURGE option. The reason is that I was careless and ignored one crucial difference between my 11g and 12c environments – in my 11g database the recyclebin was off, while in my 12c database it was on. When I initially wrote this post, I thought that this behavior had been fixed in 12c. SQL> alter table children drop constraint fk Īnd now we succeeded (well, now it really had no excuses to fail). Let’s drop the constraint (again, this operation does not lock the referenced table), and retry dropping the table: SQL> alter table children disable constraint fk Let’s disable the foreign key constraint (note that this operation does not lock the parents table), and retry dropping the table: It failed (because it tried to lock the parents table in “share” mode). ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired In one session we lock the parents table (simply by updating a record):Īnd now in another session we try to drop the children table: SQL> insert into parents values (1,null) Here is a simple test I executed in 11.2.0.4, 12.1.0.2 and 12.2.0.1:ģ parent_id number constraint fk references parents Dropping the constraint does not lock the referenced table. There is a workaround (which I think proves my last sentence): it’s possible to drop the constraint first, and then to drop the table. The referenced table was locked by other sessions, and therefore the DROP TABLE operation failed.Įven if the foreign key constraint is enabled, there is no good reason in my opinion to lock the referenced table all the more so if it’s disabled. That was weird because I knew that nobody had been using this table for months, and that the table had no enabled foreign keys.Ī quick investigation revealed the cause – the DROP TABLE operation tried to lock another table (in the quite aggressive “Share” mode) that was referenced by a disabled foreign key from the table I was trying to drop. I tried to drop a table today and failed due to “ ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |