Forum Stats

  • 3,874,655 Users
  • 2,266,761 Discussions
  • 7,911,929 Comments

Discussions

to create database links in a different schema

user12172973
user12172973 Member Posts: 5 Blue Ribbon

Right now there is no way to create a database link in another schema, not even as dba.

Why not add a clause like "owner by" to specify the schema where to create it?

Of course a "create any database link" should be necessary, likewise the creation of tables, indexes and son on

Regards

user12172973Sven W.
2 votes

Active · Last Updated

Comments

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,405 Bronze Crown

    there is a way. you can create a procedure that uses dynamic sql to create dblink in your target schema and run it:

    conn mustafa/mustafa
    
    create or replace procedure abc.sp_create_db_link as 
    begin
      execute immediate 'create database link xlink connect to user1 identified by pass1 using ''localhost:1521/testdb''';
    end;
    /
    
    exec abc.sp_create_db_link;
    
    select db_link from dba_db_links where owner = 'ABC';
    DB_LINK
    --------
    XLINK
    

    MUSTAFA user is creating a db link in ABC schema. user ABC is the target user that you want to create db link. Also, user ABC must have CREATE DATABASE LINK privileges directly (not via a role like DBA).

  • user12172973
    user12172973 Member Posts: 5 Blue Ribbon

    Of course, but it requires the direct privilege, not through a role, just another small complication that could be avoided implementing what I suggested, really a very small work