domingo, 30 de marzo de 2014

Columnas Invisibles (Característica Nueva en Oracle 12)

Aquí está un ejemplo que he traducido de la documentación inglesa. Lo hice para mostrar porque sea necesario prestar atención con esta característica nueva. Lo probé en Oracle 12.1. Para empezar, creé una tabla SIN columnas invisibles: 

SQL> create table tab1
  2  (a number,
  3   b number,
  4   c number)
  5  /
 
Tabla creada.
 
SQL> desc tab1
Nombre                     ¿Nulo?   Tipo
-------------------------- -------- ------------------
A                                   NUMBER
B                                   NUMBER
C                                   NUMBER
 
SQL>

Luego cambié columna B para hacerla invisible: 

SQL> alter table tab1 modify (b invisible)
  2  /
 
Tabla modificada.
 
SQL> desc tab1
Nombre                     ¿Nulo?   Tipo
-------------------------- -------- ------------------
A                                   NUMBER
C                                   NUMBER
 
SQL>

Entonces la cambié para hacerla visible otra vez. Cuando se hace esto, Oracle pone la columna tras las otras columnas en la tabla. Así, el orden de las columnas ha sido cambiado de A, B, C a A, C, B:

SQL> alter table tab1 modify (b visible)
  2  /
 
Tabla modificada.
 
SQL> desc tab1
Nombre                     ¿Nulo?   Tipo
-------------------------- -------- ------------------
A                                   NUMBER
C                                   NUMBER
B                                   NUMBER
 
SQL>

jueves, 27 de marzo de 2014

Fetch First n Rows Only (Característica Nueva en Oracle 12)

Probé esto en Oracle 12.1. Suelo hacer los ejemplos en este blog en español pero esta vez, tengo que empezar en inglés. Ustedes verán la razón en un momento:

SQL> alter session set nls_language = 'ENGLISH'
  2  /
 
Session altered.

SQL> 

Para empezar, creé una tabla y añadí unos datos: 

SQL> create table caracter
  2  (col1 varchar2(1))
  3  /
 
Table created.
 
SQL> insert into caracter values('a');
 
1 row created.
 
SQL> insert into caracter values('b');
 
1 row created.
 
SQL> insert into caracter values('c');
 
1 row created.
 
SQL> insert into caracter values('A');
 
1 row created.
 
SQL> insert into caracter values('B');
 
1 row created.
 
SQL> insert into caracter values('C');
 
1 row created.
 
SQL> insert into caracter values('1');
 
1 row created.
 
SQL> insert into caracter values('2');
 
1 row created.
 
SQL> insert into caracter values('3');
 
1 row created.

SQL>

Luego ordené los datos y mostré los tres primeros valores. En versiones anteriores, era posible hacerlo de la manera siguiente:

SQL> select col1 from
  2  (select col1 from caracter
  3   order by col1)
  4  where rownum < 4
  5  /
 
COL1
----
1
2
3

SQL>

Ahora, en Oracle 12, se puede hacerlo así. Por supuesto, los resultados son iguales: 

SQL> select col1 from caracter
  2  order by col1
  3  fetch first 3 rows only
  4  /
 
COL1
----
1
2
3

SQL>

Pero, si ustedes hacen la prueba en español, los dos juegos de resultados son diferentes. 

SQL> alter session set nls_language = 'SPANISH'
  2  /
 
Sesión modificada.
 
SQL> select col1 from
  2  (select col1 from caracter
  3   order by col1)
  4  where rownum < 4
  5  /
 
COL1
----
A
a
B
 
SQL> select col1 from caracter
  2  order by col1
  3  fetch first 3 rows only
  4  /
 
COL1
----
1
2
3
 
SQL>

Por el momento, no sé si haya cometido un error o si sea un bug. ¿Acaso alguien pueda ayudarme?

martes, 25 de marzo de 2014

La Diferencia Entre sum(col1)+sum(col2) y sum(col1+col2)

