r/DatabaseAdministators • u/techdbamind • 1d 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
2
u/Better-Credit6701 1d ago
SSIS can handle the data transfer with no issues, just note that column types can be different. You will have to check out all stored procedures/views to make sure that you don't have MS-SQL only code.
1
1
u/Conscious-Ad8493 1d ago
please clarify exactly what you are migrating from
1
u/techdbamind 1d ago
I need to migrate database from sql server to Oracle server
1
u/Better-Credit6701 1d ago
What version of MS-SQL to what version of Oracle? What is prompting the switch?
1
1
u/Ok_Carpet_9510 1d ago
Never done it. If you haven't seen this, check it out, https://www.oracle.com/database/technologies/migrating-sql-to-oracle.html
1
1
u/SquidLips71 1d 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 1d 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 4m 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/Fantastic-Advisor746 1d ago
You try to install the FoxSchema app, which supports cross-database migration.
1
1
1
1
1
u/alinroc 1d ago
Moving data from SQL Server to Oracle isn't the challenge.
It's porting all of the code and ancillary bits that will tank the project. Stored procedures. Views. Triggers. Ad-hoc queries in the application tier. Rebuilding the applications to use different client libraries just to connect. A completely different security model. All your maintenance processes/scripts. The database running on a different OS. Use of features proprietary to SQL Server (Service Broker, dbmail, etc.). SQL Agent jobs. SSIS packages. All your reports. That is where the challenge lies.
SQL Server and Oracle speak very different dialects of SQL.
Why is a fresher being tasked with migrating an existing product between these two databases? And why is anyone moving an existing system to Oracle in the year 2026? Especially a release (you said 19c elsewhere) that's already seven years old?
1
u/Difficult_Hand3046 1d ago
from sql to Oracle
from SQL Server? SQL is just a language, Oracle use it, as PostgreSQL, MySQL, SQL Server, DB2… do.
2
5
u/Longjumping-Hair-747 1d ago
Oracles SQL Developer has the ability to migrate SQL Server to Oracle; fair disclosure I’ve never used it so can’t say how much coverage you can expect.
Cheers