Find average Row Length and other table size calculation. metalink notes
Subject: Extent and Block Space Calculation and Usage in V7-V9 Database Doc ID :10640.1
How to choose Driver table in SQL statement
Driver table: Take the driver table which returns the less number of rows for predicate with literal valae. For example, Consider the statement below: SELECT SUM(BIP.VALUE) value FROM BSKT_ITEM_CALC_PRICING BIP,BSKT_PRICING_ELEMENT BP WHERE BIP.BASKET_ITEM_ID IN (:”SYS_B_0″) AND BIP.PRICING_ELEMENT_ID = BP.PRICING_ELEMENT_ID AND BP.PRICING_TYPE_CODE = :1; Here SQL> select BASKET_ITEM_ID, count(1) from BSKT_ITEM_CALC_PRICING group by BASKET_ITEM_ID; BASKET_ITEM_ID COUNT(1)…
SQL_PLAN.sql for checking real execution plan
set lines 132 set pages 1400 SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position) “Query Plan” FROM v$sql_plan START WITH id = 0 and sql_id=’4ftvbpzhwkfd8′ and child_number=0 CONNECT BY PRIOR id = parent_id and sql_id=’4ftvbpzhwkfd8′ and child_number=0;
Good Doc 28-JUN-2006
Good Doc About UNDO Management: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6894817116500 Cache Buffer Chain Latch: http://www.orafaq.com/maillist/oracle-l/2003/02/27/2671.raw
How to stop OCSSD Daemon
/etc/init.d/init.cssd stop to stop cssd process.
Follwoing korn shell is used to move files from source folder to destination folder and gzip it in destination folder. mv_iotto.ksh
#!/usr/bin/ksh a=`ps -ef|grep “mv_iotto.ksh”|grep -v grep|wc -l` if [ $a -gt 2 ] then echo “Exiting because of more than 1 Moving archive log script is running ” exit fi for fname in `ls -rlt /tmpdb2/oradata/UOCIOTTO_ARCH/*.arc | awk ‘{print $9}’ | sed ‘$d’ | sed ‘$d’` do print “”; print “File Name : $fname “;…
sess_server.sql
column osuser format a10 column username format a30 column machine format a30 column program format a50 set lines 132 set pages 500 select osuser, username, machine, program from v$session; select machine, username, count(1) from v$session group by machine, username;
My Minimum Tuning Programs
REM ***** gsp.sql ***** REM ***** This is used to get SPID from SID. col username format a30 col machine format a20 col program format a40 accept _sid prompt ‘Enter Oracle Session ID ->’ select a.sid, b.pid, b.spid, a.username,a.program,a.machine from v$session a,V$process b where a.paddr = b.addr and a.sid = &_sid / REM ***** gsq.sql…
How to find who is using which Rollback segment and how many rows or blocks in that rollback segments,
Using following query, we can estimate # of rows resides in each USN (Rollback Segment) and who fired that transactions. New modified GCU.sql on 16-JUN-2006 /* Get Curent USN */ column start_dt format a20 set lines 132 select /*+ ALL_ROWS */ a.usn, a1.name, a.extents, a.hwmsize, a.status usn_status, to_char(b.start_date, ‘DD-MON-RRRR:HH24:MI:SS’) start_dt, b.status tx_status, c.sid, c.sql_id, b.used_ublk,…
Generate SSH without password authentication.
*******************************************************************************************Step by Step Implementation of key based authentications from Source A to Destination B. ******************************************************************************************* (1) At source A (1-A) Generate a Key ssh-keygen -t dsa Sampe Output posdbprod21->CIF@(/export/home/oracle/paresh)ssh-keygen -t dsa Generating public/private dsa key pair. Enter file in which to save the key (/export/home/oracle/.ssh/id_dsa): Enter passphrase (empty for no passphrase): Enter same passphrase again:…