-
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.
-
Try This and see.
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
-
Translate .....
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|