Oracle Analytics (via Collector method) - v3.0.0

-- table column metadata

CREATE MATERIALIZED
VIEW <SCHEMA>.MV_KADA_TABLES
TABLESPACE DATA
PARALLEL 4
     BUILD IMMEDIATE
AS
SELECT
    atc.owner "Owner",
    atc.table_name  "Table_Name",
    atc.column_id,
    atc.column_name "Column_Name",
    initcap(data_type) ||
        decode(data_type, 
            'CHAR',      '('|| char_length ||')',
            'VARCHAR',   '('|| char_length ||')',
            'VARCHAR2',  '('|| char_length ||')',
            'NCHAR',     '('|| char_length ||')',
            'NVARCHAR',  '('|| char_length ||')', 
            'NVARCHAR2', '('|| char_length ||')',
            'NUMBER',    '('|| nvl(data_precision,data_length)|| decode(data_scale,null,null,','||data_scale)||')', null) "Data_Type",
    nullable "Nullable",
    atc.owner       sdev_link_owner,
    atc.table_name  sdev_link_name,
    'TABLE'     sdev_link_type,
    acc.comments
FROM
    sys.dba_tab_columns ATC,
    sys.dba_COL_COMMENTS ACC
WHERE atc.owner = acc.owner
    and atc.table_name = acc.table_name
    and atc.column_name = acc.column_name
order by atc.owner, atc.table_name, atc.column_id
;
GRANT SELECT ON <SCHEMA>.MV_KADA_TABLES to <KADA USER>;

-- query history

CREATE MATERIALIZED VIEW <SCHEMA>.MV_KADA_DB_LOG
TABLESPACE DATA
     BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH ROWID USING TRUSTED CONSTRAINTS     
AS
SELECT
    r.begin_interval_time,
    r.dbid,
    r.snap_id,
    b.username AS user_name,
    b.username || '/' || r.session_id || '/' || r.session_serial# || '/' || r.instance_number AS session_id,
    r.instance_number,
    r.sql_id,
    r.sample_id,
    r.service_hash,
    r.client_id,
    r.machine,
    r.port,
    s.command_type,
    s.sql_text,
    r.start_time,
    r.cpu_time_ms,
    r.time_ms,
    r.db_time_ms,
    r.machine || ':' || r.port AS client_addr
FROM (
    SELECT
        s.begin_interval_time,
        a.DBID,
        a.snap_id,
        a.user_id,
        a.session_id,
        a.session_serial#,
        a.sql_id,
        a.sample_id,
        a.service_hash,
        a.client_id,
        a.machine,
        a.port,
        a.instance_number,
        MIN(a.sample_time) AS start_time,
        SUM(a.tm_delta_cpu_time) AS cpu_time_ms,
        SUM(a.tm_delta_time) AS time_ms,
        SUM(a.tm_delta_db_time) AS db_time_ms
    FROM dba_hist_active_sess_history a
        JOIN dba_hist_snapshot s
        ON a.dbid = s.dbid AND a.snap_id = s.snap_id AND a.instance_number = s.instance_number
    WHERE a.SQL_EXEC_START >= SYSDATE-1
    GROUP BY
        a.dbid,
        a.snap_id,
        a.user_id,
        a.session_id,
        a.session_serial#,
        a.sql_id,
        a.sample_id,
        a.service_hash,
        a.client_id,
        a.machine,
        a.port,
        s.begin_interval_time,
        a.instance_number
) r
    JOIN dba_users b
    ON r.user_id = b.user_id
    JOIN dba_hist_sqltext s
    ON r.dbid = s.dbid AND r.sql_id = s.sql_id
WHERE
    s.command_type NOT IN (
      6, 7, /* system cmds */
      47, /* declare cmd */
      170, 189
    )
    AND b.username NOT IN ('C##ADP$SERVICE','C##API','C##CLOUD$SERVICE','C##CLOUD_OPS','C##DV_ACCT_ADMIN','C##DV_OWNER','C##OMLIDM','GRAPH$METADATA','GRAPH$PROXY_USER','GSMADMIN_INTERNAL','ORACLE_OCM','OML$MODELS','OML$PROXY','REMOTE_SCHEDULER_AGENT','SH','SYS$UMF','SYSBACKUP','SYSDG','SYSKM','SYSRAC','DWH_STG','ADMIN','ODIREP_WLS_RUNTIME','ODIREP_ODI_REPO','ODIREP_STB','ODI_IAU_VIEWER','ODI_IAU','ODI_ODI_REPO','ODIREP_WLS','ODIREP_IAU_VIEWER','ODI_OPSS','ODIREP_IAU_APPEND','ODI_WLS','ODI_WLS_RUNTIME','ODIREP_OPSS','ODIREP_IAU','ODI_IAU_APPEND','ODI_STB','DWH_ODI_TMP','SYSTEM', 'SYS', 'OLAPSYS', 'LBACSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'APPQOSSYS', 'SYSMAN', 'WMSYS', 'EXFSYS', 'CTXSYS', 'ORDSYS', 'MDSYS');
;
GRANT SELECT ON <SCHEMA>.MV_KADA_DB_LOG to <KADA USER>;

-- OACS usage

CREATE VIEW <SCHEMA>.V_KADA_OACS_LOGICAL
AS
    SELECT ID, USER_NAME, SESSION_ID, SAW_SRC_PATH, PRESENTATION_NAME
    FROM USAGE_TRACKING.LOGICAL_QUERIES
    WHERE START_DT >= SYSDATE-2
;
GRANT SELECT ON <SCHEMA>.V_KADA_OACS_LOGICAL to <KADA USER>;

CREATE VIEW <SCHEMA>.V_KADA_OACS_PHYSICAL
AS
    SELECT ID, LOGICAL_QUERY_ID, QUERY_BLOB, TIME_SEC, ROW_COUNT, START_DT, START_HOUR_MIN
    FROM USAGE_TRACKING.PHYSICAL_QUERIES
    WHERE START_DT >= SYSDATE-2
;
GRANT SELECT ON <SCHEMA>.V_KADA_OACS_PHYSICAL to <KADA USER>;

pip install kada_collectors_extractors_<version>-none-any.whl

pip install kada_collectors_lib-<version>-none-any.whl

{
    "username": "",
    "password": "",
    "dsn": "",
    "oracle_client_path": "",
    "wallet_path": "",
    "output_path": "/tmp/output",
    "mask": true,
    "compress": true
}

import os
import argparse
from kada_collectors.extractors.utils import load_config, get_hwm, publish_hwm, get_generic_logger
from kada_collectors.extractors.oracle_analytics import Extractor

get_generic_logger('root') # Set to use the root logger, you can change the context accordingly or define your own logger

_type = 'oracle_analytics'
dirname = os.path.dirname(__file__)
filename = os.path.join(dirname, 'kada_{}_extractor_config.json'.format(_type))

parser = argparse.ArgumentParser(description='KADA Oracle Analytics Extractor.')
parser.add_argument('--config', '-c', dest='config', default=filename, help='Location of the configuration json, default is the config json in the same directory as the script.')
parser.add_argument('--name', '-n', dest='name', default=_type, help='Name of the collector instance.')
args = parser.parse_args()

start_hwm, end_hwm = get_hwm(args.name)

ext = Extractor(**load_config(args.config))
ext.test_connection()
ext.run(**{"start_hwm": start_hwm, "end_hwm": end_hwm})

publish_hwm(_type, end_hwm)

class Extractor(username: str = None, password: str = None, dsn: str = None, \
    oracle_client_path: str = None, wallet_path: str = '', \
    output_path: str = './output', mask: bool = False, compress: bool = False) -> None