Oracle session report (waits CPU used by this session etc .. )

set linesize 300 pagesize 200 
column statname for a35 heading 'statistic' truncate 
column value    for 999,999,999,999 heading 'value' 
col sid_serial  for a20 
col kill        for a15
SELECT 
''''||ss.sid ||','|| ss.serial#||',@'||ss.inst_id ||'''' kill,ss.username username, sn.name statname, st.value value ,sql_id FROM gv$sesstat st, v$statname sn, gv$session ss 
WHERE 
--st.sid = user_sid -- AND 
sn.name IN ('CPU used by this session', 'OS Wait-cpu (latency) time','session logical reads', 'physical reads') 
AND sn.statistic# = st.statistic# 
and ss.sid = st.sid and ss.inst_id = st.inst_id
and ss.USERNAME is not null 
and ss.sql_id is not null
ORDER BY value DESC ;

===

set pages 1000 lines 1000
col OSPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a15
col PROGRAM for a23
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
col EVENT for a27
select * from (
select p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.con_id,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.MACHINE,ss.status,ss.BLOCKING_SESSION blk_sid,ss.BLOCKING_SESSION_STATUS blk_status,
se.VALUE/100 cpu_usage_sec,ss.event
from
v$session ss,
v$sesstat se,
v$statname sn,
v$process p
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
--and ss.username !='SYS' 
and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);

SQL Wise CPU Usage

col cpu_usage_sec form 99990 heading "CPU in Seconds"
select * from (
select
(se.SID),substr(q.sql_text,80),ss.module,ss.status,se.VALUE/100 cpu_usage_sec
from v$session ss,v$sesstat se,v$statname sn, v$process p, v$sql q
where
se.STATISTIC# = sn.STATISTIC#
AND ss.sql_address = q.address
AND ss.sql_hash_value = q.hash_value
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.username !='SYS'
and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);



set lines 1000
col OSPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a15
col PROGRAM for a20
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
col kill for a17
select * from (
select 
--ss.inst_id,
''''||ss.sid ||','|| ss.serial#||',@'||ss.inst_id ||'''' kill,
p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.username,ss.program,ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_seconds
from
gv$session ss,
gv$sesstat se,
gv$statname sn,
gv$process p
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
--and ss.username !='SYS' 
--and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc)
where rownum <16;




set pages 1000 lines 1000
col ospid 		for a06
col sid 		for 99999
col serial# 	for 999999
col sql_id 		for a14
col username 	for a15
col program 	for a23
col module 		for a18
col osuser 		for a10
col machine 	for a25
select * from (
select p.spid "ospid",
(se.sid),ss.serial#,ss.sql_id,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.machine,ss.status,ss.blocking_session blk_sid,ss.blocking_session_status blk_status,
se.value/100 cpu_usage_sec,ss.event
from
v$session ss,v$sesstat se,v$statname sn,v$process p
where 1=1
and se.statistic# = sn.statistic#
and name like '%CPU used by this session%'
and se.SID = ss.SID
-- and ss.username !='SYS' 
--and ss.status='ACTIVE'
--and ss.username is not null
and ss.paddr=p.addr 
and value > 0
order by se.value desc)
;

