audittype

col DETAIL for a30

SELECT 'Standard Auditing' AS AUDIT_TYPE, CASE WHEN UPPER(value) IN ('DB', 'DB_EXTENDED') THEN 'Enabled ' WHEN UPPER(value) IN ('OS', 'XML', 'XML_EXTENDED') THEN 'Enabled' ELSE 'Disabled' END AS STATUS, value AS DETAIL
FROM v$parameter
WHERE name = 'audit_trail' UNION ALL
SELECT 'Fine-Grained Auditing (FGA)' AS AUDIT_TYPE, CASE WHEN EXISTS
(SELECT 1
FROM dba_audit_policies) THEN 'Enabled' ELSE 'Disabled' END AS STATUS, NULL AS DETAIL
FROM dual UNION ALL
SELECT 'Unified Auditing' AS AUDIT_TYPE, CASE WHEN
(SELECT value
FROM v$option
WHERE parameter = 'Unified Auditing') = 'TRUE' THEN 'Enabled' ELSE 'Disabled' END AS STATUS, NULL AS DETAIL
FROM dual UNION ALL
SELECT 'Mixed Mode Auditing' AS AUDIT_TYPE, CASE WHEN COUNT(*) > 0 THEN 'Enabled' ELSE 'Disabled' END AS STATUS, CASE WHEN COUNT(*) > 0 THEN LISTAGG(policy_name, ', ') WITHIN GROUP (ORDER BY policy_name) ELSE NULL END AS DETAIL FROM audit_unified_enabled_policies;