Re: Tablespace is full ... now what??

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 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.
(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.  

Good Luck... Sandra Fergusson Received on Sun Jun 11 1995 - 00:00:00 CEST