Scripts Dba

locked object select o.object_name,l.oracle_username,l.os_user_name,l.session_id ,decode(l.locked_mode,2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6 ,'Exclusive','NULL') from user_objects o , v$locked_object l where o.object_id = l.object_id; rollback segment contention select name, waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn system wait contention select event, sum(decode(wait_Time,0,0,1)) "Prev", sum(decode(wait_Time,0,1,0)) "Curr", count(*) "Tot" from v$session_Wait group by event order by 4

Description: This script will display the active user and the rollback segment being used in the database SELECT r.name rr, nvl(s.username,'no transaction') us, s.osuser os, s.terminal te FROM v$lock l, v$session s, v$rollname r WHERE l.sid = s.sid(+) AND trunc(l.id1/65536) = r.usn AND l.type = 'TX' AND l.lmode = 6 ORDER BY r.name This script reports how many hours it has been since the rollback segments wrapped select n.name, round(24*(sysdate-to_date(i1.value||' '||i2.value,'j SSSSS')) / (s.writes/s.rssize),1) "Hours" from v$instance i1, v$instance i2, v$rollname n, v$rollstat s where i1.key = 'STARTUP TIME - JULIAN' and i2.key = 'STARTUP TIME - SECONDS' and n.usn = s.usn and s.status = 'ONLINE' Gives lots of usefull easy to read info on how your RBS are performing. Needs 132 char display

select name, XACTS, initial_extent/1048576 InitExt, next_extent/1048576 NextExt, min_extents MinExt, max_extents MaxExt, optsize/1048576 optsize, RSSIZE/1048576 rssize, HWMSIZE/1048576 hwmsize, wraps, extends, shrinks, aveshrink/1048576 aveshrink, gets, waits, writes/1024 writes, writes/gets wpg from v$rollstat,v$rollname,dba_rollback_segs where v$rollstat.usn=v$rollname.usn and dba_rollback_segs.segment_id=v$rollname.usn order by name display all sql in shared cache doing fulltable access select count(*) into v_count from plan_table where options like '%FULL%' and operation like '%TABLE%' ; if v_count > 0 then insert into full_sql(sql_text,executions) values (x1.sql_text, x1.executions) ; end if; end loop ; commit; end ; / execute p_findfullsql ; select * from full_sql; drop table full_sql; buffer cache hit ratio select a.value + b.value "logical_reads", c.value "phys_reads", round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40; Increase Shared pool size to reach a 90% hit ratio on Dictionary Cache. Entries for dc_table_grants, d_user_grants, and dc_users should be under 5% each in the MISS RATE % column select parameter, gets, Getmisses ,

