HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Drop user did not reduce size of tablespace file

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
filetablespacedidusersizedropreducenot

Problem

I have a development environment in which I frequently copy a certain schema.

When I'm done with the copy, I will use OracleEM to drop that user/schema.

My problem is that this does not seem to reclaim the space that the schema was occupying from my tablespace file.

I read doing an DROP SCHEMA .... CASCADE would do this, but the users are already deleted.

Now my entire drive is filled up.

How can I reclaim this space now?

Solution

I don't think there's much value in me copying what others have done so well in explaining in the past, so I'll just point you at a brilliant blog post: Reclaiming Unused Space in Datafiles.

Essentially you just need to do:

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 111M;

Code Snippets

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 111M;

Context

StackExchange Database Administrators Q#22694, answer score: 4

Revisions (0)

No revisions yet.