views:

37

answers:

2

I tried to delete the data from tablespace using the command

DROP USER xyz CASCADE;

I found that user was dropped but size of my tablespace did not decreased. Is my data fom the tablespace have been deleted? If yes, how can I confirm that? And why was the space not deallocated?

+2  A: 

Oracle does not automatically shrink datafiles, which is what a tablespace is made of. Oracle simply marked the space which had been used by user XYZ's segments (tables, indexes, and the like) as free for some other user's segments to use.

SELECT * FROM DBA_OBJECTS WHERE OWNER = 'XYZ'; should demonstrate that user XYZ no longer owns any physical (tables, indexes, clusters) or logical (sequences, procedures, packages, triggers, types) objects in the database.

Adam Musch
THANX Adam It was helpful...
Ranjeet Kumar
+2  A: 

You can shrink datafile, under certain circumstances, with following command:

ALTER DATABASE DATAFILE 'filename2' RESIZE new_size;

Datafile size can be reduced if there is no data at the end of file. See "Managing Tablespaces" for detailed information.

zendar