Skip to content

Private DB Link Creation By SYS

August 28, 2012

Create Private DB Link’s as SYS or DBA Users:
===================================================

1) 1st scenario which generally fails of creating Private DB Links as SYS

2) 2nd scenario to Over come the above issue as SYS

 Users Involved:
=============
 
User Name 1 : SYS   ( DBA who needs to create Private DB links in other Schema (SCOTT) whose password is not known to DBA)
 

 User Name 2: SCOTT (This is the User Where DB link need to be created Or who will be owning the DB Link, create by DBA)

 User Name 3: TEST (This is the User where DB link will be pointed to access TEST Schema Objects)

 SQL> create user scott identified by tiger;

 User created.

 SQL> grant create database link, create session, create table to scott;

 Grant succeeded.

 SQL> conn scott/tiger

Connected.

SQL>

SQL> show user

USER is “SCOTT”

SQL>

 SQL> conn / as sysdba

Connected.

 SQL> set line 200

SQL> col OBJECT_NAME for a30

 SQL> show user

USER is “SYS”

 SQL> select owner, object_name, object_type from dba_objects where owner=’SCOTT’;

 no rows selected

 SQL> select owner, object_name, object_type from dba_objects where owner=’TEST’;

 OWNER                          OBJECT_NAME                    OBJECT_TYPE
—————————— —————————— ——————-
TEST                           DEPT                           TABLE
TEST                           PK_DNO                         INDEX
TEST                           T1                             TABLE
TEST                           MTEST                          TABLE
TEST                           EMP1                           TABLE
TEST                           MY_EMP                         TABLE
TEST                           EMP                            TABLE
 
7 rows selected.
 
************************************************************************
Scenario 1) Using alter session set current_schema Which Fails
************************************************************************

select owner, object_name, object_type from dba_objects where owner=’SCOTT’;

 SQL> show user

USER is “SYS”

 SQL> alter session set current_schema=SCOTT;

 Session altered.

 SQL> show user

USER is “SYS”

 SQL> set line 200

SQL> col OBJECT_NAME for a30

SQL> select owner, object_name, object_type from dba_objects where owner=’SCOTT’;

 no rows selected

 SQL> create table EMP (ename varchar2 (20));

 Table created.

 SQL> select owner, object_name, object_type from dba_objects where owner=’SCOTT’;

 OWNER                          OBJECT_NAME                    OBJECT_TYPE
—————————— —————————— —————–
SCOTT                          EMP                            TABLE
 
 SQL> CREATE DATABASE LINK TEST_LNK
  2  CONNECT TO test IDENTIFIED BY test
  3  USING ‘d041_oltp’;
 
CREATE DATABASE LINK TEST_LNK
                     *
ERROR at line 1:
ORA-01031: insufficient privileges

 

***Wondering To See Table got created but Private DB Link creation failed ***

———————————————————————————————
Checking Whether Parameter for DB Link Creation is working fine:
Parameters like connect string (d041_oltp) and Password for TEST user
———————————————————————————————

 SQL> conn test/test

Connected.

 SQL> !tnsping d041_oltp
 
 TNS Ping Utility for Solaris: Version 11.2.0.3.0 – Production on 23-AUG-2012 03:55:07
 Copyright (c) 1997, 2011, Oracle.  All rights reserved.
 Used parameter files:
/u01/app/oracle/product/11.2.0.3/network/admin/sqlnet.ora 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = maludev-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = d041) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))))
OK (10 msec)

(Password and TNS string works fine as ABOVE)

 

************************************************************************
Scenario 2) Using Procedure to Private DB Link in others Schema from SYS or DBA
************************************************************************

You need to know the password of schema to whom the DBLink point to (our case test/test)

 SQL> conn / as sysdba

Connected.

 SQL> show user

USER is “SYS”

 SQL> grant create database link, create session to scott;

 SQL> l
create or replace procedure scott.proc_db_lnk_create ( Lnk_name varchar2,uname varchar2, passwd varchar2, tns_string varchar2)
is
begin
execute immediate ‘create database link ‘||Lnk_name ||’ connect to ‘||uname||’ identified by ‘||passwd ||’ using ”’||tns_string||””;
end;
/

 Procedure created.

 SQL> set line 200

SQL> col OBJECT_NAME for a30

SQL> select owner,object_name,object_type from dba_objects where owner=’SCOTT’;

 OWNER                          OBJECT_NAME                    OBJECT_TYPE
—————————— —————————— —————–
SCOTT                          EMP                            TABLE
SCOTT                          PROC_DB_LNK_CREATE             PROCEDURE
 

SQL>

 col owner for a10

col db_link for a15

col username for a10

SQL> select owner,db_link,username from dba_db_links where owner=’SCOTT’;

 no rows selected

 SQL> show user

USER is “SYS”

 SQL> desc scott.PROC_DB_LNK_CREATE
PROCEDURE scott.PROC_DB_LNK_CREATE
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 LNK_NAME                       VARCHAR2                IN
 UNAME                          VARCHAR2                IN
 PASSWD                         VARCHAR2                IN
 TNS_STRING                     VARCHAR2                IN
 

 SQL> exec scott.PROC_DB_LNK_CREATE(‘tst_lnk’,’test’,’test’,’d041_oltp’);

 PL/SQL procedure successfully completed.

 SQL> select owner,db_link,username from dba_db_links where owner=’SCOTT’;

 OWNER      DB_LINK         USERNAME
