Re: Problem with Truncate Table

On Mar 1, 10:07 am, "BGT" <bgt0..._at_optonline.net> wrote:
> On Feb 28, 2:39 pm, "joel garry" <joel-ga..._at_home.com> wrote:
>
>
>
>
>
> > 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
>
> I tred to send this before but Google ate my msg.

All purpose excuse of the century :-)

>
> Joel is correct I don't have a DROP_SEGMENTS entry in that table. I
> checked the sql.bsq script and the correct entry is there for this
> table, but running Catalog.slq did not bring it back. I manually
> entered the column into the mon_mods$ table and I am running catalog
> again to see if it wacts any different.
>
> Thanks
> Barry

Frank explained why, but I would add that one generally wants to use DBCA to create a script to create databases, rather than simply create the db. It's been a while since I've actually done that (as I subsequently use the scripts!) but I have a vague memory you have to select the correct options in order to be asked to save the script. Googling about should find the instructions if you need to figure it out (and there's probably a metalink note).

Sorry about the catalog.sql brain fart, sql.bsq is executed for the create database statement. Modifications of mon_mods$ are in other files in the same directory, like i0900010.sql. If you look at the catalog.sql, you'll see it creates views from things like mon_mods$, including that drop_segments column, so you have to run catalog.sql after the table mod to make the views right (as you seem to have figured out).

jg

--
@home.com is bogus.
http://www.uebersetzung.at/twister/vi.htm