Alert tab of oracle Grid Control

We have grid control installed in one of our setups. This is quite useful as you can register all your databases here and monitor them from a centralized control. One of the good things i like about this is the alert tab. By clicking on the alert tab you can easily see all the errors coming in any of the databases whether they are ORA- errors or any other.
So another useful feature and observation from my side.

DBMS_SCHEDULER

This is some task i did for the first time in one of my recent projects.
so just an another attempt to share my learning.
To create a scheduled job the syntax is -

begin
sys.dbms_scheduler.create_job(job_name => 'test_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''ABC''); END;',
repeat_interval => 'FREQ=DAILY;BYHOUR=21;BYMINUTE=45;BYSECOND=0',
start_date => sysdate,
job_class => 'DEFAULT_JOB_CLASS',
comments => 'gather stats job',
auto_drop => TRUE,
enabled => TRUE);
END;

How to check for blocking session in oracle?

It happens many a times that a user calls you and tells you that he has executed a query a long time back which is a very small update or delete statement and the Sql screen is in a hung state. In this case it is proably the user session is being blocked by some other session. So the easiest way to track this is using the v$session view in oracle 10g. This view has a field blocking_session, which will tell you what sid is blocking your session.
e.g
suppose user joe calls and tells you the same problem, his userid is XY123
-
sql>select sid,serial#,blocking_session grom v$session where username='XY123';

output

sql> 523,1234,321

so here we can see session 321 is blocking session 523 of joe.

I have always found this useful as i come across this situation many a times.

Oracle Database- Who is using my undo Tablespace??

It Happened once that i came in morning and logged onto the database. I was just checking tablespaces when i observed undo tablespace is totally full....so what was eatin up my undo..in such case oracle provides you a view called V$transaction that gives you information about transactions using Oracle db undo tablespace. This view can be joined with V$session and with help of this you can easily come to know which user is using your undo tablespace. In my case some developer has left running an update query which was to update a major table at night and had not committed..I called up the developer..and told him to either commit or rollback..he committed his transaction..and undo got freed..

Tracing a User Seeion in Oracle - DBMS_SUPPORT

Many a times we need to trace a user session.

For this oracle has supplied a package called DBMS_TRACE.

To Trace a user session we can start Sql Tracing by using -

exec dbms_support.start_trace_in_session(sid,serial#,binds=>true,waits=>true);

Since we are setting waits=> true this will capture wait information of Sqls.

And Binds=> true will capture bind values.

To stop tracing -

exec dbms_support.stop_trace_in_session(sid,serial#)