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

Parris Geiser (parris_at_walleye.esp.bellcore.com) wrote:
: I created a tablespace for a user ... 10M.
: The user just wanted to test some scripts that created and then dropped
: tables. The problem is, that after a while, the tablespace gets full.
: The table create script does allocate min extents of 1. The user never
: puts any data into the tables. Just creates and then drops.
: Why do the extents stay around?

Are you absolutely sure that the tables were dropped with a "DROP TABLE..." statement? If you query DBA_EXTENTS for this tablespace, are the extents really this user's tables? All extents for a table (and it's indexes) should go away when the table is dropped.

: Even after I dropped the user the extents didn't go away???

How do you know they didn't go away? If you query DBA_FREE_SPACE is there not 10M free in this tablespace?

Did you "drop" the user with "DROP USER joe CASCADE" or with "REVOKE CONNECT FROM joe"? The former should revoke access from the user and drop all of his objects. The latter just revokes access (i.e. the users objects stay around).

: It would seem that this should be a trivial thing but am having no
: luck trying to resolve.
 

: I suppose that I could drop the tablespace and re-create but that is a pain
: since I may not be around when this user needs to do this, etc.
 

: What am I missing??
 

: Thanks for suggestions,
: parris

Hope some of this was helpful.

• Dan