miércoles, 10 de diciembre de 2014

Como Se Puede Crear Una Tabla Con Un Máximo De Una Fila

Probé este ejemplo en Oracle 12.1. Si quieres crear una tabla que no tendrá nunca más de una fila, se puede hacerlo de la manera siguiente:
 
SQL> create table tab1(col1 number)
  2  /
 
Tabla creada.
 
SQL> create unique index ind1 on tab1(1)
  2  /
 
Índice creado.
 
SQL> insert into tab1 values(1)
  2  /
 
1 fila creada.
 
SQL> insert into tab1 values(2)
  2  /
insert into tab1 values(2)
*
ERROR en línea 1:
ORA-00001: restricción única (SYS.IND1) violada
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         1 

SQL>

jueves, 20 de noviembre de 2014

¿Porqué Tuve Que Borrar Esta Tabla Dos Veces?

Hallé este ejemplo en un sitio inglés y decidí traducirlo para los hispanohablantes. Lo he probado en Oracle 9.2 y Oracle 11.2 lo que quiere decir que la versión no sea demasiado importante. Un usuario que se llama Fred se conecta a una base de datos: 

SQL> conn fred/bloggs
Connected.
SQL>

Tiene solamente una sesión y no hay nadie más en la base de datos:

SQL> select username from v$session
  2  where username is not null
  3  /
 
USERNAME
------------------------------
FRED

SQL>

... y su usuario es vacio:

SQL> select count(*) from user_objects
  2  /
 
  COUNT(*)
----------
         0
 
SQL>

Fred crea una tabla, ejecuta unos comandos de SQL y intenta a borrar la tabla. Cuando lo hace por primera vez, ve dos errores que no te he mostrado:

SQL> drop table tab1
  2  /
drop table tab1
*
ERROR at line 1:
ORA-?????
ORA-?????
 
SQL>

Cuando lo hace por segunda vez, no hay problemas:

SQL> drop table tab1
  2  /
 
Table dropped.
 
SQL>

Yo necesito saber:
  1.  ¿Cómo fue creada la tabla?
  2. ¿Cuáles fueron los comandos de SQL?
  3. ¿Cuáles errores ha visto Fred?
Si sabes las respuestas puedes ponerlas en un comentario bajo estas líneas.

martes, 18 de noviembre de 2014

OR No Es Siempre Conmutativa

Hallé la idea para este ejemplo aquí y decidí traducirlo para los hispanohablantes. La palabra OR suele ser conmutativa en Oracle y por eso los resultados de los comandos siguientes deberían ser iguales: 

select * from t where p=q or not p=q

y

select * from t where not p=q or p=q 

Pero en Oracle 9.2.0.7 y Oracle 10.2.0.3 los dos comandos daban resultados diferentes como se puede ver en el ejemplo que sigue: 

SQL> col p format a20
SQL> col q format a20
SQL> create type point as object (x real, y real)
  2  /
 
Type created.
 
SQL> create table t (p point, q point)
  2  /
 
Table created.
 
SQL> insert into t values
  2  (point(null, null), point(null,null))
  3  /
 
1 row created.
 
SQL> insert into t values
  2  (point(1, null), point(1,null))
  3  /
 
1 row created.
 
SQL> insert into t values (point(1, 2), point(1,2))
  2  /
 
1 row created.
 
SQL> select * from t
  2  /
 
P(X, Y)              Q(X, Y)
-------------------- --------------------
POINT(NULL, NULL)    POINT(NULL, NULL)
POINT(1, NULL)       POINT(1, NULL)
POINT(1, 2)          POINT(1, 2)
 
SQL> select * from t where p=q or not p=q
  2  /
 
P(X, Y)              Q(X, Y)
-------------------- --------------------
POINT(1, 2)          POINT(1, 2)
 
SQL> select * from t where not p=q or p=q
  2  /
 
P(X, Y)              Q(X, Y)
-------------------- --------------------
POINT(1, NULL)       POINT(1, NULL)
POINT(1, 2)          POINT(1, 2)
 
SQL> 

Creo que fuese causado por un error porque en Oracle 11.2.0.1, el problema había desaparecido: 

SQL> col p format a20
SQL> col q format a20
SQL> create type point as object (x real, y real)
  2  /
 
Type created.
 
SQL> create table t (p point, q point)
  2  /
 
Table created.
 
SQL> insert into t values
  2  (point(null, null), point(null,null))
  3  /
 
1 row created.
 
