Drop Partition Issue, Drop Partition issue with 11.2.0.1 Options

[Rekha Anupati]

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?

[burleson]

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

[Rekha Anupati]

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?

[burleson]

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.

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"

[burleson]

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;

[Rekha Anupati]

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.

[Rekha Anupati]

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,

[SteveC]

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.