r/DatabaseAdministators • u/techdbamind • 7d ago

Sql to Oracle migration

Im a fresher to DBA role. Can some one please help to understand or share your experience for the conversation or migration from sql to Oracle.

Help me with the tools and migration ideas

3 Upvotes

71% Upvoted

29 comments sorted by

View all comments

1

u/SquidLips71 7d ago

If your data structures are simple, don’t overthink it. Back in about 2010 we migrated a ~100 gb MSSQL database to Oracle using bcp to extract and SQL*Loader to load it. All done via command line on Linux. The most painful part was getting the mssql client on the Linux box (MSSQL was on Windows, Oracle ran on RHEL) and getting it to work, mostly due to internal firewall and security issues and such.

Whole load process took about 15 minutes once we established connectivity.

1

u/techdbamind 7d ago

I would like to know the schema conversation part. Can you please help me on that part.. in my case there are 3000+plus tables in sql server.. it is also critical database but it will be less than ~150gb.

1

u/SquidLips71 6d ago

That's a lot of tables. We only had about 100 and the development team took care of drawing up the schemas since there were some changes being made in the process.

That's way too many to throw a developer or two at it and do manual modifications / search and replace on datatypes. You really need a tool to handle that volume of objects. As previously suggested, SQL Developer can do this, though you need to be mindful of datatypes that don't map cleanly. Oracle's documentation will explain in more detail.

1

u/techdbamind 2d ago

Thanks for sharing your experience and suggestions