Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> ORA-01502 error while truncating the table, ORA-01502 Index or partition is in unusable status.
krmreddy
post Apr 22 2010, 08:55 AM
Post #1


Member
**

Group: Members
Posts: 24
Joined: 6-July 08
Member No.: 27,853



Hi All,


One of our Devlopers compalined that he is getting ORA-01502 : Index or partition is unusable status while truncating the a table in our Dataware house production database. He is using the following commands.

Alter index <index_name> unusable;

Truncate table <table_name> ;

He is running a scripts to truncate each table and ecah time he is passing the table name as an input parameter to script. He is using same method to truncate four tables each having a BITMAP and a REGULAR index. For two tables every thing is working fine, but for other two tables the he is getting ORA-01502 for BITMAP indexes. It a weekly process and every week he is getting the same issue. I checkd the Index status, they are in valid status only.

For a work around I have created a table with BITMAP and regular index in our dev database. made the indexes unusable, checked their status. I truncated the table. Importent thing here is the Indexes are becoming vaild when I truncate the table.

I suspect that my devloper's Indexes were already in unusable status (before he use the command ALTER INDEX), when he truncated the table, oracle trying to validate the index and throwing the error ORA-01502 because the Indexes are in unusabel statsu for a while.

I tried searching for the mechanism of truncate table command and its effect on Indexes. But I did not find any luck, no one is speaking about index when truncating the table. Can any one please help me????


Sorry for lengthy post. Any help is greatly appriciated and I thank every one in advance. rolleyes.gif
Go to the top of the page
 
+Quote Post
SteveC
post Apr 22 2010, 09:01 AM
Post #2


Advanced Member
***

Group: Members
Posts: 2,835
Joined: 11-March 08
Member No.: 18,933



And the point of marking an index unusable beforehand is...?

Documentation, it's really underrated at times.
http://download.oracle.com/docs/cd/B19306_....htm#SQLRF01707
"If table is not empty, then the database marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table."
Go to the top of the page
 
+Quote Post
HAL9000
post Apr 22 2010, 02:19 PM
Post #3


Advanced Member
***

Group: Members
Posts: 884
Joined: 25-September 07
Member No.: 12,336




Of course an index is unusable when the rows are being truncated:

QUOTE
ORA-01502 index 'string.string' or partition of such index is in unusable state.

Cause: An attempt has been made to access an index or index partition ...

Action: DROP the specified index, or REBUILD the specified index


"One of our Devlopers compalined that he is getting ORA-01502 : Index or partition is unusable status while truncating the a table in our Dataware house production database"

You let a DEVELOPER truncate a production table?

You deserve what you get, what a MORONIC thing to do.

Tell your manager IMMEDIATELY.

The DBA who granted truncate privileges to this developer should be fired immediately.



Go to the top of the page
 
+Quote Post
krmreddy
post Apr 22 2010, 04:06 PM
Post #4


Member
**

Group: Members
Posts: 24
Joined: 6-July 08
Member No.: 27,853



Thanks for your response Steves.

"If table is not empty, then the database marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table."
[/quote]


But I did try on our Dev databases. Though my table has data, the Truncate command did not alter the index when they are in VALID status, But it did change Index status VALID when they are in UNUSABLE status. We are using Oracle 10.2.0.4. Does it depends on Oracle version???


Thanks again.
Go to the top of the page
 
+Quote Post
krmreddy
post Apr 23 2010, 10:06 AM
Post #5


Member
**

Group: Members
Posts: 24
Joined: 6-July 08
Member No.: 27,853



QUOTE (HAL9000 @ Apr 22 2010, 11:20 AM) *
Of course an index is unusable when the rows are being truncated:



"One of our Devlopers compalined that he is getting ORA-01502 : Index or partition is unusable status while truncating the a table in our Dataware house production database"

You let a DEVELOPER truncate a production table?
You deserve what you get, what a MORONIC thing to do.

Tell your manager IMMEDIATELY.

The DBA who granted truncate privileges to this developer should be fired immediately.



Hey HAL, I am not sure whether he is devloper or not. But this person does not belongs to Production support team, who runs all jobs in our Prod environment. This person may be from App team who loads new data into the table evry week, truncating the old data. I cancel my word DEVELOPER laugh.gif

Go to the top of the page
 
+Quote Post
krmreddy
post Apr 25 2010, 12:11 AM
Post #6


Member
**

Group: Members
Posts: 24
Joined: 6-July 08
Member No.: 27,853



Any other ideas on this issue please???
Go to the top of the page
 
+Quote Post
burleson
post Apr 25 2010, 09:22 AM
Post #7


Advanced Member
***

Group: Members
Posts: 11,710
Joined: 26-January 04
Member No.: 13




>> This person may be from App team who loads new data into the table evry week, truncating the old data.

That's not very smart, doing a manual procedure that could be fully automated . . . .

***************************************
>> I tried searching for the mechanism of truncate table command and its effect on Indexes.

Truncate quickly nukes a table by lowering the high water mark to zero, leaving the row data in-place . . .

See here, how to speed-up truncate performance:

http://www.dba-oracle.com/t_truncate_table_performance.htm

Obviously, you should drop your indexes first . . . .





--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th November 2014 - 03:03 PM