Search This Blog

Total Pageviews

Friday 25 February 2011

Oracle text index re create script

spool index_scripts.lst
set long 2000000000
set head off
set pagesize 10000

select ctx_report.create_index_script ('username.text_index_name') from dual;

select ctx_report.describe_index('username.text_index_name') FROM DUAL;

spool off

netstat linux

apt-lnxtst-01:/etc/init.d # netstat -planet
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State User Inode PID/Program name
tcp 0 0 0.0.0.0:8192 0.0.0.0:* LISTEN 0 9282 -
tcp 0 0 0.0.0.0:8193 0.0.0.0:* LISTEN 0 9271 -
tcp 0 0 0.0.0.0:8194 0.0.0.0:* LISTEN 0 9272 -
tcp 0 0 0.0.0.0:38115 0.0.0.0:* LISTEN 0 9574 -
tcp 0 0 1.1.1.1:389 0.0.0.0:* LISTEN 0 9421 -
tcp 0 0 192.168.2.232:389 0.0.0.0:* LISTEN 0 9420 -
tcp 0 0 127.0.0.1:10024 0.0.0.0:* LISTEN 7005 26955 -
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 60 93548 -




apt-lnxtst-01:/etc/init.d # netstat -t –s
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 apt-lnxtst-01.apt:38115 apt-lnxtst-01.apt:11927 ESTABLISHED
tcp 0 0 apt-lnxtst-01.aptu:8194 192.168.2.106:4791 ESTABLISHED
tcp 0 0 apt-lnxtst-01.apt:40562 192.168.2.106:8194 ESTABLISHED
tcp 0 0 apt-lnxtst-01.aptu:8194 apt-lnxtst-01.apt:63080 ESTABLISHED
tcp 0 0 apt-lnxtst-01.apt:11927 apt-lnxtst-01.apt:38115 ESTABLISHED
tcp 0 0 apt-lnxtst-01.apt:63080 apt-lnxtst-01.aptu:8194 ESTABLISHED
tcp 0 0 apt-lnxtst-01.tus:ssh apt-amd-02.tus.:52499 ESTABLISHED
tcp 0 0 apt-lnxtst-01.tus:ssh amd64-05.tus.co:58674 ESTABLISHED



netstat -nat | awk '{ print $5}' | cut -d: -f1 | sed -e '/^$/d' | uniq


apt-lnxtst-01:/etc/init.d # netstat -anp |grep 'tcp\|udp' | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n
1 192.168.2.253
2 192.168.2.106
2 192.168.2.251
4 192.168.2.232
5 192.168.2.240
14
31 0.0.0.0



apt-lnxtst-01:/etc/init.d # netstat -anp | grep 80
tcp 0 0 127.0.0.1:8081 0.0.0.0:* LISTEN -
tcp 0 0 192.168.2.232:8194 192.168.2.232:63080 ESTABLISHED -
tcp 0 0 192.168.2.232:63080 192.168.2.232:8194 ESTABLISHED -
tcp 0 0 :::7780 :::* LISTEN -
tcp 0 0 :::80 :::* LISTEN -
tcp 1 0 192.168.2.232:143 192.168.2.240:58038 CLOSE_WAIT -
unix 2 [ ACC ] STREAM LISTENING 8041 - /var/run/nscd/socket
unix 3 [ ] STREAM CONNECTED 188180 -
unix 3 [ ] STREAM CONNECTED 9280 -
unix 2 [ ] DGRAM 8055 -
unix 2 [ ] DGRAM 8040 -
unix 2 [ ] DGRAM 8039 -
unix 3 [ ] STREAM CONNECTED 6806 -



-a, --all, --listening display all sockets (default: connected)
-n, --numeric don't resolve names
-p, --programs display PID/Program name for sockets

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Mysql database error

apt-lnxtst-01:/etc/init.d # mysql start
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)


check any other mysql database is running on port 3306 or not ?????????

(3306 is the default port for mysql)


lnxtst:/etc/init.d # netstat -planet|grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 60 93548 -



lnxtst:/etc/init.d # su - mysql
mysql@apt-lnxtst-01:~> /etc/init.d/mysql start
Starting service MySQL

Thursday 24 February 2011

How many Times a query executed ?

select s.begin_interval_time, sql.sql_id as sql_id, sql.executions_delta as exe_delta, sql.EXECUTIONS_TOTAL
from dba_hist_sqlstat sql, dba_hist_snapshot s
where sql_id='cvhk2j2gymzhd'
and s.snap_id = SQL.snap_id
and s.begin_interval_time> TO_date('18-feb-2011 1:00', 'dd-mon-yyyy hh24:mi')
and s.begin_interval_time< TO_date('18-feb-2011 2:30', 'dd-mon-yyyy hh24:mi') order by s.begin_interval_time;

Oracle 10g Top_5sql

Oracle top 5sql
Oracle top sql



SQL> select sysdate from dual;

SYSDATE
---------
24-FEB-11



exec top_stmt5('21-feb-11','2');


for two days



from web

http://www.evdbt.com/top_stmt5_10g.sql



set echo on feedback on timing on

spool top_stmt5_10g

