sql plan hash value change

###################################
######coe_xfr_sql_profile.sql######
###################################

SPO coe_xfr_sql_profile.log;
SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SET SERVEROUT ON SIZE UNL;
REM
REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.2.3 2011/01/14 csierra $
REM
REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   coe_xfr_sql_profile.sql
REM
REM DESCRIPTION
REM   This script generates another that contains the commands to
REM   create a manual custom SQL Profile out of a known plan from
REM   memory or AWR. The manual custom profile can be implemented
REM   into the same SOURCE system where the plan was retrieved,
REM   or into another similar TARGET system that has same schema
REM   objects referenced by the SQL that generated the known plan.
REM
REM PRE-REQUISITES
REM   1. Oracle Tuning Pack license.
REM
REM PARAMETERS
REM   1. SQL_ID (required)
REM   2. Plan Hash Value for which a manual custom SQL Profile is
REM      needed (required). A list of known plans is presented.
REM
REM EXECUTION
REM   1. Connect into SQL*Plus as SYSDBA or user with access to
REM      data dictionary.
REM   2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and
REM      plan hash value (parameters can be passed inline or until
REM      requested).
REM
REM EXAMPLE
REM   # sqlplus system
REM   SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
REM   SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;
REM   SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;
REM   SQL> START coe_xfr_sql_profile.sql;
REM
REM NOTES
REM   1. For possible errors see coe_xfr_sql_profile.log
REM   2. If SQLT is installed in SOURCE, you can use instead:
REM      sqlt/utl/sqltprofile.sql
REM   3. Be aware that using DBMS_SQLTUNE requires a license for
REM      Oracle Tuning Pack.
REM
SET TERM ON ECHO OFF;
PRO
PRO Parameter 1:
PRO SQL_ID (required)
PRO
DEF sql_id = '&1';
PRO
WITH
p AS (
SELECT plan_hash_value
  FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
   AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
  FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
   AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
       SUM(elapsed_time)/SUM(executions) avg_et_secs
  FROM gv$sql
WHERE sql_id = TRIM('&&sql_id.')
   AND executions > 0
GROUP BY
       plan_hash_value ),
a AS (
SELECT plan_hash_value,
       SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
  FROM dba_hist_sqlstat
WHERE sql_id = TRIM('&&sql_id.')
   AND executions_total > 0
GROUP BY
       plan_hash_value )
SELECT p.plan_hash_value,
       ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
  FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
   AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
       avg_et_secs NULLS LAST;
PRO
PRO Parameter 2:
PRO PLAN_HASH_VALUE (required)
PRO
DEF plan_hash_value = '&2';
PRO
PRO Values passed:
PRO ~~~~~~~~~~~~~
PRO SQL_ID         : "&&sql_id."
PRO PLAN_HASH_VALUE: "&&plan_hash_value."
PRO
SET TERM OFF ECHO ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;

VAR sql_text CLOB;
VAR other_xml CLOB;
EXEC :sql_text := NULL;
EXEC :other_xml := NULL;

-- get sql_text from memory
DECLARE
  l_sql_text VARCHAR2(32767);
BEGIN -- 10g see bug 5017909
  FOR i IN (SELECT DISTINCT piece, sql_text
              FROM gv$sqltext_with_newlines
             WHERE sql_id = TRIM('&&sql_id.')
             ORDER BY 1, 2)
  LOOP
    IF :sql_text IS NULL THEN
      DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
      DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
    END IF;
    l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
    DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text), l_sql_text);
  END LOOP;
  IF :sql_text IS NOT NULL THEN
    DBMS_LOB.CLOSE(:sql_text);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);
    :sql_text := NULL;
END;
/

-- get sql_text from awr
BEGIN
  IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN
    SELECT REPLACE(sql_text, CHR(00), ' ')
      INTO :sql_text
      FROM dba_hist_sqltext
     WHERE sql_id = TRIM('&&sql_id.')
       AND sql_text IS NOT NULL
       AND ROWNUM = 1;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
   :sql_text := NULL;
END;
/

SELECT :sql_text FROM DUAL;

-- to avoid errors when sql_text lacks LFs and is more than 2000 bytes
BEGIN
  :sql_text := REPLACE(:sql_text, ')', ')'||CHR(10));
  :sql_text := REPLACE(:sql_text, ',', ','||CHR(10));
  -- remove consecutive LFs
  :sql_text := REPLACE(:sql_text, CHR(10)||CHR(10)||CHR(10)||CHR(10)||CHR(10), CHR(10));
  :sql_text := REPLACE(:sql_text, CHR(10)||CHR(10)||CHR(10), CHR(10));
  :sql_text := REPLACE(:sql_text, CHR(10)||CHR(10), CHR(10));
END;
/

SELECT :sql_text FROM DUAL;

-- get other_xml from memory
BEGIN
  FOR i IN (SELECT other_xml
              FROM gv$sql_plan
             WHERE sql_id = TRIM('&&sql_id.')
               AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
               AND other_xml IS NOT NULL
             ORDER BY
                   child_number, id)
  LOOP
    :other_xml := i.other_xml;
    EXIT; -- 1st
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
    :other_xml := NULL;
END;
/

-- get other_xml from awr
BEGIN
  IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
    FOR i IN (SELECT other_xml
                FROM dba_hist_sql_plan
               WHERE sql_id = TRIM('&&sql_id.')
                 AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
                 AND other_xml IS NOT NULL
               ORDER BY
                     id)
    LOOP
      :other_xml := i.other_xml;
      EXIT; -- 1st
    END LOOP;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
    :other_xml := NULL;
END;
/

SELECT :other_xml FROM DUAL;

-- generates script that creates sql profile in target system:
SET ECHO OFF;
PRO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql.
SET FEED OFF LIN 666 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 FOR WOR;
SET SERVEROUT ON SIZE UNL FOR WOR;
SPO OFF;
SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;
DECLARE
  l_pos NUMBER;
  l_hint VARCHAR2(32767);
