oracle

Reads:

SELECT a.sid,SUBSTR(MACHINE,1,15),SUBSTR(A.USERNAME,1,15),

B.CONSISTENT_GETS,

B.PHYSICAL_READS,A.PROGRAM,A.LOCKWAIT FROM V$SESS_IO B, V$SESSION A

WHERE A.SID = B.SID and B.PHYSICAL_READS > 10 ORDER BY 5 DESC

/

Date Alter:

ALTER SESSION

SET NLS_DATE_FORMAT = ' MM-DD-yyyy HH24:MI:SS (day)'

Date Back to normal:

ALTER SESSION

SET NLS_DATE_FORMAT = ' MM-DD-yy'

Extents report:

select substr(segment_name,1,25), segment_type, max(extent_id) from dba_extents

where owner = 'SYSADM' HAVING MAX(EXTENT_ID) > 1

GROUP BY SEGMENT_NAME,segment_type ORDER BY 3

Free space report:

select TABLESPACE_NAME,TO_CHAR(SUM(BYTES),'999,999,999,999.99') from dba_free_space where tablespace_name <> 'PSTEMP'

GROUP BY TABLESPACE_NAME ORDER BY 2

Get sql report:

ACCEPT SID CHAR PROMPT 'ENTER SID:';

select * from V$SQLtext A, V$SESSION B where

A.ADDRESS = B.SQL_ADDRESS AND B.SID = '&SID' ORDER BY a.piece

Get Hit ratio:

SELECT USERNAME,

CONSISTENT_GETS,

BLOCK_gets,

PHYSICAL_READS,

100*(CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS)/

(CONSISTENT_GETS+BLOCK_GETS) HitRatio, v$session.sid

from v$session,v$sess_io

where v$session.sid = v$sess_io.sid

and (consistent_gets+block_gets) > 0

and username is not null order by 5 desc

How many bytes:

select to_char(sum(bytes),'999,999,999,999.99'),segment_type from dba_extents

group by segment_type

What Columns are indexed:

ACCEPT IND CHAR PROMPT 'ENTER TABLE NAME:';

select COLUMN_POSITION,SUBSTR(index_name,1,20),SUBSTR(A.column_name,1,20),B.NUM_DISTINCT,

B.LAST_ANALYZED,B.DENSITY from dba_ind_columns A,DBA_TAB_COL_STATISTICS B where

A.table_name = UPPER('&IND') AND A.COLUMN_NAME = B.COLUMN_NAME

AND A.TABLE_NAME = B.TABLE_NAME AND

A.INDEX_OWNER = 'SYSADM' AND A.TABLE_OWNER = 'SYSADM' ORDER BY 2,1

Locks report:

rem $TOOLS/locks.sql

rem

rem Displays locking information for the current ORACLE_SID database

rem

rem Last Change 12/04/96 by Brian Lomasky

rem

set termout off

set heading off

col dbname1 new_value dbname noprint

select name dbname1 from v$database;

set termout on

set heading on

set pagesize 9999

column osuser format a14 heading "-----O/S------|Username Pid"

column username format a17 heading "-----ORACLE-----|Username ID Ser"

column locktype format a10 heading "Type"

column held format a9 heading "Lock Held"

column object_name format a15 heading "Object Name" wrap

column request format a9 heading " Lock|Requested"

column id1 format 999999

column id2 format 9999

spool locks.lis

ttitle center 'Lock report for the ' &&dbname ' database' skip 2

select rpad(osuser, 9)||lpad(p.spid, 5) osuser,

