Datafile resize

[PAVB]

Didn't you truncate a 4.7 Gig table?

In the affirmative case... did you use "drop storage" option on truncate statement?

[ams-jamali]

No., I gave only
Truncate table tablename;

not used (drop storage) clause.

So what shall i do now to use that space;

[PAVB]

drop storage option is default option for truncate...

did you aborted truncate after a while 'cause of truncate was not finishing?

Try deallocate unused space on target table...

alter table schema_name.table_name deallocate

You might want to check complete syntax.

[ams-jamali]

No, i had not aborted in the middle.
It just completes its job within few seconds.

I think there is no need of using deallocate option after Truncate.

But you guys know better than me..,

So.., what else i can do further.

[Bore]

Look at dba_extents howmany extents the truncated table occupies and howmuch space

[ams-jamali]

it nearly occupies 87 extents and only one extent has 7MB
and all the other has 1MB and some 0.628MB

[Bore]

That means the table occupies only around 100MB.

[ams-jamali]

no bore..,

before truncating that table i checked the size using segment then i checked that result in toad too.
Same 4.7GB it shows,So i confirmed and then proceed to trucate.
now that table occupies
1886051 (present rec-count)rec within two weeks.
may be b'cos of this it might have shown this result.

[Bore]

"I found free space using dba_data_files & dba_extents it shows only 154MB.
So instead of resizing i left that..,"

Maybe u want to look at DBA_FREE_SPACE

[ams-jamali]

bore..,
by using dba_data_files & dba_extents we can find how much one can resize the datafile(by giving block size).


So for resizing i joined these two tables and found the size(that can be resized).

dba_free_space gives you overall Freespace in that tablespace.
but from that one cannot resize the datafile because it may contain data.
Bore you know better than me.

if u need i will post that query how to find space in datafile that can be resized.without finding that how much can be resized in each datafile.If any tries to resize it shows error stating that

file contains used data beyond requested resize value..