• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

HOW TO EXECUTE THIS PROCEDURE

i have the following procedure, which is used to mark local indexes as unusable. So, i have a table A with an index named INDEX1. how make the index INDEX1 unusable using the procedure i have (below is the procedure).


Open in new window


CREATE OR REPLACE PROCEDURE CUST_DW."SP_MARK_UNUSABLE_LOCAL_INDEXES" (
   i_table_name   IN   user_indexes.table_name%TYPE,
                          --table for which indexes have to be marked unusable
   p_table_name        VARCHAR2   --table that defines the partitioned columns
)
AUTHID DEFINER
IS
-- variables
   v_col_sql        VARCHAR2 (500);
   v_col_name       VARCHAR2 (32);
   v_part_sql       VARCHAR2 (500);
   v_part_name      VARCHAR2 (50);
   i                NUMBER  := 0;
   v_year_mo        VARCHAR2 (8);

   TYPE partnametyp IS TABLE OF VARCHAR2 (50)
      INDEX BY BINARY_INTEGER;

   v_partname       partnametyp;

   TYPE partcurtyp IS REF CURSOR;

   v_part_cur       partcurtyp;
   part_rec         VARCHAR2 (40);

   TYPE colcurtyp IS REF CURSOR;

   v_col_cur        colcurtyp;
   col_rec          VARCHAR2 (40);

   CURSOR id_columns
   IS
      SELECT DISTINCT column_name
                 FROM all_tab_columns
                WHERE table_name = p_table_name
                  AND column_name IN ('JOURNAL_ENTRY_DT', 'REVENUE_YEARMO')
             ORDER BY column_name DESC;

   -- identify local indexes on a given table
   CURSOR id_indexes (c_table_name user_indexes.table_name%TYPE)
   IS
      SELECT index_name
        FROM user_indexes a
       WHERE a.table_name = i_table_name
         AND a.uniqueness = 'NONUNIQUE'
         AND index_type = 'BITMAP';

-- record variables
   rec_id_indexes   id_indexes%ROWTYPE;
   rec_id_columns   id_columns%ROWTYPE;
-- variables
   l_status         NUMERIC;
   l_table_name     user_indexes.table_name%TYPE;
   l_stmt           VARCHAR2 (255);
