Useful scripts for DBA's

1) To find out no of transactions happens on table per hour basis.

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



2) Query to find basic redo log information

SELECT A.GROUP#,A.MEMBER, B.BYTES/1024/1024 "MB", B.ARCHIVED,B.STATUS,B.SEQUENCE#
FROM V$LOGFILE A INNER JOIN V$LOG B ON A.GROUP# = B.GROUP#
ORDER BY GROUP# ASC;


3) To split alert log file which having size in GB to MB, in order to retrieve info easily.

split -b 100m alert_test.log


4) Candidate datafiles which are applicable for resize throughout the database.

ORA-03297: file contains used data beyond requested RESIZE value

set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno)
)
select
case when autoextensible='YES' and maxbytes>=bytes
then -- we generate resize statements only if autoextensible can grow back to current size
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
else -- generate only a comment when autoextensible is off
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)
||'M after setting autoextensible maxsize higher than current size for file '
|| file_name||' */'
end SQL
from hwmdf
where
bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc;


******(BELOW WILL EXCLUDE UNDO DATAFILES & NON EXTENDABLE DATAFILES)

set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
ó get highest block id from each datafiles ( from x$ktfbue as we donít need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
ó join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
ó join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno) where tablespace_name not like ë%UNDO%í
)
select
case when autoextensible=íNOí and maxbytes>=bytes
then ó we generate resize statements only if autoextensible can grow back to current size
ë/* reclaim ë||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||íM from ë||to_char(ceil(bytes/1024/1024),999999)||íM */ ë
||íalter database datafile îí||file_name||îí resize ë||ceil(hwm_bytes/1024/1024)||íM;í
else ó generate only a comment when autoextensible is off
ë/* reclaim ë||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||íM from ë||to_char(ceil(bytes/1024/1024),999999)||íM */ ë
||íalter database datafile îí||file_name||îí resize ë||ceil(hwm_bytes/1024/1024)||íM;í
end SQL
from hwmdf
where
bytes-hwm_bytes>1024*1024 ó resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/

REFERENCE: http://blog.dbi-services.com/resize-your-oracle-datafiles-down-to-the-minimum-without-ora-03297/