set pages 500 lines 500
col sid for a05
col serial for a06
col program for a15
col event for a28
col username for a15
col sql_text for a65
select substr(v2.sid,1,5) "SID",substr(v2.serial#,1,5) "SERIAL",substr(v2.username,1,15) "Username"
,substr(program,1,45) "Program",v2.event event,v1.sql_id,sql_text 
from gv$sqlarea v1,gv$session v2 
where v1.address= v2.sql_address 
and v1.hash_value = v2.sql_hash_value 
and v1.inst_id = v2.inst_id
order by 1 asc
;

set pages 100

set lines 1000

col SPID heading 'PID' for a06

col SID for 99999

col SERIAL# heading 'serial' for 999999

col SQL_ID for a14

col USERNAME for a10

col PROGRAM for a20

col MODULE for a18

col OSUSER for a07

col MACHINE for a20

select p.spid,s.sid,s.serial#,s.sql_id,s.username,s.status,s.program,s.module,s.osuser,s.machine,s.event

from gv$session s, gv$process p 

where s.paddr=p.addr 

and p.spid=&spid;

               

====

SET LINESIZE 200
	SET PAGESIZE 200
	COLUMN metric_name FORMAT a25
	COLUMN metric_unit FORMAT a20
	COLUMN awr_cpu_usage FORMAT a13
	ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
	SELECT *
	FROM (
	SELECT instance_number
	, LAG(snap_id, 1, 0) OVER(PARTITION BY dbid, instance_number ORDER BY snap_id) first_snap_id
	, snap_id second_snap_id
	, begin_time
	, end_time
	, metric_name
	, metric_unit
	, ROUND(average, 2) || '%' awr_cpu_usage
	FROM dba_hist_sysmetric_summary
	WHERE metric_name = 'Host CPU Utilization (%)'
	ORDER BY instance_number
	, first_snap_id
	)
	WHERE first_snap_id <> 0
	; INSTANCE_NUMBER FIRST_SNAP_ID SECOND_SNAP_ID BEGIN_TIME END_TIME METRIC_NAME METRIC_UNIT AWR_CPU_USAGE --------------- ------------- -------------- ------------------- ------------------- ------------------------- -------------------- ------------- 1 2569 2570 2023-04-22 06:59:41 2023-04-22 07:59:40 Host CPU Utilization (%) % Busy/(Idle+Busy) 5.66% 1 2570 2571 2023-04-22 07:59:40 2023-04-22 08:59:40 Host CPU Utilization (%) % Busy/(Idle+Busy) 3.86% 1 2571 2572 2023-04-22 08:59:40 2023-04-22 10:00:41 Host CPU Utilization (%) % Busy/(Idle+Busy) 3.99% 1 2572 2573 2023-04-22 10:00:41 2023-04-22 11:00:41 Host CPU Utilization (%) % Busy/(Idle+Busy) 4.24% 1 2573 2574 2023-04-22 11:00:41 2023-04-22 11:59:41 Host CPU Utilization (%) % Busy/(Idle+Busy) 4.36% 1 2574 2575 2023-04-22 11:59:41 2023-04-22 12:59:41 Host CPU Utilization (%) % Busy/(Idle+Busy) 4.7% 1 2575 2576 2023-04-22 12:59:41 2023-04-22 13:59:40 Host CPU Utilization (%) % Busy/(Idle+Busy) 4.93% 204 rows selected.

=====
col kill for a15
col CLIENT_INFO for a20
col USERNAME  for a20 
select * from
(
select username,''''||sid ||','|| serial#||',@'||inst_id ||'''' kill,sid,sql_id,prev_sql_id,
       round((cpu_usage/(
                        select sum(value) total_cpu_usage
                          from gv$sesstat t
                         inner join gv$session  s on ( t.sid = s.sid )
                         inner join gv$statname n on ( t.statistic# = n.statistic# )
                         where n.name like '%CPU used by this session%'
                           and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24
                        ))*100,2) cpu_usage_per_cent,
       module_info,client_info 
  from
(
select nvl(s.username,'Oracle Internal Proc.') username,s.sid,s.serial#,s.inst_id,t.value cpu_usage, nvl(s.module, s.program) module_info, decode(s.osuser,'oracle', s.client_info, s.osuser) client_info,
sql_id,prev_sql_id
  from gv$sesstat t
       inner join gv$session  s on ( t.sid = s.sid )
       inner join gv$statname n on ( t.statistic# = n.statistic# )
 where n.name like '%CPU used by this session%'
   and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24
) s1
)
order by cpu_usage_per_cent desc;

===

set linesize 500
col sql_text for a80
col cpu_usage_sec form 99990 heading "CPU in Seconds"
select * from (
select
(se.SID),q.sql_id,substr(q.sql_text,80) sql_text,ss.module,ss.status,se.VALUE/100 cpu_usage_sec
from v$session ss,v$sesstat se,
v$statname sn, v$process p, v$sql q
where
se.STATISTIC# = sn.STATISTIC#
AND ss.sql_address = q.address
AND ss.sql_hash_value = q.hash_value
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
--and ss.username !='SYS'
and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);

=======

set linesize 300
col day for a11
select
to_char(begin_time,'DD-MON-YY') Day,
round(max(decode(to_char(begin_time,'HH24'),'00',maxval,NULL)),2) "00",
round(max(decode(to_char(begin_time,'HH24'),'01',maxval,NULL)),2) "01",
round(max(decode(to_char(begin_time,'HH24'),'02',maxval,NULL)),2) "02",
round(max(decode(to_char(begin_time,'HH24'),'03',maxval,NULL)),2) "03",
round(max(decode(to_char(begin_time,'HH24'),'04',maxval,NULL)),2) "04",
round(max(decode(to_char(begin_time,'HH24'),'05',maxval,NULL)),2) "05",
round(max(decode(to_char(begin_time,'HH24'),'06',maxval,NULL)),2) "06",
round(max(decode(to_char(begin_time,'HH24'),'07',maxval,NULL)),2) "07",
round(max(decode(to_char(begin_time,'HH24'),'08',maxval,NULL)),2) "08",
round(max(decode(to_char(begin_time,'HH24'),'09',maxval,NULL)),2) "09",
round(max(decode(to_char(begin_time,'HH24'),'10',maxval,NULL)),2) "10",
round(max(decode(to_char(begin_time,'HH24'),'11',maxval,NULL)),2) "11",
round(max(decode(to_char(begin_time,'HH24'),'12',maxval,NULL)),2) "12",
round(max(decode(to_char(begin_time,'HH24'),'13',maxval,NULL)),2) "13",
round(max(decode(to_char(begin_time,'HH24'),'14',maxval,NULL)),2) "14",
round(max(decode(to_char(begin_time,'HH24'),'15',maxval,NULL)),2) "15",
round(max(decode(to_char(begin_time,'HH24'),'16',maxval,NULL)),2) "16",
round(max(decode(to_char(begin_time,'HH24'),'17',maxval,NULL)),2) "17",
round(max(decode(to_char(begin_time,'HH24'),'18',maxval,NULL)),2) "18",
round(max(decode(to_char(begin_time,'HH24'),'19',maxval,NULL)),2) "19",
round(max(decode(to_char(begin_time,'HH24'),'20',maxval,NULL)),2) "20",
round(max(decode(to_char(begin_time,'HH24'),'21',maxval,NULL)),2) "21",
round(max(decode(to_char(begin_time,'HH24'),'21',maxval,NULL)),2) "22",
round(max(decode(to_char(begin_time,'HH24'),'21',maxval,NULL)),2) "23"
from     dba_hist_sysmetric_SUMMARY A 
where BEGIN_TIME > sysdate - 7
and metric_name like 'Host CPU Utilization%'
--and  A.METRIC_NAME in('Average Active Sessions') 
group by to_char(begin_time,'DD-MON-YY')
order by 1
;





DAY                 00         01         02         03         04         05         06         07         08         09         10         11      12         13         14         15         16         17         18         19         20         21         22         23
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
07-JUL-23                                                                                                 33.5      40.53      47.96      45.15      43.84   43.65      41.18      39.09      38.59      38.64       31.5      47.73      49.27      54.92      54.92      54.92
08-JUL-23        25.24      16.35      17.24      19.12      18.15        100      20.54      19.42      23.73      25.46      31.01      31.27      29.57   33.55       30.2      30.01      26.34      28.52      29.12      40.59      53.57      43.98      43.98      43.98

https://github.com/khailey-zz/ashmasters/commit/2963230a03e8b8fbc8b288e8829c08066206907d

set linesize 400

with AASIO as (
           select
              class, sum(AAS) AAS, begin_time, end_time
              from (
                 select
                 decode(n.wait_class,'User I/O','User I/O',
                                     'Commit','Commit',
                                     'Wait')                               CLASS,
                 sum(round(m.time_waited/m.INTSIZE_CSEC,3))  AAS,
                 -- normalize the begin/end times between instances
                 -- not perfect but I think close enough for general trends
                 -- should never be more than 2 minutes variance assuming 3+ instances
                 -- 1 minute variance for 2 instances
                 min(BEGIN_TIME) over (partition by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')) begin_time ,
                 min(END_TIME) over (partition by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')) end_time
           from  gv$waitclassmetric  m,
                 gv$system_wait_class n
           where m.wait_class_id=n.wait_class_id
             and n.wait_class != 'Idle'
                                 and n.inst_id = m.inst_id
           group by  decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait'), BEGIN_TIME, END_TIME
           order by begin_time
         )
         group by class, begin_time, end_time
),
CORES as (
   select cpu_core_count from dba_cpu_usage_statistics where timestamp= (select max(timestamp) from dba_cpu_usage_statistics)
),
AASSTAT as (
             select class, aas, begin_time, end_time from aasio
          union
             select 'CPU_ORA_CONSUMED'                                     CLASS,
                    round(sum(value)/100,3)                                     AAS,
                 min(BEGIN_TIME) begin_time,
                 max(END_TIME) end_time
             from gv$sysmetric
             where metric_name='CPU Usage Per Sec'
               and group_id=2
            group by metric_name
          union
           select 'CPU_OS'                                                CLASS ,
                    round((prcnt.busy*parameter.cpu_count)/100,3)          AAS,
                 BEGIN_TIME ,
                 END_TIME
            from
              ( select sum(value) busy, min(BEGIN_TIME) begin_time,max(END_TIME) end_time from gv$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,
              ( select sum(value) cpu_count from gv$parameter where name='cpu_count' )  parameter
          union
             select
               'CPU_ORA_DEMAND'                                            CLASS,
               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS,
               cast(min(SAMPLE_TIME) as date) BEGIN_TIME ,
               cast(max(SAMPLE_TIME) as date) END_TIME
             from gv$active_session_history ash
              where SAMPLE_TIME >= (select min(BEGIN_TIME) begin_time from gv$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 )
               and SAMPLE_TIME < (select max(END_TIME) end_time from gv$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 )
)
select
		 sysdate timestamp,
       to_char(BEGIN_TIME,'YYYY-MM-DD HH24:MI:SS') BEGIN_TIME,
       to_char(END_TIME,'YYYY-MM-DD HH24:MI:SS') END_TIME,
       cpu.cpu_core_count cores,
       ( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) +
       CPU_ORA_CONSUMED +
        decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL,
       decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS,
       CPU_ORA_CONSUMED CPU_ORA,
       decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT,
       COMMIT,
       READIO,
       WAIT
from (
select
       min(BEGIN_TIME) BEGIN_TIME,
       max(END_TIME) END_TIME,
       sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED,
       sum(decode(CLASS,'CPU_ORA_DEMAND'  ,AAS,0)) CPU_ORA_DEMAND,
       sum(decode(CLASS,'CPU_OS'          ,AAS,0)) CPU_OS,
       sum(decode(CLASS,'Commit'          ,AAS,0)) COMMIT,
       sum(decode(CLASS,'User I/O'        ,AAS,0)) READIO,
       sum(decode(CLASS,'Wait'            ,AAS,0)) WAIT
from AASSTAT
)
, cores cpu
/

Oracle wait report

wait.sql

Author : Daniel W. Fink

Oracle Response Time report


SET LINESIZE 132 PAGESIZE 100 FEEDBACK OFF
COLUMN time_sum NOPRINT
COLUMN total_time NOPRINT
COLUMN wait_class FORMAT A15 HEADING 'Class'
COLUMN event FORMAT A40 HEADING 'Event'
COLUMN wait_seconds FORMAT 999,999,999,999.99 HEADING 'Wait Seconds'
COLUMN wait_pct FORMAT 99.999999 HEADING '% of T' NOPRINT
COLUMN nonidle_pct FORMAT 99.999999 HEADING '% of R'

BREAK ON wait_class SKIP PAGE
COMPUTE SUM OF wait_seconds nonidle_pct ON wait_class

TTITLE LEFT "*** Response Time Components by Class ***"

SELECT vswc.time_waited time_sum,
ven.wait_class,
vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
instime.time_waited total_time,
(ROUND((vse.time_waited_micro/1000000),6)/(instime.time_waited/100))*100 wait_pct,
TO_NUMBER(DECODE(vswc.wait_class, 'Idle', NULL, 'Network', NULL,
(ROUND((vse.time_waited_micro/1000000),6)/
((instime.time_waited - idletime.time_waited)/100))*100)) nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
UNION
SELECT vss.value time_sum,
'CPU Utilization' wait_class,
NULL,
vss.value/100 wait_seconds,
instime.time_waited total_time,
(ROUND((vss.value/100),6)/(instime.time_waited/100))*100 wait_pct,
((ROUND((vss.value/100),6)/((instime.time_waited - idletime.time_waited)/100))*100) nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE vss.name = 'CPU used when call started'
ORDER BY time_sum, wait_class, wait_seconds DESC
/

CLEAR BREAK
CLEAR COMPUTE

TTITLE LEFT "*** Response Time Components by Time ***"

SELECT vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
(ROUND((vse.time_waited_micro/1000000),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
AND vswc.wait_class NOT IN ('Idle', 'Network')
UNION
SELECT 'CPU Utilization' wait_class,
vss.value/100 wait_seconds,
(ROUND((vss.value/100),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE vss.name = 'CPU used when call started'
ORDER BY wait_seconds DESC
/



*** Response Time Components by Time ***
Event Wait Seconds % of R
---------------------------------------- ------------------- ----------
control file parallel write 34,523.56 29.613725
latch: shared pool 18,121.77 15.544544
CPU Utilization 14,916.65 12.795248
db file async I/O submit 10,522.31 9.025855
os thread startup 9,933.96 8.521180
db file sequential read 7,489.39 6.424267
log file parallel write 7,079.45 6.072632
RMAN backup & recovery I/O 4,572.45 3.922174
log file sync 2,991.17 2.565779

Oracle awr matrix report

awr snapshot report 
awr snapshot matrix report


snap info !!!!!



Author      : Daniel W. Fink 


awr_snap_wkly_matrix.sql


COLUMN sort_ord                        NOPRINT
COLUMN snap_week                       NOPRINT

DEFINE awr_dbid = &1
DEFINE awr_instid = &2

SET HEADING off PAGESIZE 0
TTITLE off

PROMPT *** Snapshots in AWR repository for Database &usr_db_name Instance &usr_inst_name
PROMPT *** Weekly by hour (intermediate snapshots not listed)

SELECT     DISTINCT 001 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , NULL	         hour_of_day
         , 'Sunday'	 sunday_snapid
         , 'Monday'	 monday_snapid
         , 'Tuesday'	 tuesday_snapid
         , 'Wednesday'	 wednesday_snapid
         , 'Thursday'	 thursday_snapid
         , 'Friday'	 friday_snapid
         , 'Saturday'	 saturday_snapid
FROM       sys.wrm$_snapshot
UNION ALL
SELECT     010 sort_ord
         , TO_CHAR(s.first_sunday, 'YYYYMMDD')	snap_week
         , NULL hour_of_day
         , TO_CHAR(s.first_sunday,   'MM/DD/YY') sunday_snapid
         , TO_CHAR(s.first_sunday+1, 'MM/DD/YY') monday_snapid
         , TO_CHAR(s.first_sunday+2, 'MM/DD/YY') tuesday_snapid
         , TO_CHAR(s.first_sunday+3, 'MM/DD/YY') wednesday_snapid
         , TO_CHAR(s.first_sunday+4, 'MM/DD/YY') thursday_snapid
         , TO_CHAR(s.first_sunday+5, 'MM/DD/YY') friday_snapid
         , TO_CHAR(s.first_sunday+6, 'MM/DD/YY') saturday_snapid
FROM       ( SELECT     NEXT_DAY(MIN(end_interval_time) - 7, 'SUNDAY') first_sunday
             FROM       sys.wrm$_snapshot
             WHERE      dbid = &awr_dbid
               AND      instance_number = &awr_instid
           ) s
UNION ALL
SELECT     011 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , NULL hour_of_day
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) sunday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),2,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) monday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),3,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) tuesday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),4,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) wednesday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),5,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) thursday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),6,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) friday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),7,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) saturday_of_week
FROM       sys.wrm$_snapshot
WHERE      dbid = &awr_dbid
  AND      instance_number = &awr_instid
