Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am the admin of one of the database, and I have created a view in SYS schema using below code which returns the status of the running jobs:

CREATE OR REPLACE VIEW CAF_DB_REFRESH AS
SELECT *
FROM all_scheduler_job_run_details
WHERE OWNER = 'CAFANALYST'
  AND log_id > 2573
ORDER BY log_date;

I have created public synonym for this view and given access to read only account which will be used by all users in my team.

When they perform the following query, they are getting only column names with no rows:

select * from CAF_DB_REFRESH;

Could you please guide me what is the problem and if any special privileges required for read only account? If yes, I request you to guide which one is the best to see but not to modify by read only user.

share|improve this question

migrated from stackoverflow.com Jun 8 '13 at 10:06

This question came from our site for professional and enthusiast programmers.

1  
The ALL_* views only return rows that are visible to the current user. If you login as another user, they'll only show rows that they are allowed to see. You can't use views to expose data that is otherwise hidden. –  Jeffrey Kemp May 23 '13 at 3:00
    
so what is the best method/table to refer in sql query. –  user2039795 May 23 '13 at 9:44
    
Not sure - might be a better way, but a function that returns a table of records based on the view might do the trick. –  Jeffrey Kemp May 23 '13 at 13:42
1  
Belongs on DBA.SE. –  Yasir Arsanukaev Jun 7 '13 at 10:59

2 Answers 2

As @Jeffrey pointed out, ALL_* view is not useful in your case. You should use DBA_* views because they show the records for all owners of the job objects.

The following sequence works just fine for me:

$ sqlplus /nolog

SQL> connect / as sysdba
Connected.

SQL> select distinct owner from dba_scheduler_job_run_details;

OWNER
------------------------------
EXFSYS
SPONGEBOB
SYS

SQL> create or replace view sys.jcaf as
  2  select * from dba_scheduler_job_run_details
  3  where owner = 'EXFSYS';    
View created.

SQL> grant select on sys.jcaf to spongebob;    
Grant succeeded.

SQL> create public synonym jcaf_v for sys.jcaf;    
Synonym created.

SQL> connect spongebob/passwd    
Connected.

SQL> select distinct owner from jcaf_v;

OWNER
------------------------------
EXFSYS
share|improve this answer

I think that you need grants on table for other users or you can try with table reference sys in front of all_scheduler_job_running_detais..... dont know if that will do the trick

CREATE OR REPLACE VIEW CAF_DB_REFRESH AS
SELECT *
FROM sys.all_scheduler_job_run_details
WHERE OWNER = 'CAFANALYST'
  AND log_id > 2573
ORDER BY log_date;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.