Rod Fergusson (rod.fergusson_at_canada.attgis.com) wrote:
> Hi Parris!
>
> If you are using Oracle V7.x there are 2 alternatives to
> cleaning up allocated space from a table. If the user is just
> continutally created and dropping tables...you want them to
> first use the TRUNCATE TABLE command with the DROP STORAGE
###
### We can not use truncate because there are constraints on the tables.
### Pg. 4-397 of the SQL Language Reference manual:
### "You cannot truncate the parent table of an enabled referential integrity
### constraint." The user would need first get rid of the contraints and then
### drop the table and then ...
### Remember, IMHO, ORACLE should be smart enough to realize that it no longer
### needs the space and free it.
###
> qualifier. The qualifier will deallocate ALL space that was
> given to the rows in the table...THEN you can use a DROP table
> command, which will then remove the table. Keep in mind that
> oracle likes to allocate blocks in contiguous space...repeated
> drops and recreates only fragment the tablespace...and
> unfortunately, the only way to de-frag is to drop the
> tablespace and re-create it. It's really no big deal..you
> don't have to take the database down or anything...just bring
> that tablespace OFFLINE using the ALTER TABLESPACE command
> (don't forget to ALSO take offline any related INDEX
> tablespaces)...then drop the tablespace and recreate it.
###
### I'm sort of surprised that you don't think that this is not a big deal.
### I can't just bring tablespaces offline and re-create
### while users are running.
### In my shop the users could be accessing the database at all hours.
### I would need to schedule this activity ... but how often?
### This would mean that I would be constantly monitoring the tablespaces
### and at any moment try to schedule the re-creation.
### I know that my original statement did not mention the environment but
### as you can see, the problem is not trivial.
###
> (ALSO..don't forget to delete the old .DBS datafile from disk
> after your DROP it...oracle does not automatically remove files
> at the operating system level). Give this a try..and let me
> know how it works out. You can also take a look at the STORAGE
> clauses for both the tablespace and the tables that the person
> is creating. A MINEXTENT of 1 is of no value if the INITIAL is
> too small....it only compounds your fragmentation problem.
> Increase the INITIAL to the estimated total size for the
> initial table load.
