CREATE OR REPLACE package pkg_obj_rbld
authid current_user


as

 gv_ddl_source_table varchar2(64);
 
 
 --
 -- force_ddl ignoriert globale indexe in partitionierten tabellen
 -- ein 'exchange partition' invalidiert den gesamten index, eine partitionsweise
 -- redefinition ist da nicht moeglich
 --
  gv_ddl_force boolean default false;
  
 --
 --  dies schaltet die angabe des tablespaces im erstellen des intermediary tables ab
 --  die neue tabelle / indexe werden also im default tablespace des
 --   users erstellt
 --      true: es wird der tablespace der original tabelle genutzt
 --      false: es wird der default tablespace des users verwendet
 --
  gv_use_orig_tbsp boolean default true;
  
  --
  -- der in der redef angelegte intermed table
  -- wird normaler weise nach erfolgreichem abschluß der redefinition
  -- gelöscht.
  --
  -- wenn man mit synonymen auf tabellen zugreift kann es aber zum fehler 'syn. translation no longer valid'
  -- kommen, solange transaktionen noch aktiv sind. Zur Vermeidung des Fehlers kann der intermed 
  -- table behalten werden und zu einem späteren zeitpunnkt gelöscht werden
  --
  --
  --
  gv_keep_intermed boolean default false;
  
  --
  -- bei grossen partitionierten tabellen wird redef pro partition ausgefuehrt
  -- wir koennen aber auch eine partitionierte tabelle als ganzes redefinieren
  --
  gv_ignore_parts  boolean default false;
  
  
  
  --
  --  diese option redefiniert eine partitonierte Tabelle 
  --  zu einer nicht partitionierten
  --
  --
  gv_remove_part boolean default false;

 procedure set_ddl_source_table (v_name in all_tables.table_name%type);
 
 procedure set_force_ddl (b_force_ddl in boolean default false);
 
 procedure set_keep_intermed  (b_keep_intermed in boolean default false);
 
  procedure set_ignore_parts  (b_ignore_parts in boolean default false);
 
  procedure use_orig_tbsp (b_use_orig_tbsp in boolean default true);
  
  procedure set_remove_part (b_remove_part in boolean default false);


 procedure run_tbl_rbld (v_owner in varchar2, v_tname in varchar2 , v_partition_name in varchar2 default null);
 
 procedure run_ind_rbld (v_owner in varchar2, v_tname in varchar2 ,v_partition_name in varchar2 default null);

 
--
--      exchange_part:   Tauscht die temp erstellte tabelle mit der ddl_source_table aus.   
--                      * true ruft die procedure exchange_partitions auf 
--                      * false (default)  fuehrt kein exchange partition durch
--

 
 procedure init_jobs_iot(v_owner varchar2, v_tname in varchar2,v_date_start in date , v_date_end in date, exchange_part in boolean default false);
 
 
  procedure cr_interm_tab(v_interm_name out varchar2,full_create in boolean default true);

    function show_version return varchar2;
    
    
/*
Sample partition wise iot rebuild into new tablespace (manuelly create OTB_TIC_RBLD first!)
exec pkg_common_logging.set_log_level(3);
exec PKG_OBJ_RBLD.set_force_ddl(true);
exec PKG_OBJ_RBLD.use_orig_tbsp(false);
exec PKG_OBJ_RBLD.set_keep_intermed(true);
exec PKG_OBJ_RBLD.set_ddl_source_table('DEPLOY.OTB_TIC_RBLD');
exec PKG_OBJ_RBLD.RUN_TBL_RBLD('DEPLOY','OTB_TIC_DATA');



*/    
    
end;
/

CREATE OR REPLACE package body

                                                                             DEPLOY.pkg_obj_rbld

as

    type  tbsp_type is table of ALL_TAB_PARTITIONS.TABLESPACE_NAME%type;
    my_tbsp tbsp_type  := tbsp_type('USERS','VRS_DATA','VRS_IDX');
    is_iot boolean := false;
     v_stop_ident varchar2(32) := 'must_stop';

