Script_dg new

 select dest_id,process,transmit_mode,async_blocks, 

net_timeout,delay_mins,reopen_secs,register,binding 

from v$archive_dest; 

column error format a55 tru 

select dest_id,status,error from v$archive_dest; 

column message format a80 

select message, timestamp 

from v$dataguard_status 

where severity in ('Error','Fatal') 

order by timestamp; 

select group#,sequence#,bytes,used,archived,status from v$standby_log;

select group#,thread#,sequence#,bytes,archived,status from v$log; 

select process,status,client_process,sequence#,block#,active_agents,known_agents

from v$managed_standby;

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"

from (select thread# thrd, max(sequence#) almax

      from v$archived_log

      where resetlogs_change#=(select resetlogs_change# from v$database)

      group by thread#) al,

     (select thread# thrd, max(sequence#) lhmax

      from v$log_history

      where first_time=(select max(first_time) from v$log_history)

      group by thread#) lh

where al.thrd = lh.thrd;

select * from v$archive_gap; 

set numwidth 5 

column name format a30 tru 

column value format a50 wra 

select name, value 

from v$parameter 

where isdefault = 'FALSE';

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

multiple_awr_generator

set lines 150

set pages 500

col BEGIN_INTERVAL_TIME for a30

col END_INTERVAL_TIME for a30

select SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME

from dba_hist_snapshot

where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-&no)

order by 1;

CREATE OR REPLACE DIRECTORY AWR_REPORTS_DIR AS '/tmp/awrreports';

 

DECLARE

  -- Adjust before use.

  l_snap_start       NUMBER := 15080; --Specify Initial Snap ID

  l_snap_end         NUMBER := 15088; --Specify End Snap ID

  l_dir              VARCHAR2(50) := 'AWR_REPORTS_DIR';

  

  l_last_snap        NUMBER := NULL;

  l_dbid             v$database.dbid%TYPE;

  l_instance_number  v$instance.instance_number%TYPE;

  l_file             UTL_FILE.file_type;

  l_file_name        VARCHAR(50);

 

BEGIN

  SELECT dbid

  INTO   l_dbid

  FROM   v$database;

 

  SELECT instance_number

  INTO   l_instance_number

  FROM   v$instance;

    

  FOR cur_snap IN (SELECT snap_id

                   FROM   dba_hist_snapshot

                   WHERE  instance_number = l_instance_number

                   AND    snap_id BETWEEN l_snap_start AND l_snap_end

                   ORDER BY snap_id)

  LOOP

    IF l_last_snap IS NOT NULL THEN

      l_file := UTL_FILE.fopen(l_dir, 'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.html', 'w', 32767);

      

      FOR cur_rep IN (SELECT output

                      FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id)))

      LOOP

        UTL_FILE.put_line(l_file, cur_rep.output);

      END LOOP;

      UTL_FILE.fclose(l_file);

    END IF;

    l_last_snap := cur_snap.snap_id;

  END LOOP;

  

EXCEPTION 

  WHEN OTHERS THEN

    IF UTL_FILE.is_open(l_file) THEN

      UTL_FILE.fclose(l_file);

    END IF;

    RAISE; 

END;

/

DBA Daily monitoring scripts

 act_wait.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 

/

 

=================================================================== 

act_wait_user.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

/ 

=================================================================== 

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'; 

=================================================================== 

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'); 

=================================================================== 

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; 

=================================================================== 

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; 

=================================================================== 

bind_cap.sql 

select  name||'|'|| value_string

from v$sql_bind_capture

where sql_id='&sqlid'

/ 

=================================================================== 

bind_history.sql 

SELECT snap_id, NAME,POSITION,DATATYPE_STRING,VALUE_STRING,LAST_CAPTURED cdate

FROM DBA_HIST_SQLBIND WHERE SQL_ID='&sqlid'

-- and SNAP_ID='&sqlid'

order by snap_id desc

/

 

=================================================================== 

blocking.sql 

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) on Node ' ||s1.inst_id||' is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) on Node'||s2.inst_id AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l2.request > 0 and l1.BLOCK <>0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 and s2.username is not null and s1.username is not null

/

 

=================================================================== 

cf_en.sql 

--holder of the CF enqueue

select l.sid, p.program, p.pid, p.spid, s.username, s.terminal, s.module, s.action, s.event, s.wait_time, s.seconds_in_wait, s.state

from gv$lock l, gv$session s, gv$process p

where l.sid = s.sid

and s.paddr = p.addr

and l.type='CF'

and l.lmode >= 5;

--session waiting to get the CF enqueue

select l.sid, p.program, p.pid, p.spid, s.username, s.terminal, s.module, s.action, s.event, s.wait_time, s.seconds_in_wait, s.state

from gv$lock l, gv$session s, gv$process p

where l.sid = s.sid

and s.paddr = p.addr

and l.type='CF'

and l.request >= 5; 

=================================================================== 

cgo.sql 

select owner,object_type,count(1) NO_OF_OBJECTS from dba_objects where owner in ('AUDITLOG','CONTENTSTORE','METRICSTORE','NOTIFICATION') group by object_type,owner order by owner

/

 

=================================================================== 

cgs.sql 

select owner,segment_type,tablespace_name,sum(bytes)/1024/1024/1024 SIZE_IN_GB from dba_segments where owner in ('AUDITLOG','CONTENTSTORE','METRICSTORE','NOTIFICATION') group by owner,segment_type,tablespace_name order by 4

/

 

=================================================================== 

da.sql 

select * from table ( dbms_xplan.display_awr('&sql_id'))

/

 

=================================================================== 

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;

 

=================================================================== 

dc.sql 

select * from table ( dbms_xplan.display_cursor('&sql_id'))

/

 

=================================================================== 

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

/

 

=================================================================== 

dict.sql 

col comments for a70

 select * from dict

 where table_name like upper('%&ENTER%');

 

=================================================================== 

dict_stats.sql 

exec dbms_stats.gather_dictionary_stats; 

=================================================================== 

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;

 

=================================================================== 

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;

 

=================================================================== 

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;

 

=================================================================== 

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;

 

=================================================================== 

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.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;

 

=================================================================== 

exp_progress.sql 

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 gv$session_longops

WHERE opname LIKE 'SYS_EXPORT_SCHEMA_01'

/*AND opname NOT LIKE '%aggregate%'*/

AND totalwork != 0

AND sofar <> totalwork

/

 

=================================================================== 

finalhw.sql 

select final_blocking_Session,final_blocking_instance ,count(1) from gv$session group by final_blocking_Session,final_blocking_instance; 

=================================================================== 

ftext.sql 

set head off;

set long 99999999

select SQL_FULLTEXT from v$sqlarea where SQL_ID = '&SQL_ID';

set head on;

 

=================================================================== 

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 

/

 

=================================================================== 

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;

 

=================================================================== 

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

/

 

=================================================================== 

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'; 

=================================================================== 

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'; 

=================================================================== 

gcom.sql 

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 gv$session_longops

WHERE opname = '&opname'

/*AND opname NOT LIKE '%aggregate%'*/

AND totalwork != 0

AND sofar <> totalwork

/

 

=================================================================== 

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;

 

=================================================================== 

gftext.sql 

set head off;

set long 99999999

select SQL_FULLTEXT from gv$sqlarea where SQL_ID = '&SQL_ID';

set head on;

 

=================================================================== 

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;

 

=================================================================== 

gkill_id.sql 

SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||INST_ID||''' IMMEDIATE;' FROM GV$SESSION  WHERE SQL_ID='&SQL_ID'; 

=================================================================== 

gkill_sid.sql 

SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||inst_id||''' IMMEDIATE;' FROM GV$SESSION where (sid,inst_id) in (select sid,inst_id from gv$access where object='&obj' and owner='&owner');  

=================================================================== 

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;

 

=================================================================== 

glc_1.sql 

 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 and vs.inst_id=1 ORDER BY 4,3,11

/

 

=================================================================== 

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;

 

=================================================================== 

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;

 

=================================================================== 

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'

/

 

=================================================================== 

gvlock_1.sql 

select

   c.owner,

   c.object_name,

   c.object_type,

   b.sid,

   b.serial#,

   b.status,

   b.osuser,

   b.machine

from

   gv$locked_object a ,

   gv$session b,

   dba_objects c

where

   b.sid = a.session_id

and

   a.object_id = c.object_id

/

 

=================================================================== 

gvsk.sql 

select 'alter system kill session '''||SID||','||SERIAL#||',@'||INST_ID||''' immediate;' from gv$session where (sid,inst_id) in (select sid,inst_id from gv$access where object='&obj' and owner='&owner')

/

 

=================================================================== 

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);

 

=================================================================== 

gv_sess_access.sql 

col machine for a40

col username for a15

col osuser for a20

set lines 1000 pages 200

alter session set nls_date_format='DD-MM-YYYY hh24:mi:ss';

select inst_id,sid,serial#,sql_id,username,osuser,machine,module,status,logon_time from gv$session where (sid,inst_id) in (select sid,inst_id from gv$access where object='&obj' and owner='&owner')

/

 

=================================================================== 

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;

 

=================================================================== 

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;

 

=================================================================== 

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;

 

=================================================================== 

imp_progress.sql 

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 gv$session_longops

WHERE opname LIKE 'SYS_IMPORT_FULL_01'

/*AND opname NOT LIKE '%aggregate%'*/

AND totalwork != 0

AND sofar <> totalwork

/

 

=================================================================== 

ind.sql 

select b.sid,a.sql_id,p.spid,round(sum(c.sofar)/sum(c.totalwork),3)*100 percentage from gv$sqlarea a,gv$session b,gv$session_longops c,gv$process p where /*c.totalwork <> c.sofar and */ c.sid =b.sid and b.paddr = p.addr and b.sql_hash_value=a.hash_value group by a.sql_id,p.spid,b.sid order by 4

/

 

=================================================================== 

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');

 

=================================================================== 

ind_column_info.sql 

select

b.uniqueness, a.index_name,b.index_type, a.table_name, a.column_name

from all_ind_columns a, all_indexes b

where a.index_name=b.index_name

and a.table_name = upper('&table_name')

order by a.table_name, a.index_name, a.column_position

/

 

=================================================================== 

ind_progress.sql 

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 gv$session_longops

WHERE opname = 'Sort Output'

/*AND opname NOT LIKE '%aggregate%'*/

AND totalwork != 0

AND sofar <> totalwork

/

 

=================================================================== 

ind_sid.sql 

select b.sid,a.sql_id,p.spid,round(sum(c.sofar)/sum(c.totalwork),3)*100 percentage from gv$sqlarea a,gv$session b,gv$session_longops c,gv$process p where /*c.totalwork <> c.sofar and */ c.sid =b.sid and b.paddr = p.addr and b.sql_hash_value=a.hash_value and b.sid='&sid' group by a.sql_id,p.spid,b.sid order by 4

/

 

=================================================================== 

ind_sql.sql 

select b.sid,a.sql_id,p.spid,round(sum(c.sofar)/sum(c.totalwork),3)*100 percentage from gv$sqlarea a,gv$session b,gv$session_longops c,gv$process p where /*c.totalwork <> c.sofar and */ c.sid =b.sid and b.paddr = p.addr and b.sql_hash_value=a.hash_value and a.sql_id='&sql_id' group by a.sql_id,p.spid,b.sid order by 4

/

 

=================================================================== 

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;

 

=================================================================== 

kill_id.sql 

SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||inst_id||''' IMMEDIATE;' FROM GV$SESSION  WHERE username='&username'; 

=================================================================== 

kill_sql.sql 

SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||inst_id||''' IMMEDIATE;' FROM GV$SESSION  WHERE sql_id='&sql_id'; 

=================================================================== 

pid.sql 

select     s.sid || ',' || s.serial# "SID/SERIAL"

,     s.username

,     s.osuser

,     p.spid "OS PID"

,     s.program

from     gv$session s

,     gv$process p

Where     s.paddr = p.addr

and s.sid=1471

order      by to_number(p.spid)

/

 

=================================================================== 

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;

 

=================================================================== 

resume.sql 

select user_id, instance_id, status, name, error_msg, suspend_time, resume_time from dba_resumable; 

=================================================================== 

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 gv$session_longops

WHERE opname LIKE '%RMAN%'

/*AND opname NOT LIKE '%aggregate%'*/

AND totalwork != 0

AND sofar <> totalwork;

 

=================================================================== 

rollback_status.sql 

select state,

undoblocksdone,

undoblockstotal,

undoblocksdone / undoblockstotal * 100

from gv$fast_start_transactions

/

 

=================================================================== 

rollback_status1.sql 

SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done",

undoblockstotal-undoblocksdone "ToDo",

DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))

"Finish at"

FROM gv$fast_start_transactions

/

 

=================================================================== 

spid.sql 

select   s.inst_id,  s.sid || ',' || s.serial# "SID/SERIAL"

,     s.username

,     s.osuser

,     p.spid "OS PID"

,     s.program

from     gv$session s

,     gv$process p

Where     s.paddr = p.addr

and s.sid=&sid

order      by to_number(p.spid)

/

 

=================================================================== 

sqld.sql 

select sql_id,disk_Reads,buffer_gets,buffer_gets/executions,executions from gv$sqlarea where sql_id = '&sqlid'; 

=================================================================== 

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 - - - - - - - - - - - - - - - - - - - - - - - - - -

 

=================================================================== 

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; 

=================================================================== 

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; 

=================================================================== 

syninfo.sql 

SELECT OWNER, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS WHERE SYNONYM_NAME = '&SYNONYM_NAME'; 

=================================================================== 

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');

 

=================================================================== 

table_create.sql 

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 gv$session_longops

WHERE opname in ('Table Scan','Hash Join')

/*AND opname NOT LIKE '%aggregate%'*/

AND totalwork != 0

AND sofar <> totalwork

/

 

=================================================================== 

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;

/

 

=================================================================== 

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

/

 

=================================================================== 

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)

/

 

=================================================================== 

tde_progress.sql 

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 gv$session_longops

WHERE opname like 'TDE%'

/*AND opname NOT LIKE '%aggregate%'*/

AND totalwork != 0

AND sofar <> totalwork

/

 

=================================================================== 

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;

 

=================================================================== 

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;

 

=================================================================== 

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;******************************

 

=================================================================== 

temp_tbs.sql 

 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_temp_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)

/

 

=================================================================== 

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

/

 

=================================================================== 

temp_usage1.sql 

SELECT

   A.tablespace_name tablespace,

   D.mb_total,

   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM

   gv$sort_segment A,

(

SELECT

   B.name,

   C.block_size,

   SUM (C.bytes) / 1024 / 1024 mb_total

FROM

   gv$tablespace B,

   gv$tempfile C

WHERE

   B.ts#= C.ts#

GROUP BY

   B.name,

   C.block_size

) D

WHERE

   A.tablespace_name = D.name

GROUP by

   A.tablespace_name,

   D.mb_total

/

 

=================================================================== 

temp_util.sql 

SELECT SUB.TABLESPACE_NAME, SUB.STATUS, SUB.EXTENT_MANAGEMENT, SUB.SEGMENT_SPACE_MANAGEMENT, SUB.TOTAL_SPACE_MB, SUB.USED_SPACE_MB, SUB.FREE_SPACE_MB, SUB.PERCENT_FREE_SPACE, SUB.CONTENTS FROM ( SELECT D.TABLESPACE_NAME,D.STATUS,D.EXTENT_MANAGEMENT,D.SEGMENT_SPACE_MANAGEMENT,ROUND((A.BYTES/1024/1024),2) TOTAL_SPACE_MB,ROUND(((A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),2) USED_SPACE_MB, ROUND(((A.BYTES/1024/1024)-(A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),2) FREE_SPACE_MB, DECODE(A.BYTES, 0, TO_NUMBER(NULL), ROUND((((A.BYTES/1024/1024)-(A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024)/(A.BYTES/1024/1024))*100.0,2)) PERCENT_FREE_SPACE, D.CONTENTS FROM SYS.DBA_TABLESPACES D,(SELECT TABLESPACE_NAME, SUM(NVL(BYTES, 0)) BYTES FROM SYS.DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,SYS.SM$TS_FREE F WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME AND F.TABLESPACE_NAME(+)=D.TABLESPACE_NAME  UNION ALL SELECT D.TABLESPACE_NAME,D.STATUS, D.EXTENT_MANAGEMENT,D.SEGMENT_SPACE_MANAGEMENT,ROUND((A.BYTES / 1024 / 1024),2) TOTAL_SPACE_MB, ROUND(NVL(T.BYTES, 0)/1024/1024,2) USED_SPACE_MB,ROUND((A.BYTES / 1024 / 1024) - (NVL(T.BYTES, 0)/1024/1024),2) FREE_SPACE_MB, DECODE(A.BYTES, 0, TO_NUMBER(NULL), ROUND(100 - (NVL(T.BYTES /A.BYTES * 100, 0)),2)) PERCENT_FREE_SPACE, D.CONTENTS FROM SYS.DBA_TABLESPACES D, (SELECT TABLESPACE_NAME, SUM(DECODE(BYTES, NULL, 0, BYTES)) BYTES FROM SYS.DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, SUM(DECODE(BYTES_CACHED, NULL, 0, BYTES_CACHED)) BYTES  FROM SYS.V_$TEMP_EXTENT_POOL GROUP BY TABLESPACE_NAME) T WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+) AND D.EXTENT_MANAGEMENT LIKE 'LOCAL' AND D.CONTENTS LIKE 'TEMPORARY' ) SUB ORDER BY SUB.TABLESPACE_NAME

/

 

=================================================================== 

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;

 

=================================================================== 

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;

 

=================================================================== 

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; 

 

=================================================================== 

undo_seg.sql 

select segment_name,

round(nvl(sum(act),0)/(1024*1024*1024),3 ) "ACT GB BYTES",

round(nvl(sum(unexp),0)/(1024*1024*1024),3) "UNEXP GB BYTES",

round(nvl(sum(exp),0)/(1024*1024*1024),3) "EXP GB BYTES",

NO_OF_EXTENTS

from ( select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp, count(*) NO_OF_EXTENTS

from DBA_UNDO_EXTENTS

where status='ACTIVE' and tablespace_name = '&UNDO_TBS_NAME'

group by segment_name

union

select segment_name,00 act, nvl(sum(bytes),0) unexp, 00 exp , count(*) NO_OF_EXTENTS

from DBA_UNDO_EXTENTS

where status='UNEXPIRED' and tablespace_name = '&UNDO_TBS_NAME'

group by segment_name

union

select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp, count(*) NO_OF_EXTENTS

from DBA_UNDO_EXTENTS

where status='EXPIRED' and tablespace_name = '&UNDO_TBS_NAME'

group by segment_name

) group by segment_name, NO_OF_EXTENTS order by 5 desc

/

 

=================================================================== 

undo_stat.sql 

 select status, sum(bytes)/1024/1024/1024 GB

  from dba_undo_extents

  group by status

/

 

=================================================================== 

waitstat.sql 

select inst_id,METRIC_NAME,VALUE from gv$sysmetric where METRIC_NAME like 'Database Wait Time%' order by inst_id

/

 

=================================================================== 

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;

 

=================================================================== 

xplan.sql 

select * from table (dbms_xplan.display); 

=================================================================== 

db scripts

db scripts

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);