Oracle-L: order by in subquery workaround

From: Baker, Barbara <bbaker_at_denvernewspaperagency.com>
Date: Thu, 11 Jul 2002 09:38:31 -0800
Message-ID: <F001.0049546F.20020711093831@fatcity.com>

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

Received on Thu Jul 11 2002 - 12:38:31 CDT