If some data has been put in ever since last delete then the truncate wouldn't be an option at this time. Rebuilding table online with no significant data will also bring the hwm down. You can use the following statement and vary the storage clause also if desired:
ALTER TABLE <tablename> MOVE Tablespace <New or same tablespacename> PctUsed 80 PctFree 10 IniTrans 1 MaxTrans 255 Logging Parallel 2 Storage (Initial 16K next 16 K PctIncrease 0 MinExtents 1 MaxExtents UNLIMITED Freelists 1 Freelist Groups 1 Buffer_Pool Default);
After move it is good idea to rebuild the indexes online also if your tables has some data in there.

Gurmohan
www.onlymath.com - Place to learn and teach math.



-----Original Message-----
From: rbvogel
[mailto:oracledba-ezmlmshield-x6886649.[Email address protected]
Sent: Wednesday, November 02, 2005 11:29 AM
To: LazyDBA Discussion
Subject: Re: select count(*) on a empty table


OH.... forgot another way to reset HWM... "truncate table ....." even easier than "alter table move..."

rbv


> We have a table that had some 20,000+ rows. All rows in this table were
> deleted using sqlplus. Now the response time for a select count(*) on this
> empty table is very slow. The table has 5 user indexes and 3 system
> generated indexes.
>
> Assumed that Oracle was holding onto to some old index information and so
> the statistics for the table and its indexes were re-computed using OEM
> (enterprise manager). That did not help. So I deleted the statistics and
> re-computed new statistics for this object and that did not help either.
>
> So had to take a drastic action of dropping the table and it indexes and
> re-adding them. That took care of the response time problem but is there a
> less drastic workaround to my problem. What am I missing here ? What sort
> of cleanup is required on a table after all it's rows have been removed ?
>
> The database is Oracle 10g; the SQL tools used were sqlplus & isql (via
> ODBC)
>
> TIA
>
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these terms:http://www.lazydba.com/legal.html
>


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html

Oracle LazyDBA home page