Hello Everyone

Im kinda new to Oracle Database ( used to work with Mainframe DB2), and for this new project that Im working, I need to check fo the activity (DML activity) on application schemas. The main reason, there is little documentation for the applications that we currently have, and there is a high possibility that there are old databases that are not being used anymore, and in order to get this information, Im creating a script that can check for these info, so far, this is what I got:

#!/bin/bash

# Directories and data
DATA=$(date +"%Y-%m-%d_%H%M%S")
OUTDIR="$HOME/coletas/oracle_saida"
LOGDIR="$HOME/coletas/logs"
CONF_FILE="$HOME/bancos.conf"
mkdir -p "$OUTDIR" "$LOGDIR"

# Log
LOGFILE="$LOGDIR/coletabanco_${DATA}.log"
echo "executing on $DATA" | tee -a "$LOGFILE"

# Redirect the output and errors to log
echo "Executing on $DATA" | tee -a "$LOGFILE"

# Reading Config file (have the Profiles for each CDB)
while IFS='|' read -r PROFILE PDB; do
echo
echo "========================================="
echo "Processing: PROFILE=$PROFILE | PDB=$PDB"
echo "========================================="

Checks if the profile exists

if [[ ! -f "$HOME/$PROFILE" ]]; then
echo "Profile $HOME/$PROFILE não encontrado. Pulando..."
continue
fi

Setting the CDB Profike

. "$HOME/$PROFILE"
SID=$(echo "$PROFILE" | cut -d. -f1)

Output file

ARQOUT="$OUTDIR/oracle_${SID}_${PDB}_uso_${DATA}.csv"

Connecting to SQLPlus

sqlplus -s / as sysdba <<EOF
SET COLSEP ';'
SET PAGESIZE 0
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET LINESIZE 1000

SPOOL $ARQOUT

PROMPT CONTAINER atual:
ALTER SESSION SET CONTAINER = $PDB;
SHOW CON_NAME;

-- Last statistic per schema
PROMPT === Step 1: Gathering statistic ===
PROMPT
SELECT 'STATS_ANALYZED', OWNER, TO_CHAR(MAX(LAST_ANALYZED), 'YYYY-MM-DD HH24:MI:SS') AS ULTIMA_COLETA
FROM DBA_TAB_STATISTICS
GROUP BY OWNER
ORDER BY 3 DESC FETCH FIRST 10 ROWS ONLY;

PROMPT
PROMPT === DDL activity per schema ===
PROMPT
-- Last DDL per schema
SELECT 'DDL_TIME', OWNER, TO_CHAR(MAX(LAST_DDL_TIME), 'YYYY-MM-DD HH24:MI:SS') AS ULTIMO_DDL
FROM DBA_OBJECTS
GROUP BY OWNER
ORDER BY 3 DESC FETCH FIRST 10 ROWS ONLY;

PROMPT
PROMPT === Checking if audit_trail is enabled ===
PROMPT
show parameter audit_trail;

PROMPT
PROMPT === Checking DML for the last 10 days, showing the last 20 rows ===´
PROMPT
SELECT
os_username,
object_name,
action_name,
event_timestamp
FROM unified_audit_trail
WHERE event_timestamp > SYSDATE - 10
AND action_name IN ('INSERT', 'UPDATE', 'DELETE')
AND os_username NOT IN (
'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'APPQOSSYS', 'AUDSYS',
'XDB', 'APEX_PUBLIC_USER', 'ORDDATA', 'ORDSYS', 'OUTLN',
'ANONYMOUS', 'DVSYS', 'GGSYS', 'WMSYS', 'LBACSYS',
'OJVMSYS', 'CTXSYS', 'ZABBIX')
ORDER BY event_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

SPOOL OFF
EXIT;
EOF
done < "$CONF_FILE"

echo
echo "Finished on $(date)" | tee -a "$LOGFILE"
echo "Logs: $LOGFILE"
echo "Saídas: $OUTDIR/*.txt"

For the last query, its taking a lot of time to get the information, is there an easier and faster way to check when there was DML acitivites on it (would be good to check if any app user read on the schema as well, as it may be a read only datatabase)