Below is the script to find the os server process id of a SID. set lines 123 col USERNAME for a15 col OSUSER for a8 col MACHINE for a15 col PROGRAM for a20 select b.spid, a.username, a.program , a.osuser ,a.machine, a.sid, a.serial#, a.status from gv$session a, gv$process b where addr=paddr(+) and sid=&sid; SEE ALSO – […]

If you have the sql_id of the sql query, then you can create a baseline of that using below code. DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache( sql_id => ‘&sql_id’); END; / Suppose you have multiple plan hash value of the sql_id and you wish to create baseline with a particular plan hash value , […]

Below is the script to find the utilizations of redo logs SELECT le.leseq “Current log sequence No”, 100*cp.cpodr_bno/le.lesiz “Percent Full”, cp.cpodr_bno “Current Block No”, le.lesiz “Size of Log in Blocks” FROM x$kcccp cp, x$kccle le WHERE le.leseq =CP.cpodr_seq AND bitand(le.leflg,24) = 8 /

Below script will generate the database size for past few days(per hour) set serveroutput on Declare v_BaselineSize number(20); v_CurrentSize number(20); v_TotalGrowth number(20); v_Space number(20); cursor usageHist is select a.snap_id, TIMESTAMP, sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum from (select SNAP_ID, sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA from DBA_HIST_SEG_STAT group by SNAP_ID having sum(SPACE_ALLOCATED_TOTAL) <> 0 order by 1 ) […]

Below script is used to find, how a user account got locked. — Return code 1017 ( INVALID LOGIN ATTEMPT) — Return code 28000 ( ACCOUNT LOCKED) set pagesize 1299 set lines 299 col username for a15 col userhost for a13 col timestamp for a39 col terminal for a23 SELECT username,userhost,terminal,timestamp,returncode FROM dba_audit_session WHERE username=’&USER_NAME’ […]

Below script is for finding the row counts of all partitions of a table in Oracle. set serverout on size 1000000 set verify off declare sql_stmt varchar2(1024); row_count number; cursor get_tab is select table_name,partition_name from dba_tab_partitions where table_owner=upper(‘&&TABLE_OWNER’) and table_name=’&&TABLE_NAME’; begin dbms_output.put_line(‘Checking Record Counts for table_name’); dbms_output.put_line(‘Log file to numrows_part_&&TABLE_OWNER.lst ….’); dbms_output.put_line(‘….’); for get_tab_rec in […]

Below scripts are useful to find the i/o usage of tempfiles SELECT SUBSTR(t.name,1,50) AS file_name, f.phyblkrd AS blocks_read, f.phyblkwrt AS blocks_written, f.phyblkrd + f.phyblkwrt AS total_io FROM v$tempstat f,v$tempfile t WHERE t.file# = f.file# ORDER BY f.phyblkrd + f.phyblkwrt DESC; select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB, i.inst_id,i.host_name […]

Below is the script for finding high water mark of a table. — — Show the High Water Mark for a given table, or all tables if ALL is specified for Table_Name. — SET LINESIZE 300 SET SERVEROUTPUT ON SET VERIFY OFF DECLARE CURSOR cu_tables IS SELECT a.owner, a.table_name FROM all_tables a WHERE a.table_name = […]

Below are the scripts for finding the sessions holding libary cache lock: For standalone db: select sid Waiter, p1raw, substr(rawtohex(p1),1,30) Handle, substr(rawtohex(p2),1,30) Pin_addr from v$session_wait where wait_time=0 and event like ‘%library cache%’; For RAC DB: select a.sid Waiter,b.SERIAL#,a.event,a.p1raw, substr(rawtohex(a.p1),1,30) Handle, substr(rawtohex(a.p2),1,30) Pin_addr from v$session_wait a,v$session b where a.sid=b.sid and a.wait_time=0 and a.event like ‘library cache%’; […]

                   If you have the sql_id of the sql query , then you can get the bind values of the bind variables, from v$sql_bind_capture. Script: SELECT sql_id, b. LAST_CAPTURED, t.sql_text sql_text, b.HASH_VALUE, b.name bind_name, b.value_string bind_value FROM gv$sql t JOIN gv$sql_bind_capture b using (sql_id) WHERE b.value_string is […]