Andrew Fraser DBA

Change oracle password temporarily

Want to connect as a particular user but don’t know their password? You can temporarily change it and then reset it back, if you have DBA/alter any user privilege:

set pages 9999 lines 132 long 9999
column col1 form a132
SELECT DBMS_METADATA.get_ddl ( 'USER' , 'MYUSER' ) AS col1 FROM DUAL ;
ALTER USER myuser IDENTIFIED BY mypassword ;
conn myuser/mypassword
ALTER USER myuser IDENTIFIED BY VALUES 'string pasted from above' ;

The above works for versions 11gR1 onwards, for older versions instead use:

SELECT password FROM dba_users WHERE username = 'MYUSER' ;

If doing this a lot, you can set this up permanently to avoid even temporarily changing a users password (from 10gR2 onwards):

ALTER USER myuser GRANT CONNECT THROUGH system ;
conn system[myuser]/system_password

If you just want to run a script without having to edit it to include owner name for each object, setting current_schema is an easier method:

ALTER SESSION SET current_schema = myuser ;

H/t Laurent Schneider and Jonathan Lewis.