BEGIN
  DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..log;');
  DBMS_OUTPUT.PUT_LINE('SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM $Header: 215187.1 coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql 11.4.2.3 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' csierra $');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM AUTHOR');
  DBMS_OUTPUT.PUT_LINE('REM   carlos.sierra@oracle.com');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM SCRIPT');
  DBMS_OUTPUT.PUT_LINE('REM   coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION');
  DBMS_OUTPUT.PUT_LINE('REM   This script is generated by coe_xfr_sql_profile.sql');
  DBMS_OUTPUT.PUT_LINE('REM   It contains the SQL*Plus commands to create a custom');
  DBMS_OUTPUT.PUT_LINE('REM   SQL Profile for SQL_ID &&sql_id. based on plan hash');
  DBMS_OUTPUT.PUT_LINE('REM   value &&plan_hash_value..');
  DBMS_OUTPUT.PUT_LINE('REM   The custom SQL Profile to be created by this script');
  DBMS_OUTPUT.PUT_LINE('REM   will affect plans for SQL commands with signature');
  DBMS_OUTPUT.PUT_LINE('REM   matching the one for SQL Text below.');
  DBMS_OUTPUT.PUT_LINE('REM   Review SQL Text and adjust accordingly.');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM PARAMETERS');
  DBMS_OUTPUT.PUT_LINE('REM   None.');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM EXAMPLE');
  DBMS_OUTPUT.PUT_LINE('REM   SQL> START coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM NOTES');
  DBMS_OUTPUT.PUT_LINE('REM   1. Should be run as SYSTEM or SYSDBA.');
  DBMS_OUTPUT.PUT_LINE('REM   2. User must have CREATE ANY SQL PROFILE privilege.');
  DBMS_OUTPUT.PUT_LINE('REM   3. SOURCE and TARGET systems can be the same or similar.');
  DBMS_OUTPUT.PUT_LINE('REM   4. To drop this custom SQL Profile after it has been created:');
  DBMS_OUTPUT.PUT_LINE('REM      EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''coe_&&sql_id._&&plan_hash_value.'');');
  DBMS_OUTPUT.PUT_LINE('REM   5. Be aware that using DBMS_SQLTUNE requires a license');
  DBMS_OUTPUT.PUT_LINE('REM      for the Oracle Tuning Pack.');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE;');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('VAR signature NUMBER;');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('DECLARE');
  DBMS_OUTPUT.PUT_LINE('sql_txt CLOB;');
  DBMS_OUTPUT.PUT_LINE('h       SYS.SQLPROF_ATTR;');
  DBMS_OUTPUT.PUT_LINE('BEGIN');
  DBMS_OUTPUT.PUT_LINE('sql_txt := q''[');
  WHILE NVL(LENGTH(:sql_text), 0) > 0
  LOOP
    l_pos := INSTR(:sql_text, CHR(10));
    IF l_pos > 0 THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR(:sql_text, 1, l_pos - 1));
      :sql_text := SUBSTR(:sql_text, l_pos + 1);
    ELSE
      DBMS_OUTPUT.PUT_LINE(:sql_text);
      :sql_text := NULL;
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(']'';');
  DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR(');
  DBMS_OUTPUT.PUT_LINE('q''[BEGIN_OUTLINE_DATA]'',');
  FOR i IN (SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
                   SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'), 1, 4000) hint
              FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), '/*/outline_data/hint'))) d)
  LOOP
    l_hint := i.hint;
    WHILE NVL(LENGTH(l_hint), 0) > 0
    LOOP
      IF LENGTH(l_hint) <= 500 THEN
        DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'',');
        l_hint := NULL;
      ELSE
        l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
        DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'',');
        l_hint := '   '||SUBSTR(l_hint, l_pos);
      END IF;
    END LOOP;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('q''[END_OUTLINE_DATA]'');');
  DBMS_OUTPUT.PUT_LINE(':signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);');
  DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (');
  DBMS_OUTPUT.PUT_LINE('sql_text    => sql_txt,');
  DBMS_OUTPUT.PUT_LINE('profile     => h,');
  DBMS_OUTPUT.PUT_LINE('name        => ''coe_&&sql_id._&&plan_hash_value.'',');
  DBMS_OUTPUT.PUT_LINE('description => ''coe &&sql_id. &&plan_hash_value. ''||:signature||'''',');
  DBMS_OUTPUT.PUT_LINE('category    => ''DEFAULT'',');
  DBMS_OUTPUT.PUT_LINE('validate    => TRUE,');
  DBMS_OUTPUT.PUT_LINE('replace     => TRUE,');
  DBMS_OUTPUT.PUT_LINE('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );');
  DBMS_OUTPUT.PUT_LINE('END;');
  DBMS_OUTPUT.PUT_LINE('/');
  DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR CONTINUE');
  DBMS_OUTPUT.PUT_LINE('SET ECHO OFF;');
  DBMS_OUTPUT.PUT_LINE('PRINT signature');
  DBMS_OUTPUT.PUT_LINE('PRO');
  DBMS_OUTPUT.PUT_LINE('PRO ... manual custom SQL Profile has been created');
  DBMS_OUTPUT.PUT_LINE('PRO');
  DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";');
  DBMS_OUTPUT.PUT_LINE('SPO OFF;');
  DBMS_OUTPUT.PUT_LINE('PRO');
  DBMS_OUTPUT.PUT_LINE('PRO COE_XFR_SQL_PROFILE_&&sql_id._&&plan_hash_value. completed');
END;
/
SPO OFF;
SET DEF ON TERM ON ECHO OFF FEED 6 VER ON HEA ON LIN 80 PAGES 14 LONG 80 LONGC 80 TRIMS OFF TI OFF TIMI OFF SERVEROUT OFF NUMF "" SQLP SQL>;
PRO
PRO Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
PRO on TARGET system in order to create a custom SQL Profile
PRO with plan &&plan_hash_value linked to adjusted sql_text.
PRO
UNDEFINE 1 2 sql_id plan_hash_value
PRO
PRO COE_XFR_SQL_PROFILE completed.






Check Table Statistics History:
==============================

select OWNER,TABLE_NAME,STATS_UPDATE_TIME from dba_tab_stats_history where TABLE_NAME='BOB_INWARD';



20-AUG-14 05.32.29.766190 AM -06:00


09-OCT-14 01.34.40.455274 AM +05:30


09-OCT-14 03.11.48.644821 AM +05:30

PROCEDURE RESTORE_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 AS_OF_TIMESTAMP                TIMESTAMP WITH TIME ZONE IN
 RESTORE_CLUSTER_INDEX          BOOLEAN                 IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT



Restore Table Statistics:
=========================

EXEC dbms_stats.RESTORE_TABLE_STATS(OWNNAME=>'CTSMUMBAI',TABNAME=>'WORKLISTTABLE',AS_OF_TIMESTAMP=>'09-OCT-14 01.34.40.455274 AM +05:30',NO_INVALIDATE=>TRUE);

EXEC dbms_stats.RESTORE_TABLE_STATS(OWNNAME=>'CTSMUMBAI',TABNAME=>'BOB_INWARD',AS_OF_TIMESTAMP=>'09-OCT-14 05.07.37.315034 AM +05:30',NO_INVALIDATE=>TRUE);


SELECT COLUMN_NAME, NUM_NULLS,NUM_DISTINCT,HISTOGRAM,LAST_ANALYZED FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'WORKLISTTABLE'  AND OWNER='CTSDELHI' ORDER BY 2,3;


Gather Statistics with histogram:
=================================


exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'CTSDELHI', TABNAME => 'WORKLISTTABLE',degree=>50 ,cascade=>true,estimate_percent =>100,method_opt=>'FOR COLUMN Q_QUEUEID SIZE AUTO');


How to check histogram:
======================
SELECT COLUMN_NAME, NUM_NULLS,NUM_DISTINCT,HISTOGRAM,LAST_ANALYZED FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'WORKLISTTABLE'  AND OWNER='CTSCHENNAI' oRDER BY 2,3;






select count(1) from ctsmumbai.workdonetable;

DB scripts

******************************
/home/satvvikv/act_wait_id.sql
******************************
col SCHEMANAME for a15
select gvs.INST_ID, gvs.SCHEMANAME, gvs.SID, gvp.SPID, gvs.PROCESS, gvs.SQL_ID, substr(gvs.EVENT,1,25) EVENT, substr(gvs.SECONDS_IN_WAIT/60/60,1,3) HRS_WAIT, gvs.MODULE from gv$session gvs, gv$process gvp where gvs.PADDR=gvp.ADDR and gvs.TYPE != 'BACKGROUND' and gvs.USERNAME != 'SYS' and gvs.STATUS = 'ACTIVE' and gvs.SECONDS_IN_WAIT/60/60 > 0.5 order by gvs.INST_ID, gvs.SECONDS_IN_WAIT
/
******************************
/home/satvvikv/act_wait_usr.sql
******************************
select OSUSER, USERNAME, SID, PROCESS, SQL_ID, substr(EVENT,1,25) EVENT, SECONDS_IN_WAIT, MODULE from v$session where TYPE != 'BACKGROUND' and USERNAME not in('SYS','SYSTEM') and STATUS = 'ACTIVE' and SECONDS_IN_WAIT > &WAIT_TIME_IN_SECONDS order by SECONDS_IN_WAIT
/******************************
/home/satvvikv/advisor.sql
******************************
select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message
from dba_advisor_findings af, dba_advisor_objects ao
where ao.task_id = af.task_id
and ao.object_id = af.object_id
and ao.OWNER = 'SYS';
******************************
/home/satvvikv/arch_hour.sql
******************************
select
to_char(a.first_time,'YYYY-MM-DD') "Date ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'00',(BLOCKS*BLOCK_SIZE/1024/1024/1024),0)),'9999') "00 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'01',(BLOCKS*BLOCK_SIZE/1024/1024/1024),0)),'9999') "01 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'02',(BLOCKS*BLOCK_SIZE/1024/1024/1024),0)),'9999') "02 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'03',(BLOCKS*BLOCK_SIZE/1024/1024/1024),0)),'9999') "03 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'04',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "04 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'05',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "05 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'06',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "06 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'07',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "07 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'08',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "08 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'09',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "09 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'10',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "10 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'11',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "11 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'12',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "12 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'13',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "13 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'14',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "14 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'15',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "15 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'16',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "16 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'17',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "17 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'18',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "18 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'19',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "19 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'20',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "20 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'21',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "21 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'22',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "22 ",
to_char(sum(decode(substr(to_char(a.first_time,'HH24'),1,3),'23',BLOCKS*BLOCK_SIZE/1024/1024/1024,0)),'9999') "23 ",
to_char(sum(BLOCKS*BLOCK_SIZE/1024/1024/1024),'9999999.99') "TOTAL"
from v$archived_log a
where dest_id=1 and
trunc(a.first_time) >= trunc(sysdate-19)
group by to_char(a.first_time,'YYYY-MM-DD')
order by to_char(a.first_time,'YYYY-MM-DD');
******************************
/home/satvvikv/asw.sql
******************************
SET LINES 188 PAGES 1000
COL EVENT FOR A33
COL P2TEXT FOR A14
COL P1TEXT FOR A25
COL P3TEXT FOR a25
COL SID FOR 999999
COL STATE FOR A10 TRUNC
COL USERNAME FOR A15
SELECT A.USERNAME, A.SQL_ID, EVENT, SECONDS_IN_WAIT SECS_WAIT, STATE, WAIT_TIME, P1, P2, P1TEXT, P2TEXT
FROM V$SESSION A, V$PROCESS B WHERE A.PADDR = B.ADDR
AND A.STATUS = 'ACTIVE' AND A.USERNAME IS NOT NULL
AND A.TYPE != 'BACKGROUND'
ORDER BY 3;
******************************
/home/satvvikv/asw_history.sql
******************************
COL TOTAL_WAIT FOR 999999999999999;
SELECT EN.NAME WAIT_EVENT, SUM(ASH.WAIT_TIME + ASH.TIME_WAITED) TOTAL_WAIT FROM V$ACTIVE_SESSION_HISTORY ASH, V$EVENT_NAME EN WHERE ASH.EVENT_ID = EN.EVENT_ID AND EN.WAIT_CLASS <> 'IDLE' GROUP BY EN.NAME ORDER BY 2 DESC;
******************************
/home/satvvikv/bind_cap.sql
******************************
select  name||'|'|| value_string
from v$sql_bind_capture
where sql_id='&sqlid'
/
******************************
/home/satvvikv/check_stale_stats.sql
******************************
-- - - - - - - - - - - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - - - - - - - - - - - -
--  NAME:  CHECK_STALE_STATS.SQL
--   Execute as SYS as sysdba
-- ----------------------------------------------------------------------------------------------------------------
-- AUTHOR: 
--    Raja Ganesh - Oracle Support Services - DataServer Group
--    Copyright 2008, Oracle Corporation     
-- -----------------------------------------------------------------------------------------------------------------
-- PURPOSE:
-- This script is an automated way to deal with stale statistics
-- operations that are required to be done as part of manual
-- upgrade OR when reported by DBUA.
--
-- This script will work in both Windows and Unix platforms from database
-- version 9.2 or higher.
-- ------------------------------------------------------------------------------------------------------------------
-- DISCLAIMER:
--    This script is provided for educational purposes only. It is NOT 
--    supported by Oracle World Wide Technical Support.
--    The script has been tested and appears to work as intended.
--    You should always run new scripts on a test instance initially.
-- -------------------------------------------------------------------------------------------------------------------
SET FEEDBACK OFF
SET LINESIZE 250
SET SERVEROUTPUT ON
DECLARE
-- Variables declared
P_OTAB DBMS_STATS.OBJECTTAB;
MCOUNT NUMBER := 0;
P_VERSION VARCHAR2(10);
-- Cursor defined
CURSOR c1
IS
SELECT distinct schema
FROM dba_registry
ORDER by 1;
-- Beginning of the anonymous block
BEGIN
-- Verifying version from v$instance
SELECT version INTO p_version FROM v$instance;
DBMS_OUTPUT.PUT_LINE(chr(13));
-- Defining Loop 1 for listing schema which have stale stats
FOR x in c1
  LOOP
 DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>x.schema,OPTIONS=>'LIST STALE',OBJLIST=>p_otab);
-- Defining Loop 2 to find number of objects containing stale stats
 FOR i in 1 .. p_otab.count
   LOOP
  IF p_otab(i).objname NOT LIKE 'SYS_%'
   AND p_otab(i).objname NOT IN ('CLU$','COL_USAGE$','FET$','INDPART$',
            'MON_MODS$','TABPART$','HISTGRM$',
            'MON_MODS_ALL$',
            'HIST_HEAD$','IN $','TAB$',
            'WRI$_OPTSTAT_OPR','PUIU$DATA',
            'XDB$NLOCKS_CHILD_NAME_IDX',
            'XDB$NLOCKS_PARENT_OID_IDX',
            'XDB$NLOCKS_RAWTOKEN_IDX', 'XDB$SCHEMA_URL',
            'XDBHI_IDX', 'XDB_PK_H_LINK')
  THEN
-- Incrementing count for  each object found with statle stats
   mcount := mcount + 1;
  END IF;
-- End of Loop 2
   END LOOP;
-- Displays no stale statistics, if coun  is 0
  IF mcount!=0
   THEN
-- Displays Schema with stale stats if count is greater than 0
    DBMS_OUTPUT.PUT_LINE(chr(13));
    DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('-- '|| x.schema || ' schema contains stale statistics use the following to gather the statistics '||'--');
    DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
   
-- Displays Command to be executed if schema with stale statistics is found depending on the version.
    IF SUBSTR(p_version,1,5) in ('8.1.7','9.0.1','9.2.0')
   THEN
    DBMS_OUTPUT.PUT_LINE(chr(13));
    DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_SCHEMA_STATS('''||x.schema||''',OPTIONS=>'''||'GATHER STALE'||''', ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => '''||'FOR ALL COLUMNS SIZE AUTO'||''', CASCADE => TRUE);');
    ELSIF SUBSTR(p_version,1,6) in ('10.1.0','10.2.0','11.1.0','11.2.0')
   THEN
    DBMS_OUTPUT.PUT_LINE(chr(13));
    DBMS_OUTPUT.PUT_LINE('EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('''||x.schema||''',OPTIONS=>'''||'GATHER STALE'||''', ESTIMATE_PERCENT  => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => '''||'FOR ALL COLUMNS SIZE AUTO'||''', CASCADE => TRUE);');
    ELSE
    DBMS_OUTPUT.PUT_LINE(chr(13));
    DBMS_OUTPUT.PUT_LINE('Version is '||p_version);
    END IF;
  ELSE
    DBMS_OUTPUT.PUT_LINE('-- There are no stale statistics in '|| x.schema || ' schema.');
    DBMS_OUTPUT.PUT_LINE(chr(13));
  END IF;
-- Reset count to 0.
   mcount := 0;
-- End of Loop 1
  END LOOP;
END;
/
SET FEEDBACK ON
-- - - - - - - - - - - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - - - - - - - - - - - - - -
******************************
/home/satvvikv/ctsche.sql
******************************
 select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where owner='CTSCHENNAI' and TABLE_NAME in
('BOB_INWARD','BOB_INWARD_UPDATESTATUS','BOB_OUTWARD','BOB_INWARD_HISTORY','BOB_OUTWARD_HISTORY','HISTORYROUTELOGTABLE',
'PDBFOLDER','WORKDONETABLE','WORKLISTTABLE','WFMESSAGEINPROCESSTABLE','CURRENTROUTELOGTABLE','PDBFOLDER','ISDOC','ISVOLBLOCK',
'BOB_OUTWARD','PDBDOCUMENT','PENDINGWORKLISTTABLE','PROCESSINSTANCETABLE','BOB_COREDATAMATCHING','WORKWITHPSTABLE','WORKINPROCESSTABLE','USRCHIBANK');******************************
/home/satvvikv/ctsmum.sql
******************************
select owner,table_name,last_analyzed, decode(substr(last_analyzed,1,10),substr(sysdate,1,10),(SELECT 'COMPLETE' from dual),(SELECT 'INCOMPLETE' from dual)) "ANALYZE STATUS" from dba_tables where table_name in
('BOB_INWARD','BOB_INWARD_UPDATESTATUS','BOB_OUTWARD','BOB_INWARD_HISTORY',
'BOB_OUTWARD_HISTORY','HISTORYROUTELOGTABLE',',PDBFOLDER','WORKDONETABLE',
'WORKLISTTABLE','WFMESSAGEINPROCESSTABLE','PDBFOLDER','PDBFOLDER','ISDOC',
'ISVOLBLOCK','BOB_OUTWARD','PDBDOCUMENT','PENDINGWORKLISTTABLE',
'PROCESSINSTANCETABLE','BOB_COREDATAMATCHING','WORKWITHPSTABLE',
'WORKINPROCESSTABLE','USRCHIBANK','CURRENTROUTELOGTABLE') order by 3;
******************************
/home/satvvikv/da.sql
******************************
select * from table ( dbms_xplan.display_awr('&sql_id'))
/
******************************
/home/satvvikv/datafiles.sql
******************************
COL FILE_NAME FOR A90
SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 "SIZE_IN_MB", STATUS, AUTOEXTENSIBLE FROM DBA_DATA_FILES ORDER BY 1;
******************************
/home/satvvikv/dc.sql
******************************
select * from table ( dbms_xplan.display_cursor('&sql_id'))
/
******************************
/home/satvvikv/dg.sql
******************************
select GROUP_NUMBER,NAME,STATE,TOTAL_MB/1024 TOTAL_GB,FREE_MB/1024 FREE_GB,HOT_USED_MB/1024 HOT_USED_GB, COLD_USED_MB/1024 COLD_USED_GB,REQUIRED_MIRROR_FREE_MB/1024 REQUIRED_MIRROR_FREE_GB,USABLE_FILE_MB/1024  USABLE_FILE_GB from v$asm_diskgroup
/
******************************
/home/satvvikv/dict.sql
******************************
col comments for a70
select * from dict
 where table_name like upper('%&ENTER%');
******************************
/home/satvvikv/dict_stats.sql
******************************
exec dbms_stats.gather_dictionary_stats;
******************************
/home/satvvikv/dips.sql
******************************
select a.table_name,b.INDEX_NAME,b.INDEX_OWNER,b.PARTITION_NAME,b.LAST_ANALYZED from dba_ind_partitions b,dba_indexes a where b.INDEX_NAME
in (select INDEX_NAME from dba_part_indexes where TABLE_NAME in (select table_name from dba_synonyms
where synonym_name in ('DTD','DTH','TDTD','TDT','LGI','LGT','ATA','ADT','GUP','SOD','ETH','ETD','OTT','OCS','OCP','OCI','ICP'))) and b.index_name=a.index_name order by 5 desc
/
******************************
/home/satvvikv/dis.sql
******************************
select index_name,table_name,last_analyzed from dba_indexes where owner='TBAADM' and table_name in ('ACCT_TURN_OVER_TABLE','SQLDBA_AUDIT_TABLE','PRINT_QUEUE_TABLE','ORG_TRAN_TABLE','LOGIN_VAL_ITEMS_TABLE','BUSINESS_TRAN_TABLE')
/
******************************
/home/satvvikv/dtps.sql
******************************
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED,GLOBAL_STATS from dba_tab_partitions where TABLE_NAME in (select table_name from dba_synonyms where synonym_name in ('DTD','DTH','TDTD','TDT','LGI','LGT','ATA','ADT','GUP','SOD','ETH','ETD','OTT','OCS','OCP','OCI','ICP')) order by 4 desc;
******************************
/home/satvvikv/dts.sql
******************************
select table_name,last_analyzed from dba_tables where owner='TBAADM' and table_name in ('ACCT_TURN_OVER_TABLE','SQLDBA_AUDIT_TABLE','PRINT_QUEUE_
TABLE','ORG_TRAN_TABLE','LOGIN_VAL_ITEMS_TABLE','BUSINESS_TRAN_TABLE')
/
******************************
/home/satvvikv/eglc.sql
******************************
COL MODULE FOR A20 TRUNC
COL INST_ID FOR 9999999
COL USERNAME FOR A20 TRUNC
COL SID FOR 99999999
COL MACHINE FOR A30 TRUNC
COL LAST_CALL_ET FOR 99999999
COL OPTIMIZER_COST FOR 999999999999999999999
COL OSUSER FOR A15
SELECT A.INST_ID,A.SID,A.SQL_ID,B.OPTIMIZER_COST,(SELECT COUNT(1) FROM GV$SESSION WHERE SQL_ID=A.SQL_ID AND INST_ID=A.INST_ID) SQL_COUNT, A.USERNAME,A.MACHINE,A.OSUSER,A.MODULE,A.LAST_CALL_ET/60 MINS FROM GV$SESSION A , GV$SQLAREA B WHERE A.SQL_ID=B.SQL_ID AND A.INST_ID=B.INST_ID AND A.STATUS = 'ACTIVE' AND A.USERNAME IS NOT NULL AND A.TYPE != 'BACKGROUND' ORDER BY 4,LAST_CALL_ET;
******************************
/home/satvvikv/eglc_d.sql
******************************
COL MODULE FOR A20 TRUNC;
COL INST_ID FOR 9999999;
COL USERNAME FOR A10;
COL OSUSER FOR A12;
COL MACHINE FOR A15 TRUNC;
COL LAST_CALL_ET FOR 99999999;
COL EVENT FOR A35 TRUNC;
SELECT A.INST_ID, A.SQL_ID, (SELECT COUNT(1) FROM GV$SESSION WHERE SQL_ID=A.SQL_ID) SQL_COUNT, A.OSUSER, A.USERNAME, A.MACHINE, A.MODULE, A.EVENT, A.LAST_CALL_ET/60 mINS, A.SECONDS_IN_WAIT/60 WAIT_MINS FROM GV$SESSION A , GV$SQLAREA B WHERE A.SQL_ID=B.SQL_ID AND A.STATUS = 'ACTIVE' AND A.USERNAME IS NOT NULL AND A.TYPE != 'BACKGROUND' AND A.SQL_ID = '&SQL_ID' ORDER BY 3,LAST_CALL_ET;

******************************
/home/satvvikv/elc.sql
******************************
COL MODULE FOR A20 TRUNC
COL USERNAME FOR A20 TRUNC
COL SID FOR 99999999
COL MACHINE FOR A30 TRUNC
COL LAST_CALL_ET FOR 99999999
COL OPTIMIZER_COST FOR 999999999999999999999
COL OSUSER FOR A15
SELECT A.SID,A.SQL_ID,B.OPTIMIZER_COST,(SELECT COUNT(1) FROM V$SESSION WHERE SQL_ID=A.SQL_ID) SQL_COUNT, A.OSUSER, A.USERNAME,A.MACHINE,A.MODULE,A.LAST_CALL_ET/60 MINs FROM V$SESSION A , V$SQLAREA B WHERE A.SQL_ID=B.SQL_ID AND A.STATUS = 'ACTIVE' AND A.USERNAME IS NOT NULL AND A.TYPE != 'BACKGROUND' ORDER BY 3,LAST_CALL_ET;
******************************
/home/satvvikv/elc_d.sql
******************************
col module for a20 trunc
col username for a20 trunc
col schemaname for a20 trunc
col sid for 99999999
col last_call_et for 99999999
col osuser for a15
select a.sid,a.sql_id,b.optimizer_cost,(select count(1) from v$session where sql_id=a.sql_id) sql_count, a.osuser, a.username,a.machine,a.module,a.last_call_et/60/60 Hours from v$session a , v$sqlarea b where a.sql_id=b.sql_id and a.status = 'ACTIVE' and a.username is not null and a.type != 'BACKGROUND' and a.sql_id = '&SQL_ID' order by 3,last_call_et;
******************************
/home/satvvikv/event.sql
******************************
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID  FOR A6
COL APP_ID FOR A6
BREAK ON SQL_ID dup skip page
SELECT /*SATVVIK*/ VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM V$SESSION VS, V$PROCESS VP WHERE VS.PADDR = VP.ADDR AND  VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.EVENT='&EVENT' ORDER BY 3,8;
******************************
/home/satvvikv/finalhw.sql
******************************
select final_blocking_Session,final_blocking_instance ,count(1) from gv$session group by final_blocking_Session,final_blocking_instance;
******************************
/home/satvvikv/ftext.sql
******************************
set head off;
set long 99999999
select SQL_FULLTEXT from v$sqlarea where SQL_ID = '&SQL_ID';
set head on;

******************************
/home/satvvikv/gact_wait_usr.sql
******************************
select gvs.INST_ID, gvs.OSUSER, gvs.USERNAME, gvs.SID, gvs.PROCESS, gvs.SQL_ID, substr(gvs.EVENT,1,25) EVENT, gvs.SECONDS_IN_WAIT, gvs.MODULE from gv$session gvs where gvs.TYPE != 'BACKGROUND' and gvs.USERNAME not in ('SYS','SYSTEM') and gvs.STATUS = 'ACTIVE' and gvs.SECONDS_IN_WAIT > &WAIT_TIME_IN_SECONDS order by gvs.INST_ID, gvs.SECONDS_IN_WAIT
/
******************************
/home/satvvikv/gasw.sql
******************************
SET LINES 188 PAGES 1000
COL EVENT FOR A33
COL P2TEXT FOR A14
COL P1TEXT FOR A25
COL P3TEXT FOR A25
COL SID FOR 999999
COL STATE FOR A10 TRUNC
COL USERNAME FOR A15
SELECT A.INST_ID, A.USERNAME, A.SQL_ID, EVENT, SECONDS_IN_WAIT SECS_WAIT, STATE, WAIT_TIME, P1, P2, P1TEXT, P2TEXT
FROM GV$SESSION A, GV$PROCESS B WHERE A.PADDR = B.ADDR
AND A.INST_ID=B.INST_ID
AND A.STATUS = 'ACTIVE' AND A.USERNAME IS NOT NULL
AND A.TYPE != 'BACKGROUND'
ORDER BY 3;
******************************
/home/satvvikv/gasw_history.sql
******************************
col TOTAL_WAIT for 999999999999999;
select en.name WAIT_EVENT, sum(ash.wait_time + ash.time_waited) TOTAL_WAIT from gv$active_session_history ash, gv$event_name en where ash.event_id = en.event_id and en.wait_class <> 'Idle' group by en.name order by 2 desc
/
******************************
/home/satvvikv/gather_index_stats.sql
******************************
select 'exec dbms_stats.gather_index_stats('''||OWNER||''','''||INDEX_NAME||''', granularity => ''AUTO'', DEGREE => 32);' from dba_indexes  where owner = '&OWNER' and index_name = '&INDEX_NAME';
******************************
/home/satvvikv/gather_table_stats.sql
******************************
select 'exec dbms_stats.gather_table_stats('''||OWNER||''','''||TABLE_NAME||''',granularity => ''AUTO'',cascade => TRUE, DEGREE => 32);' from dba_tables  where owner = '&OWNER' and table_name = '&TABLE_NAME';
******************************
/home/satvvikv/gevent.sql
******************************
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID  FOR A6
COL APP_ID FOR A6
BREAK ON SQL_ID dup skip page
SELECT /*SATVVIK*/ VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM GV$SESSION VS, GV$PROCESS VP WHERE VS.PADDR = VP.ADDR AND  VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.EVENT='&EVENT' ORDER BY 3,8;
******************************
/home/satvvikv/gftext.sql
******************************
set head off;
set long 99999999
select SQL_FULLTEXT from gv$sqlarea where SQL_ID = '&SQL_ID';
set head on;

******************************
/home/satvvikv/ghw.sql
******************************
col HOLDER_SQL_PREVSQL_PROCESS for a40
col MODULE for a30
col OSUSER for a10
col USERNAME for a10
select inst_id,sid WAITER_SID, blocking_session HOLDER_SID ,
(select sql_id||'|'||prev_sql_id ||'|'||process from gv$session where sid=sess.blocking_session and rownum <=1) holder_sql_prevsql_process ,
sql_id WAITER_sqlid,seconds_in_wait secw, username,module,osuser,last_call_et/60 wait_mins
from gv$session sess
WHERE  blocking_session IS NOT NULL
order by 2;
******************************
/home/satvvikv/gkill_id.sql
******************************
SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||INST_ID||''' IMMEDIATE;' FROM GV$SESSION  WHERE SQL_ID='&SQL_ID';
******************************
/home/satvvikv/glc.sql
******************************
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID  FOR A6
COL APP_ID FOR A6
SELECT /*SATVVIK*/ VS.INST_ID, VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM GV$SESSION VS, GV$PROCESS VP WHERE VS.PADDR = VP.ADDR AND  VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.SQL_ID IS NOT NULL ORDER BY 4,3,11;
******************************
/home/satvvikv/glc_d.sql
******************************
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID  FOR A6
COL APP_ID FOR A6
SELECT /*SATVVIK*/ VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM GV$SESSION VS, GV$PROCESS VP WHERE VS.PADDR = VP.ADDR AND  VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.SQL_ID = '&SQL_ID' ORDER BY 10,3,11;
******************************
/home/satvvikv/glc_mach.sql
******************************
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID  FOR A6
COL APP_ID FOR A6
BREAK ON MACHINE dup skip page
SELECT /*SATVVIK*/ VS.INST_ID, VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM GV$SESSION VS, GV$PROCESS VP WHERE VS.PADDR = VP.ADDR AND  VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.SQL_ID IS NOT NULL ORDER BY 10,3,11;
******************************
/home/satvvikv/gsql.sql
******************************
select distinct gv.sql_id from gv$session gv, gv$sql gs where gv.sql_id=gs.sql_id and gs.sql_text like '%&SAMPLE_SQL_TEXT%' and gv.status= 'ACTIVE' and gv.username <> 'SYS'
/
******************************
/home/satvvikv/gv_lock.sql
******************************
alter session set "_hash_join_enabled"=TRUE;
select object_id, object_name from dba_objects where object_id in (select object_id from gv$locked_object);
******************************
/home/satvvikv/high_cpu.sql
******************************
-- sessions with highest CPU consumption
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;
******************************
/home/satvvikv/hotblock.sql
******************************
COL OBJECT_NAME FOR A60;
SELECT INST_ID, COUNT(*), SQL_ID, NVL(O.OBJECT_NAME, ASH.CURRENT_OBJ#) OBJECT_NAME, SUBSTR(O.OBJECT_TYPE, 0,10) OBJECT_TYPE, CURRENT_FILE# FILE_NUMBER, CURRENT_BLOCK# BLOCK_NUMBER FROM GV$ACTIVE_SESSION_HISTORY ASH, DBA_OBJECTS O WHERE EVENT LIKE '%&EVENT%' AND O.OBJECT_ID (+) = ASH.CURRENT_OBJ# GROUP BY INST_ID, SQL_ID, CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#, O.OBJECT_NAME, O.OBJECT_TYPE ORDER BY 1,2,4;
******************************
/home/satvvikv/hw.sql
******************************
col HOLDER_SQL_PREVSQL_PROCESS for a40
col MODULE for a30
col OSUSER for a10
col USERNAME for a10
select sid WAITER_SID, blocking_session HOLDER_SID,
(select sql_id||'|'||prev_sql_id ||'|'||process from v$session where sid=sess.blocking_session and rownum <=1) holder_sql_prevsql_process ,
sql_id WAITER_sqlid,seconds_in_wait secw, username,module,osuser,last_call_et/60 wait_mins
from v$session sess
WHERE  blocking_session IS NOT NULL
order by 9;
******************************
/home/satvvikv/indinfo.sql
******************************
COL OWNER FOR A20;
COL INDEX_NAME FOR A30;
COL INDEX_TYPE FOR A10;
COL TABLESPACE_NAME FOR A25;
COL DEGREE FOR A6
COL NUM_ROWS FOR 9999999999999;
SELECT OWNER, INDEX_NAME, INDEX_TYPE, PARTITIONED, COMPRESSION, TABLESPACE_NAME, NUM_ROWS, LAST_ANALYZED, DEGREE, STATUS FROM DBA_INDEXES WHERE TABLE_NAME = UPPER('&TABLE_NAME');
******************************
/home/satvvikv/kill_id.sql
******************************
SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''' IMMEDIATE;' FROM GV$SESSION  WHERE SQL_ID='&SQL_ID';
******************************
/home/satvvikv/killi_bobind.sql
******************************
PROMPT DAILY_TRAN_HEADER_TABLE
PROMPT DAILY_TRAN_DETAIL_TABLE
PROMPT GENERAL_ACCT_MAST_TABLE
SELECT 'ALTER SYSTEM KILL SESSION ' ||''''||SID||','||' '||SERIAL#||''''||' '||'IMMEDIATE'||';' AS SESSION_KILL, LOGON_TIME, LAST_CALL_ET, SQL_ID, PREV_SQL_ID FROM V$SESSION WHERE SID IN (SELECT SESSION_ID  FROM  GV$LOCKED_OBJECT WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='&OBJECT_NAME')) AND STATUS ='INACTIVE' AND LOGON_TIME < (SYSDATE - &TIME_IN_HRS/24) ORDER BY LOGON_TIME;
******************************
/home/satvvikv/killi_lock.sql
******************************
SELECT 'ALTER SYSTEM KILL SESSION ' ||''''||SID||','||' '||SERIAL#||''''||' '||'IMMEDIATE'||';' AS SESSION_KILL, LOGON_TIME, LAST_CALL_ET, SQL_ID, PREV_SQL_ID FROM V$SESSION WHERE SID IN (SELECT SESSION_ID  FROM  GV$LOCKED_OBJECT WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='&OBJECT_NAME')) AND STATUS ='INACTIVE' AND LOGON_TIME < (SYSDATE - &TIME_IN_HRS/24) ORDER BY LOGON_TIME;
******************************
/home/satvvikv/lc.sql
******************************
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID  FOR A6
COL APP_ID FOR A6
BREAK ON SQL_ID dup skip page
SELECT /*SATVVIK*/ VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM V$SESSION VS, V$PROCESS VP WHERE VS.PADDR = VP.ADDR AND  VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.SQL_ID IS NOT NULL ORDER BY 3,2,11;
******************************
/home/satvvikv/lc_d.sql
******************************
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID  FOR A6
COL APP_ID FOR A6
SELECT /*SATVVIK*/ VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM V$SESSION VS, V$PROCESS VP WHERE VS.PADDR = VP.ADDR AND  VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.SQL_ID = '&SQL_ID' ORDER BY 10,3,11;
******************************
/home/satvvikv/lc_mach.sql
******************************
COL MODULE FOR A40
COL OSUSER FOR A10
COL USERNAME FOR A8
SET PAGES 10000
COL SID FOR 99999999
COL SQL_CNT FOR 9999999
COL MACHINE FOR A10
COL LAST_CALL_ET FOR 999999
COL SPID  FOR A6
COL APP_ID FOR A6
BREAK ON MACHINE dup skip page
SELECT /*SATVVIK*/ VP.SPID, VS.SQL_ID, (SELECT OPTIMIZER_COST FROM V$SQLAREA WHERE SQL_ID=VS.SQL_ID) SQL_COST, (SELECT COUNT(1) FROM V$SESSION WHERE SQL_HASH_VALUE=VS.SQL_HASH_VALUE) SQL_COUNT, VS.PROCESS APP_ID, VS.USERNAME, TO_CHAR(VS.LOGON_TIME,'DD-MM HH24:MI:SS') LOGON_TIME, VS.OSUSER, VS.MODULE, VS.MACHINE, VS.LAST_CALL_ET FROM V$SESSION VS, V$PROCESS VP WHERE VS.PADDR = VP.ADDR AND  VS.STATUS = 'ACTIVE' AND VS.USERNAME IS NOT NULL AND VS.TYPE <> 'BACKGROUND' AND VS.SQL_ID IS NOT NULL ORDER BY 10,3,11;
******************************
/home/satvvikv/lock.sql
******************************
SELECT (SELECT USERNAME FROM V$SESSION WHERE SID=A.SID) BLOCKER, A.SID, 'IS BLOCKING', (SELECT USERNAME FROM V$SESSION WHERE SID = B.SID) BLOCKEE,  B.SID FROM V$LOCK A, V$LOCK B WHERE A.BLOCK=1 AND B.REQUEST > 0 AND A.ID1=B.ID1 AND A.ID2=B.ID2;
******************************
/home/satvvikv/locki.sql
******************************
col session_kill for a60
select 'alter system kill session ' ||''''||sid||','||' '||serial#||''''||' '||'immediate'||';' as session_kill,LOGON_TIME,last_call_et,sql_id,prev_sql_id from v$session where sid in (select SESSION_ID  from  v$locked_object where OBJECT_ID in (select OBJECT_ID from dba_objects where OBJECT_NAME='&TABLE_NAME')) and status ='INACTIVE' order by LOGON_TIME;
******************************
/home/satvvikv/logfiles.sql
******************************
COL MEMBER FOR A85
SELECT GROUP#, MEMBER, TYPE, STATUS FROM V$LOGFILE ORDER BY 1;
******************************
/home/satvvikv/lops_d.sql
******************************
-- AUTHOR: SATVVIK VISSWANATHAN
-- SCRIPT: Get Estimates of Long Running Operations By Operation Name
SELECT OPNAME, ROUND(SOFAR/TOTALWORK*100,2) PERCENT_COMPLETE, ELAPSED_SECONDS/60/60 ELAPSED_HRS, TIME_REMAINING/60/60 TIME_REMAINING_HRS from gv$session_longops where OPNAME = '&OPERATION_NAME';
******************************
/home/satvvikv/planc.sql
******************************
 select ss.snap_id, ss.instance_number node, begin_interval_time, s.sql_id, s.plan_hash_value,
 nvl(executions_delta,0) execs,
 (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
 (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
 from DBA_HIST_SQLSTAT S,
DBA_HIST_SNAPSHOT SS
 where s.sql_id = nvl('&sql_id','4dqs2k5tynk61')
 and ss.snap_id = S.snap_id
 and ss.instance_number = S.instance_number
 and executions_delta > 0 --and plan_hash_value=3099046451
 order by 1, 2, 3;
******************************
/home/satvvikv/redo.sql
******************************
SELECT THREAD#, GROUP#, MEMBERS, ARCHIVED, STATUS, SEQUENCE#, BYTES/1024/1024 "SIZE IN MB", TO_CHAR(FIRST_TIME, 'DD-MON-YY HH:MI:SS') "FIRST TIME" FROM V$LOG ORDER BY 2;
******************************
/home/satvvikv/repair.sql
******************************
set time off
set timing off
set echo off
set head off
set pages 50000
set lines 200
set feedback on
select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE' ,object_type) ||' '||
 ltrim(rtrim(Object_name)) ||
 decode(object_type,'PACKAGE BODY',' compile body',' compile')||' ;'
 from dba_objects
 where status = 'INVALID';
set time on
set timing on
set heading on

******************************
/home/satvvikv/resume.sql
******************************
select user_id, instance_id, status, name, error_msg, suspend_time, resume_time from dba_resumable;
******************************
/home/satvvikv/rman_progress.sql
******************************
col opname for a35
break on CONTEXT on report
compute SUM of SOFAR_GB TOTALWORK_GB TIME_MIN on report
compute max of TIME_MIN  on report
compute sum of PENDING_GB on report
SELECT sid, opname, context, sofar*8192/1024/1024/1024 sofar_GB, totalwork*8192/1024/1024/1024 totalwork_GB, (totalwork-sofar)*8192/1024/1024/1024 pending_gb,
TIME_REMAINING/60 time_min, ELAPSED_SECONDS/60 elps_min, ELAPSED_SECONDS elpse_time,  round(sofar/totalwork*100, 2) compl ,
round(sofar*8192/1024/1024/ELAPSED_SECONDS, 2) speed_MB_per_sec
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
/*AND opname NOT LIKE '%aggregate%'*/
AND totalwork != 0
AND sofar <> totalwork;
******************************
/home/satvvikv/seginfo.sql
******************************
COL OWNER FOR A20;
COL SEGMENT_NAME FOR A35;
COL TABLESPACE_NAME FOR A35;
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 "SIZE IN GB" FROM DBA_SEGMENTS WHERE SEGMENT_NAME = UPPER('&SEGMENT_NAME') GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME ORDER BY 1;
******************************
/home/satvvikv/sgastat.sql
******************************
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
PROMPT ************************
select sysdate from dual;
PROMPT ************************
select POOL,NAME,round(BYTES/1024/1024,0) size_mb from v$sgastat where name='FileOpenBlock';
exit
******************************
/home/satvvikv/sql.sql
******************************
select distinct v.sql_id from v$session v, v$sql s where v.sql_id=s.sql_id and s.sql_text like '%&SAMPLE_SQL_TEXT%' and v.status= 'ACTIVE' and v.username <> 'SYS';
******************************
/home/satvvikv/sql_stat.sql
******************************
******************************
/home/satvvikv/sql_stats.sql
******************************
 select ss.snap_id, begin_interval_time, s.sql_id, s.PARSE_CALLS_TOTAL, s.FETCHES_TOTAL, s.PHYSICAL_READ_BYTES_TOTAL,s.IOWAIT_TOTAL,
 nvl(executions_delta,0) execs,
 (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
 (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
 from DBA_HIST_SQLSTAT S,
DBA_HIST_SNAPSHOT SS
 where s.sql_id = nvl('&sql_id','4dqs2k5tynk61')
 and ss.snap_id = S.snap_id
 and ss.instance_number = S.instance_number
 and executions_delta > 0 --and plan_hash_value=3099046451
 order by 1, 2, 3;
******************************
/home/satvvikv/sqld.sql
******************************
select sql_id,disk_Reads,buffer_gets,buffer_gets/executions,executions from v$sqlarea where sql_id = '&sqlid';
******************************
/home/satvvikv/start.sql
******************************
col instance_name new_value instance_name noprint;
select user ||'@' || instance_name "instance_name" from v$instance;
set sqlprompt "&instance_name>";
set timing on;
set time on;
set lines 198;
set pages 1000;
set colsep |;
set long 99999999;
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
clear scr
******************************
/home/satvvikv/status.sql
******************************
set lines 198 pages 1000 timing on time on
col HOST_NAME for a40
select INSTANCE_NUMBER,INSTANCE_NAME,host_name,name db_name, database_role, open_mode,to_char(startup_time,'DD-MM-YYYY HH24:MI:SS') Start_Time,  to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') System_Time from gv$database a, gv$instance b  where a.inst_id=b.instance_number order by 1;
******************************
/home/satvvikv/sync.sql
******************************
select  ads.DESTINATION , ads.STATUS, ads.TYPE, ads.ARCHIVED_SEQ#, ads.APPLIED_SEQ#,
a.ARCHIVED_SEQ#-ads.ARCHIVED_SEQ# archive_gap, decode(ads.APPLIED_SEQ#,0,0,a.ARCHIVED_SEQ#-ads.APPLIED_SEQ#)  applied_gap, ads.ERROR
from v$archive_dest_status ads, (select ARCHIVED_SEQ# from v$archive_dest_status where dest_id=1) a
where status <> 'INACTIVE';
******************************
/home/satvvikv/sync_dc.sql
******************************
select THREAD#, max(SEQUENCE#), ARCHIVED, APPLIED, DEST_ID from v$archived_log where DEST_ID = 1 group by THREAD#, ARCHIVED, APPLIED, DEST_ID order by 1;
******************************
/home/satvvikv/sync_dr.sql
******************************
select THREAD#, max(SEQUENCE#), ARCHIVED, APPLIED, DEST_ID from v$archived_log where DEST_ID = 2 and APPLIED = 'YES' group by THREAD#, ARCHIVED, APPLIED, DEST_ID order by 1;
******************************
/home/satvvikv/syninfo.sql
******************************
SELECT OWNER, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS WHERE SYNONYM_NAME = '&SYNONYM_NAME';
******************************
/home/satvvikv/tabinfo.sql
******************************
COL DEGREE FOR A10
SELECT OWNER, TABLE_NAME, PARTITIONED, COMPRESSION, TABLESPACE_NAME, NUM_ROWS, LAST_ANALYZED, DEGREE FROM DBA_TABLES WHERE TABLE_NAME = UPPER('&TABLE_NAME');
******************************
/home/satvvikv/tabpart.sql
******************************
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, COMPRESSION, COMPRESS_FOR, SUM(BLOCKS*8192)/1024/1024/1024 "SIZE IN GB" FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = '&TABLE_NAME' GROUP BY TABLE_OWNER, TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, COMPRESSION, COMPRESS_FOR ORDER BY 3;
/
******************************
/home/satvvikv/tabpart_d.sql
******************************
 select table_owner, table_name, partition_name, compression, compress_for, (blocks*8192)/1024/1024/1024 GB from dba_tab_partitions where table_name = upper('&tabname') order by 3
/
******************************
/home/satvvikv/tbs.sql
******************************
set line 160
set pagesize 50
col file_name for a80
col TABLESPACE_NAME for a25
select df.FILE_NAME,df.TABLESPACE_NAME,round(df.BYTES/1024/1024) Allocated,
round(nvl(dfs.BYTES/1024/1024,0)) FREE_SPACE, round(((df.BYTES/1024/1024)-nvl((dfs.BYTES/1024/1024),0))) USED_SPACE
from dba_data_files df,
(select file_id,tablespace_name,sum(bytes) bytes
 from dba_free_space
 group by file_id,tablespace_name) dfs
where df.FILE_ID=dfs.FILE_ID (+)
AND df.TABLESPACE_NAME=upper('&TABLESPACE_NAME')
order by free_space
--substr(df.FILE_NAME,-6,2)
/
******************************
/home/satvvikv/temp_usage.sql
******************************
col tablespace for a25
col EVENT for a35
col USERNAME a15
SELECT s.inst_id,s.sid,s.sql_id,t.TABLESPACE,s.event,s.username,SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, COUNT(*) statements FROM gv$sort_usage T, gv$session S, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.tablespace = TBS.tablespace_name and T.inst_id=s.inst_id GROUP BY s.inst_id,s.sid,s.sql_id,s.event,s.username,T.blocks,TBS.block_size,t.tablespace order by 7
/
******************************
/home/satvvikv/tempfiles.sql
******************************
COL FILE_NAME FOR A60
SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024/1024 "SIZE IN GB" FROM DBA_TEMP_FILES ORDER BY 3;
******************************
/home/satvvikv/tempfree.sql
******************************
SELECT TFS.TABLESPACE_NAME "TABLESPACE NAME", ROUND(TFS.TABLESPACE_SIZE/1024/1024/1024) "TABLESPACE SIZE", ROUND(TFS.ALLOCATED_SPACE/1024/1024/1024) "ALLOCATED SPACE", ROUND(SUM(TSH.BYTES_FREE/1024/1024/1024)) "FREE SPACE", ROUND(TFS.FREE_SPACE/1024/1024/1024) "ACTUAL FREE SPACE" FROM DBA_TEMP_FREE_SPACE TFS INNER JOIN V$TEMP_SPACE_HEADER TSH
ON TFS.TABLESPACE_NAME = TSH.TABLESPACE_NAME GROUP BY TFS.TABLESPACE_NAME,TFS.TABLESPACE_SIZE,TFS.ALLOCATED_SPACE,TFS.FREE_SPACE ORDER BY 1;
******************************
/home/satvvikv/tempfree1.sql
******************************
select tablespace_name,round(sum(bytes_used/1024/1024/1024)) "Used_Space(MB)",round(sum(bytes_free/1024/1024/1024)) "Free_Space(GB)" from v$temp_space_header group by tablespace_name order by 1;******************************
/home/satvvikv/total_temp_usage.sql
******************************
select SUM (T.blocks) * TBS.block_size / 1024 / 1024 / 1024 "TEMP USAGE(GB)" FROM gv$sort_usage T, gv$session S, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.tablespace = TBS.tablespace_name and T.inst_id=s.inst_id GROUP BY TBS.block_size;
******************************
/home/satvvikv/try.sql
******************************
col TABLESPACE_NAME format a30
col TOTAL_SIZE_GB format 999999999.99
col FREE_SIZE_GB format 9999999999.99
col USED_SIZE_GB format 999999999.99
select df.tablespace_name TABLESPACE_NAME, df.bytes/(1024*1024*1024) TOTAL_SIZE_GB, (df.bytes/(1024*1024*1024) -sum(fs.bytes)/(1024*1024*1024)) USED_SIZE_GB, sum(fs.bytes)/(1024*1024*1024) FREE_SIZE_GB, round(sum(fs.bytes)*100/df.bytes) FREE_PERCENT, round((df.bytes-sum(fs.bytes))*100/df.bytes) USED_PERCENT from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files  group by tablespace_name ) df where fs.tablespace_name = df.tablespace_name group by df.tablespace_name, df.bytes order by USED_PERCENT;
******************************
/home/satvvikv/ts_frag.sql
******************************
select sum(dfs.bytes)/1024/1024/1024 BYTES_GB from dba_free_space dfs where dfs.tablespace_name = '&&TABLESPACE_NAME' and dfs.bytes < (select min(ds.next_extent) from dba_segments ds where ds.tablespace_name = '&&TABLESPACE_NAME') order by dfs.block_id;
******************************
/home/satvvikv/waitstat.sql
******************************
select inst_id,METRIC_NAME,VALUE from gv$sysmetric where METRIC_NAME like 'Database Wait Time%' order by inst_id
/
******************************
/home/satvvikv/ws.sql
******************************
col event form a45
col wait_class form a15
col no_of_sessions form 999999
col avg_wait_time form 999999
col wait_time form 999999
select inst_id,event, wait_class, sum(SECONDS_IN_WAIT), sum(WAIT_TIME), count(1) no_of_sessions from gv$session_wait where wait_class <> 'Idle'
group by inst_id,event, wait_class
order by 1;
******************************
/home/satvvikv/ws_old.sql
******************************
column spoolfile new_value xspoolfile;
select 'wait_stats.'||instance_name||'-'||lower(HOST_NAME)||'.'||lower(to_char(sysdate,'DDMMYYYYHH24MI'))||'.lst' spoolfile from v$instance;
spool &xspoolfile;
col event form a45
col wait_class form a15
col no_of_sessions form 999999
col avg_wait_time form 999999
col wait_time form 999999
select inst_id,event, wait_class, sum(SECONDS_IN_WAIT), sum(WAIT_TIME), count(1) no_of_sessions from gv$session_wait where wait_class <> 'Idle'
group by inst_id,event, wait_class
order by 1
******************************
/home/satvvikv/xplan.sql
******************************
select * from table (dbms_xplan.display);

How to change the Grid infrastructure home path

Changing the Oracle Grid Infrastructure Home Path

With release 11.2.0.3 and later, after installing Oracle Grid Infrastructure for a cluster (Oracle Clusterware and Oracle ASM configured for a cluster), if you need to change the Grid home path, then use the following example as a guide to detach the existing Grid home, and to attach a new Grid home:

Caution:

Before changing the Grid home, you must shut down all executables that run in the Grid home directory that you are relinking. In addition, shut down applications linked with Oracle shared libraries.

1. Detach the existing Grid home by running the following command as the Oracle Grid Infrastructure installation owner (grid), where/u01/app/11.2.0/grid is the existing Grid home location:

   $ cd /u01/app/11.2.0/grid/oui/bin
   $ ./detachhome.sh -silent -local -invPtrLoc /u01/app/11.2.0/grid/oraInst.loc
   

2. As root, unlock and move the Grid binaries from the old Grid home location to the new Grid home location. For example, where the old Grid home is/u01/app/11.2.0/grid and the new Grid home is /u01/app/grid:

   cd /u01/app/11.2.0/grid/
   perl rootcrs.pl -unlock
   # mv /u01/app/11.2.0/grid /u01/app/grid
   

3. Clone the Oracle Grid Infrastructure installation, using the instructions provided in "Creating a Cluster by Cloning Oracle Clusterware Step 3: Run the clone.pl Script on Each Destination Node," in Oracle Clusterware Administration and Deployment Guide.
   When you navigate to the Grid_home/clone/bin directory and run the clone.pl script, provide values for the input parameters that provide the path information for the new Grid home.
4. As root again, enter the following commands to start up in the new home location:

   # cd Grid_home/rdbms/install/
   # ./rootadd_rdbms.sh
   # cd Grid_home/crs/install
   # perl rootcrs.pl -patch -destcrshome /u01/app/grid
   

5. Repeat steps 1 through 4 on each cluster member node.

You must relink the Oracle Clusterware and Oracle ASM binaries every time you move the Grid home.

Log file sync wait resolution

Adaptive Log File Sync: Oracle, Please Don’t Do That Again

Disclaimer: Much of what follows is pure speculation on my part. It could be completely wrong, and I’m putting it out there in the hopes that it’ll eventually be proven one way or the other.

The Summary

• Underscore parameter _use_adaptive_log_file_sync
  • Default value changed in 11.2.0.3 from FALSE to TRUE
  • Dynamic parameter
• Enables a new method of communication for LGWR to notify foreground processes of commit
  • Old method used semaphores, LGWR had to explicitly “post” every waiting process
  • New method has the FG processes sleep and “poll” to see if commit is complete
  • Advantage is to free LGWR from CPU work required to inform lots of processes about commits
• LGWR dynamically switches between old and new method based on load and responsiveness
  • Method can switch frequently at runtime, max frequency is 3 switches per minute (configurable)
  • Switch is logged in LGWR tracefile, we have seen several switches per day
• Few problems in general, possible issues seem to be in RAC and/or the switching process itself

The Story

We’re working with a customer who had a very successful Exadata go-live last weekend. They moved a large application mostly unchanged from 9i to Exadata – and we’ve generally had very positive feedback. (!!) It might be that just getting on modern hardware and software accounts for this, but it still gives everyone a good feeling.

Nevertheless, there are always a few little adventures. One of these was a mysterious orange glob on the Cloud Control radar Monday at 1am. Orange is the “commit” class – so this was a little surprising! (And reminiscent of Karl Arao’s famous halloween monster!) A few drill-down clicks and we’re looking at “log file sync” events.  To make a long story short, it wasn’t any of the usual suspects, and the necessary diagnostics info wasn’t available after the fact to say what happened with certainty. We’ve done a fairly comprehensive incident report and it hasn’t recurred… So I’m happy for now.

Anyway, I haven’t gotten to the really fun part yet. The really fun part is the #everydaylearning as Yury would say. In the process of analyzing the orange glob, I ended up investigating whether it might be related to a new feature called Adaptive Log File Sync.  I’d never heard of this before so I had quite a bit of fun learning, and it seemed worthwhile to share a few things I found.

Before I dive in, one other important thing: I have to mention Christo Kutrovsky. We were both on-site and digging into this feature at the same time. There was a constant exchange of competing theories and different ideas about how things might work. Together we assembled a strong, in-depth, and working theory very, very quickly – I love working on a great team.

Log File Sync

First off, there are a few basic starting points for anyone troubleshooting time spent in the log file sync event.

• For past problems, use AWR reports and ASH data.
• For live problems, use sql trace and Tanel’s snapper.
• Read Riyaj Shamsudeen’s excellent article about Tuning ‘log file sync’ Event Waits.
• Read Kevin Closson’s excellent article about LGWR processing.
• Note 34592.1 is a generic reference note explaining the wait event.
• Note 137696.1 is a useful troubleshooting guide.
• Note 1064487.1 is a useful script to collect some diagnostic info for LFS troubleshooting.

In my specific case, I discovered quickly from the ASH data that this was a very unusual situation. This led to some more creative searching in the Oracle Support KnowledgeBase – at which point I discovered something very interesting:

• Note 1462942.1

Adaptive Log File Sync

Note 1462942.1 describes a feature whereby LGWR can switch between log write methods.  This feature is enabled through an underscore parameter called _use_adaptive_log_file_sync and the description of this parameter is: adaptively switch between post/wait and polling.

Now I’ll be honest: searching on the internet and searching in Oracle Support’s KnowledgeBase for “adaptive log file sync” yields almost nothing. I do believe in the principle of BAAG – but this is a case where some guesswork might be useful, especially to guide experimentation that could nail down more concrete answers. Hence the disclaimer at the top of this article.

The words “post” and “wait” indicate that we’re talking about semaphores.  For some general background on semaphores, check out the Wikipedia article and for more detail about the unix post() and wait() calls, a good resource is chapter 30 from Andrea Arpaci-Dusseau’s (UW Madison) textbook on Operating Systems.

Oracle uses semaphores extensively. In fact, LGWR and commits are specifically mentioned as an example in the Performance Tuning Guide – and Oracle even has an API to replace semaphore use with a third-party driver for lightweight post-wait implementations. (Like an ODM for your OS kernel.)

If you look for references to “commit” in the Oracle docs, you’ll find the word “post” everywhere when they talk about communication between the foreground processes and LGWR.  Now, remember that a COMMIT has two options: first, IMMEDIATE or BATCH and second, WAIT or NOWAIT.  It looks like this to me:

• Immediate: FG process will post to LGWR, triggering I/O  (default)
• Batch: FG process will not post LGWR
• Wait: LGWR will post FG process when I/O is complete  (default)
• NoWait: LGWR will not post FG process when I/O is complete

Why change a good thing? My theory is that it ties back to a rare problem Riyaj mentioned in that blog post over 4 years ago.  I’ll quote him:

“LGWR is unable to post the processes fast enough, due to excessive commits. It is quite possible that there is no starvation for CPU or memory, and that I/O performance is decent enough. Still, if there are excessive commits, LGWR has to perform many writes/semctl calls, and this can increase ‘log file sync’ waits. This can also result in sharp increase in redo wastage statistics.”

Maybe Oracle is reading Riyaj’s blog? It appears that they came up with a new algorithm where LGWRdoesn’t post. My guess: foreground processes can probably still post LGWR – but LGWR never posts back. Instead, foreground processes in WAIT mode “poll” either a memory structure or LGWR itself. It could be an in-house implementation by Oracle, it could still use semaphores, or it could use message queues somehow [seems like a long shot but the unix poll() call is found there].

There’s one interesting challenge that I can think of in implementing this. With the semaphore approach, all the active commits are *sleeping* (off the CPU) while LGWR flushes the log buffer.  There could be dozens or hundreds of foreground processes simultaneously commiting on a very busy system.  If we switch to a polling method, how do we ensure that these hundreds of processes don’t start spinning and steal CPU from LGWR, making the whole system even worse than it was in the beginning?

The answer might lie in a quick search for “adaptive_log_file_sync” from the underscore parameters. There are five more hidden parameters with that string:

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx and a.ksppinm like '_%adaptive_log%' escape ''
order by name

Name	Default	Description
_adaptive_log_file_sync_high_switch_freq_threshold	3	Threshold for frequent log file sync mode switches (per minute)
_adaptive_log_file_sync_poll_aggressiveness	0	Polling interval selection bias (conservative=0, aggressive=100)
_adaptive_log_file_sync_sched_delay_window	60	Window (in seconds) for measuring average scheduling delay
_adaptive_log_file_sync_use_polling_threshold	200	Ratio of redo synch time to expected poll time as a percentage
_adaptive_log_file_sync_use_postwait_threshold	50	Percentage of foreground load from when post/wait was last used

It appears that there are a number of knobs to turn with this new algorithm – and it looks like Oracle is somehow dynamically calculating a “polling interval”.  Furthermore, it seems to be taking “scheduling delay” into account.

It also seems that by default, we’re limited to switching modes every 20 seconds (3 per minute).  This switching seems to be controlled by “thresholds” – and the threshold for enabling the new polling mode seems to be based on time.  From the default percentage, it looks to me like Oracle won’t switch until it thinks the poll time will be less than half of the current post time.  For switching back it also seems to be another “half” percentage (50), though I’m not sure what “foreground load” might mean.

Now there’s one other underscore parameter which I noticed while looking for things related to log file sync:

Name	Default	Description
_fg_sync_sleep_usecs	0	Log file sync via usleep

This parameter is interesting for one reason: The adaptive sync parameters were introduced in 11g, but this parameter was introduced in 10g. And if you think about the name, it actually sounds very similar to the “polling” strategy and not like a semaphore strategy at all! Foreground processes call usleep() during a log sync – if you’re calling wait() then you don’t need to sleep. But if you’re polling, you definitely need to sleep. Maybe Oracle has been working on this idea since 10g? I wouldn’t put it past them. :)

So how do you know if you’re using this feature?  The most obvious sign will be in the LGWR trace file.

There will be messages each time that a switch happens, looking something like this:

*** 2012-10-16 01:47:50.289
kcrfw_update_adaptive_sync_mode: poll->post current_sched_delay=0 switch_sched_delay=1 current_sync_count_delta=8 switch_sync_count_delta=59

*** 2012-10-16 01:47:50.289
Log file sync switching to post/wait
Current approximate redo synch write rate is 2 per sec

*** 2012-10-16 02:51:19.285
kcrfw_update_adaptive_sync_mode: post->poll long#=51 sync#=352 sync=4600 poll=1061 rw=500 rw+=500 ack=7 min_sleep=1061

*** 2012-10-16 02:51:19.285
Log file sync switching to polling
Current scheduling delay is 1 usec
Current approximate redo synch write rate is 117 per sec

I mentioned that the underscore parameters were introduced in 11g; however, my current understanding is that only in 11.2.0.3 did the default value of _use_adaptive_log_file_sync change to true!! Is it comforting that they waited so long, or is it scary that they’d make this change with no available documentation or troubleshooting information?  I guess it depends on your perspective. Here’s mine:

Explanation Please?

• This is a change of critical, core code that impacted every single database installation and upgrade.
• I think it is a strange departure from Oracle’s normal practices to change such important code with absolutely no announcement, documentation, or technical troubleshooting information even in their customer-only support database.
• In my opinion this requires explanation; they can do (and have done) much better.

Regardless, it’s now been 12 months since 11.2.0.3 was released. At present, it’s only mentioned in a single support article. This offers at least some evidence that adaptive log file sync hasn’t caused widespread panic.  For the past year, many of you have been using this feature without even knowing – and there weren’t enough problems to merit even two Oracle Support notes.

I sent a few emails out to friends who also tend to work on large, loaded systems. I wasn’t the first person to stumble across this – James Morle mentioned it in a tweet a few months ago, and (not surprisingly)Riyaj has stumbled across the feature as well. It seems to me that the issues that have been encountered intersect with RAC and/or the switching process itself. But my informal survey was hardly scientific.

Overall, I think Oracle dodged the bullet this time. But they still made an important change without supporting documentation. To my friends inside the big red mothership: Please don’t do that again! We like it better the other way.

Have you ever heard of this new 11.2.0.3 feature? Have you heard of the underscore parameter? I’d love to hear your stories!

Migrate Oracle Database from Standard Edition (SE) to Enterprise Edition (EE) and vice verse

Moving From the Standard Edition to the Enterprise Edition and vice-verse

If you are using a Standard Edition database (Release prior to 11gR1), then you can change it to an Enterprise Edition database.

Step 1
Standard Edition database software should be same as the Enterprise Edition database software.
Step 2
Shutdown the database
Step 3
Shut down your all oracle services including oracle database
Step 4
De-install the Standard Edition oracle software
Step 5
Install the Enterprise Edition server software using the Oracle Universal Installer.
Step 6
Select the same Oracle home that was used for the de-installed Standard Edition. During the installation, be sure to select the Enterprise Edition. When prompted, choose Software only from the Database Configuration screen.
Step 7
Start up your database.
Your database is now upgraded to the Enterprise Edition.

Tips:

1. You can only convert Standard Edition Database to the Enterprise Edition Database by using above method.

2. If you want to convert from an Enterprise Edition database to a Standard Edition database, you must use Export/Import operation. Without Export/Import you can not convert.


Inside Story:

1. The Enterprise Edition contains data dictionary objects which are not available in the Standard Edition. If you just install the Standard Edition software, then you will end up with data dictionary objects which are useless. Some of them might be invalid and possibly create problems when maintaining the database.

2. The Export/Import operation does not introduce data dictionary objects specific to the Enterprise Edition, because the SYS schema objects are not exported. Oracle recommends using the Standard Edition EXP utility to export the data.

3. After the Import in the Standard Edition database, you are only required to drop all user schemas related to Enterprise Edition features, such as the MDSYS account used with Oracle Spatial.