Home » SQL & PL/SQL » SQL & PL/SQL » Sequence no longer exists....
Sequence no longer exists.... [message #217091] Wed, 31 January 2007 11:38 Go to next message
stardust
Messages: 11
Registered: January 2007
Location: Cambridge, UK
Junior Member
I am experiencing this problem at the moment:

The story goes like this:
1. I have a big sql script (10000+ lines) for creating Users(schemas) and tables
2. I run this script in SQL*Plus command prompt (ie: NOT SQL Worksheet)
3. After the script finished I checked the spool file and found the following error:
old   1: INSERT INTO &HKDB_Schema_Name..HKCOLUMNTYPE (COLUMNTYPENAME) VALUES ('PK')
new   1: INSERT INTO PL1DB.HKCOLUMNTYPE (COLUMNTYPENAME) VALUES ('PK')
INSERT INTO PL1DB.HKCOLUMNTYPE (COLUMNTYPENAME) VALUES ('PK')
ERROR at line 1:
ORA-04016: sequence HKCOLUMNTYPE_ID_SEQ no longer exists 
ORA-06512: at "PL1DB.HKCOLUMNTYPE_ID_TRIG", line 2 
ORA-04088: error during execution of trigger 'PL1DB.HKCOLUMNTYPE_ID_TRIG'


However, just a few lines above I have:
old   1: CREATE SEQUENCE &HKDB_Schema_Name.."HKCOLUMNTYPE_ID_SEQ" INCREMENT BY 1 START
new   1: CREATE SEQUENCE PL1DB."HKCOLUMNTYPE_ID_SEQ" INCREMENT BY 1 START

Sequence created.

Commit complete.



I have tried manually run the above command, dropping the sequence then recreated it, commited the changes to the database and ran the queries and I still have the same problem.

I also checked that the sequence is listed in the list or sequence in the Enterprise Management Console, but when I use the test query given by Balaji I got the same error message.

I am using Oracle 9i R2 Production (9.2.0.1.0).

Thank you very much.
Re: Sequence no longer exists.... [message #217092 is a reply to message #217091] Wed, 31 January 2007 11:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
who are you logged in as at the time?
SQL> show user
displays what?
Re: Sequence no longer exists.... [message #217093 is a reply to message #217091] Wed, 31 January 2007 11:52 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You might have some peculiar situation like the trigger on the table is owned by a different user from PL1DB and that user can't see the sequence. As a short term measure, try creating a public synonym for the sequence and giving select access to public on it.

[Updated on: Wed, 31 January 2007 11:52]

Report message to a moderator

Re: Sequence no longer exists.... [message #217280 is a reply to message #217093] Thu, 01 February 2007 08:03 Go to previous messageGo to next message
stardust
Messages: 11
Registered: January 2007
Location: Cambridge, UK
Junior Member
Hi,

At the time of running the script I am logged in as sys:
SQL> show user
USER is "SYS"


Then I tried this:
SQL> show user
USER is "SYS"
SQL> select Owner, Object_Name, Object_Type, Status FROM ALL_OBJECTS
  2  WHERE Owner = 'PL1DB' AND
  3  OBJECT_NAME = 'HKCOLUMNTYPE_ID_SEQ";
ERROR:
ORA-01756: quoted string not properly terminated


SQL> SELECT Owner, Object_Name, Object_Type, Status FROM ALL_OBJECTS
  2  WHERE Owner = 'PL1DB' AND
  3  OBJECT_NAME = 'HKCOLUMNTYPE_ID_SEQ';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------
STATUS
-------
PL1DB                          HKCOLUMNTYPE_ID_SEQ            SEQUENCE
VALID


SQL> SELECT Owner, Object_Name, Object_Type, Status FROM ALL_OBJECTS
  2  WHERE Owner = 'PL1DB' AND
  3  OBJECT_NAME = 'HKCOLUMNTYPE_ID_TRIG';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------
STATUS
-------
PL1DB                          HKCOLUMNTYPE_ID_TRIG           TRIGGER
VALID


SQL> SELECT PL1DB.HKColumnType_ID_SEQ.NextVal FROM DUAL;
SELECT PL1DB.HKColumnType_ID_SEQ.NextVal FROM DUAL
             *
ERROR at line 1:
ORA-04016: sequence HKCOLUMNTYPE_ID_SEQ no longer exists

SQL> insert into PL1DB.HKColumnType(ColumnTypeName) values('PK');
insert into PL1DB.HKColumnType(ColumnTypeName) values('PK')
                  *
ERROR at line 1:
ORA-04016: sequence HKCOLUMNTYPE_ID_SEQ no longer exists
ORA-06512: at "PL1DB.HKCOLUMNTYPE_ID_TRIG", line 2
ORA-04088: error during execution of trigger 'PL1DB.HKCOLUMNTYPE_ID_TRIG'


I then using the Enterprise Mangement Console and successfully insert the same row as I tried above to the HKColumnType table. So is that mean there is something wrong with my session with the command prompt SQL*Plus?
Re: Sequence no longer exists.... [message #217286 is a reply to message #217091] Thu, 01 February 2007 08:37 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It's not the normal message for a missing sequence (ORA-2289). I had a look on MetaLink and there do seem to be a few bugs in this area e.g.

Bug No. 2217015 
Filed 07-FEB-2002 Updated 27-AUG-2003 
Product Oracle Server - Enterprise Edition Product Version  8.1.7.0 
Platform Solaris Operating System (SPARC 32-bit) Platform Version 2.6 
Database Version 8.1.7.0 Affects Platforms  Generic 
Severity  Severe Loss of Service Status Development to Q/A 
Base Bug N/A Fixed in Product Version 10.0 

Problem statement:

ORA-4016 OCCURS WHEN USING SEQUENCE 


 If you get ORA-4016 when accessing recreated sequence after either drop user  cascade or cleaning up obj$ by SMON, you are probably hitting this bug. ]] ORA-4016 occured when accessing recreated sequence after either drop user  ]] cascade or cleaning up obj$ by SMON. 


It looks as if you might have hit something like this.
Previous Topic: TimeZone
Next Topic: difference between timestamp
Goto Forum:
  


Current Time: Sat Dec 07 05:27:00 CST 2024