Dynamically create a SQL script to recompile all INVALID objects

set feedback off
set verify off
set echo off
set pagesize 0
set heading off

spool compile_invalid_objects.sql
select  'alter ' ||
       decode(object_type, 'PACKAGE BODY', 'package', object_type) ||
       ' ' ||
       object_name||
       ' compile' ||
       decode(object_type, 'PACKAGE BODY', ' body;', ';')
from   dba_objects
where  status = 'INVALID'
/
spool off
set feedback on
set verify on
set heading on
set pagesize 40
@compile_invalid_objects

Scripts Used in Critical Production Biz Time Monitoring

=== Temp Usage ===

 

select sum(blocks)/1024*8 "Size in MB" FROM v$sort_usage;

 

SELECT ss.sid, sum(st.blocks)/1024*8 FROM v$sort_usage st, v$session ss where ss.saddr=st.session_addr group by ss.sid having sum(st.blocks)/1024*8 > 100 order by 2;

 

SELECT  /*+ RULE */ s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),
                         NVL(s.module,'N/A'), NVL(s.action,'N/A'), NVL(s.program,'N/A'),
                         s.status ||' for '||
                         LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||
                         LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
                         u.tablespace, u.contents, u.extents, round((u.blocks*8)/1024),
                         s.sql_address, s.sql_hash_value
                 FROM    v$session s, v$sort_usage u
                 WHERE   s.saddr    = u.session_addr
                 AND     u.contents = 'TEMPORARY'
                 AND     s.audsid != USERENV('sessionid')
                 AND    (u.blocks*8)/1024 >= 1000
                 ORDER   BY 1,2,3,4,5 Desc;

 

=== High Redo ===

 

SELECT s.inst_id,s.sid, s.serial#, s.username, s.program, i.block_changes
FROM gv$session s, gv$sess_io i
WHERE s.sid = i.sid
AND i.block_changes > 10000000
ORDER BY 6 desc, 1, 2, 3, 4;

=== Rollback Used ===
                                 
SELECT rn.name, ROUND(rs.rssize/1024/1024),
                        s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),
                        NVL(s.module,'N/A'), NVL(s.action,'N/A'), NVL(s.program,'N/A'),
                        s.sql_address, s.sql_hash_value, p.spid,
                        s.status ||' for '||
                        LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||
                        LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
                        round(ceil((t.used_ublk*8)/1024),1)
                 FROM   v$rollname rn, v$rollstat rs,
                        v$session s, v$transaction t, v$process p
                 WHERE  rn.usn = rs.usn
                 AND    round((t.used_ublk*8)/1024) >= 1000
                 AND    rs.usn = t.xidusn
                 AND    s.sid = p.pid (+)
                 AND    s.taddr = t.addr
                 ORDER  BY 2 desc, s.sid ,s.status

 

=== Roll back segement Information ====

 

select tablespace_name, status segment_status, count(extent_id) "Extent Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) "Total Space in GB" from dba_undo_extents where tablespace_name like '%UNDO%'
group by tablespace_name, status;

 

=== Shared Pool Usage ===

 

SELECT INST_ID,ROUND(bytes/1024/1024, 2)||' MB' FROM gv$sgastat  WHERE name='free memory' AND pool='shared pool';

 

=== Archive Generation for last 5 hours ===

 

SELECT TO_CHAR(first_time, 'DD-MM-YY') AS Day,TO_CHAR(first_time, 'HH24') AS Hour,COUNT(*)  FROM v$log_history WHERE TO_CHAR(first_time, 'DD-MM-YY') = TO_CHAR(sysdate, 'DD-MM-YY') AND  TO_CHAR(first_time, 'HH24') >= TO_CHAR(sysdate, 'HH24') - 5 GROUP BY  TO_CHAR(first_time, 'DD-MM-YY'), TO_CHAR(first_time, 'HH24') ORDER BY 2;

 

=== High Memory ===

 

select s.inst_id, s.sid, name, round(value/1024/1024) Mem_in_mb, p.*
from gv$sesstat s, v$statname n, gv$session p
where n.statistic# = s.statistic#
and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%'
and value > 1*1024*1024*512;

 

=== Performance ===

 

select sql_hash_value, count(*) from v$session where event like 'enq%' group by sql_hash_value;

 