Probé este ejemplo en Oracle 11.2 para mostrar porque sum(col1)+sum(col2) y sum(col1+col2) puedan producir resultados diferentes. Para empezar, creé una tabla y añadí unos datos:

SQL> create table empleado
  2  (nombre varchar2(10),
  3   sueldo number,
  4   comision number)
  5  /

Tabla creada.

SQL> insert into empleado values ('Andrew',10,5)
  2  /

1 fila creada.

SQL> insert into empleado values ('Brian',10,null)
  2  /

1 fila creada.

SQL> insert into empleado values ('Colin',null,5)
  2  /

1 fila creada.

SQL>

Luego ejecuté dos comandos de SQL y vi que los resultados eran diferentes:

SQL> select sum(sueldo) + sum(comision) from empleado
  2  /

SUM(SUELDO)+SUM(COMISION)
-------------------------
                       30

SQL> select sum(sueldo + comision) from empleado
  2  /

SUM(SUELDO+COMISION)
--------------------
                  15

SQL>

Entonces miré los datos que había añadido: 

SQL> select * from empleado
  2  /

NOMBRE         SUELDO   COMISION
---------- ---------- ----------
Andrew             10          5
Brian              10
Colin                          5

SQL>

Verifiqué sum(sueldo). Esta cifra cuenta los valores pero ignora los nulos:

SQL> select sum(sueldo) from empleado
  2  /

SUM(SUELDO)
-----------
         20

SQL>

La misma cosa pasa con sum(comision):

SQL> select sum(comision) from empleado
  2  /

SUM(COMISION)
-------------
           10

SQL>

Pero si se suman sueldo y comisión en cada fila, el resultado es nulo si sueldo sea nulo. También es nulo si comisión sea nulo:

SQL> select nombre, (sueldo + comision) "Pago Total"
  2  from empleado
  3  /

NOMBRE     Pago Total
---------- ----------
Andrew             15
Brian
Colin

SQL>

Esto es porque, si se añaden un número y un nulo, el resultado es un nulo: 

SQL> l
  1  select nvl(to_char(10+null),'10 + null es nulo')
  2* from dual
SQL> /
 
NVL(TO_CHAR(10+NU
-----------------
10 + null es nulo
 
SQL> 

Por eso, los dos comandos producen resultados diferentes cuando la tabla contiene nulos.

sábado, 22 de marzo de 2014

Instalación de Oracle 12.1 se Para a "updating registry key"

Descargué los archivos para instalar Oracle 12c en Windows y los extraé en dos directorios distintos como se puede ver bajo estas líneas.  Se puede hacer clic en la imagen para verla con su tamaño original:


Empecé la instalación y luego vi unos mensajes sobre archivos que Oracle no podía hallar. Por fin la instalación se paró con el mensaje updating registry key y tuve que cancelarla:


Extraé los archivos otra vez pero los puse todos en el mismo directorio. Es decir que incorporé el contenido del archivo 2of2 con el contenido del archivo 1of2 que ya había extraido. Esto no era difícil, el software de extracción lo hizo para mí:


Repetí la instalación pero esta vez no había errores:

viernes, 21 de marzo de 2014

Como se Puede Mover un Datafile en Oracle 12c

Hallé esto aquí y decidí traducirlo para los hispanohablantes. Lo probé en Oracle 12.1. En versiones anteriores, era necesario seguir varias etapas para mover un datafile. Ahora es posible hacerlo con un comando como se puede ver bajo estas líneas: 

SQL> l
  1  select file_name from dba_data_files
  2* where tablespace_name = 'USERS'
SQL> /
 
FILE_NAME
----------------------------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\O1_MF_USERS_9LM1W41L_.DBF
 
SQL> alter database move datafile
  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\O1_MF_USERS_9LM1W41L_.DBF'
  3  to
  4  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'
  5  /
 
Base de datos modificada.
 
SQL> select file_name from dba_data_files
  2  where tablespace_name = 'USERS'
  3  /
 
FILE_NAME
----------------------------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF
 
SQL> 

... y aquí está el nuevo archivo en el sistema operativo para probarlo: 

PS C:\app\administrator\oradata\orcl1\datafile> dir users.*
 
 
    Directory: C:\app\administrator\oradata\orcl1\datafile
 
 
Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---        20/03/2014     18:55    5251072 USERS.DBF
 
 
PS C:\app\administrator\oradata\orcl1\datafile> 

El comando aun parece funcionar con el tablespace SYSTEM

SQL> select file_name from dba_data_files
  2  where tablespace_name = 'SYSTEM'
  3  /
 
FILE_NAME
--------------------------------------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\O1_MF_SYSTEM_9LM1SGKN_.DBF
 
SQL> alter database move datafile
  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\O1_MF_SYSTEM_9LM1SGKN_.DBF'
  3  to
  4  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\SYSTEM.DBF'
  5  /
 
Base de datos modificada.
 
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
PS C:\app\administrator\oradata\orcl1\datafile> dir system.*
 
 
    Directory: C:\app\administrator\oradata\orcl1\datafile
 
 
Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---        20/03/2014     19:01  817897472 SYSTEM.DBF
 
 
PS C:\app\administrator\oradata\orcl1\datafile>

jueves, 20 de marzo de 2014

DBMS_UTILITY.EXPAND_SQL_TEXT (Característica Nueva en Oracle 12)

Hallé esto en el blog de Tom Kyte y lo probé en Oracle 12.1. De vez en cuando, tienes una vista complicada que está formada de vistas sobre vistas etc. Si quieres ver las tablas y columnas que mira, se puede hacerlo con esta función. Aquí está un ejemplo:

SQL> set serveroutput on format word_wrapped
SQL> declare
  2    original_sql clob := 'select * from all_users';
  3    expanded_sql clob := empty_clob();
  4  begin
  5    dbms_utility.expand_sql_text(original_sql,expanded_sql);
  6    dbms_output.put_line(expanded_sql);
  7  end;
  8  /
SELECT "A1"."USERNAME" "USERNAME","A1"."USER_ID"
"USER_ID","A1"."CREATED" "CREATED","A1"."COMMON"
"COMMON","A1"."ORACLE_MAINTAINED" "ORACLE_MAINTAINED" FROM
(SELECT "A4"."NAME" "USERNAME","A4"."USER#"
"USER_ID","A4"."CTIME"
"CREATED",DECODE(BITAND("A4"."SPARE1",128),128,'YES','NO')
"COMMON",DECODE(BITAND("A4"."SPARE1",256),256,'Y','N')
"ORACLE_MAINTAINED" FROM "SYS"."USER$" "A4","SYS"."TS$"
"A3","SYS"."TS$" "A2" WHERE "A4"."DATATS#"="A3"."TS#" AND
"A4"."TEMPTS#"="A2"."TS#" AND "A4"."TYPE#"=1) "A1"
 
Procedimiento PL/SQL terminado correctamente.
 
SQL>

lunes, 17 de marzo de 2014

El Problema con REVOKE

Probé este ejemplo en Oracle 11.2 para mostrar porque sea necesario prestar atención con el comando REVOKE. Si das el rol DBA a un usuario, Oracle le da a él también el privilegio UNLIMITED TABLESPACE

SQL> create user andrew identified by reid
  2  /
 
Usuario creado.
 
SQL> grant dba to andrew
  2  /
 
Concesión terminada correctamente.

SQL> select granted_role from dba_role_privs
  2  where grantee = 'ANDREW'
  3  /

GRANTED_ROLE
------------------------------
DBA

SQL> select privilege from dba_sys_privs
  2  where grantee = 'ANDREW'
  3  /

PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE


SQL>

Cuando se revoca el rol DBA de un usuario, Oracle revoca también el privilegio UNLIMITED TABLESPACE: 

SQL> revoke dba from andrew
  2  /

Revocación terminada correctamente.

SQL> select granted_role from dba_role_privs
  2  where grantee = 'ANDREW'
  3  /

ninguna fila seleccionada

SQL> select privilege from dba_sys_privs
  2  where grantee = 'ANDREW'
  3  /

ninguna fila seleccionada


SQL> 

¿Pero qué pasa si un usuario recibe el privilegio UNLIMITED TABLESPACE por sí mismo?

SQL> grant unlimited tablespace to andrew
  2  /

Concesión terminada correctamente.

SQL> select privilege from dba_sys_privs
  2  where grantee = 'ANDREW'
  3  /

PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE


SQL>

Y alguien le da el rol DBA:

SQL> grant dba to andrew
  2  /

Concesión terminada correctamente.

SQL> select granted_role from dba_role_privs
  2  where grantee = 'ANDREW'
  3  /

GRANTED_ROLE
------------------------------
DBA

SQL> select privilege from dba_sys_privs
  2  where grantee = 'ANDREW'
  3  /

PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE


SQL>

Luego se revoca el rol DBA:

SQL> revoke dba from andrew
  2  /

Revocación terminada correctamente.

SQL> select granted_role from dba_role_privs
  2  where grantee = 'ANDREW'
  3  /

ninguna fila seleccionada


SQL>

Este usuario pierde también el privilegio UNLIMITED TABLESPACE y tal vez no querías hacer esto:

SQL> select privilege from dba_sys_privs
  2  where grantee = 'ANDREW'
  3  /

ninguna fila seleccionada

SQL>



miércoles, 12 de marzo de 2014

ORA-02097 y ORA-00044

Probé este ejemplo en Oracle 11.2. Cambié el valor de timed_statistics a true antes de hacer una prueba:

SQL> alter session set timed_statistics = true
  2  /
 
Session altered.

SQL>

Hice la prueba y después intenté a cambiar timed_statistics a false pero Oracle me dio dos errores:

SQL> alter session set timed_statistics = false
  2  /
ERROR:
ORA-02097: no se puede modificar el parametro porque
el valor especificado no es valido
ORA-00044: timed_statistics debe estar definido en
TRUE si statistics_level no esta definido en BASIC

SQL>

Leí los errores, miré el valor de statistics_level y lo cambié de la manera siguiente:

SQL> select value from v$parameter
  2  where name = 'statistics_level'
  3  /
 
VALUE
-------------------------------------------------------
TYPICAL
 
SQL> alter session set statistics_level = 'BASIC'
  2  /
 
Session altered.

SQL>

Luego pude cambiar el valor de timed_statistics sin problemas:

SQL> alter session set timed_statistics = false
  2  /
 
Session altered.
 
SQL>

martes, 11 de marzo de 2014

Flash Recovery Area

Esto llegó en Oracle 10g.  Da el nombre de un directorio en un disco donde Recovery Manager (RMAN) pueda guardar los backups y los registros archivados de redo tras crearlos.  El administrador de bases de datos especifica  el nombre del directorio y el tamaño máximum del Flash Recovery Area.
Se puede especificar este nombre con el comando siguiente:

C:\Users\Andrew>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Lun Mar 10 18:27:56 2014

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

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set db_recovery_file_dest
  2  = 'C:\Users\Andrew\Oracle\FRA'
  3  /
alter system set db_recovery_file_dest
*
ERROR en línea 1:
ORA-02097: no se puede modificar el parámetro porque el valor especificado no
es v
álido
ORA-19802: no se puede utilizar DB_RECOVERY_FILE_DEST sin
DB_RECOVERY_FILE_DEST_SIZE

SQL>

Pero, como se puede ver, si intentas a hacerlo antes de especificar el tamaño, Oracle te da un error. Se puede especificar el tamaño así:

SQL> l
  1* alter system set db_recovery_file_dest_size = 4g
SQL> /

Sistema modificado.

SQL>

Y luego se puede especificar el nombre del directorio sin ver ningún error:

SQL> l
  1  alter system set db_recovery_file_dest
  2* = 'C:\Users\Andrew\Oracle\FRA'
SQL> /
 
Sistema modificado.
 
SQL>


Entonces, si haces un alter system switch logfile:

SQL> alter system switch logfile
  2  /
 
Sistema modificado.
 
SQL>

... el registro archivado de redo aparece en un lugar apropiado debajo del directorio especificado para el Flash Recovery Area:

C:\Users\Andrew\Oracle\FRA\ORCL1\ARCHIVELOG\2014_03_10>dir
 Volume in drive C is OS
 Volume Serial Number is 50B4-FA92
 
 Directory of C:\Users\Andrew\Oracle\FRA\ORCL1\ARCHIVELOG\2014_03_10
 
10/03/2014  19:16    <DIR>          .
10/03/2014  19:16    <DIR>          ..
10/03/2014  19:16         3,857,408 O1_MF_1_78_9KW3W938_.ARC
               1 File(s)      3,857,408 bytes
               2 Dir(s)  423,189,569,536 bytes free
 
C:\Users\Andrew\Oracle\FRA\ORCL1\ARCHIVELOG\2014_03_10>

domingo, 9 de marzo de 2014

Como se Puede Poner una Base de Datos en Modo ARCHIVELOG

En unos de los ejemplos que siguen, necesitaré una base de datos en modo ARCHIVELOG. En otros, necesitaré una base de datos en modo NOARCHIVELOG. Por eso, os muestro como se puede cambiar una base de datos de modo NOARCHIVELOG a modo ARCHIVELOG. Nada más.  Explicaré la diferencia entre NOARCHIVELOG y ARCHIVELOG en el futuro.  Probé este ejemplo en Oracle 11.2.  Se puede ver el modo de una base de datos así:

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL>

Se puede poner una base de datos en modo ARCHIVELOG con el comando alter database archivelog.  Pero si intentas a hacerlo cuando la base datos sea abierta, Oracle te da un error:

SQL> alter database archivelog
  2  /
alter database archivelog
*
ERROR en l
ínea 1:
ORA-01126: la base de datos debe estar montada en esta instancia y no estar  abierta en ninguna instancia

SQL>

Para evitar este error, hay que montar la base de datos. Pensaba que podría hacerlo de la manera siguiente:

SQL> startup force mount
Instancia ORACLE iniciada.


Total System Global Area  523108352 bytes
Fixed Size                  1375704 bytes
Variable Size             327156264 bytes
Database Buffers          188743680 bytes
Redo Buffers                5832704 bytes
Base de datos montada.
SQL>

Pero después, cuando intenté a cambiar el modo, Oracle me dio otro error:

SQL> alter database archivelog
  2  /
alter database archivelog
*
ERROR en l
ínea 1:
ORA-00265: es necesario recuperar la instancia, no se puede definir el modo ARCHIVELOG

SQL> 

Luego tuve que abrir y cerrar la base de datos antes de montarla: 

SQL> alter database open
  2  /

Base de datos modificada.

SQL> shutdown
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL> startup mount
Instancia ORACLE iniciada.

Total System Global Area  523108352 bytes
Fixed Size                  1375704 bytes
Variable Size             327156264 bytes
Database Buffers          188743680 bytes
Redo Buffers                5832704 bytes
Base de datos montada.
SQL>

Entonces pude por fin poner la base de datos en modo ARCHIVELOG y abrirla: 

SQL> alter database archivelog
  2  /

Base de datos modificada.

SQL> alter database open
  2  /

Base de datos modificada.

SQL> select log_mode from v$database
  2  /

LOG_MODE
------------
ARCHIVELOG

SQL>