Oracle truncate with / without drop storage

Question

Tried to get a bit more info on this, but figured I'd just ask.... I attempted to perform a table truncate on Oracle 10g with the drop storage, however the results aren't entirely what I was expecting.

Basically, the table in question had over 30 million records and over 3Gb allocated for it, but somehow the space allocated for it still appears to be used. Is there a way to actually drop the allocated space used, or am I going about it the wrong way?

===EDIT====

My bad...I was referring to physical disk space, in terms of the datafile size... At the moment our export / backup amounted to about 13GB, so the intention here was to shrink the actual file in the process of truncating the data when we export to a file for backup.

Answer 1

What kind of extent management and segment_space_management are you using?

select tablespace_name from user_segments
  where segment_name = 'T'   ---T here would b your table name

TABLESPACE_NAME
------------------------------
TS_USERS_01

sql > select TABLESPACE_NAME, EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT
  2     from dba_tablespaces where tablespace_name = 'TS_USERS_01';

TABLESPACE_NAME                EXTENT_MAN SEGMEN
------------------------------ ---------- ------
TS_USERS_01                    LOCAL      AUTO

Are you using dictionary managed tablespaces or locally managed tablespaces with system allocated segments ?

Answer 2

You don't exactly say what you were expecting to happen when you truncated the table. Remember that if you de-allocate the storage from a table/index then it's just returned to the tablespace as free-space. The space used on disk from the operating system's perspective isn't going to get any smaller. So whilst a tablespace can be set to "auto extend" when more space is required, it does not auto-shrink when space is freed.