getmisses/(gets+getmisses)*100 "miss ratio", (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" from v$rowcache where gets+getmisses <>0 group by parameter, gets, getmisses ; Reduce the Reloads and try to increase the hit ratios to above 85% select namespace,gets,gethits,gethitratio,pins,pinhits, pinhitratio, reloads from v$librarycache where gets+gethits+pins+pinhits>0 ; This should be near 0.If the Ratio is larger than 1% then increase the SHARED_POOL_SIZE select sum(pins) "Total Pins", sum(reloads) "Total Reloads", sum(reloads)/sum(pins) *100 libcache from v$librarycache To run this script you must get connect as user sys select name, 1 - ( physical_reads / ( db_block_gets + consistent_gets)) "HIT RATIO" from sys.v$buffer_pool_statistics where db_block_gets + consistent_gets > 0 CASE STUDY: NCsoft Supports Massive Number of Simultaneous Connections CASE STUDY: Learn how Microsoft helped the state of Illinois serve its 12 million constituents more quickly while saving taxpayer dollars WHITEPAPER: Linux vs. Microsoft--Making the Right Choice for Your Client-Server Infrastructure WHITEPAPER: Using the .NET Framework to Increase Performance, Availability, & Business Agility

Maximizing the Performance of SUSE Linux Enterprise Real Time for Financial Services Applications on AMD64 Technology Whitepaper: The technology needs of financial services companies offer a remarkable challenge for computer hardware and software vendors. The central requirement is deterministic, real-time computing for high-priority transactions that must execute accurately and predictably every time. However, a real-time computing solution must also offer rock-solid stability and reliability, since there is zero tolerance for missed transactions, lost records, or system downtime. Click here. Real-World Results of SUSE Linux Enterprise Real Time Whitepaper: What if you had a dedicated lane in your computer, similar to the car

pool lane on the highway? A lane that could guarantee your high-priority processes could run without being interrupted. That's what you get with SUSE Linux Enterprise Real Time. Its broad functionality provides highly deterministic performance for chosen applications, and CPU shielding provides a dedicated processor core to guarantee deterministic low-latency performance regardless of system load or traffic. Click here. Technical Whitepaper: SUSE Enterprise Linux Real Time Whitepaper: This paper outlines the specific technical capabilities of SUSE Linux Enterprise Real Time, including enterprise services offerings, and shows how SUSE Linux Enterprise Real Time delivers a solid foundation for your Service-oriented Infrastructure (SOI). Learn how SUSE Linux Enterprise Real Time can be used to build the next generation of data-center computing, creating an on-demand infrastructure that is modular, highly responsive and easy to deploy and control. Click here.

Description: Code:

The Hit Ratio should be higher than 90%

select Username, OSUSER, Consistent_Gets, Block_Gets, Physical_Reads, 100*( Consistent_Gets + Block_Gets - Physical_Reads)/ ( Consistent_Gets + Block_Gets ) "Hit Ratio %" 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 Username,"Hit Ratio %"; Description: If you want to copy the data from one database to another having many tables and constraints,all you need is this script which will first disable all the constraints then delete the prior data and then finaly copy the data from any remote database and finaly it enables all the constraints on the table of that database.......all you need is to have a table having names of all the tables and the corresponding constraints Code: CREATE OR REPLACE procedure pre_tra as cursor cconstraints is select table_name, constraint_name, status from all_constraints where owner='owner name'; c2 cconstraints%rowtype; mytab varchar2(200); mytab1 varchar2(200); mytab3 varchar2(200); mytab4 varchar2(200); retrycounter number; counter number;

cstatus varchar2(20); enableerrorfound boolean:=true; --MAX_TRY number:=10000; begin begin open cconstraints; loop fetch cconstraints into c2; exit when cconstraints%notfound; mytab := c2.table_name; mytab1 := c2.constraint_name; mytab3 :='alter table ' || mytab || ' disable constraint ' || mytab1; execute immediate mytab3; end loop; close cconstraints;

open cconstraints; loop fetch cconstraints into c2; exit when cconstraints%notfound; mytab :=c2.table_name; execute immediate 'delete ' || mytab; end loop; close cconstraints; end; open cconstraints; loop fetch cconstraints into c2; exit when cconstraints%notfound; mytab :=c2.table_name; mytab4 :='DATABASENAME'; execute immediate 'insert into ' || mytab || ' (select * from '|| mytab || '@' || mytab4 || ')'; DBMS_OUTPUT.PUT_LINE(mytab); commit; DBMS_OUTPUT.PUT_LINE(mytab); end loop; close cconstraints; begin DBMS_OUTPUT.PUT_LINE('Starting enable script.....'); RETRYCOUNTER := 3; WHILE RETRYCOUNTER > 0 loop open cconstraints; loop

fetch cconstraints into c2; exit when cconstraints%notfound; DBMS_OUTPUT.PUT_LINE('retrycounter==>'||mytab); mytab := c2.table_name; mytab1 := c2.constraint_name; --cstatus:= c2.status; --if cstatus = 'DISABLED' then mytab3 :='alter table ' || mytab || ' enable constraint ' || mytab1; DBMS_OUTPUT.PUT_LINE('Q' || mytab3); DBMS_OUTPUT.PUT_LINE(retrycounter); --enableerrorfound:=false; BEGIN execute immediate mytab3; EXCEPTION WHEN OTHERS THEN enableerrorfound:=true; END; --end if;

end loop; RETRYCOUNTER := RETRYCOUNTER - 1; DBMS_OUTPUT.PUT_LINE(retrycounter); close cconstraints; end loop; DBMS_OUTPUT.PUT_LINE(retrycounter); IF (enableerrorfound) THEN DBMS_OUTPUT.PUT_LINE('enableerrorfound==> TRUE'); else DBMS_OUTPUT.PUT_LINE('enableerrorfound==> FALSE'); end if; end; end pre_tra; Description: Code: column column column column column column ttitle

List the UGA and PGA used by each session on the server

name format a25 total format 999 heading 'Cnt' bytes format 9999,999,999 heading 'Total Bytes' avg format 99,999,999 heading 'Avg Bytes' min format 99,999,999 heading 'Min Bytes' max format 9999,999,999 heading 'Max Bytes' 'PGA = dedicated server processes - UGA = Client machine process'

compute sum of minmem on report compute sum of maxmem on report break on report select se.sid,n.name, max(se.value) maxmem from v$sesstat se,

v$statname n where n.statistic# = se.statistic# and n.name in ('session pga memory','session pga memory max', 'session uga memory','session uga memory max') group by n.name,se.sid order by 3 Description: than 1 Code:

Try to reduce the contention by reducing all the ratios to be less

SET feedback OFF COLUMN name FORMAT a15 COLUMN gets FORMAT 99999999 COLUMN misses FORMAT 999999 COLUMN immediate_gets FORMAT 99999999 HEADING 'IMM_GETS' COLUMN immediate_misses FORMAT 99999999 HEADING 'IMM_MISSES' PROMPT Examining Contention for Redo Log Buffer Latches... PROMPT ---------------------------------------------------SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROM v$latch WHERE name IN ('redo allocation', 'redo copy');