I beg your pardon, it is about USER B cyclically collecting and evaluating certain information from distributed databases in a central database. USER A owns the database links to the distributed databases. The following scripts shoud illustrate the problem:
As DBA create the user A and B:
DROP USER A CASCADE;
CREATE USER A
IDENTIFIED BY A
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT RESOURCE TO A;
ALTER USER A DEFAULT ROLE NONE;
-- 2 System Privileges for A
GRANT CREATE DATABASE LINK TO A;
GRANT CREATE SESSION TO A;
CREATE USER B
IDENTIFIED BY B
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT RESOURCE TO B;
ALTER USER B DEFAULT ROLE NONE;
-- 2 System Privileges for B
GRANT CREATE TABLE TO B;
GRANT CREATE PROCEDURE TO B;
GRANT CREATE SESSION TO B;
ALTER USER B QUOTA UNLIMITED ON USERS;
Login as User A and create the database links (To keep the example simple, I have simply let the database links refer to the local DB):
CREATE DATABASE LINK "DB1"
CONNECT TO HR
IDENTIFIED BY HR
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521)) (CONNECT_DATA = (SERVER = dedicated) (SID=ORCL)))';
CREATE DATABASE LINK "DB2"
CONNECT TO HR
IDENTIFIED BY HR
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521)) (CONNECT_DATA = (SERVER = dedicated) (SID=ORCL)))';
CREATE DATABASE LINK "DB3"
CONNECT TO HR
IDENTIFIED BY HR
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521)) (CONNECT_DATA = (SERVER = dedicated) (SID=ORCL)))';
Login as User B and run the following commands:
DROP TABLE dbs;
DROP TABLE depstats;
CREATE TABLE dbs
(
dblink VARCHAR2 (100)
);
INSERT INTO dbs
VALUES ('A.DB1');
INSERT INTO dbs
VALUES ('A.DB2');
INSERT INTO dbs
VALUES ('A.DB3');
CREATE TABLE depstats
(
dblink VARCHAR2 (100)
, department_id VARCHAR2 (4)
, emps# NUMBER (5)
, avg_sal NUMBER (10, 2)
, max_sal NUMBER (8, 2)
, refresh_date DATE
);
CREATE OR REPLACE PROCEDURE refresh_deptstats
IS
BEGIN
FOR r IN (SELECT dblink FROM dbs)
LOOP
EXECUTE IMMEDIATE 'INSERT INTO depstats (dblink
, department_id
, emps#
, avg_sal
, max_sal
, refresh_date)
SELECT ''' || r.dblink || ''' as dblink
, department_id
, COUNT (employee_id) AS emps#
, ROUND (AVG (salary)) AS avg_sal
, MAX (salary) AS max_sal
, SYSDATE
FROM hr.employees@' || r.dblink || ' GROUP BY department_id';
COMMIT;
END LOOP;
END;
/
EXEC refresh_deptstats;
SELECT * FROM depstats;
The Procedure refresh_deptstats fails with
ORA-02019: connection description for remote database not found
Since there is no way to grant privileges on database links to other users, I don't know how to accomplish the task.