Anuncios en tutorial de programación PLSQL

domingo, 15 de noviembre de 2015

Acceso remoto mediante DBLINK a una base de datos Oracle

Accesso remoto a una base de datos Oracle en PL/SQL y SQLLa manera más sencilla de acceder desde una base de datos Oracle a tablas y vistas (views) de otra base de datos Oracle es mediante el uso de un DBLink (enlace a base de datos). No obstante, en muchos casos puede no ser recomendable la utilización de DBLinks, ya que el acceso a tablas y vistas remotas puede provocar importantes problemas de rendimiento en ambas bases de datos, tanto la remota como la local. En la mayoría de los casos estos problemas de rendimiento se deben a la imposibilidad de utilizar los índices de las tablas remotas.

Los DBLinks se crean en la base de datos local utilizando el comando PL/SQL o SQL CREATE DATABASE LINK. Obviamente, el usuario que ejecute dicho comando debe tener los permisos necesarios para poder hacerlo. La sintaxis del comando SQL mencionado es como sigue:

CREATE [PUBLIC] DATABASE LINK Nombre_dblink
CONNECT TO Nombre_usuario INDENTIFIED BY Contraseña
USING 'Cadena_conexion';

En donde:

Nombre_dblink es el nombre del DBLink.

Nombre_usuario y Contraseña son los identificadores que utilizará el DBLink para conectarse a la base de datos remota. Por lo tanto, el acceso a las tablas y vistas de la base de datos remota se hará con los permisos que dicho usuario tenga sobre ellas en dicha base de datos. Si se omiten estos valores, entonces el acceso a la base de datos remota se hará con el mismo nombre de usuario y contraseña con el que el usuario en cuestión esté conectado a la base de datos local.

Cadena_conexion identifica a la base de datos remota. Puede ser el nombre de la instancia, si ésta está definida en el archivo tnsnames.ora de la base de datos origen, o también, para la versión 8 de la base de datos Oracle o posteriores, podemos utilizar directamente la definición TNS (esto posibilita que no tengamos que modificar el fichero tnsnames.ora).

Por otro lado, si definimos el DBLink como PUBLIC, todos los usuarios tendrán acceso al mismo.

A continuación os pongo algunos ejemplos del comando CREATE DATABASE LINK:

CREATE DATABASE LINK dbl_bd001
CONNECT TO repadmin IDENTIFIED BY reppass1
USING 'bd001';

CREATE PUBLIC DATABASE LINK dbl_bd001
CONNECT TO repadmin IDENTIFIED BY reppass1
USING '(description = 
(address = 
(protocol = tcp)
(host = bd.miempresa.com)
(Port = 1521) )
(connect_data = 
(sid = bd001) )
)';

Una vez creado el DBLink, para referenciar una tabla o vista de la base de datos remota se ha de indicar el nombre de la tabla o vista, concatenado con el carácter "@" y el nombre que se le ha dado al DBLink. Las tablas y vistas remotas podrán usarse en consultas (sentencias SELECT) y, si la base de datos Oracle utiliza el sistema de bases de datos distribuidas, entonces se podrán ejecutar también sentencias SQL o PLSQL del tipo DELETE, INSERT, UPDATE o LOCK TABLE. Asimismo, se pueden crear sinónimos para abreviar la forma de referenciar a las tablas y vistas remotas. A continuación os pongo algunos ejemplos de sentencias:

SELECT * FROM Nombre_tabla_remota@Nombre_dblink;

INSERT INTO Nombre_tabla_remota@Nombre_dblink (campo1)
VALUES ('prueba');

UPDATE Nombre_tabla_remota@Nombre_dblink;
SET campo1 = 'actualizado'
WHERE campo1 = 'prueba';

CREATE SYNONYM tremota
FOR Nombre_tabla_remota@Nombre_dblink;

SELECT * FROM tremota;

Nota: Existe otro concepto que no he explicado aquí y es el de los DBLinks compartidos (comando CREATE SHARED DATABASE LINK), el que necesite más información sobre este concepto puede mirar en el manual de Oracle sobre sistemas de base de datos distribuidos (Oracle Distributed Database Systems). Por otro lado, también es posible acceder mediante DBLink a bases de datos remotas que no sean Oracle. Para crear un DBLink de este tipo, y antes de que nadie me pregunte, podéis realizar una búsqueda en Internet de las palabras: "Oracle Heterogeneous Services". Quizá más adelante escriba algún artículo con referencia a estos dos temas.

