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 DTorder by DT; 

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

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