BEGIN
   l_status := 0;
   l_table_name := UPPER (i_table_name);

   OPEN id_columns;

   FETCH id_columns
    INTO rec_id_columns;

   IF rec_id_columns.column_name = 'REVENUE_YEARMO'
   THEN
      v_col_sql :=
            'SELECT DISTINCT '
         || rec_id_columns.column_name
         || ' FROM '
         || p_table_name;
   ELSE
      v_col_sql :=
            'SELECT DISTINCT '
         || 'TO_CHAR('
         || rec_id_columns.column_name
         || ','
         || '''YYYYMM'')'
         || 'FROM '
         || p_table_name;
   END IF;

--   DBMS_OUTPUT.put_line (v_col_sql);
--execute immediate v_col_sql into v_year_mo;
--   DBMS_OUTPUT.put_line (v_year_mo);

   CLOSE id_columns;

   OPEN v_col_cur FOR v_col_sql;

   LOOP
      FETCH v_col_cur
       INTO col_rec;

      EXIT WHEN v_col_cur%NOTFOUND;

      OPEN id_indexes (l_table_name);

      LOOP
         FETCH id_indexes
          INTO rec_id_indexes;

         EXIT WHEN id_indexes%NOTFOUND;
         v_part_sql :=
               'SELECT partition_name
        FROM user_ind_partitions
       WHERE index_name = '''
            || rec_id_indexes.index_name
            || ''' AND SUBSTR (partition_name, 2, 6) IN ('
            || col_rec
            || ')';


         OPEN v_part_cur FOR v_part_sql;

         LOOP
            FETCH v_part_cur
             INTO part_rec;

            EXIT WHEN v_part_cur%NOTFOUND;
            l_stmt :=
                  'ALTER INDEX '
               || rec_id_indexes.index_name
               || ' MODIFY PARTITION '
               || part_rec
               || ' UNUSABLE ';
            DBMS_OUTPUT.put_line (l_stmt);

            EXECUTE IMMEDIATE l_stmt;
--         dbms_output.put_line('partition_nam is '||part_Rec);
         END LOOP;

         IF (v_part_cur%ROWCOUNT = 0)
         THEN                                              -- no indexes found
            DBMS_OUTPUT.put_line
                      (   '-- rebuild_indexes: No partitions found on table '
                       || l_table_name
                      );
         END IF;

         CLOSE v_part_cur;

         i := i + 1;
--      DBMS_OUTPUT.put_line ('part sql is ' || v_part_sql);
--      DBMS_OUTPUT.put_line ('i value is ' || i);
--      DBMS_OUTPUT.PUT_LINE('INDEX_NAME IS '||REC_ID_INDEXES.INDEX_NAME);
      END LOOP;

      IF (id_indexes%ROWCOUNT = 0)
      THEN                                                 -- no indexes found
         DBMS_OUTPUT.put_line
                         (   '-- rebuild_indexes: No indexes found on table '
                          || l_table_name
                         );
      END IF;                                                 -- no rows found

      CLOSE id_indexes;
   END LOOP;

   CLOSE v_col_cur;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      l_status := 1002;
      DBMS_OUTPUT.put_line
                          (   ' rebuild_indexes: no indexes exist for table '
                           || l_table_name
                          );
   WHEN OTHERS
   THEN
      l_status := SQLCODE;
      DBMS_OUTPUT.put_line (' rebuild_indexes: ' || SQLERRM (l_status));

      IF (id_indexes%ISOPEN)
      THEN
         CLOSE id_indexes;
      END IF;
END SP_MARK_UNUSABLE_LOCAL_INDEXES;
/
CREATE OR REPLACE PROCEDURE CUST_DW."SP_MARK_UNUSABLE_LOCAL_INDEXES" (
   i_table_name   IN   user_indexes.table_name%TYPE,
                          --table for which indexes have to be marked unusable
   p_table_name        VARCHAR2   --table that defines the partitioned columns
)
AUTHID DEFINER
IS
-- variables
   v_col_sql        VARCHAR2 (500);
   v_col_name       VARCHAR2 (32);
   v_part_sql       VARCHAR2 (500);
   v_part_name      VARCHAR2 (50);
   i                NUMBER  := 0;
   v_year_mo        VARCHAR2 (8);

   TYPE partnametyp IS TABLE OF VARCHAR2 (50)
      INDEX BY BINARY_INTEGER;

   v_partname       partnametyp;

   TYPE partcurtyp IS REF CURSOR;

   v_part_cur       partcurtyp;
   part_rec         VARCHAR2 (40);

   TYPE colcurtyp IS REF CURSOR;

   v_col_cur        colcurtyp;
   col_rec          VARCHAR2 (40);

   CURSOR id_columns
   IS
      SELECT DISTINCT column_name
                 FROM all_tab_columns
                WHERE table_name = p_table_name
                  AND column_name IN ('JOURNAL_ENTRY_DT', 'REVENUE_YEARMO')
             ORDER BY column_name DESC;

   -- identify local indexes on a given table
   CURSOR id_indexes (c_table_name user_indexes.table_name%TYPE)
   IS
      SELECT index_name
        FROM user_indexes a
       WHERE a.table_name = i_table_name
         AND a.uniqueness = 'NONUNIQUE'
         AND index_type = 'BITMAP';

-- record variables
   rec_id_indexes   id_indexes%ROWTYPE;
   rec_id_columns   id_columns%ROWTYPE;
-- variables
   l_status         NUMERIC;
   l_table_name     user_indexes.table_name%TYPE;
   l_stmt           VARCHAR2 (255);
BEGIN
   l_status := 0;
   l_table_name := UPPER (i_table_name);

   OPEN id_columns;

   FETCH id_columns
    INTO rec_id_columns;

   IF rec_id_columns.column_name = 'REVENUE_YEARMO'
   THEN
      v_col_sql :=
            'SELECT DISTINCT '
         || rec_id_columns.column_name
         || ' FROM '
         || p_table_name;
   ELSE
      v_col_sql :=
            'SELECT DISTINCT '
         || 'TO_CHAR('
         || rec_id_columns.column_name
         || ','
         || '''YYYYMM'')'
         || 'FROM '
         || p_table_name;
   END IF;

