HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Shake It!
ORACLE STUFFS
17-Mar-2015
Time after time we need to move tables in development databases to "heal" non-standard operation gaps and chains. Following script could serve as a template.

Declare  
  p_table_name  varchar2(30):='.*';--regular expression table mask  
  p_table_owner varchar2(30):=USER;-- tables owner name  
  p_partition_mask varchar2(30):='.*';--regular expression partition mask  
  Procedure ManageIndexes (p_table_name Varchar2,p_partition_name Varchar2, p_Usable boolean) is  
    v_IndexStatusFrom Varchar2(30):=case when p_Usable then 'UNUSABLE' else 'VALID' end;  
    v_PartStatusFrom  Varchar2(30):=case when p_Usable then 'UNUSABLE' else 'USABLE' end;  
  Begin  
    for r2 in (  
      Select ai.OWNER, ai.INDEX_NAME, ai.STATUS, ai.PARTITIONED  
      From ALL_INDEXES ai  
      where TABLE_NAME=p_table_name and table_owner=p_table_owner  
      and status in ('N/A',v_IndexStatusFrom)  
    )LOOP  
      if r2.partitioned='YES' then  
        for r3 in(  
          Select aip.PARTITION_NAME  
          From ALL_IND_PARTITIONS aip   
          Where aip.INDEX_NAME=r2.INDEX_NAME and aip.INDEX_OWNER=r2.OWNER  
          and status=v_PartStatusFrom  
          and aip.PARTITION_NAME=p_partition_name  
        )LOOP  
          BEGIN
          execute immediate 'ALTER INDEX '||r2.owner||'.'||r2.INDEX_NAME  
         ||case when P_USABLE then ' rebuild partition '||r3.partition_name||' PARALLEL'   
               else ' modify partition '||r3.partition_name||' unusable' end;
          DBMS_OUTPUT.put_line( p_table_name||'('||r3.PARTITION_NAME||')');  
      
         EXCEPTION 
          when others then null;
         END;         
        end LOOP;--r3  
      else  
          BEGIN
            execute immediate 'ALTER INDEX '||r2.owner||'.'||r2.INDEX_NAME  
            ||case when P_USABLE then ' rebuild PARALLEL' else ' unusable' end;  
            DBMS_OUTPUT.put_line(p_table_name);  
         EXCEPTION 
            when others then null;
         END;         
      end if;    
    end LOOP;--r2  
  End ManageIndexes;
  Procedure MoveIt(p_table_name Varchar2,p_partition_name Varchar2) is 
  Begin 
    if p_partition_name is null then
       execute immediate 'Alter table '||p_table_owner||'.'||p_table_name||' move';
    else 
       execute immediate 'Alter table '||p_table_owner||'.'||p_table_name||' move partition '||p_partition_name;
    end if;
  --Exception
   -- when others then null;  
  End MoveIt; 
Begin  
  for r1 in ( 
    Select t.table_name, ap.PARTITION_NAME 
    from ALL_TABLES t  
    left join ALL_TAB_PARTITIONS ap on AP.TABLE_NAME=T.TABLE_NAME and AP.TABLE_OWNER=T.OWNER  
    where regexp_like(t.table_name,'^'||p_table_name||'$','i') and table_owner=p_table_owner  
    and regexp_like(partition_name,'^'||p_partition_mask||'$','i')  
  )LOOP 
    MoveIt(r1.table_name,r1.PARTITION_NAME);  
  end LOOP;--r1  
  for r1 in ( 
    Select t.table_name, ap.PARTITION_NAME 
    from ALL_TABLES t  
    left join ALL_TAB_PARTITIONS ap on AP.TABLE_NAME=T.TABLE_NAME and AP.TABLE_OWNER=T.OWNER  
    where regexp_like(t.table_name,'^'||p_table_name||'$','i') and table_owner=p_table_owner  
    and regexp_like(partition_name,'^'||p_partition_mask||'$','i')  
  )LOOP 
    ManageIndexes(r1.table_name,r1.PARTITION_NAME,true);  
  end LOOP;--r1  
End;

[Download]
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky