Hi,
Just hoping someone can clear something up for me.
This is from the [Oracle documentation|http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_1001.htm#i2099120]:
Oracle Database implicitly commits the current transaction before and after every DDL statement.
I start two sessions, S1 and S2.
In S1, I create two tables:
USER@DB > create table t1
2 (col1 number(1));
Tabla creada.
USER@DB > create table t2
2 (col1 number(1));
Tabla creada.
Now S1 inserts a row into T1:
USER@DB > insert into t1 values (1);
1 fila creada.
and S2 cannot see that row:
USER@DB > select * from t1;
ninguna fila seleccionada
Now S1 performs some DDL (ALTER on table t2) that will fail:
USER@DB > alter table t2 add (col2 number(35639456426));
alter table t2 add (col2 number(35639456426))
*
ERROR en línea 1:
ORA-01727: el especificador de precisión numérica está fuera de rango (1 a 38)
But S2 still does not see the insert that S1 did:
USER@DB > select * from t1;
ninguna fila seleccionada
*- So the COMMIT was not issued
before the DDL in S1?*
Of course, if the DDL in S1 succeeds:
USER@DB > alter table t2 add (col2 number(3));
Tabla modificada.
Now S2 sees the row (so the COMMIT has been issued in S1):
USER@DB > select * from t1;
COL1
----------
1
So, my question: *If DDL performs a commit
before and
after (hence if the DDL
fails or
succeeds) shouldn't S2 see the row that S1 had inserted, immediately after the
failed DDL statement in S1?*
Regards,
Ados