Tablespace coalesce no effact

[Kumud]

Our tablespace has lots of delete and insert and updates,
When I am trying tablespace coalesce it does not create any space in datafiles, it shows tablespace altered but there is no effact on space.
I deleted some users, many tables still data files shows full.
removeing object from tablespace does not create any space.
my disk is full please help

[jmodic]

http://otn.oracle.com/docs/products/...a76965/toc.htm

[tamilselvan]

The reason you were not gaining disk space is your tablespace (datafiles) has more bubble and honeycomb.
Do not create objects with different initial and next sizes in one tablespace.

Use "ALTER TABLE tablename MOVE TABLESPACE tablespacename STORAGE ..." to gain spaces within the tablespace.
Also use "ALTER INDEX indexname REBUILD" when there is low level database activity.

[Kumud]

Let me be more specific. My tablespace is full.
I deleted many users schema which contains lots of data.
now since I deleted many objects from tablespaces it should free some space in tablespace. How can I create free space ?
where that space has gone ? (of deleted objects)

I am just confused becuase by coalesce it does not create free space.

[Shestakov]

Coalesce tablespace can't create no free 1 bytes in tablespaces.
It only union neast free extents into 1 lager extent, and that's it.
U should check dba_free_extents : does ur tablespace has fee space or not?
and only then, if u have free extents, u can coalesce this tablespace.

[danisha]

I think u need to resize your datafile. Here is the syntax

ALTER DATABASE
DATAFILE <'datafile'>
RESIZE

There are some method we can find out the highest water mark in the datafile but here u can use guess and trial method by taking a small size and increasing and little bit if it fails.

Best of luck

[aarroyob]

Originally posted by Shestakov
Coalesce tablespace can't create no free 1 bytes in tablespaces.
It only union neast free extents into 1 lager extent, and that's it.
U should check dba_free_extents : does ur tablespace has fee space or not?
and only then, if u have free extents, u can coalesce this tablespace.

I think the view you point to is DBA_FREE_SPACE

Cheers

Angel