RE: We want to truncate a table in 11.2 which has a Primary Key

From: Michael Dinh <mdinh_at_XIFIN.Com>
Date: Thu, 1 Dec 2011 13:53:43 -0800
Message-ID: <D29F9902E534D5478F2E83FD6A44B30648ED191818_at_mail02.mba.xifin.com>



One of the DBAs here wants to truncate a table in 11.2 which has a Primary Key -- really?

Is this being done in OLTP or DW and why does the DBA want to truncate table with PK?

In DW, you can set have rely for PK and FK which will allow truncate on table with PK, but table with FK are truncated as well, just don't need to be done in order.

Michael Dinh

Disparity Breaks Automation (DBA)

Confidence comes not from always being right but from not fearing to be wrong - Peter T Mcintyre  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark Sent: Thursday, December 01, 2011 1:29 PM To: oracle-l_at_freelists.org
Subject: RE: We want to truncate a table in 11.2 which has a Primary Key

To my knowledge "cascade constraints" which is available with the alter table, drop table, drop tablespace, and revoke all commands has never been part of the Truncate command. A check of the 11.2 SQL manual entry for truncate does not show the option.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfson Larry - lwolfs Sent: Thursday, December 01, 2011 2:54 PM To: oracle-l_at_freelists.org
Subject: We want to truncate a table in 11.2 which has a Primary Key

One of the DBAs here wants to truncate a table in 11.2 which has a Primary Key which normally you'd get round with a cascade constraints The cascade constraint isn't part of the syntax but it used to either take it or ignore it. Not sure which. SYS.LARRY> truncate table XYZ cascade constraint drop storage;  truncate table XYZ cascade constraint drop storage

                              *

ERROR at line 1:
ORA-03291: Invalid truncate option - missing STORAGE keyword

I've never seen Oracle complain about the missing storage keyword before, but maybe they tightened the syntax.

SYS.LARRY> truncate table XYZ drop storage cascade constraint;  truncate table XYZ drop storage cascade constraint

  • ERROR at line 1: ORA-03291: Invalid truncate option - missing STORAGE keyword

SYS.LARRY> truncate table XYZ drop storage; truncate table XYZ drop storage

                *

ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Any Ideas?



The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.


--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Dec 01 2011 - 15:53:43 CST

Original text of this message