dieguz2: ORACLE

Mostrando entradas con la etiqueta ORACLE. Mostrar todas las entradas

ORACLE: privilegios de un rol

Para saber que privilegios tiene asignado un determiando rol:

SELECT * FROM ROLE_SYS_PRIVS ;
Para ver los roles asignado a un usuario:

SELECT * FROM USER_ROLE_PRIVS;

ORA-00600: codigo de error interno, argumentos: [kkqsMMVPCTDriverForEQ:eq],

Me daba esté código de error al hacer un inner join de dos tablas con una condición como esta

on t1.year = 2003 and t2.year = 2003 and t1.otrocampo = t2.otrocampo

Lo he solucionado al cambiar esa condición por esta otra equivalente:

on t1.year = 2003 and t2.year = t1.year and t1.otrocampo = t2.otrocampo

Oracle: normalizar issn mediante sql o pl/sql

Función rápida para normalizar un issn, de modo que tenga 8 caracteres y no tenga guión:

select lpad(REGEXP_REPLACE(issn,'[^0-9X,]',''),8,'0') issn_normalizado from tabla

Si tuviesemos un campo de entrada tipo ISSN, ISSN podríamos usar algo así

select title,issn,lpad( regexp_substr( REGEXP_REPLACE(issn,'[^0-9X,]',''), '^[^ ,]*') ,8,'0') issn1,
lpad( regexp_substr( REGEXP_REPLACE(issn,'[^0-9X,]',''), '[^ ,]*$') ,8,'0') issn2
from tabla

lo único malo que si sólo hay un ISSN saldrá duplicado.

Md5 en oracle

Para calcular md5 en Oracle podemos crear la siguiente función:

create or replace

FUNCTION md5hash (str IN VARCHAR2)
    RETURN VARCHAR2
    IS v_checksum VARCHAR2(32);

    BEGIN
        v_checksum := LOWER( RAWTOHEX( UTL_RAW.CAST_TO_RAW( sys.dbms_obfuscation_toolkit.md5(input_string => str) ) ) );
        RETURN v_checksum;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
            NULL;
        WHEN OTHERS THEN
            -- Consider logging the error and then re-raise
            RAISE;
    END md5hash;

Ejemplo de uso:

select md5hash('diego') from dual;

Resultado:
078c007bd92ddec308ae2f5115c1775d

Si nuestra idea es almacenarlo como raw:

create or replace
FUNCTION md5raw (str IN VARCHAR2)
    RETURN RAW
    IS v_checksum VARCHAR2(32);

    BEGIN
       v_checksum := UTL_RAW.CAST_TO_RAW( sys.dbms_obfuscation_toolkit.md5(input_string => str) ) ;
       RETURN v_checksum;
       EXCEPTION
           WHEN NO_DATA_FOUND THEN
           NULL;
       WHEN OTHERS THEN
           -- Consider logging the error and then re-raise
           RAISE;
    END md5raw;

Ejemplo de uso (crear tabla, crear campo, insertar, consultar):

drop table tmp_die_md5;
create table tmp_die_md5(md5 raw(16) );
insert into tmp_die_md5 select md5raw('Diego') from dual;
select * from tmp_die_md5 where md5= '4FB845C67D91BCB3178498FC6FE1FEDC' ;
select * from tmp_die_md5 where md5= ( md5raw('Diego'));

Fuente:
http://www.pilcrow.nl/2010/12/md5-function-in-oracle

Oracle obtener fecha de creación de una tabla

Para obtener cuando se creó una tabla usaremos la siguiente consulta:

select object_name, created from user_objects
where object_name = 'NOMBRE_TABLA'
AND object_type = 'TABLE';

Para obtener todas las fechas de creación de todas las tablas del usuario:

select object_name, created from user_objects
where object_name in (select table_name from user_tables )
AND object_type = 'TABLE'
order by created desc;

ORACLE: ejecutar procedimiento y paquetes con el usuario actual

Si creamos un procedimiento o paquete con un usuario (UsuarioDueno), y le damos permisos de ejecución a otros (UsuarioAutorizado), el procedimiento se ejecuta por defecto como si lo ejecutase el UsuarioDueño.

Ej, supongamos que tenemos un procedimiento que cuenta las tablas del usuario ( select count(*) from user_tables ) y nos devuelve un número:

create or replace function cuenta return integer is
salida integer;
begin
execute immediate 'select count(*) from user_tables' into salida;
return salida;
end;
/