20 comentarios:

Moshi V2.0 dijo...

Hola Pepelu.

Muchas gracias por tu respuesta.
Y si me quedo claro el uso de los DBLINKS, ahora habrá que hacer las respectivas pruebas.

Si tengo alguna otra duda o se me dificulta algo, espero poder contar con tu apoyo.

Cuidate y estamos en contacto.

Saludos

majin_antonio dijo...

Hola,
Hay alguna manera de saber el tipo de dato de una tabla externa a través de dblink?

Algo parecido a: Nombre_tabla_remota.nombre_campo%type@Nombre_dblink

Pepelu dijo...

Hola Majín Antonio,

Si el usuario con el que se configura el DBLink tiene accesso a las tablas remotas DBA_TAB_COLUMNS y DBA_TABLES, se podría acceder a la información que indicas. Mira la siguiente entrada del blog: DBA_TABLES.

Pero si lo que quieres es hacer referencia al tipo de dato desde el código PL/SQL utilizando:

Nombre_variable Nombre_tabla_remota@Nombre_dblink.Nombre_columna%type;

Pues la verdad es que no estoy seguro. Haz una prueba y comprueba si el procedimiento compila correctamente.

Jazmín dijo...

¡Hola! Les cuento que en www.becascontrolf.com.ar ya se pueden anotar y aplicar para becas de programacion java, oracle, y muchas más.

Martín dijo...

Hola, tengo una consulta para hacerles:
Cree un procedimiento en el cual solo se ejecuta un SELECT INTO a una tabla a traves de un dblink y me pasa que al finalizar la ejecucion de dicho prc, queda pendiente de que un commit o rollbak. Por que puede pasar eso?
Me pasa solo en consultas en las que uso un dblink, en tablas locales no pasa nada.
Espero que me puedan ayudar, gracias...

Pepelu dijo...

Martín, sinceramente no entiendo cual es el problema. ¿El procedure se te queda colgado a la espera de la ejecución de un commit y no termina?

Carlos dijo...

Hola pepelu

Dejame decirte que este blog me ha resultado muy util en mi trabajo y cuando me surge algun problema lo consulto aqui, si no encuentro la solucion toca google....

Ahora me encuentro en un dilema ya que no encuentro lo siguiente:

En la empresa donde estoy se trabaja con la suite de oracle, y en un desarrollo que tengo pendiente necesito realizar un juego de valores pero las tablas estan en otra instancia y ya tengo el query armado en el pl/sql junto con el dblink, mi pregunta es, puedo colocar ese query con las tablas de la otra instacia directamente o debo crear una vista q me ejecute el llamado al dblink?

Pepelu dijo...

Carlos, puedes utilizar la consulta directamente, no es necesario crear ningún view, incluso puedes utilizar en la misma consulta tablas de la base de datos remota y de la local.

De todas formas por temas de rendimiento, si tienes que acceder a varias tablas remotas desde la misma consulta, puede resultar muy conveniente definir una view en la base de datos remota y acceder sólo a la view y no a todas las tablas remotas, más que nada porque si ejecutas la consulta remotamente, desde la base de datos local no serán visibles los índices de las tablas remotas.

Carlos dijo...

Hola pepelu, gracias por tu respuesta, en realidad la consulta solo accede a 2 tablas y al momento de ejecutar el select por el pl/sql me trae 1264 registros en 3 segundos.

segun tu experiencia se puede trabajar esa select asi o mejor si se crea la vista?

Pepelu dijo...

Aunque la respuesta ahora es rápida, pensando en un posible crecimiento de ambas tablas mi consejo es que creéis una vista en la base de datos remota y que accedáis a dicha vista en lugar de utilizar las dos tablas directamente.

Fabian dijo...

el uso de los dblinks es buenaso en aplicaciones q no tenga acceso simultaneos, por tengo un aplicativo en donde se cunsulta de forma concecutiva varias tablas y por cada consulta me deja a la tabla en row_exclusivo y eso me esta causando el problema en cuestien de rendimiento de la bda sabes como puedo solucionar eso

