Database Monitoring for Self-Hosted Oracle

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.

Multi-tenant
Non-CDB
Oracle 11

CREATE USER c##atatus IDENTIFIED BY &password CONTAINER = ALL ;
ALTER USER c##atatus SET CONTAINER_DATA=ALL CONTAINER=CURRENT;

CREATE USER atatus IDENTIFIED BY &password ;

CREATE USER atatus IDENTIFIED BY &password ;

2. Grant the user access to the database

Run the following commands while logged in as sysdba.

Multi-tenant
Non-CDB
Oracle 11

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:

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:

connect / as sysdba
alter session set container = your_pdb ;
grant set container to c##atatus ;

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;

grant execute on sys.dbms_lob to atatus;

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;

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.

Multi-tenant
Non-CDB
Oracle 11

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 ;

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;

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

Multi-tenant
Non-CDB
Oracle 11

Update the configuration file by adding the following configuration to /etc/atatus-infra-agent/conf.d/oracle.d/oracle.yml:

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:

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:

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

Multi-tenant
Non-CDB
Oracle 11

Restart the Atatus Infra Agent to apply the changes:

sudo service atatus-infra-agent restart

Restart the Atatus Infra Agent to apply the changes:

sudo service atatus-infra-agent restart

Restart the Atatus Infra Agent to apply the changes:

sudo service atatus-infra-agent restart

​Before You Begin

​1. Create the Atatus user

​2. Grant the user access to the database

​3. Create Required View

​4. Configure Atatus Infra Agent

​5. Restart the Atatus Infra Agent

Before You Begin

1. Create the Atatus user

2. Grant the user access to the database

3. Create Required View

4. Configure Atatus Infra Agent

5. Restart the Atatus Infra Agent