Rate of Data Change
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Rate of Data Change

  1. #1
    Join Date
    Aug 2002
    Posts
    43
    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.

  2. #2
    Join Date
    Aug 2000
    Location
    Sao Paulo
    Posts
    114

    Wink 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.


  3. #3
    Join Date
    Aug 2002
    Posts
    43
    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

  4. #4
    Join Date
    Aug 2000
    Location
    Sao Paulo
    Posts
    114

    Talking 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
  •  


Click Here to Expand Forum to Full Width