Re: Oracle utility to quickly compare two tables

From: Mike Rose <mmrose_at_home.com>
Date: Tue, 18 May 1999 02:01:11 GMT
Message-ID: <HR303.14932$u34.6730@news.rdc1.md.home.com>

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_at_home.com

Chuck <ccarson_at_phi.org> wrote in message news:3740a973.92721165_at_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
Received on Mon May 17 1999 - 21:01:11 CDT