******************************
/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);
/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);
No comments:
Post a Comment