Oracle Wait info

Oracle Wait information

w1.sql



prompt Report Current Wait Events.

set linesize 150
set verify off
set serveroutput on size 1000000
set feedback off

declare
cursor c_waits is
select w.sid
,decode (s.username,s.schemaname,s.username,s.username||'/'||s.schemaname) user_schema
,w.event
,w.p1, w.p1text, w.p1raw
,w.p2, w.p2text
,w.p3, w.p3text
-- ,w.wait_time
,w.seconds_in_wait
,decode (substr (w.state, 1, 7)
,'WAITING','Y'
,'WAITED ','N'
,w.state) as state
from v$session_wait w
,v$session s
where w.event <> 'SQL*Net message from client'
and w.event <> 'SQL*Net message to client'
and w.event <> 'rdbms ipc message'
and w.event <> 'smon timer'
and w.event <> 'pmon timer'
and w.sid = s.sid
order by w.p1text, w.p1, w.sid;
str varchar2(500);
s_desc varchar2(500);
n_db_files number default 1024;
begin
dbms_output.put_line ('Sid User Event W Scnds Details');
dbms_output.put_line ('--- ---------- ------------------------------- - ----- -----------------------------');
for r_wait in c_waits
loop
str := rpad (to_char (r_wait.sid),4);
str := str|| rpad (substr (r_wait.user_schema,1,10),11);
str := str|| rpad (substr (r_wait.event,1,30),31);
str := str|| ' '||r_wait.state;
str := str|| lpad (r_wait.seconds_in_wait,6);
str := str|| lpad (substr (r_wait.p1text,1,15),16)||': ';
str := str|| '('||r_wait.p1raw||')';
-- decode parameter 1 of wait event if we know how
if (r_wait.p1text = 'files') then
str := str||' Files:'||r_wait.p1||' Blks:'||r_wait.p2||' Reqs:'||r_wait.p3;
elsif (r_wait.p1text = 'file#')
or (r_wait.p1text = 'file number') then
begin
select to_number (value)
into n_db_files
from v$parameter
where name = 'db_files';
exception
when others then null;
end;
if r_wait.p1 > n_db_files then
begin /* temporary file */
select substr (file_name, instr (file_name,'/',-2) + 1)
into s_desc
from dba_temp_files
where to_char (file_id) = to_char(r_wait.p1 - n_db_files);
exception
when no_data_found then s_desc := to_char(null);
end;
else
begin /* data file */
select substr (file_name, instr (file_name,'/',-2) + 1)
into s_desc
from dba_data_files
where file_id = r_wait.p1;
exception
when no_data_found then s_desc := to_char(null);
end;
end if;
str := str||' '''||s_desc||'''';
elsif r_wait.p1text='name|mode' then
begin
select chr(bitand(r_wait.p1,-16777216)/16777215) ||
chr(bitand(r_wait.p1,16711680)/65535)||'|'||
bitand (r_wait.p1,65536)
into s_desc
from dual;
exception
when no_data_found then s_desc := to_char(null);
end;
str := str||' ('||s_desc||')';
end if;
-- decode parameter 2 of wait event if we know how
if (r_wait.event = 'latch free')
or (r_wait.event = 'latch activity') then
begin
select name
into s_desc
from v$latch
where latch# = r_wait.p2;
exception
when no_data_found then s_desc := to_char(null);
end;
str := str||' '||s_desc;
end if;
dbms_output.put_line (str);
end loop;
end;
/

set feedback 6







Sid User Event W Scnds Details
--- ---------- ------------------------------- - ----- -----------------------------
49 /SYS Space Manager: slave idle wait Y 1 Slave ID: (00)
28 /SYS Streams AQ: qmn slave idle wai Y 24 Type: (0000000000000001)
5 /SYS DIAG idle wait Y 0 component: (0000000000000005)
8 /SYS DIAG idle wait Y 1 component: (0000000000000005)
3 /SYS VKTM Logical Idle Wait Y713694: (00)
26 /SYS Streams AQ: qmn coordinator id Y 24: (00)
30 /SYS Streams AQ: waiting for time m Y 11350: (00)
51 /SYS VKRM Idle Y 42483: (00)

Some use full tool for Oracle DBA

anuj putty

Download link ...
putty winscp ExamDiff


Putty download

http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html



ExamDiff A freeware windows ASCII file comparison utility available

http://www.prestosoft.com/ps.asp?page=edp_examdiff



http://winscp.net/eng/download.php#download2

Portable executables (2.5 MiB; 175,857 downloads to date)

Oracle memory report

Oracle memory report
Oracle SGA report

@mem.sql


from Web


prompt
prompt 10g Memory Report
prompt

set linesize 120
set pagesize 100
set echo off
set trimspool on

rem create dated and instance named spool file
column file_name new_value spool_name
set heading off

select 'mem10g_'||to_char(sysdate,'mmdd')||'_'||name as file_name
from v$database;

set heading on

spool &spool_name..log

prompt memory report
prompt
column today format a30 heading "Todays Date"
select to_char(sysdate,'HH24:MI:SS ddth Monthfm YYYY') today
from sys.dual
/

col host_name for a20
col uptime for a20
col startup_time for a17
col version for a10
col instance_number for 9,990 heading 'Inst.'
col days for a5
col HRS for a4
col mins for a4

select instance_number
, instance_name
, decode
( instr (host_name,'.')
, 0 , host_name
, substr (host_name, 0, instr (host_name,'.')-1)
) as host_name
, version
, to_char(startup_time,'Dy dd-Mon hh24:mi') as startup_time
, to_char (floor (sysdate - startup_time), '000') as DAYS
, to_char (mod (floor ((sysdate - startup_time) * 24 ), 24), '00') as HRS
, to_char (mod (floor ((sysdate - startup_time) * (24*60)), 60), '00') as MINS
from v$instance
/

-- prompt SGA
-- show sga

col pname for a30 heading 'Parameter'
col pvalue for a20 heading 'Value'

select name as pname
, value as pvalue
, isdefault as def
, isses_modifiable as ses
, issys_modifiable as sys
, ismodified as mod
, isadjusted as adj
from v$parameter
where name in
('buffer_pool_keep'
,'buffer_pool_recycle,
,'db_block_size'
,'hash_area_size'
,'java_pool_size'
,'large_pool_size'
,'shared_pool_size'
,'sort_area_size'
)
or name like '%cache_size%'
order by name
-- useful for debug only /

set serveroutput on size 100000
set feedback off

declare

blk_size number;
anum number;
bnum number;
param_num number;
param_str varchar2(100);
param_name varchar2(50);

cursor c_sga is
select name, value from v$sga;

cursor c_free is
select pool, bytes from v$sgastat where name = 'free memory' and bytes > 0;

cursor c_cache is
select name from v$parameter where name like 'db_%cache_size' and value<>'0' order by 1;

function bytes_to_mb (p_num in number) return varchar2 is
s_str varchar2 (100);
begin
s_str := to_char (round (p_num/(1024*1024)), '999,999,990') || ' Mb';
return s_str;
exception
when others then return p_num;
end bytes_to_mb;

function bytes_to_kb (p_num in number) return varchar2 is
s_str varchar2 (100);
begin
s_str := to_char (round (p_num/1024), '999,999,990') || ' Kb';
return s_str;
exception
when others then return p_num;
end bytes_to_kb;

procedure get_val (p_name in varchar2) is
begin
select value, name
into param_str, param_name
from v$parameter
where upper(name) = upper(p_name);

begin
-- translate parameter string value to a number of bytes
if rtrim (upper (param_str),'K ') <> upper (param_str) then
param_num := 1024 * to_number (rtrim (upper (param_str),'K '));
elsif rtrim (upper (param_str),'M ') <> upper (param_str) then
param_num := 1024 * 1024 * to_number (rtrim (upper (param_str),'M '));
elsif rtrim (upper (param_str),'G ') <> upper (param_str) then
param_num := 1024 * 1024 * 1024 * to_number (rtrim (upper (param_str),'G '));
else
param_num := to_number (param_str);
end if;

-- translate number of bytes to a formated string value
if param_num < 2048 then
param_str := to_char (param_num, '999,999,999,990');
elsif param_num < (2048*1024) then
param_str := bytes_to_kb (param_num);
else
param_str := bytes_to_mb (param_num);
end if;
exception
when others then null;
end;
exception
when others then
param_str := 'Invalid Parameter';
param_num := NULL;
param_name := p_name;
end get_val;

procedure trace (p_indent in integer, p_name in varchar2, p_val in varchar2) is
s_str varchar2(100);
begin
s_str := rpad ('.',p_indent*3)||rpad (p_name,30)||'-'||lpad (p_val, 20);
dbms_output.put_line (s_str);
end trace;

procedure trace (p_indent in integer, p_name in varchar2) is
s_str varchar2(100);
begin
s_str := rpad ('.',p_indent*3)||rpad (p_name,30);
dbms_output.put_line (s_str);
end trace;

begin

get_val ('db_block_size');
blk_size := param_num;
trace (2,param_name, to_char (param_num,'999,990'));

trace (1,'SGA');
anum :=0;
for r_sga in c_sga
loop
trace (2,r_sga.name, to_char (r_sga.value, '999,999,999,990'));
anum := anum + r_sga.value;
end loop;
trace (2,'TOTAL SGA', bytes_to_mb (anum));
get_val ('sga_target');
trace (2, param_name, param_str);
get_val ('sga_max_size');
trace (2, param_name, param_str);
if param_num < (128*1024*1024)
then trace (2, 'Granule Size','4 Mb');
else trace (2, 'Granule Size','16 Mb');
end if;

trace (1,'SGA caches');
for r_cache in c_cache
loop
get_val (r_cache.name);
trace (2, param_name, param_str);
end loop;
get_val ('log_buffer');
trace (2, param_name, param_str);
get_val ('large_pool_size');
trace (2, param_name, param_str);
get_val ('shared_pool_size');
trace (2, param_name, param_str);
get_val ('java_pool_size');
trace (2, param_name, param_str);

trace (1, 'PGA');
get_val ('sort_area_size');
anum := param_num;
trace (2, param_name, param_str);
get_val ('hash_area_size');
bnum := param_num;
trace (2, param_name, param_str);
bnum := (anum + bnum) + (1024*1024);
trace (2, 'nominal PGA per session', bytes_to_kb (bnum));
get_val ('pga_aggregate_target');
trace (2, param_name, param_str);
get_val ('workarea_size_policy');
trace (2, param_name, param_str);

select max (value)
into anum
from v$sesstat s
, v$statname n
, v$session ses
where n.name = 'session pga memory max'
and n.statistic# = s.statistic#
and ses.sid = s.sid
and ses.type = 'USER';
trace (2, 'Peak PGA for one session', bytes_to_kb (anum));

select sessions_highwater
into anum
from v$license;
trace (2, 'peak number sessions', to_char (anum, '999,990'));

trace (2, 'nominal peak PGA demand', bytes_to_mb (anum*bnum));

trace (1, 'Free SGA Memory');
anum := 0 ;
for r_free in c_free
loop
anum := anum + r_free.bytes;
trace (2, r_free.pool, to_char(r_free.bytes,'9,999,999,990'));
end loop;
trace (2, 'TOTAL FREE SGA MEMORY',to_char (anum, '9,999,999,990'));

end;
/

col memory for a7 heading 'Memory'
col name for a35 heading 'Memory Area/Usage'
col ratio for 990.000 heading 'Efficiency%'
col mb for a15 heading 'Size (Mb)'
select 'SGA' as memory
, name||' '||block_size as name
, 100 * (1 - (physical_reads/(db_block_gets+consistent_gets))) as ratio
from v$buffer_pool_statistics
where ( consistent_gets + db_block_gets ) !=0
union
select 'SGA', 'Library Cache'
, 100 - (sum(reloads)/sum(pins) *100)
from v$librarycache
union
select 'SGA', 'Dictionary Cache'
, 100 - (sum(getmisses)/sum(gets) * 100)
from v$rowcache
union
select 'PGA', 'Sorting'
, (mem/(disk+mem))*100
from
( select value as disk from v$statname a , v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name = 'sorts (disk)'),
( select value as mem from v$statname a , v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name = 'sorts (memory)'
)
order by 1,2
/
select 'PGA' as memory, name
, to_char (value/(1024*1024), '999,990.0')||' Mb' as mb
from v$pgastat
where name like '%PGA allocated'
or name like '%auto workareas'
order by memory, value desc, name
/

prompt PGA_TARGET_ADVICE
col PGA_TARGET_FOR_ESTIMATE for 999,999,999,990 heading 'PGA_TARGET'
col PGA_TARGET_FACTOR for 99.0 heading 'Factor'
col BYTES_PROCESSED for 999,999,999,999,990 heading 'Bytes Processed'
col ESTD_EXTRA_BYTES_RW for 999,999,999,999,990 heading 'Est. Bytes|r/w'
col ESTD_PGA_CACHE_HIT_PERCENTAGE for 9,990 heading 'Est. PGA|hit rate'
col ESTD_OVERALLOC_COUNT for 999,990 heading 'Est.|overalloc'
select PGA_TARGET_FOR_ESTIMATE
, PGA_TARGET_FACTOR
, BYTES_PROCESSED
, ESTD_EXTRA_BYTES_RW
, ESTD_PGA_CACHE_HIT_PERCENTAGE
, ESTD_OVERALLOC_COUNT
from v$pga_target_advice
order by PGA_TARGET_FACTOR
/

col name for a20 heading 'Buffer Cache'
col size_for_estimate for 999,990 heading 'Size (Mb)'
col size_factor for 90.000 heading 'Size|Factor'
col estd_physical_read_factor for 90.000 heading 'Est. rd|Factor'
col estd_physical_reads for 999,999,990 heading 'Est. physical|Reads'
select name||' '||block_size as name
, size_for_estimate
, size_factor
, decode (size_factor, 1, '*', ' ') as c
, estd_physical_read_factor
, estd_physical_reads
from v$db_cache_advice
order by name, block_size, size_factor
/

-- @@dba_pga_usage

prompt
spool off

set feedback 6

Oracle Health Check report

check1.sql

Anuj_1022_ORCL.lst <<<<<--- output will be in this file

===
prompt
prompt Report Health Check.
prompt

set trimspool on
set timing off
set linesize 150
set pause off
set verify off
set echo off
set pagesize 40
clear breaks
clear computes
ttitle off
whenever sqlerror continue;
whenever oserror continue;

rem create dated and instance named spool file
column file_name new_value spool_name
set heading off

select 'Anuj_'||to_char(sysdate,'mmdd')||'_'||name as file_name from v$database;

set heading on

spool &spool_name

prompt *****************************
prompt * H E A L T H C H E C K *
prompt *****************************
prompt
column today format a30 heading "Todays Date"
select to_char(sysdate,'HH24:MI:SS ddth Monthfm YYYY') today
from sys.dual
/

@heading.sql

col host_name for a20
col uptime for a20
col startup_time for a17
col version for a10
col instance_number for 9,990 heading 'Inst.'
col days for a5
col HRS for a4
col mins for a4

select instance_number
, instance_name
, host_name
, version
, to_char(startup_time,'Dy dd-Mon hh24:mi') as startup_time
, to_char (floor (sysdate - startup_time), '000') as DAYS
, to_char (mod (floor ((sysdate - startup_time) * 24 ), 24), '00') as HRS
, to_char (mod (floor ((sysdate - startup_time) * (24*60)), 60), '00') as MINS
from v$instance
/

prompt
prompt Files needing recovery.
select * from v$recover_file
/

prompt
prompt Files in backup mode.
Select * from v$backup where status!='NOT ACTIVE'
/

prompt
prompt Unresolved distributed transactions
Select * from dba_2pc_pending
/

prompt
prompt Top 10 wait events from v$system_event
prompt (average_wait is in 100ths of a second)
col event for A30
col average_wait for 999,990.00 heading 'Avg Wait|(s)'
col time_waited for 999,999,990 heading 'Total Wait|(mins)'
col total_waits for 999,999,990 heading 'Number|Waits'
col total_timeouts for 999,999,990 heading 'Number|Timeouts'
col pos for 990

select rownum as Pos
, event
, total_timeouts
, total_waits
, time_waited/(100*60) as time_waited
, average_wait/100 as average_wait
from
( select event
, total_timeouts
, total_waits
, time_waited
, average_wait
from v$system_event e
where e.event not in
('smon timer'
,'pmon timer'
,'rdbms ipc message'
,'Null event'
,'parallel query dequeue'
,'pipe get'
,'client message'
,'SQL*Net message to client'
,'SQL*Net message from client'
,'SQL*Net more data from client'
,'dispatcher timer'
,'virtual circuit status'
,'lock manager wait for remote message'
,'PX Idle Wait'
,'jobq slave wait'
,'PX Deq: Execution Msg'
,'wakeup time manager')
order by time_waited desc, total_waits, event
)
where rownum < 11
/

prompt
prompt CPU usage (Minutes)
prompt THIS NEEDS A BIT OF TESTING
prompt db uptime = how long database has been up
prompt total waits = sum of non-idle non-CPU wait event times
prompt total cpu = amount of CPU used so far
prompt If CPU exceeds other waits then box has CPU contention
col cpu_mins for 999,999,990 heading 'Total CPU'
col uptime_mins for 999,999,990 heading 'DB UpTime'
col wait_mins for 999,999,990 heading 'Total Waits'
select (sysdate - i.startup_time)*(24*60) as uptime_mins
, (to_number (b.value)/100) as cpu_mins
, wait_mins
from v$statname a
, v$sysstat b
, v$instance i
,( select sum (e.time_waited)/100 as wait_mins
from v$system_event e
where e.event not in
('smon timer'
,'pmon timer'
,'rdbms ipc message'
,'Null event'
,'parallel query dequeue'
,'pipe get'
,'client message'
,'SQL*Net message to client'
,'SQL*Net message from client'
,'SQL*Net more data from client'
,'dispatcher timer'
,'virtual circuit status'
,'lock manager wait for remote message'
,'PX Idle Wait'
,'jobq slave wait'
,'PX Deq: Execution Msg'
,'wakeup time manager')
)
where a.statistic# = b.statistic#
and a.name = 'CPU used when call started'
/

prompt
prompt v$waitstat
rem needs timed statistics enabled
set numwidth 15
col count for 999,999,990
col time_secs for 999,990 heading 'Time (s)'
col suggestion for a50
select class, count , time/100 as time_secs
, decode (upper (class)
,'DATA BLOCK', 'eliminate hot blocks (reduce rows per block)'
,'SEGMENT HEADER', 'more freelists/groups'
,'FREELIST BLOCKS', 'more freelists'
,'SYSTEM UNDO HEADER', 'more system rollback extents'
,'SYSTEM UNDO BLOCK', 'bigger system rollback extents'
,'UNDO HEADER', 'more rollback extents'
,'UNDO BLOCK', 'bigger rollback extents'
,to_char(null)) as suggestion
from v$waitstat
where count > 0
order by 1
/
set numwidth 9

prompt
prompt parallel query system statistics
prompt The following figures are only a snapshot but.....
prompt if 'Servers in use' is normally > min_servers then increase min_servers.
prompt if 'Servers in use' is normally < max_servers then decrease max_servers.
column pq_value for 999,999,990 heading 'Value'
select value as pq_value, statistic
from v$px_process_sysstat
where upper(statistic) like '%SERVER%'
union
select to_number(value), name
from v$parameter
where name like ('parallel_%_servers')
order by 2
/



select decode(totalq, 0, 'No Requests'
,round (WAIT/TOTALQ,6) || ' HUNDREDTHS OF SECONDS')
as "AVERAGE WAIT TIME PER REQUESTS"
from v$queue
where type = 'COMMON'
/
prompt 8i
select conf_indx as "INDEX"
,decode (sum (totalq), 0 , 'No Responses'
, round(sum(wait)/sum(totalq),6)||' 100ths of seconds')
as "AVERAGE WAIT TIME PER RESPONSE"
from V$QUEUE Q, V$DISPATCHER d
where q.type = 'DISPATCHER'
and q.paddr = d.paddr
group by conf_indx
/
prompt 7.3.4
select name as dispatcher
,decode (sum (totalq), 0 , 'No Responses'
, round(sum(wait)/sum(totalq),6)||' 100ths of seconds')
as "AVERAGE WAIT TIME PER RESPONSE"
from V$QUEUE Q, V$DISPATCHER d
where q.type = 'DISPATCHER'
and q.paddr = d.paddr
group by name
/
select count (*) as "Shared Server Processes" from v$shared_server
where status != 'QUIT'
/

prompt
prompt LOG BUFFER
col p_name for a30 heading Parameter
col p_value for a20 heading Value
select name as p_name, value as p_value
from v$parameter
where name in ('log_buffer','archive_lag_target')
/
prompt Increase the log_buffer parameter if the ratio is over 5,000
prompt This value should be 512K at least anyway.
col requests for 999,999,990
col entries for 999,999,990
col log_ratio for 999,990
select requests, entries, entries/decode(requests,0,entries,requests) as log_ratio
from
(select nvl(value,1) as requests
from v$sysstat
where name = 'redo log space requests'
),
(select nvl(value,1) as entries
from v$sysstat
where name = 'redo entries'
)
/
prompt archived redo log generation
col aredo_age for 9,990 heading 'No. Days'
col aredo_files for 999,990 heading 'No. Logs'
col aredo_avg for 999,990 heading 'Avg. per day'
col aredo_peak for 999,990 heading 'Peak Daily'
select age as aredo_age
, logfiles as aredo_files
, avg_per_day as aredo_avg
, max_per_day as aredo_peak
from
( select max (per_day) as max_per_day from
( select count (*) as per_day
from v$loghist
group by trunc (first_time)
)
),
( select ceil (sysdate - min(first_time)) as age
, count (*) as logfiles
, count (*) / ceil (sysdate - min(first_time)) as avg_per_day
from v$loghist
)
/
col log_bytes for 999,999,990 heading 'Bytes'
col members for 990 heading 'Members'
col group# for 990
col thread# for 990
col sequence for 999,990
select GROUP#, THREAD#, SEQUENCE#, BYTES as log_bytes
, MEMBERS, ARCHIVED, STATUS, FIRST_CHANGE#
, to_char (FIRST_TIME, 'dd-Mon-yy hh24:mi') as first_time
from v$log
order by group#
/

col memory for a7 heading 'Memory'
col name for a35 heading 'Memory Area/Usage'
col ratio for 990.000 heading 'Efficiency%'
col mb for a15 heading 'Size (Mb)'
select 'SGA' as memory
, name||' '||block_size as name
, 100 * (1 - (physical_reads/(db_block_gets+consistent_gets))) as ratio
from v$buffer_pool_statistics
union
select 'SGA', 'Library Cache'
, 100 - (sum(reloads)/sum(pins) *100)
from v$librarycache
union
select 'SGA', 'Dictionary Cache'
, 100 - (sum(getmisses)/sum(gets) * 100)
from v$rowcache
order by 1,2
/

prompt
prompt latch contention shown by ratio(s) greater then 1%
column lname format a35 heading 'Latch Type'
column gets format 999,999,999,999 heading 'Gets'
column miss format 999,999,999 heading 'Misses'
column igets format 999,999,999,999 heading 'Im. Gets'
column imiss format 999,999,999 heading 'Im. Misses'
column ratio format 90.00
column iratio format 90.00
column sleeps format 999,990
column hwaits format 999,999,990 heading 'waits|when|holding'
select n.name as lname
, gets as gets
, misses as miss
, (misses/decode(gets,0,1,gets))*100 as ratio
, immediate_gets as igets
, immediate_misses as imiss
, (immediate_misses/decode (immediate_gets,0,1,immediate_gets))*100 as iratio
, sleeps
, waits_holding_latch as waits
from v$latchname n , v$latch l
where n.latch# = l.latch#
and ( ((misses/decode(gets,0,1,gets))*100 > 1)
or ((immediate_misses/decode (immediate_gets,0,1,immediate_gets))*100 >1))
order by upper(n.name)
/

prompt
prompt Checking sort_area_size values
prompt To make best use of sort memory the initial extent of your
prompt Users sort-work Tablespace should be sufficient to hold at
prompt least one sort run from memory to reduce dynamic space allocation.
prompt See also dba_mem8i.sql and dba_mem9i.sql
column init_value format A40 heading 'Initialization Parameters'
select rpad (name,27)||value as init_value
from v$parameter
where name in
('sort_area_size','workarea_size_policy','sort_area_retained_size'
,'pga_aggregate_target')
order by 1
/
column sort_name format a20
column sort_value format 999,999,999,999 heading 'Value'
select a.name as sort_name, value as sort_value
from v$statname a , v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)','sorts (memory)','sorts (rows)')
/
column max_space format a8 heading 'Tablespace|Name'
column max_tot_mb format 999,990 heading 'Max Total|Mbytes'
column max_used_mb format 999,990 heading 'Max Single|Use Mbytes'
column max_sort_mb format 999,990 heading 'Max Single|Sort Mbytes'
column max_file_mb format 999,990 heading 'Tablespace|Size (Mb)'
column ftype heading 'File|Type'
column extent_size format 99,999,999 heading 'Ext. Size|(Bytes)'

-- get the database blk size
column blk_size new_value _blk_size
set termout off
select to_number (value) as blk_size
from v$parameter
where upper(name) = 'DB_BLOCK_SIZE';
set termout on

prompt sort segments:
select s.tablespace_Name as max_space
, ftype as ftype
, (s.extent_size*&_blk_size) as extent_size
, (file_bytes)/(1024*1024) as max_file_mb
, (s.max_blocks*&_blk_size)/(1024*1024) as max_tot_mb
, (s.max_used_blocks*&_blk_size)/(1024*1024) as max_used_mb
, (s.max_sort_blocks*&_blk_size)/(1024*1024) as max_sort_mb
from v$sort_segment s
,(select tablespace_name
, sum (bytes) as file_bytes
, 'Data' as ftype
from dba_data_files
group by tablespace_name
union
select tablespace_name
, sum (bytes) as file_bytes
, 'Temp' as ftype
from dba_temp_files
group by tablespace_name
) f
where f.tablespace_name = s.tablespace_name
order by 1
/

prompt
prompt Rollback Statistics
prompt if (waits/gets)>.01 then contention for rollback segs
prompt if high wraps then use bigger extents
prompt if high shrinks then increase optimal size
column tablespace_name format A10
column extents format 9990 heading 'Ext.'
column instance_num format a5 heading 'Inst.'
column status format a10
column waits format 99,990
column xacts format 99,990
column shrinks format 9,999,990
column mb format 9,990.00 heading 'Size Mb'
column hwm format 9,990.00 heading 'HWM Mb'
column osize format 9,990.00 heading 'Optimum'
column wraps format 9,990
column gets format 999,999,990
column rb_seg format a11
select rs.tablespace_name,
rs.instance_num,
substr(rs.segment_name,1,10) rb_seg,
rs.status,
v.extents,
(rs.initial_extent*v.extents)/(1024*1024) mb,
v.optsize/(1024*1024) osize,
v.hwmsize/(1024*1024) hwm,
v.xacts,
v.shrinks,
v.wraps,
v.waits,
v.gets
from v$rollstat v,
sys.dba_rollback_segs rs
where v.usn (+) = rs.segment_id
order by 1,2,3
/

prompt
prompt segments that cannot extend into biggest free extent
col owner for a10 heading 'Owner'
col space1 for a20 heading 'Tablespace'
col type for A10 heading 'Type'
col name for a30 heading 'Name'
col next_kb for 999,999,990 heading 'Next Kb'
col free_kb for 999,999,990 heading 'Max Free Kb'
select owner, space1 as tablespace, type, name, next_kb, nvl(free_kb,0) as free_kb
from
(select owner, tablespace_name as space1
,'TABLE' as type
,table_name as name
,round (next_extent/(1024),2) as next_kb
from sys.dba_tables
union
select table_owner, tablespace_name
,'TABPART' as type
,table_name||'.'||partition_name
,round (next_extent/(1024),2)
from sys.dba_tab_partitions
union
select owner, tablespace_name, 'INDEX', index_name, round (next_extent/(1024),2)
from sys.dba_indexes
union
select index_owner, tablespace_name, 'INDPART'
, index_name||'.'||partition_name
, round (next_extent/(1024),2)
from sys.dba_ind_partitions
union
select owner, tablespace_name, 'CLUSTER', cluster_name, round (next_extent/(1024),2)
from sys.dba_clusters
union
select owner, tablespace_name, 'ROLLBACK', segment_name, round (next_extent/(1024),2)
from sys.dba_rollback_segs
),
(select tablespace_name as space2, round (max(bytes)/(1024),2) as free_kb
from sys.dba_free_space
group by tablespace_name
)
where space1 = space2(+)
and next_kb >= nvl(free_kb,0)
order by 1,2,3,4
/

prompt
prompt segments that have reached the maximum number of extents
select owner
, segment_type
, segment_name
, extents as cur_ext
, max_extents as max_ext
from dba_segments
where extents >= max_extents
and max_extents > 0
order by 1,2,3
/

prompt
prompt Tablespaces with less than 10% free space
prompt
col space1 format A30 heading 'Tablespace'
col free_mb format 999,999,990 heading 'Free Mb'
col tot_mb format 999,999,990 heading 'Total Mb'
col pct_free format 990.00 heading '% Free'
col max_ext format 999,999,990 heading 'Max|(Next Ext.)'
col sum_ext format 999,999,990 heading 'Sum|(Next Ext.)'
col cnt_ext format 999,999,990 heading 'Count|(Next Ext.)'
select /*+ RULE */ space1
, tot_mb
, (free_mb/tot_mb)*100 as pct_free
, free_mb
, sum (sum_ext) as sum_ext
, max (max_ext) as max_ext
, sum (cnt_ext) as cnt_ext
from
(select tablespace_name as space1, sum (Bytes)/(1024*1024) as tot_mb
from dba_data_files
group by tablespace_name
),
(select tablespace_name as space2, sum(bytes)/(1024*1024) as free_mb
from dba_free_space
group by tablespace_name
),
(select tablespace_name as space3
,sum (next_extent)/(1024*1024) as sum_ext
,max (next_extent)/(1024*1024) as max_ext
,count (*) as cnt_ext
from dba_tables
where next_extent is not null
group by tablespace_name
union
select tablespace_name
,sum (next_extent)/(1024*1024) as sum_ext
,max (next_extent)/(1024*1024) as max_ext
,count (*) as cnt_ext
from dba_tab_partitions
where next_extent is not null
group by tablespace_name
union
select tablespace_name
,sum (next_extent)/(1024*1024) as sum_ext
,max (next_extent)/(1024*1024) as max_ext
,count (*) as cnt_ext
from dba_indexes
where next_extent is not null
group by tablespace_name
union
select tablespace_name
,sum (next_extent)/(1024*1024) as sum_ext
,max (next_extent)/(1024*1024) as max_ext
,count (*) as cnt_ext
from dba_ind_partitions
where next_extent is not null
group by tablespace_name
)
where space1 = space2
and space1 = space3
and (free_mb/tot_mb) < 0.1
group by space1, tot_mb, free_mb
order by 1,2
/


prompt
prompt free space that could be coalesced:
prompt
set serveroutput on size 100000
declare
cursor c_ts is
select tablespace_name as name
from dba_tablespaces
where status = 'ONLINE'
and contents = 'PERMANENT'
and extent_management = 'DICTIONARY'
order by tablespace_name;
cursor c_segs (tsname in varchar2) is
select tablespace_name
,file_id
,block_Id
,blocks
from dba_free_space
where tablespace_name = tsname
order by file_id, block_id;
prev_end number default 0;
ts_count number default 0;
segs_count number;
segs_bytes number;
block_size number;
begin
select to_number (value)
into block_size
from v$parameter
where upper(name) = 'DB_BLOCK_SIZE';
<>
for l_ts in c_ts
loop
prev_end := 0;
segs_count := 0;
segs_bytes := 0;
ts_count := ts_count + 1;
<>
for r_seg in c_segs (l_ts.name)
loop
if r_seg.block_id=prev_end then
segs_count := segs_count + 1;
segs_bytes := segs_bytes + r_seg.blocks*block_size;
end if;
prev_end := r_seg.block_id + r_seg.blocks;
end loop segment;
if segs_count > 0 then
dbms_output.put_line ('TABLESPACE '||l_ts.name||': '||segs_count||' segments, totalling '||round (segs_bytes/(1024*1024))||' Mb');
end if;
end loop tablespace;
dbms_output.put_line (ts_count||' tablespaces analysed');
end;
/
set serveroutput off
set feedback 6

prompt
prompt indexes with blevel > 3
col owner_table_index for a50
select owner||'.'||table_name||'.'||index_name as owner_table_index
, blevel, round (num_rows) as num_rows, last_analyzed
from dba_indexes
where blevel > 2
order by owner, table_name, index_name
/

prompt
prompt The following indexes are unusable
select index_owner, index_name, partition_name, status
from dba_ind_partitions
where status = 'UNUSABLE'
union
select owner, index_name, to_char(null), status
from dba_indexes
where status = 'UNUSABLE'
order by 1,2,3
/

prompt
prompt The following tables may benefit from rebuilding due to the high percentage
prompt of blocks that are in the freelist(s).
col percentage for 90.00
col owner for a20
col table_name for a30
col blocks for 999,990
col num_rows for 999,999,990
select owner
,table_name
,blocks
,NUM_FREELIST_BLOCKS
,round(nvl(num_freelist_blocks,1)/nvl(blocks+1,1)*100,2) as percentage
,num_rows
,chain_cnt
from all_tables
where ( ((nvl(num_freelist_blocks,1)/nvl(blocks+1,1)) > 0.1)
or (num_freelist_blocks > 20 ) )
and nvl(blocks,1) > 128
order by num_freelist_blocks desc
/

-- chained and migrated rows
prompt Chained rows are too large for one block to contain.
prompt Migrated rows have been moved to another block because an update made them too
prompt large to stay in the current block. Too many migrated rows may suggest a table
prompt rebuild is required but this statistic is mixed in with chained rows.
prompt Migrated rows leave a forwarding address thus increasing access IO.
col num_rows format 999,999,990
col chain_cnt format 999,999,990
select owner, table_name, num_rows, chain_cnt, avg_row_len
from dba_tables
where nvl(chain_cnt,0) > 0
order by chain_cnt desc
/

prompt
prompt <=8i SYS/SYSTEM schemas should have 0 objects with stats
select a.owner
, a.tables
, b.indexes
from
( select owner
, sum (decode (last_analyzed, null, 0, 1)) as tables
from dba_tables
where owner in ('SYS', 'SYSTEM')
group by owner
) a,
( select owner
, sum (decode (last_analyzed, null, 0, 1)) as indexes
from dba_indexes
where owner in ('SYS', 'SYSTEM')
group by owner
) b
where a.owner = b.owner
order by 1,2
/

prompt
prompt users using system tablespace (be careful!)
select username, default_tablespace, temporary_tablespace
from dba_users
where default_tablespace = 'SYSTEM'
or temporary_tablespace = 'SYSTEM'
order by 1
/

prompt
prompt Invalid objects
select owner, count (*)
from dba_objects
where status = 'INVALID'
group by owner
order by 1
/

prompt
prompt Anuj HEALTH CHECK report

spool off

clear breaks
clear computes
clear columns

Oracle heading.sql for Report

heading.sql
@heading.sql
Oracle database useful info
Oracle report heading


set heading off
set linesize 175
select 'Server Detail:- '||SYS_CONTEXT('USERENV','SERVER_HOST')||'.'||SYS_CONTEXT( 'USERENV', 'DB_DOMAIN' )||':'||UTL_INADDR.get_host_address||' Inst Name and Id->'||SYS_CONTEXT( 'USERENV', 'INSTANCE_NAME' )||'='||SYS_CONTEXT( 'USERENV', 'INSTANCE' )||', Db Name= '||SYS_CONTEXT( 'USERENV', 'DB_NAME' )||' OS:- '||dbms_utility.port_string ||' Uptime:-' || floor(sysdate - startup_time) || 'days' ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || 'hours' ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||'minutes' ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||'seconds' Db_Detail from
sys.v_$instance where rownum=1;

col NAME format a30
col VALUE format a50
select NAME,VALUE from v$parameter
where NAME like 'log_archive_dest_%'
and VALUE is not null
and VALUE!='enable'
union
select NAME,VALUE from v$parameter
where NAME in ('cluster_database','cluster_database_instances','db_recovery_file_dest');

select 'FlashBack '||FLASHBACK_ON FROM v$database;

set heading on

Oracle RAC on Archive Log Mode

RAC in archive log mode
Enable Archive Log Mode in Oracle RAC 10g Database
Oracle RAC on Archive Log Mode



1- Login to one of the clustered database nodes and change parameter cluster_database to false:


sqlplus / as sysdba

alter system set cluster_database=false scope=spfile sid='rac1'


2- Stop all database instances:

srvctl stop database -d racdb


3- startup mount the instance and enable archivelog mode:

sqlplus / as sysdba

startup mount;

alter database archivelog;


4- Shutdown the previous running Instance and set cluster_database

sqlplus / as sysdba

alter system set cluster_database=true scope=spfile sid='rac1' ; -----<<<<<<<--- Imp

shutdown immediate;



5- Start all instance back:

srvctl start database -d racdb

To check the mode:

sqlplus / as sysdba

archive log list;

or

select log_mode from v$database;