Checking snapshots out of refresh groups in Oracle

Question

I have an Oracle Database with a schema A, which has select privilege on DBA_* views.

I also have an schema B that doesn't have these privilege, but it has a lot of materialized views.

In schema A, there are three refresh groups, each one having some materialized views from schema B.

I'd like to check whether there are snapshots out of the refresh groups, but I'd like to do this check connected as B.

I'm using the following query to perform this check in schema A:

SELECT OWNER, NAME FROM DBA_SNAPSHOTS WHERE OWNER = 'B'
MINUS
SELECT OWNER, NAME FROM DBA_REFRESH_CHILDREN WHERE RNAME IN ('REFRESH_G1','REFRESH_G2','REFRESH_G3');

The problems:

B doesn't have privileges on DBA_* views (and It can't have)
If I change DBA_* views for ALL_* views, B can't see the refresh groups (and I can not grant alter any materialized view to B)

So:

Is there another way to perform this check in B?
Or is there another way to make B see the refresh groups?
Or is there a way to "freeze" the result of the query using ALL_* views, such as B will see the result of the query as A?

Thanks!

Another way would be to create a `view` on `A` based on `DBA_%` tables and then access `select` to `B` on that view. — Anjan Biswas, Aug 15 '12 at 20:54

score 0 · Answer 1 · answered Aug 15 '12 at 20:57

On A

CREATE OR REPLACE VIEW V_REFRESH_SNAPSHOT AS
SELECT OWNER, NAME FROM DBA_SNAPSHOTS WHERE OWNER = 'B'
MINUS
SELECT OWNER, NAME FROM DBA_REFRESH_CHILDREN WHERE RNAME IN ('REFRESH_G1','REFRESH_G2','REFRESH_G3');

GRANT SELECT ON A.V_REFRESH_SNAPSHOT TO B;

From B

SELECT * FROM A.V_REFRESH_SNAPSHOT;

Checking snapshots out of refresh groups in Oracle

1 Answers1