Re: No rollback

On Jul 21, 4:37 am, "astalavista" <nob..._at_nowhere.com> wrote:
> > Not able to look over your shoulder and read your code I haven't the
> > slightest idea in the world. Perhaps your code contains the keyword
> > COMMIT. Or perhaps you didn't kill the session, but rather killed the
> > client and the procedure completed. Who can say?
>
> > Next post include enough information that someone can help you.
>
> Thanks, th code was
> 1 begin
> 2 for i in 1..10000000
> 3 loop
> 4 insert into big values (i, 'titi');
> 5 end loop;
> 6* end;
> SQL> /
>
> no commit at all
> the session was killed

Here is a short example that may explain what you are seeing: Create a simple table:
CREATE TABLE T1 (MY_DATE DATE); Table created.

Activate a 10046 trace for the session:
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8'; Insert 99,999 rows into the table.
INSERT INTO
  T1
SELECT
  TRUNC(SYSDATE)+ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<100000;

99999 rows created.

Close SQL*Plus:
EXIT Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

(Reconnect to Oracle)

Let's see how many rows are in the table: SELECT
  COUNT(*)
FROM
  T1;

  COUNT(*)

     99999

Taking a look at the last two lines of the 10046 trace file: WAIT #0: nam='SQL*Net message from client' ela= 5831875 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2429307593 XCTEND rlbk=0, rd_only=0

The last line indicates that a COMMIT was performed automatically when SQL*Plus closed. This is the default behavior for SQL*Plus.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Jul 21 2007 - 06:13:57 CDT