Wednesday, May 30, 2012

Some Useful oracle query for every dba

1. To find the DDL of schema's in Oracle Database Command to find the DDL of Schema's:

select dbms_metadata.get_ddl( 'USER','username' ) from dual
     UNION ALL
     select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','username') from
dual
          UNION ALL
          select dbms_metadata.get_granted_ddl('OBJECT_GRANT','username')
from dual
               UNION ALL
               select dbms_metadata.get_granted_ddl('ROLE_GRANT','username')
from dual;

2.how to trace a querry before executing that

Enable SQL TRACE for a SQL*Plus session (this starts tracing for an individual session):

sql> alter session set SQL_TRACE true;

now run your querry

find the hint of trace file generated using following querry

Select spid, s.sid,s.serial#, s.username, p.program
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select sid from v$mystat where rownum=1);


--- the aboe query will returns the sid of the given session
Disable SQL TRACE for the SQL*Plus session:

alter session set SQL_TRACE false;


3.See what SQL users are running on the system

select a.sid, a.serial#, b.sql_text from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.username='SFMS';

4.Here is a query that gives us a list of blocking sessions and the sessions that they are blocking:

select
   blocking_session,
   sid,
   serial#,
   wait_class,
   seconds_in_wait
from
   v$session
where
   blocking_session is not NULL
order by
   blocking_session;

BLOCKING_SESSION        SID    SERIAL# WAIT_CLASS           SECONDS_IN_WAIT
---------------- ---------- ---------- -------------------- --------
             148        135      61521 Idle                              64

In this case, we find that session 148 is blocking session 135 and has been for 64 seconds. We would then want to find out who is running session 148, and go find them and see why they are having a problem.

note:-blocking_session is availabe from 10g onwards

5.Identify the blocking session

   select l1.sid, ' IS BLOCKING ', l2.sid
    from v$lock l1, v$lock l2
   where l1.block =1 and l2.request > 0
   and l1.id1=l2.id1
   and l1.id2=l2.id2;

Detailed inforamation

select s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
   || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    from v$lock l1, v$session s1, v$lock l2, v$session s2
    where s1.sid=l1.sid and s2.sid=l2.sid
   and l1.BLOCK=1 and l2.request > 0
   and l1.id1 = l2.id1
   and l2.id2 = l2.id2 ;

identifieying the content of locked row

select do.object_name,
   row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
    dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
    from v$session s, dba_objects do
    where sid=143
    and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

if your output is like this

OBJECT_NAME     ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- ------------- -------------- --------------- ------------- ------------------
TSTLOCK                 88519             16          171309             0 AAAVnHAAQAAAp0tAAA

Then obtain the locked row by


select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR
--- ---
1   a

6.redo log switch per day

select trunc(completion_time) rundate
,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"
from v$archived_log
group by trunc(completion_time)
order by 1 desc;

7.High Physical Read Sessions

Select OSUSER os_user,username,PROCESS pid, ses.SID sid,SERIAL#,PHYSICAL_READS,
BLOCK_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
and username is not null
and status='ACTIVE'
order by PHYSICAL_READS;

8. dbms_stats.gather_table_stats - Stats Gather Dynamic Sql script for tables

SELECT    ' exec sys.dbms_stats.gather_table_stats (ownname=>'''
       || owner
       || ''',tabname=>'''
       || table_name
       || ''', estimate_percent=>SYS.DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>''FOR ALL COLUMNS SIZE 1'',degree=>8,cascade=>true,no_invalidate=>false);'
  FROM dba_tables
WHERE table_name IN
          ('DEPT','BONUS','EMP','SALGRADE')
   AND owner = 'SCOTT';

9. You can check freespace in undo tablesapce with :

select sum(bytes)/1024/1024 "available M in RBS" from dba_undo_extents where status='EXPIRED';

Blocks are expired when undo_retention is past after the commit/rollback of the transaction.

10. how to calculate network bandwidth in data guard configuration

SELECT DT,
SUM(RB*8/3600000000*1.3) Mbps_REQ_A_DAY,
MIN(RB*8/3600000000*1.3) MIN_Mbps_REQ_AN_HOUR,
MAX(RB*8/3600000000*1.3) MAX_Mbps_REQ_AN_HOUR ,
AVG(RB*8/3600000000*1.3) AVG_Mbps_REQ_AN_HOUR
FROM
(
SELECT TRUNC (COMPLETION_TIME) DT,
TO_CHAR (COMPLETION_TIME,'HH24') HH,
SUM(BLOCKS*BLOCK_SIZE) RB
FROM
V$ARCHIVED_LOG
WHERE COMPLETION_TIME > SYSDATE-5
AND DEST_ID=1
GROUP BY TRUNC(COMPLETION_TIME),
TO_CHAR (COMPLETION_TIME, 'HH24')
)
GROUP BY DT
order by DT; 

11. Query to find out features not available in particular database edition

SELECT Parameter,Value FROM V$OPTION Where Value = 'FALSE';

1 comment:

  1. Very useful post.. every dba will be needing this. keep posting good things.

    ReplyDelete