When using the APM Oracle client what rights/roles does the user ID need for these DB queries to work ?
As far as I know, just whatever permissions are necessary to access the table you want to query.
Are you getting the error: ORA-00942: table or view does not exist ? Certain tables may only be viewable by the database administrator, for example: v$session, v$sql.
Have not gotten that far. Sent an e-mail to our Oracle DBA stating that I needed a user ID and password to test this monitoring....and he came back with the following
What level of access you need? What database objects that you may need to access? Will those accounts access data dictionary objects including DB environment parameters and DB audit?
What level of access you need?
If it were me answering your DBA, here is what I'd say: "Whatever level will let me run a SQL query."
What database objects that you may need to access?
"I need access to the xyz table" or "I'm not sure. What's table would you recommend I run a query on to see if the database is working correctly? I just need to get back a single tuple."
Will those accounts access data dictionary objects including DB environment parameters and DB audit?
"Nope. Not unless you want me to keep track of certain database parameters for you. In which case, you know better than me what needs to be monitored. Want to meet for a few minutes and figure it out?"
If you are only wanting to use the "Oracle Database" template and you arent creating new Oracle monitoring components from scratch, it's easier. Tell your DBA you need an account with whatever permissions are necessary to run these SQL queries. (I'm just copying and pasting these from the template definition.):
select sum(bytes/1024/1024) "Total_free_space, MB" from dba_free_space
select round(((1-(sum(decode(name,'physical reads', value,0))/(sum(decode(name, 'db block gets', value,0))+(sum(decode(name, 'consistent gets',value, 0))))))*100),2) "Buffer Cache Hit Ratio, %" from v$sysstat
select rest.hr "dictionary cache hit ratio, %" from ( select sum(GETS), sum(GETMISSES), round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2) hr from v$rowcache ) rest
select rest.hit_ratio "library cache hit ratio, %" from ( select sum(PINS) Executions, sum(RELOADS) cache_misses, round((1 - (sum(RELOADS) / sum(PINS))) * 100,2) hit_ratio from v$librarycache ) rest
select sum(rt.fm) "available free memory, MB" from ( select name, bytes/1024/1024 fm from v$sgastat where name='free memory') rt
select disk.VALUE Disk, memory.VALUE Memory from ( select NAME, VALUE from v$sysstat where NAME like 'sorts (disk)' ) disk, (select NAME, VALUE from v$sysstat where NAME like 'sorts (memory)' ) memory
select count(users.username) "number of connected users" from ( SELECT s.username FROM v$session s WHERE type = 'USER') users