Pepelu dijo...

Hola Fabian, mi sugerencia es que crees una vista en la base de datos Oracle remota y que accedas a la vista en vez de a la tabla directamente.

Oscar dijo...

Gracias por el artículo.
Pero tengo una pregunta. Al hacer una consultas en la que intervengan varias tablas (todas de la base de datos remota). como actúa oracle?
trae los datos de las tablas y la base de datos en al que me conecto yo hace el cruce o la base de datos remota hace todo el cálculo y solo me devuelve el resultado?

Entiendo que si solo hace referencia a tablas de la base de datos remota, el motor de la base de datos remota debe hacer todo el trabajo y solo traerme el resultado. Pero no estoy muy seguro.

Gracias por anticipado.

Pepelu dijo...

Hola Oscar, el trabajo se hace siempre localmente por lo que en tu caso sería recomendable crear una vista (CREATE VIEW) en la base de datos remota y llamar desde la base de datos Oracle local a dicha vista.

También es posible utilizar el hint DRIVING_SITE(nombre_tabla) para que el trabajo se haga en la base de datos remota en vez de en la local.

Oscar dijo...

Gracias por la respuesta.

De todas maneras quiero plantear la pregunta de otra manera.
Tengo 2 tablas en la base de datos remota, una de 10 millones de registros, si hay un cruce entre las 2 tablas (cruce que se supone que solo me tendría que devolver unos 100 registros nada más). Lo que hace oracle es traerme los datos de las 2 tablas por el dblink (más de 10 millones de registros) y hacerme el cruce en la base de datos local?
o hacer el cruce en la base de datos remota y solo traerme el resultado.
Ten en cuenta que en la consulta únicamente actúan esas dos tablas, no ninguna referencia a ningún objeto de la base de datos local.

Muchas gracias. Es un Blog muy útil y me ha ayudado mucho.

Pepelu dijo...

Óscar, el cruce se haría localmente salvo que indiques lo contrario utilizando el hint DRIVING_SITE(nombre_tabla).

Omar dijo...

Hola pepelu,

Necesito accesar datos en SQL Server, esto lo hago através de un dblink llamado easy_pega y la tabla se llama super_user_input, es decir, cuando hago una consulta lo hago de la siguiente manera, por ejemplo:

select * super_user_input@easy_pega;

El problema es al traer los campos, me trae sólo los campos del tipo fecha y los numéricos, los varchar no. Por ejemplo, si realizo la consulta select "fecha_entrada" from "super_user_input"@easy_pega" lo trae sin ningun problema, en cambio si hago la consulta:

Select "nombre_usr" from "super_user_input"@"easy_pega" arroja un error de "Identificador Invalido". Es más, al realizar un describe de la tabla solo muestra los campos fecha y numericos, no muestra los tipo varchar, mientras que en SQL Server si me muestra todos los campos, varchar incluidos.

Cualquier ayuda es valida

Gracias y saludos,

Omar

Pepelu dijo...

Hola Omar,

Las conexiones de bases de datos Oracle a bases de datos SQL server experimentan algunos problemas de compatibilidad.

Así, por ejemplo, los campos de tipo VARCHAR no mapean correctamente en Oracle y este es el motivo de que no los veas.

Deberás utilizar campos CHAR que si que mapean correctamente.

VEAME dijo...

Hola Pepilu
Gracias por tu aporte, por favor ayudame con esta consulta: ya defini el dblink y no tengo problema, cree una vista con tablas de mi base local y una tabla de la base remota, pero al hacer el select * from esquema.vista me presenta la información sin problema pero en la herramienta que utilizo para hacer el select (no es el toad) se activa un botón que normalmente pasa cuando hago un update o insert como si esperara que haga un commit; luego que hago el commit; entonces ya se desaparece; mi preocupación es que si en verdad está esperando el commit entonces quiere decir que el rendimiento de la base va a ser lenta.
En resumen cuando se hace un "select" a tablas remotas, espera o no espera el commit ??

José Luis Pérez dijo...

Hola Veame,
Después de ejecutar un SELECT, ya sea solo atacando tablas locales como atacando tablas remotas, no requiere que se ejecute un COMMIT.