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>

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?

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.

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:

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>

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>

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>

En inglés 

En francés 

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>

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>

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>