oratest

CLI to read useful data from several Oracle databases at once, for example all users connected to our DML schemas. All databases are defined in config.json.

build

make build    # ./build/linux/oratest and ./build/darwin/oratest
make run      # build for macOS and run 'get users'
make clean

config

Copy config.example.json to config.json and add one entry per database. A tenant can have several databases, e.g. a test and a prod database:

{
  "databases": [
    {
      "tenant": "OMC",
      "name": "omc-prod",
      "host": "db.example.com",
      "port": 1521,
      "username": "SOME_USER",
      "password": "",
      "sid": "DMLPROD.IT.NET",
      "schemas": ["SCHEMA_PROD"]
    }
  ]
}

field	description
tenant	group name, used with -t / --tenant; may be used several times
name	optional, unique name used with -d / --db; defaults to sid
host	database host
port	listener port, defaults to 1521
username	user with read access to v$session
password	password; leave it empty to use an env variable (see below)
sid	service name, the connection goes to host:port/sid
schemas	schemas to check

Env variables override the password from the file, checked in this order:

1. ORA_PASSWORD_<NAME> for one database, e.g. omc-prod -> ORA_PASSWORD_OMC_PROD
2. ORA_PASSWORD_<TENANT> for all databases of a tenant, e.g. ORA_PASSWORD_OMC

Characters other than letters and digits become _, so a database without a name (named by its sid DMLPROD.IT.NET) uses ORA_PASSWORD_DMLPROD_IT_NET.

config.json contains passwords and is in .gitignore. Only commit config.example.json, and without passwords.

usage

oratest get databases               # list the configured databases (no connection, no passwords)
oratest get users                   # connected users of all databases, as a table
oratest get users -t OMC            # all databases of one tenant (-t IPG,OMC for several)
oratest get users -d omc-prod       # only these databases by name (-d a,b for several)
oratest get users -t OMC -d omc-prod   # both filters: must match both
oratest get users -j                # JSON output
oratest get users -c                # write a CSV file {date}_users.csv

Global flags:

flag	description
--config <path>	config file, default ./config.json or env ORATEST_CONFIG
-t, --tenant	only databases of these tenants
-d, --db	only these databases by name
--timeout 30s	timeout per database

get users lists each machine + os user combination once per schema. All selected databases are queried in parallel. If one fails, the others are still shown, the error is printed as [tenant/name] message (or listed under errors in JSON) and the exit code is 1.

Table output:

TENANT  DATABASE        SCHEMA        MACHINE              OS_USER        LOGON_TIME           STATUS
OMC     DMLPROD.IT.NET  DML2OMG_BASE  oneds\ODEMEW1-PS289  some.user      2026-11-03 23:24:24  INACTIVE

TENANT  DATABASE        SCHEMA        COUNT
OMC     DMLPROD.IT.NET  DML2OMG_BASE  1

JSON output:

{
  "items": [
    {
      "tenant": "IPG",
      "database": "awsembwdml002.emea.corp.ipgnetwork.com",
      "schema": "DML_PROD",
      "machine": "IPGEMEA\\AWSEFEDTST001",
      "os_user": "some.user",
      "logon_time": "2026-11-02T09:54:15+01:00",
      "status": "INACTIVE"
    }
  ],
  "results": [
    { "tenant": "IPG", "database": "awsembwdml002.emea.corp.ipgnetwork.com", "schema": "DML_PROD", "count": 1 }
  ],
  "errors": [
    { "tenant": "OMC", "database": "DMLTEST.IT.NET", "error": "host not reachable: ..." }
  ]
}

project layout

path	content
main.go	entry point
cmd/	cobra commands (get databases, get users)
config/	loads and validates config.json, tenant/db filters
oradb/	connection and SQL queries
models/	output structures (JSON)

To add a new check:

1. add the query to oradb/oradb.go
2. add a command in cmd/ (see cmd/users.go) and register it in cmd/get.go; eachDB(...) connects to every selected database in parallel.

Note: main.go marks Go's standard flag set as parsed on purpose. The Oracle driver would otherwise parse the command line itself and fail on our flags.

TODO'S

• command to kill open sessions (ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;) with a list of os users not to kill
• check, if schema is valid
• create a microservice integrated in fedwork(?)
• add support for multiple schemas (PROD, SPIELWIESE)
• show all supported schemas in the json output
• support several databases and tenants via config.json