Home » RDBMS Server » Server Administration » High water mark (10.2.0.4)
High water mark [message #530959] Fri, 11 November 2011 09:04 Go to next message
varunvir
Messages: 330
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 #530961 is a reply to message #530959] Fri, 11 November 2011 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 23155
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: High water mark [message #530963 is a reply to message #530959] Fri, 11 November 2011 09:45 Go to previous messageGo to next message
John Watson
Messages: 4862
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;
any MANUAL tablespaces may have problems with re-using space efficiently.


Re: High water mark [message #530966 is a reply to message #530963] Fri, 11 November 2011 09:56 Go to previous messageGo to next message
varunvir
Messages: 330
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 #530968 is a reply to message #530963] Fri, 11 November 2011 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
..In addition Oracle NEVER brings down the high water mark unless you explicitly tell it to do so with a ALTER TABLE or TRUNCATE statement.

Regards
Michel
Re: High water mark [message #530970 is a reply to message #530968] Fri, 11 November 2011 10:12 Go to previous messageGo to next message
varunvir
Messages: 330
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 #530977 is a reply to message #530970] Fri, 11 November 2011 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it will increase performances of full table scan but decrease those of inserts (or some updates that increse the size of the rows).

Regards
Michel
Re: High water mark [message #559208 is a reply to message #530977] Fri, 29 June 2012 15:01 Go to previous message
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.
Previous Topic: listener log
Next Topic: compression
Goto Forum:
  


Current Time: Sat Dec 20 16:19:04 CST 2014

Total time taken to generate the page: 0.06040 seconds