v_SESSION_MODULE varchar2(64) := 'pkg_obj_rbld.run_script';

parallel_degree constant number  := 3;
v_job BINARY_INTEGER;

b_partitioned boolean default false;


redef_option_flag  PLS_INTEGER;

TYPE t_tab_ident IS RECORD
    (
        owner          all_tables.owner%TYPE,
        table_name     all_tables.table_name%TYPE
    );

    my_tab_ident                     t_tab_ident;

    c_version constant varchar2(32) := '01.20 / 20121228';
     
    
    
function show_version return varchar2
is

begin

    return 'pkg_obj_rbl version is: '||c_version;
end show_version;


    PROCEDURE set_tab_ident (v_tab_ident IN VARCHAR2) IS
    BEGIN
        my_tab_ident.owner :=
            UPPER (
                NVL (
                    SUBSTR (
                        TRIM (v_tab_ident),
                        1,
                        INSTR (TRIM (v_tab_ident), '.') - 1
                    ),
                    USER
                )
            );
        my_tab_ident.table_name :=
            UPPER (
                SUBSTR (
                    TRIM (v_tab_ident),
                    INSTR (TRIM (v_tab_ident), '.') + 1,
                    100
                )
            );
        pkg_common_logging.write_log (
            'table owner: ' || my_tab_ident.owner,
            pkg_common_logging.set_debug
        );
        pkg_common_logging.write_log (
            'table_name: ' || my_tab_ident.table_name,
            pkg_common_logging.set_debug
        );
    END;
    
    
FUNCTION check_global_index (p_owner in varchar2, p_table_name in VARCHAR2, force_drop IN BOOLEAN default false)
        RETURN BOOLEAN IS
        e_global_index   EXCEPTION;
        v_id                  NUMBER;
    BEGIN
        -- check global indexes
        SELECT    COUNT (*)
          INTO    v_id
          FROM    all_indexes
         WHERE         partitioned <> 'YES'
                    AND table_name = upper(trim(p_table_name))
                    AND index_type <> 'LOB'
                    AND owner =upper(trim( p_owner) ) ;

        pkg_common_logging.write_log (
            'global indexes found: ' || TO_CHAR (v_id),
            pkg_common_logging.set_debug
        );

        -- bail out if global index exists
        IF (v_id > 0 AND NOT force_drop) THEN
            pkg_common_logging.write_log (
                'force_drop must be true in oder to modify partitions',
                pkg_common_logging.set_info
            );
            RAISE e_global_index;
        END IF;

        RETURN TRUE;
    EXCEPTION
        WHEN e_global_index THEN
            DBMS_OUTPUT.put_line (
                'Global Indexes exists, can not drop a partition without index invalidation'
            );
              pkg_common_logging.write_log (  'Global Indexes exists, can not drop a partition without index invalidation');
              
            RETURN FALSE;
        WHEN OTHERS THEN
            RAISE;
    END check_global_index;
    
    

function is_partitioned(v_owner in varchar2, v_tname in varchar2) return boolean is
    
    v_ret all_tables.PARTITIONED%type;
    
begin
    
    select PARTITIONED into v_ret from all_tables where 
        owner    = upper(v_owner)
        and table_name = upper(v_tname);

b_partitioned := v_ret in ('YES');

return b_partitioned;


end;



function f_is_iot (v_owner in varchar2, v_tname in varchar2) return boolean is
    
    is_iot_ret all_tables.iot_type%type;
begin

    select nvl(iot_type,'NO')  into is_iot_ret from all_tables where owner = v_owner and table_name = v_tname;
    
    if is_iot_ret = 'IOT' then
        is_iot := true;
        
    else 
        is_iot := false;
    end if;
    
    return is_iot;


end;


procedure set_redef_option(v_owner in varchar2, v_tname in varchar2) 
    is
    v_tmp number := 0;
