ORA-02205

Hallé unas notas de un curso que tomé en 1990. Decían que solamente era posible hacer un GRANT SELECT o un GRANT ALTER con una secuencia. Esto me parecío razonable pero quería verificar si fuese todavía el caso. Hice esta prueba en Oracle 12.1. Para empezar, creé un usuario que sería el dueño de una secuencia:

SQL> create user u1 identified by pw1

  2  /

 

Usuario creado.

 

SQL> grant create session, create sequence to u1

  2  /

 

Concesión terminada correctamente.

SQL>

Entonces creé un usuario para recibir los derechos en la secuencia: 

SQL> create user u2 identified by pw2

  2  /

 

Usuario creado.

SQL>

El primer usuario creó una secuencia y luego hizo un GRANT ALL al segundo usuario: 

SQL> conn u1/pw1

Conectado.

SQL> create sequence s1

  2  /

Secuencia creada.

SQL> grant all on s1 to u2

  2  /

Concesión terminada correctamente.

SQL>

Luego busqué los derechos que habían sido dado al segundo usuario. Solamente hallé un ALTER y un SELECT. Esto confirmó lo que había leído en mis notas:

SQL> select privilege from all_tab_privs

  2  where grantor = 'U1'

  3  and grantee = 'U2'

  4  and table_name = 'S1'

  5  /

PRIVILEGE                                             

----------------------------------------              

ALTER                                                 

SELECT

SQL> 

Por fin intenté a hacer un GRANT UPDATE en la secuencia pero recibí un error ORA-02205:

SQL> grant update on s1 to u2

  2  /

grant update on s1 to u2

                *

ERROR en línea 1:

ORA-02205: sólo los privilegios SELECT y ALTER son

válidos para secuencias

 

SQL>

En inglés

sqlnet.ora

Hallé la idea para este ejemplo aquí y decidí hacer una traducción para los hispanohablantes.

Cada vez que alguien crea una sesión nueva, Oracle lee el archivo sqlnet.ora. Por eso, no es necesario parar y empezar el listener después de cambiar este archivo. Aquí está un ejemplo para mostrar lo que quiero decir. Cambié el archivo sqlnet.ora en un servidor UNIX a las dos menos cinco: 

NLGENUT1 /var/opt/oracle > ls -l sqlnet.ora

-rw-r--r--   1 oracle   dba           24 Oct  8 13:55 sqlnet.ora

NLGENUT1 /var/opt/oracle >

Me conecté a una de las bases de datos en el servidor UNIX desde mi ordenador con Windows a las dos menos tres: 

C:\Users\J0294094>sqlplus system@nlgenut1

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 8 13:57:37 2014

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

Enter password:

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> 

Confirmé que el archivo sqlnet.ora había sido accedido a la misma hora:

NLGENUT1 /var/opt/oracle > ls -lu sqlnet.ora

-rw-r--r--   1 oracle   dba           24 Oct  8 13:57 sqlnet.ora

NLGENUT1 /var/opt/oracle >

library cache lock

Hallé la idea para este ejemplo aquí y decidí hacer una traducción  para los hispanohablantes. 

Esto pasó en Oracle versión 11.1. Para empezar, noté el SID de la sesión roja bajo estas líneas. Se puede ver porque hice esto más tarde: 

SQL> l

  1* select distinct sid from v$mystat

SQL> /

 

       SID

----------

       155

SQL>

Luego intenté cambiar la definición de una columna pero nada pasaba:

SQL> alter session

  2  set ddl_lock_timeout = 3600

  3  /

 

Session altered.

 

SQL> alter table srce.pb_tolerance_charge

  2  modify (mg_abv null)

  3  /

Entonces, en la segunda sesión azul, verifiqué que la sesión roja esperaba y noté que la causa del retraso era un library cache lock:

SQL> l

  1  select wait_time, event

  2  from v$session

  3* where sid = 155

SQL> /

 

WAIT_TIME EVENT

---------- --------------------

         0 library cache lock

 

SQL>

Hice un reportaje del estado del sistema de la manera siguiente:

 

SQL> conn / as sysdba

Connected.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug unlimit

Statement processed.

SQL> oradebug dump systemstate 10

Statement processed.

SQL>

Hallé el reportaje en el directorio que contenía el archivo de alert y los otros archivos .trc:

PQECOG4 /oracle/app/oracle/product/diag/rdbms/pqecog4/PQECOG4/trace > ls -l PQECOG4_ora_1890.trc

-rw-r-----   1 oracle   dba      25412891 Jul 10 11:54 PQECOG4_ora_1890.trc

PQECOG4 /oracle/app/oracle/product/diag/rdbms/pqecog4/PQECOG4/trace >

Busqué las palabras library cache lock en el reportaje del estado del sistema y verifiqué que el SID era 155:

    (session) sid: 155 ser: 2275 trans: 0x3904dd858, creator: 0x3924f0450

              flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

              flags2: (0x4008) DDLT1/-

              DID: , short-term DID:

              txn branch: 0x0

              oct: 15, prv: 0, sql: 0x38b2bede8, psql: 0x387dca5d0, user: 74/ELEC_ORACLE

    client details:

      O/S info: user: oracle, term: pts/18, ospid: 18140

      machine: zge-mktred-ddb2 program: sqlplus@zge-mktred-ddb2 (TNS V1-V3)

      application name: SQL*Plus, hash value=3669949024

    Dumping Current Wait Stack:

     0: waiting for 'library cache lock'

        handle address=38b2557a8, lock address=38b04c6d8, 100*mode+namespace=12d

        wait_id=1683 seq_num=1684 snap_id=1

        wait times (usecs) - snap=185051570 exc=185051570 total=185051570

        wait times (usecs) - max=infinite

        wait counts (exc) - calls=65 os=65

        in_wait=1 iflags=0x15a2

    There is at least one session blocking this session.

    Dumping one blocker:

      inst: 1, sid: 192, ser: 6553

Vi que la sesión 192 bloqueaba la sesión 155 y por eso la maté:

SQL> alter system kill session '192,6553' immediate

  2  /

 

System altered.

 

SQL> 

… entonces el cambio de la definición de la columna se terminó: 

SQL> alter table srce.pb_tolerance_charge

  2  modify (mg_abv null)

  3  /

 

Table altered.

 

SQL>