| High water mark [message #530959] |
Fri, 11 November 2011 09:04  |
varunvir
Messages: 389
Registered: November 2007
|
Senior Member |
|
|
Hi Experts,
I have deleted lot of records in a table.Would oracle be able
to insert in the empty blocks generated from deletion of records
without bringing the high water mark down.
Thanks,
Varun
|
|
|
|
|
|
| Re: High water mark [message #530963 is a reply to message #530959] |
Fri, 11 November 2011 09:45  |
John Watson
Messages: 8928
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Yes, though you need to be using Automatic Segment Space Management for this to be really effective. Do you know how to check that?select tablespace_name,segment_space_management from dba_tablespaces;
|
|
|
|
| Re: High water mark [message #530966 is a reply to message #530963] |
Fri, 11 November 2011 09:56 |
varunvir
Messages: 389
Registered: November 2007
|
Senior Member |
|
|
Thanks John for clearing my doubt.
Why would we need to reclaim space manually with move or shrink
if oracle can reuse space efficiently in assm?
Thanks,
Varun
|
|
|
|
|
|
| Re: High water mark [message #530970 is a reply to message #530968] |
Fri, 11 November 2011 10:12 |
varunvir
Messages: 389
Registered: November 2007
|
Senior Member |
|
|
Thanks Michel but why would we want to bring high water mark down when oracle is able to resuse the space in ASSM.Is it just for the performance of full table scan?
Thanks,
Varun
|
|
|
|
|
|
| Re: High water mark [message #559208 is a reply to message #530977] |
Fri, 29 June 2012 15:01 |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
When you alter a table or truncate it to free up space, it releases the size of the table for other tables and indexes to use within the tablespace. Here is an example.
ECSCDAP1P > select bytes/1024/1024 megabytes from dba_segments where segment_name='ALAN';
MEGABYTES
----------
26
ECSCDAP1P > select count(*) from alan;
COUNT(*)
----------
2097152
ECSCDAP1P > delete from alan where rownum < 2097151;
2097150 rows deleted.
ECSCDAP1P > commit;
Commit complete.
ECSCDAP1P > select bytes/1024/1024 megabytes from dba_segments where segment_name='ALAN';
MEGABYTES
----------
26
ECSCDAP1P > alter table ECSCDAP1P.alan enable row movement;
Table altered.
ECSCDAP1P > alter table ECSCDAP1P.alan shrink space;
Table altered.
ECSCDAP1P > alter table ECSCDAP1P.alan disable row movement;
Table altered.
ECSCDAP1P > select bytes/1024/1024 megabytes from dba_segments where segment_name='ALAN';
MEGABYTES
----------
.0625
"truncate table ECSCDAP1P.alan;" removes all rows from the table and its indexes and removes all extra extents from the table and all its indexes. Truncate table also has the added benefit of not generating a lot of redo logs but will eliminate all rows completely and no rolling back.
"alter table ECSCDAP1P.alan move online;" moves and shrinks the table but invalidates its indexes so the application goes down (if indexes exist on the table).
"alter table ECSCDAP1P.alan shrink space;" moves and shrinks the table and does not leave any indexes unusable so the application never has an outage. Indexes can then be rebuilt online if you want to reclaim the space from the indexes.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