Y le damos permisos de ejecucion a UsuarioAutorizado:

grant execute on cuenta to UsuarioAutorizado;

Si ejecutamos el procedimiento:

set serveroutput on;
declare
tablas integer;
begin
tablas := cuenta();
dbms_output.put_line(tablas);
end;
/

Tanto si lo hacemos como UsuarioDueno como UsuarioAutorizado nos saldrá el mismo número de tablas. Esto es a que siempre se accede a UsuarioDueno.user_tables, cuando lo que en realidad queremos es que se acceda a UsuarioAutorizado, que es nuestro usuario actual (current user) y el usuario que está ejecutando.

En Oracle, hay dos clausulas para controlar el comportamiento:
AUTHID CURRENT_USER : ejecutar el paquete/función como si fueses el usuario que lo lanza.
AUTHID DEFINER (valor por defecto): ejecutar el procedimiento con los permisos del creador de la función.

Nosotros en este caso tenemos que decir que se ejecute como AUTHID CURRENT_USER. Para ello definimos de nuevo la función, añadiendo:

create or replace function cuenta return integer AUTHID CURRENT_USER is
salida integer;
begin
execute immediate 'select count(*) from user_tables' into salida;
return salida;
end;
/

Si estamos con un paquete, hacemos lo mismo y lo definimos justo antes del "as":

create or replace package contador AUTHID CURRENT_USER as
FUNCTION cuenta RETURN integer;
end contador
/

Con esto conseguirmos que un procedimiento se ejecute con otro usuario, el usuario actual, el que ejecuta programa.

Oracle 10G : Pivotar una tabla

Para simular la función PIVOT (introducida en oracle 11), podemos simularla en Oracle 10g con lo siguiente

Partimos de la tabla con la estructura:

( category , year, valor )

Year es el valor que queremos pivotar y tiene valores entre 2003 y 2010. Estos valores habrá que ponerlos a mano

SELECT
category,
MAX(DECODE(year,'2003',valor,NULL)) a2003
,MAX(DECODE(year,'2004',valor,NULL)) a2004
,MAX(DECODE(year,'2005',valor,NULL)) a2005
,MAX(DECODE(year,'2006',valor,NULL)) a2006
,MAX(DECODE(year,'2007',valor,NULL)) a2007
,MAX(DECODE(year,'2008',valor,NULL)) a2008
,MAX(DECODE(year,'2009',valor,NULL)) a2009
,MAX(DECODE(year,'2010',valor,NULL)) a2010
FROM tabla
where year >= 2003
group by category;

obtendremos una tabla como

category,a2003,a2004,a2005,a2006,a2007,a2008,a2009,a2010

Oracle : tamaño de todas las tablas de la base de datos

Consulta para obtener el tamaño de todas las tablas de la Base de datos:

SELECT
   owner propietario , table_name tabla , TRUNC(sum(bytes)/1024/1024) MB
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND   s.owner = i.owner
AND   s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX')
--WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;

Oracle Listener poison : solución sin rac

Para solucionar la vulnerabilidad (sin aplicar parche) en un oracle en configuración sin rac, hay que al listener.ora ($ORACLE_HOME/network/admin/listener.ora) la siguiente linea:

DYNAMIC_REGISTRATION_LISTENER = off

Más información en:

http://seclists.org/fulldisclosure/2012/Apr/204
https://forums.oracle.com/forums/thread.jspa?threadID=2381756&start=15&tstart=0
http://unaaldia.hispasec.com/2012/04/oracle-tns-poison-solucionada-4-anos.html

Oracle : crear una suma acumulada

Para crear una suma acumulada usaremos las funciones analíticas de oracle. Un ejemplo completo

--creacion de la tabla
create table gastos(nombre varchar2(30),dia integer,gasto integer);
--inserción de datos de prueba
insert into gastos values('diego',1,10);
insert into gastos values('diego',2,20);
insert into gastos values('diego',3,30);
insert into gastos values('fede',1,5);
insert into gastos values('fede',2,20);
insert into gastos values('fede',3,10);

--agrupa por nombre, ordena por día
select nombre,dia,gasto, sum(gasto) over (partition by nombre order by dia desc rows unbounded preceding) sum_acu from gastos;

Con partition dividimos en grupos.
Con order by indicamos el campo de ordenación

Instalar SQLplus de Oracle en Linux ( Suse)

Vamos a instalar el sqlplus en linux estos pasos son validos tanto para OpenSuse como SUSE linux entreprise server (SLES) .

