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

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, vinesh.

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

Answered by: Chris Saxon - Last updated: August 21, 2020 - 7:52 am UTC

Category: SQL - Version: 11g

Viewed 100+ 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 we 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

and you rated our response

  (3 ratings)

Reviews

August 20, 2020 - 1:49 pm UTC

Reviewer: A reader

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

Followup  

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.

August 20, 2020 - 5:01 pm UTC

Reviewer: A reader

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

Followup  

August 21, 2020 - 7:52 am UTC

Good suggestion!

Thank You Note

August 28, 2020 - 4:27 am UTC

Reviewer: A reader

Thanks for your suggestion.