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

Daniel Fink

2004-06-16

Replies:
For the past few days, I have been waging war with dbms_metadata (and I'm still standing!). One of the issues I had to deal with was
the incorrect parsing of a view for output into a ddl call. Finally got that a workaround coded for that one this morning. Whew!

Okay, so what does this have to do with Barb's problem? (Barb, you should stop reading now if you don't want to run up a serious tab
at Pint's Pub). dba_views.text is a long...dba_constraints.search_condition is a long. I needed to perform character operations on
the text, so I needed to convert it to a character string...not possible in one step. However, if you create a table (gtt in this
case) where the text is stored as a CLOB, you can use the TO_LOB function on the insert to perform this conversion. Once you convert
it to a CLOB, you can use TO_CHAR and apply character functions. Including = in a predicate!

Here you go...it works for me..in a 9i db. (8i has clobs, but to_char does not appear to be able to handle them). If you are on 8i,
sorry...

create global temporary table gtt_constraints
( owner varchar2(30),
 table_name varchar2(30),
 constraint_name varchar2(30),
 search_condition clob)
on commit preserve rows
/

insert into gtt_constraints (select owner, table_name, constraint_name, to_lob(search_condition) from dba_constraints)
/

commit
/

select count(*) from gtt_constraints
/

set linesize 132 pages 45 feedback off
col owner format a15
col table_name format a30
col search_condition format a40 word_wrap
col constraint_name1 format a15
col constraint_name2 format a15


select g1.owner, g1.table_name, g1.search_condition, g1.constraint_name constraint_name1, g2.constraint_name constraint_name1
from gtt_constraints g1,
   gtt_constraints g2
where g1.owner = g2.owner
 and g1.table_name = g2.table_name
 and to_char(g1.search_condition) = to_char(g2.search_condition)
 and g1.constraint_name != g2.constraint_name
/



and the output...

SQL> @get_dup_constraints
SQL> create global temporary table gtt_constraints
 2 ( owner varchar2(30),
 3   table_name varchar2(30),
 4   constraint_name varchar2(30),
 5   search_condition clob)
 6 on commit preserve rows
 7 /
SQL>
SQL> insert into gtt_constraints (select owner, table_name, constraint_name, to_lob(search_condition) from dba_constraints)
 2 /
SQL>
SQL> commit
 2 /
SQL>
SQL> select count(*) from gtt_constraints
 2 /

 COUNT(*)
----------
    2970
SQL>
SQL> set linesize 132 pages 45 feedback off
SQL> col owner format a15
SQL> col table_name format a30
SQL> col search_condition format a40 word_wrap
SQL> col constraint_name1 format a15
SQL> col constraint_name2 format a15
SQL>
SQL>
SQL> select g1.owner, g1.table_name, g1.search_condition, g1.constraint_name constraint_name1, g2.constraint_name constraint_name1
 2 from gtt_constraints g1,
 3     gtt_constraints g2
 4 where g1.owner = g2.owner
 5   and g1.table_name = g2.table_name
 6   and to_char(g1.search_condition) = to_char(g2.search_condition)
 7   and g1.constraint_name != g2.constraint_name
 8 /

OWNER       TABLE_NAME              SEARCH_CONDITION                 CONSTRAINT_NAME CONSTRAINT_NAME
--------------- ------------------------------ ---------------------------------------- --------------- ---------------
SYSTEM       DEF$_PUSHED_TRANSACTIONS     disabled IN ('T', 'F')             SYS_C001325   SYS_C00761
SYSTEM       DEF$_PUSHED_TRANSACTIONS     disabled IN ('T', 'F')             SYS_C00761    SYS_C001325




Barbara Baker wrote:
> Hi, all.
> 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? Perhaps I'm
> overlooking something simple. Thanks for any help.
> Barb
>
>
> SYSTEM:ENT>select a.constraint_name,
>  2      b.constraint_name,
>  3      a.table_name,
>  4      a.search_condition,
>  5      b.search_condition
>  6 from dba_constraints a,
>  7     dba_constraints b
>  8 where a.table_name = b.table_name
>  9   and a.search_condition=b.search_condition
> 10   and a.table_name = 'ACTUALPAGES'
> 11 /
>  and a.search_condition=b.search_condition
>     *
> ERROR at line 9:
> ORA-00997: illegal use of LONG datatype
>
>
>
>
>>select constraint_name, constraint_type,
>
> search_condition, generated f
> rom user_constraints where table_name='ACTUALPAGES';
>
>  Constraint         Search
>    Name    C      Condition      GENERATED
> -------------- - -------------------------
> --------------
> SYS_C0010088  C "PAPER" IS NOT NULL     USER NAME
> SYS_C0010089  C "PDATE" IS NOT NULL     USER NAME
> SYS_C0013708  C "PAPER" IS NOT NULL     GENERATED
> NAME
> SYS_C0013709  C "PDATE" IS NOT NULL     GENERATED
> NAME
>          
>
>
>
>  
>    
> __________________________________
> Do you Yahoo!?
> Friends. Fun. Try the all-new Yahoo! Messenger.
> http://messenger.yahoo.com/
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------