Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

RE: Help with sql identifying dup constraints

Jacques Kilchoer

2004-06-09

Well, you can't have two "not null" constraints on a column (see example below showing the error) as far as I know.
What I think you have is one "NOT NULL" constraint and one CHECK constraint. From the DBA_ views NOT NULL constraints and CHECK constraints look the same, but in SYS.CDEF$ they have different values for TYPE#.
As you noticed you can't compare the constraint text in SQL since it's stored in a LONG. (Shouldn't this column have been changed to a CLOB in 9.2? But I digress.)
I would write a query that looks for a NOT NULL constraint (which can only have one column), and then look for a CHECK constraint that only has one column and the same column as the not null constraint. This should help narrow your search down, and by looking at the text for the CHECK constraint you could easily figure out which ones are duplicates of a not null constraint. E.g. something like this:

select
   c.username || '.' || d.table_name || '.' || d.column_name as constraint_column,
   b.name || ' (NN)' as nn_constraint_name,
--   * you don't really need this text since we know it will be
--   * "COLUMN_NAME" IS NOT NULL
--   * a.condition as nn_constraint_text,
   f.name || ' (CHECK)' as ck_constraint_name,
   e.condition as ck_constraint_text
 from
   sys.cdef$ a, sys.con$ b, dba_users c, dba_cons_columns d,
   sys.cdef$ e, sys.con$ f, dba_cons_columns g
 where
   a.type# = 7
   and a.cols = 1
   and a.con# = b.con#
   and b.owner# = c.user_id
   and c.username = d.owner
   and b.name = d.constraint_name
   and e.obj# = a.obj#
   and e.type# = 1
   and e.con# = f.con#
   and f.owner# = b.owner#
   and c.username = g.owner
   and f.name = g.constraint_name
   and d.column_name = g.column_name
--  * add your specific conditions here from dba_users
--  * and dba_cons_columns
   and c.username = user ;

Example: I try to create a table with two NOT NULL constraints on the same column - Oracle error. However I can create a column with a NOT NULL constraint and a similar CHECK constraint. My query will show this.

SQL> create table t (n number not null, d date not null) ;
Table créée.
SQL> alter table t add (check (n is not null)) ;
Table modifiée.
SQL> alter table t modify (d not null) ;
alter table t modify (d not null)
                      *
ERREUR à la ligne 1 :
ORA-01442: colonne à modifier en NOT NULL est déjà NOT NULL

SQL> select
  2     c.username || '.' || d.table_name || '.' || d.column_name as constraint_column,
  3     b.name || ' (NN)' as nn_constraint_name,
  4  --   * you don't really need this text since we know it will be
  5  --   * "COLUMN_NAME" IS NOT NULL
  6  --   * a.condition as nn_constraint_text,
  7     f.name || ' (CHECK)' as ck_constraint_name,
  8     e.condition as ck_constraint_text
  9   from
 10     sys.cdef$ a, sys.con$ b, dba_users c, dba_cons_columns d,
 11     sys.cdef$ e, sys.con$ f, dba_cons_columns g
 12   where
 13     a.type# = 7
 14     and a.cols = 1
 15     and a.con# = b.con#
 16     and b.owner# = c.user_id
 17     and c.username = d.owner
 18     and b.name = d.constraint_name
 19     and e.obj# = a.obj#
 20     and e.type# = 1
 21     and e.con# = f.con#
 22     and f.owner# = b.owner#
 23     and c.username = g.owner
 24     and f.name = g.constraint_name
 25     and d.column_name = g.column_name
 26  --  * add your specific conditions here from dba_users
 27  --  * and dba_cons_columns
 28     and c.username = user ;

CONSTRAINT_COLUMN
-----------------------------------------------------------------------------------------------
NN_CONSTRAINT_NAME                  CK_CONSTRAINT_NAME
----------------------------------- --------------------------------------
CK_CONSTRAINT_TEXT
--------------------------------------------------------------------------------
JRK.T.N
SYS_C005033 (NN)                    SYS_C005035 (CHECK)
n is not null



Now if I add another check constraint on column d,
SQL> alter table t add (check (d > to_date ('2000/01/01', 'YYYY/MM/DD'))) ;
Table modifiée.


We will see that the query will show a NOT NULL constraint on column d and also a check constraint on column d, but the text of the constraint is enough to tell us that we don't have a "duplicate" not null constraint.

SQL> select
  2     c.username || '.' || d.table_name || '.' || d.column_name as constraint_column,
  3     b.name || ' (NN)' as nn_constraint_name,
  4  --   * you don't really need this text since we know it will be
  5  --   * "COLUMN_NAME" IS NOT NULL
  6  --   * a.condition as nn_constraint_text,
  7     f.name || ' (CHECK)' as ck_constraint_name,
  8     e.condition as ck_constraint_text
  9   from
 10     sys.cdef$ a, sys.con$ b, dba_users c, dba_cons_columns d,
 11     sys.cdef$ e, sys.con$ f, dba_cons_columns g
 12   where
 13     a.type# = 7
 14     and a.cols = 1
 15     and a.con# = b.con#
 16     and b.owner# = c.user_id
 17     and c.username = d.owner
 18     and b.name = d.constraint_name
 19     and e.obj# = a.obj#
 20     and e.type# = 1
 21     and e.con# = f.con#
 22     and f.owner# = b.owner#
 23     and c.username = g.owner
 24     and f.name = g.constraint_name
 25     and d.column_name = g.column_name
 26  --  * add your specific conditions here from dba_users
 27  --  * and dba_cons_columns
 28     and c.username = user ;

CONSTRAINT_COLUMN
----------------------------------------------------------------------------------------------
NN_CONSTRAINT_NAME                  CK_CONSTRAINT_NAME
----------------------------------- --------------------------------------
CK_CONSTRAINT_TEXT
--------------------------------------------------------------------------------
JRK.T.N
SYS_C005033 (NN)                    SYS_C005035 (CHECK)
n is not null

JRK.T.D
SYS_C005034 (NN)                    SYS_C005036 (CHECK)
d > to_date ('2000/01/01', 'YYYY/MM/DD')




-----Original Message-----
From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Barbara Baker

I appear to have a bit of a mess on my hands. I've
identified some tables that have a duplicate "not
null" constraint on the same column.  Only difference
in the constraints is that one is generated and one is
user named (even tho they're both sys_c00xxx
constraints).

(I believe this happened when a vendor used a 3rd
party pkg to try to duplicate their schema in our
database.)

I'd like to identify all of the tables with this
condition. Any method I can think to do this requires
comparing the search condition of dba_constraints,
which is a LONG.

Can anyone think of a way to do this?