Shared Lock 10g x Lock shared 11g

[diegoleite]

Environment information:
* Oracle version: 10g and 11g
* Operating system: Oracle Enterprise Linux 4.5 / 5


Maybe this is Any 11g bug or something that does not make sense until
prove to me on the contrary. Could you help me ??



was making an Oracle lock test repair the Oracle 10G as
behaves.

Note:
When issuing the DML (Update) command in SESSAO1 and do not quit
Transaction (Commit;).
já SESSAO2 The attempt to issue Cum DDL (ALTER TABLE) I received the following

Select all

"ORA-00054: o recurso está 
 
ocupado e é obtido com o NOWAIT especificado" 

due to the lock in the table. (RIGHT).



was doing an Oracle lock test repair the Oracle 11g as
behaves.

Observe:
The same process done in No 11G, and was seen that it does not lock the table
table and makes change with the DDL command ALTER TABLE, already with
DDL DROP TABLE it emits the following message:

Select all

"ORA-00054: o recurso 
está ocupado e é obtido com o NOWAIT especificado ou o timeout expirou."

This process occurs with SESSOO1 open.



Session 1

Select all

SQL> SHOW USER 
USER é SESSAO1 
 
SQL> SELECT * FROM V$VERSION; 
 
BANNER 
---------------------------------------------------------------- 
Oracle Database 10g Release 10.2.0.1.0 - Production 
PL/SQL Release 10.2.0.1.0 - Production 
CORE    10.2.0.1.0      Production 
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production 
NLSRTL Version 10.2.0.1.0 - Production 
 
 
 
SQL> create table teste as select * from dual; 
 
Tabela criada. 
 
 
SQL> insert into teste values('1'); 
 
1 linha criada. 
 
SQL> commit; 
 
Commit concluído. 
 
SQL> update teste set DUMMY='2'; 
 
2 linhas atualizadas.

[99]]]

Select all

PROMPT============= SESSAO 2 ORACLE 10G ======================

Sessao 2

Select all

SQL> SHOW USER 
 USER é SESSAO2 
 
SQL> SELECT * FROM V$VERSION; 
 
BANNER 
---------------------------------------------------------------- 
Oracle Database 10g Release 10.2.0.1.0 - Production 
PL/SQL Release 10.2.0.1.0 - Production 
CORE    10.2.0.1.0      Production 
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production 
NLSRTL Version 10.2.0.1.0 - Production 
 
 
 
SQL> select * from teste; 
 
D 
- 
X 
1 
 
SQL> alter table teste add c date; 
alter table teste add c date 
* 
ERRO na linha 1: 
ORA-00054: o recurso está ocupado e é obtido com o NOWAIT especificado

[99]

============================= ======================================================== ================ =============

Select all

PROMPT============= SESSAO 1 ORACLE 11G ======================

Select all

SQL> SHOW USER 
USER é SESSAO1 
 
SQL> select * from v$version; 
 
BANNER 
-------------------------------------------------------------------------------- 
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production 
PL/SQL Release 11.1.0.6.0 - Production 
CORE    11.1.0.6.0      Production 
TNS for Linux: Version 11.1.0.6.0 - Production 
NLSRTL Version 11.1.0.6.0 - Production 
 
 
SQL> create table teste as select * from dual; 
 
Tabela criada. 
 
SQL> insert into teste values('1'); 
 
1 linha criada. 
 
SQL> commit; 
 
Commit concluído. 
 
SQL> update teste set DUMMY='2'; 
 
2 linhas atualizadas.

]]

Select all

PROMPT============= SESSAO 2 ORACLE 11G ======================

Select all

SQL> SHOW USER 
USER é SESSAO2 
 
SQL> select * from v$version; 
 
BANNER 
-------------------------------------------------------------------------------- 
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production 
PL/SQL Release 11.1.0.6.0 - Production 
CORE    11.1.0.6.0      Production 
TNS for Linux: Version 11.1.0.6.0 - Production 
NLSRTL Version 11.1.0.6.0 - Production 
 
SQL> select * from teste; 
 
D 
- 
X 
1 
 
SQL> alter table teste add c date; 
 
Tabela alterada. 
 
 
SQL> DROP TABLE TESTE; 
DROP TABLE TESTE 
          * 
ERRO na linha 1: 
ORA-00054: o recurso está ocupado e é obtido com o NOWAIT especificado ou o 
timeout expirou

[diegoleite]

Strange a forum that I always participated actively .. Do you position yourself ?? Not an idea ?? Whatever it is !!!

[rogenaro]

Would not it be due to the new functionality [url=http://www.oracle.com/technology/pub/ar ... ement.html]DDL Wait of 11g? The ddl_lock_timeout parameter was set in your session?

As I do not have this version here you can not see how this molescence behaves, but apparently explains what must have occurred with the ALTER TABLE command.

However, I do not understand why it did not occur with the drop table (judging by the documentation, this option would be valid for any DDL command ...) The error was related to the Timeout of the ALTER TABLE command?

Why otherwise the behavior would really be "half" strange ...