Re: temp file useage on 10.2..0.4 enterprise database / windows 2003

> AFAIK that metric is not recorded in the AWR repository; it's reported
> from the v$tempseg_usage view.

Somewhat older, but practically completely table is V$SORT_USAGE. The obvious advantage of V$SORT_USAGE ove V$TEMPSEG_USAGE is the shorter name, thus cutting down on typing and the possibility of getting carpal tunnel.

SQL> desc v$tempseg_usage

 Name                                      Null?    Type
 ----------------------------------------- -------- 
----------------------------
 USERNAME                                           VARCHAR2(30)
 USER                                               VARCHAR2(30)
 SESSION_ADDR                                       RAW(4)
 SESSION_NUM                                        NUMBER
 SQLADDR                                            RAW(4)
 SQLHASH                                            NUMBER
 SQL_ID                                             VARCHAR2(13)
 TABLESPACE                                         VARCHAR2(31)
 CONTENTS                                           VARCHAR2(9)
 SEGTYPE                                            VARCHAR2(9)
 SEGFILE#                                           NUMBER
 SEGBLK#                                            NUMBER
 EXTENTS                                            NUMBER
 BLOCKS                                             NUMBER
 SEGRFNO#                                           NUMBER

SQL> desc v$sort_usage
 Name                                      Null?    Type
 ----------------------------------------- -------- 
----------------------------
 USERNAME                                           VARCHAR2(30)
 USER                                               VARCHAR2(30)
 SESSION_ADDR                                       RAW(4)
 SESSION_NUM                                        NUMBER
 SQLADDR                                            RAW(4)
 SQLHASH                                            NUMBER
 SQL_ID                                             VARCHAR2(13)
 TABLESPACE                                         VARCHAR2(31)
 CONTENTS                                           VARCHAR2(9)
 SEGTYPE                                            VARCHAR2(9)
 SEGFILE#                                           NUMBER
 SEGBLK#                                            NUMBER
 EXTENTS                                            NUMBER
 BLOCKS                                             NUMBER
 SEGRFNO#                                           NUMBER

SQL> If you take a more detailed look into v$tempseg_usage, you will discover that it is just a synonym for the view v_$sort_usage, just like v$sort_usage, which might explain why these two views look so similar. There is an obvious name change pending :)

-- 
http://mgogala.byethost5.com