The Jonathan Lewis Pages

New V$ items in Oracle 8 (and a demo of symmetric difference)

One of the first things I do with a new version of Oracle (even a minor release) is to check if there are any changes in the v$ performance monitoring view. This note describes how I do it, and highlights a few of the interesting differences between 7.3.3.5 and 8.0.4.2.

To list the definitions of the V$ views, I take the simple minded approach that the views themselves will all have names starting with 'V_$' - if you check catalog.sql you will find sections of code like the following - so it seems a good bet to look at the dictionary view USER_TAB_COLUMNS where table_name like 'V_$%' to get the information I need.

	create or replace view v_$session as selcet * from v$session;
	drop public synonym v$session;
	create public synonym v$session for v_$session;

The approach I take to identifying the changes is to get both instances up and running at once, then create database links between the two so that I can check the symmetric difference. For those not in the know, the symmetric difference is simply the list of the bits where two table do not overlap - Oracle does not support this feature, although it supports the rest of the set-based operations - UNION, INTEREST, and MINUS.

To get at the symmetric difference in Oracle, we have two options:

	(A union B) minus (A intersect B)
or	(A minus B) union (B minus A)

The second option is the most appropriate for getting the difference whilst including a tag to show which table the data comes from. The code below is the script I used to find the differences between the two sets of V$ tables currently on my system.

Back to Main Index of Topics

rem
rem	Script:		get_v2.sql
rem	Author:		Jonathan Lewis
rem	Dated:		14-Aug-98
rem	Purpose:	Report changes in V$ views across versions
rem
rem	Notes:
rem	------
rem	Has to be run by a DBA-type.
rem	Needs a couple of database links defined before you use it.
rem	The version of SQL has to support the ESCAPE option for wildcards
rem	
rem	Alternatively:
rem		Use substr(table_name,2,1) = '_'
rem	or	Replace '_' with '#' (say)
rem
rem	The version column (7 or 8) tags which instance the data came from
rem

set pagesize 0
set trimspool on
column table_name format a29
column column_name format a27
column version format 99 heading Vn

break on table_name skip 1

spool v_list

select * from 
	(
		select table_name,column_name,8 version
		from user_tab_columns@D804 
		where table_name like  'V\_$%' escape '\'
	MINUS
		select table_name,column_name,8 version
		from user_tab_columns@D733
		where table_name like  'V\_$%' escape '\'
	)
union all	-- there will be no duplicates, so union all is safe
	(
		select table_name,column_name,7 version
		from user_tab_columns@D733 
		where table_name like  'V\_$%' escape '\'
	MINUS
		select table_name,column_name,7 version
		from user_tab_columns@D804
		where table_name like  'V\_$%' escape '\'
	)
order by 1,3,2
;

spool off

Back to Main Index of Topics

Sample of Output

V_$INSTANCE                   KEY                           7
                              VALUE                         7
                              ARCHIVER                      8
                              HOST_NAME                     8
                              INSTANCE_NAME                 8
                              INSTANCE_NUMBER               8
                              LOGINS                        8
                              LOG_SWITCH_WAIT               8
                              PARALLEL                      8
                              SHUTDOWN_PENDING              8
                              STARTUP_TIME                  8
                              STATUS                        8
                              THREAD#                       8
                              VERSION                       8

660 rows selected.

Back to Main Index of Topics

You will notice that there are rather a lot of differences between the two - a total of 660 column changes. It is a matter of opinion which ones are most interesting or useful, but from my viewpoint the ones I particularly like are:

There are also many more views for:

Back to Main Index of Topics