Thursday, December 20, 2007

Resolve hanging mview refresh job (UNIX)

The above steps must be followed in order to resolve a hanging mview refresh job.

select * from dba_jobs_runing in order to find the job number(JOB_ID) and the sid (SID)
of the hanging job.

exec dbms_job.broken(JOB_ID,TRUE); in order to broke the job

select a.spid, b.sid, b.username
from v$process a, v$session b
where a.addr = b.paddr and sid=SID;
in order to find the process id (SPID)

On the O/S kill the process: kill -9 SPID

And after that run the job .

dbms_job.run(JOB_ID);

Same process is working on windows too , except the concept how to kill the process on windows.

Wednesday, December 19, 2007

Friday, December 14, 2007

Drop temporary tablespace hang

I issued a drop tablespace temp and it hang. If you are in the same situation check the above:

select username,session_num,session_addr from v$sort_usage
where tablespace='[Name of the temp tablespace you want to drop]';

if this query return rows use the session_num to find the sid from the v$session.

select sid,serial#,status from v$session where serial#=[session_num];

Alternatively use the session_addr.

select sid,serial#,status from v$session where saddr='[session_addr]';

and then kill the session at will.

alter system kill session '[sid],[serial#]' immediate;

If in RAC then check each instance !!!

Thursday, December 13, 2007

Monitoring Redo Latches

The following query caclulates 2 ratios for redo copy latch & redo allocation latch.

If either of the 2 ratios is above 1% then we have latch contention.

SELECT substr(ln.name, 1, 20) "Latch"
, round(misses/gets,2) "misses/gets ratio",
case when immediate_gets=0 then 0 else
round(immediate_misses/immediate_gets,2)
end "immediate misses/gets ratio"
FROM v$latch l, v$latchname ln
WHERE ln.name in ('redo allocation', 'redo copy')
and ln.latch# = l.latch#;

Tuesday, December 11, 2007

Materialized Views & Refresh Groups

mviews & refresh groups queries

The follwoing query (9i and onwards) shows which mview is refreshed the current time.

select currmvowner, currmvname
from v$mvrefresh;


begin
DBMS_MVIEW.EXPLAIN_MVIEW('MVIEW_NAME');
end;
/

Analyzes mview and by querying the MV_CAPABILITIES_TABLE you can see the details of the analysis e.g if mview is fast refreshable.

Refresh Groups

All refresh groups with its mviews

select r.rowner, r.rname, r.job, c.name,
r.next_date next_refresh, r.broken
from all_refresh r, all_refresh_children c
where r.job = c.job;

select o.owner, o.object_name mview, username, s.sid
from v$lock l, dba_objects o, v$session s
where o.object_id=l.id1 and
l.type='JI' and
l.lmode=6 and
s.sid=l.sid and
o.object_type='TABLE';

If u use refresh groups the above query shows the locks on the base tables of mview so you can see if the refresh group is running.

Monday, December 3, 2007

DAY to DAY

Last days , i am concerning on monitoring my databases.

I am checking for space problems , performance problems and etc.