How do I determine the rate of data change in our Production database? Is a logical export any useful? We don't have any of the Oracle OEM packs.
Printable View
How do I determine the rate of data change in our Production database? Is a logical export any useful? We don't have any of the Oracle OEM packs.
It verifies the amount of REdo-log used during one
hour in the period of 60 days each redo tends 20Mb,
if in 1 hour we have the value 4 q we can say in this period
80 Mb mesta was moved hour. * *
set feedback off echo off trims on
set pagesize 50000
set line 200
set veri off
set termout off
def time="time" -- Oracle7
col time new_value time
col mes format a3
spool d:\somtingdir\temp\redolog.txt
select 'to_char(first_time,''MM/DD/YY HH24:MI:SS'')' time
from dual
where &&_O_RELEASE like '8%' -- Oracle8
/
set termout on
break on mes skip 1 on report
ttitle center 'Relatorio de quantidade e tamanho de transações por
periodo de até 60 dias' center
compute sum of Total_Diario on mes
compute sum of Volume_Mb on mes
compute avg of total_diario on report
-- compute sum of total_diario on report
compute avg of volume_Mb on report
col total_diario format 999,999.99
col volume_mb format 999,999.99
select substr(&&time,1,2) mes,
substr(&&time,4,5) dia,
to_char(sum(decode(substr(&&time,10,2),'00',1,0)),'999') "00",
to_char(sum(decode(substr(&&time,10,2),'01',1,0)),'999') "01",
to_char(sum(decode(substr(&&time,10,2),'02',1,0)),'999') "02",
to_char(sum(decode(substr(&&time,10,2),'03',1,0)),'999') "03",
to_char(sum(decode(substr(&&time,10,2),'04',1,0)),'999') "04",
to_char(sum(decode(substr(&&time,10,2),'05',1,0)),'999') "05",
to_char(sum(decode(substr(&&time,10,2),'06',1,0)),'999') "06",
to_char(sum(decode(substr(&&time,10,2),'07',1,0)),'999') "07",
to_char(sum(decode(substr(&&time,10,2),'08',1,0)),'999') "08",
to_char(sum(decode(substr(&&time,10,2),'09',1,0)),'999') "09",
to_char(sum(decode(substr(&&time,10,2),'10',1,0)),'999') "10",
to_char(sum(decode(substr(&&time,10,2),'11',1,0)),'999') "11",
to_char(sum(decode(substr(&&time,10,2),'12',1,0)),'999') "12",
to_char(sum(decode(substr(&&time,10,2),'13',1,0)),'999') "13",
to_char(sum(decode(substr(&&time,10,2),'14',1,0)),'999') "14",
to_char(sum(decode(substr(&&time,10,2),'15',1,0)),'999') "15",
to_char(sum(decode(substr(&&time,10,2),'16',1,0)),'999') "16",
to_char(sum(decode(substr(&&time,10,2),'17',1,0)),'999') "17",
to_char(sum(decode(substr(&&time,10,2),'18',1,0)),'999') "18",
to_char(sum(decode(substr(&&time,10,2),'19',1,0)),'999') "19",
to_char(sum(decode(substr(&&time,10,2),'20',1,0)),'999') "20",
to_char(sum(decode(substr(&&time,10,2),'21',1,0)),'999') "21",
to_char(sum(decode(substr(&&time,10,2),'22',1,0)),'999') "22",
to_char(sum(decode(substr(&&time,10,2),'23',1,0)),'999') "23",
sum ((decode(substr(&&time,10,2),'00',1,0))+
(decode(substr(&&time,10,2),'00',1,0))+
(decode(substr(&&time,10,2),'01',1,0))+
(decode(substr(&&time,10,2),'02',1,0))+
(decode(substr(&&time,10,2),'03',1,0))+
(decode(substr(&&time,10,2),'04',1,0))+
(decode(substr(&&time,10,2),'05',1,0))+
(decode(substr(&&time,10,2),'06',1,0))+
(decode(substr(&&time,10,2),'07',1,0))+
(decode(substr(&&time,10,2),'08',1,0))+
(decode(substr(&&time,10,2),'09',1,0))+
(decode(substr(&&time,10,2),'10',1,0))+
(decode(substr(&&time,10,2),'11',1,0))+
(decode(substr(&&time,10,2),'12',1,0))+
(decode(substr(&&time,10,2),'13',1,0))+
(decode(substr(&&time,10,2),'14',1,0))+
(decode(substr(&&time,10,2),'15',1,0))+
(decode(substr(&&time,10,2),'16',1,0))+
(decode(substr(&&time,10,2),'17',1,0))+
(decode(substr(&&time,10,2),'18',1,0))+
(decode(substr(&&time,10,2),'19',1,0))+
(decode(substr(&&time,10,2),'20',1,0))+
(decode(substr(&&time,10,2),'21',1,0))+
(decode(substr(&&time,10,2),'22',1,0))+
(decode(substr(&&time,10,2),'23',1,0))) Total_Diario,
sum ((decode(substr(&&time,10,2),'00',1,0))+
(decode(substr(&&time,10,2),'00',1,0))+
(decode(substr(&&time,10,2),'01',1,0))+
(decode(substr(&&time,10,2),'02',1,0))+
(decode(substr(&&time,10,2),'03',1,0))+
(decode(substr(&&time,10,2),'04',1,0))+
(decode(substr(&&time,10,2),'05',1,0))+
(decode(substr(&&time,10,2),'06',1,0))+
(decode(substr(&&time,10,2),'07',1,0))+
(decode(substr(&&time,10,2),'08',1,0))+
(decode(substr(&&time,10,2),'09',1,0))+
(decode(substr(&&time,10,2),'10',1,0))+
(decode(substr(&&time,10,2),'11',1,0))+
(decode(substr(&&time,10,2),'12',1,0))+
(decode(substr(&&time,10,2),'13',1,0))+
(decode(substr(&&time,10,2),'14',1,0))+
(decode(substr(&&time,10,2),'15',1,0))+
(decode(substr(&&time,10,2),'16',1,0))+
(decode(substr(&&time,10,2),'17',1,0))+
(decode(substr(&&time,10,2),'18',1,0))+
(decode(substr(&&time,10,2),'19',1,0))+
(decode(substr(&&time,10,2),'20',1,0))+
(decode(substr(&&time,10,2),'21',1,0))+
(decode(substr(&&time,10,2),'22',1,0))+
(decode(substr(&&time,10,2),'23',1,0)))* 20
Volume_Mb
-- 20 is the size in Mb of your redolog file
from sys.v_$log_history
where substr(&&time, 1, 5) != '10/12'
AND substr(&&time, 1, 5) != '10/13'
group by substr(&&time, 1, 2), substr(&&time, 4, 5)
/
spool off
Change the value of !='xx/xx' above for a valid date your.
this script is write in Portugues if you prefer I changed for Ingles.
See firts if this resolve your question.
Thank you hhbrazil. I was wondering if I'd ever get a reply to my question. Yes, please could I have it in English.
What's &&time?
Thanks
It verifies the amount of REdo-log used during one hour in
the period of 60 days each redo tends 20Mb, if in 1 hour we have the value 4 q we can say in this period 80 Mb was
moved per hour. * *
set feedback off echo off trims on
set pagesize 50000
set line 200
set veri off
set termout off
def time="time" -- Oracle7
col time new_value time
col month format a3
spool d:\sometingdir\temp\redolog.txt
select 'to_char(first_time,''MM/DD/YY HH24:MI:SS'')' time
from dual
where &&_O_RELEASE like '8%' -- Oracle8
/
set termout on
break on month skip 1 on report
ttitle center 'Amount report and size of transactions for period of up to 60 days' center
compute sum of Daily_total on month -- here I give the total for MOUNTH
compute sum of Amount_Mb on month -- here I give the total for AMOUNT PER MONTH
compute avg of Daily_total on report -- here I give the averange for Daily
-- compute sum of Daily_total on report
compute avg of Amount_Mb on report -- here I give the averange for Amount
col Daily_total format 999,999.99
col Amount_Mb format 999,999.99
select substr(&&time,1,2) month,
substr(&&time,4,5) day,
to_char(sum(decode(substr(&&time,10,2),'00',1,0)),'999') "00",
to_char(sum(decode(substr(&&time,10,2),'01',1,0)),'999') "01",
to_char(sum(decode(substr(&&time,10,2),'02',1,0)),'999') "02",
to_char(sum(decode(substr(&&time,10,2),'03',1,0)),'999') "03",
to_char(sum(decode(substr(&&time,10,2),'04',1,0)),'999') "04",
to_char(sum(decode(substr(&&time,10,2),'05',1,0)),'999') "05",
to_char(sum(decode(substr(&&time,10,2),'06',1,0)),'999') "06",
to_char(sum(decode(substr(&&time,10,2),'07',1,0)),'999') "07",
to_char(sum(decode(substr(&&time,10,2),'08',1,0)),'999') "08",
to_char(sum(decode(substr(&&time,10,2),'09',1,0)),'999') "09",
to_char(sum(decode(substr(&&time,10,2),'10',1,0)),'999') "10",
to_char(sum(decode(substr(&&time,10,2),'11',1,0)),'999') "11",
to_char(sum(decode(substr(&&time,10,2),'12',1,0)),'999') "12",
to_char(sum(decode(substr(&&time,10,2),'13',1,0)),'999') "13",
to_char(sum(decode(substr(&&time,10,2),'14',1,0)),'999') "14",
to_char(sum(decode(substr(&&time,10,2),'15',1,0)),'999') "15",
to_char(sum(decode(substr(&&time,10,2),'16',1,0)),'999') "16",
to_char(sum(decode(substr(&&time,10,2),'17',1,0)),'999') "17",
to_char(sum(decode(substr(&&time,10,2),'18',1,0)),'999') "18",
to_char(sum(decode(substr(&&time,10,2),'19',1,0)),'999') "19",
to_char(sum(decode(substr(&&time,10,2),'20',1,0)),'999') "20",
to_char(sum(decode(substr(&&time,10,2),'21',1,0)),'999') "21",
to_char(sum(decode(substr(&&time,10,2),'22',1,0)),'999') "22",
to_char(sum(decode(substr(&&time,10,2),'23',1,0)),'999') "23",
sum ((decode(substr(&&time,10,2),'00',1,0))+
(decode(substr(&&time,10,2),'00',1,0))+
(decode(substr(&&time,10,2),'01',1,0))+
(decode(substr(&&time,10,2),'02',1,0))+
(decode(substr(&&time,10,2),'03',1,0))+
(decode(substr(&&time,10,2),'04',1,0))+
(decode(substr(&&time,10,2),'05',1,0))+
(decode(substr(&&time,10,2),'06',1,0))+
(decode(substr(&&time,10,2),'07',1,0))+
(decode(substr(&&time,10,2),'08',1,0))+
(decode(substr(&&time,10,2),'09',1,0))+
(decode(substr(&&time,10,2),'10',1,0))+
(decode(substr(&&time,10,2),'11',1,0))+
(decode(substr(&&time,10,2),'12',1,0))+
(decode(substr(&&time,10,2),'13',1,0))+
(decode(substr(&&time,10,2),'14',1,0))+
(decode(substr(&&time,10,2),'15',1,0))+
(decode(substr(&&time,10,2),'16',1,0))+
(decode(substr(&&time,10,2),'17',1,0))+
(decode(substr(&&time,10,2),'18',1,0))+
(decode(substr(&&time,10,2),'19',1,0))+
(decode(substr(&&time,10,2),'20',1,0))+
(decode(substr(&&time,10,2),'21',1,0))+
(decode(substr(&&time,10,2),'22',1,0))+
(decode(substr(&&time,10,2),'23',1,0))) Daily_total,
sum ((decode(substr(&&time,10,2),'00',1,0))+
(decode(substr(&&time,10,2),'00',1,0))+
(decode(substr(&&time,10,2),'01',1,0))+
(decode(substr(&&time,10,2),'02',1,0))+
(decode(substr(&&time,10,2),'03',1,0))+
(decode(substr(&&time,10,2),'04',1,0))+
(decode(substr(&&time,10,2),'05',1,0))+
(decode(substr(&&time,10,2),'06',1,0))+
(decode(substr(&&time,10,2),'07',1,0))+
(decode(substr(&&time,10,2),'08',1,0))+
(decode(substr(&&time,10,2),'09',1,0))+
(decode(substr(&&time,10,2),'10',1,0))+
(decode(substr(&&time,10,2),'11',1,0))+
(decode(substr(&&time,10,2),'12',1,0))+
(decode(substr(&&time,10,2),'13',1,0))+
(decode(substr(&&time,10,2),'14',1,0))+
(decode(substr(&&time,10,2),'15',1,0))+
(decode(substr(&&time,10,2),'16',1,0))+
(decode(substr(&&time,10,2),'17',1,0))+
(decode(substr(&&time,10,2),'18',1,0))+
(decode(substr(&&time,10,2),'19',1,0))+
(decode(substr(&&time,10,2),'20',1,0))+
(decode(substr(&&time,10,2),'21',1,0))+
(decode(substr(&&time,10,2),'22',1,0))+
(decode(substr(&&time,10,2),'23',1,0)))* 40 Amount_Mb
-- 20 are the size in Mb. of the redolog file
from sys.v_$log_history
where substr(&&time, 1, 5) != '10/12'
AND substr(&&time, 1, 5) != '10/13'
group by substr(&&time, 1, 2), substr(&&time, 4, 5)
/
spool off
--------------------------------
Example (Short)
(1) (2) (3) (4) (5) (6) (7) ...........
MON DAY 00 01 02 03 04
--- ----- ---- ---- ---- ---- ----
07 18/02 0 0 0 0 0
19/02 3 0 0 0 0
20/02 22 10 0 0 0
21/02 13 17 15 12 1
(1) Month
(2) Day & Year
(3) 0 Hour
(4) 1 Hour
(5) 2 Hour
(6) 3 Hour
(7) 4 Hour
....
..
..
see that you will have a report that shows it a day and
hour the amount of switch logs that yours database did,
being able to not likewise, to see in which schedules
have activities more or less in its database.