Search code examples
oracle11gdblinkexternal-tables

External Table via DBLINK

I am trying to select a set of data on external table via DBLINK. I am getting, however, this error:

ORA-06564: object MY_EXTERNAL_DIR does not exist ORA-02063: preceding line from foo

foo is my remote database link. And no, there is no directory on that disc.. This directory is located on the remote db.

I have created a VIEW on local DB and tried to access it remotely, but it didn 't help.

Am I wasting my time?

Thank you.

Solution

  • I have tested this and it is possible to access an external table over a database link. I think that you will need to grant read and write permissions on the (oracle database object) directory to the user that you are connecting the db link to if this isn't the user that owns the external table.

    Assuming that you have a user bob that owns a working external table called ext_tab in the xtern_data_dir directory on a remote database dbr and you want to access that table via the remote user jim

    CREATE USER jim IDENTIFIED BY "passwd";

GRANT CREATE SESSION to jim;

GRANT SELECT ON bob.ext_tab TO jim;

GRANT READ, WRITE ON DIRECTORY xtern_data_dir TO jim;

    On the local database create the db link to jim to dbr

    CREATE DATABASE LINK dbr CONNECT TO jim IDENTIFIED BY "passwd" USING 'DBR';

    Now the select will work 

    SELECT * FROM bob.ext_tab@DBR;