Re: sppurge (Statspack Purge) does not seem to free up space

so you're looking to reduce the high water mark of your tablespaces' datafiles?
the following would do that:

SQL> drop tablespace :tablespace_name including contents including datafiles;

one cannot simply reduce the high water mark of a datafile. you can alter the tables and move them into a new tablespace, followed by rebuilding the indexes.

even running spdrop.sql won't reset the datafile HWM.

If what you are truly attempting to do - is to reclaim the space, do the following:

check to see what schemas have segments in the tablespace that you are going to drop.
export the data in that tablespace
@?/rdbms/admin/spdrop.sql;

drop tablespace <tablespace_name> including contents including datafiles;

create a new tablespace
@?/rdbms/admin/spcreate

re-schedule your dbms_jobs

backup the tablespace.
backup the controlfile.

P.S. I believe that there is something in 10g that can reduce a datafile HWM, but I haven't tested it yet.

-bdbafh Received on Fri Feb 11 2005 - 12:56:31 CST