Query to find buffer cache details of data, rollback information

col class form A10
select decode(greatest(class,10),10,decode(class,1,'Data',2 ,'Sort',4,'Header',to_char(class)),'Rollback') "Class",
sum(decode(bitand(flag,1),1,0,1)) "Not Dirty",
sum(decode(bitand(flag,1),1,1,0)) "Dirty",
sum(dirty_queue) "On Dirty",
count(*) "Total"
from x$bh
group by decode(greatest(class,10),10,decode(class,1,'Data',2 ,'Sort',4,'Header',to_char(class)),'Rollback')
/

Note -> This is query on Oracle internal table x$

Query to find segment details initial,next,pctfree, pctincrease,max extents

Note - Resource intensive query, use this during less load, or add criteria's according to need.

set pagesize 25
set linesize 100
col owner form a10
col segment_name head "Segment" form a30
col segment_type head "Type" form a5 trunc
col blocks form 999999
col extents form 9999
col initial_extent head "Initial|in MB" form 999.99
col next_extent head "Next|in MB" form 999.99
col pct_increase head "%|Inc" form 99
col max_extents head "Max|Extents"

select owner,segment_name,segment_type,blocks,extents,(initial_extent/1048576) initial_extent,
       (next_extent/1048576) next_extent,pct_increase,max_extents
from dba_segments
where (owner,segment_name) in(
select owner,segment_name
from dba_extents
group by owner,segment_name
having sum(blocks)>1000)
order by 1,4 desc
/

Query to find Session details from Process ID (if we have the details of OS level Process ID)

col cprogram form a30 trunc head "Client|Program"
col sprogram form a30 trunc head "Server|Program"
col sid form 999
col pid form 999
col process head "Client|Process|ID" form a10
col spid head "Oracle|Background|ProcessID" form 99999
select a.sid,b.pid,a.program cprogram,a.process,b.program sprogram,b.spid
from v$session a,v$process b
where a.paddr(+)=b.addr
and b.spid='&1'
/

Query to find archive log details with location, completion time,checkpint info

bre on comp_day
col sequence# head "Seq#" form 9999999999999999
col comp_day head "Archived|Day" form a9
col comp_time head "Arch-|ived|Time" form a5
col name head "Archive File Name" form a45
col first_change# head "First|Change" form 9999999999999999999999999
col next_change# head "Next|Change" form 9999999999999999999999999
select sequence#,to_char(completion_time,'dd-mon-yy') comp_day,
to_char(completion_time,'hh24:mi') comp_time,
name,first_change#,next_change#
from v$archived_log
order by 1
/
cle bre

Query to find archival size generated each day

select sum(blocks*block_size)/1048576 "archsize MB", to_char(COMPLETION_TIME, 'DD-Mon') date1
from v$archived_log
group by to_char(COMPLETION_TIME, 'DD-Mon')
order by to_char(COMPLETION_TIME, 'DD-Mon')
/

Query to find no. of archives generated each hour on each day

SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from
v$log_history
where to_date(first_time) > sysdate -20
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time);

Query to find number of archives generated each day

select to_char(completion_time,'dd-mon-yyyy'),count(*) from v$archived_log group by to_char(completion_time,'dd-mon-yyyy') order by to_char(completion_time,'dd-mon-yyyy')
/

Query to find which session is accessing the object

select sid from v$access where object='&object_name'
/