| Sequence no longer exists.... [message #217091] |
Wed, 31 January 2007 11:38  |
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 #217093 is a reply to message #217091] |
Wed, 31 January 2007 11:52  |
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 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