--   DBMS_OUTPUT.put_line (v_col_sql);
--execute immediate v_col_sql into v_year_mo;
--   DBMS_OUTPUT.put_line (v_year_mo);

   CLOSE id_columns;

   OPEN v_col_cur FOR v_col_sql;

   LOOP
      FETCH v_col_cur
       INTO col_rec;

      EXIT WHEN v_col_cur%NOTFOUND;

      OPEN id_indexes (l_table_name);

      LOOP
         FETCH id_indexes
          INTO rec_id_indexes;

         EXIT WHEN id_indexes%NOTFOUND;
         v_part_sql :=
               'SELECT partition_name
        FROM user_ind_partitions
       WHERE index_name = '''
            || rec_id_indexes.index_name
            || ''' AND SUBSTR (partition_name, 2, 6) IN ('
            || col_rec
            || ')';


         OPEN v_part_cur FOR v_part_sql;

         LOOP
            FETCH v_part_cur
             INTO part_rec;

            EXIT WHEN v_part_cur%NOTFOUND;
            l_stmt :=
                  'ALTER INDEX '
               || rec_id_indexes.index_name
               || ' MODIFY PARTITION '
               || part_rec
               || ' UNUSABLE ';
            DBMS_OUTPUT.put_line (l_stmt);

            EXECUTE IMMEDIATE l_stmt;
--         dbms_output.put_line('partition_nam is '||part_Rec);
         END LOOP;

         IF (v_part_cur%ROWCOUNT = 0)
         THEN                                              -- no indexes found
            DBMS_OUTPUT.put_line
                      (   '-- rebuild_indexes: No partitions found on table '
                       || l_table_name
                      );
         END IF;

         CLOSE v_part_cur;

         i := i + 1;
--      DBMS_OUTPUT.put_line ('part sql is ' || v_part_sql);
--      DBMS_OUTPUT.put_line ('i value is ' || i);
--      DBMS_OUTPUT.PUT_LINE('INDEX_NAME IS '||REC_ID_INDEXES.INDEX_NAME);
      END LOOP;

      IF (id_indexes%ROWCOUNT = 0)
      THEN                                                 -- no indexes found
         DBMS_OUTPUT.put_line
                         (   '-- rebuild_indexes: No indexes found on table '
                          || l_table_name
                         );
      END IF;                                                 -- no rows found

      CLOSE id_indexes;
   END LOOP;

   CLOSE v_col_cur;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      l_status := 1002;
      DBMS_OUTPUT.put_line
                          (   ' rebuild_indexes: no indexes exist for table '
                           || l_table_name
                          );
   WHEN OTHERS
   THEN
      l_status := SQLCODE;
      DBMS_OUTPUT.put_line (' rebuild_indexes: ' || SQLERRM (l_status));

      IF (id_indexes%ISOPEN)
      THEN
         CLOSE id_indexes;
      END IF;
END SP_MARK_UNUSABLE_LOCAL_INDEXES;
/

Open in new window

0
D-pk
Asked:
D-pk
  • 18
  • 16
1 Solution
 
slightwv (䄆 Netminder) Commented:
Is tableA a partitioned table?  Is index1 a local partition index?  Is it a nonunique and bitmap index?

If not, you can't use that code.

Also, the first loop through the column names doesn't appear to do anything.
0
 
D-pkAuthor Commented:
INDEX1 is a normal index  and no, the table is not partitioned table. ITS a global index. Any other way to make the index unusable, or to disable the index , so that i can rebuild it after i do the inserts?
0
 
slightwv (䄆 Netminder) Commented:
Just issue the SQL directly from sqlplus.

alter index index1 unusable;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
D-pkAuthor Commented:
I did give the statement

alter index index1 unusable;

but it says the index name does not exist, but i can see the same index in that table.

ORA-01418: specified index does not exist
0
 
slightwv (䄆 Netminder) Commented:
Are you logged in as the owner of the index?

if not:
alter index owner.index1 unusable;

where 'owner' is, well, the owner.
0
 
D-pkAuthor Commented:
Yes i'm logged in as the owner
0
 
slightwv (䄆 Netminder) Commented:
>>but i can see the same index in that table

How are you 'seeing it'?

post the results of:
select table_name from user_indexes where index_name='INDEX1';

0
 
D-pkAuthor Commented:
No, i'm not logged in as the owner of the index, and i tried the statement

alter index owner.index1 unusable;

it still gives the same error

ORA-01418: specified index does not exist
0
 
slightwv (䄆 Netminder) Commented:
>>alter index owner.index1 unusable;

Did you replace the actual word 'owner' with the correct username/owner of the index?

If SCOTT owns the index:
alter user scott.index1 unusable;
0
 
slightwv (䄆 Netminder) Commented:
If you still cannot figure it out please post the results of:
select owner,table_name from all_indexes where index_name='INDEX1';
0
 
D-pkAuthor Commented:
I did execute the statement,

Single Record View
As of: 4/20/2011 10:43:14 AM

TABLE_NAME:  


it did not return any table name
0
 
slightwv (䄆 Netminder) Commented:
>>it did not return any table name

Then the user you are connected with cannot see a table where there is an index named 'INDEX1'.

Can you see the dba views?

select owner,table_name from dba_indexes where index_name='INDEX1';
0
 
D-pkAuthor Commented:
THIS IS THE RESULT FOR THE STATEMENT

select owner,table_name from all_indexes where index_name='INDEX1';

Single Record View
As of: 4/20/2011 10:45:12 AM

OWNER:       C_DW
TABLE_NAME:  A
0
 
slightwv (䄆 Netminder) Commented:
Then this should work:

alter index C_DW.INDEX1 unusable;
0
 
D-pkAuthor Commented:
I'm not logged in as C_DW
0
 
slightwv (䄆 Netminder) Commented:
>>I'm not logged in as C_DW

so?  as long as you have the permissions it shouldn't matter.
0
 
D-pkAuthor Commented:
It still gives the same error

ORA-01418: specified index does not exist
0
 
slightwv (䄆 Netminder) Commented:
The code in http:#a35433983 gives you that error?

Although I would expect a different error, you might not have permission to mark the index as unusable.

What user are you currently connected as?
0
 
D-pkAuthor Commented:
CAPP is the USERNAME which i'm logged in as
0
 
slightwv (䄆 Netminder) Commented:
I just verified this on my test database.  You will receive the ORA-01418 when you do not have the permission to do this.

CAPP does not have the permission granted to modify C_DW's indexes.
0
 
D-pkAuthor Commented:
so, i must be logged in as C_DW to do it, right?
0
 
slightwv (䄆 Netminder) Commented:
There are other options:
be granted the permission to do it.

or (and I suspect this is the reason for the procedure you originally posted)
have C_DW create a procedure to perform the action and grant CAPP execute permission on the procedure.
0
 
D-pkAuthor Commented:
I now logged in as C_DW, made the index unusable and again logged in as CAPP and ran the insert statement and it gave me the following error

ORA-01502: index 'C_DW.INDEX1or partition of such index is in unusable state
0
 
slightwv (䄆 Netminder) Commented:
Are you sure the table isn't partitioned?

I cannot reproduce that error on my development database.  What version of Oracle are you using?

What is the reason for marking it as unusable until the rebuild is done?
0
 
D-pkAuthor Commented:
When i ran the insert query before making the index UNUSABLE , it gave me the following error.

ORA-00001: unique constraint (CUST_DW.INDEX1) violated
0
 
slightwv (䄆 Netminder) Commented:
>>ORA-00001: unique constraint (CUST_DW.INDEX1) violated

so making it unusable won't fix that.  unusable only involves the Optimizer and what it has available.

Even if you drop the index, you will not be able to rebuild it if the insert produces constraint violations.
0
 
D-pkAuthor Commented:
I thought if i make the index unusable and run the INSERT command, it will go through and then after the required rows are inserted i can rebuild the index back. And, no the table is not partitioned.
0
 
D-pkAuthor Commented:
Then any other sollution for this issue? can we drop and re-create the index?
0
 
D-pkAuthor Commented:
Any way to disable and then enable the index back?
0
 
slightwv (䄆 Netminder) Commented:
>>after the required rows are inserted i can rebuild the index back

not if there are duplicate rows.

>>I thought if i make the index unusable and run the INSERT command

OK, I reproduced the error.  If the index is UNIQUE (used to enforce integrity), you cannot insert the table with the index unusable.  That could allow exactly what you are trying to get around: an integrity violation.
0
 
slightwv (䄆 Netminder) Commented:
>>Any way to disable and then enable the index back?

Even if it is possible, how are you going to resolve the constraint violation (ORA-00001: unique constraint (CUST_DW.INDEX1) violated )?
0
 
slightwv (䄆 Netminder) Commented:
>>Then any other sollution for this issue? can we drop and re-create the index?
yes but see above http:#a35434476.
0
 
D-pkAuthor Commented:
But i'm running the insert using the primary key of the table ,  So how could there be duplicates in the primary key?
0
 
slightwv (䄆 Netminder) Commented:
>>But i'm running the insert using the primary key of the table ,  So how could there be duplicates in the primary key?

No idea.  I don't know your tables or insert statement.  Anyway, that is a different question that the one asked.

The question asked is how to execute that procedure to make an index 'unusable'.  The answer is "you cannot", you need to just issue the alter index command.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 18
  • 16
Tackle projects and never again get stuck behind a technical roadblock.
Join Now