Results 1 to 2 of 2

Thread: SQL Help

  1. #1
    Join Date
    Sep 2008
    Posts
    41

    Unanswered: SQL Help

    Hi,

    Am trying to run the SQL below to find the maximum generated logs and on what date:

    This SQL below, list the generated archivelogs group by the date, this works fine on its own:

    select ceil(sum(blocks*block_size+block_size)/1024/1024/1024), to_char(completion_time,'YYYYMMDD')
    from v$archived_log where dest_id = 1
    group by to_char(completion_time,'YYYYMMDD');

    To get the date when the maximum archivelogs are generated, I thought it will be as simple as the changing the SQL to the one below, unfortunately, that does not seem to be case:

    SQL> select max(ceil(sum(blocks*block_size+block_size)/1024/1024/1024)), to_char(completion_time,'YYYYMMDD')
    2 from v$archived_log where dest_id = 1
    3 group by to_char(completion_time,'YYYYMMDD');
    group by to_char(completion_time,'YYYYMMDD')
    *
    ERROR at line 3:
    ORA-00937: not a single-group group function


    SQL> select max(ceil(sum(blocks*block_size+block_size)/1024/1024/1024)), to_char(completion_time,'YYYYMMDD')
    2 from v$archived_log where dest_id = 1
    3 group by ceil(sum(blocks*block_size+block_size)/1024/1024/1024), to_char(completion_time,'YYYYMMDD')
    4 ;
    group by ceil(sum(blocks*block_size+block_size)/1024/1024/1024), to_char(completion_time,'YYYYMMDD')
    *
    ERROR at line 3:
    ORA-00934: group function is not allowed here

    At the moment, I've re-written the SQL to be as below and it is working like I want it to be but am just curious if there is a better way of doing it as am not a big fun of select from a select. Thanks in advance.

    BTW, is the query below an example of what are normally referred to as INLINE VIEWS?

    select *
    from
    (
    select ceil(sum(blocks*block_size+block_size)/1024/1024/1024) a,
    to_char(completion_time,'YYYYMMDD') b
    from v$archived_log where dest_id = 1
    group by to_char(completion_time,'YYYYMMDD')
    ) where
    a in (select max(ceil(sum(blocks*block_size+block_size)/1024/1024/1024))
    from v$archived_log where dest_id = 1
    group by to_char(completion_time,'YYYYMMDD'))

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT   To_char(completion_time,'YYYYMMDD')    run_date,
             Round(Sum(blocks * block_size + block_size) / 1024 / 1024 / 1024) redo_blocks
    FROM     v$archived_log
    GROUP BY To_char(completion_time,'YYYYMMDD')
    ORDER BY 2
    /
    Last edited by anacedent; 07-05-09 at 23:58.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •