RE: order by in subquery workaround

Rob and Jack:
Found it. (165420.1)
Thanks for your help.
Barb

> ----------
> From: Robert Pegram[SMTP:pegramrg_at_yahoo.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Friday, July 12, 2002 6:48 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: order by in subquery workaround
>
> I downloaded it from metalink a while back. It was
> pretty straight forward to install. The report name
> was something different like statsrep.sql or
> statsrep80.sql. Also, there was a view that had to be
> created. All the information/instructions were in the
> tar file that I downloaded.
>
> If you can't find it on metalink, let me know, and I
> will round up my old notes and the tar file and send
> it to you.
>
> Rob Pegram
> Oracle Certified DBA
>
> --- Jack van Zanen <nlzanen1_at_EY.NL> wrote:
> > Hi
> >
> > I have in the past seen a download for a backported
> > statspack on I believe
> > OTN. This would work on Oracle 8.0.5
> > Maybe still there
> >
> > Jack
> >
> >
> >
> >
> >
> > "Baker, Barbara"
> >
> >
> > <bbaker_at_denvernewspaper
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > agency.com>
> > cc: (bcc: Jack van
> > Zanen/nlzanen1/External/MEY/NL)
> >
> > Sent by:
> > Subject: order by in subquery workaround
> >
> > root_at_fatcity.com
> >
> >
> >
> >
> >
> >
> >
> >
> > 11-07-2002 19:38
> >
> >
> > Please respond to
> >
> >
> > ORACLE-L
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > > Solaris 2.6
> > > Oracle RDBMS v8.0.5.2.1
> > >
> > List:
> > I'm trying to run a retrofit of statspack on an
> > 8.0.5 database. (ya, I
> > know. upgrading would actually be easier....)
> > The statspack stuff came from an 8.1.7 install I
> > have on the same box.
> >
> > The spreport.sql report encounters errors in the
> > "order by" clause. The
> > problem is that you can't do an "order by" on a
> > subquery before version
> > 8.1.something-or-other
> >
> > I know there's some kind of work around for this. I
> > had something to do
> > with a hint. Even after 3 cups of coffee, I can't
> > remember what it is.
> >
> > Can anyone help?
> > Thanks!
> > Barb
> >
> > here are the errors:
> >
> > Shared Pool Statistics Begin End
> > ------ ------
> > Memory Usage %: 75.99 70.05
> > % SQL with executions>1: 21.95 48.60
> > % Memory for SQL w/exec>1: 11.28 21.84
> > order by time desc, waits desc
> > *
> > ERROR at line 24:
> > ORA-00907: missing right parenthesis
> >
> >
> >
> >
> > pmon timer 1,184 1,177
> > 355,304 3001
> > 0.2
> >
> >
> -------------------------------------------------------------
> > order by (e.buffer_gets -
> > nvl(b.buffer_gets,0)) desc, e.hash_value,
> > st.piece
> > *
> > ERROR at line 41:
> > ORA-00907: missing right parenthesis
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Baker, Barbara
> > INET: bbaker_at_denvernewspaperagency.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
> >
> >
> >
> >
> ===================================================================
> > De informatie verzonden in dit e-mailbericht is
> > vertrouwelijk en is
> > uitsluitend bestemd voor de geadresseerde.
> > Openbaarmaking,
> > vermenigvuldiging, verspreiding en/of verstrekking
> > van deze informatie aan
> > derden is, behoudens voorafgaande schriftelijke
> > toestemming van Ernst &
> > Young, niet toegestaan. Ernst & Young staat niet in
> > voor de juiste en
> > volledige overbrenging van de inhoud van een
> > verzonden e-mailbericht, noch
> > voor tijdige ontvangst daarvan. Ernst & Young kan
> > niet garanderen dat een
> > verzonden e-mailbericht vrij is van virussen, noch
> > dat e-mailberichten
> > worden overgebracht zonder inbreuk of tussenkomst
> > van onbevoegde derden.
> >
> > Indien bovenstaand e-mailbericht niet aan u is
> > gericht, verzoeken wij u
> > vriendelijk doch dringend het e-mailbericht te
> > retourneren aan de verzender
> > en het origineel en eventuele kopieën te verwijderen
> > en te vernietigen.
> >
> > Ernst & Young hanteert bij de uitoefening van haar
> > werkzaamheden algemene
> > voorwaarden, waarin een beperking van
> > aansprakelijkheid is opgenomen. De
> > algemene voorwaarden worden u op verzoek kosteloos
> > toegezonden.
> >
> =====================================================================
> > The information contained in this communication is
> > confidential and is
> > intended solely for the use of the individual or
> > entity to whom it is
> > addressed. You should not copy, disclose or
> > distribute this communication
> > without the authority of Ernst & Young. Ernst &
> > Young is neither liable for
> > the proper and complete transmission of the
> > information contained in this
> > communication nor for any delay in its receipt.
> > Ernst & Young does not
> > guarantee that the integrity of this communication
> > has been maintained nor
> > that the communication is free of viruses,
> > interceptions or interference.
> >
> > If you are not the intended recipient of this
> > communication please return
> > the communication to the sender and delete and
> > destroy all copies.
> >
> > In carrying out its engagements, Ernst & Young
> > applies general terms and
> > conditions, which contain a clause that limits its
> > liability. A copy of
> > these terms and conditions is available on request
> > free of charge.
> >
> ===================================================================
> >
> >
> >
> >
> >
> >
> > --
> >
> === message truncated ===
>
>
> __________________________________________________
> Do You Yahoo!?
> Sign up for SBC Yahoo! Dial - First Month Free
> http://sbc.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Robert Pegram
> INET: pegramrg_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Baker, Barbara
  INET: bbaker_at_denvernewspaperagency.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).