SQL Help Options

[newbie_01]

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'))

[HAL9000]

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

In Oracle you can place a select with a select:

- In the select clause (a scalar subquery)
- In the from clause (an inline view)

Beware that these cause sub-plan and poor execution plans, and it's always better to materialize them whenever possible using the WITH clause:

http://www.dba-oracle.com/t_with_clause.htm

[HAL9000]

"find the maximum generated logs and on what date:"

I would use a count(*) function for this.

I would also use v$log_history instead, see here:

http://www.dba-oracle.com/tips_oracle_v�...log_history.htm

CODE

-- Daily Count and Size of Redo Log Space (Single Instance)
--
SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
   SELECT
   To_Char(First_Time,'YYYY-MM-DD') DAY,
   Count(1) Count#,
   Min(RECID) Min#,
   Max(RECID) Max#
FROM
   v$log_history
GROUP BY
   To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B
;

"ORA-00937: not a single-group group function"

Try reversing the items:

CODE

select
   to_char(completion_time,'YYYYMMDD'),
   ceil(sum(blocks*block_size+block_size)/1024/1024/1024)
from

If you need to learn advanced SQL techniques get Laurent Schneiders book on advanced programming with SQL:

http://www.rampant-books.com/book_0802_adv...programming.htm