Exadata General (MOSC)

MOSC Banner

Unable to drop constraint on a table

edited Jul 3, 2014 5:00AM in Exadata General (MOSC) 2 commentsAnswered

The constraint drop takes for ever in a table of 14GB with (657 Million rows - OLTP Compressed). There are no active sessions on the table and neither any rollback segment on this table in the database.

ALTER TABLE SCHEMA1.Table123 drop CONSTRAINT SYS_C00377512

LongOps Message: Table Scan:  (stale or locked) obj# 13516108: 1722 out of 1731199 Blocks done

Scenario: The table has 8 constraints and i am trying to drop those. It worked for dropping 2 constraints but hangs for third one showing time remaining to table scan as 22 hours. I tried to re-create the table and tried again, but the issue was same again after dropping 2 constraints. Also gathering the statistics in this table doesn't works.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

Instructions for posting

×

1. Select a discussion category from the picklist.


2. Enter a title that clearly identifies the subject of your question.


3. In the body, insert detailed information, including Oracle product and version.


Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI).


Cloud / Fusion customers - Our Cloud community has moved! Please go to Cloud Customer Connect .


New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center