Home » SQL & PL/SQL » SQL & PL/SQL » v$tempseg_usage
v$tempseg_usage [message #258137] Fri, 10 August 2007 06:33 Go to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Hi all,

I was wondering how the view v$tempseg_usage is being build. I did the following to find out:

1 : What type of object is v$tempseg_usage?
SQL> select object_type from dba_objects where object_name='V$TEMPSEG_USAGE';

OBJECT_TYPE
------------------
SYNONYM

SQL>


2 : What is this synonym pointing to?
SQL> select * from dba_synonyms where synonym_name='V$TEMPSEG_USAGE';

OWNER                SYNONYM_NAME         TABLE_OWNER          TABLE_NAME           DB_LINK
-------------------- -------------------- -------------------- -------------------- ----------
PUBLIC               V$TEMPSEG_USAGE      SYS                  V_$SORT_USAGE

SQL>


3 : What type object is V_$SORT_USAGE?
SQL> select object_type from dba_objects where object_name='V_$SORT_USAGE';

OBJECT_TYPE
------------------
VIEW

SQL>


4 : Now I'm where I want to be (I think): I'm going to find out how the view is being build.
SQL> select * from dba_views where view_name='V_$SORT_USAGE';

OWNER                VIEW_NAME                      TEXT_LENGTH
-------------------- ------------------------------ -----------
TEXT                                                                             TYPE_TEXT_LENGTH
-------------------------------------------------------------------------------- ----------------
TYPE_TEXT
----------------------------------------------------------------------------------------------------
OID_TEXT_LENGTH
---------------
OID_TEXT
----------------------------------------------------------------------------------------------------
VIEW_TYPE_OWNER                VIEW_TYPE                      SUPERVIEW_NAME
------------------------------ ------------------------------ ------------------------------
SYS                  V_$SORT_USAGE                          176
select "USERNAME","USER","SESSION_ADDR","SESSION_NUM","SQLADDR","SQLHASH","TABLE
SPACE","CONTENTS","SEGTYPE","SEGFILE#","SEGBLK#","EXTENTS","BLOCKS","SEGRFNO#" f
rom v$sort_usage


5 : OK.... let's find out what v$sort_usage is
SQL> select object_type from dba_objects where object_name='V$SORT_USAGE';

OBJECT_TYPE
------------------
SYNONYM


6 : And this sysnonym point towards :
SQL> select * from dba_synonyms where synonym_name='V$SORT_USAGE';

OWNER                SYNONYM_NAME         TABLE_OWNER          TABLE_NAME           DB_LINK
-------------------- -------------------- -------------------- -------------------- ----------
PUBLIC               V$SORT_USAGE         SYS                  V_$SORT_USAGE

SQL>


To me that's a little strange. The view v_$sort_usage is selecting from v$sort_usage, which is a synonym to v_$sort_usage.

Am I missing something? Can someone point me to some documents or examples which can clarify?
(Ofcourse it can very well be that my thinking is to complicated and that it's not strange at all)

This was all on :
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

SQL>


Best Regards,

Martijn Bos
Re: v$tempseg_usage [message #258147 is a reply to message #258137] Fri, 10 August 2007 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
V$ views are SYS objects and so special.
Search in v$fixed_view_definition.

Regards
Michel
Re: v$tempseg_usage [message #258159 is a reply to message #258147] Fri, 10 August 2007 07:22 Go to previous message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Very usefull

Homework assignment to myself is now reading up on the relationship between v$fixed_view_definition and dba_views.

thanks
Previous Topic: doubt on simple select
Next Topic: SELECT within DECODE
Goto Forum:
  


Current Time: Sat Dec 10 02:54:19 CST 2016

Total time taken to generate the page: 0.09018 seconds