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;