create or replace procedure top_stmt5
(
in_start_date in date,
in_nbr_days in number,
in_top_count in integer default 10,
in_instance_nbr in number DEFAULT NULL,
in_max_disk_reads in integer default 10000,
in_max_buffer_gets in integer default 100000
) is
--
cursor get_top_stmts(in_dr in integer, in_bg in integer,
in_dbid in integer, in_inst_nbr in integer,
in_begin_time in timestamp, in_end_time in timestamp)
is
select sq.sql_id,
sq.module,
st.command_type,
sum(sq.disk_reads_delta) disk_reads,
sum(sq.buffer_gets_delta) buffer_gets,
sum(sq.cpu_time_delta)/1000000 cpu_time,
sum(sq.elapsed_time_delta)/1000000 elapsed_time,
sum(sq.executions_delta) executions,
(1 - (sum(sq.disk_reads_delta) / sum(sq.buffer_gets_delta)))*100 bchr,
sum(sq.disk_reads_delta) / sum(sq.executions_delta) dr_per_exe,
sum(sq.buffer_gets_delta) / sum(sq.executions_delta) bg_per_exe,
sum(sq.cpu_time_delta)/1000000 / sum(sq.executions_delta) cpu_per_exe,
sum(sq.elapsed_time_delta)/1000000 / sum(sq.executions_delta) ela_per_exe,
((sum(sq.disk_reads_delta)*100)+sum(sq.buffer_gets_delta))/100 factor
from dba_hist_sqltext st,
dba_hist_sqlstat sq,
dba_hist_snapshot ss
where ss.dbid = in_dbid
and ss.instance_number = nvl(in_inst_nbr, ss.instance_number)
and ss.begin_interval_time between in_begin_time and in_end_time
and sq.dbid = ss.dbid
and sq.instance_number = ss.instance_number
and sq.snap_id = ss.snap_id
and st.sql_id = sq.sql_id
group by sq.sql_id,
sq.module,
st.command_type
having (sum(sq.disk_reads_delta) > in_dr
or sum(sq.buffer_gets_delta) > in_bg)
and sum(sq.buffer_gets_delta) > 0
and sum(sq.executions_delta) > 0
order by factor desc;
--
cursor get_sql_plan(in_dbid in number,
in_sql_id in varchar2,
in_begin_time in timestamp,
in_end_time in timestamp)
is
select distinct plan_hash_value, timestamp
from dba_hist_sql_plan
where dbid = in_dbid
and sql_id = in_sql_id
and timestamp between in_begin_time and in_end_time
order by 2;
--
v_text_lines integer;
v_sql_text clob;
v_sql_text_len integer;
n integer;
v_tot_logr integer;
v_tot_phyr integer;
v_sql_tot_cnt integer := 0;
v_sql_tot_dr integer := 0;
v_sql_tot_bg integer := 0;
v_sql_tot_cpu integer := 0;
v_sql_tot_ela integer := 0;
v_plsql_tot_cnt integer := 0;
v_plsql_tot_dr integer := 0;
v_plsql_tot_bg integer := 0;
v_plsql_tot_cpu integer := 0;
v_plsql_tot_ela integer := 0;
v_dbid integer;
v_instance_nbr integer;
v_begin_snapshot timestamp;
v_end_snapshot timestamp;
v_nbr_snapshots integer;
v_nbr_instances integer;
v_plan_id integer;
--
v_errcontext varchar2(100);
v_errmsg varchar2(512);
v_save_module varchar2(48);
v_save_action varchar2(32);
--
begin
--
dbms_application_info.read_module(v_save_module, v_save_action);
v_errcontext := 'query dba_hist_database_instance';
dbms_application_info.set_module('TOP_STMT5', v_errcontext);
select h.dbid, decode(in_instance_nbr, null, null, i.instance_number), count(distinct i.instance_number)
into v_dbid, v_instance_nbr, v_nbr_instances
from dba_hist_database_instance h,
gv$instance i
where h.instance_number = i.instance_number
and i.instance_number = nvl(in_instance_nbr, i.instance_number)
group by h.dbid, decode(in_instance_nbr, null, null, i.instance_number);
--
v_errcontext := 'query dba_hist_snapshot';
dbms_application_info.set_action(v_errcontext);
select min(begin_interval_time),
max(end_interval_time),
count(*)
into v_begin_snapshot,
v_end_snapshot,
v_nbr_snapshots
from dba_hist_snapshot
where begin_interval_time between in_start_date and (in_start_date + in_nbr_days)
and dbid = v_dbid
and instance_number = nvl(v_instance_nbr, instance_number);
--
v_errcontext := 'query dba_hist_sysstat';
dbms_application_info.set_action(v_errcontext);
select sum(cg.value_delta+dbg.value_delta),
sum(p.value_delta)
into v_tot_logr,
v_tot_phyr
from (select dbid, instance_number, snap_id,
decode(greatest(value, lag(value,1,0) over (partition by dbid, instance_number order by snap_id)),
value, value - lag(value,1,0) over (partition by dbid, instance_number order by snap_id),
value) value_delta
from dba_hist_sysstat
where stat_name = 'consistent gets') cg,
(select dbid, instance_number, snap_id,
decode(greatest(value, lag(value,1,0) over (partition by dbid, instance_number order by snap_id)),
value, value - lag(value,1,0) over (partition by dbid, instance_number order by snap_id),
value) value_delta
from dba_hist_sysstat
where stat_name = 'db block gets') dbg,
(select dbid, instance_number, snap_id,
decode(greatest(value, lag(value,1,0) over (partition by dbid, instance_number order by snap_id)),
value, value - lag(value,1,0) over (partition by dbid, instance_number order by snap_id),
value) value_delta
from dba_hist_sysstat
where stat_name = 'physical reads') p,
dba_hist_snapshot s
where s.begin_interval_time between in_start_date and (in_start_date + in_nbr_days)
and s.dbid = v_dbid
and s.instance_number = nvl(v_instance_nbr, s.instance_number)
and cg.snap_id = s.snap_id
and cg.dbid = s.dbid
and cg.instance_number = s.instance_number
and dbg.snap_id = s.snap_id
and dbg.dbid = s.dbid
and dbg.instance_number = s.instance_number
and p.snap_id = s.snap_id
and p.dbid = s.dbid
and p.instance_number = s.instance_number;
--
v_errcontext := 'open/fetch get_top_stmts';
dbms_application_info.set_action(v_errcontext);
for a in get_top_stmts(in_max_disk_reads, in_max_buffer_gets,
v_dbid, v_instance_nbr,
v_begin_snapshot, v_end_snapshot) loop
--
if get_top_stmts%rowcount > in_top_count then
--
exit;
--
end if;
--
v_errcontext := 'put_line formfeed';
dbms_application_info.set_action(v_errcontext);
if get_top_stmts%rowcount > 1 then
--
dbms_output.put_line(chr(12));
--
end if;
--
v_errcontext := 'put_line statement header';
dbms_application_info.set_action(v_errcontext);
dbms_output.put_line(rpad('Beginning Snap Time: ',30) ||
to_char(v_begin_snapshot, 'MM/DD/YY HH24:MI:SS') ||
lpad('Page ' ||
to_char(get_top_stmts%rowcount,'990'),60));
dbms_output.put_line(rpad('Ending Snap Time : ',30) ||
to_char(v_end_snapshot, 'MM/DD/YY HH24:MI:SS') ||
lpad('Nbr of Snapshots: ' ||
to_char(v_nbr_snapshots,'990'),60));
dbms_output.put_line(rpad('Date of Report : ',30) ||
to_char(sysdate, 'MM/DD/YY HH24:MI:SS') ||
lpad('Nbr of Instances: ' ||
to_char(v_nbr_instances,'990'),60));
dbms_output.put_line(rpad('Total Logical Reads: ', 23) ||
to_char(v_tot_logr,'999,999,999,999,999,990') ||
lpad('Total Physical Reads: ' ||
to_char(v_tot_phyr,'999,999,999,999,999,990'), 60));
dbms_output.put_line('.');
--
if a.module is not null then
v_errcontext := 'display module';
dbms_output.put_line('Module: "' || a.module || '"');
dbms_output.put_line('.');
end if;
--
dbms_output.put_line('SQL Statement Text (SQL ID=' || a.sql_id || ')');
dbms_output.put_line('-------------------------------' || rpad('-', length(trim(to_char(a.sql_id))), '-') || '-');
--
v_errcontext := 'get sql_text from dba_hist_sqltext';
dbms_application_info.set_action(v_errcontext);
select sql_text,
dbms_lob.getlength(sql_text) len
into v_sql_text,
v_sql_text_len
from dba_hist_sqltext
where sql_id = a.sql_id;
v_text_lines := 1;
n := 1;
while n < v_sql_text_len loop
--
dbms_output.put_line(rpad(to_char(v_text_lines),6) ||
replace(dbms_lob.substr(v_sql_text, 100, n),chr(10),null));
n := n + 100;
v_text_lines := v_text_lines + 1;
--
v_errcontext := 'fetch/close get_text';
--
end loop;
--
v_errcontext := 'put_line statement totals';
dbms_application_info.set_action(v_errcontext);
dbms_output.put_line('.');
dbms_output.put_line(':' ||
lpad('Disk ',16) ||
lpad('Buffer',16) ||
lpad('Cache Hit',10) ||
lpad(' ',11) ||
lpad('DR Per',12) ||
lpad('BG Per',12) ||
lpad('CPU Per',15) ||
lpad('Ela Per',15));
dbms_output.put_line(':' ||
lpad('Reads',16) ||
lpad('Gets',16) ||
lpad('Ratio',10) ||
lpad('Runs',11) ||
lpad('Run',12) ||
lpad('Run',12) ||
lpad('Run',15) ||
lpad('Run',15));
dbms_output.put_line(':' ||
lpad('-----',16) ||
lpad('------',16) ||
lpad('---------',10) ||
lpad('----',11) ||
lpad('------',12) ||
lpad('------',12) ||
lpad('------',15) ||
lpad('------',15));
dbms_output.put_line(':' ||
lpad(ltrim(to_char(a.disk_reads,'999,999,999,990')),16) ||
lpad(ltrim(to_char(a.buffer_gets,'999,999,999,990')),16) ||
lpad(ltrim(to_char(a.bchr,'990.00')||'%'),10) ||
lpad(ltrim(to_char(a.executions,'99,999,990')),11) ||
lpad(ltrim(to_char(a.dr_per_exe,'999,999,990')),12) ||
lpad(ltrim(to_char(a.bg_per_exe,'999,999,990')),12) ||
lpad(ltrim(to_char(a.cpu_per_exe,'999,999,990.00')),15) ||
lpad(ltrim(to_char(a.ela_per_exe,'999,999,990.00')),15));
dbms_output.put_line(':' ||
lpad('('||ltrim(to_char(round((a.disk_reads/v_tot_phyr)*100,3),
'990.000'))||'%)',16) ||
lpad('('||ltrim(to_char(round((a.buffer_gets/v_tot_logr)*100,3),
'990.000'))||'%)',16));
--
v_errcontext := 'open/fetch get_sql_plan';
dbms_application_info.set_action(v_errcontext);
for p in get_sql_plan(v_dbid, a.sql_id, v_begin_snapshot, v_end_snapshot) loop
--
v_text_lines := 0;
v_errcontext := 'open/fetch get_xplan';
dbms_application_info.set_action(v_errcontext);
for s in (select plan_table_output
from table(dbms_xplan.display_awr(a.sql_id, p.plan_hash_value, v_dbid, 'ALL'))) loop
--
if s.plan_table_output like 'Plan hash value: %' then
v_text_lines := 1;
end if;
--
if v_text_lines = 1 then
dbms_output.put_line('.');
dbms_output.put_line('. SQL execution plan from "'||
to_char(p.timestamp,'MM/DD/YY HH24:MI:SS') || '"');
end if;
--
if v_text_lines >= 1 then
dbms_output.put_line(s.plan_table_output);
v_text_lines := v_text_lines + 1;
end if;
--
end loop;
--
v_errcontext := 'fetch/close get_sql_plan';
--
end loop;
--
if a.command_type = 47 then
--
v_plsql_tot_cnt := v_plsql_tot_cnt + 1;
v_plsql_tot_dr := v_plsql_tot_dr + a.disk_reads;
v_plsql_tot_bg := v_plsql_tot_bg + a.buffer_gets;
v_plsql_tot_cpu := v_plsql_tot_cpu + a.cpu_time;
v_plsql_tot_ela := v_plsql_tot_ela + a.elapsed_time;
--
else
--
v_sql_tot_cnt := v_sql_tot_cnt + 1;
v_sql_tot_dr := v_sql_tot_dr + a.disk_reads;
v_sql_tot_bg := v_sql_tot_bg + a.buffer_gets;
v_sql_tot_cpu := v_sql_tot_cpu + a.cpu_time;
v_sql_tot_ela := v_sql_tot_ela + a.elapsed_time;
--
end if;
--
v_errcontext := 'fetch/close get_top_stmt';
dbms_application_info.set_action(v_errcontext);
--
end loop;
--
if v_sql_tot_cnt > 0 then
--
v_errcontext := 'put_line SQL cumulative totals';
dbms_application_info.set_action(v_errcontext);
dbms_output.put_line('.');
dbms_output.put_line('.');
dbms_output.put_line(': =============================================================================');
dbms_output.put_line(':');
dbms_output.put_line(': >>> CUMULATIVE TOTALS FOR '||v_sql_tot_cnt||' "TOP ' || in_top_count || '" SQL STATEMENTS <<<');
dbms_output.put_line(':');
dbms_output.put_line(':' ||
lpad('Disk ',16) ||
lpad('Buffer',20) ||
lpad('Cache Hit',10) ||
lpad('CPU',20) ||
lpad('Elapsed',20));
dbms_output.put_line(':' ||
lpad('Reads',16) ||
lpad('Gets',20) ||
lpad('Ratio',10) ||
lpad('Time',20) ||
lpad('Time',20));
dbms_output.put_line(':' ||
lpad('-----',16) ||
lpad('------',20) ||
lpad('---------',10) ||
lpad('---------',20) ||
lpad('---------',20));
dbms_output.put_line(':' ||
lpad(ltrim(to_char(v_sql_tot_dr,'999,999,999,990')),16) ||
lpad(ltrim(to_char(v_sql_tot_bg,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char((1 - (v_sql_tot_dr/v_sql_tot_bg))*100,'990.00')||'%'),10) ||
lpad(ltrim(to_char(v_sql_tot_cpu,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char(v_sql_tot_ela,'999,999,999,999,990')),20));
dbms_output.put_line(':' ||
lpad('('||ltrim(to_char(round((v_sql_tot_dr/v_tot_phyr)*100,3),
'990.000'))||'%)',16) ||
lpad('('||ltrim(to_char(round((v_sql_tot_bg/v_tot_logr)*100,3),
'990.000'))||'%)',20));
--
end if;
--
if v_plsql_tot_cnt > 0 then
--
v_errcontext := 'put_line PLSQL cumulative totals';
dbms_application_info.set_action(v_errcontext);
dbms_output.put_line('.');
dbms_output.put_line('.');
dbms_output.put_line(': =============================================================================');
dbms_output.put_line(':');
dbms_output.put_line(': >>> CUMULATIVE TOTALS FOR '||v_plsql_tot_cnt||' "TOP '||in_top_count||'" PL/SQL STATEMENTS <<<');
dbms_output.put_line(':');
dbms_output.put_line(':' ||
lpad('Disk ',20) ||
lpad('Buffer',20) ||
lpad('Cache Hit',10) ||
lpad('CPU',20) ||
lpad('Elapsed',20));
dbms_output.put_line(':' ||
lpad('Reads',16) ||
lpad('Gets',20) ||
lpad('Ratio',10) ||
lpad('Time',20) ||
lpad('Time',20));
dbms_output.put_line(':' ||
lpad('-----',20) ||
lpad('------',20) ||
lpad('---------',10) ||
lpad('---------',20) ||
lpad('---------',20));
dbms_output.put_line(':' ||
lpad(ltrim(to_char(v_plsql_tot_dr,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char(v_plsql_tot_bg,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char((1 - (v_plsql_tot_dr/v_plsql_tot_bg))*100,'990.00')||'%'),10) ||
lpad(ltrim(to_char(v_plsql_tot_cpu,'999,999,999,999,990')),20) ||
lpad(ltrim(to_char(v_plsql_tot_ela,'999,999,999,999,990')),20));
dbms_output.put_line(':' ||
lpad('('||ltrim(to_char(round((v_plsql_tot_dr/v_tot_phyr)*100,3),
'990.000'))||'%)',20) ||
lpad('('||ltrim(to_char(round((v_plsql_tot_bg/v_tot_logr)*100,3),
'990.000'))||'%)',20));
--
end if;
--
rollback;
--
dbms_application_info.set_module(v_save_module, v_save_action);
--
exception
when others then
v_errmsg := sqlerrm;
dbms_application_info.set_module(v_save_module, v_save_action);
rollback;
raise_application_error(-20000, v_errcontext || ': ' || v_errmsg);
end top_stmt5;
/
show errors
spool off

Wednesday 23 February 2011

Oracle sqlplus Unix nohup

nohup sqlplus prod1/prod1 @index.sql > index.out 2>&1 &

nohup /export/home/oracle/restore.sh > /export/home/oracle/restore_`date '+%Y%m%d_%H%M%S'`.log 2>&1 &

Thursday 17 February 2011

How To Estimate Oracle Text Index Fragmentation ?

is Oracle text index Fragmented ?


SYS AS SYSDBA>set linesize 200
select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where INDEX_TYPE='DOMAIN'



INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------- --------------------------------------------------------------------------------- ---------------
SYSOBJECT_FULLTEXT DOMAIN DM_SYSOBJECT_S



Text I

SYS AS SYSDBA>select table_name,OWNER from dba_tables where table_name like 'DR$SYS%';

or

select table_name,OWNER from dba_tables where table_name like '%SYSOBJECT_FULLTEXT%'


SYS AS SYSDBA>select table_name,owner from dba_tables where table_name like '%SYSOBJECT_FULLTEXT%' ;

TABLE_NAME OWNER
------------------------------ ---------------
DR$SYSOBJECT_FULLTEXT$P PROD1
DR$SYSOBJECT_FULLTEXT$I PROD1 <<<--- we have to use this table
DR$SYSOBJECT_FULLTEXT$K PROD1
DR$SYSOBJECT_FULLTEXT$R PROD1
DR$SYSOBJECT_FULLTEXT$N PROD1




select avg(tfrag) from ( select /*+ ORDERED USE_NL(i) INDEX(i DR$TEXT_IDX$X) */ i.token_text,
(1-(least(round((sum(dbms_lob.getlength(i.token_info))/3800)+(0.50 - (1/3800))),count(*))/count(*)))*100 tfrag
from ( select token_text, token_type from prod1.dr$SYSOBJECT_FULLTEXT$i sample(0.149)
where rownum <= 100 ) t, prod1.dr$SYSOBJECT_FULLTEXT$i i
where i.token_text = t.token_text
and i.token_type = t.token_type
group by i.token_text, i.token_type);



AVG(TFRAG)
----------
12.6856968




if result is more then 40 % the your index is fragmented .

Oracle Text Index statistics report / fragmentation report

Here is an example of using CTX_REPORT.INDEX_STATS for text Index


create this table
create table output (result CLOB);

or

if already there

truncate table OUTPUT ;

from sys


prompt create table output (result CLOB);

truncate table OUTPUT ;

declare
x clob := null;

begin

ctx_report.index_stats('PROD1.SYSOBJECT_FULLTEXT',x);
insert into output values (x);
commit;
dbms_lob.freetemporary(x);
end;
/



set this line to read clob

spool text_stat_report

SET LONG 1000000 LONGC 1000000 LIN 80 TIMI ON
select * from output;

spool off

Monday 14 February 2011

Oracle RMAN Configuring Tablespaces for Exclusion/exclude

RMAN exclude Tablespace

The exclusion condition applies to any datafiles that you add to this tablespace in the future.

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE XXXX;

XXXX <<<<<<<--- tablespace name

RMAN will not take backup of XXXX tablespace


To Clear exclusion .....

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE XXXX CLEAR ;

RMAN> SHOW EXCLUDE;

Oracle Date Time - How to add second Minute hour

Example using NUMTODSINTERVAL with SYSDATE:



Example-- Add 20 second in system time

select to_char(sysdate,'dd/mm/yyyy hh:mi:ss'),to_char (SYSDATE + NUMTODSINTERVAL(20,'SECOND'),'dd/mm/yyyy hh:mi:ss') from dual;


One of the string constants: 'DAY', 'HOUR', 'MINUTE', or 'SECOND'.

Wednesday 9 February 2011

Oracle dynamic_sampling and level

/*+ dynamic_sampling(Table_name 2) */
/*+ dynamic_sampling (Table_name 4) */


Level 1: Samples tables that appear in join or subquery conditions that have no indexes and
have more blocks than 32,the default for dynamic sampling.

Level 2 (default): Samples all unanalyzed tables that have more than 32 blocks.

Level 3: Samples tables using a single column that applies selectivity to the table being
sampled.

Level 4: Samples tables using two or more columns that apply selectivity to the table being
sampled.

Level 5: Doubles the dynamic sample size and samples 64 blocks on tables.

Level 6: Quadruples the dynamic sample size and samples 128 blocks on tables.

Level 7: Samples 256 blocks on tables.

Level 8: Samples 1,024 blocks on tables.

Level 9: Samples 4,096 blocks on tables.

Level 10: Samples all of the block in the tables.

Tuesday 8 February 2011

Oracle version count info metalink 438755.1

Running the Script
-- Generate reports for all cursors with more than 100 versions using SQL_ID (10g and up)
select b.* from v$sqlarea a ,table(version_rpt(a.sql_id)) b where loaded_versions >=100;

-- Generate reports for all cursors with more than 100 versions using HASH_VALUE
select b.* from v$sqlarea a ,table(version_rpt(null,a.hash_value)) b where loaded_versions>=100;

-- Generate the report for cursor with sql_id cyzznbykb509s
select * from table(version_rpt('cyzznbykb509s'));


====



create or replace view SQL_SHARED_CURSOR
as select * from sys.v$sql_shared_cursor;


create or replace function version_rpt(p_sql_id varchar2 default null,p_hash number default null) return DBMS_DEBUG_VC2COLL PIPELINED is

type vc_arr is table of varchar2(32767) index by binary_integer;

type num_arr is table of number index by binary_integer;



v_version varchar2(100);
v_colname vc_arr;
v_Ycnt num_arr;
v_count number:=-1;
v_no number;
v_all_no number:=-1;
v_query varchar2(4000);
v_sql_where varchar2(4000);
v_sql_where2 varchar2(4000);
v_sql_id varchar2(15):=p_sql_id;
v_addr varchar2(100);
V_coladdr varchar2(100);
v_hash number:=p_hash;
v_mem number;
v_parses number;
theCursor number;
columnValue char(1);
status number;
v_driver varchar2(1000);
TYPE cursor_ref IS REF CURSOR;
vc cursor_ref;
v_bind_dumped boolean:=false;
v_auth_dumped boolean:=false;


BEGIN

select version into v_version from v$instance;
v_coladdr:=case when v_version like '9%' then 'KGLHDPAR' else 'ADDRESS' end;

if v_sql_id is not null then
open vc for 'select sql_text query,hash_value hash,rawtohex(ADDRESS) addr , sql_id , SHARABLE_MEM,PARSE_CALLS '|| ' from v$sqlarea where sql_id=:v_sql_id 'using v_sql_id ;

else -- Use Hash Value

open vc for

'select sql_text query,hash_value,rawtohex(ADDRESS) addr,'||case when v_version like '9%' then ' NULL ' end ||' sql_id,SHARABLE_MEM,PARSE_CALLS '
||' from v$sqlarea where hash_value=:v_hash'
using v_hash;
end if;

PIPE ROW('Version Count Report Version 3.1 -- Today''s Date '||to_char(sysdate,'dd-mon-yy hh24:mi')) ;



/*

This loop is in the remote case there are more than 1 SQL with the same hash value or sql_id
After this loop I cannot guarantee that I can distinguish the colliding SQL from one another.

*/

loop



fetch vc into v_query,v_hash,v_addr,v_sql_id,v_mem,v_parses;

exit when vc%notfound;


v_colname.delete;
v_Ycnt.delete;
v_count:=-1;
v_no:=0;
v_all_no:=-1;
PIPE ROW('================================================================');
PIPE ROW('Addr: '||v_addr||' Hash_Value: '||v_hash||' SQL_ID '||v_sql_id);
PIPE ROW('Sharable_Mem: '||v_mem||' bytes Parses: '||v_parses);
PIPE ROW('Stmt: ');
for i in 0 .. trunc(length(v_query)/64) loop
PIPE ROW(i||' '||substr(v_query,1+i*64,64));
end loop;



if v_sql_id is not null then
v_sql_where:=' WHERE SQL_ID='''||v_sql_id||'''';
else
v_sql_where:=' WHERE hash_value='||to_char(v_hash);
end if;
v_sql_where2:=' and '||v_coladdr||'=HEXTORAW('''||V_ADDR||''')';

SELECT COLUMN_NAME,0 bulk collect into v_colname,v_Ycnt
from cols
where table_name='SQL_SHARED_CURSOR'
and CHAR_LENGTH=1
order by column_id;
v_query:='';

for i in 1 .. v_colname.count loop
v_query:= v_query ||','|| v_colname(i);
end loop;



v_query:= 'SELECT '||substr(v_query,2) || ' FROM SQL_SHARED_CURSOR ';
v_query:=v_query||v_sql_where||v_sql_where2;



begin

theCursor := dbms_sql.open_cursor;
sys.dbms_sys_sql.parse_as_user( theCursor, v_Query, dbms_sql.native );

for i in 1 .. v_colname.count loop
dbms_sql.define_column( theCursor, i, columnValue, 8000 );

end loop;



status := dbms_sql.execute(theCursor);



while (dbms_sql.fetch_rows(theCursor) >0) loop
v_no:=0;
v_count:=v_count+1;
for i in 1..v_colname.count loop
dbms_sql.column_value(theCursor, i, columnValue);
if columnValue='Y' then
v_Ycnt(i):=v_Ycnt(i)+1;
else
v_no:=v_no+1;
end if;
end loop;



if v_no=v_colname.count then
v_all_no:=v_all_no+1;
end if;
end loop;
dbms_sql.close_cursor(theCursor);
end;
PIPE ROW('');
PIPE ROW('Versions Summary');
PIPE ROW('----------------');
for i in 1 .. v_colname.count loop
if v_Ycnt(i)>0 then
PIPE ROW(v_colname(i)||' :'||v_Ycnt(i));
end if;
end loop;

If v_all_no>1 then

PIPE ROW('Versions with ALL Columns as "N" :'||v_all_no);

end if;

PIPE ROW('Total Versions:'||v_count);
PIPE ROW(' ');


declare

v_phv num_arr;

v_phvc num_arr;

begin


v_sql_where2:=' and ADDRESS=HEXTORAW('''||V_ADDR||''')';


v_query:='select plan_hash_value,count(*) from v$sql '||v_sql_where||v_sql_where2||' group by plan_hash_value';


execute immediate v_query bulk collect into v_phv,v_phvc;



PIPE ROW('Plan Hash Value Summary');

PIPE ROW('-----------------------');

PIPE ROW('Plan Hash Value Count');

PIPE ROW('=============== =====');

for i in 1 .. v_phv.count loop

PIPE ROW(to_char(v_phv(i),'99999999999999')||' '||to_char(v_phvc(i),'9999'));

end loop;

PIPE ROW(' ');

end;

end loop;



for i in 1 .. v_colname.count loop


if v_Ycnt(i)>0 then
PIPE ROW('~~~~~~~~~~~~~~'||rpad('~',length(v_colname(i)),'~'));
PIPE ROW('Details for '||v_colname(i)||' :');
PIPE ROW('');
if ( v_colname(i) in ('BIND_MISMATCH','USER_BIND_PEEK_MISMATCH','BIND_EQUIV_FAILURE','BIND_UACS_DIFF')
or (v_version like '11.1%' and v_colname(i)='ROW_LEVEL_SEC_MISMATCH')) then
if v_bind_dumped=true then -- Dump only once
PIPE ROW('Details shown already.');
else
v_bind_dumped:=true;
if v_version like '9%' then
PIPE ROW('No details for '||v_version);
else
PIPE ROW('Consolidated details for :');
PIPE ROW('BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF and');
PIPE ROW('BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)');

PIPE ROW('');
declare
v_position num_arr;
v_maxlen num_arr;
v_minlen num_arr;

v_dtype num_arr;

v_prec num_arr;

v_scale num_arr;

v_n num_arr;



begin

v_query:='select position,min(max_length),max(max_length),datatype,precision,scale,count(*) n'

||' from v$sql_bind_capture where sql_id=:v_sql_id'

||' group by sql_id,position,datatype,precision,scale'

||' order by sql_id,position,datatype,precision,scale';



EXECUTE IMMEDIATE v_query

bulk collect into v_position, v_minlen, v_maxlen , v_dtype ,v_prec ,v_scale , v_n

using v_sql_id;



PIPE ROW('from v$sql_bind_capture');

PIPE ROW('COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE (PRECISION,SCALE)');

PIPE ROW('======== ======== =============== =============== ======== ================');

for c in 1 .. v_position.count loop

PIPE ROW( to_char(v_n(c),'9999999')||' '||to_char(v_position(c),'9999999')||' '|| to_char(v_minlen(c),'99999999999999')

||' '|| to_char(v_maxlen(c),'99999999999999')

||' '|| to_char(v_dtype(c),'9999999')||' ('|| v_prec(c)||','||v_scale(c)||')' );

end loop;



if v_version like '11%' then

v_query:='select sum(decode(IS_OBSOLETE,''Y'', 1, 0)),sum(decode(IS_BIND_SENSITIVE ,''Y'',1, 0))'

||',sum(decode(IS_BIND_AWARE,''Y'',1,0)),sum(decode(IS_SHAREABLE,''Y'',1,0))'

||' from v$sql where sql_id = :v_sql_id';



EXECUTE IMMEDIATE v_query

bulk collect into v_position, v_minlen, v_maxlen , v_dtype

using v_sql_id;



PIPE ROW('');

PIPE ROW('SUM(DECODE(column,Y, 1, 0) FROM V$SQL');

PIPE ROW('IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE');

PIPE ROW('=========== ================= ============= ============');

for c in 1 .. v_position.count loop

PIPE ROW(to_char(v_position(c),'9999999999')||' '|| to_char(v_minlen(c),'9999999999999999')

||' '|| to_char(v_maxlen(c),'999999999999')

||' '|| to_char(v_dtype(c),'99999999999'));

end loop;

end if;

end;

end if;

end if;

elsif v_colname(i) ='OPTIMIZER_MODE_MISMATCH' then

for c in (select OPTIMIZER_MODE,count(*) n from v$sql where hash_value=v_hash group by OPTIMIZER_MODE) loop

PIPE ROW(c.n||' versions with '||c.OPTIMIZER_MODE);

end loop;

elsif v_colname(i) ='OPTIMIZER_MISMATCH' then

if v_version like '9%' then

PIPE ROW('No details available for '||v_version);

else

declare
v_param vc_arr;
v_value vc_arr;
v_n num_arr;
begin
v_query:='select o.NAME,o.VALUE ,count(*) n '
||'from V$SQL_OPTIMIZER_ENV o,sql_shared_cursor s '
||'where ISDEFAULT=''NO'' '
||' and OPTIMIZER_MISMATCH=''Y'' '
||' and s.sql_id=:v_sql_id '
||' and o.sql_id=s.sql_id '
||' and o.CHILD_ADDRESS=s.CHILD_ADDRESS '
||' group by o.NAME,o.VALUE ';

EXECUTE IMMEDIATE v_query
bulk collect into v_param,v_value,v_n using v_sql_id ;
for c in 1 .. v_n.count loop
PIPE ROW(v_n(c)||' versions with '||v_param(c)||' = '||v_value(c));

end loop;

end;
end if;
elsif v_colname(i) ='AUTH_CHECK_MISMATCH' then

declare
v_pusr num_arr;
v_pschid num_arr;
v_pschname vc_arr;
v_n num_arr;

begin

v_query:='select PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME ,count(*) n from v$sql '
||v_sql_where||v_sql_where2
||' group by PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME';


EXECUTE IMMEDIATE v_query
bulk collect into v_pusr,v_pschid,v_pschname,v_n;

PIPE ROW('# of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME');
PIPE ROW('======== =============== ================= ===================');
for c in 1 .. v_n.count loop
PIPE ROW(to_char(v_n(c),'9999999')|| TO_CHAR(v_pusr(c),'9999999999999999')|| to_char(v_pschid(c),'99999999999999999')||' '||v_pschname(c));
end loop;
end;
elsif v_colname(i) = 'TRANSLATION_MISMATCH' then

declare
v_objn num_arr;
v_objow vc_arr;
v_objnm vc_arr;
begin
v_query:='select distinct p.OBJECT#,p.OBJECT_OWNER,p.OBJECT_NAME'
||' from (select OBJECT_NAME ,count(distinct object#) n from v$sql_plan '
||v_sql_where||v_sql_where2
||' and object_name is not null group by OBJECT_NAME ) d'
||' ,v$sql_plan p where d.object_name=p.object_name and d.n>1';

EXECUTE IMMEDIATE v_query
bulk collect into v_objn,v_objow,v_objnm;

If v_objn.count>0 then
PIPE ROW('Summary of objects probably causing TRANSLATION_MISMATCH');
PIPE ROW(' ');
PIPE ROW('Object# Owner.Object_Name');
PIPE ROW('======= =================');
for c in 1 .. v_objn.count loop
PIPE ROW(to_char(v_objn(c),'999999')||' '||v_objow(c)||'.'||v_objnm(c));
end loop;
else
PIPE ROW('No objects in the plans with same name and different owner were found.');
end if;
end;
else
PIPE ROW('No details available');
end if;
end if;
end loop;
IF v_version not like '9%' then
PIPE ROW('####');
PIPE ROW('To further debug Ask Oracle Support for the appropiate level LLL.');
if v_version in ('10.2.0.1.0','10.2.0.2.0','10.2.0.3.0') THEN
PIPE ROW('and read note:457225.1 Cannot turn off Trace after setting CURSORTRACE EVENT');
end if;
PIPE ROW('alter session set events ');
PIPE ROW(' ''immediate trace name cursortrace address '||v_hash||', level LLL'';');
PIPE ROW('To turn it off do use address 1, level 2147483648');
end if;
PIPE ROW('================================================================');
exception
when others then
PIPE ROW('Error :'||sqlerrm);
PIPE ROW('for Addr: '||v_addr||' Hash_Value: '||v_hash||' SQL_ID '||v_sql_id);
for i in 0 .. trunc(length(v_query)/64) loop
PIPE ROW(i||' '||substr(v_query,1+i*64,64));
end loop;

end;

/

Top 10 SQL Buffer Gets from schema

Top SQL .... Buffer Gets



prompt PARSING_SCHEMA_NAME='SCOTT' <<<<<<<<<<--------------

declare
v_sql_id varchar2(20);
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select sql_id,buffer_gets,substr(sql_text,1,4000)
from v$sqlarea
where PARSING_SCHEMA_NAME='SCOTT'
order by buffer_gets desc;
begin
dbms_output.put_line('sql_id '||' '||' Reads'||' '||' Text');
dbms_output.put_line ('-----'||'-----'||' '||'---------------------------------------------------');
dbms_output.put_line(' ');
open c1;
for i in 1 .. 10 loop
dbms_output.put_line(' ');
fetch c1 into v_sql_id,top5, text1;
dbms_output.put_line(v_sql_id||' '||rpad(to_char(top5),9)|| ' '||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line('" '||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/

Friday 4 February 2011

Oracle Script to view Table stat

from http://kerryosborne.oracle-guy.com/



1.

create or replace function display_raw (rawval raw, type varchar2)
return varchar2
is
cn number;
cv varchar2(32);
cd date;
cnv nvarchar2(32);
cr rowid;
cc char(32);

begin
if (type = 'NUMBER') then
dbms_stats.convert_raw_value(rawval, cn);
return to_char(cn);
elsif (type = 'VARCHAR2') then
dbms_stats.convert_raw_value(rawval, cv);
return to_char(cv);
elsif (type = 'DATE') then
dbms_stats.convert_raw_value(rawval, cd);
return to_char(cd,'dd-mon-yyyy');
elsif (type = 'NVARCHAR2') then
dbms_stats.convert_raw_value(rawval, cnv);
return to_char(cnv);
elsif (type = 'ROWID') then
dbms_stats.convert_raw_value(rawval, cr);
return to_char(cnv);
elsif (type = 'CHAR') then
dbms_stats.convert_raw_value(rawval, cc);
return to_char(cc);
else
return 'UNKNOWN DATATYPE';
end if;
end;
/

==================================
table_stat1.sql <<<<<<<<<<<<<<--------------

set echo off feed off
set serveroutput on size 1000000

accept ownname prompt 'Owner : '
accept tabname prompt 'Table : '

set lines 80

define v_desc = '&ownname..&tabname.'
desc &v_desc
set lines 200

declare
v_query varchar2(4000) ;
v_owner varchar2(30) := upper('&&ownname');
v_table varchar2(30) := upper('&&tabname');
v_max_colname number ;
v_max_ndv number ;
v_max_nulls number ;
v_max_bkts number ;
v_max_smpl number ;
v_max_avg_col_len number ;
v_max_low number ;
v_max_high number ;
v_max_endnum number ;
v_max_endval number ;
v_ct number ;
prev_col varchar2(30) ;


cursor col_stats is
select a.column_name, nvl(a.last_analyzed,to_date('01/01/1900','mm/dd/yyyy')) last_analyzed,
decode(a.nullable,'N','NOT NULL',' ') nullable,
a.num_distinct, a.density, a.num_nulls,
a.num_buckets, a.avg_col_len, nvl(to_char(a.sample_size),' ') sample_size,
display_raw(low_value,data_type) low_value, display_raw(high_value,data_type) high_value
from all_tab_columns a
where a.owner = v_owner
and a.table_name = v_table
order by a.column_name;


cursor hist_stats is
select b.column_name, b.endpoint_number, b.endpoint_value, b.endpoint_actual_value
from all_tab_histograms b
where b.owner = v_owner
and b.table_name = v_table
and (exists (select 1 from all_tab_columns
where num_buckets > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
or
exists (select 1 from all_tab_histograms
where endpoint_number > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
)
order by b.column_name, b.endpoint_number;

procedure print_table ( p_query in varchar2, p_date_fmt in varchar2 default 'dd-MON-yyyy hh24:mi:ss' ) is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_cs varchar2(255);
l_date_fmt varchar2(255);

-- Small inline procedure to restore the session's state.
-- We may have modified the cursor sharing and nls date format
-- session variables. This just restores them.
procedure restore
is
begin
if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
then
execute immediate
'alter session set cursor_sharing=exact';
end if;
if ( p_date_fmt is not null )
then
execute immediate
'alter session set nls_date_format=''' || l_date_fmt || '''';
end if;
dbms_sql.close_cursor(l_theCursor);
end restore;
begin
-- I like to see the dates print out with times, by default. The
-- format mask I use includes that. In order to be "friendly"
-- we save the current session's date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format.
if ( p_date_fmt is not null )
then
select sys_context( 'userenv', 'nls_date_format' )
into l_date_fmt
from dual;
execute immediate
'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;

-- To be bind variable friendly on ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar. If not, set it to force so when we parse literals
-- are replaced with binds.
if ( dbms_utility.get_parameter_value
( 'cursor_sharing', l_status, l_cs ) = 1 )
then
if ( upper(l_cs) not in ('FORCE','SIMILAR'))
then
execute immediate
'alter session set cursor_sharing=force';
end if;
end if;


-- Parse and describe the query sent to us. We need
-- to know the number of columns and their names.
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

-- Define all columns to be cast to varchar2s. We
-- are just printing them out.
for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;

-- Execute the query, so we can fetch.
l_status := dbms_sql.execute(l_theCursor);

-- Loop and print out each column on a separate line.
-- Bear in mind that dbms_output prints only 255 characters/line
-- so we'll see only the first 200 characters by my design...
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
substr( l_columnValue, 1, 200 ) );
end loop;
dbms_output.put_line( '-----------------' );
end loop;

-- Now, restore the session state, no matter what.
restore;
exception
when others then
restore;
raise;
end;


begin
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Table Statistics');
dbms_output.put_line('==========================================================================================');


v_query := 'select table_name, last_analyzed, trim(degree) degree, partitioned,
num_rows, chain_cnt, blocks, empty_blocks, avg_space,
avg_row_len, monitoring, sample_size
from all_tables
where owner = ''' || UPPER(v_owner) || ''' and table_name = ''' || UPPER(v_table) || '''';
print_table (v_query);

v_ct := 0 ;

select count(1)
into v_ct
from all_tab_partitions
where table_owner = v_owner
and table_name = v_table;

if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Partition Information');
dbms_output.put_line('==========================================================================================');


v_query := 'select partition_name, last_analyzed, high_value,
num_rows, chain_cnt, blocks, empty_blocks,
avg_space, avg_row_len
from all_tab_partitions
where table_owner = ''' || UPPER(v_owner) || '''
and table_name = ''' || UPPER(v_table) || '''';

print_table (v_query);
end if ;

select max(length(column_name)) + 1, max(length(num_distinct)) + 3,
max(length(num_nulls)) + 1, max(length(num_buckets)) + 1,
max(length(sample_size)) + 1,
max(length(avg_col_len)) + 1,
max(length(display_raw(low_value,DATA_TYPE))) + 1,
max(length(display_raw(high_value,DATA_TYPE))) + 1
into v_max_colname, v_max_ndv, v_max_nulls, v_max_bkts, v_max_smpl, v_max_avg_col_len, v_max_low, v_max_high
from all_tab_columns
where owner = v_owner
and table_name = v_table ;

if v_max_nulls < 8 then
v_max_nulls := 8 ;
end if ;

if v_max_bkts < 10 then
v_max_bkts := 10 ;
end if ;

if v_max_smpl < 7 then
v_max_smpl := 7;
end if;

if v_max_avg_col_len < 7 then
v_max_avg_col_len := 7;
end if;

if v_max_low < 4 then
v_max_low := 4;
end if;

if v_max_high < 4 then
v_max_high := 4;
end if;

dbms_output.put_line('=============================================================================================================');
dbms_output.put_line(' Column Statistics');
dbms_output.put_line('=============================================================================================================');
dbms_output.put_line('' || rpad('Name',v_max_colname) || ' Analyzed Null? ' ||
lpad(' NDV',v_max_ndv) || ' ' || lpad('Density',8) || ' ' ||
lpad('# Nulls',v_max_nulls) || ' ' || lpad('# Buckets',v_max_bkts) || ' ' ||
lpad('Sample',v_max_smpl) || ' ' || lpad('Avg Len',v_max_avg_col_len) || ' ' ||
lpad('Min',v_max_low) || ' ' || lpad('Max',v_max_high));
dbms_output.put_line('==============================================================================================================');


for v_rec in col_stats loop
dbms_output.put_line(rpad(v_rec.column_name,v_max_colname) || ' ' ||
to_char(v_rec.last_analyzed,'mm/dd/yyyy') || ' ' ||
v_rec.nullable || ' ' ||
lpad(v_rec.num_distinct,v_max_ndv) || ' ' ||
to_char(v_rec.density,'9.999999') || ' ' ||
lpad(v_rec.num_nulls,v_max_nulls) || ' ' ||
lpad(v_rec.num_buckets,v_max_bkts) || ' ' ||
lpad(v_rec.sample_size,v_max_smpl) || ' ' ||
lpad(v_rec.avg_col_len,v_max_avg_col_len) || ' ' ||
lpad(v_rec.low_value,v_max_low) || ' ' ||
lpad(v_rec.high_value,v_max_high));

end loop ;

select max(length(column_name)) + 1, max(length(endpoint_number)) + 1,
max(length(endpoint_value)) + 1
into v_max_colname, v_max_endnum, v_max_endval
from all_tab_histograms
where owner = v_owner
and table_name = v_table ;

if v_max_endnum < 12 then
v_max_endnum := 12 ;
end if ;

if v_max_endval < 16 then
v_max_endval := 16 ;
end if ;

select count(1)
into v_ct
from all_tab_histograms b
where b.owner = v_owner
and b.table_name = v_table
and (exists (select 1 from all_tab_columns
where num_buckets > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
or
exists (select 1 from all_tab_histograms
where endpoint_number > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
);

/* Histogram data commented out

if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Histogram Statistics');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' ' || rpad('Name',v_max_colname) || ' ' ||
rpad('Endpoint #',v_max_endnum) || ' ' ||
rpad('Endpoint Value',v_max_endval) || ' Endpoint Actual Value');

v_ct := 0 ;
for v_rec in hist_stats loop
if v_ct = 0 then
v_ct := 1 ;
prev_col := v_rec.column_name ;
elsif prev_col <> v_rec.column_name then
dbms_output.put_line('------------------------------------------------------------------------------------------');
prev_col := v_rec.column_name ;
end if ;
dbms_output.put_line(rpad(v_rec.column_name, v_max_colname) || ' ' ||
rpad(v_rec.endpoint_number,v_max_endnum) || ' ' ||
rpad(v_rec.endpoint_value,v_max_endval) || ' ' ||
substr(v_rec.endpoint_actual_value,1,20) ) ;
end loop ;
end if ;
*/

v_ct := 0;

select count(1)
into v_ct
from all_indexes a
where a.table_owner = v_owner
and a.table_name = v_table;

if v_ct > 0 then
dbms_output.put_line('============================================================================================================');
dbms_output.put_line(' Index Information');
dbms_output.put_line('============================================================================================================');

v_query := 'select a.index_name, substr(a.index_type, 1, 4) index_type,
a.last_analyzed, a.degree, a.partitioned, a.blevel,
a.leaf_blocks, a.distinct_keys,
a.avg_leaf_blocks_per_key, a.avg_data_blocks_per_key,
a.clustering_factor, b.blocks blocks_in_table, b.num_rows rows_in_table
from all_indexes a, all_tables b
where (a.table_name = b.table_name and a.table_owner = b.owner)
and a.table_owner = ''' || UPPER(v_owner) || '''
and a.table_name = ''' || UPPER(v_table) || '''' ;

print_table (v_query);

dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Index Columns Information');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line('Index Name Pos# Order Column Name Expression');
dbms_output.put_line('==========================================================================================');
end if;

end ;
/




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

SQL> @table_stat1
Owner : SCOTT
Table : EMP
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

old 3: v_owner varchar2(30) := upper('&&ownname');
new 3: v_owner varchar2(30) := upper('SCOTT');
old 4: v_table varchar2(30) := upper('&&tabname');
new 4: v_table varchar2(30) := upper('EMP');
==========================================================================================
Table Statistics
==========================================================================================
TABLE_NAME : EMP
LAST_ANALYZED : 09-SEP-2010 22:00:06
DEGREE : 1
PARTITIONED : NO
NUM_ROWS : 14
CHAIN_CNT : 0
BLOCKS : 5
EMPTY_BLOCKS : 0
AVG_SPACE : 0
AVG_ROW_LEN : 37
MONITORING : YES
SAMPLE_SIZE : 14
-----------------
=============================================================================================================
Column Statistics
=============================================================================================================
Name Analyzed Null? NDV Density # Nulls # Buckets Sample Avg Len Min Max
==============================================================================================================
COMM 09/09/2010 4 .250000 10 1 4 2 0 1400
DEPTNO 09/09/2010 3 .333333 0 1 14 3 10 30
EMPNO 09/09/2010 NOT NULL 14 .071429 0 1 14 4 7369 7934
ENAME 09/09/2010 14 .071429 0 1 14 6 ADAMS WARD
HIREDATE 09/09/2010 13 .076923 0 1 14 8 17-dec-1980 23-may-1987
JOB 09/09/2010 5 .200000 0 1 14 8 ANALYST SALESMAN
MGR 09/09/2010 6 .166667 1 1 13 4 7566 7902
SAL 09/09/2010 12 .083333 0 1 14 4 800 5000
============================================================================================================
Index Information
============================================================================================================
INDEX_NAME : PK_EMP
INDEX_TYPE : NORM
LAST_ANALYZED : 09-SEP-2010 22:00:06
DEGREE : 1
PARTITIONED : NO
BLEVEL : 0
LEAF_BLOCKS : 1
DISTINCT_KEYS : 14
AVG_LEAF_BLOCKS_PER_KEY : 1
AVG_DATA_BLOCKS_PER_KEY : 1
CLUSTERING_FACTOR : 1
BLOCKS_IN_TABLE : 5
ROWS_IN_TABLE : 14
-----------------
==========================================================================================
Index Columns Information
==========================================================================================
Index Name Pos# Order Column Name Expression
==========================================================================================

Oracle Awr report run for Last 24 Hr .. on unix prompt


snap id info 



alter session set "_push_join_predicate" = FALSE ;   If awr running slow !!!!

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

@?/rdbms/admin/awrrpt.sql      --> basic AWR report
@?/rdbms/admin/awrsqrpt.sql    --> Standard SQL statement Report
@?/rdbms/admin/awrddrpt.sql    --> Period diff on current instance
@?/rdbms/admin/awrrpti.sql     --> Workload Repository Report Instance (RAC)
@?/rdbms/admin/awrgrpt.sql     --> AWR Global Report (RAC)
@?/rdbms/admin/awrgdrpt.sql    --> AWR Global Diff Report (RAC)
@?/rdbms/admin/awrinfo.sql     --> Script to output general AWR information


ADDM report !!!

SET LONG 500000 PAGESIZE 0
SELECT DBMS_ADDM.GET_REPORT('ADDM:1825264339_20529') FROM DUAL;



col snap_id new_value last_snap
col BEGIN_INTERVAL_TIME for a25 
col END_INTERVAL_TIME   for a25 
select snap_id,CON_ID,begin_interval_time,end_interval_time from dba_hist_snapshot 
order by begin_interval_time desc 
fetch first 3 row only ;


column BEGIN_INTERVAL_TIME format a25
column END_INTERVAL_TIME format a25
column STARTUP_TIME format a25
set lines 1000
select instance_number, snap_id , BEGIN_INTERVAL_TIME, END_INTERVAL_TIME,
STARTUP_TIME from dba_hist_snapshot where dbid=(select dbid from v$database) and BEGIN_INTERVAL_TIME 
between to_date('12-sep-2021','dd-mon-yyyy') and to_date('13-sep-2021','dd-mon-yyyy') 
and instance_number in (select instance_number from v$instance)
order by BEGIN_INTERVAL_TIME asc;


select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;




define p_inst=1
define p_days=2
column dt heading 'Date/Hour' format a11
set linesize 500
set pages 9999  
select * from (
select min(snap_id) as snap_id,  
     to_char(start_time,'dd-MM-YY') as dt, to_char(start_time,'HH24') as hr
from (
select snap_id, s.instance_number, begin_interval_time start_time, 
   end_interval_time end_time, snap_level, flush_elapsed,
   lag(s.startup_time) over (partition by s.dbid, s.instance_number   order by s.snap_id) prev_startup_time,
   s.startup_time
from  dba_hist_snapshot s, gv$instance i
where begin_interval_time between trunc(sysdate)-&p_days and sysdate 
and   s.instance_number = i.instance_number
and   s.instance_number = &p_inst
order by snap_id
)
group by to_char(start_time,'dd-MM-YY') , to_char(start_time,'HH24') 
order by snap_id, start_time )
pivot (sum(snap_id)  for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23'))
order by dt
;





define num_days=2

SET LINESIZE 200  PAGESIZE 200
--UNDEF num_days
COL startup_time FOR a30
COL db_name FOR a10
COL snap_start FOR 9999999
COL snap_end FOR 9999999
COL start_interval FOR a25
COL end_interval FOR a25
COL range_interval FOR a40
COL qtd_snaps FOR 999
 
SELECT
    s.startup_time,
di.dbid,
    di.instance_name,
    MIN(snap_id) snap_start,
    MAX(snap_id) snap_end,
    MIN(end_interval_time) start_interval,
    MAX(end_interval_time) end_interval,
    EXTRACT(DAY FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
    || ' Days(s) '
    || EXTRACT(HOUR FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
    || ' Hour(s) '
    || EXTRACT(MINUTE FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )
    || ' Minute(s) ' range_interval,
    MAX(snap_id) - MIN(snap_id) qtd_snaps
FROM
    dba_hist_snapshot s,
    dba_hist_database_instance di
WHERE
    di.dbid = s.dbid
    AND   di.instance_number = s.instance_number
    AND   end_interval_time > DECODE(&&num_days,0,TO_DATE('31-JAN-9999','DD-MON-YYYY'),3.14,s.end_interval_time,TO_DATE(SYSDATE,'dd/mm/yyyy') - (&num_days - 1) )
GROUP BY
    s.startup_time,
di.dbid,
    di.instance_name
ORDER BY     startup_time ASC;





#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=orcl
sqlplus -s /nolog <connect / as sysdba
set head off
set pages 0
set lines 132
set echo off
set feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v\$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v\$instance ;
select 'spool awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;
-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );
spool off
exit
EOF
=================


For Awr Report different spool file name




#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=orcl
TODAY=$(date)
DATE=`date +%d%m%Y:%H:%M:%S`
# DATE=`date +%d%m%Y`
l_awr_log_file="Awrrpt_$DATE.log"
#echo $l_awr_log_file
DATE=`date +%d%m%Y:%H:%M:%S`
sqlplus -s /nolog <connect / as sysdba
set head off
set pages 0
set lines 132
set echo off
set feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v\$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v\$instance ;
--spool select ' awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;
-- spool $DATE
spool $l_awr_log_file
-- spool print x
-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );
spool off
exit
EOF

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


set long 1000000
set pagesize 50000
column get_clob format a80
select dbms_advisor.get_task_report(task_name, 'TEXT', 'ALL') as ADDM_report
from dba_advisor_tasks where task_id=( select max(t.task_id)
from dba_advisor_tasks t, dba_advisor_log l
where t.task_id = l.task_id
and t.advisor_name='ADDM'
and l.status= 'COMPLETED');




select output
from table(
dbms_workload_repository.ash_report_text(
(select dbid from v$database),
1, -- instance id
sysdate - 2/24, -- startdate
sysdate - 1/24, -- enddate
0)
) ;





--- global Report
set head off  pages 0  lines 300 echo off  feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
SELECT output FROM TABLE (dbms_workload_repository.awr_global_report_text (:DID,'',:BgnSnap,:EndSnap,0 ) );




--- With Spool file

set head off  pages 0  lines 300 echo off  feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;

column awr new_val X
select to_char(:BgnSnap||'_'||:EndSnap) awr from dual;
spool awr_&X._file.txt

SELECT output FROM TABLE (dbms_workload_repository.awr_global_report_text (:DID,'',:BgnSnap,:EndSnap,0 ) );
spool off 



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


from 
https://blog.yannickjaquier.com/oracle/script-generate-series-awr-reports.html

for last 4 awr text report  .. 




 cat awr.sh

#!/bin/ksh

LOGFILE=/tmp/awr.log

sqlplus -s / as sysdba << EOF > $LOGFILE
set lines 130
set feedback off
set pages 0
select
  'export dbid=' || d.dbid as dbid
from
  v\$database d;

select
  'export db_name=' || d.name as db_name
from
  v\$database d;

select
  'export inst_num=' || i.instance_number as inst_num
from
  v\$instance i;

select
  'export inst_name=' || i.instance_name as  inst_name
from
  v\$instance i;


select 'export ininum=' ||(max(snap_id) -4) as ininum from dba_hist_snapshot ;

select 'export endnum=' ||max(snap_id)  as endnum from dba_hist_snapshot ;

EOF

while read line
do
  eval $line
done < $LOGFILE

sqlplus -s / as sysdba << EOF
set lines 130
set pages 1000
select
  snap_id,
  to_char(end_interval_time,'dd-Mon-YYYY hh24:mi') as snapdat
from dba_hist_snapshot
order by snap_id;
EOF
echo ininum = $ininum
echo endnum = $endnum

# ininum=39050
# endnum=39052
while [ $ininum -lt $endnum ];
do
  nxtnum=`expr $ininum + 1`
  repnam='awrrpt_'$inst_num'_'$ininum'_'$nxtnum'.txt'

  sqlplus -s / as sysdba << EOF
    define inst_num     = $inst_num;
    define num_days     = 100;
    define inst_name    = $inst_name;
    define db_name      = $db_name;
    define dbid         = $dbid;
    define report_type  = 'text';
    define begin_snap   = $ininum;
    define end_snap     = $nxtnum;
    define report_name  = $repnam;
    @@?/rdbms/admin/awrrpti
EOF

  ininum=$nxtnum
done

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



for last 4 awr html report  .. 


 cat awr.sh

#!/bin/ksh

LOGFILE=/tmp/awr.log

sqlplus -s / as sysdba << EOF > $LOGFILE
set lines 130
set feedback off
set pages 0
select
  'export dbid=' || d.dbid as dbid
from
  v\$database d;

select
  'export db_name=' || d.name as db_name
from
  v\$database d;

select
  'export inst_num=' || i.instance_number as inst_num
from
  v\$instance i;

select
  'export inst_name=' || i.instance_name as  inst_name
from
  v\$instance i;


select 'export ininum=' ||(max(snap_id) -4) as ininum from dba_hist_snapshot ;

select 'export endnum=' ||max(snap_id)  as endnum from dba_hist_snapshot ;

EOF

while read line
do
  eval $line
done < $LOGFILE

sqlplus -s / as sysdba << EOF
set lines 130
set pages 1000
select
  snap_id,
  to_char(end_interval_time,'dd-Mon-YYYY hh24:mi') as snapdat
from dba_hist_snapshot
order by snap_id;
EOF
echo ininum = $ininum
echo endnum = $endnum

# ininum=39050
# endnum=39052
while [ $ininum -lt $endnum ];
do
  nxtnum=`expr $ininum + 1`
  repnam='awrrpt_'$inst_num'_'$ininum'_'$nxtnum'.html'

  sqlplus -s / as sysdba << EOF
    define inst_num     = $inst_num;
    define num_days     = 100;
    define inst_name    = $inst_name;
    define db_name      = $db_name;
    define dbid         = $dbid;
    define report_type  = 'html';
    define begin_snap   = $ininum;
    define end_snap     = $nxtnum;
    define report_name  = $repnam;
    @@?/rdbms/admin/awrrpti
EOF

  ininum=$nxtnum
done



============
set linesize 300 pagesize 300
select snap_id,instance_number ,con_id,snap_id, end_interval_time from dba_hist_snapshot 
--WHERE begin_interval_time > TO_DATE('2011-06-07 07:00:00', 'YYYY-MM-DD HH24:MI:SS') 
WHERE end_interval_time > SYSDATE - 1
and instance_number=1 
order by 1
;


 VAR dbid NUMBER
 var bid NUMBER
 var eid NUMBER
 
exec :bid := '34710'
exec :eid := '34711'

BEGIN
  SELECT dbid INTO :dbid FROM v$database;
END;
/
/

SET TERMOUT OFF PAGESIZE 0 HEADING OFF LINESIZE 1000 TRIMSPOOL ON TRIMOUT ON TAB OFF

--*** Node 1
-- HTML
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(:dbid, 1, :bid, :eid));
-- Text 
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_text(:dbid, 1, :bid, :eid));


================
--*** Node 2
-- HTML
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(:dbid, 2, :bid, :eid));

-- Text 
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_text(:dbid, 2, :bid, :eid));



*** Global 

-- SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML(:dbid, CAST(null AS VARCHAR2(10)), &bid, &eid));

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_text(:dbid, CAST(null AS VARCHAR2(10)), :bid, :eid));

====

col OUTPUT for A150
select * from table(SYS.DBMS_WORKLOAD_REPOSITORY.awr_report_text(  (select dbid from v$database), 1,  (select max(snap_id) from dba_hist_snapshot) - 1,   (select max(snap_id) from dba_hist_snapshot)));


===========


VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
select 'spool awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;
-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );


******


if below error 

SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) )
                          *
ERROR at line 1:
ORA-20020: Database/Instance/Snapshot mismatch
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 16546
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 2660
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 1189
ORA-06512: at line 1


use below code !!!!

VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot where DBID= (select  DBID from v$database);
exec select max(snap_id) into :EndSnap from dba_hist_snapshot where DBID= (select  DBID from v$database);
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
select 'spool awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;
-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );


====



set serveroutput on
declare
cursor c is
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'dd-mm-yyyy-hh24:mi') beginsnapdat
, to_char(s.end_interval_time,'dd-mm-yyyy-hh24:mi')   endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s, dba_hist_database_instance di,gv$instance i,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.begin_interval_time > trunc(sysdate -1) --<<<<<<<< last last 1 days
order by di.db_name, i.instance_name, s.snap_id
;

begin
for c1 in c
loop
if c1.begin_snap_id > 0 then
dbms_output.put_line('spool '||c1.inst_name||'_'||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.txt');
dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_text( '||c1.dbid||','||c1.instance_number||','||c1.begin_snap_id||','||c1.end_snap_id||',0 ));');
dbms_output.put_line('spool off');
end if;
end loop;
end;
/


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

for html 



set serveroutput on linesize 300
declare
cursor c is
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'dd-mm-yyyy-hh24:mi') beginsnapdat
, to_char(s.end_interval_time,'dd-mm-yyyy-hh24:mi')   endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s, dba_hist_database_instance di,gv$instance i,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.begin_interval_time > trunc(sysdate -1) --<<<<<<<< last last 1 days
order by di.db_name, i.instance_name, s.snap_id
;

begin
for c1 in c
loop
if c1.begin_snap_id > 0 then
dbms_output.put_line('Set heading off  trimspool off linesize 1500 termout on feedback off');
dbms_output.put_line('spool '||c1.inst_name||'_'||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.html');
dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html( '||c1.dbid||','||c1.instance_number||','||c1.begin_snap_id||','||c1.end_snap_id||',0 ));');
dbms_output.put_line('spool off');
end if;
end loop;
end;
/

====
For Global (RAC all node) !!!



set serveroutput on linesize 300
declare
cursor c is
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'dd-mm-yyyy-hh24:mi') beginsnapdat
, to_char(s.end_interval_time,'dd-mm-yyyy-hh24:mi')   endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s, dba_hist_database_instance di,gv$instance i,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.begin_interval_time > trunc(sysdate -1) --<<<<<<<< last last 1 days
order by di.db_name, i.instance_name, s.snap_id
;

begin
for c1 in c
loop
if c1.begin_snap_id > 0 then
dbms_output.put_line('Set heading off  trimspool off linesize 1500 termout on feedback off');
dbms_output.put_line('spool '||c1.inst_name||'_'||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_Global_'||c1.endsnapdat||'.html');
dbms_output.put_line('select output from table(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML( '||c1.dbid||','||''''''||','||c1.begin_snap_id||','||c1.end_snap_id||',0 ));');
dbms_output.put_line('spool off');
end if;
end loop;
end;
/


====


define instance_number=1

set trimspool on trimout on
set lines 1500
set echo off
set heading off
set pages 0
set feedback off
set verify off
set trimspool on trimout on


define instance_number=1
--spool generate_awr_reports1..sql
select 'set heading off'   || chr(10) ||
      'set feedback off'  || chr(10) ||
      'set linesize 5000' || chr(10) ||
      'set trimspool on trimout on' || chr(10) ||
      'spool awr_'|| to_char(instance_number) || '_' || to_char(snap_id) || '_' || to_char(snap_id+1) || '.html' || chr(10) ||
      'select output from table(dbms_workload_repository.awr_report_html(' || to_char(dbid) || ',' || to_char(instance_number) || ',' ||
to_char(snap_id) || ',' || to_char(snap_id+1) || '));' || chr(10) ||
      'spool off'
from DBA_HIST_SNAPSHOT
where 1=1
and instance_number=1
and snap_id < ( select max(snap_id) from dba_hist_snapshot where instance_number=&instance_number)
order by snap_id;





set linesize 200 pagesize 200
col snaptime for a25
select dhdi.instance_name,
dhdi.db_name,
dhdi.DBID,
dhs.snap_id,
to_char(dhs.begin_interval_time,'MM/DD/YYYY:HH24:MI') begin_snap_time,
to_char(dhs.end_interval_time,'MM/DD/YYYY:HH24:MI') end_snap_time,
decode(dhs.startup_time,dhs.begin_interval_time,'**db restart**',null) db_bounce
from dba_hist_snapshot dhs, dba_hist_database_instance dhdi
where dhdi.dbid = dhs.dbid
and dhdi.instance_number = dhs.instance_number
and dhdi.startup_time = dhs.startup_time
and dhs.end_interval_time >= sysdate -2
order by db_name, instance_name, snap_id;


define  num_days     = 2;
define  db_name      = 'RAC';
define  dbid         = 1222414252;
define  begin_snap   = 10319;
define  end_snap     = 10320;
define  report_type  = 'html';
define  instance_numbers_or_ALL = 'ALL'
define  report_name  =  awrrpt_RAC_&&begin_snap._&&end_snap..&&report_type

@?/rdbms/admin/awrgrpti





VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
define begin_snap=:BgnSnap
define EndSnap=:EndSnap

define  num_days     = 3;
define  db_name      = 'Database';
define  dbid         = :DID;
define  begin_snap   = :BgnSnap;
define  end_snap     = :EndSnap;
define  report_type  = 'html';
define  instance_numbers_or_ALL = 'ALL'
define  report_name  =  awrrpt_RAC_&begin_snap._&end_snap

@?/rdbms/admin/awrgrpti


=====

Awr dump !!!!


define 3="TIMESTAMP'2023-01-31 19:00:00'" 
define 4="TIMESTAMP'2023-02-01 19:00:00'"

define DB_DIR='DATA_PUMP_DIR'

set serveroutput on linesize 300
declare
cursor c is
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'dd-mm-yyyy-hh24:mi') beginsnapdat
, to_char(s.end_interval_time,'dd-mm-yyyy-hh24:mi')   endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s, dba_hist_database_instance di,gv$instance i,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
--and s.begin_interval_time > trunc(sysdate -1) --<<<<<<<< last last 1 days
AND begin_interval_time BETWEEN &3 AND &4
order by di.db_name, i.instance_name, s.snap_id
;



begin
for c1 in c
loop
if c1.begin_snap_id > 0 then
--dbms_output.put_line('Set heading off  trimspool off linesize 1500 termout on feedback off');
--dbms_output.put_line('spool '||c1.inst_name||'_'||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.html');
dbms_output.put_line('begin dbms_swrf_internal.awr_extract
( '||'DMPFILE   =>' ||'''awr_data'||c1.begin_snap_id||''''||','||'dmpdir   => '|| '''&DB_DIR'''||',' ||' bid =>'||c1.begin_snap_id||','||'eid      => '||c1.end_snap_id||','||'dbid     =>'||c1.dbid||'); ' );
dbms_output.put_line('dbms_swrf_internal.clear_awr_dbid;');
dbms_output.put_line('end; ');
dbms_output.put_line('/');
end if;
end loop;
end;
/


=================================!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


Gobal report



set head off  pages 0  lines 132  echo off  feedback off


VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBERS varchar2(20);
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
select listagg (INSTANCE_NUMBER,',') within group (order by INSTANCE_NAME) into :INST_NUMBERS from gv$instance;

-- awr text.sql
select output from table(dbms_workload_repository.awr_global_report_text(:DID,:INST_NUMBERS,:BgnSnap,:EndSnap))
/

-- awr html.sql
select output from table(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML(:DID,:INST_NUMBERS,:BgnSnap,:EndSnap))
/



ADDM


VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot where DBID= (select  DBID from v$database);
exec select max(snap_id) into :EndSnap from dba_hist_snapshot where DBID= (select  DBID from v$database);
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;


DECLARE
  task_name VARCHAR2(30) := 'SYSTEM_ADDM';
  task_desc VARCHAR2(30) := 'ADDM Feature Test';
  task_id   NUMBER;
BEGIN
  select count(*)
    into task_id
    from dba_advisor_tasks
   where task_name = 'SYSTEM_ADDM';
  if task_id = 0 then
    dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
  else
    dbms_advisor.reset_task(task_name => 'SCOTT_ADDM');
  end if;
  dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'START_SNAPSHOT', :BgnSnap);
  dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'END_SNAPSHOT', :EndSnap);
  dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'INSTANCE', :INST_NUMBER);
  dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'DB_ID', :DID);
  dbms_advisor.execute_task('SYSTEM_ADDM');
END;
/

select dbms_advisor.get_task_report('SYSTEM_ADDM', 'TEXT', 'ALL') from dual;





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

mini Awr report 

http://anuj-singh.blogspot.com/2023/              

Oracle Mini Awr report ....



Oracle DBA

anuj blog Archive