GROUP BY   TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
HAVING     MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) IS NOT NULL
UNION ALL
SELECT     DISTINCT 020 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , '---------'   hour_of_day
         , '---------'	 sunday_snapid
         , '---------'	 monday_snapid
         , '---------'	 tuesday_snapid
         , '---------'	 wednesday_snapid
         , '---------'	 thursday_snapid
         , '---------'	 friday_snapid
         , '---------'	 saturday_snapid
FROM       sys.wrm$_snapshot
WHERE      dbid = &awr_dbid
  AND      instance_number = &awr_instid
UNION ALL
SELECT     030 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , TO_CHAR(end_interval_time, 'hh24')||':00' hour_of_day
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),1,snap_id,NULL)),'999999') sunday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),2,snap_id,NULL)),'999999') monday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),3,snap_id,NULL)),'999999') tuesday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),4,snap_id,NULL)),'999999') wednesday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),5,snap_id,NULL)),'999999') thursday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),6,snap_id,NULL)),'999999') friday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),7,snap_id,NULL)),'999999') saturday_of_week
FROM       sys.wrm$_snapshot
WHERE      dbid = &awr_dbid
  AND      instance_number = &awr_instid
GROUP BY   TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
         , TO_CHAR(end_interval_time, 'hh24')||':00'
