Re: Problem with Truncate Table

From: BGT <bgt0990_at_optonline.net>
Date: 1 Mar 2007 03:53:06 -0800
Message-ID: <1172749985.987274.11630@t69g2000cwt.googlegroups.com>

On Feb 28, 3:03 pm, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> joel garry schreef:
>
>
>
> > On Feb 28, 7:23 am, "BGT" <bgt0..._at_optonline.net> wrote:
> >> I am no longer able to truncate tables in this 9.2.0.6.0 database.
> >> When I do a "truncate table "tablename" drop storage" I get ORA-00904
> >> "DROP SEGMENTS" INVALID IDENTIFIER.
>
> >> I've tried this on multiple tables as the schema owner and a SYSDBA
> >> user with the same results. Any idea what is happening?
>
> >> Thanks
> >> Barry
>
> > Well, there was a bug in an earlier version, maybe somewhere along the
> > line you ran the wrong catalog.sql or got an old sql.bsq.
>
> > as sys,
> > SQL> desc mon_mods$
> > Name Null? Type
> > ----------------------------------------- --------
> > ----------------------------
> > OBJ# NUMBER
> > INSERTS NUMBER
> > UPDATES NUMBER
> > DELETES NUMBER
> > TIMESTAMP DATE
> > FLAGS NUMBER
> > DROP_SEGMENTS NUMBER
>
> > If you don't have a field named drop_segments, you have this problem
> > (which could potentially be a quite serious patching goof). Check
> > sql.bsq to be sure it has a create statement that matches this table.
> > Then try running catalog.sql.
>
> > jg
> > --
> > @home.com is bogus.
> > Are they still using Sybase? http://www.kstp.com/article/stories/S33513.shtml?cat=1
>
> Sounds like a default installed database with patched software?
> One time, a version downloadable was patched (9.2.0.4/Linux, iirc), but
> the sample databases were *not*.
>
> Is this the case here?
>
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...- Hide quoted text -
>
> - Show quoted text -

I have since determined that this only applies to approx 4 tables out of 150. I can truncate perfectly on other tables but not on these "disorder" tables. See SQLPlus session below

SQL> truncate table azcms_tst.disorder_avg_master drop storage; truncate table azcms_tst.disorder_avg_master drop storage

                         *

ERROR at line 1:
ORA-00904: "DROP_SEGMENTS": invalid identifier

I can't figure out what could make this happen on just these few tables. We have deleted * from the tables and reloaded them but I still cannot do a truncate.

This database was a 9.2.0.2.0 patched to 9.2.0.6.0

Thanks
Barry Received on Thu Mar 01 2007 - 05:53:06 CST