SQL> insert into t values
  2  (point(1, null), point(1,null))
  3  /
 
1 row created.
 
SQL> insert into t values (point(1, 2), point(1,2))
  2  /
 
1 row created.
 
SQL> select * from t
  2  /
 
P(X, Y)              Q(X, Y)
-------------------- --------------------
POINT(NULL, NULL)    POINT(NULL, NULL)
POINT(1, NULL)       POINT(1, NULL)
POINT(1, 2)          POINT(1, 2)
 
SQL> select * from t where p=q or not p=q
  2  /
 
P(X, Y)              Q(X, Y)
-------------------- --------------------
POINT(1, 2)          POINT(1, 2)
 
SQL> select * from t where not p=q or p=q
  2  /
 
P(X, Y)              Q(X, Y)
-------------------- --------------------
POINT(1, 2)          POINT(1, 2)
 
SQL>

domingo, 9 de noviembre de 2014

Archivos de Control

Aquí está una pregunta para ayudaros a estudiar para el examen OCA:

¿Cuántos archivos de control puede tener una base de datos?

A. Cuatro
B. Ocho
C. Doce
D. No hay límite


Probé la respuesta así. Hice ocho copias del archivo de control: 

C:\>dir CF*
Volume in drive C has no label.
Volume Serial Number is 269C-9AD9
 
Directory of C:\
 
05/11/2014  17:50        10,043,392 CF1
05/11/2014  17:50        10,043,392 CF2
05/11/2014  17:50        10,043,392 CF3
05/11/2014  17:50        10,043,392 CF4
05/11/2014  17:50        10,043,392 CF5
05/11/2014  17:50        10,043,392 CF6
05/11/2014  17:50        10,043,392 CF7
05/11/2014  17:50        10,043,392 CF8
               8 File(s)     80,347,136 bytes
               0 Dir(s)  12,114,571,264 bytes free
 
C:\>

Cambié el parámetro control_files de la manera siguiente:

control_files='C:\CF1','C:\CF2','C:\CF3','C:\CF4','C:\CF5','C:\CF6','C:\CF7','C:\CF8'

Abrí la base de datos y comprobé que había ocho archivos de control:

C:\Users\Andrew>sqlplus / as sysdba
 
SQL*Plus: Release 12.1.0.1.0 Production on Mié Nov 5 17:48:49 2014
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Conectado a una instancia inactiva.
 
SQL> startup
Instancia ORACLE iniciada.
 
Total System Global Area 1043886080 bytes
Fixed Size                  2410568 bytes
Variable Size             566233016 bytes
Database Buffers          469762048 bytes
Redo Buffers                5480448 bytes
Base de datos montada.
Base de datos abierta.
SQL> col name format a30
SQL> select name from v$controlfile
  2  /
 
NAME
------------------------------
C:\CF1
C:\CF2
C:\CF3
C:\CF4
C:\CF5
C:\CF6
C:\CF7
C:\CF8
 
8 filas seleccionadas.
 
SQL>

Repetí el procedimiento con nueve archivos de control pero esta vez no podía abrir la base de datos:

C:\Users\Andrew>sqlplus / as sysdba
 
SQL*Plus: Release 12.1.0.1.0 Production on Mié Nov 5 17:56:50 2014
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Conectado a una instancia inactiva.
 
SQL> startup
Instancia ORACLE iniciada.
 
Total System Global Area 1043886080 bytes
Fixed Size                  2410568 bytes
Variable Size             566233016 bytes
Database Buffers          469762048 bytes
Redo Buffers                5480448 bytes
ORA-03113: fin de archivo en el canal de comunicación
Identificador de Proceso: 2820
Identificador de Sesión: 240 Número de Serie: 3
 
SQL> exit
Desconectado de Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
C:\Users\Andrew>

Vi el error siguiente en el archivo alert:

Wed Nov 05 17:57:05 2014
Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\orcl1\orcl1\trace\orcl1_ckpt_2592.trc:
ORA-00208: el numero de nombres de archivos de control excede el limite de 8
Wed Nov 05 17:57:06 2014 

Por eso, la respuesta es B.

lunes, 3 de noviembre de 2014

Conexión Como SYSDBA

Aquí está una pregunta para ayudaros a estudiar para el examen OCA:

Acabo de conectarme a una base de datos así:

SQL> conn fred/bloggs as sysdba
Conectado.
SQL>

¿Cuál usuario estoy empleando en la base?

