Dermot's Tech Tips

During the offline data move, scripts are created to use BCP to dump out your data in SQL Server or Sybase to .dat files. Other scripts are created to use Oracle SQL*Loader to load the .dat files into the Oracle tables. Your SQL Server/ Sybase database will dump out the DATETIME and SMALLDATETIME values in formats which may not be recognized by Oracle. So you have to specify what the format mask is. SQL Server / Sybase DataTypes DATETIME has millisecond precision. SMALLDATETIME has only second precision. Oracle DataTypes DATE has only second precision TIMESTAMP has millisecond precision You can specify their format mask in the migration preferences. The preferences work like this The Timestamp Mask is applied to the DATETIME Sybase or SQL Server values The Date Mask is applied to the SMALLDATIME Sybase or SQL Server values. So now the full date value is recognized by SQL*Loader. The best bit is that even if you map Sybases/ SQL Servers DATETIME to Oracles DATE (which is the def...

This Quick Guide will run through the main steps you should take to perform a Sybase or SQL Server migration to Oracle. Installation Download the latest version of SQL Developer ( using 1.5.1 today), to your Development PC which can access your Oracle database and your SQL Server/ Sybase database. SQL Developer ships with the migration extensions built in. Download and Install Oracle XE if you dont have access to an exiting Oracle database. UnZip SQL Developer into its own directory (for example "SQLDev") Don't use an existing ORACLE_HOME or ORACLE directory Run sqldeveloper Setup the jtds driver (open source JDBC driver for Sybase and SQL Server) with SQL Developer JTDS can be got using the Check For Updates feature. Tools > Prefereneces > Extensions > Check For Updates . Walk through the wizard and choose any new migration updates and the JTDS driver. Setup Connections & Migration Repository Create a connection to Sybase or SQL Server Create a connection...

SQL Developer 1.5 released 8 weeks ago with the new Sybase capability, aiding migrations from Sybase 12 and Sybase 15 to Oracle. It worked pretty good, and we got some great feedback from customer using it in the real world on their migration projects. All the enhancements and now provided in SQL Developer 1.5.1 released on Monday. Thanks to all. Download it here http://www.oracle.com/technology/products/database/sql_developer/index.html Here are the main improvements Improved Offline Data Move Scripts including support for both DATETIME And SMALLDATETIME formats Variable Name Collision Management Temporary Table Name Collision Management Sybase Identity Columns Converted Correctly Emulation Packages Improved Many of the Issues noted by some customers regarding SQL Developer such as ORACLE_HOME issues, Connections dissapearing, NLS settings, .. are resolved. List of Migration Bug fixes for 1.5.1 Num Subject 7137280 SYBASE 12 LINUX OFFLINE CAPTURE SCRIPTS NOT AVAILABLE 7129820 C...

Noticed that sometimes I got an ORA-12519 error when using the oracle_ctl.bat file which calls SQL*Loader to upload each dat file into its Oracle table. When I ran the script line by line I got no problems. Seemed like SQL*Loader was a little to fast for Oracle XE. Found this on the forums Intermittent ORA-12519 error on 10g XE http://forums.oracle.com/forums/thread.jspa?messageID=1252551 After setting ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE and restarting the database, my oracle_ctl.bat file ran without issues.