As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do?

I would assume the concepts are the same and the difference is merely the programming language but I have not seen the other side of the door.


2 Answers 2


Oracle and SQL Server have a number of architectural and idiomatic differences, and several key bits of terminology are used differently in the documentation. It's quite a few years since I did this, but some of the major idiomatic differences are:

  • Oracle has no direct equivalent to tempdb. Global temp tables are persistent entities and you do not create them on the fly as you would with a temp table in SQL Server.

  • Idiomatic PL/SQL is much more procedural than idiomatic T-SQL. On the flip side, PL/SQL has features to support fast cursor ops including parallel loop constructs. Often, intermediate cursor results are used in ways similar to the sort things one would use temp tables for in SQL Server. Look up how to use '%' to get some polymorphism in cursor variable types.

  • SQL*Plus is a command line tool similar to SQLCMD. On a vanilla installation it may be all you have available. If you want a GUI tool like SSMS you can get SQL Developer or third party tools such as TOAD. TOAD is quite expensive.

  • Learn the role of rollback segments (used in Oracle's MVCC) and the joys of the 'snapshot too old' error.

  • Get a metalink account if possible.

  • Read the concepts guide. It has a lot of useful starter information.

  • Tablespaces are roughly equivalent to filegroups.

  • SQL and the SQL dialect embedded within PL/SQL are not quite the same thing in Oracle.

  • Oracle didn't support recursive CTEs until quite recently. You can do recursive queries with CONNECT BY.

  • Read the docs for RMAN to see how to do backups. You can do a few neat tricks like merge differential backups into a single restorable backup.

  • The system data dictionary has 'ALL', 'USER' and 'DBA' versions of the tables. 'USER_' shows objects owned by the login. 'ALL_' shows all objects the login can read. 'DBA_*' is a global view, but you have to have appropriate permissions to see that. You will probably use the data dictionary more with Oracle than with SQL Server, so get to know it.

  • Clustered tables are called 'index ordered tables' in Oracle. 'Clustereed tables' in Oracle refer to a very different feature. In Oracle, a clustered table is a facility that allows physical storage of master/detail tables to be comingled based on a common key. The feature is not widely used.

  • Indexed views are called 'materialized views' in Oracle. They work somewhat differently, with enough nuances to merit their own question.

  • Oracle does not have a concept equivalent to a 'database' in SQL Server. In Oracle, a schema is just the owner of some database objects.

  • PL/SQL has an explicit module structure called 'packages'.

  • Look at using sequences instead of identity columns.

  • Getting a query plan is a bit fiddlier in older versions of Oracle (prior to 10g). You have to create a table (of the right format) to dump the plan to, then 'EXPLAIN PLAN' the query into the table to get the query plan. You can then get the query plan back out of the table. Everybody and their dog have favourite query plan queries. Do a google search and find one, then frig it to taste. 10g added a package called dbms_xplan, which simplifies working with query plans.

  • If you're running Oracle on a unix or linux host, having a working knowledge of shell scripting wouldn't hurt.

  • Oracle has support for autonomous transactions - you can commit an inner autonomous transaction and expect it to stay committed if the parent rolls back. SQL Server nested transactions are not true nested transactions and have different semantics. You could emulate this behaviour with a CLR stored procedure in SQL Server by opening an independent connection and performing the autonomous operation through that.

Oracle comes with less B.I. tooling out of the box, although Oracle do sell OLAP servers, reporting tools and ETL tooling.

  • Oracle bundle a bulk load tool called SQL*Loader that is similar in function to bcp.

  • The closest equivalent to SSIS is Oracle Warehouse Builder (OWB), but this is a separate item and not bundled with the base system.

  • Oracle make a reporting tool called Oracle Reports, but this is also not bundled with the base system. This is the closest equivalent to Reporting Services.

  • Oracle also make a ROLAP tool called Discoverer. This is the closest product to Report Builder that Oracle produce. More recent versions ('Drake') have support for Oracle's OLAP engine.

  • Oracle used to have an OLAP product called Express. At one point they built it into the core Oracle DBMS engine, storing its native data structures in opaque blobs. It is not totally compatible with Express, and has a propritary interface with very little support from third-party OLAP front-end tools.

  • Oracle B.I. Enterprise Edition is what used to be Siebel Analytics. This has some OLAP functionality but I'm not familiar with it. Maybe someone who has used it might like to comment. Without directly admitting that Siebel was a completely separate product from its own B.I. tooling, Oracle stated that there was 'no upgrade path' from Oracle B.I. Standard Edition to Enterprise Edition. Note that both the Standard and Enterprise B.I. suites are separate products and not bundled with the base system.

  • In practice, the closest thing to SSAS sold by Oracle is Essbase (formerly Hyperion). This is a separate product and not bundled with the base system.

Not surprisingly, Oracle's B.I. strategy is viewed by industry pundits as something of a dog's breakfast.

There are probably other things that I can't think of OTOH; I'll add them as I think of them.

  • 1
    Wow this is very close to what I was looking for. Great information! I read that Oracle has SQL server type jobs that can be ran automated. Anything equivalent to SSIS in Oracle? Commented Dec 27, 2011 at 20:37
  • 1
    The closest thing to SSIS in Oracle is Oracle Warehouse Builder (OWB) but it is not bundled with the base system. You have to purchase it separately. There is a bulk load tool similar to bcp called SQL*Loader. Commented Dec 27, 2011 at 22:36
  • 6
    Things I would add to this list: in Oracle readers never block writers and writers never block readers. My understanding is that this is still different in a default installation in SQL Server. Oracle never allows to read uncommitted data. You hardly use temp tables in Oracle - doing everything in one big statement is very often a lot faster. Frequent commits will slow down bulk loading. Ideally one should just issue a single commit once everything is done.
    – user1822
    Commented Dec 27, 2011 at 23:56
  • 1
    @a_horse_with_no_name your understanding is correct Commented Dec 28, 2011 at 6:39
  • 2

We work with both Oracle and SQL Server, and our DBA lead, Michelle Malcher, frequently helps coworkers with this transition. Based on this experience, she wrote a book entitled "Oracle Database Administration for Microsoft SQL Server DBAs (Oracle Press)". I liked this book.

I am not sure if recommending a book by a coworker is appropriate for this site. I do not report to Michelle, I lead a team of developers.

  • 2
    I remember getting involved on a conversation about self promotion on meta.stackoverflow.com and replying that the grey area around self-promotion was OK if it was relevant to the question. Jeff Atwood agreed. Commented Jan 3, 2012 at 18:44
  • 2
    I don't work with Michelle, but I liked this book a lot. I actually found this way of explaining - comparing SQL Server architecture with that of Oracle more interesting than say, reading a standard Oracle DBA book.
    – RK Kuppala
    Commented Mar 21, 2012 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.