Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: v$tempfile.file#+200 = $sort_usage.segfile# in 8i, +1000 in 9i ?

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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 17 Apr 2006 11:33:17 +0100
Message-ID: <EvCdnXM2uMBw897ZRVnyhQ@bt.com>

"Spendius" <spendius_at_muchomail.com> wrote in message news:1144917325.187235.268240_at_e56g2000cwe.googlegroups.com...
> I've noticed that in order to join V$SORT_USAGE and V$TEMPFILE
> I have to add 200 or 1000 to the value of the file number in the
> latter (depending on the version).
> In Metalink note
> https://metalink.oracle.com/metalink/plsql/f?p=130:14:4053916170257422556::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,67534.1,1,1,1,helvetica
>
> they say that SQLADDR and SQLHASH can appear wrong, I'm afraid
> they forgot to add that other columns of this view may be erroneous too
> !
>
> I'm going to check it in 10g.
>
> Spendius
>

You'll notice that the "historical note" section of that metalink entry suggests you join to v$datafile, not v$tempfile - so the statement was (possibly) true when people used standard data files for their temp.

When you join to temp files, you have to adjust file numbers by the value of parameter db_files. v$tempfile exposes x$kcctf.tfnum as the file number, rather than x$kcctf.tfanm.

        tfanm = db_files + tfnum.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Received on Mon Apr 17 2006 - 05:33:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US