Re: drop partition - with foreign keys enabled?

http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm Oracle9i Database Administrator's Guide
Release 2 (9.2)
Part Number A96521-01
Chapter 17 - About half way in, verbatim: Dropping a Partition Containing Data and Referential Integrity Constraints If a partition contains data and the table has referential integrity constraints, choose either of the following methods to drop the table partition. This table has a local index only, so it is not necessary to rebuild any indexes.

Method 1:

Disable the integrity constraints, issue the ALTER TABLE ... DROP PARTITION statement, then enable the integrity constraints:

ALTER TABLE sales

   DISABLE CONSTRAINT dname_sales1;
ALTER TABLE sales DROP PARTITTION dec98; ALTER TABLE sales

   ENABLE CONSTRAINT dname_sales1;

This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table.

Method 2:

Issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE ... DROP PARTITION statement. The DELETE statement enforces referential integrity constraints, and also fires triggers and generates redo and undo log.

DELETE FROM sales WHERE TRANSID < 10000; ALTER TABLE sales DROP PARTITION dec94;

This method is most appropriate for small tables or for large tables when the partition being dropped contains a small percentage of the total data in the table.

I think I'll opt for method 3 - just drop the partition, without jumping through any flaming hoops.

-Don Granaman

• Original Message ----- From: Jacques Kilchoer To: Don Granaman ; oracle-l_at_freelists.org Sent: Thursday, January 20, 2005 10:32 AM Subject: RE: drop partition - with foreign keys enabled?

  Yes, the documentation is wrong. Where did it say this in the documentation? I couldn't find it.   I can drop a partition from order_detail without disabling order_detail_fk1.

  -----Original Message-----
  From: Don Granaman [mailto:granaman_at_cox.net]   Sent: mercredi, 19. janvier 2005 23:10   To: Jacques Kilchoer; oracle-l_at_freelists.org   Subject: Re: drop partition - with foreign keys enabled?

  [I replied to this last night, but it was rejected for over-quoting.]

  Perhaps I was not clear...

  The situation is, using your example, that the docs say that to drop (or   exchange) a partition from the (referencing) ORDER_DETAIL table, one has to   first disable the foreign key ORDER_DETAIL_FK1. This is what seems odd.   One would not need to disable this foreign key prior to truncating a   non-partitioned ORDER_DETAIL table. I suspect that this is simply a   documentation bug.

  It does makes perfect sense that one would have to disabled the   ORDER_DETAIL_FK1 constraint prior to dropping (or exchanging) a partition   from the (referenced) ORDERS table - for the reason you mentioned.

  Don Granaman

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 20 2005 - 23:16:01 CST