Problem in accessing sequence [message #420887] |
Thu, 03 September 2009 01:16  |
 |
delna.sexy
Messages: 941 Registered: December 2008 Location: Surat, The Diamond City
|
Senior Member |
|
|
Hello all,
I have some doubt about accessing NEXTVAL of SEQUENCE.
As I have read from all sources, USER views gives list of all the objects owned by that user. While ALL views gives list of all objects on which user has rights to access.
And according to that, I should not get the error at last in following code.
SQL>select sequence_name from user_sequences;
SEQUENCE_NAME
------------------------------
SEQ
1 row selected.
SQL>select seq.nextval from dual;
NEXTVAL
----------
67080442
1 row selected.
SQL>select sequence_name from all_sequences;
SEQUENCE_NAME
------------------------------
SCHEDULER$_JOBSUFFIX_S
DM$EXPIMP_ID_SEQ
HS_BULK_SEQ
XDB$NAMESUFF_SEQ
SDO_WS_CONFERENCE_IDS
DBMS_MVSEQ
SDO_GEOR_SEQ
SDO_IDX_TAB_SEQUENCE
TMP_COORD_OPS
SAMPLE_SEQ
WWV_FLOW_SESSION_SEQ
WWV_SEQ
WWV_FLOW_SESSION_SEQ
WWV_SEQ
RMAN_SEQ
SEQ
16 rows selected.
SQL>select sample_seq.nextval from dual;
select sample_seq.nextval from dual
*
ERROR at line 1:
ORA-02289: sequence does not exist
Although I have access rights on all above sequences, last select statement should not raise error.
Please correct me, wherever I am wrong.
regards,
Delna
|
|
|
|
|
Re: Problem in accessing sequence [message #420893 is a reply to message #420891] |
Thu, 03 September 2009 02:17   |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
Quote: | But that is not in case of TABLE.
What can be the reason?
|
Not always
XE@SQL> sho user
USER is "OPS$RANIYENG\ADMINISTRATOR"
XE@SQL> select table_name,owner from all_tables where table_name in ('MAP_OBJECT
','HELP');
TABLE_NAME OWNER
------------------------------ ------------------------------
MAP_OBJECT SYS
HELP SYSTEM
2 rows selected.
XE@SQL> select count(*) from HELP;
select count(*) from HELP
*
ERROR at line 1:
ORA-00942: table or view does not exist
XE@SQL> select count(*) from system.HELP;
COUNT(*)
----------
980
1 row selected.
XE@SQL> select count(*) from map_object;
COUNT(*)
----------
0
1 row selected.
XE@SQL>
XE@SQL> select synonym_name,owner,table_name from all_synonyms where table_name
in ('MAP_OBJECT','HELP')
2 /
SYNONYM_NAME OWNER TABLE_NAME
-------------------- ---------- ----------
MAP_OBJECT PUBLIC MAP_OBJECT
1 row selected.
XE@SQL>
1 row selected.
XE@SQL>
1 row selected.
XE@SQL>
It works for some because of Public synonym.
[Updated on: Thu, 03 September 2009 02:23] Report message to a moderator
|
|
|
|
|