1) Instalar Oracle instant client siguiendo los pasos 1 y 2 (con la versión que necesitemos para nuestro SO de 32 o 64 bits):

http://dieguz2.blogspot.com/2010/10/instalacion-del-instant-client-de.html

2) Descargar el RPM de sqlplus podemos encontrarlas

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

Descargamos el archivo (en nuestro caso de 64 bits) : oracle-instantclient11.2-sqlplus-11.2.0.2.0.x86_64.rpm

3) Instalamos el paquete

sudo rpm -Uvh oracle-instantclient11.2-sqlplus-11.2.0.2.0.x86_64.rpm

4) Verificamos que se ha instalado y dónde

rpm -ql oracle-instantclient11.2-sqlplus-11.2.0.2.0.x86_64

/usr/bin/sqlplus64
/usr/lib/oracle/11.2/client64/bin/sqlplus
/usr/lib/oracle/11.2/client64/lib/glogin.sql
/usr/lib/oracle/11.2/client64/lib/libsqlplus.so
/usr/lib/oracle/11.2/client64/lib/libsqlplusic.so

5) Añadimos la ruta a la libreria. podemos hacerlo de varias, yo he usado la opción C:

a) Definir la variable LD_LIBRARY_PATH a nivel de usuario, editando el archivo .bashrc

echo LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib/:$LD_LIBRARY_PATH >> ~/.bashrc

b) Definir la variable LD_LIBRARY_PATH a nivel de sistema editando el archivo /etc/profile.local:

sudo echo LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib/:$LD_LIBRARY_PATH >>

/etc/profile.local
c) Añadir una entrada al /etc/ld.so.conf.

sudo echo /usr/lib/oracle/11.2/client64/lib/ > /etc/ld.so.conf.d/oracle.conf
sudo /sbin/ldconfig.

Tras estos pasos tendremos instalado el instant client.

6) Opcionalmente podemos usar un archivo tnsnames.ora para poder usar alias de nuestras conexiones. Este archivo lo crearemos en /etc/tnsnames.ora si queremos que esté compartido por todos los usuarios. Opcionalmente, si queremos colocarlo en otra ubicación distinta tenemos que definir la variable de entorno NS_ADMIN con la ruta al tnsnames.ora. Para definir la variable podemos usar el archivo ~/.bashrc a nivel local para cada usuario o /etc/profile.local para todos.

La ruta del tnsnames.ora para un Oracle instalado (sin el instant client) $ORACLE_HOME\network\admin

Relacionado:
Conexion mediante alias : tnsnames.ora
Ejemplos de TNSNAMES.ORA y LISTENER.ORA

Instalación del Oracle Instant Client y configuración de Oci8 en php y apache en Suse

Vamos a instalar el instant client de la version 11 de oracle en una máquina son Suse Linux Enterprise Server (SLES11) versión de 64 bits. Configuraremos ademas el módulo oci8 para que se cargue en php y en apache.

En la versión Enterprise necesitaremos tener algunos paquetes de desarrollo entre otros instalaremos los siguientes:

zypper in php5-devel autoconf php5-pear

Si no están en los repositorios tendremos que instalar (sólo en la versión Enterprise, en opensuse estan por defecto) el SUSE Linux Enterprise SDK 11, más info aqui
Cómo instalar SUSE Linux Enterprise SDK 11

En este caso no tenemos oracle instalado por lo que usaremos las librerías del oracle instant client para poder conectarnos a un oracle en remoto.

1- Descargar el instant client y el devel

http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

La Version 11.2.0.2.0 tienes estas url

http://download.oracle.com/otn/linux/instantclient/112020/oracle-instantclient11.2-basic-11.2.0.2.0.x86_64.rpm
http://download.oracle.com/otn/linux/instantclient/112020/oracle-instantclient11.2-devel-11.2.0.2.0.x86_64.rpm

2- Instalar instant client de Oracle una vez descargados

rpm -Uvh oracle-instantclient11.2-basic-11.2.0.2.0.x86_64.rpm
rpm -Uvh oracle-instantclient11.2-devel-11.2.0.2.0.x86_64.rpm

(para ver los archivos y las rutas que nos harán falta para configurar oci8)

