In Oracle Enterprise Manager (OEM), database administrators often need to monitor and manage user accounts. These accounts can accumulate over time, and tracking their status, creation date, and expiration helps prevent security risks and potential login issues.

The following SQL query retrieves a list of ODBC user accounts across multiple targets and hostnames in the OEM repository (MGMT$DB_USERS), grouped by hostname and target name:

SELECT 
    host_name,
    target_name,
    target_type,
    username,
    MIN(created) AS created,
    profile,
    MIN(expiry_date) AS expiry_date
FROM 
    MGMT$DB_USERS
WHERE 
    username LIKE '%DBADEEDS%'
GROUP BY 
    host_name,
    target_name,
    target_type,
    username,
    profile
ORDER BY 
    host_name ASC,
    target_name ASC;

Security Audits: Identify unused or stale ODBC accounts that may need disabling.

Account Expiry Monitoring: Monitor upcoming account expirations to prevent unexpected application failures.

Happy OEM Querying! 🎯