Re: how to pull just a single member from v$logfile

On Fri, Nov 21, 2008 at 10:57 AM, godba blore <racoradba_at_gmail.com> wrote:

> Hi group,
>
> Need help in writing a query.
> I have 6 groups in a database and in each of these groups I have 2 members.
> I need to write a query where in it pulls only one member from each of the
> group
>
> select a.group#,a.thread#,b.member from v$log a,v$logfile b where
> a.group#=b.group#;
> GROUP# THREAD# MEMBER
> ---------- ---------- --------------------
> 1 1 /dev/rorarptlv00801
> 1 1 /dev/rorarptlv00901
> 2 1 /dev/rorarptlv00802
> 2 1 /dev/rorarptlv00902
> 3 1 /dev/rorarptlv00803
> ...I know this is a wrong place to ask, but I think my dba community can
> help me out here.
>
>

No, this is the right place.

There's more than one way to do this, but this works, as long as you don't care which
member of each group is shown in the results.

select a.group#,a.thread#,min(b.member) member from v$log a,v$logfile b
where a.group#=b.group#
group by a.group#, a.thread#

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l