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