|
|
| Re: database in sync [message #58855 is a reply to message #58850] |
Mon, 06 October 2003 07:21   |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Amit,
are you talking about schema structure or data ?
there are a number of tools such as OEM,Quest Software tools,DbDiff etc to compare schemas.
If you want to compare data,you will have to compare one table at a time using MINUS etc.. again there may be tools to compare the data or you can write your own..
-Thiru
|
|
|
|
|
|
| Re: database in sync [message #58867 is a reply to message #58865] |
Tue, 07 October 2003 12:34 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Ok,I will give you a small example comparing two tables. You can build on that..
thiru@9.2.0:SQL>create table t1 as select * from scott.emp;
Table created.
thiru@9.2.0:SQL>create table t2 as select * from scott.emp where rownum <=10;
Table created.
thiru@9.2.0:SQL>select * from t1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 20 STARTER
7499 SMITH SALESMAN 7698 20-FEB-81 1700 300 30 STARTER
7521 Ward SALESMAN 7698 22-FEB-81 1350 500 30 STARTER
7566 Jones MANAGER 7839 02-APR-81 18520 20 SENIOR
7654 Martin SALESMAN 7698 28-SEP-81 1350 1400 30 STARTER
7698 Blake MANAGER 7839 01-MAY-81 17746 30 JUNIOR
7782 Clark MANAGER 7839 09-JUN-81 15270 10 JUNIOR
7788 Scott ANALYST 7566 19-APR-87 18675 20 SENIOR
7839 King PRESIDENT 17-NOV-81 31059 10 SENIOR
7844 Turner SALESMAN 7698 08-SEP-81 1600 0 30 STARTER
7876 Adams CLERK 7788 23-MAY-87 1200 20 STARTER
7900 James CLERK 7698 03-DEC-81 1050 30 STARTER
7902 Ford ANALYST 7566 03-DEC-81 18675 20 SENIOR
7934 Miller CLERK 7782 23-JAN-82 1400 10 STARTER
1000 Victor DBA 7839 20-MAY-03 11000 0 10 JUNIOR
15 rows selected.
thiru@9.2.0:SQL>select * from t2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 20 STARTER
7499 SMITH SALESMAN 7698 20-FEB-81 1700 300 30 STARTER
7521 Ward SALESMAN 7698 22-FEB-81 1350 500 30 STARTER
7566 Jones MANAGER 7839 02-APR-81 18520 20 SENIOR
7654 Martin SALESMAN 7698 28-SEP-81 1350 1400 30 STARTER
7698 Blake MANAGER 7839 01-MAY-81 17746 30 JUNIOR
7782 Clark MANAGER 7839 09-JUN-81 15270 10 JUNIOR
7788 Scott ANALYST 7566 19-APR-87 18675 20 SENIOR
7839 King PRESIDENT 17-NOV-81 31059 10 SENIOR
7844 Turner SALESMAN 7698 08-SEP-81 1600 0 30 STARTER
10 rows selected.
thiru@9.2.0:SQL>select * from
2 ( select 'T1-T2',t1.* from t1 MINUS select 'T1-T2',t2.* from t2 )
3 UNION ALL
4 Select * from ( Select 'T2-T1',t2.* from t2 MINUS select 'T2-T1',t1.* from t1)
5 ;
'T1-T EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE
----- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
T1-T2 1000 Victor DBA 7839 20-MAY-03 11000 0 10 JUNIOR
T1-T2 7876 Adams CLERK 7788 23-MAY-87 1200 20 STARTER
T1-T2 7900 James CLERK 7698 03-DEC-81 1050 30 STARTER
T1-T2 7902 Ford ANALYST 7566 03-DEC-81 18675 20 SENIOR
T1-T2 7934 Miller CLERK 7782 23-JAN-82 1400 10 STARTER
thiru@9.2.0:SQL>insert into t2 values(2000,'Tom','ANALYST',7839,'05-MAY-95',30000,NULL,20,'SENIOR');
1 row created.
thiru@9.2.0:SQL>commit;
Commit complete.
thiru@9.2.0:SQL>( select 'T1-T2',t1.* from t1 MINUS select 'T1-T2',t2.* from t2 )
2 UNION ALL
3 ( Select 'T2-T1',t2.* from t2 MINUS select 'T2-T1',t1.* from t1);
'T1-T EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE
----- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
T1-T2 1000 Victor DBA 7839 20-MAY-03 11000 0 10 JUNIOR
T1-T2 7876 Adams CLERK 7788 23-MAY-87 1200 20 STARTER
T1-T2 7900 James CLERK 7698 03-DEC-81 1050 30 STARTER
T1-T2 7902 Ford ANALYST 7566 03-DEC-81 18675 20 SENIOR
T1-T2 7934 Miller CLERK 7782 23-JAN-82 1400 10 STARTER
T2-T1 2000 Tom ANALYST 7839 05-MAY-95 30000 20 SENIOR
6 rows selected.
But since MINUS will do a distinct,it wont take care of the following situation..
lets insert 'TOM' again into T2 ..
thiru@9.2.0:SQL>insert into t2 values(2000,'Tom','ANALYST',7839,'05-MAY-95',30000,NULL,20,'SENIOR');
1 row created.
thiru@9.2.0:SQL>commit;
Commit complete.
thiru@9.2.0:SQL>select * from t2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 20 STARTER
7499 SMITH SALESMAN 7698 20-FEB-81 1700 300 30 STARTER
7521 Ward SALESMAN 7698 22-FEB-81 1350 500 30 STARTER
7566 Jones MANAGER 7839 02-APR-81 18520 20 SENIOR
7654 Martin SALESMAN 7698 28-SEP-81 1350 1400 30 STARTER
7698 Blake MANAGER 7839 01-MAY-81 17746 30 JUNIOR
7782 Clark MANAGER 7839 09-JUN-81 15270 10 JUNIOR
7788 Scott ANALYST 7566 19-APR-87 18675 20 SENIOR
7839 King PRESIDENT 17-NOV-81 31059 10 SENIOR
7844 Turner SALESMAN 7698 08-SEP-81 1600 0 30 STARTER
2000 Tom ANALYST 7839 05-MAY-95 30000 20 SENIOR
2000 Tom ANALYST 7839 05-MAY-95 30000 20 SENIOR
12 rows selected.
thiru@9.2.0:SQL>
thiru@9.2.0:SQL>Select 'T2-T1',t2.* from t2 MINUS select 'T2-T1',t1.* from t1;
'T2-T EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE
----- ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
T2-T1 2000 Tom ANALYST 7839 05-MAY-95 30000 20 SENIOR
ie) it reports the Tom record only once..although these 2 records are missing from T1..
-- Maybe NOT IN can be helpful here..
thiru@9.2.0:SQL>select * from t2 where (empno,ename,job,mgr,hiredate,sal,comm,deptno,Grade) NOT IN
2 (select * from t1);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
2000 Tom ANALYST 7839 05-MAY-95 30000 20 SENIOR
2000 Tom ANALYST 7839 05-MAY-95 30000 20 SENIOR
but beware of NOT IN when the subquery returns NULL..
or you could also try NOT EXISTS..But you will have primary keys ,right ?
Hope this helps
Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
