?
Solved

Marking Index as Unusable ORACLE 11G

Posted on 2012-09-05
12
Medium Priority
?
790 Views
Last Modified: 2012-09-18
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
0
Comment
Question by:cookiejar
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1500 total points
ID: 38369331
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38370731
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38370733
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:cookiejar
ID: 38370760
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38370775
"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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38371908
>>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
 

Author Comment

by:cookiejar
ID: 38372931
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
 

Author Comment

by:cookiejar
ID: 38372952
If  an error occurs before executing the REBUILD INDEX statement, how should I handle this?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38373017
>>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
 

Author Comment

by:cookiejar
ID: 38373100
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38373185
>>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
 
LVL 32

Expert Comment

by:awking00
ID: 38373527
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month14 days, 23 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question