My Female Doctor Stares At My Penis

row enable Oracle Movement



The "enable row movement" feature of Oracle is somewhat confusing to beginners and this notes describes some times when "enable row movement" is used. For complete details, see my book "
Oracle Tuning: The Definitive Reference
"




Oracle has several commands to reclaim unused disk space for objects (tables and indexes). Using the "
alter table xxx shrink space compact"
command also has the benefit of making full-table scans run faster, as less block accesses are required. With standard Oracle tables, you can reclaim space with the "alter table shrink space" command:








SQL> alter table mytable enable row movement;
Table altered

SQL> alter table mytable shrink space;
Table altered













































Finding tables and indexes for shrinking




The Oracle 10g segment advisor will recommend tables that will benefit from shrinking and indexes that require rebuilding (to reclaim space).

When you add the clause "enable row movement" to a create table statement, you are giving Oracle permission to change the ROWID's. This allows Oracle to condense table rows and make it easier to reorganize tables. The enable row movement clause in used within these features:

    
 Alter table xxx shrink space compact 
  - When using Automatic Segment   Storage Management (ASSM, a.k.a. bitmap freelists) you can issue the " 
 alter   table xxx shrink 
 " command. to compress the table rows into less data   blocks, and Oracle moves down the high water mark  to release the space.    This makes full-table scans run faster. 
  
  
    
    

Flashback table - Using the flashback table features requires "enable row movement".

    
    
 Table reorganization 
  - An Oracle10g database can reclaim space within   data segments online without affecting the ability of end users to access   their data. The only thing that must be ensured before using online segment   reorganization capability is that the tablespaces have the Automatic Segment   Space Management (ASSM) and row movement features enabled. Oracle10g   introduces the ability to reclaim space from a segment by shrinking of the   segment. Shrinking a segment will make unused space  available to other   segments in the tablespace and may improve the performance of queries and   DML operations.     
  
    
   
  
  
  
    

The segment shrink is an online operation where the table being shrunk is open to queries and DML while the segment is being shrunk. Additionally, segment shrink is performed in-place. This is a key advantage over performing Online Table Redefinition for compaction and reclaiming space.

             
  
   
  
   
  
   
   
      
 Finding opportunities for enable      row movement 
  
  
   
   
       
  
   
   
       
 With the introduction of the      
 alter table xxx shrink space      compact 
  syntax, the DBA gets a powerful tool for      effective and easy database space management. However, the DBA      needs to know what data segments experience high space waste in      order to reclaim free space to the database and shrink segments.      
  
       
      This page 
  shows scripts that can be run to identify      opportunities for using the "alter table shrink compact" command      and you can use the  

awr_list_seg_block_space.sql

script to report percentages of free space for data segments.

  
       
  
  
   
   
       
  
    
   
      
 Caveat:  Using enable row      movement can corrupt any Oracle features that rely on ROWID,      such as nested tables, and they should be used with caution. 
  
  
  
  

PD:

article original : http://www.dba-oracle.com/t_enable_row_movement.htm