Unix Commands that always usefull

netstat -na grep 1522
ps -ef grep lsnr
glance -j 1
du -k sort -n
du -sk * sort -nrhead
df -k grep /bkdump
ifconfig -a
du -sh *
ipcs -m grep ora

What is blocking at the DB Level

On web i found this script which helped me a lot :-)
***************************************************
SQL> select s1.username '@' s1.machine
2 ' ( SID=' s1.sid ' ) is blocking '
3 s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
4 from v$lock l1, v$session s1, v$lock l2, v$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2 ;

BLOCKING_STATUS
--------------------------------------------------------------------------------
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=32 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=36 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=45 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=49 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=72 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=86 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=90 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=99 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=123 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=141 )

10 rows selected.

SQL> alter system kill session '88,24356';
System altered.
SQL> select * from v$lock where block <> 0 or request <> 0;
no rows selected
************************************
Please let me know your experience in handling this issue.

Logging In As a Different User – When you don’t know the Password

You may need to sometimes log in as another DBA to perform certain actions. However, even the Oracle DBA doesn’t have access to users’ passwords, which are stored in an encrypted form. You could use the ALTER USER statement to change the user’s password, but you might not want to inconvenience the user by changing the password for good.

In a case like this, you can change the password of a user temporarily and use the new password to get in as that user. Before you change a user’s password, get the original encrypted password, which you can use to reset the user’s password back after you’re done. Here’s an example:

SQL> SELECT 'alter user tester identified by values 'password';'
2 FROM dba_users
3* WHERE username='TESTER';

'ALTERUSERTESTERIDENTIFIED
---------------------------------------------------------
alter user tester identified by values 1825ACAA229030F1;

SQL>
Now change the password of user tester so you can log in as that user:

SQL> ALTER USER tester IDENTIFIED BY newpassword;
When you’re done using the tester user account, use the ALTER USER statement again to change user tester’s password back to its original value. Make sure you enclose the encrypted password in single quotes.

SQL> ALTER USER tester IDENTIFIED BY VALUES '1825ACAA229030F1';
User altered.

What would be the first thing you would do if an end user complains that performance is poor?

I always used to come up with this question on forums and well as from the Application support, and a time with my first interview in India.
As for the answer to the question, one should touch upon all these points:
- Check alert log, for any errors and UN even entries
- Check for archive log destination/file system full
- Check for database locks # Click here for a notes on locks
- Check server performance (CPU/IO)
- Check network performance
- Check if statistics is up to date
- If new SQL, then tune SQL (maybe take trace)
- Check if any parameters have been modified recently
- Check if any new updates as been done from the Application team.

And few more from Oracle Online Documentation as well
1) Identifying and Gathering Data on Resource-Intensive SQL # Oracle Doc
2) Oracle9i Database Performance Tuning Guide and Reference # Oracle Doc
I recommend reading the documents from Oracle, which give clear understanding of all the above. Please add lines if you I miss something which is useful.