请问如何解决 seq.nextval 返回多个值 increment by 1

显示全部楼层 · 2005-2-28 12:57:00

一个SEQ.NEXTVALUE 会还回两个值，造成过程中调用
SELECT SEQ.NEXTVALUE INTO V_SEQ FROM DUAL;
都EXCEPTION
有什么好建议？

千问 · 2005-2-28 12:57:00

执行
select * from dual
看看，正常是应该只有一条记录，你的肯定是两条，那一条是怎么多出来的？那就要问你自己了，呵呵。。。

千问 · 2005-2-28 12:57:00

下回把错误信息贴出来

千问 · 2005-2-28 12:57:00

SELECT SEQ.NEXTVALUE INTO V_SEQ FROM DUAL;
是nextval吧？
检查一下dual中有几条记录

千问 · 2005-2-28 12:57:00

tar id 15581092.6
1* create table test_table (a number(10))
SQL> /

Table created.

SQL> insert into wuzhi_test_table values (test1.nextval);

1 row created.

SQL> select * from wuzhi_test_table ;

A
----------
21

SQL> commit;

Commit complete.

SQL> select test1.nextval from dual;

NEXTVAL
----------
22
23

SQL> select * from all_objects where object_name like '%DUAL%';

OWNER
OBJECT_NAME
OBJECT_ID
------------------------------ ------------------------------ ----------
OBJECT_TYPECREATED
LAST_DDL_TIME
------------ ------------------ ------------------
TIMESTAMP
---------------------------------------------------------------------------
STATUS
-------
SYS
DUAL
141
TABLE27-SEP-03
27-SEP-03
2003-09-27:13:36:24
VALID

OWNER
OBJECT_NAME
OBJECT_ID
------------------------------ ------------------------------ ----------
OBJECT_TYPECREATED
LAST_DDL_TIME
------------ ------------------ ------------------
TIMESTAMP
---------------------------------------------------------------------------
STATUS
-------
PUBLIC
DUAL
142
SYNONYM27-SEP-03
27-SEP-03
2003-09-27:13:36:24
VALID

SQL> SELECT * FROM SYS.DUAL;

D
-
X
Y

SQL> SELECT * FROM PUBLIC.DUAL;
SELECT * FROM PUBLIC.DUAL

*
ERROR at line 1:
ORA-00903: invalid table name

SQL> select * from all_synonyms where synonym_name='DUAL';

千问 · 2005-2-28 12:57:00

最初由 yangtingkun 发布
[B]SELECT SEQ.NEXTVALUE INTO V_SEQ FROM DUAL;
是nextval吧？
检查一下dual中有几条记录 [/B]
呵呵，不好意思是NEXTVAL

tar id 15581092.6
1* create table test_table (a number(10))
SQL> /

Table created.

SQL> insert into test_table values (test1.nextval);

1 row created.

SQL> select * from wuzhi_test_table ;

A
----------
21

SQL> commit;

Commit complete.

SQL> select test1.nextval from dual;

NEXTVAL
----------
22
23

SQL> select * from all_objects where object_name like '%DUAL%';

OWNER
OBJECT_NAME
OBJECT_ID
------------------------------ ------------------------------ ----------
OBJECT_TYPECREATED
LAST_DDL_TIME
------------ ------------------ ------------------
TIMESTAMP
---------------------------------------------------------------------------
STATUS
-------
SYS
DUAL
141
TABLE27-SEP-03
27-SEP-03
2003-09-27:13:36:24
VALID

OWNER
OBJECT_NAME
OBJECT_ID
------------------------------ ------------------------------ ----------
OBJECT_TYPECREATED
LAST_DDL_TIME
------------ ------------------ ------------------
TIMESTAMP
---------------------------------------------------------------------------
STATUS
-------
PUBLIC
DUAL
142
SYNONYM27-SEP-03
27-SEP-03
2003-09-27:13:36:24
VALID

SQL> SELECT * FROM SYS.DUAL;

D
-
X
Y

SQL> SELECT * FROM PUBLIC.DUAL;
SELECT * FROM PUBLIC.DUAL

*
ERROR at line 1:
ORA-00903: invalid table name

SQL> select * from all_synonyms where synonym_name='DUAL';

千问 · 2005-2-28 12:57:00

DELETE SYS.DUAL WHERE DUMMY = 'Y';
COMMIT;

千问 · 2005-2-28 12:57:00

SYS.DUAL为ORACLE内存表，可以直接删吗？
这个＝Y的记录是什么产生进去的？
今天才有，过去都很正常啊？

千问 · 2005-2-28 12:57:00

重跑一遍数据字典可否恢复