Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Archive log file size

763212Apr 20 2010 — edited Apr 20 2010
Redlogfile size is 16 Mb and generating archivelog file size is around 10Mb. what could be the reason?

Oracle Version: 10.2.0.4

OS : Windows

Edited by: Deccan Charger on Apr 19, 2010 11:31 PM
This post has been answered by Chinar on Apr 20 2010
Jump to Answer

Comments

Tubby
Sure.
TUBBY_TUBBZ? With
  2     customer_data as
  3  (
  4     select 2  as cust_id from dual union all
  5     select 3  as cust_id from dual union all
  6     select 6  as cust_id from dual union all
  7     select 7  as cust_id from dual union all
  8     select 11 as cust_id from dual union all
  9     select 12 as cust_id from dual
 10  ),
 11     all_ids as
 12  (
 13     select
 14        min_cust_id + level - 1 as generated_cust_id
 15     from
 16     (
 17        select
 18           1            as min_cust_id,
 19           max(cust_id) as max_cust_id
 20        from
 21           customer_data
 22     )
 23     connect by level <= max_cust_id - min_cust_id
 24  )
 25  select
 26     a.generated_cust_id
 27  from
 28     all_ids        a,
 29     customer_data  c
 30  where a.generated_cust_id = c.cust_id (+)
 31  and   c.cust_id is null
 32  order by a.generated_cust_id asc;

 GENERATED_CUST_ID
------------------
                 1
                 4
                 5
                 8
                 9
                10

6 rows selected.

Elapsed: 00:00:00.03
776822
outstanding! Thanks! I am amazed how fast you found the solution
We are looking for a very good database developer to join our group and our project lead offered this puzzle to put on the interview test. Forgive me please if my question is not appropriate. Do you know of any US citizen with your skill level who would be interested to apply for database programmer position? We have wasted so much time already with staffing agencies candidates...
Solomon Yakobson
Or simpler:
with customer_data as (
                       select 2  as cust_id from dual union all
                       select 3  as cust_id from dual union all
                       select 6  as cust_id from dual union all
                       select 7  as cust_id from dual union all
                       select 11 as cust_id from dual union all
                       select 12 as cust_id from dual
                      )
select  column_value missing_cust_id
  from  (
         select  lag(cust_id,1,0) over(order by cust_id) + 1 gap_start,
                 cust_id - lag(cust_id,1,0) over(order by cust_id) - 1 gap_count
           from  customer_data
        ) t,
        table(
              cast(
                   multiset(
                            select  gap_start + level - 1
                              from  dual
                              connect by level <= gap_count
                           )
                   as sys.OdciNumberList
                  )
             )
  where gap_count > 0
  order by gap_start
/

MISSING_CUST_ID
---------------
              1
              4
              5
              8
              9
             10

6 rows selected.

SQL> 
SY.
Tubby
mavs wrote:
outstanding! Thanks! I am amazed how fast you found the solution
We are looking for a very good database developer to join our group and our project lead offered this puzzle to put on the interview test. Forgive me please if my question is not appropriate. Do you know of any US citizen with your skill level who would be interested to apply for database programmer position? We have wasted so much time already with staffing agencies candidates...
Thanks :)

I'm not aware of any US citizens, but that's likely because i'm Canadian and haven't ventured across the border for work yet.

You should be able to find a suitable candidate via a staffing firm, but you'll likely have to be very specific with what you are looking for. Years of experience, desire for continual learning (some developers have been coding for 20 years, but never bother to update their skills so they are still stuck in cursor loops and the like), things like that.

Good luck in the search!
Solomon Yakobson
Solomon Yakobson wrote:
Or simpler:
Actually, unless max(cust_id) is very large number and there are few gaps:
with customer_data as (
                       select 2  as cust_id from dual union all
                       select 3  as cust_id from dual union all
                       select 6  as cust_id from dual union all
                       select 7  as cust_id from dual union all
                       select 11 as cust_id from dual union all
                       select 12 as cust_id from dual
                      )
 select  level missing_cust_id
   from  dual
   connect by level < (select max(cust_id) from customer_data)
minus
 select  cust_id
   from  customer_data
order by missing_cust_id
/

MISSING_CUST_ID
---------------
              1
              4
              5
              8
              9
             10

6 rows selected.

SQL> 
SY.
Tubby
Solomon Yakobson wrote:
Solomon Yakobson wrote:
Or simpler:
Actually, unless max(cust_id) is very large number and there are few gaps:
Yup, which is mostly what i had in my original post, though i took the anti-join approach over the minus.

Though i started using MIN and MAX since i didn't see the OP wanted to include values outside the range contained within the table values. I got lazy so didn't change to that exact variant .... since it's not production code i didn't think anyone would mind :)

I liked the other solution you posted though, cool stuff.
Aketi Jyuuzou
I like recursive with clause B-)
with t(Val) as(
select *
  from table(sys.odciNumberList(2,3,6,7,11,12))),
rec(LagVal,Val) as(
select LagVal,Val-1
from (select Lag(Val,1,0) over(order by Val) as LagVal,Val
        from t)
where LagVal < Val-1
union all
select LagVal,Val-1
  from rec
where LagVal < Val-1)
select*from rec order by Val;

LagVal  Val
------  ---
     0    1
     3    4
     3    5
     7    8
     7    9
     7   10
776822
Looks like this was the least costly solution, in terms of performance; according to explain plan
However, all of them are very interesting, thanks!
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 18 2010
Added on Apr 20 2010
34 comments
23,492 views