begin

    -- check for pk
    select count(*) into v_tmp from all_constraints where table_name = upper(trim(v_tname)) and owner = upper(trim(v_owner))
     and constraint_type = 'P';
     
      pkg_common_logging.write_log('select count(*) into v_tmp from all_constraints where table_name = upper(trim(v_tname)) and owner = upper(trim(owner))->'
                            ||nvl(to_char(v_tmp),'null')||';'||upper(trim(v_tname)) ||';'|| upper(trim(v_owner)), pkg_common_logging.set_debug);

  if v_tmp > 0 then 
      redef_option_flag := dbms_redefinition.cons_use_pk;
      pkg_common_logging.write_log('using PK for redefinition');
  else
      redef_option_flag := dbms_redefinition.cons_use_rowid;
            pkg_common_logging.write_log('using ROWID  for redefinition');
  end if;
  
  
  
exception
    when others  then 
      pkg_common_logging.write_log_error;
      raise;
      
end;


procedure run_script (v_sql in varchar2, v_log_info in varchar2 default null, fail_on_error in boolean default true)
is

begin

    pkg_common_logging.write_log(v_sql, pkg_common_logging.set_info);
execute immediate v_sql;
    pkg_common_logging.r_comlog_app_log.NO_OF_RECORDS := sql%rowcount;

    if v_log_info is not null then  
        pkg_common_logging.write_log(v_log_info);
    end if;



exception
    when others  then 
      pkg_common_logging.write_log_error;
      if  fail_on_error then
       raise;    
      end if;
      
      
