Deleting table data from tablespace in Oracle
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开发者_Python百科? And why was the space not deallocated?
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.
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.
精彩评论