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
Thanks and Regards !!
Message was edited by: 622930 Message was edited to include the Oracle Version we are using.
Message was edited by: 622930 Message was edited to format the create statements.