rebuild_table's Index

----------重建索引，还是不错的procude，建议大家参考参考

Purpose: This Procedure will rebuild Invalid indexes on the Table. And it can be used upon Partitioned and non partitioned tables.


What we should know before running this procedure:
The user running this procedure should have proper grants to run this procedure.

What to know before running this procedure.
This procedure will only rebuild indexes that are invalid. And status of the index will be in unusable state.

Ex: exec Rebuild_Index ('TDF1496','dly_test');
Here TDF1496 is the Schema: The owner of the table
And DLY_TEST is the Table name

EXEC Rebuild_Index (‘SCHEMA_NAME’,’TABLE_NAME’);


CREATE OR REPLACE PROCEDURE SHODS01.Rebuild_Index (
    p_schema_name     IN VARCHAR2,
    p_table_name     IN VARCHAR2)
AS
    v_no_table_found EXCEPTION;
    v_table_count     NUMBER;
    v_sql              VARCHAR2 (2000);
BEGIN
    SELECT COUNT ( * )
      INTO v_table_count
      FROM all_tables
     WHERE UPPER (table_name) = UPPER (p_table_name)
             AND UPPER (owner) = UPPER (p_schema_name);


    IF (v_table_count = 0)
    THEN
        RAISE v_no_table_found;
    ELSE
        BEGIN
            dbms_index_utl.build_table_indexes (
                p_schema_name || '.' || p_table_name,
                TRUE,
                'ALL',
                TRUE,
                TRUE,
                8);
        END;
    END IF;
EXCEPTION
    WHEN v_no_table_found
    THEN
        raise_application_error (
            -20735,
            'Please Check the table name you may be entered wrong table name or table does not exists');
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
END;
/







Example on a Partitioned Table:

Create Table:
CREATE TABLE DLY_TEST
(
  BATCH_DTE_CYMD             DATE               NOT NULL,
  ACCT_NO                          number
)
TABLESPACE TSHODSTAB1
PARTITION BY RANGE (BATCH_DTE_CYMD)
( 
  PARTITION P_ACT_RLS_D_20100301 VALUES LESS THAN (TO_DATE(' 2010-03-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE TS201003)
/

CREATE UNIQUE INDEX DLY_test_I1U ON DLY_test
(ACCT_NO, BATCH_DTE_CYMD)
COMPRESS LOCAL ( 
  PARTITION P_ACT_RLS_D_20100301
    TABLESPACE TS201003)
/



Create partitions on the table using the Gen_Part_DLY procedure.
set serveroutput on
set define off
variable v_error varchar2(100);
exec Gen_Part_DLY ('TDF1496','DLY_TEST','02-MAR-2010','15-mar-2010','NONE','M',:v_error);

alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100302 values less than (to_date (' 2010-03-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100303 values less than (to_date (' 2010-03-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100304 values less than (to_date (' 2010-03-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100305 values less than (to_date (' 2010-03-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100308 values less than (to_date (' 2010-03-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100309 values less than (to_date (' 2010-03-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100310 values less than (to_date (' 2010-03-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100311 values less than (to_date (' 2010-03-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100312 values less than (to_date (' 2010-03-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
alter table TDF1496.DLY_TEST add partition P_ACT_RLS_D_20100315 values less than (to_date (' 2010-03-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TSM201003
PL/SQL procedure successfully completed.

Now make indexes unusable using the following:
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100301 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100302 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100303 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100304 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100305 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100308 unusable;
alter index DLY_TEST_I1U modify partition P_ACT_RLS_D_20100309 unusable;
Make sure that the indexes are invalid:
select index_name,partition_name,status from dba_ind_partitions where index_name like 'DLY%' order by partition_name;

INDEX_NAME                     PARTITION_NAME                 STATUS 
------------------------------ ------------------------------ --------
DLY_TEST_I1U                   P_ACT_RLS_D_20100301           UNUSABLE
DLY_TEST_I1U                   P_ACT_RLS_D_20100302           UNUSABLE
DLY_TEST_I1U                   P_ACT_RLS_D_20100303           UNUSABLE
DLY_TEST_I1U                   P_ACT_RLS_D_20100304           UNUSABLE
DLY_TEST_I1U                   P_ACT_RLS_D_20100305           UNUSABLE
DLY_TEST_I1U                   P_ACT_RLS_D_20100308           UNUSABLE
DLY_TEST_I1U                   P_ACT_RLS_D_20100309           UNUSABLE
DLY_TEST_I1U                   P_ACT_RLS_D_20100310           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100311           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100312           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100315           USABLE 

11 rows selected.
Now run the procedure Rebuild indexes: This procedure will only rebuild indexes that are invalid.

Set Serveroutput on
exec rebuild_index4 ('TDF1496','DLY_TEST');

PL/SQL procedure successfully completed.  

If the Indexes are huge then this will take some time.








Now Verify the Status of the Indexes :

select index_name,partition_name,status from dba_ind_partitions where index_name like 'DLY%' order by partition_name;


INDEX_NAME                     PARTITION_NAME                 STATUS 
------------------------------ ------------------------------ --------
DLY_TEST_I1U                   P_ACT_RLS_D_20100301           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100302           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100303           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100304           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100305           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100308           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100309           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100310           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100311           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100312           USABLE 
DLY_TEST_I1U                   P_ACT_RLS_D_20100315           USABLE 

11 rows selected.






How To rebuild indexes on a normal table :
Create a test table :
CREATE TABLE TDF1496.DLY_TEST1
(
  BATCH_DTE_CYMD1  DATE                         NOT NULL,
  ACCT_NO1         NUMBER
)
TABLESPACE TSHODSTAB1;

CREATE UNIQUE INDEX TDF1496.YDLY_TEST_I1U1 ON TDF1496.DLY_TEST1
(ACCT_NO1, BATCH_DTE_CYMD1);

After the table is created make sure that the index is valid :
select  index_name,STATUS from dba_indexes where index_name like 'YDLY%';


INDEX_NAME                     STATUS 
------------------------------ --------
YDLY_TEST_I1U1                 VALID  
1 row selected.


Now invalidate the Index using this command :
alter index YDLY_TEST_I1U1 unusable;

Verify the status of the index

select  index_name,STATUS from dba_indexes where index_name like 'YDLY%';
INDEX_NAME                     STATUS 
------------------------------ --------
YDLY_TEST_I1U1                 UNUSABLE
1 row selected.

Run the Procedure:
Set Serveroutput on
exec rebuild_index ('TDF1496','DLY_TEST1');

Verify the status of the index :
select  index_name,STATUS from dba_indexes where index_name like 'YDLY%';

INDEX_NAME                     STATUS 
------------------------------ --------
YDLY_TEST_I1U1                 VALID  
1 row selected.