rpm -ql oracle-instantclient11.2-basic-11.2.0.2.0
/usr/lib/oracle/11.2/client64/bin/adrci
/usr/lib/oracle/11.2/client64/bin/genezi
/usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1
/usr/lib/oracle/11.2/client64/lib/libnnz11.so
/usr/lib/oracle/11.2/client64/lib/libocci.so.11.1
/usr/lib/oracle/11.2/client64/lib/libociei.so
/usr/lib/oracle/11.2/client64/lib/libocijdbc11.so
/usr/lib/oracle/11.2/client64/lib/ojdbc5.jar
/usr/lib/oracle/11.2/client64/lib/ojdbc6.jar
/usr/lib/oracle/11.2/client64/lib/xstreams.jar

3-Obtener oci8, y descomprimir

wget http://pecl.php.net/get/oci8-1.4.3.tgz
tar -zxvf oci8-1.4.3.tgz
cd oci8-1.4.3/

4- configurar el modulo con la ruta del instant client ( /usr/lib/oracle/11.2/client64/lib )

phpize
./configure -with-oci8=shared,instantclient,/usr/lib/oracle/11.2/client64/lib

5- Instalar el paquete

sudo make install

6- Verificar que se ha instalado correctamente

ls -la /usr/lib64/php5/extensions/oci8.so

7- Cambiar los permisos

sudo chmod 644 /usr/lib64/php5/extensions/oci8.so

8- Crear enlace simbólico

sudo ln -s /usr/lib/oracle/11.2/client64/lib/libnnz11.so /usr/lib64/libnnz11.so

9- Editar archivo php.ini ( /etc/php5/apache2/php.ini ) para añadir el modulo oci (que debe estar bien instalado en /usr/lib64/php5/extensions ) o bien creamos el archivo
/etc/php5/conf.d/oci8.ini para que se cargue el modulo tanto en php como en linea de comandos. En ambos casos tenemos que añadir la linea extension=oci8.so. Optamos por la segunda opción:

echo extension=oci8.so > /etc/php5/conf.d/oci8.ini

10- Reiniciar apache

rcapache2 restart

11- todo deberia estar cargado y funcionando. Podemos verificar que esta todo correcto:

11.1 ejecutando phpinfo desde linea de comandos

php -r "phpinfo();" | grep oci8

11.2 Con phpinfo en una web (con el contenido )

11.3 Comprobando si hay errores en apache

cat /var/log/apache2/error_log | grep 'oci8.so'

Fuentes:
http://en.opensuse.org/SDB:Howto_build_oci8_extension_module_for_php
http://dieguz2.blogspot.com/2007/11/opensuse-oracle-php-y-apache-oci8.html

Relacionado:
http://dieguz2.blogspot.com/2007/11/conexion-mediantes-tnsnamesora.html

Oracle Bug 4604970 : el group by no funciona

Recientemente encontre problemas con una consulta en las que usaba varios SUM y Group BY en mi version de oracle 10g. El problema era que no agregaba correctamente los datos, y que el resultado fuese correcto tenía que cambiar el orden de los campos.

Buscando por internet encontre este enlace:
http://oracle.ittoolbox.com/groups/technical-functional/oracle-sql-l/sql-result-totals-differ-with-and-without-group-by-1671408

En el que se comenta que es un bug y las soluciones. Si no aplicamos el parche la corrección para Oracle este problema puede solventarse de varias formas, que al menos a mi en mi caso concreto han funcionado cualqueira de estas dos opciones:

1) Incluir un ORDER BY en la consulta, usando uno cualquiera de los campos
2) Tal y como se indica en el link anterior, en el caso concreto de la consulta que estaba ejcutando bastó con una sóla de las alter:

alter session set "_gby_hash_aggregation_enabled" = FALSE;

o si quieremos hacer el cambio permanente

alter system set "_gby_hash_aggregation_enabled" = FALSE;

Si bien es más recomendable usar siempre los útlimos parches para nuestra BD.

Oracle: Character set válidos

La tabla V$NLS_VALID_VALUES contiene una lista con todos los valores válidos para los parámetros NLS

SELECT * FROM V$NLS_VALID_VALUES WHERE parameter = 'CHARACTERSET';

Por ejemplo usando
SELECT * FROM V$NLS_VALID_VALUES WHERE parameter = 'CHARACTERSET';
Podremos ver todos los posibles valores del character set, para funciones como convert

select convert('leño','UTF8') from dual;

y evitaremos el error "ORA-01482:juego de caracteres no soportado" por no saber como escribir el character set.

SELECT: Obtener los primeros registros en una consulta

Para que al realizar una consulta nos salga el número de registro podemos realizar lo siguiente:

select rownum , campo1, campo2 from tabla1

