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: ORA-600 Deadlock Issues

Karthik

2005-06-27

Replies:
Tom,

I used the following script to identify FK columns without indexes.

Thanks, Karthik


rem
-----------------------------------------------------------------------
rem  Shows the foreign keys without appropiate index
rem
-----------------------------------------------------------------------
rem
SET echo off
SET verify off
--
COLUMN OWNER noprint new_value own
COLUMN TABLE_NAME format a24 wrap heading "Table Name"
COLUMN CONSTRAINT_NAME format a24 wrap heading "Constraint Name"
COLUMN CONSTRAINT_TYPE format a3 heading "Typ"
COLUMN COLUMN_NAME format a24 wrap heading "1. Column"
BREAK ON OWNER skip page
--
SET TERMOUT ON
TTITLE CENTER 'Unindexed Foreign Keys owned by Owner: ' own SKIP 2
PROMPT
PROMPT Please enter Owner Name and Table Name. Wildcards allowed
(DEFAULT: %)
PROMPT
PROMPT eg.: SCOTT, S% OR %
PROMPT eg.: EMP, E% OR %
PROMPT
--
ACCEPT vOwner prompt "Owner <%>: " DEFAULT %
ACCEPT vTable prompt "Tables <%>: " DEFAULT %
--
SELECT OWNER, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
 FROM DBA_CONS_COLUMNS c
 WHERE position=1 AND
  (OWNER, TABLE_NAME, COLUMN_NAME) IN
  (SELECT c.OWNER, c.TABLE_NAME,cc.COLUMN_NAME
    FROM DBA_CONSTRAINTS c, DBA_CONS_COLUMNS cc
   WHERE c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
    AND c.TABLE_NAME    = cc.TABLE_NAME
    AND c.OWNER       = cc.OWNER
    AND c.CONSTRAINT_TYPE = 'R'
    AND cc.POSITION     = 1
    AND c.OWNER       LIKE UPPER('&vOwner')
    AND c.TABLE_NAME    LIKE UPPER('&vTable')
   MINUS
  SELECT table_owner, table_name, column_name
    FROM DBA_IND_COLUMNS
   WHERE COLUMN_POSITION = 1
    AND TABLE_OWNER LIKE UPPER('&vOwner')
    AND TABLE_NAME LIKE UPPER('&vTable')
 )
 ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME;
--
ttitle off
SET pause off
COLUMN TABLE_NAME clear
COLUMN CONSTRAINT_NAME clear
COLUMN CONSTRAINT_TYPE clear
COLUMN COLUMN_NAME clear
clear breaks



On 27-Jun-05, at 5:18 PM, Mercadante, Thomas F (LABOR) wrote:

> Karthik,
>
> As you have figured out, the largest cause of Deadlock problems are bad
> coding and missing indexes to support foreign keys. Below is a sql to
> help you find tables with foreign keys but no indexes to support them.
> The query is not 100% bullet proof, but it is a start.
>
> I use this for Curam applications as they do not believe in supplying
> indexes for foreign keys (their official response is to drop the FK's -
> nice, eh?).
>
> Good Luck!
>
> SELECT 'create index ' || substr(ut.table_name,1,20) ||
> ROUND(sys.dbms_random.value*100)||'IDX ' ||
> 'ON ' || ut.table_name || ' (' || ucc.column_name || ') ' ||
> 'TABLESPACE ' || ut.TABLESPACE_NAME||'PK pctfree 10' ddl_string
> FROM USER_TABLES ut, USER_CONSTRAINTS uc, USER_CONS_COLUMNS ucc
> WHERE uc.constraint_type='R'
> AND ucc.constraint_name = uc.constraint_name
> AND ut.table_name = uc.table_name
> AND NVL(position,1) = 1
> AND NOT EXISTS(SELECT 1 FROM USER_IND_COLUMNS uic
> WHERE uic.table_name=ucc.table_name
> AND  ucc.column_name = uic.column_name
> AND  uic.column_position=1)
> ORDER BY 1;
>
> Tom

--
http://www.freelists.org/webpage/oracle-l