Archive log Report On Hourly Basis

col day for a10
col thread# format 9999 heading "Thread"
break on thread# skip 2;
set lines 500
set pages 300
set trimspool on

select thread#, to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
count(*) Total
from v$log_history
where first_time > sysdate - &DaysMinus
group by thread#, to_char(first_time,'YYYY-MM-DD') order by 2 ;

Dba_scheduler_running_jobs

Using DBA_SCHEDULER_RUNNING_JOBS one can see jobs currently running. In the example below it shows the oracle session id and corresponding OS process id.
22:20:51 sys@TESTDB> select job_name, session_id from dba_scheduler_running_jobs;
JOB_NAME SESSION_ID
—————————— ———-
GATHER_STATS_JOB 364
1 row selected.
sys@TESTDB> select program from v$session where sid = 364;
PROGRAM
————————————————
oracle@hostname (J002)
1 row selected.
sys@TESTDB> select vs.program, spid from v$session vs, v$process vp where vs.sid = 364 and vs.paddr = vp.addr
PROGRAM SPID
———————————————— ————
oracle@hostname (J002) 1642698
1 row selected.
Oracle j000 shadow processes are DBMS_SCHEDULER sessions
sys@TESTDB> !ps -ef | grep 1642698
oracle 1642698 1 120 00:00:07 – 1071:14 ora_j002_TESTDB
oracle 13836458 11677872 2 22:23:06 pts/5 0:00 grep 1642698

Tablespace usage report (w/ autoextend)

If you use auto extending table-spaces in your Oracle environment and use Grid Control to monitor them, you will know that it does not take the auto extension into account. The following script will give you a breakout of your TS usage including the auto extension.

============================================================
set pagesize 1000
set lines 200

col tablespace_name format a32 head 'Tablespace|Name'
col total format 999.99 head 'Total(GB)'
col used format 999.99 head 'Used(GB)'
col free format 999.99 head 'Free(GB)'
col pct format 999 head 'Pct|Used'
col next_extent format 999,999 head 'Next|Extent(KB)'
col Contents format a4 head 'Cont'
col Logging format a3 head 'Log'
col Extent_Management format a3 head 'Ext|Mgt'
col Allocation_Type format a5 head 'Alloc|Type'

SELECT inn1.tablespace_name tbs_name
 ,ROUND(inn1.total/1024,2) TOTAL_SPACE_GB
 ,ROUND((inn1.total-inn1.allocated+NVL(inn2.free,0))/1024,2) FREE_SPACE_GB
,ROUND((inn1.allocated-NVL(inn2.free,0))/1024,2) USED_SPACE_GB
,ROUND(((inn1.allocated-NVL(inn2.free,0))/inn1.total)*100) USED_PERCENT
FROM
(select tablespace_name,SUM(bytes)/1024/1024 allocated,SUM(DECODE(autoextensible,'YES',maxbytes,bytes))/1024/1024 total
    FROM dba_data_files
    GROUP BY tablespace_name) inn1,
(SELECT tablespace_name, NVL(SUM(bytes)/1024/1024,0) free
FROM dba_free_space
GROUP BY tablespace_name) inn2
WHERE inn1.tablespace_name= inn2.tablespace_name(+) 
and inn1.tablespace_name = '&tbsname' ;


SELECT file_name, tablespace_name, round(bytes / 1073741842,2) SIZE_GB, autoextensible, (increment_by * 32768) / 1048576 increment_MB, maxbytes / 1073741824 max_size_GB FROM dba_data_files WHERE tablespace_name = '&tbsname';

Dba_Data_Files,Dba_Segments and Dba_Free_Space

Dba_data_files: The dba_data_files means total size of the data file.
SQL>  Select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_data_files group by tablespace_name order by 1;
This example display the total size of the data file:
SQL> Select sum(bytes)/(1024*1024*1024) from dba_data_files;
---------------------------
526.2392578125

DBA_SEGMENTS: The dba_segments means used size of the data file.
SQL> select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_segments group by tablespace_name order by 1;
This example display the total size of the segments:
SQL> select sum(bytes)/(1024*1024*1024) from dba_segments;
SUM(BYTES)/(1024*1024*1024)
---------------------------
279.55779

DBA_FREE_SPACE: The dba_free_space means free size of the data file.
SQL> select tablespace_name,sum(bytes)/(1024*1024*1024)from dba_free_space group by tablespace_name order by 1;
This example display the total size of the free space:
SQL> select sum(bytes)/(1024*1024*1024) from dba_free_space;
SUM(BYTES)/(1024*1024*1024)
---------------------------
246.29922

DBA_DATA_FILES = DBA_SEGMENTS + DBA_FREE_SPACE + Oracle overhead (header, bitmap... in few KBs)