la salida sera algo así

1 campo1 campo2
2 campo1 campo2
3 campo1 campo2
4 campo1 campo2

Si queremos usar una consulta con un order by y que se muestre el número de columna podríamos usar esta consulta:

select rownum, campo1, campo2 from tabla1 order by campo1

La salida no es lo que queremos:

1 campo1 campo2
4 campo1 campo2
3 campo1 campo2
2 campo1 campo2

Si queremos que el número se corresponda con el orden podemos hacer esta consulta:

select rownum , campo1, campo2 from tabla1 , dual where tabla1.campo1 = dual.dummy(+)
order by campo1

1 campo1 campo2
2 campo1 campo2
3 campo1 campo2
4 campo1 campo2

Estamos realizando un "outer join" entre la tabla dual y la tabla1 usando como campo común el que deseamos para la ordenación. La salida estaŕa ordenada por el campo1 y los número serán correctos.

Si tenemos dos o más campos con los que se tiene que ordenar, podemos realizar una consulta dentro de otra:

select rownum, t1.* from (select campo1, campo2 from tabla1 ORDER BY campo1 , campo2) t1;

Si ,continuando con el caso anterior, tenemos una consulta con un Order by y queremos restringirlo a los N primeros registros podemos usar lo siguiente

Si esta es la consulta con la clausula order by:

select campo1, campo2 from tabla1 Order by campo1

Rodearemos la consulta con otro select y le aplicaremos la restricción en cuanto al número de columnas:

select * from (select campo1, campo2 from tabla1 Order by campo1) where rownum < 100

por regla general:

select * from ( tuconsulta) where rownum < 100

Script de arranque automático de oracle en Linux

Si necesitamos que nuestra instancia de oracle arranque automáticamente cuando se reinicia o arranca el servidor / ordenador. Los siguientes pasos deben de funcionar tanto para oracle 10g como para 11g. Están realizados para Opensuse por lo que la ubicación de lagunos archivos podría diferir dependiendo de la instalación (esencialmente el init.d o los runlevels).

Debemos hacer lo siguiente en Opensuse como ROOT:

1) Editar el archivo el archivo /etc/oratab
En dicho archivo tendremos que poner algo similar a esto =

miinstancia:/home/dieguz/oracle/product/10.2.0/db_1:Y

Su sintaxis es

[instancia]:[ORACLE_HOME]:[Y o N]

La N indica que no queremos que se arranque automáticamente. La Y indica que queremos que se arranque automáticamente.

2) Creamos el archivo /etc/init.d/oracle como ROOT (desde consola podemos ejecutar cat > /etc/init.d/oracle ) :

#!/bin/sh
# chkconfig: 345
# descripcion: Oracle auto start-stop script.
#
# Set ORA_HOME al quivalente $ORACLE_HOME del sistema
# Set ORA_OWNER Al id del usuario linux que es el propietario de la BD

ORA_HOME=/home/dieguz/oracle/product/10.2.0/db_1
ORA_OWNER=dieguz

if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi

case "$1" in
'start')
   # Arranca la Base de Datos
   su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
   ;;
'stop')
   # Parar la Base de Datos
   su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
   ;;
esac

3) Damos los permisos adecuados al fichero:

chmod 755 /etc/init.d/oracle

4) Añadimos el script a los niveles de ejecución 345:

chkconfig oracle 345

5) Si usamos la versión 10g Release 2 , tendremos el siguiente mensaje de error:

Failed to auto-start Oracle Net Listener using /ade/vikrkuma_new/oracle/bin/tnslsnr

Tenemos que editar el archivo $ORACLE_HOME/bin/dbstart y modificar la línea :

ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle

y poner

ORACLE_HOME_LISTNER=$ORACLE_HOME

Si no tenemos definida la variable de entorno $ORACLE_HOME ponemos la ruta a pelo
/home/dieguz/oracle/product/10.2.0/db_1

6) Ahora automáticamente debería arrancarse oracle al reiniciar el sistema. Si quieremos arracancarla o pararla manualmente podemos usar el comando:

/etc/init.d/oracle start

o para pararla con

/etc/init.d/oracle stop

Fuente:
http://www.oracle-base.com/articles/linux/AutomatingDatabaseStartupAndShutdownOnLinux.php

Update de tablas con inner join

Normalmente las consultas de update que contienen inner dan alguno de los siguientes errores:
SQL Error: ORA-00933: comando SQL no terminado correctamente
ORA-00933: SQL command not properly ended