———- ————— ———-
SCOTT      TST_LNK         TEST
 

 SQL> select owner,object_name,object_type from dba_objects where owner=’SCOTT’;

 OWNER      OBJECT_NAME                    OBJECT_TYPE
———- —————————— ——————-
SCOTT      EMP                            TABLE
SCOTT      PROC_DB_LNK_CREATE             PROCEDURE
SCOTT      TST_LNK                        DATABASE LINK
 
TO Test the Scott’s DB Link from SYS:
======================================

SQL> show user

USER is “SYS”

 SQL> select owner,object_name,object_type from dba_objects where owner=’SCOTT’;

 OWNER      OBJECT_NAME                    OBJECT_TYPE
———- —————————— ——————-
SCOTT      EMP                            TABLE
SCOTT      PROC_DB_LNK_CREATE             PROCEDURE
SCOTT      PROC_DB_LNK_DROP               PROCEDURE
SCOTT      TST_LNK                        DATABASE LINK
 

SQL>

 create or replace procedure scott.proc_db_lnk_test ( Lnk_name varchar2)
is
TYPE lnkCurTyp IS REF CURSOR;
cur_Lnk_name lnkCurTyp;
v_tabname varchar2(30);
 
begin
 
OPEN cur_Lnk_name For ‘select TABLE_NAME from user_tables@’||Lnk_name;
LOOP
      FETCH cur_Lnk_name into v_tabname;
      EXIT WHEN cur_Lnk_name%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(‘Table Name is :’ || v_tabname );
END LOOP;
 
CLOSE cur_Lnk_name;
 
END;
/

Procedure created.

SQL> select owner,object_name,object_type from dba_objects where owner=’SCOTT’;

OWNER      OBJECT_NAME                    OBJECT_TYPE
———- —————————— ——————-
SCOTT      EMP                            TABLE
SCOTT      PROC_DB_LNK_CREATE             PROCEDURE
SCOTT      PROC_DB_LNK_DROP               PROCEDURE
SCOTT      PROC_DB_LNK_TEST               PROCEDURE
SCOTT      TST_LNK                        DATABASE LINK
 

SQL> set serveroutput on

SQL> exec scott.proc_db_lnk_test (‘TST_LNK’);

Table Name is :DEPT

Table Name is :T1

Table Name is :EMP1

Table Name is :MY_EMP

Table Name is :EMP

Table Name is :MTEST

 PL/SQL procedure successfully completed.

 Or as Scott user
===================

SQL> select TABLE_NAME from user_tables@TST_LNK;

TABLE_NAME
——————————
DEPT
T1
EMP1
MY_EMP
EMP
MTEST
 
6 rows selected.
 
 
 To Drop the SCOTT’s Private DB Link as SYS
===========================================
SQL> select owner, object_name,object_type from dba_objects where owner=’SCOTT’;
OWNER      OBJECT_NAME                    OBJECT_TYPE
———- —————————— ——————-
SCOTT      EMP                            TABLE
SCOTT      PROC_DB_LNK_CREATE             PROCEDURE
SCOTT      TST_LNK                        DATABASE LINK
SCOTT      PROC_DB_LNK_DROP               PROCEDURE
 

SQL> show user
USER is “SYS”
SQL> drop DATABASE LINK SCOTT.TST_LNK;
drop DATABASE LINK SCOTT.TST_LNK
*
ERROR at line 1:
ORA-02024: database link not found

SQL> create or replace procedure scott.proc_db_lnk_drop ( Lnk_name varchar2)
is
begin
execute immediate ‘Drop database link ‘||Lnk_name;
end;
/
 
Procedure created.
 

SQL> exec scott.PROC_DB_LNK_DROP(‘TST_LNK’);

PL/SQL procedure successfully completed.

SQL> select owner,object_name,object_type from dba_objects where owner=’SCOTT’;

OWNER      OBJECT_NAME                    OBJECT_TYPE
———- —————————— ——————-
SCOTT      EMP                            TABLE
SCOTT      PROC_DB_LNK_CREATE             PROCEDURE
SCOTT      PROC_DB_LNK_DROP               PROCEDURE

 =========================EOF Scenario 2 ==================

 For more Information:
=========================

http://www.akadia.com/services/ora_database_links.html

Known Issues:
==========================

 ORA-00600: [Kzdlk_zt2 Err] While Selecting Using a Database Link [ID 456320.1]

 ALTER DATABASE LINK

http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_1005.htm

 SQL> CREATE DATABASE LINK TEST_LNK
  2  CONNECT TO test
  3  IDENTIFIED BY values ‘7A0F2B316C212D67’
  4  USING ‘d041_oltp’;
 
Database link created.
 
SQL> select count(1) from user_tables@TEST_LNK;
select count(1) from user_tables@TEST_LNK
                                 *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzdlk_zt2 err], [18446744073709551603], [], [], [], [], [], [], [], [], [], []
SQL> CREATE DATABASE LINK TEST_LNK
  2  CONNECT TO test
  3  IDENTIFIED BY values ‘S:222D22D87C951B76A52A993D1ED0C2A3CDF612D81E847A80D2C9BCA3A1F3’
  4  USING ‘d041_oltp’;
IDENTIFIED BY values ‘S:222D22D87C951B76A52A993D1ED0C2A3CDF612D81E847A80D2C9BCA3A1F3’
                     *
ERROR at line 3:
ORA-02153: invalid VALUES password string
 

To Download the same in PDF format , click here : Create Private DB Link as SYS User

Advertisements

From → Oracle

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: