Re: TEMP tablespace - objects in

From: <Winnie_Liu_at_infonet.com>
Date: Wed, 28 Jun 2000 10:56:35 -0700
Message-Id: <10542.110694@fatcity.com>

--0__=DlA3K9ebzHewejX4LjN6n2b4pm5uGI9HMZBWs7PNHB6J1G0vIdw3YzQf
Content-type: text/plain; charset=us-ascii Content-Disposition: inline

V$SORT_USAGE is not used in 734. It starts in V8.X.

For 7, I used the script below. I got it from someone else in the list. It shows you a general idea about who is doing sort:

set pagesize 100

col "OS user" for a10
col "oracle user" for a8
col "login terminal" for a10
col "program running" for a50
SELECT  vs.osuser "OS user",
       vs.username "oracle user",
       vs.terminal "login terminal",
       vs.program "program running",
        vsn.name,
        vss.value
FROM    v$session vs,
        v$sesstat vss,
        v$statname vsn
WHERE   vss.statistic# = vsn.statistic#
AND     vs.sid =vss.sid
AND     vsn.name like '%sort%'

ORDER BY vsn.name,vss.value
/

Winnie

John Barron <jbarron_at_windriver.com> on 06/28/2000 10:56:47 AM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Winnie Liu/HQ/ISC)

Subject: TEMP tablespace - objects in

Hi

Im trying to find what objects are filling up our temp ts using this sql on a
734 db but it returns the error:

from v$session s, v$sort_usage u

                  *

ERROR at line 3:
ORA-00942: table or view does not exist

Any ideas. I got the sql from the list - cant remeber who. Full sql below.

Ive looked at the objects in the temp ts using dbartisan and there is just one
- SYS.8.21107 occupying 999M in our IG temp ts.

John

select s.username, u."USER", u.tablespace,

       u.contents, u.extents, u.blocks
from v$session s, v$sort_usage u
where s.addr = u.session_addr;

--0__=DlA3K9ebzHewejX4LjN6n2b4pm5uGI9HMZBWs7PNHB6J1G0vIdw3YzQf
Content-type: text/html;

        name="att1.htm"

Content-Disposition: attachment; filename="att1.htm"
Content-transfer-encoding: base64
Content-Description: Internet HTML

PGh0bWw+DQpIaTxicj4NCjxicj4NCkltIHRyeWluZyB0byBmaW5kIHdoYXQgb2JqZWN0cyBhcmUg ZmlsbGluZyB1cCBvdXIgdGVtcCB0cyB1c2luZyB0aGlzIHNxbA0Kb24gYSA3MzQgZGIgYnV0IGl0 IHJldHVybnMgdGhlIGVycm9yOjxicj4NCjxicj4NCjxmb250IGZhY2U9InJfYW5zaSI+ZnJvbSB2 JHNlc3Npb24gcywgdiRzb3J0X3VzYWdlIHU8YnI+DQombmJzcDsmbmJzcDsmbmJzcDsmbmJzcDsm bmJzcDsmbmJzcDsmbmJzcDsmbmJzcDsmbmJzcDsmbmJzcDsmbmJzcDsmbmJzcDsmbmJzcDsmbmJz cDsmbmJzcDsmbmJzcDsmbmJzcDsNCio8YnI+DQpFUlJPUiBhdCBsaW5lIDM6PGJyPg0KT1JBLTAw OTQyOiB0YWJsZSBvciB2aWV3IGRvZXMgbm90IGV4aXN0PGJyPg0KPGJyPg0KPC9mb250PkFueSBp ZGVhcy4gSSBnb3QgdGhlIHNxbCBmcm9tIHRoZSBsaXN0IC0gY2FudCByZW1lYmVyIHdoby4gRnVs bA0Kc3FsIGJlbG93Ljxicj4NCjxicj4NCkl2ZSBsb29rZWQgYXQgdGhlIG9iamVjdHMgaW4gdGhl IHRlbXAgdHMgdXNpbmcgZGJhcnRpc2FuIGFuZCB0aGVyZSBpcw0KanVzdCBvbmUgLSBTWVMuOC4y MTEwNyBvY2N1cHlpbmcgOTk5TSBpbiBvdXIgSUcgdGVtcCB0cy48YnI+DQo8YnI+DQpKb2huPGJy Pg0KPGJyPg0KPGJyPg0KPGZvbnQgZmFjZT0icl9hbnNpIj5zZWxlY3Qgcy51c2VybmFtZSwgdS4m cXVvdDtVU0VSJnF1b3Q7LA0KdS50YWJsZXNwYWNlLDxicj4NCiZuYnNwOyZuYnNwOyZuYnNwOyZu YnNwOyZuYnNwOyZuYnNwOyB1LmNvbnRlbnRzLCB1LmV4dGVudHMsIA0KdS5ibG9ja3M8YnI+DQpm cm9tIHYkc2Vzc2lvbiBzLCB2JHNvcnRfdXNhZ2UgdTxicj4NCndoZXJlIHMuYWRkciA9IHUuc2Vz c2lvbl9hZGRyOzxicj4NCjwvZm9udD48L2h0bWw+DQoNCg== Received on Wed Jun 28 2000 - 12:56:35 CDT