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

Home -> Community -> Mailing Lists -> Oracle-L -> system views and their underlying structure

system views and their underlying structure

From: Lyall Barbour <lbarbour_at_stanford.edu>
Date: Tue, 29 Aug 2000 16:46:01 -0700
Message-Id: <10603.115801@fatcity.com>


Hello All,

        I think I asked this question before, a long time ago. But, since the answer has slipped my mind I'm going to ask again.

Where to the v$ views get their data from? The reason I ask is this:

We are trying to upgrade to Oracle Apps 11 and eventually 11i. Part of the functionality that the testers of Apps 11 (that they were used to in 10.7) was to export data that they queried up to tab separated files they could open with Excel. Well, we had a problem with some of the web stuff and searching through, we found the main problem was with a "database connect" or .dbc file on the server used in this whole "exporting" process. Everytime someone wanted to "Export" data, they would get an error saying /blah/blah/blah/<hostname>_<Oracle_Sid> was unreadible or something to that effect. Through searching we found where the file was supposed to be and that it was not actually there. The program was looking for cfdev.stanford.edu_cor1_crp and that file does not exist, cfdev.stanford.edu_cor1_crp.dbc does exist. I don't know if it was a bug with Oracle or what, but at Stanford our hostnames all have the domain in them. If you do a hostname command at the Solaris prompt, you'll get back the machine name and domain name all together..... I don't know if anyone else in the world does that with their computers.... but that's what was causing our problem here...

OK, so, I was searching around and found the host_name column the the v$instance view. I wanted to know where this view gets its information from so I queried v$fixed_view_definitions and found gv$intance. Querying dba_objects I found synonyms and views that kept leading me around in a circle.... Can someone shed some light on the whole underneath of v$ views and (if you could specifically tell) about the host_name column in v$instance?

SQL> select * from v$fixed_view_definition

   2 where view_name = 'V$INSTANCE';

VIEW_NAME



VIEW_DEFINITION


V$INSTANCE
select INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSION , STARTUP_TIME , S
TATUS , PARALLEL , THREAD# , ARCHIVER , LOG_SWITCH_WAIT , LOGINS , SHUTDOWN_PEND
ING from GV$INSTANCE where inst_id = USERENV('Instance')

SQL> select * from dba_objects

   2 where object_name = 'GV$INSTANCE';

OWNER



OBJECT_NAME

SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

------------------------------ ---------- -------------- ---------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G
--------- --------- ------------------- ------- - -
PUBLIC
GV$INSTANCE
                                       708                SYNONYM
10-NOV-98 10-NOV-98 1998-11-10:14:29:52 VALID N N SQL> select * from dba_synonyms

   2 where synonym_name = 'GV$INSTANCE';

OWNER                          SYNONYM_NAME

------------------------------ ------------------------------
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
DB_LINK

PUBLIC                         GV$INSTANCE
Received on Tue Aug 29 2000 - 18:46:01 CDT

Original text of this message

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