Re: v$tempfile.file#+200 = $sort_usage.segfile# in 8i, +1000 in 9i ?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 13 Apr 2006 07:16:30 -0700
Message-ID: <1144937790.921926.222440@t31g2000cwb.googlegroups.com>

What edition and version of Oracle are you testing with? What platform?

Using the EE edition version 9.2.0.6 on AIX 5.2L it would appear that the v$sort_usage.segrfno# equates to the v$tempfile.rfile#

USERNAME     USER                           SESSION_ADDR

SESSION_NUM
------------ ------------------------------ ----------------

SQLADDR SQLHASH TABLESPACE CONTENTS SEGTYPE SEGFILE#
---------------- ---------- ------------ --------- --------- ----------

SEGBLK# EXTENTS BLOCKS SEGRFNO# FILE# CREATION_CHANGE#
---------- ---------- ---------- ---------- ---------- ----------------
CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS
--------- ---------- ---------- ------- ---------- ----------

CREATE_BYTES BLOCK_SIZE
------------ ----------

NAME

IA           IA                             07000000033A5530
22725
07000000072B1AB0 2256990019 TMP          TEMPORARY SORT             264
      2057          6       1536          9          9                0
                   2          9 ONLINE  READ WRITE 1049624576
128128
  1049624576       8192

/dev/rtfd1_tmp_9.dbf

The v$tempfile file# and rfile# are 1 - 12 while the v$sort_usage file# in use will show something like 264 but the segrfno# appears to match up to v$tempfile.

Interested in what you find on 10g.

HTH -- Mark D Powell -- Received on Thu Apr 13 2006 - 09:16:30 CDT