ALTER DATABASE LINK

Antes de empezar este ejemplo, cambié la contraseña de un usuario en una base de datos remota. Este usuario es empleado por un vínculo entre una base de datos local y la base de datos remota.

SQL> alter session set nls_language = 'SPANISH'
  2  /

Session altered.

SQL>

Ahora el vínculo no funciona en la base de datos local:

SQL> select * from dual@link1
  2  /
select * from dual@link1
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: line precediendo a LINK1

SQL>

Antes de Oracle 11 versión 2, era necesario borrar el vínculo y recrearlo. Ahora con Oracle 11 versión 2, se puede hacerlo con el comando ALTER DATABASE LINK. Para emplear este comando, es necesario tener el privilegio con el mismo nombre. Pero nadie tiene este privilegio en la base de datos local, hasta el usuario Oracle:

SQL> show user
USER is "ORACLE"
SQL> alter database link link1
  2  connect to andrew identified by reid
  3  /
alter database link link1
*
ERROR at line 1:
ORA-01031: privilegios insuficientes

SQL>

Pero el usuario Oracle tiene el papel DBA:

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

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

SQL>

… y el papel DBA tiene el privilegio GRANT ANY PRIVILEGE:

SQL> select grantee from dba_sys_privs
  2  where privilege = 'GRANT ANY PRIVILEGE'
  3  /

GRANTEE
------------------------------
IMP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
DBA
SYS

SQL>

Así , Oracle puede dar el privilegio ALTER DATABASE LINK a sí mismo:

SQL> show user
USER is "ORACLE"
SQL> grant alter database link to oracle
  2  /

Grant succeeded.

SQL>

Tras hacer éste, el comando funciona:

SQL> alter database link link1
  2  connect to andrew identified by reid
  3  /

Database link altered.

SQL>

El vínculo funciona también:

SQL> select * from dual@link1
  2  /

D
-
X

SQL>

El comando ALTER DATABASE LINK no funciona en Oracle 11 versión 1:

SQL> alter database link link1
  2  connect to andrew
  3  identified by reid
  4  /
alter database link link1
*
ERROR at line 1:
ORA-02231: falta la opcion para ALTER DATABASE o no es
valida

SQL>

La Papelera de Reciclaje no Guarda Tablas del Tablespace SYSTEM

Oracle introdujo la papelera de reclicaje en la versión 10. Este ejemplo muestra que Oracle no pone tablas del tablespace SYSTEM en la papelera de reciclaje tras borrarlas. Lo probé en Oracle 11.2.0.2.7. Para empezar, creé una tabla en el tablespace USERS:

SQL> conn / as sysdba

Connected.

SQL> alter session set nls_language = 'SPANISH'

  2  /

 

Sesion modificada.

 

SQL> create table tab1

  2  (col1 number)

  3  tablespace users

  4  /

 

Tabla creada.

SQL>

Luego borré la tabla, comprobé que podía verla en la papelera de reclicaje y la restauré: 

SQL> drop table tab1

  2  /

 

Tabla borrada.

 

SQL> select original_name from recyclebin

  2  /

 

ORIGINAL_NAME

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

TAB1

 

SQL> flashback table tab1 to before drop

  2  /

 

Flashback terminado.

 

SQL> desc tab1

Nombre                     ?Nulo?   Tipo

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

COL1                                NUMBER

SQL>

Entonces hice la misma cosa otra vez pero creé la tabla en el tablespace SYSTEM:

SQL> create table tab2

  2  (col1 number)

  3  tablespace system

  4  /

 

Tabla creada.

 

SQL> drop table tab2

  2  /

 

Tabla borrada.

SQL>

Esta vez, cuando busqué la tabla en la papelera de reclicaje, no estaba allí:

 

SQL> select original_name from recyclebin

  2  /

 

ninguna fila seleccionada

SQL>

... y cuando intenté a restaurar la tabla, no podía hacerlo:

 

SQL> flashback table tab2 to before drop

  2  /

flashback table tab2 to before drop

*

ERROR en linea 1:

ORA-38305: el objeto no esta en la papelera de

reciclaje

 

SQL>

En inglés / in English

ORA-00959

Aquí está un ejemplo sobre deferred segment creation. Lo probé en Oracle 11.2.0.1.0. Lo he escrito en español porque Laurent Schneider ya ha hecho un ejemplo en inglés. Se puede ver su ejemplo aquí. En esta versión de Oracle, la opción por defecto para deferred segment creation es TRUE: 

SQL> conn fred/bloggs

Connected.

SQL> alter session set nls_language = 'SPANISH'

  2  /

Session altered.

SQL> col value format a5

SQL> col isdefault format a10

SQL> select value, isdefault from v$parameter

  2  where name = 'deferred_segment_creation'

  3  /

VALUE ISDEFAULT

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

TRUE  TRUE

SQL>

Necesitamos un tablespace para este ejemplo y este tablespace tiene que ser el tablespace por defecto de Fred: 

SQL> create tablespace users1

  2  extent management local datafile

  3  '/database/Andrew/ANDREW01/users1.dbf'

  4  reuse

  5  /

Tablespace created.

SQL> alter user fred

  2  default tablespace users1

  3  /

User altered.

SQL>

Si intentas crear una tabla con deferred segment creation, Oracle no la crea inmediatamente. La crea más tarde cuando alguien inserta líneas en la tabla: 

SQL> create table dsc as

  2  select * from dba_objects

  3  where 1=2

  4  /

Table created.

SQL> select * from dsc

  2  where owner like 'A%'

  3  /

no rows selected

SQL>

Así hay detalles para la tabla en DBA_TABLES: 

SQL> select tablespace_name

  2  from dba_tables

  3  where table_name = 'DSC'

  4  /

TABLESPACE_NAME

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

USERS1

SQL>

Pero no hay nada en DBA_SEGMENTS: 

SQL> select tablespace_name

  2  from dba_segments

  3  where segment_name = 'DSC'

  4  /

no rows selected

SQL>

Luego, si borras el tablespace que contiene la tabla: 

SQL> drop tablespace users1

  2  /

Tablespace dropped.

SQL>

… Oracle te da un mensaje ORA-00959 si intentas leer o borrar la tabla: 

SQL> select * from dsc

  2  where owner like 'B%'

  3  /

select * from dsc

*

ERROR at line 1:
ORA-00959: el tablespace 'USERS1' no existe

SQL> drop table dsc

  2  /

drop table dsc

*

ERROR at line 1:
ORA-00959: el tablespace 'USERS1' no existe

SQL>

Para borrar la tabla, es necesario recrear el tablespace: 

SQL> create tablespace users1

  2  extent management local datafile

  3  '/database/Andrew/ANDREW01/users1.dbf'

  4  reuse

  5  /

Tablespace created.

SQL>

Luego se puede borrar la tabla: 

SQL> drop table dsc

  2  /

Table dropped.

SQL>

… y si intentas leer la tabla otra vez, Oracle te da un mensaje ORA-00942 lo que prueba que la tabla no existe: 

SQL> select * from dsc

  2  where owner like 'C%'

  3  /

select * from dsc

*

ERROR at line 1:

ORA-00942: la tabla o vista no existe

SQL>

Como se Puede Renombrar un Tablespace Oracle

En Oracle 9 no era posible cambiar el nombre de un tablespace:

SQL> l

  1* alter tablespace users rename to andrew

SQL> /

alter tablespace users rename to andrew

                              *

ERROR at line 1:

ORA-01904: DATAFILE keyword expected

 

SQL>

... pero desde Oracle 10 es posible hacerlo. Decidí renombrar el tablespace USERS y llamarlo ANDREW para hacer una demostración. Para empezar comprobé que tenía un tablespace USERS y que no  tenía un tablespace ANDREW: 

SQL> l

  1  select tablespace_name from dba_tablespaces

  2* where tablespace_name in ('ANDREW', 'USERS')

SQL> /

 

TABLESPACE_NAME

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

USERS

 

SQL>

Luego mostré que había una tabla llamada COCHES en el tablespace USERS:

 

SQL> l

  1  select tablespace_name from dba_tables

  2* where table_name = 'COCHES'

SQL> /

 

TABLESPACE_NAME

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

USERS

 

SQL>

Cambié el nombre del tablespace USERS y lo llamé ANDREW: 

SQL> alter tablespace users rename to andrew;

 

Tablespace modificado.

 

SQL>

Entonces comprobé que tenía un tablespace ANDREW y que no  tenía un tablespace USERS:

SQL> l

  1  select tablespace_name from dba_tablespaces

  2* where tablespace_name in ('ANDREW', 'USERS')

SQL> /

 

TABLESPACE_NAME

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

ANDREW

 

SQL>

También comprobé que la tabla COCHES estaba en el tablespace ANDREW: 

SQL> l

  1  select tablespace_name from dba_tables

  2* where table_name = 'COCHES'

SQL> /

 

TABLESPACE_NAME

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

ANDREW

 

SQL>

Hice el tablespace ANDREW offline:

 

SQL> alter tablespace andrew offline;

 

Tablespace modificado.

SQL>

Luego intenté renombrarlo pero no era permitido:

 

SQL> alter tablespace andrew rename to users;

alter tablespace andrew rename to users

*

ERROR en linea 1:

ORA-01135: el archivo 4 accedido para LMD/consulta

esta offline

ORA-01110: archivo de datos 4:

'/database/ORCL/datafiles/users01.dbf'

 

SQL>

Tampoco se puede cambiar los nombres de los tablespaces SYSTEM y SYSAUX:

 

SQL> alter tablespace system rename to fred;

alter tablespace system rename to fred

*

ERROR en linea 1:

ORA-00712: no se puede cambiar el nombre del

tablespace del sistema

 

SQL> alter tablespace sysaux rename to fred;

alter tablespace sysaux rename to fred

*

ERROR en linea 1:

ORA-13502: No se puede cambiar el nombre del

tablespace SYSAUX

 

SQL>

Por fin, si intentas a dar un nuevo nombre inválido a tu tablespace, Oracle responde con un ORA-02150:

SQL> alter tablespace users rename to 123;

alter tablespace users rename to 123

                                 *

ERROR en linea 1:

ORA-02150: nuevo nombre de tablespace no valido

 

SQL>

Así, ahora mi problema es - ¿Cómo se puede tener un ORA-00711? Desafortunadamente, no tengo una versión española de este error: 

Solaris > oerr ora 00711

00711, 00000, "new tablespace name is invalid"

// *Cause:  An attempt to rename a tablespace failed because the new name

//          is invalid.

// *Action: Choose a valid new name and retry the command.

Solaris > 

En inglés / in English

Como se Pueden Emplear Nombres Malos en Oracle

Hallé este ejemplo, escrito por Robert Vollman, aquí. Es en inglés y por eso decidí hacer una traducción española. En Oracle, el nombre de una tabla, un procedimiento o una variable no puede empezar con un número: 

SQL> alter session set nls_language = 'SPANISH';

Session altered.

SQL> CREATE TABLE 123Tabla (unvalor NUMBER);
CREATE TABLE 123Tabla (unvalor NUMBER)
             *
ERROR at line 1: ORA-00903: nombre de tabla no válido
SQL>

Si quieres hacerlo, hay que poner el nombre entre comillas:

SQL> CREATE TABLE "123Tabla" (unvalor NUMBER);

Table created.

SQL>

Un nombre entre comillas es sensible al uso de mayúsculas y minúsculas:

SQL> DROP TABLE "123TABLA";
DROP TABLE "123TABLA"
           *
ERROR at line 1:
ORA-00942: la tabla o vista no existe

SQL> DROP TABLE "123Tabla";

Table dropped.

SQL>

También se pueden emplear palabras reservadas entre comillas:

SQL> CREATE TABLE TABLE (NUMBER NUMBER);
CREATE TABLE TABLE (NUMBER NUMBER)
             *
ERROR at line 1:
ORA-00903: nombre de tabla no válido

SQL> CREATE TABLE "TABLE" (NUMBER NUMBER);
CREATE TABLE "TABLE" (NUMBER NUMBER)
                      *
ERROR at line 1:
ORA-00904: : identificador no válido

SQL> CREATE TABLE "TABLE" ("NUMBER" NUMBER);

Table created.

SQL> DESC "TABLE"

Name                    Null?    Type
----------------------- -------- ----------------
NUMBER                           NUMBER

SQL>

Transacciones Pendientes

Hallé este ejemplo aquí: http://laurentschneider.com/wordpress/2010/10/how-to-check-if-i-have-a-pending-transaction.html

Era escrito en inglés y por eso decidí hacer una traducción española.

