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

From: Subodh Deshpande <deshpande.subodh_at_gmail.com>
Date: Fri, 2 Dec 2011 12:48:33 +0530
Message-ID: <CAJsOtB4wvEU6iWtoDuey2E1GX-DqGud6bs_1mErncgjp=tXoVw_at_mail.gmail.com>



Hello,
I think cascade and storage is not permissible as per doc..

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm

for drop storage it should not give any problem I suppose..

SQL> truncate table emp drop storage cascade constraint; truncate table emp drop storage cascade constraint

                                *

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

SQL> truncate table emp drop storage ;

Table truncated.

thanks...subodh
On 2 December 2011 01:23, Wolfson Larry - lwolfs < lawrence.wolfson_at_acxiom.com> wrote:

> 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
>
>
>

-- 
=============================================
TRUTH WINS AT LAST, DO NOT FORGET TO SMILE TODAY
=============================================


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Dec 02 2011 - 01:18:33 CST

Original text of this message