thansk Tom
JUSTIN RAJU, October 11, 2018 - 9:36 pm UTC
Thanks Tom (connar) for sharing the information it did help.
how can i check how many user are accessing this Public DB link.
October 12, 2018 - 2:55 am UTC
If you run AUDIT on database links, you get records based on create/alter/drop of links. But you can also audit SELECT statements - then the audit records for the SELECT will contain information about the db link used, eg
SQL> set lines 1000
SQL> column userid format a15
SQL> column ntimestamp# format a30
SQL> column sqltext format a40
SQL> column comment$text format a200
SQL> select userid, ntimestamp#, sqltext, comment$text from aud$ where comment$text like '%DBLINK%';
USERID NTIMESTAMP# SQLTEXT COMMENT$TEXT
--------------- ------------------------------ ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
MARK 26-OCT-15 12.49.55.219992 PM SELECT "A1"."COL1" FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 12.51.34.476959 PM SELECT 0 FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 12.51.59.086081 PM SELECT 0 FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.00.37.517809 PM Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=52438)); DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.00.37.527570 PM SELECT "A1"."COL1" FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.01.36.310674 PM SELECT "A1"."COL1" FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.05.07.564925 PM Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=52725)); DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.05.07.595509 PM SELECT "A1"."COL1" FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.08.54.204852 PM SELECT "A1"."COL1" FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.09.34.166246 PM SELECT "A1"."COL1" FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.09.59.877822 PM SELECT "A1"."COL1" FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.10.29.447086 PM SELECT "A1"."COL1" FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.10.38.853625 PM DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.10.46.192234 PM Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=53072)); DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.10.46.199130 PM SELECT "A1"."COL1" FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.10.57.918255 PM SELECT "A1"."COL1" FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.11.09.381775 PM DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.11.31.318063 PM Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=53119)); DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 01.11.31.323628 PM SELECT "A1"."COL1" FROM "GIGI" "A1" DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
MARK 26-OCT-15 03.50.24.076062 PM DBLINK_INFO: (SOURCE_GLOBAL_NAME=db11204.4294967295)
(Taken from MOS NOte 2071002.1)
Rachel Barker, May 07, 2021 - 2:04 pm UTC
I have been debating the easiest way to do this. As always you have a great answer. Thank you Connor!
May 10, 2021 - 7:51 am UTC
glad we could help
Flo, January 19, 2022 - 1:49 pm UTC
What if object X in the remote DB is a synonym to another user's table? It doesn't seem to work. Is it possible to do that?
January 19, 2022 - 6:36 pm UTC
You can use synonyms over db links - you still need access to the underlying objects though
Packages
Vic, October 13, 2022 - 11:09 am UTC
If you call a table via dblink its work.
But if you call a package it doesnt work.
For example.
User A:
Create view AAA as select package.function@dblink(parameter) from dual
Grant select on AAA to B
User B:
Select * from AAA
ORA-04504
October 13, 2022 - 12:30 pm UTC
What is error ORA-04504? Do you mean ORA-04054 DB link doesn't exist?
If so, have you verified the DB link name? What is the statement that does work?
Data
Vic, October 13, 2022 - 1:57 pm UTC
Thank you for your response.
Yes the error is ORA-04054.
I have a view in a schema A:
CREATE OR REPLACE FORCE VIEW AAA
(SELECT COUNT (C.COLUMN1)
FROM TABLE1@DBLINK1 C)
The dblink DBLINK1 was created in schema A.
With schema A: If I made a SELECT * FROM AAA, it works.
I grant the view to another schema called B:
GRANT SELECT ON AAA TO B
If I made a SELECT * FROM AAA, it works. Like in you example.
---
Now the same but adding a package call in where clause, for example:
CREATE OR REPLACE FORCE VIEW AAA
(SELECT COUNT (C.COLUMN1)
FROM TABLE1@DBLINK1 C
WHERE PACKAGE_NAME_FUNCTION_NAME@DBLINK1 (PARAMETER) = 'N')
With schema A: If I made a SELECT * FROM AAA, it works.
If I made a SELECT * FROM A.AAA, in schema B, it returns:
ORA-04054 database link string does not exist
It is really annoying
Thanks.
October 26, 2022 - 3:57 am UTC
Unfortunately that is just the way it is.
I'm generally not a fan of dblinks in view definitions, because if you are building a view, then you may as well build that view on the source system and then access just the view via the dblink.
Where dblink in view the better/only path
Clay, July 06, 2023 - 9:43 pm UTC
I agree with "generally not a fan of dblinks in view definitions, because if you are building a view, then you may as well build that view on the source system and then access just the view via the dblink", however, in our organization, we cannot make database links public so providing access to the link is not option. Our only option is to create the view with the embedded db link and then grant permissions to the view.