3.6 SchemaDiff

3.6 SchemaDiff

DDL::Oracle is a very helpful resource, and many of the new Oracle tools coming off the open source Perl conveyer belt from SourceForge.net and FreshMeat.net are based upon it. In this section we'll take a look at one of these tools, Alistair Orchard's SchemaDiff program, which you can use to compare different Oracle schemas.

3.6.1 Installing SchemaDiff

You can obtain SchemaDiff from:

http://sourceforge.net/projects/schemadiff

We downloaded SchemaDiff-2.3.0.zip and ran it on Win32, after having expanded it into the C:\SchemaDiff directory. (It works equally well on Unix.)

We already had the SCOTT user set up on the ORCL database. We decided to set up the IRISH user on the MYDB database with exactly the same structure. Once we'd done this, we ran the following SQL while logged on as IRISH:

DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        SSN VARCHAR2(50),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        COMM NUMBER(7,2),
        DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
DROP TABLE EMP2;
CREATE TABLE EMP2
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP2 PRIMARY KEY,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        COMM NUMBER(7,2),
        DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO2 REFERENCES DEPT);

We wanted to check to see if SchemaDiff would notice that IRISH has the SSN (Social Security number) column added to the standard EMP table, and see if it would also spot the extra EMP2 table.

3.6.2 Running SchemaDiff

Let's see how SchemaDiff behaves with the database and user described in the previous section.

To get going, just start up the program:
```
$ perl SchemaDiff.pl
```
This will generate the left screen in Figure 3-11. Fill this in appropriately, connecting to the target databases as a DBA user.

Figure 3-11. Setting up SchemaDiff
On the second screen, use the selection boxes to link the two target schemas, in this case SCOTT=IRISH.
The third screen now allows you to choose options for generating various report formats and DDL files to upgrade one schema or the other, depending on which you prefer to be dominant.

When running SchemaDiff, we opted for the HTML report option and for the DDL scripts to be written from the point of view of the IRISH schema. You can see part of the HTML summary in Figure 3-12, along with some of the DDL generated within the IRISH.sql file.

Figure 3-12. Typical SchemaDiff output

You'll find that plenty of other fine treasures can be hauled from SchemaDiff. Check out its Mother-of-Perl SourceForge foundry for the latest version.

3.6 SchemaDiff

3.6.1 Installing SchemaDiff

3.6.2 Running SchemaDiff

Figure 3-11. Setting up SchemaDiff

Figure 3-12. Typical SchemaDiff output