Home » SQL & PL/SQL » SQL & PL/SQL » how to compare two schemas and get all tables that are not in other schema
how to compare two schemas and get all tables that are not in other schema [message #266033] Sun, 09 September 2007 03:49 Go to next message
satishveluru
Messages: 8
Registered: September 2007
Junior Member
hello
i have two schemas A and B
1) schema A having 100 tables
2) schema B having 50 tables(these are already in schema A)

now my requirement is i want to create remaining tables of schema A in schema B (with all constraints)

is ther any procedure or plsql code to fetch all tables that are not in schema B
Re: how to compare two schemas and get all tables that are not in other schema [message #266034 is a reply to message #266033] Sun, 09 September 2007 04:14 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is ther any procedure or plsql code to fetch all tables that are not in schema B

No but you can use OEM / Change management to get difference between schemas.
You can also query the dictionary and generates the statements something like:
select 'create table schema2.'||table_name||' as select * from schema1.||table_name||';'
from ( select table_name from dba_tables where owner='SCHEMA1'
       minus
       select table_name from dba_tables where owner='SCHEMA2' )
/

Regards
Michel

Previous Topic: map the oracle user name
Next Topic: WITH CHECK OPTION !!!!!
Goto Forum:
  


Current Time: Sat Dec 10 01:28:33 CST 2016

Total time taken to generate the page: 0.06226 seconds