CODEWITHSUNDEEP
oracle-databasepartitioning
homer
homer

Reputation: 433

Index rebuild for partitioned tables?

Friends...I'm trying to debug procedure to move and rebuild indexes for partitioned tables.

I'm trying to do below:

  1. Move partitioned table to new tablespace
  2. Rebuild indexes of #1 partitioned tables

For #2 I'm using dba_ind_partitions but since it doesn't have table_name, table_owner it's hard to filter out.

PROCEDURE moveParTbl (OldTbs in varchar2, NewTbs in varchar2)
    AUTHID CURRENT USER
IS
  CURSOR curTable IS
    SELECT table_owner, table_name, partition_name, tablespace_name
      FROM dba_tab_partition
      WHERE tablespace_name = OldTbs
      ORDER BY TABLE_NAME;

  CURSOR curIndex (lParNam IN varchar2)
IS
  SELECT index_owner, index_name, partition_name, tablespace_name
  FROM dba_ind_partitions
  WHERE partition_name = lParNam;


BEGIN
  FOR rec1 IN curTable LOOP

    EXECUTE IMMEDIATE 'alter table ' || rec1.table_owner || '.' || rec1.table_name ||
                      ' move partition ' || rec1.partition_name || ' tablespace ' ||NewTbs;

   -- Starting curIndex
  FOR rec2 IN curIndex (rec1.partition_name) LOOP
    EXECUTE IMMEDIATE 'rebuild index ' || rec2.index_owner || '.' || rec2.index_name ||
                      ' rebuild partition ' || rec2.partition_name || ' tablespace ' ||NewTbs || ' online';

      END LOOP; -- index cursor loop
      END LOOP;  --curTable for loop
END moveParTbl;

What's the error:

1 Table: Tab-A, Tab-B, Tab-C
2. Index: Tab-A (Ind1, Ind2, Ind3)

Tab-A 3 indexes gets rebuild after every partitioned table move and not just once after Tab-A move.

* below is wrong

Move Tab-A
Rebuild ind1, ind2, ind3
Move TAb-B
Rebuild ind1, ind2, ind3
Move Tab-C
Rebuld ind1, ind2, ind3

* How it should be

Move Tab-A
Rebuild ind1, ind2, ind3
Move TAb-B
Move Tab-C

Upvotes: 1

Views: 2965

Answers (1)

Daniel Stolf
Daniel Stolf

Reputation: 245

You probably have repeated partition names across your tables (oracle doesn't complain about that because the full segment_name is composed of [object_name]_[partition_name]). You need to join dba_tables and dba_indexes (on table_name and table_owner) and dba_ind_partitions (on index_name and index_owner) if you want to get only indexes partitions related to that specific table partition. Your cursor curIndex should look like this:

          CURSOR curIndex (lTabOwn IN varchar2, lTabNam IN varchar2, lParNam IN varchar2)
          IS
            select i.index_owner, i.index_name, ip.partition_name from 
                dba_tables t join dba_indexes i 
            on t.table_name=i.table_name and t.owner=i.table_owner
                join dba_ind_partitions ip on i.index_name=ip.index_name and 
                     i.owner=ip.index_owner
            where t.table_name=lTabNam and ip.partition_name=lParNam and 
            and t.owner=lTabOwn and status<>'USABLE';

And, of course, you'll need to feed owner and table_name into it also, not only the partition_name.

Upvotes: 3

Related Questions