eminus-sleepus.blogspot.com
Eminus Sleepus Chamber: Calculate Optimal Undo in Oracle
http://eminus-sleepus.blogspot.com/2009/09/calculate-optimal-undo-in-oracle.html
Sunday, September 20, 2009. Calculate Optimal Undo in Oracle. SELECT d.undo size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",. SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",. ROUND( d.undo size / (to number(f.value) *. Gundo block per sec) ) "OPTIMAL UNDO RETENTION [Sec]". SELECT SUM(a.bytes) undo size. FROM v$datafile a,. WHERE c.contents = 'UNDO'. AND cstatus = 'ONLINE'. AND bname = c.tablespace name. AND ats# = b.ts#. SELECT MAX(undoblks/( end time-begin time)*3600*24). Undo block per sec. FROM v$datafile a,.
eminus-sleepus.blogspot.com
Eminus Sleepus Chamber: checking long operations in Oracle RAC
http://eminus-sleepus.blogspot.com/2009/09/checking-long-operations-in-oracle-rac.html
Sunday, September 20, 2009. Checking long operations in Oracle RAC. SELECT s.sid,. TRUNC(sl.elapsed seconds/60) ':' MOD(sl.elapsed seconds,60) elapsed,. TRUNC(sl.time remaining/60) ':' MOD(sl.time remaining,60) remaining,. ROUND(sl.sofar/sl.totalwork*100, 2) progress pct. FROM gv$session s,. WHERE s.sid = sl.sid. AND sserial# = sl.serial#. AND sinst id = sl.inst id. Subscribe to: Post Comments (Atom). Subscribe To Eminus Sleepus. Place Your ADS here. Join the Dogster community. Learn Java and more.
eminus-sleepus.blogspot.com
Eminus Sleepus Chamber: Open cursor usage in Oracle
http://eminus-sleepus.blogspot.com/2009/09/open-cursor-usage-in-oracle.html
Sunday, September 20, 2009. Open cursor usage in Oracle. Select a.value, s.username, s.sid, s.serial#. From v$sesstat a, v$statname b, v$session s. Where a.statistic# = b.statistic# and s.sid=a.sid. And bname = 'opened cursors current';. Select max(a.value) as highest open cur, p.value as max open cur. From v$sesstat a, v$statname b, v$parameter p. Where a.statistic# = b.statistic#. And bname = 'opened cursors current'. And pname= 'open cursors'. Group by p.value;. Subscribe to: Post Comments (Atom).
eminus-sleepus.blogspot.com
Eminus Sleepus Chamber: eminus's Activities | RunKeeper
http://eminus-sleepus.blogspot.com/2010/10/eminuss-activities-runkeeper.html
Tuesday, October 19, 2010. Subscribe to: Post Comments (Atom). Subscribe To Eminus Sleepus. Place Your ADS here. Join the Dogster community. Powered By: Google Analytics Counter. Http:/ www.kadhiresan.blogspot.com/. Http:/ javajumper.blogspot.com/. Learn Java and more. Http:/ rodgersnotes.blogspot.com. View my complete profile. Http:/ www.linkalizer.com/ Linkalizer. Offers the most efficient way for you to exchange quality links. Picture Window template. Powered by Blogger.
eminus-sleepus.blogspot.com
Eminus Sleepus Chamber: useful when resizing datafile
http://eminus-sleepus.blogspot.com/2010/09/useful-when-resizing-datafile.html
Monday, September 20, 2010. Useful when resizing datafile. This script i got from asktom.com is a usefull script to determine what object/s are in the end of the datafile which will cause for you to not to shrink it the way you want it to shrink :) (I mean if there is a big free space between that object and the other object). Column tablespace name format a20. Column "Name" format a45. Break on file id skip 1. Select file id, block id, blocks,. Owner '.' segment name "Name". From sys.dba extents. Select...
eminus-sleepus.blogspot.com
Eminus Sleepus Chamber: view open cursor usage in Oracle
http://eminus-sleepus.blogspot.com/2009/11/view-open-cursor-usage-in-oracle.html
Wednesday, November 04, 2009. View open cursor usage in Oracle. Count open cursors by username, SID. Comp sum of curs on report. Select user name, SID, count(*) cursors from V$OPEN CURSOR group by User Name, SID order by User Name, SID;. Displays information on cursor usage for the current session. Select * from V$SESSION CURSOR CACHE;. Displays information on cursor usage for the system. Select * from V$SYSTEM CURSOR CACHE;. Displays open cursors usage in details. Thanks for the syntax, it help me alot.
eminus-sleepus.blogspot.com
Eminus Sleepus Chamber: Oracle: how to get the DDL of a certain object
http://eminus-sleepus.blogspot.com/2010/03/oracle-how-to-get-ddl-of-certain-object.html
Tuesday, March 02, 2010. Oracle: how to get the DDL of a certain object. I have a sql script here to extract DDL of all the objects in a schema, the example below will extract the indexes of a certain schema. this is already formatted so you can spool it in a sql file and later run it. Set feedback off echo off. Set long 200000 pages 0 lines 400 trimspool on linesize 400. Column txt format a400 word wrapped. Variable ind owner varchar2(100);. Ind owner := &schema;. Spool $ORACLE SID-get idx ddl.sql.
eminus-sleepus.blogspot.com
Eminus Sleepus Chamber: Check Session Details in Oracle
http://eminus-sleepus.blogspot.com/2009/09/check-session-details-in-oracle.html
Sunday, September 20, 2009. Check Session Details in Oracle. Col PID for a7. Col SID for 9999999999. Col ser# for a7. Col box for a29. Col status for a9. Col username for a20. Col os user for a12. Col program for a25. Col LOGON TIME for a14. Col module for a14. Col machine for a10. Select b.status as status,sql hash value,prev hash value,module,machine,. To char(a.spid) as pid, to char(b.sid) as sid, to char(b.serial#) as "ser#", substr(b.machine,1,25) as box, b.username as username,. Bpaddr = a.addr.
eminus-sleepus.blogspot.com
Eminus Sleepus Chamber: Temporary tablespace usage in Oracle
http://eminus-sleepus.blogspot.com/2009/09/temporary-tablespace-usage-in-oracle.html
Sunday, September 20, 2009. Temporary tablespace usage in Oracle. Col sid serial for a15. Col osuser for a10. Col module for a20. Col program for a30. Col tablespace for a10. Listing of temp segments. SELECT A.tablespace name tablespace, D.mb total,. SUM (A.used blocks * D.block size) / 1024 / 1024 mb used,. Dmb total - SUM (A.used blocks * D.block size) / 1024 / 1024 mb free. FROM v$sort segment A,. SELECT B.name, C.block size, SUM (C.bytes) / 1024 / 1024 mb total. FROM v$tablespace B, v$tempfile C.
eminus-sleepus.blogspot.com
Eminus Sleepus Chamber: View RMAN jobs in Oracle
http://eminus-sleepus.blogspot.com/2009/09/view-rman-jobs-in-oracle.html
Sunday, September 20, 2009. View RMAN jobs in Oracle. Set lines 150 pages 30000. Col ins format a10. Col outs format a10. Col TIME TAKEN DISPLAY format a10. Select SESSION KEY,. COMPRESSION RATIO,input type,. INPUT BYTES PER SEC DISPLAY ins,. OUTPUT BYTES PER SEC DISPLAY outs,. TIME TAKEN DISPLAY,to char(START TIME,'mm/dd/yy hh24:mi') start time,. To char(END TIME,'mm/dd/yy hh24:mi') end time,elapsed seconds/3600 hrs. From V$RMAN BACKUP JOB DETAILS. Order by session key;. Where session key = &session key.