Finding Differences Across Two Tablespaces

[tmcneil]

I have two tablesspaces with tables, views and etc. I would like to write a SQL statement that would compare what tables are not in the other tablespaces. For instance, I wrote this SQL statement to determine the table commonalities between two tablespaces.

SELECT * FROM (SELECT TABLE_NAME AS DB1_TABLE_NAME
               FROM all_all_tables
               WHERE owner='DB1') a,
              (SELECT TABLE_NAME AS DB2_TABLE_NAME
               FROM all_all_tables
               WHERE owner='DB2') b
WHERE a.DB1_TABLE_NAME=b.DB2_TABLE_NAME
ORDER BY a.DB1_TABLE_NAME;

How would I do the opposite of this? If DB1 and DB2 had these tables:

DB1: A, B, C and D
DB2: B, C, D, and E

Therefore, the differences would be DB1 has A and DB2 has E. What would be the correct syntax?

Thanks,
Todd

 

[SantaMufasa]

Todd,

First, in the Oracle World, where you have used the term tablespaces, you, instead, should use any of these terms: "user", "owner", or "schema".

In Oracle, a tablespace is simply the logical storage facility for database objects where you can find objects residing from any user, owner, schema that a) has permission to store objects in that tablespace, b) has available storage quota in that tablespace, and c) chooses to store objects in that tablespace. Therefore, one tablespace may be the "home"/storage space for all of the objects of all of the (non-SYS/non-SYSTEM) users in your database.

Now, to your query at hand. To list each of the tables that reside in one Oracle schema for which there are not matching table names in another Oracle schema, you can say:

col a heading "Tables in DB1 not in DB2" format a30
SELECT TABLE_NAME AS a FROM dba_tables WHERE owner='DB1'
MINUS
SELECT TABLE_NAME FROM dba_tables WHERE owner='DBs';

To obtain the inverse, just swap the schema names (and change the "heading" title).

Let us know if this resolves your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

The very instant that I hit the [Submit Post] button, I saw (in my code, above) that 'DBs' should be 'DB2'...Sorry.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[tmcneil]

Well, that's cool and it works well. What if I wanted to add another schema to the query? I tried to add the typical MINUS then anotehr SQL statement and there's an error.

Todd

 

[Turkbear]

Hi,
MINUS works with 2 sets of data, so maybe this would do it

SELECT TABLE_NAME As a FROM dba_tables WHERE owner = DB3
MINUS
(
SELECT TABLE_NAME AS a FROM dba_tables WHERE owner='DB1'
MINUS
SELECT TABLE_NAME FROM dba_tables WHERE owner='DB2';
)

Not sure though...but it should show all tables in the DB3 schema that do not exist in either the DB1 or DB2 schemas..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[carp]

If you want to find tables that are in some schemas but not in others, this might do the trick:

SELECT a.owner, a.table_name 
FROM (SELECT table_name, count(*) schemas 
        FROM all_tables
	   WHERE owner IN ('OWNER1', 'OWNER2', 'OWNER3', 'OWNER4')
	   GROUP BY table_name
	   HAVING count(*) BETWEEN 1 AND 3) v, 
	   all_tables a
WHERE a.table_name = v.table_name
ORDER BY 2,1;

This approach requires you to (1) list out all of the schemas you want to compare, (2) count how many schemas you've listed, and (3) put the correct number (# of schemas - 1) as the upper limit in the BETWEEN condition. In return, you can compare an arbitrarily long list of schemas.

If you wanted to find a list of tables that are in more than one of the schemas but not in all of the schemas, change the lower limit of the BETWEEN condition to 2.

 

[carp]

Ooops. Just noticed a bug in the code. The above query will bring up owner.table combinations that are not in your list of owners. Try this instead:

SELECT a.owner, a.table_name 
FROM (SELECT table_name, count(*) schemas 
        FROM all_tables
       WHERE owner IN ('OWNER1', 'OWNER2', 'OWNER3', 'OWNER4')
       GROUP BY table_name
       HAVING count(*) BETWEEN 1 AND 3) v, 
       all_tables a
WHERE a.table_name = v.table_name
  AND a.owner IN ('OWNER1', 'OWNER2', 'OWNER3', 'OWNER4')
ORDER BY 2,1;