Prev: Cannot connect to Oracle 10g database
Next: Complex Structures within PL/SQL
From: Tim on 23 Apr 2008 10:42
I think this may be a bug in Oracle, but Im not 100% sure.

A little background: I have a script which builds up and tears down a
database schema for testing purposes. This script is run several times
a day, and usually works fine but I just had the following issue
arise.

1. In this script, I have a table called CATALOG_ITEM.

2. The script tries to add a primary key to this table via:

ALTER TABLE CATALOG_ITEM ADD ( CONSTRAINT PK_CATALOG_ID PRIMARY KEY
(CATALOG_ID) NOT DEFERRABLE INITIALLY IMMEDIATE )

3. However, this complains that:
"name already used by an existing constraint"

4. When I do a SELECT * FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME =
'PK_CATALOG_ID' there is nothing in there with that name.

5. However, if I do a SELECT * FROM SYS.OBJ$ WHERE NAME =
'PK_CATALOG_ID' there is an entry for an object.

6. If I try and drop this constraint from the table, I recieve:
Cannot drop constraint - nonexistent constraint

7/ Alarmingly, this query returns a few rows, one of them being the
PK_CATALOG_ID:

SELECT * FROM SYS.CDEF$ WHERE OBJ# NOT IN (SELECT OBJ# FROM SYS.OBJ$)

I'm assuming (maybe incorrectly) that CDEF$ is short for constraints
definition, and that I have constraints that are now pointing to
tables that no longer exist.

Any idea how to clean this up or what would cause it? It seems that
none of the SYS
tables that I'm looking at have foreign key constraints so I'm
extremely hesitant to start deleting or even touching records in the
SYS schema.

Thanks,
Tim Orme
From: madhusreeram on 23 Apr 2008 11:27

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'

-Madhu
From: Vince on 23 Apr 2008 12:17

>
> 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?

From: Tim on 23 Apr 2008 13:16
>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...(a)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:
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
From: Tim on 23 Apr 2008 14:03
On Apr 23, 1:16 pm, Tim <TimothyO...(a)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...(a)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

 | 
Pages: 1
Prev: Cannot connect to Oracle 10g database
Next: Complex Structures within PL/SQL