| Soumya |
Posted: Mar 13, 2008 04:13:55 PM |
Total Post: 57
Joined: Mar, 2006
|
Hi,
My tablespace had reached 19 gb. It hat 1 table which was 10 gb.
I have deleted from that tabel , now its 2 gb .
the datafile for that tablespace is not getting reduced , when trying to
do that , as the datafile's HWM is high than the actual used data.
Can you plz tell me the steps to reclaim the used space on that TS ?
Thanx
|
|
|
 shantanu |
| Posted: Mar 13, 2008 07:39:13 PM |  |
|
Total Post: 48
Joined: May, 2007
|
use coalesce option.
SQL> ALTER TABLESPACE TABLESPACENAME COALESCE;
|
|
|
|
|
| vinnu |
| Posted: Mar 14, 2008 06:25:06 AM | |
|
Total Post: 6
Joined: Mar, 2008
|
If coalscing does not work, pls re org the table.This will surely remove the fragmention part from the table.
|
|
|
|
|
| vinnu |
| Posted: Mar 14, 2008 06:30:01 AM | |
|
Total Post: 6
Joined: Mar, 2008
|
One simple soluton would be,
create a new TS.
Move the table which is now only 2 GB using -->alter table <Table name> move tablespace;
coallsce the new TS.
Then move the table back to the ols TS
Drop the new TS which was created.
Make sure you rebuild the indexes on that table which got re orged
|
|
|
|
|
| pank |
| Posted: Mar 14, 2008 09:02:51 PM | |
|
Total Post: 1
Joined: Mar, 2008
|
create a new table in another tablespace 'create table new_table_name as select * from old_table_name' then truncate old table after that use 'insert into old_table_name select * from new_table_name'
|
|
|
|
|
| Gitesh |
| Posted: Mar 14, 2008 11:00:49 PM | |
|
Total Post: 322
Joined: May, 2005
|
Hi,
Best solution is that execute following commands.
1) Move table in to same tablespace using move command
SQL>alter table <table_name> move;
Above command will move table in to same tablespace.
2)Rebuild indexes
SQL>alter index <index_name> rebuild;
3)Now issue coalesce command
SQL>alter tablespace <tablespace_name> coalesce;
4) Resize the respective datafiles and reduce size from original.
SQL>alter database datafile '<file_name>' resize 100M
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
