column osuser format a10 column username format a30 column machine format a30 column program format a40 column sid format 99999999 column serial# format 99999999 column TOT format 999999 heading “Total Sessions” break on report compute sum of TOT on report set lines 141 set pages 500 select machine, osuser, username, count(1) “TOT” from v$session group…
set lines 120 pages 200 column event format a30 column object_type format a14 column object_name format a30 column owner format a10 column sql_text format a120 accept event_name prompt ‘Enter fraction of event to trace : ‘ select distinct a.event, s.sql_id, s.sql_text, s.executions from v$session a, v$sqlarea s where s.sql_id = a.sql_id and lower(a.event) like lower(‘%&event_name%’)…
######### # _kks_use_mutex_pin # # Enables use of more efficient mutex mechanism for # implementing library cache pins. # ######### _kks_use_mutex_pin=TRUE Apparently, Oracle is migrating some of its serialization protection from latches to mutexes. For example, the structures previously protected by the Library Cache Pin latch are now protected by a mutex and evidenced by…
Problem: ——– I cannot create a db_link on my database that does not have the fuly qualified domain.name. eg. if I create a db_link called fred as follows: create database link fred connect to ITU604SP4B identified by using ‘sup9206_hippo’ a link called fred.intec.co.za is created, I just want fred. I have set global_names= FALSE db_domain=””…
column name format a10 column position format 99 column datatype_string format a20 column value_string format a50 set lines 120 pages 200 select sql_id, name, position, datatype_string, value_string from v$sql_bind_capture where sql_id = ‘&sql_id’ /
SQL> select distinct sid from v$mystat; SID ———- 1365
#!/bin/ksh -x # First, we must set the environment . . . . ORACLE_SID=WEBP18F export ORACLE_SID ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d’:’` export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH SERVER_NAME=`uname -a|awk ‘{print $2}’` typeset -u SERVER_NAME export SERVER_NAME # sample every five minutes (300 seconds) . . . . SAMPLE_TIME=30 while true do vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$…
Subject: Extent and Block Space Calculation and Usage in V7-V9 Database Doc ID :10640.1
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)…
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;
