-
Didn't you truncate a 4.7 Gig table?
In the affirmative case... did you use "drop storage" option on truncate statement?
Last edited by PAVB; 05-25-2007 at 01:12 PM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
No., I gave only
Truncate table tablename;
not used (drop storage) clause.
So what shall i do now to use that space;
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
Look at dba_extents howmany extents the truncated table occupies and howmuch space
-
it nearly occupies 87 extents and only one extent has 7MB
and all the other has 1MB and some 0.628MB
-
That means the table occupies only around 100MB.
-
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.
-
"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
-
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..
Last edited by ams-jamali; 05-30-2007 at 05:57 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|