Skip to Main Content
  • Questions
  • How to compare a table present in 2 different schemas in same server, as part of data migration

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, vinesh.

Asked: August 19, 2020 - 6:43 pm UTC

Last updated: August 21, 2020 - 7:52 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Team:

The DB verison we are using :

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Below are Sample table Generation scripts for reference:

I have two DB users by name USER_A and USER_B and we have a table by name EMP in both the DB users.
The Create table statements are shown below:
CREATE TABLE user_a.emp
(
empno     NUMBER(4),
ename     VARCHAR2(10 BYTE) DEFAULT 'XYZ',
job       VARCHAR2(9 BYTE),
mgr       NUMBER(4),
hiredate  DATE,
sal       NUMBER(7,2),
comm      NUMBER(7,2),
deptno    NUMBER(2)
);


CREATE TABLE user_b.emp
(
empno     NUMBER(4),
ename     VARCHAR2(10 BYTE) DEFAULT 'XYZ',
job       VARCHAR2(9 BYTE),
mgr       NUMBER(4),
hiredate  DATE,
sal       NUMBER(7,2),
comm      NUMBER(7,2),
dept_name    VARCHAR2(50) DEFAULT 'ACCOUNTING',
dept_location VARCHAR2(50)
);

On further obsertvation , we see that user_a.emp table has deptno column and user_b.emp table does not have the deptno column.
And user_b.emp table has dept_name and dept_location columns which are not present in user_a.emp table.

Requirement :

Can someone assist me in writing 2 alter statement(dynamically) wherein one alter statement is to generate the column names along with datatype and default_values
which are present in user_b.emp table and not present in user_a.emp table. I need this alter statement to add the missing columns .The table sizes are small but our project migration tables have a lot number of columns.

And the other alter statement to generate the column names along its datatype and default_values present in user_a.emp table and not present in user_b.emp table.

This would assist me in making tables to be in sync

and Chris said...

Writing a fully automated process to do this is tricky and risky.

For example user A has

deptno    NUMBER(2)


And user B has:

dept_name    VARCHAR2(50) DEFAULT 'ACCOUNTING',
dept_location VARCHAR2(50)


Should deptno be dropped when adding dept_name and dept_location? Really you want a person to review these differences and make the call.

You probably also want to sync all the constraints, indexes, etc. too. So rather than trying to roll your own, I'd use an existing tool.

The database diff option in SQL Developer is a good way to do this (Tools -> Database Diff...)

Create a connection for each user and you can compare them. This includes many options for how to do the comparison, a nice report of the differences, and the scripts to sync them up.

https://www.thatjeffsmith.com/archive/2012/09/using-database-diff-to-compare-schemas-when-you-dont-have-the-destination-user-password/
https://blogs.oracle.com/oraclemagazine/make-the-easy-move

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 20, 2020 - 1:49 pm UTC

Hi,

Agree with you on using the Sql Diff tool, but we want to do this for few tables only.

Further the user_a.emp table's deptno column has to be as it is and no changes to be done for this.

Thanks and Regards,

Chris Saxon
August 20, 2020 - 4:27 pm UTC

You can select which tables you compare with SQL Developer Database Diff.

It creates the alter table statements; you review and run them.

A reader, August 20, 2020 - 5:01 pm UTC

Hi,

For now we are using the below package and it should work.

select dbms_metadata_diff.compare_alter('TABLE','EMP','EMP','USER_A','USER_B') from dual;

Please let us know in case we miss something over here.

Thanks and Regards

Chris Saxon
August 21, 2020 - 7:52 am UTC

Good suggestion!

Thank You Note

A reader, August 28, 2020 - 4:27 am UTC

Thanks for your suggestion.