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
> Drop Partition Issue, Drop Partition issue with 11.2.0.1
Rekha Anupati
post Apr 19 2012, 07:54 PM
Post #1


Advanced Member
***

Group: Members
Posts: 130
Joined: 19-April 12
From: california
Member No.: 47,104



we have our production database running on 11.2.0.2. I have a user table which has partitions. i would like to drop the partition and seeing weird issue:

The command to drop the partition i have is:

alter table SA.accounts drop partition dec_20111203 update global indexes;

Say after 10 minutes, the session is terminated and we are seeing the following: ORA-00028. We don't have a process that kills the session so we are trying to find the root cause why the session is being killed. any idea what might be going on? also as an alternative - i was looking to do the following and want to know - if this works or not:

ALTER TABLE sa.accounts TRUNCATE PARTITION dec_20111203;

Then try to do the drop partition again.

What do you experts suggest?
Go to the top of the page
 
+Quote Post
burleson
post Apr 20 2012, 06:53 AM
Post #2


Advanced Member
***

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



Hi Rekha,

Does this always happen, or is it sporadic?

>> Say after 10 minutes, the session is terminated and we are seeing the following: ORA-00028.

This is a sporadic issue, and you may have a bug:


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

If the problem percists, open an SR with MOSC:

http://support.oracle.com


--------------------
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
Rekha Anupati
post Apr 20 2012, 01:37 PM
Post #3


Advanced Member
***

Group: Members
Posts: 130
Joined: 19-April 12
From: california
Member No.: 47,104



Burleson,

Many thanks for taking time to reply to my issue. No wonder people call you the best. thanks again,

I have found something intresting and dont know, how to proceed. we have an dev environment and i ran the following command on Dev (Surprisingly works fine, but in prod give's an error) Dont know what's going on: yes both and dev are similar:

ALTER TABLE sa.accounts TRUNCATE PARTITION acc_201201 update global indexes; --- this works in Dev and doesnt work in Prod.

i am wondering, what all to check. I am pretty sure they both are similar and nothing has changed.

can you please suggest, what to look for now?
Go to the top of the page
 
+Quote Post
burleson
post Apr 20 2012, 04:39 PM
Post #4


Advanced Member
***

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



Hi Rekha,

>> command on Dev (Surprisingly works fine, but in prod give's an error)

Good Clue!

Is DEV smaller?

Does DEV have less active users?

Did you open an SR to check for a bug? The "update global indexes" can cause an insert statement waiting to complete this task on " cursor: pin S wait on X ".

It's a semaphore (mutex) issue, an exclusive lock . . .

Check out V$MUTEX_SLEEP and V$MUTEX_SLEEP_HISTORY for more details . . .

The Oracle docs note that "cursor: pin S wait on X" is when a session waits for this event when it is requesting a shared mutex pin and another session is holding an exclusive mutex pin on the same cursor object.

QUOTE
Parameter Description

P1 Hash value of cursor
P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3 Mutex where (an internal code locator) OR'd with Mutex Sleeps



See MOSC Note: 401435.1 and bug 5907779 - Self deadlock hang on "cursor: pin S wait on X"


--------------------
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
burleson
post Apr 20 2012, 05:17 PM
Post #5


Advanced Member
***

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



Hi Rekha,

As a workaround to the bug, try doing it in two steps:

ALTER TABLE sa.accounts TRUNCATE PARTITION dec_20111203;

alter table sa.accounts update global indexes;


--------------------
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
Rekha Anupati
post Apr 23 2012, 11:33 AM
Post #6


Advanced Member
***

Group: Members
Posts: 130
Joined: 19-April 12
From: california
Member No.: 47,104



Burleson,

Thank you so much for the offered solution. We did execute the query in two steps as suggested by you and it did worked in our environment: The query we ran was:

ALTER TABLE sa.accounts TRUNCATE PARTITION dec_20111203;
alter table sa.accounts update global indexes;

Many thanks again and you are simply great.



Go to the top of the page
 
+Quote Post
Rekha Anupati
post Apr 23 2012, 04:02 PM
Post #7


Advanced Member
***

Group: Members
Posts: 130
Joined: 19-April 12
From: california
Member No.: 47,104



Will running the following statement leave indexes in us-usable state?

ALTER TABLE sa.accounts TRUNCATE PARTITION dec_20111203;

apprecaited if you please let me know.

Thanks,
Go to the top of the page
 
+Quote Post
SteveC
post Apr 23 2012, 09:52 PM
Post #8


Advanced Member
***

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



QUOTE (Rekha Anupati @ Apr 23 2012, 02:02 PM) *
Will running the following statement leave indexes in us-usable state?

ALTER TABLE sa.accounts TRUNCATE PARTITION dec_20111203;

apprecaited if you please let me know.

Thanks,


Yes, that can be a performance disaster. Depending upon the size of your database and amount of indexing on your partitioned table, you could be looking at days (at least, many uncomfortable hours) before things are fixed.
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: 18th December 2014 - 06:04 PM