Objetivo:
Precisamos recriar alguns DBLinks porem não temos as senhas dos usuários utilizados nesses DBLinks, para as versões 8i e 9i podemos utilizar SYS.LINK$, mas na versão 10g o campo de senha esta criptografado, nesse caso teremos que utilizar o DATAPUMP.
Ambiente:
SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 9.2.0.4.0 e Oracle Database 10.2.0.5.0
Tópicos: Base 8i/9i
- Setup;
- Listar o conteudo da tabela SYS.LINK$;
- Gerar o comando para criação do DBLink;
- Recriar o DBLink em outro esquema;
1. Setup
Vamos criar um DBLink numa base 9i para o usuário SCOTT que irá acessar tabelas do esquema HR:
SQL> conn scott/oracle
Connected.
SQL> create database link HR_LINK connect to hr identified by oracle USING 'LAB1';
Database link created.
SQL> select count(*) from EMPLOYEES@HR_LINK;
COUNT(*)
----------
107
SQL>
.
2. Listar o conteudo da tabela SYS.LINK$
SQL> set lines 120 pages 66
col name for a25
col host for a10
col userid for a10
col password for a20
SELECT NAME,HOST,USERID,PASSWORD FROM SYS.LINK$;
NAME HOST USERID PASSWORD
------------------------- ---------- ---------- --------------------
HR_LINK.US.ORACLE.COM LAB1 HR ORACLE
SQL>
.
3. Gerar o comando para criação do DBLink
SQL> SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
2 ||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
3 ||'connect to '|| L.USERID || ' identified by "'
4 ||L.PASSWORD||'" using ''' || L.host || ''';' AS "CREATE DBLINKS"
5 FROM sys.link$ L, sys.user$ U
6 WHERE L.OWNER# = U.USER#;
CREATE DBLINKS
-------------------------------------------------------------------------------------
create database link
SCOTT.HR_LINK.US.ORACLE.COM
connect to HR identified by "ORACLE" using 'LAB1';
SQL>
.
4. Recriar o DBLink em outro esquema
SQL> conn teste/oracle
Connected.
SQL> create database link
2 HR_LINK.US.ORACLE.COM
3 connect to HR identified by "ORACLE" using 'LAB1';
Database link created.
SQL> select count(*) from EMPLOYEES@HR_LINK;
COUNT(*)
----------
107
SQL>
.
Tópicos: Base 10g
- Setup;
- Listar o conteudo da tabela SYS.LINK$;
- Export do DBLink com o DATAPUMP;
- Import do DBLink com o DATAPUMP;
- Verificar se novo DBLINK esta funcionando;
1. Setup
Vamos criar um DBLink numa base 10g para o usuário SCOTT que irá acessar tabelas do esquema HR:
SQL> conn sys/oracle as sysdba;
Connected.
SQL> grant create database link to scott;
Grant succeeded.
SQL> conn scott/oracle
Connected.
SQL> create database link HR_LINK connect to hr identified by oracle USING 'LAB1';
Database link created.
SQL> select count(*) from EMPLOYEES@HR_LINK;
COUNT(*)
----------
107
SQL>
.
2. Listar o conteudo da tabela SYS.LINK$
Quando listamos a tabela SYS.LINK$ , verificamos que a password não aparece no formato caracter:
SQL> set lines 120 pages 66
col name for a40
col host for a10
col userid for a10
col password for a20
SELECT NAME,HOST,USERID,PASSWORD FROM SYS.LINK$;
NAME HOST USERID PASSWORD
---------------------------------------- ---------- ---------- --------------------
HR_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM LAB1 HR
SQL>
.
3. Export do DBLink com o DATAPUMP
Para recriar o DBLink em outro esquema podemos utilizar o DATAPUMP com a opção INCLUDE para selecionar somente DBLinks:
[oracle@odbsrv02 ~]$ expdp system/oracle SCHEMAS=scott INCLUDE=DB_LINK DUMPFILE=scott_dblink.dmp DIRECTORY=DATA_PUMP_DIR
Export: Release 10.2.0.5.0 - Production on Wednesday, 07 July, 2010 10:18:48
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** SCHEMAS=scott INCLUDE=DB_LINK DUMPFILE=scott_dblink.dmp DIRECTORY=DATA_PUMP_DIR
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/DB_LINK
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/product/10.2.0/db_1/admin/lab1/dpdump/scott_dblink.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:19:01
[oracle@odbsrv02 ~]$
.
4. Import do DBLink com o DATAPUMP
No import podemos utilizar a opção REMAP_SCHEMA para indicar o esquema onde queremos que o DBLink seja criado:
[oracle@odbsrv02 ~]$ impdp system/oracle REMAP_SCHEMA=scott:tstuser INCLUDE=DB_LINK DUMPFILE=scott_dblink.dmp DIRECTORY=DATA_PUMP_DIR
Import: Release 10.2.0.5.0 - Production on Wednesday, 07 July, 2010 10:21:11
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** REMAP_SCHEMA=scott:tstuser INCLUDE=DB_LINK DUMPFILE=scott_dblink.dmp DIRECTORY=DATA_PUMP_DIR
Processing object type SCHEMA_EXPORT/DB_LINK
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 10:21:15
[oracle@odbsrv02 ~]$
.
5. Verificar se novo DBLINK esta funcionando
SQL> conn tstuser/oracle
Connected.
SQL> select count(*) from EMPLOYEES@HR_LINK;
COUNT(*)
----------
107
SQL>
