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.
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.
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.