Creé el ejemplo en Oracle 10 en mi propio ordenador en Linux. Cuando instalaba Oracle en la máquina, también añadí el idioma español. Esta vez los mensajes, por ejemplo Sesion modificada son en español lo que me gusta.
¿Cómo puedo verificar si tenga una transacción pendiente? Éste es sencillo. Hay que mirar si dbms_transaction.step_id sea null:

SQL> alter session set nls_language = 'SPANISH';

Sesion modificada.

SQL> create table t (col1 number);

Tabla creada.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------


SQL> 

… o tal vez: 

SQL> select
  2  nvl(to_char(dbms_transaction.step_id), 'NULL')
  3  as step_id from dual;

STEP_ID
----------------------------------------
NULL

SQL>

En este momento (arriba), no tengo ninguna transacción pendiente y dbms_transaction.step_id es  vacío.
Tras hacer un INSERT en una tabla (abajo), un valor aparece en dbms_transaction.step_id:

 SQL> insert into t values (1);

1 fila creada.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------
202312287467529

SQL>

Si hago un COMMIT, la transacción pendiente termina y el valor desaparece de dbms_transaction.step_id:

SQL> commit;

Confirmacion terminada.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------

SQL>

También se puede acabar una transacción pendiente con un ROLLBACK y el valor en dbms_transaction_step_id desaparece otra vez:

SQL> insert into t values (2);

1 fila creada.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------
236397147896370

SQL> rollback;

Rollback terminado.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------


SQL>

NOT EXISTS Contra NOT IN

Este ejemplo, probado en Oracle 10, muestra que NOT EXISTS pueda ser más eficaz que NOT IN. Para empezar, hay que crear una tabla emp para los empleados desde E1 hasta E9999:

SQL> alter session set nls_language = 'SPANISH'
  2  /

Sesion modificada.

SQL> create table emp
  2 (empno  varchar2(5),
  3  deptno varchar2(5))
  4 /

Tabla creada.

SQL>

Luego, necesitamos una tabla dept para los departamentos desde D1 hasta D9999:

SQL> create table dept
  2  (deptno varchar2(5))
  3  /

Tabla creada.

SQL>

El SQL siguiente pone el empleado E1 en el departamento D1 y el empleado E2 en el departamento D2 etc:

SQL> declare
  2  begin
  3  for ctr in 1..9999 loop
  4  insert into emp values ('E'||ctr,'D'||ctr);
  5  insert into dept values ('D'||ctr);
  6  end loop;
  7  end;
  8  /

Procedimiento PL/SQL terminado correctamente.

SQL>

Ahora creamos un índice y una estadística para la tabla emp:

SQL> create index deptno_index on emp(deptno)
  2  /

Indice creado.

SQL> exec dbms_stats.gather_table_stats -
> (ownname => 'SYSTEM', tabname => 'EMP');

Procedimiento PL/SQL terminado correctamente.

SQL>

Si borramos el empleado E5000, el departamento D5000 será vacío:

SQL> delete from emp
  2  where empno = 'E5000';

1 fila suprimida.

SQL>

Por fin buscaremos el departamento vacío con dos consultas diferentes:

SQL> set timing on
SQL> alter session
  2  set timed_statistics = true
  3  /

Sesion modificada.

Transcurrido: 00:00:00.09

SQL> alter session
  2  set sql_trace = true
  3  /

Sesion modificada.

Transcurrido: 00:00:00.05

SQL>

Una consulta que emplea NOT EXISTS toma mucho menos de un segundo:

SQL> select deptno from dept
  2  where not exists
  3  (select deptno from emp
  4   where emp.deptno = dept.deptno)
  5  /

DEPTN
-----
D5000

Transcurrido: 00:00:00.12

SQL>

Pero una consulta que emplea NOT IN toma siete segundos:

SQL> select deptno from dept
  2  where deptno not in
  3  (select deptno from emp)
  4  /

DEPTN
-----
D5000

Transcurrido: 00:00:07.26

SQL> alter session
  2  set sql_trace = false
  3  /

Sesion modificada.

Transcurrido: 00:00:00.05

SQL>

Tras utilizar tkprof para examinar la fila creada por sql_trace, se puede ver la diferencia entre las dos consultas. La consulta con NOT EXISTS emplea el índice pero la consulta con NOT IN no lo emplea. Es necesario hacer click en las imágenes una vez o dos veces para verlas en sus tamaños originales:

En inglés