Database Monitoring provides deep visibility into your Oracle databases by exposing query samples that help profile different workloads and quickly diagnose performance issues.
The Atatus Infrastructure Agent collects telemetry directly from the database by logging in as a read-only Oracle user, ensuring safe and non-intrusive monitoring.
Before You Begin
| Component | Supported Versions |
|---|---|
| Oracle | 11g, 12c, 18c, 19c, 21c |
| Atatus Infra Agent | 4.0.0 or higher |
To enable Oracle Database Monitoring with Atatus, complete the following steps:
1. Create the Atatus user
Create a read-only Atatus user and grant the required permissions.
copy
CREATE USER c##atatus IDENTIFIED BY &password CONTAINER = ALL ;
ALTER USER c##atatus SET CONTAINER_DATA=ALL CONTAINER=CURRENT;copy
CREATE USER atatus IDENTIFIED BY &password ;copy
CREATE USER atatus IDENTIFIED BY &password ;2. Grant the user access to the database
Run the following commands while logged in as sysdba.
copy
grant create session to c##atatus;
grant select on v_$session to c##atatus;
grant select on v_$database to c##atatus;
grant select on v_$containers to c##atatus;
grant select on v_$sqlstats to c##atatus;
grant select on v_$instance to c##atatus;
grant select on dba_feature_usage_statistics to c##atatus;
grant select on V_$SQL_PLAN_STATISTICS_ALL to c##atatus;
grant select on V_$PROCESS to c##atatus;
grant select on V_$SESSION to c##atatus;
grant select on V_$CON_SYSMETRIC to c##atatus;
grant select on CDB_TABLESPACE_USAGE_METRICS to c##atatus;
grant select on CDB_TABLESPACES to c##atatus;
grant select on V_$SQLCOMMAND to c##atatus;
grant select on V_$DATAFILE to c##atatus;
grant select on V_$SYSMETRIC to c##atatus;
grant select on V_$SGAINFO to c##atatus;
grant select on V_$PDBS to c##atatus;
grant select on CDB_SERVICES to c##atatus;
grant select on V_$OSSTAT to c##atatus;
grant select on V_$PARAMETER to c##atatus;
grant select on V_$SQLSTATS to c##atatus;
grant select on V_$CONTAINERS to c##atatus;
grant select on V_$SQL_PLAN_STATISTICS_ALL to c##atatus;
grant select on V_$SQL to c##atatus;
grant select on V_$PGASTAT to c##atatus;
grant select on v_$asm_diskgroup to c##atatus;
grant select on v_$rsrcmgrmetric to c##atatus;
grant select on v_$dataguard_config to c##atatus;
grant select on v_$dataguard_stats to c##atatus;
grant select on v_$transaction to c##atatus;
grant select on v_$locked_object to c##atatus;
grant select on dba_objects to c##atatus;
grant select on cdb_data_files to c##atatus;
grant select on dba_data_files to c##atatus;
If metrics are not visible after granting the above permissions, the database may be using a custom profile. In such cases, Atatus requires access to DBMS_LOB for internal query execution. You can grant this access by executing the following command:
copy
grant execute on sys.dbms_lob to c##atatus;
If you have configured custom queries on a Pluggable Database (PDB), the monitoring user must be allowed to switch containers. You can enable this by executing the following command:
copy
connect / as sysdba
alter session set container = your_pdb ;
grant set container to c##atatus ;
copy
grant create session to atatus;
grant select on v_$session to atatus;
grant select on v_$database to atatus;
grant select on v_$containers to atatus;
grant select on v_$sqlstats to atatus;
grant select on v_$instance to atatus;
grant select on dba_feature_usage_statistics to atatus;
grant select on V_$SQL_PLAN_STATISTICS_ALL to atatus;
grant select on V_$PROCESS to atatus;
grant select on V_$SESSION to atatus;
grant select on V_$CON_SYSMETRIC to atatus;
grant select on CDB_TABLESPACE_USAGE_METRICS to atatus;
grant select on CDB_TABLESPACES to atatus;
grant select on V_$SQLCOMMAND to atatus;
grant select on V_$DATAFILE to atatus;
grant select on V_$SYSMETRIC to atatus;
grant select on V_$SGAINFO to atatus;
grant select on V_$PDBS to atatus;
grant select on CDB_SERVICES to atatus;
grant select on V_$OSSTAT to atatus;
grant select on V_$PARAMETER to atatus;
grant select on V_$SQLSTATS to atatus;
grant select on V_$CONTAINERS to atatus;
grant select on V_$SQL_PLAN_STATISTICS_ALL to atatus;
grant select on V_$SQL to atatus;
grant select on V_$PGASTAT to atatus;
grant select on v_$asm_diskgroup to atatus;
grant select on v_$rsrcmgrmetric to atatus;
grant select on v_$dataguard_config to atatus;
grant select on v_$dataguard_stats to atatus;
grant select on v_$transaction to atatus;
grant select on v_$locked_object to atatus;
grant select on dba_objects to atatus;
grant select on cdb_data_files to atatus;
grant select on dba_data_files to atatus;
copy
grant execute on sys.dbms_lob to atatus;
copy
grant create session to atatus;
grant select on v_$session to atatus;
grant select on v_$database to atatus;
grant select on v_$sqlstats to atatus;
grant select on v_$instance to atatus;
grant select on dba_feature_usage_statistics to atatus;
grant select on V_$SQL_PLAN_STATISTICS_ALL to atatus;
grant select on V_$PROCESS to atatus;
grant select on V_$SESSION to atatus;
grant select on V_$SQLCOMMAND to atatus;
grant select on V_$DATAFILE to atatus;
grant select on V_$SYSMETRIC to atatus;
grant select on V_$SGAINFO to atatus;
grant select on V_$OSSTAT to atatus;
grant select on V_$PARAMETER to atatus;
grant select on V_$SQLSTATS to atatus;
grant select on V_$SQL_PLAN_STATISTICS_ALL to atatus;
grant select on V_$SQL to atatus;
grant select on V_$PGASTAT to atatus;
grant select on dba_tablespace_usage_metrics to atatus;
grant select on dba_tablespaces to atatus;
grant select on v_$asm_diskgroup to atatus;
grant select on v_$rsrcmgrmetric to atatus;
grant select on v_$dataguard_config to atatus;
grant select on v_$dataguard_stats to atatus;
copy
grant execute on sys.dbms_lob to atatus;
3. Create Required View
Create the required view, and grant access to the Atatus database user. while logged in as sysdba.
copy
CREATE OR REPLACE VIEW atatus_session AS
SELECT /*+ push_pred(sq) push_pred(sq_prev) */
s.indx as sid,
s.ksuseser as serial#,
s.ksuudlna as username,
DECODE(BITAND(s.ksuseidl, 9), 1, 'ACTIVE', 0, DECODE(BITAND(s.ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'), 'KILLED') as status,
s.ksuseunm as osuser,
s.ksusepid as process,
s.ksusemnm as machine,
s.ksusemnp as port,
s.ksusepnm as program,
DECODE(BITAND(s.ksuseflg, 19), 17, 'BACKGROUND', 1, 'USER', 2, 'RECURSIVE', '?') as type,
s.ksusesqi as sql_id,
sq.force_matching_signature as force_matching_signature,
s.ksusesph as sql_plan_hash_value,
s.ksusesesta as sql_exec_start,
s.ksusesql as sql_address,
CASE WHEN BITAND(s.ksusstmbv, POWER(2, 04)) = POWER(2, 04) THEN 'Y' ELSE 'N' END as in_parse,
CASE WHEN BITAND(s.ksusstmbv, POWER(2, 07)) = POWER(2, 07) THEN 'Y' ELSE 'N' END as in_hard_parse,
s.ksusepsi as prev_sql_id,
s.ksusepha as prev_sql_plan_hash_value,
s.ksusepesta as prev_sql_exec_start,
sq_prev.force_matching_signature as prev_force_matching_signature,
s.ksusepsq as prev_sql_address,
s.ksuseapp as module,
s.ksuseact as action,
s.ksusecli as client_info,
s.ksuseltm as logon_time,
s.ksuseclid as client_identifier,
s.ksusstmbv as op_flags,
decode(s.ksuseblocker,
4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT',
'VALID'
) as blocking_session_status,
DECODE(s.ksuseblocker,
4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL),
4294967292, TO_NUMBER(NULL), 4294967291, TO_NUMBER(NULL), BITAND(s.ksuseblocker, 2147418112) / 65536
) as blocking_instance,
DECODE(s.ksuseblocker,
4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL),
4294967292, TO_NUMBER(NULL), 4294967291, TO_NUMBER(NULL), BITAND(s.ksuseblocker, 65535)
) as blocking_session,
DECODE(s.ksusefblocker,
4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT', 'VALID'
) as final_blocking_session_status,
DECODE(s.ksusefblocker,
4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL), 4294967292, TO_NUMBER(NULL),
4294967291, TO_NUMBER(NULL), BITAND(s.ksusefblocker, 2147418112) / 65536
) as final_blocking_instance,
DECODE(s.ksusefblocker,
4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL), 4294967292, TO_NUMBER(NULL),
4294967291, TO_NUMBER(NULL), BITAND(s.ksusefblocker, 65535)
) as final_blocking_session,
DECODE(w.kslwtinwait,
1, 'WAITING', decode(bitand(w.kslwtflags, 256), 0, 'WAITED UNKNOWN TIME',
decode(round(w.kslwtstime / 10000), 0, 'WAITED SHORT TIME', 'WAITED KNOWN TIME'))
) as STATE,
e.kslednam as event,
e.ksledclass as wait_class,
w.kslwtstime as wait_time_micro,
c.name as pdb_name,
sq.sql_text as sql_text,
sq.sql_fulltext as sql_fulltext,
sq_prev.sql_fulltext as prev_sql_fulltext,
comm.command_name
FROM
x$ksuse s,
x$kslwt w,
x$ksled e,
v$sql sq,
v$sql sq_prev,
v$containers c,
v$sqlcommand comm
WHERE
BITAND(s.ksspaflg, 1) != 0
AND BITAND(s.ksuseflg, 1) != 0
AND s.inst_id = USERENV('Instance')
AND s.indx = w.kslwtsid
AND w.kslwtevt = e.indx
AND s.ksusesqi = sq.sql_id(+)
AND decode(s.ksusesch, 65535, TO_NUMBER(NULL), s.ksusesch) = sq.child_number(+)
AND s.ksusepsi = sq_prev.sql_id(+)
AND decode(s.ksusepch, 65535, TO_NUMBER(NULL), s.ksusepch) = sq_prev.child_number(+)
AND s.con_id = c.con_id(+)
AND s.ksuudoct = comm.command_type(+);
GRANT SELECT ON atatus_session TO c##atatus ;
copy
CREATE OR REPLACE VIEW atatus_session AS
SELECT /*+ push_pred(sq) push_pred(sq_prev) */
s.indx as sid,
s.ksuseser as serial#,
s.ksuudlna as username,
DECODE(BITAND(s.ksuseidl, 9), 1, 'ACTIVE', 0, DECODE(BITAND(s.ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'), 'KILLED') as status,
s.ksuseunm as osuser,
s.ksusepid as process,
s.ksusemnm as machine,
s.ksusemnp as port,
s.ksusepnm as program,
DECODE(BITAND(s.ksuseflg, 19), 17, 'BACKGROUND', 1, 'USER', 2, 'RECURSIVE', '?') as type,
s.ksusesqi as sql_id,
sq.force_matching_signature as force_matching_signature,
s.ksusesph as sql_plan_hash_value,
s.ksusesesta as sql_exec_start,
s.ksusesql as sql_address,
CASE WHEN BITAND(s.ksusstmbv, POWER(2, 04)) = POWER(2, 04) THEN 'Y' ELSE 'N' END as in_parse,
CASE WHEN BITAND(s.ksusstmbv, POWER(2, 07)) = POWER(2, 07) THEN 'Y' ELSE 'N' END as in_hard_parse,
s.ksusepsi as prev_sql_id,
s.ksusepha as prev_sql_plan_hash_value,
s.ksusepesta as prev_sql_exec_start,
sq_prev.force_matching_signature as prev_force_matching_signature,
s.ksusepsq as prev_sql_address,
s.ksuseapp as module,
s.ksuseact as action,
s.ksusecli as client_info,
s.ksuseltm as logon_time,
s.ksuseclid as client_identifier,
s.ksusstmbv as op_flags,
decode(s.ksuseblocker,
4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT',
'VALID'
) as blocking_session_status,
DECODE(s.ksuseblocker,
4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL),
4294967292, TO_NUMBER(NULL), 4294967291, TO_NUMBER(NULL), BITAND(s.ksuseblocker, 2147418112) / 65536
) as blocking_instance,
DECODE(s.ksuseblocker,
4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL),
4294967292, TO_NUMBER(NULL), 4294967291, TO_NUMBER(NULL), BITAND(s.ksuseblocker, 65535)
) as blocking_session,
DECODE(s.ksusefblocker,
4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT', 'VALID'
) as final_blocking_session_status,
DECODE(s.ksusefblocker,
4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL), 4294967292, TO_NUMBER(NULL),
4294967291, TO_NUMBER(NULL), BITAND(s.ksusefblocker, 2147418112) / 65536
) as final_blocking_instance,
DECODE(s.ksusefblocker,
4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL), 4294967292, TO_NUMBER(NULL),
4294967291, TO_NUMBER(NULL), BITAND(s.ksusefblocker, 65535)
) as final_blocking_session,
DECODE(w.kslwtinwait,
1, 'WAITING', decode(bitand(w.kslwtflags, 256), 0, 'WAITED UNKNOWN TIME',
decode(round(w.kslwtstime / 10000), 0, 'WAITED SHORT TIME', 'WAITED KNOWN TIME'))
) as STATE,
e.kslednam as event,
e.ksledclass as wait_class,
w.kslwtstime as wait_time_micro,
c.name as pdb_name,
sq.sql_text as sql_text,
sq.sql_fulltext as sql_fulltext,
sq_prev.sql_fulltext as prev_sql_fulltext,
comm.command_name
FROM
x$ksuse s,
x$kslwt w,
x$ksled e,
v$sql sq,
v$sql sq_prev,
v$containers c,
v$sqlcommand comm
WHERE
BITAND(s.ksspaflg, 1) != 0
AND BITAND(s.ksuseflg, 1) != 0
AND s.inst_id = USERENV('Instance')
AND s.indx = w.kslwtsid
AND w.kslwtevt = e.indx
AND s.ksusesqi = sq.sql_id(+)
AND decode(s.ksusesch, 65535, TO_NUMBER(NULL), s.ksusesch) = sq.child_number(+)
AND s.ksusepsi = sq_prev.sql_id(+)
AND decode(s.ksusepch, 65535, TO_NUMBER(NULL), s.ksusepch) = sq_prev.child_number(+)
AND s.con_id = c.con_id(+)
AND s.ksuudoct = comm.command_type(+);
GRANT SELECT ON atatus_session TO atatus;
copy
CREATE OR REPLACE VIEW atatus_session AS
SELECT /*+ push_pred(sq) push_pred(sq_prev) */
s.indx as sid,
s.ksuseser as serial#,
s.ksuudlna as username,
DECODE(BITAND(s.ksuseidl, 9), 1, 'ACTIVE', 0, DECODE(BITAND(s.ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'), 'KILLED') as status,
s.ksuseunm as osuser,
s.ksusepid as process,
s.ksusemnm as machine,
s.ksusemnp as port,
s.ksusepnm as program,
DECODE(BITAND(s.ksuseflg, 19), 17, 'BACKGROUND', 1, 'USER', 2, 'RECURSIVE', '?') as type,
s.ksusesqi as sql_id,
sq.force_matching_signature as force_matching_signature,
s.ksusesph as sql_plan_hash_value,
s.ksusesesta as sql_exec_start,
s.ksusesql as sql_address,
CASE WHEN BITAND(s.ksusstmbv, POWER(2, 04)) = POWER(2, 04) THEN 'Y' ELSE 'N' END as in_parse,
CASE WHEN BITAND(s.ksusstmbv, POWER(2, 07)) = POWER(2, 07) THEN 'Y' ELSE 'N' END as in_hard_parse,
s.ksusepsi as prev_sql_id,
s.ksusepha as prev_sql_plan_hash_value,
s.ksusepesta as prev_sql_exec_start,
sq_prev.force_matching_signature as prev_force_matching_signature,
s.ksusepsq as prev_sql_address,
s.ksuseapp as module,
s.ksuseact as action,
s.ksusecli as client_info,
s.ksuseltm as logon_time,
s.ksuseclid as client_identifier,
s.ksusstmbv as op_flags,
decode(s.ksuseblocker,
4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT',
'VALID'
) as blocking_session_status,
DECODE(s.ksuseblocker,
4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL),
4294967292, TO_NUMBER(NULL), 4294967291, TO_NUMBER(NULL), BITAND(s.ksuseblocker, 2147418112) / 65536
) as blocking_instance,
DECODE(s.ksuseblocker,
4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL),
4294967292, TO_NUMBER(NULL), 4294967291, TO_NUMBER(NULL), BITAND(s.ksuseblocker, 65535)
) as blocking_session,
DECODE(s.ksusefblocker,
4294967295, 'UNKNOWN', 4294967294, 'UNKNOWN', 4294967293, 'UNKNOWN', 4294967292, 'NO HOLDER', 4294967291, 'NOT IN WAIT', 'VALID'
) as final_blocking_session_status,
DECODE(s.ksusefblocker,
4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL), 4294967292, TO_NUMBER(NULL),
4294967291, TO_NUMBER(NULL), BITAND(s.ksusefblocker, 2147418112) / 65536
) as final_blocking_instance,
DECODE(s.ksusefblocker,
4294967295, TO_NUMBER(NULL), 4294967294, TO_NUMBER(NULL), 4294967293, TO_NUMBER(NULL), 4294967292, TO_NUMBER(NULL),
4294967291, TO_NUMBER(NULL), BITAND(s.ksusefblocker, 65535)
) as final_blocking_session,
DECODE(w.kslwtinwait,
1, 'WAITING', decode(bitand(w.kslwtflags, 256), 0, 'WAITED UNKNOWN TIME',
decode(round(w.kslwtstime / 10000), 0, 'WAITED SHORT TIME', 'WAITED KNOWN TIME'))
) as STATE,
e.kslednam as event,
e.ksledclass as wait_class,
w.kslwtstime as wait_time_micro,
sq.sql_text as sql_text,
sq.sql_fulltext as sql_fulltext,
sq_prev.sql_fulltext as prev_sql_fulltext,
comm.command_name
FROM
x$ksuse s,
x$kslwt w,
x$ksled e,
v$sql sq,
v$sql sq_prev,
v$sqlcommand comm
WHERE
BITAND(s.ksspaflg, 1) != 0
AND BITAND(s.ksuseflg, 1) != 0
AND s.inst_id = USERENV('Instance')
AND s.indx = w.kslwtsid
AND w.kslwtevt = e.indx
AND s.ksusesqi = sq.sql_id(+)
AND decode(s.ksusesch, 65535, TO_NUMBER(NULL), s.ksusesch) = sq.child_number(+)
AND s.ksusepsi = sq_prev.sql_id(+)
AND decode(s.ksusepch, 65535, TO_NUMBER(NULL), s.ksusepch) = sq_prev.child_number(+)
AND s.ksuudoct = comm.command_type(+);
GRANT SELECT ON atatus_session TO atatus;
4. Configure Atatus Infra Agent
Update the configuration file by adding the following configuration to /etc/atatus-infra-agent/conf.d/oracle.d/oracle.yml:
copy
metrics:
- hosts: ["127.0.0.1"]
port: <PORT>
username: c##atatus
password: <REPLACE_PASSWORD>
service_name: <REPLACE_SERVICE_NAME>
dbm: true
Note: Replace the placeholders (<PORT>, <REPLACE_PASSWORD>, and <REPLACE_SERVICE_NAME>) with your actual Oracle database connection details.
Update the configuration file by adding the following configuration to /etc/atatus-infra-agent/conf.d/oracle.d/oracle.yml:
copy
metrics:
- hosts: ["127.0.0.1"]
port: <PORT>
username: atatus
password: <REPLACE_PASSWORD>
service_name: <REPLACE_SERVICE_NAME>
dbm: true
Note: Replace the placeholders (<PORT>, <REPLACE_PASSWORD>, and <REPLACE_SERVICE_NAME>) with your actual Oracle database connection details.
Update the configuration file by adding the following configuration to /etc/atatus-infra-agent/conf.d/oracle.d/oracle.yml:
copy
metrics:
- hosts: ["127.0.0.1"]
port: <PORT>
username: atatus
password: <REPLACE_PASSWORD>
service_name: <REPLACE_SERVICE_NAME>
dbm: true
Note: Replace the placeholders (<PORT>, <REPLACE_PASSWORD>, and <REPLACE_SERVICE_NAME>) with your actual Oracle database connection details.
5. Restart the Atatus Infra Agent
Restart the Atatus Infra Agent to apply the changes:
copy
sudo service atatus-infra-agent restart
Restart the Atatus Infra Agent to apply the changes:
copy
sudo service atatus-infra-agent restart
Restart the Atatus Infra Agent to apply the changes:
copy
sudo service atatus-infra-agent restart
+1-415-800-4104