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

"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