Re: Trancate Table

peri <skanda_at_dircon.co.uk> wrote in message news:<2350529.1041807971_at_dbforums.com>...
> I want to truncate the table log3 which contains milions of records so I
> did the following;
> >trucate table log3;
> the error message was>ORA-02266 unique/primary keys in table referd by
> enabled forign keys.
> then I issued;
> >alter table log3 disable primary key cascade;
> table alterd
>
> now I was able to truncate the table.
>
> Now when I want to enable the primary keys I had error masage for the
> command I issued;
> >alter table log3 enable primary key;
> The error was, ora-01630:max # extents (135) reached in temp segment in
> table TEMP,
> Then I increased the MAX parameters to maxextent 5000 and isue the
> >alter table log3 enable primary key;
> and got the following error ora-01652:unable to extend temp segmant by
> 128 table space TEMP.
>
> I am only tryng to enable the primery keys which I disabled it to do the
> trucation, Please help
>
> Kumar

Kumar, when you disabled the PK Oracle would have dropped the unique index used to support the PK constraint and when you attemped to enable the constraint Oracle tried to recreate an index to support the constraint. Unfortunately your TEMP (sort segment) tablespace ran out of extents. Increase the size of the extents in your TEMP tablespace and make sure it is large enough to support the index rebuild operation.

I believe it would be better to explicitly create the index on the PK columns and then add the constraint, which will use the existing index.

You may want to see the cooperative FAQ article: What is the difference between a unique index and a unique constraint? at url - http://www.jlcomp.demon.co.uk/faq/uk_idx_con.html

HTH -- Mark D Powell -- Received on Mon Jan 06 2003 - 15:19:59 CET