Oracle DBA Scripts

8 Nov

Script to see the Current wait for all active sessions

   SELECT NVL(a.username, ‘(oracle)’) AS username,
a.osuser,
a.sid,
a.serial#,
d.spid AS process_id,
a.wait_class,
a.seconds_in_wait,
a.state,
a.blocking_session,
a.blocking_session_status,
a.module,
TO_CHAR(a.logon_Time,’DD-MON-YYYY HH24:MI:SS’) AS logon_time
FROM v$session a, v$process d
WHERE a.paddr = d.addr
AND a.status = ‘ACTIVE’
ORDER BY 1,2;

View the result

How to Displays scheduler information about windows:

     SELECT window_name,
resource_plan,
enabled,
active,
comments
FROM dba_scheduler_windows
ORDER BY window_name;

Check the results.

How to Display the status of buffers in the SGA

   SELECT t.name AS tablespace_name,
o.object_name, SUM(DECODE(bh.status, ‘free’, 1, 0)) AS free,
SUM(DECODE(bh.status, ‘xcur’, 1, 0)) AS xcur,
SUM(DECODE(bh.status, ‘scur’, 1, 0)) AS scur,
SUM(DECODE(bh.status, ‘cr’, 1, 0)) AS cr,
SUM(DECODE(bh.status, ‘read’, 1, 0)) AS read,
SUM(DECODE(bh.status, ‘mrec’, 1, 0)) AS mrec,
SUM(DECODE(bh.status, ‘irec’, 1, 0)) AS irec
FROM v$bh bh
JOIN dba_objects o ON o.object_id = bh.objd
JOIN v$tablespace t ON t.ts# = bh.ts#
GROUP BY t.name, o.object_name; 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: