Thursday 28 February 2013

Misclleneous Queries

Miscellaneous SQL Queries for DBA's

1) To find corrupt Object/Segments using file_id and block_id



select segment_name,block_id,file_id from dba_extents where FILE_ID=&1 and &2 between block_id and block_id+blocks-1
/

2) Indexes created on Table_name


set lin 10000
col INDEX_NAME for a30
col TABLE_NAME for a30
col index_type for a25
col status for a10
--col LAST_ANALYZED for a19 wrap
col column_name for a20
col column_expression for a25
select a.INDEX_NAME ,b.column_name, column_expression,a.TABLE_NAME,STATUS,INDEX_TYPE
from user_indexes a,user_ind_columns b, user_ind_expressions c
where a.table_name = upper(trim('&1'))
and a.TABLE_NAME=b.TABLE_NAME
and a.INDEX_NAME=b.INDEX_NAME
and a.INDEX_NAME=c.INDEX_NAME (+)
and a.INDEX_NAME=c.INDEX_NAME (+)
/ 

3) Current running query


set lin 10000
col sid for 999999999
col serial# for  9999999999
col osuer for a20
col SQL_TEXT for a70 wrap
col osuser for a10
col status for a10
--select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a,  v$sqlarea b
--where a.SQL_ADDRESS=b.ADDRESS
--and b.HASH_VALUE = a.SQL_HASH_VALUE
select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a,  v$sql b
where a.SQL_ADDRESS=b.ADDRESS
and b.HASH_VALUE = a.SQL_HASH_VALUE
order by 6 desc,5 desc
/

4) To find query how much memory (PGA)


SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 999  HEADING 'SID'
COLUMN oracle_username  FORMAT a12     HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username FORMAT a9  HEADING 'O/S User' JUSTIFY right
COLUMN session_program  FORMAT a18     HEADING 'Session Program' TRUNC
COLUMN session_machine  FORMAT a18      HEADING 'Machine'  JUSTIFY right TRUNC
COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'
COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'
COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'
COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'
SELECT
    s.sid  sid
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , s.program     session_program
  , lpad(s.machine,8)    session_machine
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
    sn.statistic# = ss.statistic# and
    sn.name = 'session pga memory') session_pga_memory
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
    sn.statistic# = ss.statistic# and
    sn.name = 'session pga memory max')    session_pga_memory_max
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
    sn.statistic# = ss.statistic# and
    sn.name = 'session uga memory') session_uga_memory
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
    sn.statistic# = ss.statistic# and
    sn.name = 'session uga memory max')    session_uga_memory_max
FROM
    v$session  s
ORDER BY session_pga_memory DESC
/


-- Active SQL 10g
SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
    SID,  
    MACHINE,
    REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
    ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
    || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
    || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT
FROM    V$SESSION SES,  
    V$SQLtext_with_newlines SQL
where SES.STATUS = 'ACTIVE'
    and SES.USERNAME is not null
    and SES.SQL_ADDRESS    = SQL.ADDRESS
    and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
    and Ses.AUDSID <> userenv('SESSIONID')
order by runt desc, 1,sql.piece;

No comments:

Post a Comment