rpad(s.username,8)||lpad(s.sid, 4)||lpad(s.serial#, 5) username,

decode(l.type,

'MR', 'Media Reco',

'RT', 'Redo Thred',

'UN', 'User Name',

'TX', 'Trans',

'TM', 'DML',

'UL', 'PL/SQL Usr',

'DX', 'Dist. Tran',

'CF', 'Cntrl File',

'IS', 'Inst State',

'FS', 'File Set',

'IR', 'Inst Reco',

'ST', 'Disk Space',

'TS', 'Temp Seg',

'IV', 'Cache Inv',

'LS', 'Log Switch',

'RW', 'Row Wait',

'SQ', 'Seq Number',

'TE', 'Extend Tbl',

'TT', 'Temp Table',

l.type) locktype,

' ' object_name,

decode(lmode,1,Null,2,'Row Share',3,'Row Excl',4,'Share',

5,'Sh Row Ex',6,'Exclusive',' ') held,

decode(request,1,Null,2,'Row Share',3,'Row Excl',4,'Share',

5,'Sh Row Ex',6,'Exclusive',' ') request

from v$lock l, v$session s, v$process p

where s.sid = l.sid and

s.username <> ' ' and

s.paddr = p.addr and

l.type <> 'TM' and

(l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)

union

select rpad(osuser, 9)||lpad(p.spid, 5) osuser,

rpad(s.username,8)||lpad(s.sid, 4)||lpad(s.serial#, 5) username,

decode(l.type,

'MR', 'Media Reco',

'RT', 'Redo Thred',

'UN', 'User Name',

'TX', 'Trans',

'TM', 'DML',

'UL', 'PL/SQL Usr',

'DX', 'Dist. Tran',

'CF', 'Cntrl File',

'IS', 'Inst State',

'FS', 'File Set',

'IR', 'Inst Reco',

'ST', 'Disk Space',

'TS', 'Temp Seg',

'IV', 'Cache Inv',

'LS', 'Log Switch',

'RW', 'Row Wait',

'SQ', 'Seq Number',

'TE', 'Extend Tbl',

'TT', 'Temp Table',

l.type) locktype,

object_name,

decode(lmode,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',

5,'Sh Row Ex',6,'Exclusive',NULL) held,

decode(request,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',

5,'Sh Row Ex',6,'Exclusive',NULL) request

from v$lock l, v$session s, v$process p, sys.dba_objects o

where s.sid = l.sid and

o.object_id = l.id1 and

l.type = 'TM' and

s.username <> ' ' and

s.paddr = p.addr

union

select rpad(osuser, 9)||lpad(p.spid, 5) osuser,

rpad(s.username,8)||lpad(s.sid, 4)||lpad(s.serial#, 5) username,

decode(l.type,

'MR', 'Media Reco',

'RT', 'Redo Thred',

'UN', 'User Name',

'TX', 'Trans',

'TM', 'DML',

'UL', 'PL/SQL Usr',

'DX', 'Dist. Tran',

'CF', 'Cntrl File',

'IS', 'Inst State',

'FS', 'File Set',

'IR', 'Inst Reco',

'ST', 'Disk Space',

'TS', 'Temp Seg',

'IV', 'Cache Inv',

'LS', 'Log Switch',

'RW', 'Row Wait',

'SQ', 'Seq Number',

'TE', 'Extend Tbl',

'TT', 'Temp Table',

l.type) locktype,

'(Rollback='||rtrim(r.name)||')' object_name,

decode(lmode,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',

5,'Sh Row Ex',6,'Exclusive',NULL) held,

decode(request,1,NULL,2,'Row Share',3,'Row Excl',4,'Share',

5,'Sh Row Ex',6,'Exclusive',NULL) request

from v$lock l, v$session s, v$process p, v$rollname r

where s.sid = l.sid and

l.type = 'TX' and

l.lmode = 6 and

trunc(l.id1/65536) = r.usn and

s.username <> ' ' and

s.paddr = p.addr

order by 5, 6;

spool off

Not in tree report:

select * from psqryrecord a where not exists (

select 'x' from pstreenode b where b.tree_node = a.recname)

Rawtohex report:

select rawtohex(qryname) from psqrydefn

Rowid report;

select rowid,rownum,open_trans_id,to_char(dttm_stamp,'YYYY-MM-DD-HH24.MI.SS')

FROM PS_OPEN_TRANS

Scans Report:

ACCEPT SID CHAR PROMPT 'ENTER SESSION ID:';

select substr(a.machine,1,10),a.sid,substr(a.program,1,10),substr(b.name,1,25),c.value from v$session a,v$statname b, v$sesstat c

where a.sid=c.sid and b.statistic#=c.statistic#

and c.value <> 0 AND b.name like '%scan%' and A.SID = &SID

Sorts Report:

SELECT name, value

FROM v$sysstat

WHERE name IN ('sorts (memory)', 'sorts (disk)')

UTLXPLAN Plan table creation:

create table PLAN_TABLE (

statement_id varchar2(30),

timestamp date,

remarks varchar2(80),

operation varchar2(30),

options varchar2(30),

object_node varchar2(128),

object_owner varchar2(30),

object_name varchar2(30),

object_instance numeric,

object_type varchar2(30),

optimizer varchar2(255),

search_columns number,

id numeric,

parent_id numeric,

position numeric,

cost numeric,

cardinality numeric,

bytes numeric,

other_tag varchar2(255),

partition_start varchar2(255),

partition_stop varchar2(255),

partition_id numeric,

other long);