| Table Size Remains the Same? [message #24217] |
Mon, 03 February 2003 09:48  |
Suchit
Messages: 6
Registered: February 2003
|
Junior Member |
|
|
hi all,
I had a huge table having close to 1 crore records. Recently i deleted records for previous years as a result of which there are onyl 3 lakh records in it.
However i notice that the table size has not gone down a bit. the tablespace also doesn't seem to be free either. the new table (and the new tablespace) where i transfered the data show data of the size i transfered?
what's the issue here? is it that once the table has been extended it doesn't shrink back automatically? how do i confirm that and how do i free up that space?
eagerly waiting for replies!
Suchit
|
|
|
|
|
|
| Re: Table Size Remains the Same? [message #24229 is a reply to message #24217] |
Mon, 03 February 2003 14:35  |
Suchit
Messages: 6
Registered: February 2003
|
Junior Member |
|
|
Hi Mahesh,
Thanks very much. Would you agree with me that tablespace coalesce would be the best option for my case?
and the dbms_space has 2 procedures both of them returning information. u just meant i can get the HWM info using that package or did u mean i could do something more?
Thanks a lot again.
Suchit
|
|
|
|
|
|
| Re: Mahesh - What do you mean? [message #24233 is a reply to message #24229] |
Mon, 03 February 2003 18:02 |
sri
Messages: 154
Registered: February 2000
|
Senior Member |
|
|
Mahesh,
I am not sure what you intended to say.
But why would we worry about creating the constraints/objects like triggers on the temp table?
Please note that we are just creating the temp table to temporarily hold the data, we will be dropping the table any way. We are not disturbing the constraints or objects on the ORIG table, TRUNCATE does not touch any constraints, it just flushes the data and resets the HIGH WATER MARK, which is the whole point here.
This method works really neat, we do not have to worry about the dependencies AT ALL.
Thx,
SriDHAR
|
|
|
|
| Re: Table Size Remains the Same? [message #24240 is a reply to message #24229] |
Tue, 04 February 2003 03:40 |
Suchit
Messages: 6
Registered: February 2003
|
Junior Member |
|
|
Hi Sri/Mahesh
I think Mahesh was having drop in mind at that time. Anyways the solution is great for me. I am really thankful to you 2 guys.
Just for my knowledge, why is it that you guys are not suggesting a tablespace coalesce? its one line command and looks the easiest. Are there some harmful side effects of it?
Suchit
|
|
|
|
| Re: Table Size Remains the Same? [message #24246 is a reply to message #24229] |
Tue, 04 February 2003 07:42 |
sri
Messages: 154
Registered: February 2000
|
Senior Member |
|
|
I think tablespace coalesce command coalesces the free extents into one new larger extent of the size required by the tablespace/table as may be the case. It does not reset the HWM of the table itself. It is more of a command than a tool for a DBA to manage the FREE extents in the database. SMON will normally perform this coalescing in the background. At situations, DBA can choose to manually opt to coalaesce. But this does not have to do anything with your issue of HWM.
Thx,
SriDHAR
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
