Marking Index as Unusable ORACLE 11G

In a stored procedure I am marking indexes as unusable before truncating and inserting data due to having to insert over 300,000 records.
While running the stored procedure I checked to see if indexes were flagged and they weren't.
When I execute these statements from TOAD, they are marked with a red X to indicate they have been flagged as unusable.

What are some factors that could contribute to them not being flagged when statements are executed in the stored procedure?


I've done the following
BEGIN
 EXECUTE IMMEDIATE ('ALTER INDEX careergrp_ndx UNUSABLE');
 EXECUTE IMMEDIATE ('ALTER INDEX rating_ndx UNUSABLE');
 EXECUTE IMMEDIATE 'TRUNCATE TABLE training;

FOR  per_rec IN get_person_training
   
    LOOP
       INSERT
   END LOOP;

-- REBUILD INDEXES
cookiejarAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Maybe a Toad bug?

Check the status of the indexes outside of Toad:
select index_name, status from user_indexes where lower(index_name) in ('careergrp_ndx','rating_ndx');
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
please add exception section to your code block and log the errors to the log/error table or the screen so that you can get to know if any errors in the dynamic sql statements execution.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
the strange behaviour/issues which we cannot find easily by reading the dynamic sql code can be found with the exception handler/error logging. also if there are any errors related to locks etc while executing the ddl in the dynamic mode, we will come to know with the exception handler.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cookiejarAuthor Commented:
I added the error handling but it did not capture the error.   Funny thing though, if I execute the alter index statements before the truncate table statement, the indexes are marked as unusable.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
"if I execute the alter index statements before the truncate table statement, the indexes are marked as unusable. " --> But that is what your code is also doing right.

EXECUTE IMMEDIATE ('ALTER INDEX careergrp_ndx UNUSABLE');
 EXECUTE IMMEDIATE ('ALTER INDEX rating_ndx UNUSABLE');
 EXECUTE IMMEDIATE 'TRUNCATE TABLE training;

So what is not working for you now ?
0
 
slightwv (䄆 Netminder) Commented:
>>Funny thing though, if I execute the alter index statements before the truncate table statement

I am also very confused by this comment.  Were were you executing this when you asked this question?
0
 
cookiejarAuthor Commented:
I am sorry I meant if I execute the truncate table before alter index -  indices are flagged as unusable

EXECUTE IMMEDIATE 'TRUNCATE TABLE training;
EXECUTE IMMEDIATE ('ALTER INDEX careergrp_ndx UNUSABLE');
EXECUTE IMMEDIATE ('ALTER INDEX rating_ndx UNUSABLE');

When I execute in this manner, indices are not flagged as unusable:
EXECUTE IMMEDIATE ('ALTER INDEX careergrp_ndx UNUSABLE');
 EXECUTE IMMEDIATE ('ALTER INDEX rating_ndx UNUSABLE');
 EXECUTE IMMEDIATE 'TRUNCATE TABLE training';
0
 
cookiejarAuthor Commented:
If  an error occurs before executing the REBUILD INDEX statement, how should I handle this?
0
 
slightwv (䄆 Netminder) Commented:
>>When I execute in this manner, indices are not flagged as unusable:

Have you checked from sqlplus?  If the statements execute, they sort of have to 'work'.

I wonder if the code completes and rebuilds the indexes before you see them in their 'unusable' state.

>>If  an error occurs before executing the REBUILD INDEX statement, how should I handle this?

Code the proper steps in the exception handler.  What do you want to happen?

Best case, issue the rebuild commands in the exception handler but that might not be possible based on what steps caused the exception.
0
 
cookiejarAuthor Commented:
Have you checked from sqlplus?
   I ran from sqlplus with altering index first, then truncate table - did  not flag unusable
   I ran from sqlplus with truncate table first, then alter index  - flagged unusable
     

slightwv - As I stated in previous posts, I am a novice so bear with me.

This is what I have in my error handling.  It throws an error index does not exist.
EXCEPTION
     WHEN NO_DATA_FOUND THEN
         NULL;
     WHEN OTHERS THEN
         EXECUTE IMMEDIATE('ALTER INDEX rate_ndx REBUILD NOLOGGING');
         EXECUTE IMMEDIATE ('ALTER INDEX careergrp_ndx REBUILD NOLOGGING');      
         RAISE;
0
 
slightwv (䄆 Netminder) Commented:
>>Have you checked from sqlplus?

I meant checking the unusable flag from sqlplus not Toad:  re: http:#a38369331

select index_name, status from user_indexes where lower(index_name) in ('careergrp_ndx','rating_ndx');


Post the output of this code:
--declare a variable at the top of the code

junk varchar2(50);

--then add some degugging

select status into junk from user_indexes where lower(index_name) = 'careergrp_ndx';

dbms_output.put_line('Before careergrp_ndx: ' || junk); 
EXECUTE IMMEDIATE ('ALTER INDEX careergrp_ndx UNUSABLE');
select status into junk from user_indexes where lower(index_name) = 'careergrp_ndx';
dbms_output.put_line('After careergrp_ndx: ' || junk); 

select status into junk from user_indexes where lower(index_name) = 'rating_ndx'; 
dbms_output.put_line('Before rating_ndx: ' || junk); 
EXECUTE IMMEDIATE ('ALTER INDEX rating_ndx UNUSABLE');
select status into junk from user_indexes where lower(index_name) = 'rating_ndx'; 
dbms_output.put_line('After rating_ndx: ' || junk); 
EXECUTE IMMEDIATE 'TRUNCATE TABLE training'; 

Open in new window


>>This is what I have in my error handling.  It throws an error index does not exist.

Does the index exist?  If it does then it should be rebuildable.

Remember, I mentioned that depending on what generated the exception that the rebuild might not be possible.
0
 
awking00Commented:
I have a feeling that flagging an index as unusable in TOAD has a different meaning than altering the index as unusable in sqlplus. I think maybe truncating a table that has indexes causes TOAD to "flag" those indexes, not because they've been altered to unusable, but because no indexes will be used once the table has been truncated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.