External Table via DBLINK

1
votes

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.

2
oracle11gdblinkexternal-tables
Can you please post how are you selecting it. Post your select statement. – XING
Sure, select * from my_table@foo – Oguen

2 Answers

1
votes

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;
0
votes

@Lunc, thank you!

I was almost sure that it was impossible to reach an external table remotely. Until your post! :)

I 've managed to select my data, this one helped.

GRANT ALL ON DIRECTORY MY_DIRECOTY TO PUBLIC;