| reducing tablespace size ??? [message #540588] |
Tue, 24 January 2012 12:32  |
janakors
Messages: 232
Registered: September 2009
|
Senior Member |
|
|
hi,
we have a tablespace of size 900 GB where 90% of space is occupied by two tables having BLOB data and now i need to drop these two tables and then to recover the space, i need to resize the tablespace (datafiles).
kindly suggest some readings and tips for the said activity as it is to be done staright away on production db.
Regards
janakors
|
|
|
|
|
|
| Re: reducing tablespace size ??? [message #540635 is a reply to message #540590] |
Wed, 25 January 2012 00:23  |
abhi_sri
Messages: 20
Registered: September 2010
Location: India
|
Junior Member |
|
|
|
For deletion.. use truncate or drop command. If you do delete table, the free blocks will go to freelist of tablespace for further use. But as these are still with tablespace, not count as free space in disk. While shrinking the datafile you can shrink it upto the last free extent. You can find the free space in a file using dba_extents, dba_free_space & dba_data_files. Use ALTER DATABASE DATAFILE '-------.dbf' RESIZE ---M;
|
|
|
|
| Re: reducing tablespace size ??? [message #540637 is a reply to message #540635] |
Wed, 25 January 2012 01:13 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:the free blocks will go to freelist of tablespace
No, free list of table (at large, in ASSM there is no free list).
Quote:But as these are still with tablespace, not count as free space in disk
There are still in table and not count as free space in tablepace (and then in disk).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
