SU to another user in Oracle

Most DBAs have to execute scripts as the schema owner for them to work correctly. While the traditional method of logging in as that user works fine most of the time. There are times when one does not know the password or have access to the secure location containing the password. Being the DBA we have some options, we can change the password, but this could break other programs. We can retrieve the password hash and change the password execute the scripts and return the password back to the hash value.

If we are using Oracle 10.2 or higher we have the option to use the proxy option. we can grant our DBA account privileges to the schema account and not have to know the password.

alter user schemaowner grant connect through dbauser;

Then the DBA can connect to the schema user by

sqlplus> connect dbauser[schemaowner]@mydatabase

Performing a show user at the SQL*Plus prompt will return

show user

schemaowner

This should be the preferred option for DBA access when needing to run scripts for patches and upgrades when access to the password is not available.

In Oracle 11g the password hash is not viewable through the dba_users view, so using the has value trick is soon to be phased out and is not a great practice anyway.

Monitoring Manual vs automated, vs hybrid

What is your preferred method to monitor databases? Should DBAs rely on just Grid control, Tivoli and other automated agents to monitor their databases? What about manual checking everyday to see if you can find a problem? This is a debate that always comes up and some like scripts, and some want to check everyday. What if you have 200-300, or even more databases to check, is this it realistic just rely on one method?

I am curious to know others preferences. I prefer an automated solution such as Oracle's Grid Control. If there is some type of alert occurring out of the normal,then have someone check it out, otherwise focus your efforts on more value added work. I am sure SQL Server has a robust monitoring solution from Quest or Red Gate too, but I am not as familiar with them.

What is the best solution?