UNION ALL
SELECT     DISTINCT 999 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , NULL	 hour_of_day
         , NULL	 sunday_snapid
         , NULL	 monday_snapid
         , NULL	 tuesday_snapid
         , NULL	 wednesday_snapid
         , NULL	 thursday_snapid
         , NULL	 friday_snapid
         , NULL	 saturday_snapid
FROM       sys.wrm$_snapshot
WHERE      dbid = &awr_dbid
  AND      instance_number = &awr_instid
ORDER BY   snap_week
         , sort_ord
         , hour_of_day
/

SET HEADING ON






apt-rdbms-01.aptus.co.uk:APTDB\sys> @awr_snap_wkly_matrix.sql
*** Snapshots in AWR repository for Database APTDB Instance aptdb
*** Weekly by hour (intermediate snapshots not listed)
          Sunday    Monday    Tuesday   Wednesday Thursday  Friday    Saturday
          08/21/11  08/22/11  08/23/11  08/24/11  08/25/11  08/26/11  08/27/11
--------- --------- --------- --------- --------- --------- --------- ---------
00:00                                      1920      1944      1968
01:00                            1897      1921      1945      1969
02:00                            1898      1922      1946      1970
03:00                            1899      1923      1947      1971
04:00                            1900      1924      1948      1972
05:00                            1901      1925      1949      1973
06:00                            1902      1926      1950      1974
07:00                            1903      1927      1951      1975
08:00                            1904      1928      1952      1976
09:00                            1905      1929      1953      1977
10:00                            1906      1930      1954      1978
11:00                            1907      1931      1955      1979
12:00                            1908      1932      1956      1980
13:00                            1909      1933      1957      1981
14:00                            1910      1934      1958      1982
15:00                            1911      1935      1959      1983
16:00                            1912      1936      1960      1984
17:00                            1913      1937      1961      1985
18:00                            1914      1938      1962      1986
19:00                            1915      1939      1963      1987
20:00                            1916      1940      1964      1988
21:00                            1917      1941      1965      1989
22:00                            1918      1942      1966      1990
23:00                            1919      1943      1967      1991

  =========
  
 
SET HEADING off PAGESIZE 0 linesize 200
COLUMN sort_ord NOPRINT
SELECT DISTINCT 001 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, 'Sunday' sunday_snapid
, 'Monday' monday_snapid
, 'Tuesday' tuesday_snapid
, 'Wednesday' wednesday_snapid
, 'Thursday' thursday_snapid
, 'Friday' friday_snapid
, 'Saturday' saturday_snapid
FROM sys.wrm$_snapshot
UNION ALL
SELECT 010 sort_ord
, TO_CHAR(s.first_sunday, 'YYYYMMDD') snap_week
, NULL hour_of_day
, TO_CHAR(s.first_sunday, 'MM/DD/YY') sunday_snapid
, TO_CHAR(s.first_sunday+1, 'MM/DD/YY') monday_snapid
, TO_CHAR(s.first_sunday+2, 'MM/DD/YY') tuesday_snapid
, TO_CHAR(s.first_sunday+3, 'MM/DD/YY') wednesday_snapid
, TO_CHAR(s.first_sunday+4, 'MM/DD/YY') thursday_snapid
, TO_CHAR(s.first_sunday+5, 'MM/DD/YY') friday_snapid
, TO_CHAR(s.first_sunday+6, 'MM/DD/YY') saturday_snapid
FROM ( SELECT NEXT_DAY(MIN(end_interval_time) - 7, 'SUNDAY') first_sunday
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
) s
UNION ALL
SELECT 011 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) sunday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),2,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) monday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),3,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) tuesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),4,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) wednesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),5,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) thursday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),6,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) friday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),7,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
HAVING MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) IS NOT NULL
UNION ALL
SELECT DISTINCT 020 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, '---------' hour_of_day
, '---------' sunday_snapid
, '---------' monday_snapid
, '---------' tuesday_snapid
, '---------' wednesday_snapid
, '---------' thursday_snapid
, '---------' friday_snapid
, '---------' saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
UNION ALL
SELECT 030 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, TO_CHAR(end_interval_time, 'hh24')||':00' hour_of_day
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),1,snap_id,NULL)),'999999') sunday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),2,snap_id,NULL)),'999999') monday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),3,snap_id,NULL)),'999999') tuesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),4,snap_id,NULL)),'999999') wednesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),5,snap_id,NULL)),'999999') thursday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),6,snap_id,NULL)),'999999') friday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),7,snap_id,NULL)),'999999') saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE')
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD'), TO_CHAR(end_interval_time, 'hh24')||':00'
UNION ALL
SELECT DISTINCT 999 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, NULL sunday_snapid
, NULL monday_snapid
, NULL tuesday_snapid
, NULL wednesday_snapid
, NULL thursday_snapid
, NULL friday_snapid
, NULL saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
ORDER BY snap_week, sort_ord , hour_of_day
/



20211003           Sunday    Monday    Tuesday   Wednesday Thursday  Friday    Saturday
20211003           10/03/21  10/04/21  10/05/21  10/06/21  10/07/21  10/08/21  10/09/21
20211003 --------- --------- --------- --------- --------- --------- --------- ---------
20211003 00:00                                     36504     36528     36552     36576
20211003 01:00                                     36505     36529     36553     36577
20211003 02:00                                     36506     36530     36554     36578

Oracle index info on table

Author : Daniel W. Fink




SET VERIFY OFF PAGESIZE 4000 LINESIZE 135
DEFINE owner_table = &1


COLUMN spoolname FORMAT A50 NEW_VALUE spool_name NOPRINT

SELECT 'tab_idx_info_hstry_'||UPPER('&&owner_table')||'_'||TO_CHAR(SYSDATE, 'YYYYMMDDhh24miss')||'.log' spoolname
FROM dual
/

SPOOL &&spool_name

COLUMN last_analyzed_time FORMAT A17 HEADING 'Analyzed Date'
COLUMN tab_degree FORMAT 9999 HEADING 'Deg'
COLUMN tab_partitioned FORMAT A4 HEADING 'Prtn'
COLUMN tab_num_rows FORMAT 999,999,999 HEADING 'Rows'
COLUMN tab_alloc_blocks FORMAT 999,999,999 HEADING 'Allocated|Blocks'
COLUMN tab_hwm_blocks FORMAT 999,999,999 HEADING 'HWM|Blocks'
COLUMN tab_analyzed_pct FORMAT 999.99 HEADING 'Analyze|Pct'
COLUMN tab_avg_space FORMAT 99999 HEADING 'Avg Block|Free Space'
COLUMN tab_avg_row_length FORMAT 99999 HEADING 'Avg Row|Length'
COLUMN tab_monitoring FORMAT A4 HEADING 'Mntr'
COLUMN tab_column_name FORMAT A30 HEADING 'Column Name'
COLUMN tab_column_datatype FORMAT A10 HEADING 'Datatype'
COLUMN tab_column_nullable FORMAT A4 HEADING 'Null'
COLUMN tab_column_numdistinct FORMAT 999,999,999 HEADING 'Distinct|Values'
COLUMN tab_column_density FORMAT 9.999999999 HEADING 'Density'
COLUMN tab_column_numnulls FORMAT 999,999,999 HEADING 'Number|of Nulls'
COLUMN tab_column_histogram FORMAT A3 HEADING 'Hst'
COLUMN tab_column_numbuckets FORMAT 999,999 HEADING 'Buckets'
COLUMN index_name FORMAT A30 HEADING 'Index Name'
COLUMN ind_status FORMAT A7 HEADING 'Status'
COLUMN ind_unique FORMAT A3 HEADING 'Unq'
COLUMN ind_blevel FORMAT 999 HEADING 'Lvl'
COLUMN ind_leafblocks FORMAT 999,999 HEADING 'Leaf Blks'
COLUMN ind_numrows FORMAT 999,999,999 HEADING 'Indx Rows'
COLUMN ind_distinctkeys FORMAT 999,999,999 HEADING 'Dstnct Keys'
COLUMN ind_clufac FORMAT 999,999,999 HEADING 'Clstr Fctr'
COLUMN column_name FORMAT A30 HEADING 'Column Name'
COLUMN low_value FORMAT A20 HEADING 'Low Value'
COLUMN high_value FORMAT A20 HEADING 'High Value'
COLUMN tab_column_id NOPRINT

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Table Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT


SELECT TO_CHAR(t.last_analyzed, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, TO_NUMBER(t.degree) tab_degree
, t.partitioned tab_partitioned
, t.num_rows tab_num_rows
, t.blocks tab_alloc_blocks
, (t.blocks - t.empty_blocks) tab_hwm_blocks
, ROUND((t.sample_size/DECODE(t.num_rows,0,1,t.num_rows))*100,2) tab_analyzed_pct
, t.avg_space tab_avg_space
, t.avg_row_len tab_avg_row_length
, t.monitoring tab_monitoring
FROM dba_tables t
WHERE t.owner||'.'||t.table_name = UPPER('&&owner_table')
UNION ALL
SELECT TO_CHAR(th.analyzetime, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, NULL tab_degree
, NULL tab_partitioned
, th.rowcnt tab_num_rows
, th.blkcnt tab_alloc_blocks
, NULL tab_hwm_blocks
, ROUND((th.samplesize/DECODE(th.rowcnt,0,1,th.rowcnt))*100,2) tab_analyzed_pct
, NULL tab_avg_space
, th.avgrln tab_avg_row_length
, NULL tab_monitoring
FROM sys.wri$_optstat_tab_history th
WHERE th.obj# = ( SELECT o.object_id
FROM dba_objects o
WHERE o.owner||'.'||o.object_name = UPPER('&&owner_table')
AND o.object_type = 'TABLE'
)
ORDER BY last_analyzed_time DESC
/


PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Column Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT
BREAK ON tab_column_name NODUP SKIP 1

SELECT tc.column_name tab_column_name
, TO_CHAR(tc.last_analyzed, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, tc.data_type tab_column_datatype
, DECODE(tc.nullable, 'Y', NULL, tc.nullable) tab_column_nullable
, tc.num_distinct tab_column_numdistinct
, tc.density tab_column_density
, tc.num_nulls tab_column_numnulls
, DECODE(tc.histogram,'NONE',NULL,'FREQUENCY','FRQ',
'HEIGHT BALANCED','HGT',tc.histogram) tab_column_histogram
, TO_NUMBER(DECODE(tc.num_buckets,1,NULL,tc.num_buckets)) tab_column_numbuckets
, tc.column_id tab_column_id
FROM dba_tab_columns tc
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
UNION ALL
SELECT tc.column_name tab_column_name
, TO_CHAR(tch.timestamp#, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, NULL tab_column_datatype
, NULL tab_column_nullable
, tch.distcnt tab_column_numdistinct
, tch.density tab_column_density
, tch.null_cnt tab_column_numnulls
, NULL tab_column_histogram
, TO_NUMBER(DECODE(tchh.num_buckets,1,NULL,tchh.num_buckets)) tab_column_numbuckets
, tch.intcol# tab_column_id
FROM dba_tab_columns tc
, sys.wri$_optstat_histhead_history tch
, ( SELECT tchh2.intcol#
, tchh2.savtime
, count(tchh2.bucket) num_buckets
FROM sys.wri$_optstat_histgrm_history tchh2
WHERE tchh2.obj# = ( SELECT o.object_id
FROM dba_objects o
WHERE o.owner||'.'||o.object_name = UPPER('&&owner_table')
AND o.object_type = 'TABLE'
)
GROUP BY tchh2.intcol#
, tchh2.savtime
) tchh
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
AND tch.obj# = ( SELECT o.object_id
FROM dba_objects o
WHERE o.owner||'.'||o.object_name = UPPER('&&owner_table')
AND o.object_type = 'TABLE'
)
AND tch.intcol# = tc.column_id
AND tch.intcol# = tchh.intcol# (+)
AND tch.savtime = tchh.savtime (+)
ORDER BY tab_column_id, last_analyzed_time DESC, tab_column_name
/



PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT
BREAK ON index_name NODUP SKIP 1 ON ind_unique NODUP

SELECT i.index_name index_name
, TO_CHAR(i.last_analyzed, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, i.status ind_status
, DECODE(i.uniqueness,'UNIQUE','Y',NULL) ind_unique
, i.blevel ind_blevel
, i.leaf_blocks ind_leafblocks
, i.num_rows ind_numrows
, i.distinct_keys ind_distinctkeys
, i.clustering_factor ind_clufac
FROM dba_indexes i
WHERE i.table_owner||'.'||i.table_name = UPPER('&&owner_table')
UNION ALL
SELECT i.index_name index_name
, TO_CHAR(ih.analyzetime, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, NULL ind_status
, DECODE(i.uniqueness,'UNIQUE','Y',NULL) ind_unique
, ih.blevel ind_blevel
, ih.leafcnt ind_leafblocks
, ih.rowcnt ind_numrows
, ih.distkey ind_distinctkeys
, ih.clufac ind_clufac
FROM dba_indexes i
, sys.wri$_optstat_ind_history ih
WHERE i.table_owner||'.'||i.table_name = UPPER('&&owner_table')
AND ih.obj# = ( SELECT o.object_id
FROM dba_objects o
WHERE o.owner||'.'||o.object_name = UPPER(i.owner||'.'||i.index_name)
AND o.object_type = 'INDEX'
)
ORDER BY ind_unique, index_name, last_analyzed_time DESC
/

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Columns for &&owner_table
PROMPT *********************************************************************************
PROMPT

BREAK ON index_name NODUP SKIP 1 ON column_name NODUP

WITH col_hi_lo_vals AS
( SELECT tc.column_name
, tc.data_type
, TO_CHAR(tc.last_analyzed, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, tc.low_value raw_low_value
, tc.high_value raw_high_value
, SUBSTR(dump(tc.low_value), (INSTR(dump(tc.low_value),': ')+2)) date_low_val
, SUBSTR(dump(tc.high_value), (INSTR(dump(tc.high_value),': ')+2)) date_high_val
FROM dba_tab_columns tc
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
UNION ALL
SELECT tc.column_name
, tc.data_type
, TO_CHAR(tch.timestamp#, 'YYYY/MM/DD hh24:mi') last_analyzed_time
, tch.lowval raw_low_value
, tch.hival raw_high_value
, SUBSTR(dump(tch.lowval), (INSTR(dump(tch.lowval),': ')+2)) date_low_val
, SUBSTR(dump(tch.hival), (INSTR(dump(tch.hival),': ')+2)) date_high_val
FROM dba_tab_columns tc
, sys.wri$_optstat_histhead_history tch
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
AND tch.obj# = ( SELECT o.object_id
FROM dba_objects o
WHERE o.owner||'.'||o.object_name = UPPER('&&owner_table')
AND o.object_type = 'TABLE'
)
AND tch.intcol# = tc.column_id
),
col_hi_lo_vals_translated AS
( SELECT column_name
, data_type
, last_analyzed_time
, CASE when data_type = 'DATE'
THEN
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,1)-100, '09')|| -- low_century
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,2)-100, '09')|| -- low_year
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,3),'09')|| -- low_month
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,4),'09')|| -- low_day
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,5)-1,'09')|| -- low_hour24
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,6)-1,'09')|| -- low_minute
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,7)-1,'09') -- low_second
ELSE
NULL
END low_date
, CASE when data_type = 'DATE'
THEN
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,1)-100, '09')|| -- high_century
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,2)-100, '09')|| -- high_year
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,3), '09')|| -- high_month
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,4), '09')|| -- high_day
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,5)-1, '09')|| -- high_hour24
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,6)-1, '09')|| -- high_minute
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,7)-1, '09') -- high_second
ELSE
NULL
END high_date
, CASE WHEN data_type = 'NUMBER'
THEN
utl_raw.cast_to_number(raw_low_value)
ELSE
NULL
END low_num
, CASE WHEN data_type = 'NUMBER'
THEN
utl_raw.cast_to_number(raw_high_value)
ELSE
NULL
END high_num
, CASE WHEN data_type LIKE '%CHAR%'
THEN
utl_raw.cast_to_varchar2(raw_low_value)
ELSE
NULL
END low_char
, CASE WHEN data_type LIKE '%CHAR%'
THEN
utl_raw.cast_to_varchar2(raw_high_value)
ELSE
NULL
END high_char
FROM col_hi_lo_vals
)
SELECT ic.index_name,
ic.column_name
, chlvt.last_analyzed_time
, CASE WHEN chlvt.data_type = 'DATE'
THEN TO_CHAR(TO_DATE(REPLACE(chlvt.low_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
WHEN chlvt.data_type = 'NUMBER'
THEN LPAD(TO_CHAR(chlvt.low_num),20)
WHEN chlvt.data_type LIKE '%CHAR%'
THEN chlvt.low_char
END low_value
, CASE WHEN chlvt.data_type = 'DATE'
THEN TO_CHAR(TO_DATE(REPLACE(chlvt.high_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
WHEN chlvt.data_type = 'NUMBER'
THEN LPAD(TO_CHAR(chlvt.high_num),20)
WHEN chlvt.data_type LIKE '%CHAR%'
THEN chlvt.high_char
END high_value
FROM dba_ind_columns ic
, col_hi_lo_vals_translated chlvt
WHERE ic.table_owner||'.'||ic.table_name = UPPER('&&owner_table')
AND ic.column_name = chlvt.column_name
ORDER BY ic.index_name, ic.column_position, chlvt.last_analyzed_time DESC
/

SPOOL off










apt-rdbms-01.aptus.co.uk:APTDB\sys> @index scott.emp





*********************************************************************************
Table Statistics for scott.emp
*********************************************************************************


Allocated HWM Analyze Avg Block Avg Row
Analyzed Date Deg Prtn Rows Blocks Blocks Pct Free Space Length Mntr
----------------- ----- ---- ------------ ------------ ------------ ------- ---------- ------- ----
2011/07/07 22:00 1 NO 14 5 5 100.00 0 38 YES


*********************************************************************************
Column Statistics for scott.emp
*********************************************************************************


Distinct Number
Column Name Analyzed Date Datatype Null Values Density of Nulls Hst Buckets
------------------------------ ----------------- ---------- ---- ------------ ------------ ------------ --- --------
EMPNO 2011/07/07 22:00 NUMBER N 14 .071428571 0

ENAME 2011/07/07 22:00 VARCHAR2 14 .071428571 0

JOB 2011/07/07 22:00 VARCHAR2 5 .200000000 0

MGR 2011/07/07 22:00 NUMBER 6 .166666667 1

HIREDATE 2011/07/07 22:00 DATE 13 .076923077 0

SAL 2011/07/07 22:00 NUMBER 12 .083333333 0

COMM 2011/07/07 22:00 NUMBER 4 .250000000 10

DEPTNO 2011/07/07 22:00 NUMBER 3 .035714286 0 FRQ 3



*********************************************************************************
Index Statistics for scott.emp
*********************************************************************************


Index Name Analyzed Date Status Unq Lvl Leaf Blks Indx Rows Dstnct Keys Clstr Fctr
------------------------------ ----------------- ------- --- ---- --------- ------------ ------------ ------------
PK_EMP 2011/07/07 22:00 VALID Y 0 1 14 14 1



*********************************************************************************
Index Columns for scott.emp
*********************************************************************************


Index Name Column Name Analyzed Date Low Value High Value
------------------------------ ------------------------------ ----------------- -------------------- --------------------
PK_EMP EMPNO 2011/07/07 22:00 7369 7934

Oracle Role detail report

Oracle role detail report


Author : Daniel W. Fink


SET LINESIZE 300 PAGESIZE 50000 FEEDBACK OFF ECHO OFF VERIFY OFF SHOWMODE OFF TRIMSPOOL ON
COLUMN grantee_name FORMAT A30 HEADING 'User Name'
COLUMN role_path FORMAT A50 HEADING 'Role Hierarchy'
COLUMN priv_obj_name FORMAT A60 HEADING 'Privilege/Object Name'
COLUMN sort_order1 NOPRINT
COLUMN priv_path_names NOPRINT
COLUMN insert_priv FORMAT A2 HEADING 'IN'
COLUMN update_priv FORMAT A2 HEADING 'UP'
COLUMN delete_priv FORMAT A2 HEADING 'DE'
COLUMN select_priv FORMAT A2 HEADING 'SE'
COLUMN alter_priv FORMAT A2 HEADING 'AL'
COLUMN execute_priv FORMAT A2 HEADING 'EX'
COLUMN other_priv FORMAT A2 HEADING 'OT'


BREAK ON grantee_name NODUPLICATE SKIP 1 ON role_path NODUPLICATE

SELECT 1 sort_order1,
urm.priv_path_names priv_path_names,
CASE WHEN urm.root_user_id != urm.priv_path THEN SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')-1)
ELSE urm.priv_path_names
END grantee_name,
CASE WHEN urm.root_user_id = urm.priv_path THEN 'Direct'
ELSE 'Role '||REPLACE(urm.priv_path_names, SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')))
END role_path,
spm.name priv_obj_name,
NULL insert_priv,
NULL update_priv,
NULL delete_priv,
NULL select_priv,
NULL alter_priv,
NULL execute_priv,
NULL other_priv
FROM system_privilege_map spm,
sysauth$ sa,
( SELECT CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, 1, INSTR(urm2.priv_path, '.')-1)
ELSE urm2.priv_path
END root_user_id,
CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, INSTR(urm2.priv_path, '.', -1)+1)
ELSE urm2.priv_path
END role_id,
urm2.priv_path,
urm2.priv_path_names
FROM ( SELECT TO_CHAR(u.user#) priv_path,
u.name priv_path_names
FROM user$ u
UNION
SELECT sam.priv_path priv_path,
sam.priv_path_names priv_path_names
FROM (SELECT sa.grantee#,
sa.privilege#,
LTRIM(SYS_CONNECT_BY_PATH(sa.grantee#, '.'), '.')||'.'||sa.privilege# priv_path,
LTRIM(SYS_CONNECT_BY_PATH(u1.name, '.'), '.')||'.'||u2.name priv_path_names
FROM sysauth$ sa,
user$ u1,
user$ u2
WHERE sa.privilege# > 0
AND u1.user# = sa.grantee#
AND u2.user# = sa.privilege#
CONNECT BY sa.grantee# = PRIOR sa.privilege#
ORDER SIBLINGS BY sa.privilege#
) sam
) urm2
) urm
WHERE urm.root_user_id IN ( SELECT usq.user#
FROM user$ usq
WHERE usq.type# = 1
AND usq.name NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','CTXSYS','PERFSTAT')
)
AND sa.grantee# = urm.role_id
AND sa.privilege# < 0
AND sa.privilege# = spm.privilege
UNION
SELECT 2 sort_order1,
urm.priv_path_names priv_path_names,
CASE WHEN urm.root_user_id != urm.priv_path THEN SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')-1)
ELSE urm.priv_path_names
END grantee_name,
CASE WHEN urm.root_user_id = urm.priv_path THEN 'Direct'
ELSE 'Role '||REPLACE(urm.priv_path_names, SUBSTR(urm.priv_path_names, 1, INSTR(urm.priv_path_names, '.')))
END role_path,
u.name||'.'||o.name priv_obj_name,
MAX(DECODE(tpm.name, 'INSERT', 'X', NULL)) insert_priv,
MAX(DECODE(tpm.name, 'UPDATE', 'X', NULL)) update_priv,
MAX(DECODE(tpm.name, 'DELETE', 'X', NULL)) delete_priv,
MAX(DECODE(tpm.name, 'SELECT', 'X', NULL)) select_priv,
MAX(DECODE(tpm.name, 'ALTER', 'X', NULL)) alter_priv,
MAX(DECODE(tpm.name, 'EXECUTE', 'X', NULL)) execute_priv,
MAX(DECODE(tpm.name, 'INSERT', NULL, 'UPDATE', NULL, 'DELETE', NULL,
'SELECT', NULL, 'ALTER', NULL, 'EXECUTE', NULL,
'X' )) other_priv
FROM objauth$ oa,
obj$ o,
user$ u,
table_privilege_map tpm,
( SELECT CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, 1, INSTR(urm2.priv_path, '.')-1)
ELSE urm2.priv_path
END root_user_id,
CASE WHEN INSTR(urm2.priv_path, '.') > 0 THEN SUBSTR(urm2.priv_path, INSTR(urm2.priv_path, '.', -1)+1)
ELSE urm2.priv_path
END role_id,
urm2.priv_path,
urm2.priv_path_names
FROM ( SELECT TO_CHAR(u.user#) priv_path,
u.name priv_path_names
FROM user$ u
UNION
SELECT sam.priv_path priv_path,
sam.priv_path_names priv_path_names
FROM (SELECT sa.grantee#,
sa.privilege#,
LTRIM(SYS_CONNECT_BY_PATH(sa.grantee#, '.'), '.')||'.'||sa.privilege# priv_path,
LTRIM(SYS_CONNECT_BY_PATH(u1.name, '.'), '.')||'.'||u2.name priv_path_names
FROM sysauth$ sa,
user$ u1,
user$ u2
WHERE sa.privilege# > 0
AND u1.user# = sa.grantee#
AND u2.user# = sa.privilege#
CONNECT BY sa.grantee# = PRIOR sa.privilege#
ORDER SIBLINGS BY sa.privilege#
) sam
) urm2
) urm
WHERE urm.root_user_id IN ( SELECT usq.user#
FROM user$ usq
WHERE usq.type# = 1
AND usq.name NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','CTXSYS','PERFSTAT')
)
AND oa.grantee# = urm.role_id
AND oa.obj# = o.obj#
AND o.owner# != 59
AND o.owner# = u.user#
AND oa.privilege# = tpm.privilege
GROUP BY 2, urm.root_user_id, urm.priv_path, urm.priv_path_names, u.name, o.name
ORDER BY priv_path_names, sort_order1, priv_obj_name


SPOOL allprivs.lst

/

SPOOL OFF





Role RESOURCE CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE

Oracle Table info

Table info and statistics
Get current optimizer statistics for a table


Author : Daniel W. Fink


SET VERIFY OFF PAGESIZE 400 LINESIZE 135

DEFINE owner_table = &1
COLUMN spoolname FORMAT A50 NEW_VALUE spool_name NOPRINT

SELECT 'tab_idx_info_'||UPPER('&&owner_table')||'_'||TO_CHAR(SYSDATE, 'YYYYMMDDhh24miss')||'.log' spoolname
FROM dual
/

SPOOL &&spool_name


COLUMN tab_degree FORMAT 9999 HEADING 'Deg'
COLUMN tab_partitioned FORMAT A4 HEADING 'Prtn'
COLUMN tab_num_rows FORMAT 999,999,999 HEADING 'Rows'
COLUMN tab_alloc_blocks FORMAT 999,999,999 HEADING 'Allocated|Blocks'
COLUMN tab_hwm_blocks FORMAT 999,999,999 HEADING 'HWM|Blocks'
COLUMN tab_last_analyzed_time FORMAT A17 HEADING 'Analyzed Date'
COLUMN tab_analyzed_pct FORMAT 999.99 HEADING 'Analyze|Pct'
COLUMN tab_avg_space FORMAT 99999 HEADING 'Avg Block|Free Space'
COLUMN tab_avg_row_length FORMAT 99999 HEADING 'Avg Row|Length'
COLUMN tab_monitoring FORMAT A4 HEADING 'Mntr'

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Table Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT


SELECT TO_NUMBER(t.degree) tab_degree,
t.partitioned tab_partitioned,
t.num_rows tab_num_rows,
t.blocks tab_alloc_blocks,
(t.blocks - t.empty_blocks) tab_hwm_blocks,
TO_CHAR(t.last_analyzed, 'MM/DD/YYYY hh24:mi') tab_last_analyzed_time,
ROUND((t.sample_size/DECODE(t.num_rows,0,1,t.num_rows))*100,2)
tab_analyzed_pct,
t.avg_space tab_avg_space,
t.avg_row_len tab_avg_row_length,
t.monitoring tab_monitoring
FROM dba_tables t
WHERE t.owner||'.'||t.table_name = UPPER('&&owner_table')
ORDER BY t.table_name
/



COLUMN tab_column_name FORMAT A30 HEADING 'Column Name'
COLUMN tab_column_datatype FORMAT A20 HEADING 'Datatype'
COLUMN tab_column_nullable FORMAT A10 HEADING 'Nullable?'
COLUMN tab_column_numdistinct FORMAT 999,999,999 HEADING 'Distinct|Values'
COLUMN tab_column_density FORMAT 9.99999 HEADING 'Density'
COLUMN tab_column_numnulls FORMAT 999,999,999 HEADING 'Number|of Nulls'
COLUMN tab_column_histogram FORMAT A16 HEADING 'Histogram'
COLUMN tab_column_numbuckets FORMAT 999,999 HEADING 'Buckets'

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Column Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT

SELECT tc.column_name tab_column_name,
tc.data_type tab_column_datatype,
DECODE(tc.nullable, 'N', 'NOT NULL', NULL) tab_column_nullable,
tc.num_distinct tab_column_numdistinct,
tc.density tab_column_density,
tc.num_nulls tab_column_numnulls,
DECODE(tc.histogram,'NONE', NULL, tc.histogram) tab_column_histogram,
TO_NUMBER(DECODE(tc.num_buckets,1,NULL,
tc.num_buckets)) tab_column_numbuckets
FROM dba_tab_columns tc
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
ORDER BY tc.column_id
/


PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Statistics for &&owner_table
PROMPT *********************************************************************************
PROMPT

SELECT i.index_name ind_name,
i.status ind_status,
DECODE(i.uniqueness,'UNIQUE','Y',NULL) ind_unique,
i.blevel ind_blevel,
i.leaf_blocks ind_leafblocks,
i.num_rows ind_numrows,
i.distinct_keys ind_distinctkeys,
i.clustering_factor ind_clufac,
TO_CHAR(i.last_analyzed, 'MM/DD/YYYY hh24:mi') last_analyzed_time
FROM dba_indexes i
WHERE i.table_owner||'.'||i.table_name = UPPER('&&owner_table')
ORDER BY i.uniqueness DESC, i.index_name
/

COLUMN index_name FORMAT A30 HEADING 'Index Name'
COLUMN column_name FORMAT A30 HEADING 'Column Name'
COLUMN low_value FORMAT A20 HEADING 'Low Value'
COLUMN high_value FORMAT A20 HEADING 'High Value'

BREAK ON index_name NODUP

PROMPT
PROMPT
PROMPT *********************************************************************************
PROMPT Index Columns for &&owner_table
PROMPT *********************************************************************************
PROMPT

WITH col_hi_lo_vals AS
( select tc.column_name
, tc.data_type
, tc.low_value raw_low_value
, tc.high_value raw_high_value
, SUBSTR(dump(tc.low_value), (INSTR(dump(tc.low_value),': ')+2)) date_low_val
, SUBSTR(dump(tc.high_value), (INSTR(dump(tc.high_value),': ')+2)) date_high_val
from dba_tab_columns tc
WHERE tc.owner||'.'||tc.table_name = UPPER('&&owner_table')
),
col_hi_lo_vals_translated AS
( SELECT column_name
, data_type
, CASE when data_type = 'DATE'
THEN
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,1)-100, '09')|| -- low_century
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,2)-100, '09')|| -- low_year
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,3),'09')|| -- low_month
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,4),'09')|| -- low_day
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,5)-1,'09')|| -- low_hour24
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,6)-1,'09')|| -- low_minute
TO_CHAR(REGEXP_SUBSTR(date_low_val, '[0-9]+',1,7)-1,'09') -- low_second
ELSE
NULL
END low_date
, CASE when data_type = 'DATE'
THEN
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,1)-100, '09')|| -- high_century
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,2)-100, '09')|| -- high_year
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,3), '09')|| -- high_month
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,4), '09')|| -- high_day
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,5)-1, '09')|| -- high_hour24
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,6)-1, '09')|| -- high_minute
TO_CHAR(REGEXP_SUBSTR(date_high_val, '[0-9]+',1,7)-1, '09') -- high_second
ELSE
NULL
END high_date
, CASE WHEN data_type = 'NUMBER'
THEN
utl_raw.cast_to_number(raw_low_value)
ELSE
NULL
END low_num
, CASE WHEN data_type = 'NUMBER'
THEN
utl_raw.cast_to_number(raw_high_value)
ELSE
NULL
END high_num
, CASE WHEN data_type LIKE '%CHAR%'
THEN
utl_raw.cast_to_varchar2(raw_low_value)
ELSE
NULL
END low_char
, CASE WHEN data_type LIKE '%CHAR%'
THEN
utl_raw.cast_to_varchar2(raw_high_value)
ELSE
NULL
END high_char
FROM col_hi_lo_vals
)
SELECT ic.index_name,
ic.column_name
, CASE WHEN chlvt.data_type = 'DATE'
THEN TO_CHAR(TO_DATE(REPLACE(chlvt.low_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
WHEN chlvt.data_type = 'NUMBER'
THEN LPAD(TO_CHAR(chlvt.low_num),20)
WHEN chlvt.data_type LIKE '%CHAR%'
THEN chlvt.low_char
END low_value
, CASE WHEN chlvt.data_type = 'DATE'
THEN TO_CHAR(TO_DATE(REPLACE(chlvt.high_date, ' '), 'YYYYMMDDHH24MISS'), 'MM/DD/YYYY hh24:mi:ss')
WHEN chlvt.data_type = 'NUMBER'
THEN LPAD(TO_CHAR(chlvt.high_num),20)
WHEN chlvt.data_type LIKE '%CHAR%'
THEN chlvt.high_char
END high_value
FROM dba_ind_columns ic
, col_hi_lo_vals_translated chlvt
WHERE ic.table_owner||'.'||ic.table_name = UPPER('&&owner_table')
AND ic.column_name = chlvt.column_name
ORDER BY ic.index_name, ic.column_position
/

SPOOL off

====


APTDB\sys> @table_index SCOTT.EMP






*********************************************************************************
Table Statistics for SCOTT.EMP
*********************************************************************************


Allocated HWM Analyze Avg Block Avg Row
Deg Prtn Rows Blocks Blocks Analyzed Date Pct Free Space Length Mntr
----- ---- ------------ ------------ ------------ ----------------- ------- ---------- ------- ----
1 NO 14 5 5 07/07/2011 22:00 100.00 0 38 YES



*********************************************************************************
Column Statistics for SCOTT.EMP
*********************************************************************************


Distinct Number
Column Name Datatype Nullable? Values Density of Nulls Histogram Buckets
------------------------------ -------------------- ---------- ------------ -------- ------------ ---------------- --------
EMPNO NUMBER NOT NULL 14 .07143 0
ENAME VARCHAR2 14 .07143 0
JOB VARCHAR2 5 .20000 0
MGR NUMBER 6 .16667 1
HIREDATE DATE 13 .07692 0
SAL NUMBER 12 .08333 0
COMM NUMBER 4 .25000 10
DEPTNO NUMBER 3 .03571 0 FREQUENCY 3

8 rows selected.



*********************************************************************************
Index Statistics for SCOTT.EMP
*********************************************************************************


IND_NAME Status Unq Lvl Leaf Blks Indx Rows Dstnct Keys Clstr Fctr Analyzed Date
------------------------------ ------- --- ---- --------- ------------ ------------ ------------ -----------------
PK_EMP VALID Y 0 1 14 14 1 07/07/2011 22:00



*********************************************************************************
Index Columns for SCOTT.EMP
*********************************************************************************


Index Name Column Name Low Value High Value
------------------------------ ------------------------------ -------------------- --------------------
PK_EMP EMPNO 7369 7934