This is a fairly common situation for a DBA: you can always connect as SYSDBA, but sometimes you really need to connect as a regular database user (i.e. to have proper environment for your queries).
If you are root in UNIX, this would be extremely easy to do:
Unfortunately, ORACLE does not have an equivalent su command and, even if you are SYS, to connect to any regular database user you need to know its password (which could be a problem).
Still, there is a simple workaround that can be applied here. Essentially, you can save user’s old password, change it to some dummy value and quickly change it back after your connection is established.
While user password hashes are no longer exposed in DBA_USERS view (starting with 11g, I believe), they are still available in SYS.USER$.
Below is the script that does exactly that (full credit goes to Matt Parker who showed me this trick).
COLUMN user_pwd new_value SZ_PWD
SET termout off
SELECT password AS user_pwd FROM USER$ WHERE UPPER(name)='&BECOME_USER';
prompt SET termout off
prompt CONNECT / AS sysdba
prompt ALTER USER &BECOME_USER IDENTIFIED BY VALUES '&SZ_PWD';;
ALTER USER &BECOME_USER IDENTIFIED BY TEMPORARY;
host sqlplus -S /nolog @/tmp/.&BECOME_USER..CHANGE
host rm /tmp/.&BECOME_USER..CHANGE
SET termout ON
Not sure if it is news, but here we go …
I was recently looking at hard parsing SQL statements overtaking one of my systems and needed to find literal SQLs that caused the problem.
The traditional way to look for literal SQLs in ORACLE (at least as it was for me) is to search for statements that are exactly the same in the first N symbols, something like:
GROUP BY substr(sql_text, 1, 80)
HAVING COUNT(1) > 10
ORDER BY 2
This seems to be a rather odd exercise – why would you ever agree to have less memory on your system to run programs ?
But, of course it makes sense in a few special cases – testing how much memory your programs really need as well as validating how the system will behave if less memory is available (not every system is privileged to start with 32Gb+ of RAM).