• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

How to compare 2 VIEWS in Oracle

I would like to know the differences in columns between 2 Views.
To give a simplify example View A_Old can have 3 while View A can have 5 columns.
There are 3 columns common to both views. How can I create a query to return the columns which arent present in both
0
diteps06
Asked:
diteps06
  • 4
  • 3
4 Solutions
 
sdstuberCommented:
if you know VIEW_A has more columns than VIEW_A_OLD try this...
select column_name,data_type from dba_tab_columns where owner = 'NEW_OWNER' and table_name = 'VIEW_A'
minus
select  column_name,data_type from dba_tab_columns where owner = 'OLD_OWNER' and ttable_name = 'VIEW_A_OLD'

Open in new window

0
 
sdstuberCommented:
if you have no idea what the difference between views A and B might be,  then try this...
  SELECT   owner, table_name view_name, column_name, data_type
    FROM   dba_tab_columns dtc
   WHERE   owner IN ('VIEW_A_OWNER', 'VIEW_B_OWNER')
       AND table_name IN ('VIEW_A', 'VIEW_B')
       AND EXISTS (SELECT   *
                     FROM   (SELECT   *
                               FROM   (SELECT   column_name, data_type
                                         FROM   dba_tab_columns
                                        WHERE   owner = 'VIEW_A_OWNER' AND table_name = 'VIEW_A'
                                       UNION
                                       SELECT   column_name, data_type
                                         FROM   dba_tab_columns
                                        WHERE   owner = 'VIEW_B_OWNER' AND table_name = 'VIEW_B')
                             MINUS
                             SELECT   *
                               FROM   (SELECT   column_name, data_type
                                         FROM   dba_tab_columns
                                        WHERE   owner = 'VIEW_A_OWNER' AND table_name = 'VIEW_A'
                                       INTERSECT
                                       SELECT   column_name, data_type
                                         FROM   dba_tab_columns
                                        WHERE   owner = 'VIEW_B_OWNER' AND table_name = 'VIEW_B')) x
                    WHERE   x.column_name = dtc.column_name AND x.data_type = dtc.data_type)
ORDER BY   owner, view_name, column_name

Open in new window

0
 
QlemoC++ DeveloperCommented:
I like this one much more, as you have to edit things on one place only:

select    a.column_name,a.data_type, b.column_name, b.data_type
from dba_tab_columns a full join dba_tab_columns b
  on a.column_name = b.column_name and a.data_type = b.data_type
 and a.owner = 'OWNER_NEW' and a.table_name = 'VIEW_NEW'
 and b.owner = 'OWNER_OLD' and b.table_name = 'VIEW_OLD'
where a.column_name is null or b.column_name is null
   or a.data_type != b.data_type

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
diteps06Author Commented:
The question is about View which a join of one or more tables.
It doesn't regard Tables.
I expected functions like DBA_VIEWS OR VIEW_NAME
0
 
sdstuberCommented:
did you try the queries?

dba_tab_columns has columns for both tables and views
0
 
diteps06Author Commented:
I had the error 'table or view doesn't exist' refering to dba_tab_columns.
Can it be a priviliege problem
0
 
sdstuberCommented:
Maybe, try all_tab_columns instead
0
 
diteps06Author Commented:
Thanks alot.
I used the query when you have no idea about the differences between the Views.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now