select sql_hash_value,username,osuser, count(*) from v$session where event like 'enq%' and SQL_HASH_VALUE='&event' group by sql_hash_value,username,osuser;

 

select sql_text from v$sqlarea where hash_value = '&hash_value';

 

select s1.sid,FETCHES,ROWS_PROCESSED from v$sql s,v$session s1 where s.HASH_VALUE=s1.SQL_HASH_VALUE and s1.sid=4885;

 

col EVENT for a50;
col OSUSER for a20;
col USERNAME for a20;
set linesize 175;
select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event from v$session s,v$session_wait x where s.paddr in
( select addr from v$process where spid in (2340,23869,13827,18261,14880,2381))
and x.sid=s.sid;

 

col EVENT for a50;
col OSUSER for a20;
col USERNAME for a20;
set linesize 175;
select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event from v$session s,v$session_wait x
where x.sid=s.sid and x.event like '&event_name';

 

=== Top 10 Memory Process at OS level ===

 

UNIX95=1 ps -eo vsz,pid,args | sort +0n -1 | grep -i `echo $ORACLE_SID` | tail -10

 

=== Other Script to update the daily report ===

 

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

 

STATUS     COUNT(1)
-------- ----------
ACTIVE           22
INACTIVE        155

 

select count(1) from dba_tables where logging='NO';

 

  COUNT(1)
----------
       919

 

select distinct status,count(1) from dba_indexes group by status;

 

STATUS     COUNT(1)
-------- ----------
N/A            1639
UNUSABLE          1
VALID          6162

 

select count(1) from dba_objects where status='INVALID';
'
  COUNT(1)
----------
       681

 

Check Concurrent Manager Status from the Back end?

How to check Concurrent Managers Status from the Back End?

select CONCURRENT_QUEUE_NAME,max_processes,running_processes,decode(control_code,
'A','Activating',
'B','Activated',
'D','Deactivating',
'E','Deactivated',
'N','Target node/queue unavailable',
'O','Suspending concurrent manager',
'P','Suspended',
'Q','Resuming concurrent manager',
'R','Restarting',
'T','Terminating',
'U','Updating environment information',
'V','Verifying',
'X','Terminated')
from apps.fnd_concurrent_queues
where
MAX_PROCESSES <> RUNNING_PROCESSES or
(control_code is not null and control_code not in ('B','E'));

Find out Explain Plan of Session

How to get explain plan of Session?

set lines 120
select
lpad(' ', 2*(depth))||operation||' '||options
||' '||decode(object_owner,null,null,
object_owner||'.'||object_name)
||' '||decode(cost,null,null,'Cost = '||cost)
||' '||decode(partition_start,null,null,
'Partition='||partition_start||'..'||partition_stop)
||' '||decode(bytes,null,null,'Bytes= '||bytes)
-- ||' '||decode(access_predicates,null,null,'Access= '||access_predicates)
-- ||' '||decode(filter_predicates,null,null,'Filter= '||filter_predicates)
--, other,other_tag, temp_space
"Execution Plan"
from v$sql_plan
where (address, hash_value) =
(select sql_address, sql_hash_value
from v$session
where sid = &sid)
order by id
/
undefine 1

Note: Pass SID when it prompts

Find out Session Waits in a Database

How to find out Session wait events in a Database?

column event format a41
set pagesize 100

select a.event,count(*),avg(b.wait_time)
from v$session a,v$session_wait b
where a.sid = b.sid
and a.type != 'BACKGROUND'
group by a.event
order by count(*) desc
/

How to find out sid of a query running?

How to find out session id of a sql that is running?

SELECT s.sid FROM v$session s, v$sqlarea a,v$process p WHERE s.SQL_HASH_VALUE = a.HASH_VALUE AND s.SQL_ADDRESS = a.ADDRESS AND s.PADDR = p.ADDR and a.sql_text like '%ALTER INDEX%';

Note: Where sql_text is some unique part of the sql running

Check Redolog count hourly basis

How to find , how many redo switches happening every hour?

select (to_char(first_time,'mm/dd')), (to_char(first_time,'HH24')), count(*)
from v$log_history
group by (to_char(first_time,'HH24'))
,(to_char(first_time,'mm/dd'))
order by 1,2 asc
/

Check package is used by any session in RAC DB?

select inst_id,sid,serial#,status from gv$session where sid in ( select sid from gv$access where object like '%E_ORDER_HEADERS_ALL_%');

Find out Package Locks

Use Following Script:

CLEAR COL BREAK COMPUTE
SET PAGES 100 PAUSE OFF VERIFY OFF FEEDBACK ON ECHO OFF

COL object_name FORMAT A35
COL type FORMAT A20
COL sid FORMAT 9999
COL held FORMAT A5
COL request FORMAT A7

SELECT session_id sid,
owner||'.'||name object_name,
type,
mode_held held,
mode_requested request
FROM dba_ddl_locks
WHERE name LIKE UPPER('%&object_name%')
/

SET PAGES 32 PAUSE OFF VERIFY OFF FEEDBACK ON ECHO OFF
PROMPT

When prompts give Package name

Tablespace Usage Info (Free Space, Used Space, Percentage)

Solution:

select round((bytes/1024)/1024,0) "Used Space(MB)",
round(total,0) "Allocated size(MB)",
round(max,0) "Maximum allowable(MB)",
round(max-(BYTES/1024)/1024,0) "Effective free(MB)",
round(((max-(BYTES/1024)/1024)/max)*100,2) "FREE(%)"
from SYS.SM$TS_USED,
(select sum((BYTES/1024)/1024) total, sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
from dba_data_files where tablespace_name='&1') where tablespace_name='&1';
Note: Pass Tablespace whenever you are prompted

Get the Temporary Table Space information?

Solution:

select (select sum(Bytes)/1024/1024/1024 from dba_temp_files) "TOTAL SPACE in GB",(select decode(sum(blocks),null,0,sum(blocks)*8192/1024/1024/1024) from v$sort_usage) "CURRENT USAGE in GB",(select count(*) from v$sort_usage where blocks>(100000000/8192))"SESSIONS USING > 1000MB" from dual;

or

SELECT inst_id "instid",
SUBSTR(tablespace_name,1,15) "ts",
used_blocks*&bs/1048/1048/1048 "used mb",
free_blocks*&bs/1048/1048/1048 "free mb",
total_blocks*&bs/1048/1048/1048 "total mb"
FROM gv$sort_segment;

(pass block_size(get it from show parameter block_size))

How do you kill Bulk number of sessions at a single Time?

Solution:

set head off
set pagesize 1000
spool kill.sql

select 'alter system kill session '||''''||sid||','||serial#||''''||';' from V$session where username='MT' AND status='INACTIVE';

spool off
@kill.sql

what Sqls/scripts/modules are running in the databases by what users?

Solution: Use the following script

column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped

select username'('sid','serial#')' username, module, action, client_infofrom v$sessionwhere moduleactionclient_info is not null;

Know Currnet SQL Running

1. How do you know what is the current SQL Running in the Database?

Solution: use the folowing script

column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in ( select username'('sid','serial# ') ospid = ' process ' program = ' program username, to_char(LOGON_TIME,' Day HH24:MI') logon_time, to_char(sysdate,' Day HH24:MI') current_time, sql_address, LAST_CALL_ET
from v$session where status = 'ACTIVE' and rawtohex(sql_address) <> '00' and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) max(decode(piece,1,sql_text,null)) max(decode(piece,2,sql_text,null)) max(decode(piece,3,sql_text,null)) sql_text from v$sqltext_with_newlines where address = x.sql_address and piece < 4)
loop if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%') then dbms_output.put_line( '--------------------' ); dbms_output.put_line( x.username ); dbms_output.put_line( x.logon_time ' ' x.current_time ' last et = ' x.LAST_CALL_ET); dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;/

Table Locks and Remove Table locks

1. How do you know What are the objects have locks??

Solution:

select * from v$lock where block<>0;

if block=0 then no locks if block=1 then there is locks

2. How do you know locked tables? and how do you remove locks?

Solution:

1. select a.object_id, a.session_id, substr(b.object_name, 1, 40)from v$locked_object a,dba_objects bwhere a.object_id = b.object_idorder by b.object_name ;
2. select sid, serial#, command, taddr from v$session where sid=
3. alter system kill session 'sid,serial#';