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.115802@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
SYS                            GV_$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


--------- --------- ------------------- ------- - -

SYS
GV_$INSTANCE

                                       707                VIEW

08-AUG-98 10-NOV-98 1998-08-08:13:46:44 VALID N N SQL> select * from dba_views

2 where view_name = 'GV_$INSTANCE';

OWNER                          VIEW_NAME                      TEXT_LENGTH


------------------------------ ------------------------------ -----------

TEXT

TYPE_TEXT_LENGTH

TYPE_TEXT

OID_TEXT_LENGTH

OID_TEXT

VIEW_TYPE_OWNER                VIEW_TYPE


------------------------------ ------------------------------

SYS                            GV_$INSTANCE                           191

select
"INST_ID","INSTANCE_NUMBER","INSTANCE_NAME","HOST_NAME","VERSION","STARTU P_TIME","STATUS","PARALLEL","THREAD#","ARCHIVER","LOG_SWITCH_WAIT","LOGINS", Received on Tue Aug 29 2000 - 18:46:01 CDT