Xref: alice comp.databases.oracle.server:49925
Path: alice!news-feed.fnsi.net!netnews.com!nntp.abs.net!newshub2.home.com!news.home.com!news.rdc1.md.home.com.POSTED!not-for-mail
From: "Mike Rose" <mmrose@home.com>
Newsgroups: comp.databases.oracle.server
References: <3740a973.92721165@client.sw.news.psi.net>
Subject: Re: Oracle utility to quickly compare two tables
Lines: 72
MIME-Version: 1.0
Content-Type: text/plain;
 charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2014.211
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211
Message-ID: <HR303.14932$u34.6730@news.rdc1.md.home.com>
Date: Tue, 18 May 1999 02:01:11 GMT
X-Complaints-To: abuse@home.net
X-Trace: news.rdc1.md.home.com 926992871 24.3.63.23 (Mon, 17 May 1999 19:01:11 PDT)
NNTP-Posting-Date: Mon, 17 May 1999 19:01:11 PDT
Organization: @Home Network


Chuck,

I don't know of an Oracle utility, but you can use a script to generate some
rather simple SQL statements for each of the tables in question.

The actual SQL (example below) would have each table's name in it:

Select * from Schema1.Table1
MINUS
Select * from Schema2.Table2

The output of the SQL would be the difference(s) ---
no rows, the data in the tables are identical!

You can get the tablenames from SYS.ALL_TABLES by specifying the OWNER of
each of the two (2) Schemas

OWNER VARCHAR2 30   N
TABLE_NAME VARCHAR2 30   N
TABLESPACE_NAME VARCHAR2 30   Y
CLUSTER_NAME VARCHAR2 30   Y
IOT_NAME VARCHAR2 30   Y
PCT_FREE NUMBER 22   Y
PCT_USED NUMBER 22   Y
INI_TRANS NUMBER 22   Y
MAX_TRANS NUMBER 22   Y
INITIAL_EXTENT NUMBER 22   Y
NEXT_EXTENT NUMBER 22   Y
MIN_EXTENTS NUMBER 22   Y
MAX_EXTENTS NUMBER 22   Y
PCT_INCREASE NUMBER 22   Y
FREELISTS NUMBER 22   Y
FREELIST_GROUPS NUMBER 22   Y
LOGGING VARCHAR2 3   Y
BACKED_UP VARCHAR2 1   Y
NUM_ROWS NUMBER 22   Y
BLOCKS NUMBER 22   Y
EMPTY_BLOCKS NUMBER 22   Y
AVG_SPACE NUMBER 22   Y
CHAIN_CNT NUMBER 22   Y
AVG_ROW_LEN NUMBER 22   Y
AVG_SPACE_FREELIST_BLOCKS NUMBER 22   Y
NUM_FREELIST_BLOCKS NUMBER 22   Y
DEGREE VARCHAR2 10   Y
INSTANCES VARCHAR2 10   Y
CACHE VARCHAR2 5   Y
TABLE_LOCK VARCHAR2 8   Y
SAMPLE_SIZE NUMBER 22   Y
LAST_ANALYZED DATE 7   Y
PARTITIONED VARCHAR2 3   Y
IOT_TYPE VARCHAR2 12   Y
TEMPORARY VARCHAR2 1   Y
NESTED VARCHAR2 3   Y
BUFFER_POOL VARCHAR2 7   Y

Mike Rose

mmrose@home.com

Chuck <ccarson@phi.org> wrote in message
news:3740a973.92721165@client.sw.news.psi.net...
>
> I have two identical environments under different schemas wherein all
> objects are the same. I want to quickly tell if two tables differ at
> all, as in any field of any row. This is a large ERP product and I am
> wondering if their is any Oracle utility to do this without complex
> SQL queries.
>
> Thanks,
> Chuck