end run_script;



 procedure init_jobs_iot(v_owner varchar2, v_tname in varchar2,v_date_start in date , v_date_end in date, exchange_part in boolean default false)
 
 is
    v_curr_date date := v_date_start -1 ;
    v_job number;
    sql_string varchar2(500);
    
 begin
 
 pkg_common_logging.init_log('v_SESSION_MODULE','init_jobs_iot' ,'start '||v_owner||'.'
                            ||v_tname||' for '||to_char(v_date_start,'yyyymmdd')||' - '||to_char(v_date_end,'yyyymmdd')
                            ); 
 
 
  if pkg_obj_rbld.gv_ddl_source_table is null then
    pkg_obj_rbld.gv_ddl_source_table := v_tname;
     pkg_common_logging.write_log('source ddl set to: '||pkg_obj_rbld.gv_ddl_source_table);
  end if;
 
 loop   
  
  v_curr_date := v_curr_date + 1;
 
    sql_string:='begin pkg_obj_rbld.gv_ddl_source_table := '''||pkg_obj_rbld.gv_ddl_source_table
                    ||'''; pkg_obj_rbld.cr_tic_data_tab('''||v_owner||''','''||v_tname||''',to_date('''
                    ||to_char(v_curr_date,'yyyymmdd')||''',''yyyymmdd''),'|| 
                     case   
                        when exchange_part then 
                            'true'
                        else
                            'false'
                         end                
                    ||' ); end;';
                    
    
    
                     DBMS_JOB.SUBMIT(v_job,sql_string, sysdate);
                    
                    pkg_common_logging.write_log('cr_tic_data_tab - '||to_char(v_curr_date,'yyyymmdd')||': job created'); 
 
 
 exit when v_curr_date >= v_date_end;
 

 
 end loop;
 
 
   pkg_common_logging.reset_log;
   
   
 exception

WHEN OTHERS THEN

pkg_common_logging.write_log_error;
    pkg_common_logging.reset_log;
    
    
RAISE;


end init_jobs_iot;

procedure init_jobs(v_owner in varchar2, v_tname in varchar2, v_partition_name in varchar2 default 'ALL',
                    start_date in date default sysdate  ) is

sql_string varchar2(1024) ;







TYPE T_RC_PART is REF CURSOR;

C_RC_PART T_RC_PART;

RC_PART_SQL varchar2(1000) := 'select partition_name from all_tab_partitions '||
                              ' where table_name = :b1 and table_owner = :b2 ';
                           --   ' and partition_name = nvl(:b3,partition_name) order by partition_position';
                              
 v_p_name         all_tab_partitions.partition_name%type;                  


begin

pkg_common_logging.init_log(v_SESSION_MODULE,v_tname ,'start');

if is_partitioned(v_owner,v_tname) then 


IF v_partition_name <> 'ALL' then 
    RC_PART_SQL := RC_PART_SQL ||' and partition_name like :b3 order by partition_position';
else
     RC_PART_SQL := RC_PART_SQL ||' and :b3 is not null  order by partition_position';
end if;
  pkg_common_logging.write_log(RC_PART_SQL, pkg_common_logging.set_debug); 


        open C_RC_PART for RC_PART_SQL using   upper(v_tname) , upper(v_owner), upper(v_partition_name);


         loop
            
                    fetch C_RC_PART into v_p_name;
                    EXIT WHEN C_RC_PART%NOTFOUND;


                      sql_string:='pkg_obj_rbld.run_tbl_rbld('''||v_owner||''','''||v_tname||''','''||v_p_name||''');';
                    
                     DBMS_JOB.SUBMIT(v_job,sql_string,start_date);
                    
                    pkg_common_logging.write_log(v_tname||'.'||v_p_name||':job created'); 
                    
                    commit;

         end loop;

else
     sql_string:='pkg_obj_rbld.run_tbl_rbld('''||v_owner||''','''||v_tname||''');';
     DBMS_JOB.SUBMIT(v_job,sql_string,start_date);
     pkg_common_logging.write_log(v_tname||' (unpartitioned): job created'); 
     commit;
     
end if;

 pkg_common_logging.write_log(v_tname||'all jobs created');
 pkg_common_logging.reset_log;

exception

WHEN OTHERS THEN

pkg_common_logging.write_log_error;
    pkg_common_logging.reset_log;
    
    
RAISE;


end init_jobs;

procedure drop_ref_const (v_owner in varchar2, v_tname in varchar2)
is


begin

for r_rec in 
(
    select 
    'alter table  '||owner||'.'||table_name||' drop constraint '||constraint_name  v_stmt
     from  all_constraints a
    where r_constraint_name  in
        (
        select constraint_name  
         from all_constraints b where b.table_name = v_tname
         and b.owner = v_owner
         and a.owner = b.owner
        )
    union all
    select 
    'alter table '||owner||'.'||table_name||' drop constraint '||constraint_name  v_stmt
     from all_constraints where table_name = v_tname
    and constraint_type ='R' and owner = v_owner
)
loop


run_script(r_rec.v_stmt,r_rec.v_stmt);

end loop;

end;




procedure cr_interm_tab(v_interm_name out varchar2, full_create in boolean default true)
 is
   v_stmt varchar2(32000);
   
 begin

  
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',  pkg_obj_rbld.gv_use_orig_tbsp);
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE', FALSE);
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',full_create); 
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',FALSE);
 dbms_metadata.set_transform_param(   dbms_metadata.session_transform, 'PRETTY', false);


 set_tab_ident( pkg_obj_rbld.gv_ddl_source_table);
 v_interm_name := substr(my_tab_ident.table_name,1,10)||dbms_random.string('u',15);
 pkg_common_logging.write_log('v_interm_name='||v_interm_name,pkg_common_logging.set_debug);
 
 if 
  --
  -- tabelle ist partitioniert und soll ohne partitionen neu erstellt werden
  --
      pkg_obj_rbld.gv_remove_part and 
      is_partitioned(my_tab_ident.owner,my_tab_ident.table_name)
      then
      
   
    pkg_common_logging.write_log('v_interm_name='||v_interm_name,pkg_common_logging.set_debug);
  
 v_stmt := 'create table '||my_tab_ident.owner||'.'||v_interm_name ||' as select * from '|| pkg_obj_rbld.gv_ddl_source_table||' where 1 = 2';
 pkg_common_logging.write_log('create nonpartitioned table: '||v_interm_name,  pkg_common_logging.set_info);
 run_script(v_stmt);
 
 
else
  --
  -- normales handling
  -- nicht '  tabelle ist partitioniert und soll ohne partitionen neu erstellt werden '
  --


 -- replace the name  
 select substr(dbms_metadata.get_ddl ('TABLE', my_tab_ident.table_name, my_tab_ident.owner),1,32000)  into v_stmt from dual;
 v_stmt := replace(v_stmt,'"'|| my_tab_ident.owner||'"."'||my_tab_ident.table_name||'"',
                                      '"'|| my_tab_ident.owner||'"."'||v_interm_name||'"');
 
 -- pk aendern        

                              
 v_stmt   := regexp_replace (v_stmt, 'CONSTRAINT ".*" PRIMARY KEY','CONSTRAINT "'||substr(v_interm_name,1,25) ||'_PK" PRIMARY KEY')   ;                           

 pkg_common_logging.write_log('replace table name in ddl to: '||v_interm_name,  pkg_common_logging.set_info);
 
 run_script(v_stmt);

if full_create then 

 -- indexe
 for r_ind in 
  (select index_name  from all_indexes where table_name = my_tab_ident.table_name and owner = my_tab_ident.owner and index_type not in ('LOB')
   and 
        -- pk ausschließen
        index_name not in 
    ( select index_name from all_constraints where  table_name =  my_tab_ident.table_name and owner = my_tab_ident.owner and constraint_type = 'P' ) 
     )
  loop
  
   select substr(dbms_metadata.get_ddl ('INDEX', r_ind.index_name, my_tab_ident.owner),1,32000)  into v_stmt from dual;
   
    pkg_common_logging.write_log('replace table name in index to: '||v_interm_name,  pkg_common_logging.set_info);
      v_stmt := replace(v_stmt,'"'|| my_tab_ident.owner||'"."'||my_tab_ident.table_name||'"',
                                      '"'|| my_tab_ident.owner||'"."'||v_interm_name||'"');
  
  
      pkg_common_logging.write_log('replace index name in index to: '|| r_ind.index_name||'_1',  pkg_common_logging.set_info);
          v_stmt := replace(v_stmt,'"'|| my_tab_ident.owner||'"."'||r_ind.index_name||'"',
                                      '"'|| my_tab_ident.owner||'"."'||r_ind.index_name||'1"');
  
      pkg_common_logging.write_log('create index   '|| my_tab_ident.owner||'.'||r_ind.index_name||'1');
       run_script(v_stmt);
  
  end loop;
 
 end if;
 
 end if;

end;

procedure run_tbl_rbld (v_owner in varchar2, v_tname in varchar2 , v_partition_name in varchar2 default null)
is

    v_stmt varchar2(1024);
    t_interm_name varchar2(32);
    v_err number;
    
    
begin
--

pkg_common_logging.init_log('pkg_obj_rbl',v_tname ,'Rebuild '|| v_partition_name); 


 if is_partitioned(v_owner,v_tname) and   check_global_index(v_owner,v_tname, pkg_obj_rbld.gv_ddl_force)  
and not gv_ignore_parts and not gv_remove_part
then 
pkg_common_logging.write_log('start partition wise rebuild', PKG_COMMON_LOGGING.SET_DEBUG);
-- todo:
  -- erstellen eines 1:1 tables, aber nicht partitioniert
  -- partitioniert: indexe erstellen, aber nicht partitioniert
  -- nehmen wir von  pkg_obj_rbld.gv_ddl_source_table
  

  

        for r_rec in 
        (select table_name, tablespace_name, partition_name , table_owner from all_tab_partitions
         where table_owner = upper(v_owner) and
                table_name = upper(v_tname) and
                partition_name = nvl(upper(v_partition_name),partition_name) )
      loop
     
      
    cr_interm_tab(t_interm_name );
  --
  PKG_COMMON_LOGGING.SET_ACTION(v_tname||':'||    r_rec.partition_name)      ;
    
  run_script( 'alter session force parallel dml parallel 4');

  run_script( 'alter session force parallel query parallel 4');
 
  dbms_redefinition.can_redef_table(uname   =>v_owner,
                                        tname =>v_tname,
                                        part_name => r_rec.partition_name );
                                 
   pkg_common_logging.write_log('Redefinition is possbile, starting now');  
   
      DBMS_REDEFINITION.START_REDEF_TABLE(uname => v_owner, 
                                           orig_table => v_tname, 
                                           int_table => t_interm_name,
                                           part_name  => r_rec.partition_name);
       
       pkg_common_logging.write_log('START_REDEF_TABLE done');                                       
 
       DBMS_REDEFINITION.FINISH_REDEF_TABLE( uname => v_owner,  
                       orig_table => v_tname, 
                       int_table => t_interm_name,
                        part_name  => r_rec.partition_name);
                        
       pkg_common_logging.write_log('FINISH_REDEF_TABLE done');                 
      
      -- die intermed. tabelle ist jetzt verbrannt und muss weg
      run_script('drop table  '|| my_tab_ident.owner||'.'||t_interm_name);

     end loop;
     

         -- todo
     -- rebuild globale indexe online
    -- pkg_obj_rbld.run_ind_rbld(v_owner,v_tname,r_rec.partition_name);
 
    
  elsif not is_partitioned(v_owner,v_tname) or gv_ignore_parts or gv_remove_part
  --
  -- we will rebuild tables entirely into a new table
  --
  then
  
  
  PKG_COMMON_LOGGING.write_log('table not partitioned',PKG_COMMON_LOGGING.set_debug);
  -- tabelle ist nicht partitioniert.
  
  if pkg_obj_rbld.gv_ddl_source_table is null then
   pkg_obj_rbld.gv_ddl_source_table := v_owner||'.'|| v_tname ;
  end if;
  --
  -- hier erstellen wir es ohne indexe, 
  -- full create ist deaktiviert (false)
  --
  cr_interm_tab(t_interm_name, false);
  --
  PKG_COMMON_LOGGING.SET_ACTION(v_tname)  ;
    
  run_script( 'alter session force parallel dml parallel 4');

  run_script( 'alter session force parallel query parallel 4');
  set_redef_option(v_owner,v_tname) ;

 dbms_redefinition.can_redef_table(uname   =>v_owner,
                                        tname =>v_tname,
                                        options_flag => redef_option_flag );
                                 
   pkg_common_logging.write_log('Redefinition is possbile, starting now using:'||t_interm_name);  
   
      DBMS_REDEFINITION.START_REDEF_TABLE(uname => v_owner, 
                                           orig_table => v_tname, 
                                           int_table => t_interm_name,
                                           options_flag => redef_option_flag);
       
       pkg_common_logging.write_log('START_REDEF_TABLE done');                                       
 
 
  dbms_redefinition.copy_table_dependents(v_owner, 
                         v_tname, 
                         t_interm_name,
                         1, 
                         TRUE, 
                         TRUE, 
                         TRUE , 
                         true, 
                         v_err);
   pkg_common_logging.write_log('copy_table_dependents; number rof errors: '||to_char(v_err)); 
 
 
       DBMS_REDEFINITION.FINISH_REDEF_TABLE( uname => v_owner,  
                       orig_table => v_tname, 
                       int_table => t_interm_name);
                        
       pkg_common_logging.write_log('FINISH_REDEF_TABLE done');                 
   --
   -- alte ref constrants löschen
   --   
   
       drop_ref_const(v_owner,t_interm_name);
   
   
      -- die intermed. tabelle ist jetzt verbrannt und muss weg
      
      if not pkg_obj_rbld.gv_keep_intermed then
      run_script('drop table  '|| my_tab_ident.owner||'.'||t_interm_name,'drop table  '|| my_tab_ident.owner||'.'||t_interm_name);
       else
        pkg_common_logging.write_log('not done: drop table  '|| my_tab_ident.owner||'.'||t_interm_name);
      end if;
      
      


  else
  
   --
   -- diese tabelle ist vermutlich partitioniert, hat aber globale indexe und 'force' ist nicht true
   --
   
       pkg_common_logging.write_log('table is most likely partitioned and has global indexes, please check');                 
  
  end if;
  


  
  
  
    set_ddl_source_table(null);
    pkg_common_logging.reset_log;

exception

WHEN OTHERS THEN

    pkg_common_logging.write_log_error;
    raise;
end run_tbl_rbld;


procedure run_ind_rbld (v_owner in varchar2, v_tname in varchar2 ,v_partition_name in varchar2 default null)
is

    v_stmt varchar2(1024);
    
    
    
    
begin


 
  for r_rec in 
        (select b.index_name, a.partition_name, nvl(a.tablespace_name, b.tablespace_name) tablespace_name,
        b.index_type
             from 
        all_indexes b,
        all_ind_partitions a where 
         a.index_owner(+) = b.owner
        and a.index_name(+) = b.index_name
        and b.table_name(+) = upper(v_tname)
         and b.owner(+)  = upper(v_owner)
         and a.partition_name(+) = upper(v_partition_name)) 
   loop
   
   if r_rec.index_type in ('NORMAL') then
   
    begin
    --
    -- see MetaLink Doc ID 312843.1
    --
    --v_stmt := 'alter index '||v_owner||'.'||r_rec.index_name|| ' partition '||r_rec.partition_name ||' compress';
    -- pkg_common_logging.write_log(v_stmt, pkg_common_logging.set_debug);
    --execute immediate v_stmt;
    
    

     
    if r_rec.partition_name is null then
        
            v_stmt := 'alter index '||v_owner||'.'||r_rec.index_name|| ' rebuild tablespace '
                       ||r_rec.tablespace_name || ' parallel '|| parallel_degree;
                       
    else
            v_stmt := 'alter index '||v_owner||'.'||r_rec.index_name|| ' rebuild partition '||r_rec.partition_name ||' tablespace '
                       ||r_rec.tablespace_name || ' parallel '|| parallel_degree;
            
    end if;
               
     pkg_common_logging.write_log(v_stmt, pkg_common_logging.set_debug);
     
     
   if r_rec.tablespace_name is null then 
    
     pkg_common_logging.write_log('index '||v_owner||'.'||r_rec.index_name||'.'||v_partition_name||' does not exists');
   
   else
   
   
    execute immediate v_stmt;
    
     pkg_common_logging.write_log('index '||v_owner||'.'||r_rec.index_name||'.'||r_rec.partition_name||' rebuild done');
   
   end if;  
    
    exception  
    
        when others then 
        pkg_common_logging.write_log_error;    
    end;
   
   else
   
   pkg_common_logging.write_log('index '||r_rec.index_name||' type '||r_Rec.index_type||'-> not normal', pkg_common_logging.set_debug);
   
   end if;
   
   
   
   
   end loop;
         

pkg_common_logging.reset_log;


exception

WHEN OTHERS THEN

    pkg_common_logging.write_log_error;
    raise;
 
    
end run_ind_rbld;




 procedure set_ddl_source_table (v_name in all_tables.table_name%type) is
 
 begin

    pkg_obj_rbld.gv_ddl_source_table := v_name;
 
 
 end;
 
 
 
   procedure use_orig_tbsp (b_use_orig_tbsp in boolean default true)
   is
   begin
   
      pkg_obj_rbld.gv_use_orig_tbsp := b_use_orig_tbsp; 
   end;
   
   
   
   
 procedure set_force_ddl (b_force_ddl in boolean default false) is
 begin
  pkg_obj_rbld.gv_ddl_force := b_force_ddl;
 end;



 
 procedure set_keep_intermed  (b_keep_intermed in boolean default false) is
 

  begin
  pkg_obj_rbld.gv_keep_intermed := b_keep_intermed;
 end;

   procedure set_ignore_parts  (b_ignore_parts in boolean default false)
   is
   
    begin
  pkg_obj_rbld.gv_ignore_parts := b_ignore_parts;
 end;
 
  procedure set_remove_part  (b_remove_part in boolean default false)
   is
   
    begin
  
  -- rebuilding a partitioned into a non partitioned table
  -- does not need to take global indexes into account
   
  pkg_obj_rbld.gv_remove_part := b_remove_part;
  pkg_obj_rbld.gv_ddl_force := true;
  pkg_obj_rbld.gv_ignore_parts := true;
 end;
 
 
end pkg_obj_rbld;
/