Is COMMIT=ROLLBACK ???

[kgb]

Hi, guys after upgrading Oracle (RedHat 6.2) 8.1.7.0.1 to 8.1.7.3 we have got very strange problem: when I specify commit rollback is occured, you can see bellow.

SVRMGR>
select to_char(nextattempt, 'yyyy-mm-dd HH24:Mi:ss') from scheduledcalls where schedule_id = 3;

SVRMGR> TO_CHAR(NEXTATTEMPT 
------------------- 
2003-04-02 16:01:10 
1 row selected.

SVRMGR> update scheduledcalls set nextattempt = nextattempt + (retryinterval/86400) where schedule_id = 3;

SVRMGR> 1 row processed.

SVRMGR> select to_char(nextattempt, 'yyyy-mm-dd HH24:Mi:ss') from scheduledcalls where schedule_id = 3;
TO_CHAR(NEXTATTEMPT 
------------------- 
2003-04-02 16:01:25 
1 row selected.

SVRMGR> commit;
Statement processed.

SVRMGR> select to_char(nextattempt, 'yyyy-mm-dd HH24:Mi:ss') from scheduledcalls where schedule_id = 3;
TO_CHAR(NEXTATTEMPT 
------------------- 
2003-04-02 16:01:10 
1 row selected.

There is no job running against of this table, there is no trigger on this table.
The same thing is occured for any column on this table. I created a new table and commit worked correctly on it.
Any ideas about it are very welcome.
Regards,

[abhaysk]

select TRIGGER_NAME from dba_triggers where TABLE_NAME=upper('scheduledcalls');

Result??

[kgb]

Originally posted by abhaysk
select TRIGGER_NAME from dba_triggers where TABLE_NAME=upper('scheduledcalls');
Result??

Result is 0 rows returned.

There is no job running against of this table, there is no trigger on this table, there is no other session which might reset values.
Even if there would be a trigger, it could not make such result.

[JMac]

Why does it not say 'Commit complete'?

'Statement processed' implies something other than a commit is happening. Is there any sort of environment setting or parameter that can switch the explicit commit off in SQL*Plus? So that the statement is processed but no commit takes place?

Try some DDL after the UPDATE and see if that commits?

Anyone?

[kgb]

This is how SERVER MANAGER works, it says that 'Statement processed.'.
SQL/Plus rolls back modification as well after 'commit' specified.
Tried to make DDL, result is the same (modifications was rolled back) as I would specify 'commit'.
Thanks

[abhaysk]

Try writing PL/SQl...

Insert some values into the table and commit;

Try capturing the errors if any..in exception part..

Lest see if atleast here errors are captured.


also jus give a try with auto commit on


Abhay.

[jovery]

Is this occuring on every table or just the one in your example? Also are you sure you have no other process (prehaps an external job) which could be reseting the value?

It might be worth adding a very simple trigger to the table (or activate auditing) to see when update occur.

Regards

[kgb]

yes, good ideas.
I'll try, thanks