A. SYS
B. PUBLIC
C. SYSTEM
D. FRED
E. SYSDBA

Probé la respuesta en Oracle 11.2:

SQL> show user
USER is "SYS"
SQL>

Por eso la respuesta es A.

viernes, 31 de octubre de 2014

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

miércoles, 8 de octubre de 2014

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 >

miércoles, 1 de octubre de 2014

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>

miércoles, 21 de mayo de 2014

¿Un Problema con V$FILESTAT?

Hallé la idea para este ejemplo aquí. Antes de empezar, quiero explicar los nombres de tres de las columnas en V$FILESTAT:

PHYRDS (Physical Reads) = El número de veces que Oracle ha leído un archivo.
 
PHYBLKRD (Physical Block Reads) = El número de bloqueos que Oracle ha leído de un archivo.

 
SINGLEBLKRDS (Single Block Reads) = El número de veces que Oracle ha leído un solo bloqueo de un archivo.


Ejecuté el comando SQL siguiente en Oracle versión 11.1.0.6: 

SQL> l
  1  select file#, phyrds, phyblkrd, singleblkrds
  2  from v$filestat
  3  where phyrds = phyblkrd
  4* and singleblkrds != phyrds
SQL> /
 
     FILE#     PHYRDS   PHYBLKRD SINGLEBLKRDS
---------- ---------- ---------- ------------
         3       7467       7467         7457
         5        113        113          103
         8        118        118          108
        19        362        362          352
        20        297        297          287
        23        116        116          106
        25        113        113          103
 
7 rows selected.
 
SQL>

En cada línea, PHYRDS = PHYBLKRD, es decir que Oracle ha leído un solo bloqueo cada vez. Así, deben haber sido SINGLEBLKRDS pero no lo son porque el valor de SINGLEBLKRDS es diferente cada vez.


sábado, 10 de mayo de 2014

Oracle no Ejecuta un Disparador AFTER DELETE Tras Hacer un Truncate

En este ejemplo, quiero mostrar que Oracle no ejecute un disparador AFTER DELETE después de hacer un TRUNCATE. Lo probé en Oracle 11.2. Para empezar, creé una tabla:

SQL> create table tab1 (my_name varchar2(10))
  2  /

Tabla creada.

SQL>

Luego inserté una fila en la tabla:

SQL> insert into tab1 values ('Andrew')
  2  /

1 fila creada.

SQL> commit
  2  /

Confirmación terminada.

SQL> select * from tab1
  2  /

MY_NAME
----------
Andrew

SQL>

Entonces creé una segunda tabla: 

SQL> create table tab2 (my_name varchar2(10))
  2  /

Tabla creada.

SQL>

Creé un disparador para insertar filas en TAB2 tras borrarlas de TAB1:

SQL> create or replace trigger trig1
  2  after delete on tab1
  3  for each row
  4  begin
  5  insert into tab2 (my_name) values (:old.my_name);
  6  end;
  7  /
 
Disparador creado.


SQL>

Para probar el disparador, empleé un DELETE para borrar la fila de TAB1. La fila apareció en TAB2:

SQL> delete tab1
  2  /

1 fila suprimida.

SQL> select * from tab1
  2  /

ninguna fila seleccionada

SQL> select * from tab2
  2  /

MY_NAME
----------
Andrew

SQL>

Ejecuté un ROLLBACK y la fila volvió de TAB2 a TAB1:

SQL> rollback
  2  /
 
Rollback terminado.
 
SQL> select * from tab1
  2  /
 
MY_NAME
----------
Andrew
 
SQL> select * from tab2
  2  /
 
ninguna fila seleccionada


SQL>

Repetí la prueba con TRUNCATE: 

SQL> truncate table tab1
  2  /
 
Tabla truncada.


SQL>

La fila desapareció de TAB1:

SQL> select * from tab1
  2  /
 
ninguna fila seleccionada


SQL>

... pero Oracle no ejecutó el disparador y la fila no apareció en TAB2:

SQL> select * from tab2
  2  /
 
ninguna fila seleccionada
 

SQL>

... y tras hacer un ROLLBACK, la fila no volvió a TAB1 porque TRUNCATE es DDL y incluye un COMMIT:

SQL> rollback
  2  /
 
Rollback terminado.
 
SQL> select * from tab1
  2  /
 
ninguna fila seleccionada
 
SQL> select * from tab2
  2  /
 
ninguna fila seleccionada
 
SQL> 


En inglés / in English