Una opción es convertir estas sentencias en otras.

Ejemplo de consulta de update con inner que NO funciona:

UPDATE tabla1
SET columna1 = tabla2.columna1
from tabla1 INNER JOIN tabla2
ON (tabla1.columna2 = tabla2.columna2) AND (tabla1.columna3 = tabla2.columna3)

Ejemplo de consulta que ha sido transformada:

UPDATE tabla1
SET columna1 = (
select tabla2.columna1 from tabla2
WHERE (tabla1.columna2 = tabla2.columna2) AND (tabla1.columna3 = tabla2.columna3)
)

En realidad la hemos convertido en una subselección o un filtro. El problema está en que se va a realizar la consulta de select por cada una de las filas de la tabla. Esta consulta sólo es util para tablas que no tienen excesivas filas

Si necesariamente necesitamos realizar el update de la tabla mediante el join la sintaxis ha de ser la siguiente:

UPDATE
(select
tabla1.columna1 old_valor ,
tabla2.columna2 new_valor
from tabla1 INNER JOIN tabla2
ON (tabla1.columna4 = tabla2.columna4) AND (tabla1.columna3 = tabla2.columna3))
SET old_valor = new_valor

Para que esto pueda realizarse las dos tablas que participan en el inner tienen que tener la constaint unique o primary key:
alter table tabla1 add constraint tabla1_u unique (columna4,columna3);

En el caso de no tenerla podríamos forzar la sentencia con el hint /*+BYPASS_UJVC*/ .

Para poder usar la sentencia SET, forzosamente tendremos que dale nombre a las columnas implicadas (en este caso con los nombres old_valor y new valor), ya que no podemos usar los nombres de las tablas (no podemos usar tabla2.columna2 como valor asignado).

Oracle: registrar esquema XSD

Para registrar un esquema:
1) Nos aseguramos que tenemos los permisos adecuados otorgandolos como system:

grant create any directory, drop any directory, connect, resource, alter session, create view to usuario_oracle

PD: quizá tambien necesitemos el rol de XDBADMIN

grant XDBADMIN to usuario_oracle

2) Creamos en oracle un directorio, que apunte donde tenemos nuestro esquema

create or replace directory XMLDIR as '/home/dieguz2/xsd'

3) Registramos el esquema:

BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'esquema.xsd',
SCHEMADOC => bfilename('XMLDIR','esquema.xsd'),
CSID => nls_charset_id('AL32UTF8'));
END;

4) Ejemplo de como crear una tabla

create table TABLA_XML of xmltype XMLSchema "esquema.xsd" element "elemento_raiz_xml";

5) Ejemplo de inserción de un elemento:

Insert into TABLA_XML values( XMLType(bfilename('XMLDIR','archivoxml.xml') ,nls_charset_id('AL32UTF8')));

Papelera de reciclaje: uso de recyclebin

En la version 10g oracle introdujo el concepto de papelera de reciclaje ( recicle bin ), cuando hacemos un "drop" de una tabla, la tabla y los indices son arrojados a esta papelera. En realidad únicamente se renombra el objeto, por lo que sigue ocupando el mismo espacio que antes:

1) Para ver el contenido de la papelera:

SELECT * FROM recyclebin;
SHOW RECYCLEBIN;
SELECT object_name, original_name, ts_name, type FROM user_recyclebin;

2) Para borrar el contenido de la papelera

PURGE RECYCLEBIN;

3) Para activar/desactivar la papelera (usar ON/OFF):

ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;

4) Para borrar una tabla que está en la papelera:

PURGE TABLE mitabla

SQLLOADER: unicode (UTF8)

Si cargamos un archivo con carácteres UTF-8 txt o csv a través de sqlloader (Cómo convertir archivos a UTF-8) puede ocurrir que al ver la tabla donde la insertamos no se hayan insertado correctamente algunos carácteres. Para solucionarlo, si nuestra base de datos fue creada con unicode podemos cargarlos correctamente añadiendo la sentencia CHARACTERSET UTF8. Por ejemplo:

LOAD DATA
CHARACTERSET UTF8
INFILE *
INTO TABLE "PATRONESUNICODE"
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED '"'
TRAILING NULLCOLS
(IDP,CC,ORGANIZACION,ORIGEN)
begindata
8008;"ES";"Universidad de Granada";"UNESCO";14027
8009;"ES";"Universidad de Huelva";"UNESCO";14270
8010;"ES";"Universidad de Jaén";"UNESCO";14319