Re: Hanging Constraints

On Apr 23, 1:16 pm, Tim <TimothyO..._at_gmail.com> wrote:
> >Can you check using the dba_constraints, instead of all_constraints
> >and let us know the results:
> > SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_NAME =
> >'PK_CATALOG_ID'
>
> The constraint is not listed here either. There are no results.
>
> On Apr 23, 12:17 pm, Vince <vinn..._at_yahoo.com> wrote:
>
> > > 6. If I try and drop this constraint from the table, I recieve:
> > > Cannot drop constraint - nonexistent constraint
>
> > Likely, this is an index of the same name. Where in your script are
> > you dropping the PK index?
>
> I had thought of this before, but this doesn't seem to be the case. I
> looked in the ALL_INDEXES view as well and couldn't find anything.
>
> The script does a simple drop on each table in the schema.
>
> drop table TABLE_NAME cascade constraints
>
> Also, I can run the script repeatedly without problems in other
> schemas, but when I try and run it in this specific schema, it breaks.
> I'm assuming that somehow something didn't get dropped properly at one
> point due to some error, and now I just have bad data hanging around.
>
> Again, if I run this: SELECT * FROM SYS.CDEF$ WHERE OBJ# NOT IN
> (SELECT OBJ# FROM SYS.OBJ$) some rows are listed, and one of the is
> the constraint that is causing me problems.
>
> My assumption is that:

Found some "help"

http://www.dbforums.com/archive/index.php/t-849990.html

Seems that maybe my dictionary is corrupt. That article is fairly old, but states that it is not possible to repair the dictionary. Is there anything in newer versions (10g) of Oracle to do this?

> The CDEF$ CON# column corresponds to an entry in the constraints
> table, and the OBJ# column points at the table to which the constraint
> is applied.
>
> For the above query, the problem constraint lists:
> CON# OBJ#
> 6550 54672
>
> SELECT * FROM CON$ WHERE CON# = 6550 returns a row with the name
> PK_CATALOG_ID
>
> SELECT * FROM OBJ$ WHERE OBJ# = 54672 returns nothing.
>
> -Tim
Received on Wed Apr 23 2008 - 13:03:38 CDT