Home -> Mailing Lists -> Oracle-L -> RE: We want to truncate a table in 11.2 which has a Primary Key

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

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Thu, 1 Dec 2011 21:29:07 +0000
Message-ID: <7C4BF3B32B80CC44AE37D31B172415937DD244A803_at_GVW1337EXC.americas.hpqcorp.net> 





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
Received on Thu Dec 01 2011 - 15:29:07 CST












Original text of this message