oraclesql server
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.
As your own question answers: "Oracle Applications is an [ERP]." An "Oracle Apps DBA" will be someone who is experienced in working with the particular schema and tooling around this ERP. There are hundreds (if not thousands) of packages, tables, stored procedures, and interfaces that are specifically built for the ERP. It is not a trivial database to admin. You can get a brief glimpse into some of the things in their ERP by looking around - for example, this blog - or by logging into a Metalink account as someone who is a customer of their ERP solution, where you can get ERDs and other documentation.
I've swapped between working on Oracle and SQL Server over the past few years, and wrote a blurb on going the other way here. There are a number of idiomatic and architectural differences, and various bits of terminology get used differently by the vendor and developer/DBA communities surrounding each product.
Physical architecture
SQL Server organises various things a bit differently to Oracle and has one or two key concepts that have no direct analogues in Oracle.
A 'Database' is a separate item in SQL Server, with its own user permissions, schemas/name spaces and storage. If you're familiar with Sybase, they work much the same as databases in Sybase, due to the common origins of the product.
Filegroups are roughly equivalent to table spaces, although they are local to a database.
A schema is a distinct concept from a database user in SQL Server, although users can have a default schema.
MVCC works somewhat differently in SQL Server. It's a relatively recent feature, maintaining different copies of a row until the locks on the old version are released. SQL Server has no direct equivalent to a rollback segment. It is not active by default on SQL Server databases.
Tempdb is much more heavily used in SQL Server. The system uses it for temporary tables and intermediate join results. More on tempdb later.
Table partitioning is somewhat clumsier than Oracle. You need to set up a partition function that creates a partition key from whatever you are supplying it, then a partition scheme over that partition function. The partition scheme behaves a bit like a filegroup in that you then create the table on the partition scheme.
Swapping partitions in and out requires you to set up a constraint on an empty table in the right structure. The constraint guarantees that the partition key values are appropriate to the partition you intend to swap into it.
Materialised views are called indexed views in SQL Server. The GROUP BY clause does have a CUBE operator, and the documentation alludes to a query rewrite feature. However, this functionality is not well documented and may not be terribly mature. YMMV.
SQL Server does not support autonomous transactions, although it does support two-phase commit through XA or OLEDB transaction protocols.
Clustered indexes are slightly different from index-ordered tables in Oracle, as they don't require all of the columns in the table to participate in the clustered index. They're much more widely used in SQL Server architecture than IOTs are in Oracle.
SQL Server does support covering indexes, but doesn't have join indexes. Bitmap indexes are not supported, although it does have an index intersection/star transform operator that can calculate intersections without hitting the fact table.
Sequences are a relatively recent addition to SQL Server. Traditionally autoincrementing keys are done through identity columns. You can load values into an identity column through set identity_insert on.
Programming
Idiomatic T-SQL has some differences to idiomatic PL/SQL. It works differently enough that some of the paradigmatic differences merit explaining in more depth.
T-SQL has no concept of a package. All stored procedures and functions in a database live in a common namespace, although schemas can be used to break this up, and the namespace is local to a database.
Get a feel for how to use temporary tables, and SELECT INTO. It's pretty rare to encounter T-SQL code that actually needs a cursor; temporary tables allow operations to be broken up into steps that can be done with set operations. SELECT INTO in tempdb is minimally logged, and it is minimally logged in certain recovery modes on user databases as well, so it is just as fast as query operator that persists an intermediate join result.
Idiomatic T-SQL will use temp tables in the sort of roles that you would see cursor variables in PL/SQL, but will make much more use of set operations. Temp tables can, however make for fairly obtuse code, so use with care.
The system data dictionary was much more obtuse than Oracle's one on older versions, but it got much better with SQL Server 2005. Although the tools supplied by Microsoft have quite a lot of introspection stuff built in in the SSMS explorer, it's still worth knowing your way around the data dictionary. It doesn't differentiate between ALL, USER and DBA views of the DB objects, though.
SSMS has a query plan viewer built in.
Identifiers in T-SQL code can be quoted with [], and can contain all sorts of rubbish if quoted. However, if we catch you calling a column 'Direct/Transfer', we will rip your intestines out.
SQL Server does have window functions (since 2005 IIRC), so you can do ordering, running sums and suchlike within groups now.
T-SQL has no direct equivalent to CONNECT BY, although recursion can be done through recursive CTEs.
If you need to write code that hops across databases (as opposed to schemas within a database), consider using public synonyms to alias the objects to something local and refer to the aliases in the code. This avoids hard-coded dependencies on database names.
If you avoid hard coded dependencies on database names, databases make it quite easy to maintain multiple environments on the same server.
Some things, such as custom aggregate functions, can only be implemented using CLR sprocs. Also, if you want to escape from a transaction context (e.g. to fake an autonomous transaction for rollback-proof error logging) you can use a CLR sproc, as it can create a local connection outside of the current transaction context.
Security
Logins are defined at the SQL Server instance level, but each login maps to zero or more databases as a 'database user'. Permissions cam be assigned to both 'logins' (server) and 'users' (database) but in a database 'roles' are normally used. Users belong to roles, permissions are assigned to roles. SQL Server 2012 adds 'server roles'.
SQL Server 2012 introduces a concept called 'partially contained databases', which allows user and role information to be held local to that database.
Within a database, the concept of user and schema is separated. A user or role can be assigned to a schema and a schema owns database objects.
Windows authentication uses login information behind the scenes to authenticate a user on a machine or domain to a SQL Server login. IIRC support for this is an optional extra on Oracle.
A special role, 'dbo' (short for 'database owner') has a sort of super-user privilege within a specific database. Each database has a 'dbo' role and users can be assigned to the 'dbo' role on a given database.
There is also a default 'dbo' schema. Objects may be owned by the dbo schema - Objects created by users with the 'dbo' role (or system-wide admin permissions) will default to being owned by the 'dbo' schema unless another schema is supplied explicitly.
Security information is not kept with a backup of a single database. Users must and roles must be configured explicitly on the server the backup is restored to. SQL Server 2012 allows user and role data to be kept locally to a database with a new 'partially contained databases' feature.
From SQL Server 2005, stored procedures can be executed in the security context of the caller, the creator, the owning schema or a specified user.
In a view on SQL Server, permissions on underlying tables are based on the permissions of the schema that owns the view. User permissions on the underlying tables do not participate in the security, although a view definition can include filters that get information from the session. In Oracle, user permissions on the underlying tables may affect the view, depending on the configuration of the grants.
Monitoring and tuning
TBA - memory architecture vs. SGA etc. in oracle
Backup and Recovery
TBA
Tooling
Microsoft bundles a set of surrounding tools with SQL Server. Some of the major items supplied are:
SQL Server Management Studio (SSMS): This does something similar to SQL Developer on Oracle - it provides an editor and code execution facility. Some useful features include a database object browser and a query plan viewer.
SQL Server Analysis Services (SSAS): This is an OLAP server that is distinct from the database server. It uses its own query language (MDX) and API (XML/A) for client-server communication. It cannot be queried with SQL. SSMS has a facility for editing MDX and raw XMLA queries and displaying the results. A command line query tool called ASCMD.EXE is also supplied.
SQL Server Reporting Services (SSRS): This is a web-based reporting tool for publishing reports. Reports can be built through BI Development Studio (BIDS) or Report Builder, and published to a web portal. The SSRS server itself has a web service API for programatically managing the server. Note that SSRS reports can consume data from a variety of sources, not just SQL Server. A command line tool called RS.EXE is supplied for programatically managing SSRS servers.
SQL Server Integration Services (SSIS): This is an ETL tool supplied with SQL Server. Architecturally, it is quite different to OWB or ODI in that it is not a code generation tool. The runtime sits client-side and can be on a separate machine to the database server. SSIS packages can be developed with BIDS and executed independently with a command line tool called DTEXEC.EXE.
B.I. Development Studio (BIDS): This is a visual studio based environnemt for developing reports, SSIS packages and SSAS cubes. If other VS based development tooling is installed (e.g. VS Professional) the tooling can be integrated into a single environment and a common project grouping.
Bulk copy (BCP): A command line bulk insert/extract tool similar to SQL*Loader
SQLCMD: A command line query tool similar to SQL*plus
SQL Profiler: A tracing and profiling tool that can capture and evaluate trace information from SQL Server, SSAS and other tools in the suite.
SQL Server Agent: A job scheduling utility that can run periodic jobs of one sort of another.
Best Answer
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.