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: 10.1.0.4 patchset 2 catcpu.sql slop

Igor Neyman

2005-06-28

Replies:
In order to deal with this kind of problems ("name is already used" or
"object doesn't exist")
I wrote a small function, which checks the existance of the the object:

CREATE OR REPLACE FUNCTION object_exists (
    object_type VARCHAR2,
    object1_name VARCHAR2 DEFAULT NULL,
    object2_name VARCHAR2 DEFAULT NULL)
RETURN BOOLEAN
AS
lCounter INTEGER := 0;
lRet BOOLEAN := FALSE;
BEGIN
IF (UPPER(object_type) = 'TRIGGER') THEN
 SELECT COUNT(*) INTO lCounter
 FROM user_triggers
 WHERE trigger_name = UPPER(object1_name);
END IF;
IF (UPPER(object_type) = 'TABLE') THEN
 SELECT COUNT(*) INTO lCounter
 FROM user_tables
 WHERE table_name = UPPER(object1_name);
END IF;
IF (UPPER(object_type) = 'COLUMN') THEN
 SELECT COUNT(*) INTO lCounter
 FROM user_tab_columns
 WHERE table_name = UPPER(object1_name)
      AND column_name = UPPER(object2_name);
END IF;
IF (UPPER(object_type) = 'INDEX') THEN
 SELECT COUNT(*) INTO lCounter
 FROM user_indexes
 WHERE index_name = UPPER(object1_name);
END IF;
IF (UPPER(object_type) = 'INDEX COLUMN') THEN
 SELECT COUNT(*) INTO lCounter
 FROM user_ind_columns
 WHERE table_name = UPPER(object1_name)
      AND column_name = UPPER(object2_name);
END IF;
IF (lCounter = 1) THEN
 lRet := TRUE;
END IF;
RETURN lRet;
END;
/

And in my upgrade scripts when I need to add/remove/modify
table/column/index I use this function along with dynamic sql:

REM Remove column HEART_BEAT from GP_ROBOT
begin
IF (object_exists('COLUMN','GP_ROBOT', 'HEART_BEAT')) THEN
 EXECUTE IMMEDIATE 'alter table GP_ROBOT drop column HEART_BEAT';
END IF;
end;
/

Or

REM Add column CHART_WIDTH to GP_STATUS_CHART
begin
IF (object_exists('COLUMN','GP_STATUS_CHART', 'CHART_WIDTH') = FALSE) THEN
 EXECUTE IMMEDIATE 'alter table GP_STATUS_CHART add CHART_WIDTH
INTEGER NULL';
END IF;
end;
/

Or

REM GP_VF_SYMMETRY_DISPLAY
begin
IF (object_exists('TABLE','GP_VF_SYMMETRY_DISPLAY') = FALSE) THEN
 EXECUTE IMMEDIATE 'create table GP_VF_SYMMETRY_DISPLAY (
 VISUAL_FIXTURE_ID   INTEGER                 not null,
 FIRST_COMPONENT_ID  INTEGER                 not null,
 SECOND_COMPONENT_ID INTEGER                 not null,
 PHYSICAL_REPRESENTATION INTEGER,
 constraint PK_VF_SYMMETRY_DISPLAY primary key (VISUAL_FIXTURE_ID,
FIRST_COMPONENT_ID, SECOND_COMPONENT_ID)
     using index
    pctfree 3
    initrans 3
    maxtrans 20
    tablespace AFS_TS,
 constraint FK_VF_SYMMETRY__VISUAL_FIXTU foreign key (VISUAL_FIXTURE_ID)
     references GP_VISUAL_FIXTURE (VISUAL_FIXTURE_ID)
     on delete cascade
)
pctfree 10
pctused 85
initrans 3
maxtrans 20
storage
(
  minextents 1
  maxextents unlimited
)
tablespace AFS_TS';
End;
/

As for views and synonyms, I always use: "create or replace ..."
Why can't Oracle do something like this in their scripts?

Igor

-----Original Message-----
From: oracle-l-bounce@(protected)]
On Behalf Of Paul Drake
Sent: Tuesday, June 28, 2005 3:35 PM
To: Oracle-L (oracle-l@(protected))
Subject: 10.1.0.4 patchset 2 catcpu.sql slop

w2k3 server std ed, 32 bit.
Oracle 10g R1, 10.1.0 patchset 3 applied (10.1.0.4)

While running the 10.1.0.4 patchset 2 post-installation script
(catcpu.sql) against a newly created database there were a rather large
amount of ORA-00955 (457) and ORA-02303 (63) errors.

These can sometimes be ignored, but basically, this means having to compare
the previous definitions of the objects against the new definition on a line
by line basis in order to determine if in fact the object definition has
changed. This is a huge waste of time, IMHO.

I was thinking of rather than taking that approach, of manually dropping all
of these objects prior to re-running catcpu.sql and examining the results
(HUGE hammer approach).

this is in testing in a db with no users.

yeah, I could just ignore the patchset and stick with 10.1.0.4 patchset 1.
we've hit an issue covered by 10.1.0.4 patchset 2 so I'd rather get the
bugfix out there than wait for the next iteration.

just trolling for some thoughts before I vent into an iTAR.

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


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