This is a technical note about using database proxy users with SQLTXPLAIN.
Since Oracle RDBMS 10.2, there has been the ability to connect as a proxy user to another database account through the SQL*Plus utility. This is extremely useful for DBAs diagnosing performance problems using the SQLTXPLAIN utility.
- How to Create & Use Proxy Users In The Database (Doc ID 1486070.1)
- Proxy Users and Auditing Proxy Users (Doc ID 782078.1)
- Why Locked Out Users Can Be Used As Proxy Users ? (Doc ID 580488.1)
- SQLT Diagnostic Tool (Doc ID 215187.1)
- SQLTXPLAIN is already installed
APP_USERis an exitsing database account that has the poorly-performing SQL query
From the procedure described in MOS Doc ID 1486070.1,
-- Account MUST be UNLOCKED CREATE USER dba_user IDENTIFIED BY "Password123"; GRANT CREATE SESSION TO dba_user; ALTER USER app_user GRANT CONNECT THROUGH dba_uer; GRANT sqlt_user_role TO app_user; CONNECT dba_user[app_user]/Password123 -- Run SQLTXPLAIN Utility
The information in MOS Doc ID 580488.1 is incorrect for 18.104.22.168 as locking the
DBA_USER account does not work.
See MOS Doc ID 580488.1 about how to audit activity by proxy users.