SET SCAN OFF;
CREATE USER TestMigration IDENTIFIED BY TestMigration
 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
 GRANT CREATE SESSION, RESOURCE, CREATE VIEW TO
TestMigration;connect TestMigration/TestMigration;

CREATE SEQUENCE  Employees_EmployeeID_SEQ
  MINVALUE 0 MAXVALUE 999999999999999999999999
  INCREMENT BY 1  NOCYCLE ;
CREATE TABLE Employees (
  EmployeeID NUMBER (11,0) NOT NULL,
  LastName VARCHAR2 (20) NOT NULL,
  FirstName VARCHAR2 (10) NOT NULL,
  Title VARCHAR2 (30),
  TitleOfCourtesy VARCHAR2 (25),
  BirthDate DATE,
  HireDate DATE,
  Address VARCHAR2 (60),
  City VARCHAR2 (15),
  Region VARCHAR2 (15),
  PostalCode VARCHAR2 (10),
  Country VARCHAR2 (15),
  HomePhone VARCHAR2 (24),
  Extension VARCHAR2 (4),
  Photo BLOB,
  Notes CLOB,
  ReportsTo NUMBER (11,0)
);

CREATE INDEX LastName ON Employees
(
  LastName
)
 ;
CREATE INDEX PostalCode ON Employees
(
  PostalCode
)
 ;

connect TestMigration/TestMigration;

CREATE OR REPLACE VIEW TestQuery AS

SELECT Employees.EmployeeID,
       Employees.LastName,
       Employees.BirthDate,
       Employees.Address,
       Employees.Country,
       Employees.HomePhone
  FROM Employees;



connect TestMigration/TestMigration;

CREATE OR REPLACE TRIGGER Employees_EmployeeID_TRG 
BEFORE INSERT OR UPDATE ON Employees
FOR EACH ROW
BEGIN
  if inserting and :new.EmployeeID is NULL then
  SELECT Employees_EmployeeID_SEQ.nextval into: 
  new.EmployeeID FROM DUAL;
  end if;
END;

/

ALTER VIEW TestQuery COMPILE;

Essentially a new schema TestMigrate, will be created in Oracle 10G XE when this script is run whose authentication is (Username/Password) TestMigrate/TestMigrate. The script when executed will create all the objects.

Creating New User and Schema Objects

Now when you run this script, you will have created all the objects. You can click on the Run Script (F5), as shown, to run the generate script.

This brings up the Select Connection window as shown where you need to specify a connection. The oracle connection conMigrate is chosen as shown.

When you choose the conMigrate, some of the information gets displayed in the Select Connection window as shown.

Now click on the OK button. After the script is successfully run, the Script Output window shows the result of running the script as shown.

Reviewing Meta Data in Oracle 10G XE

Now you can login to Oracle 10G XE with the credentials [username/password] TestMigration/TestMigration and browse the objects clicking on the Object browser. You will be seeing all the objects created. The meta data is there but the objects are empty of data.

Copying the Data

In the previous step you have all the meta data, but the data in the source needs to be transferred to those objects. Click on the Main menu Migrate and from the drop-down choose Migrate Data as shown in the next figure.

This brings up the Data Move Details window. Click on the drop-down handles appropriately and choose the source and target connections as shown. You may have to choose the Converted Model as well although there is just one in this example.

Click on the OK button on the Data Move Details window after making all the selections.

This brings up the Moving Data to Oracle window which also displays progress. The final display when the tables are completed, is as shown in the figure.

Close this window by clicking the Close button.

Reviewing Migrated Data

Now login to the Oracle 10G XE once again, to the user schema TestMigration with username/password pair to match TestMigration/TestMigration, and click on the object browser as before. Now you see the data in the Employee table as shown. Data movement during movement of data to Oracle was sluggish and the button has to be clicked a couple of times before the Completed Tables showed up.

Similarly the view TESTQUERY is created from the MS Access query, TestQuery in the source as shown.

Similarly, you can view indexes, triggers etc. The linked table, of course, does not migrate.

Conclusion

Oracle SQL Developer 1.2 is an excellent tool for migrating MS Access 2003 database to Oracle 10G XE. The steps are clear and well laid out. The data mapping between the source and the target is very clear and can be modified. Also as described, the capturing process showed objects other objects in addition to the intended ones (see Figure 15). Not sure if this is a bug. Copying the data step had to be clicked multiple times before the Completed Tables showed up.