So I’m obviously biased in favor of DB2, but here are a few things that have surprised me to learn are different in Oracle. Terminology differences are rampant. This list is a bit scattered as it’s mostly a brain dump, but maybe something here can help someone.
Oracle writes uncommited data to disk in the tablespaces. I think they use the table headers and the undo tablespace to manage this, but I’m still fuzzy on undo and redo logs.
Oracle does not stripe data across tablespace containers (data files). It uses various algorithms to select where data goes, trying to put partitions of tables in the same place or tables organized by an index in the same place. It does not re-balance if you add a new container. If you want to stripe data, you have to use a separate tool that Oracle supplies called ASM.
Oracle has only one buffer pool (buffer cache) per page size, except for the base page size for which there are two additional ones – the keep and the recycle to pin tables in memory or get them out as fast as possible.
Oracle does not prefetch data into the buffer pools (buffer caches)
(I have to research this one more) Class instructor indicated that DataGuard must be used with another tool (streams or Golden Gate) to get fully what HADR does
Automatic memory tuning requires an additionally licensed product – the Diagnostics Pack
While configuration seems a bit more complicated, it is all in one location rather than the three that db2 uses (db, dbm, db2 registry)
LOBs can optionally be cached in the buffer cache
Rows can span blocks (don’t know the implications of that, but in DB2 a row must fit on a page)
While I think you can’t turn off logging as easily as db2 (load), you can choose not to log on say index creation
Network listeners are started separately from core database functionality, and one listener can do multiple instances
Db2 allows multiple databases per instance, where Oracle does not – the definition of an instance in Oracle is different from DB2
In Oracle, you must have certain users with the predefined names. SYS, SYSTEM, DB
PUBLIC is considered a USER in Oracle from which other users inherit permissions (in DB2 it is a group that includes everyone who can log in to the server)
Oracle has things you are not licensed for enabled by default
Oracle licensing is pretty complicated compared to DB2
You have to maintain the actual files for configuration and back them up and have copies of them for disaster recovery.
In Oracle, you can query the V$ views even if you don’t have a database connection
Start script should include:
connect / as sysdba
emctl start dbconsole
Stop script should include:
emctl stop dbconsole
connect / as sysdba
Databases are “cataloged” by an entry in tnsnames.ora
Use of port 1521 makes things easier for EM and also for several other things, so most don’t switch to other ports
JDBC urls still work, of course – specifying the host, port, user, pw, and service (service is a name you choose to identify the incoming connection on the server)
Like the DB2 cca (client configuration assistant), Oracle has guis for cataloging – netmgr or netca, or you can manually edit tnsnames.ora
Oracle has two main modes for connections – direct and shared. Shared is more like the way DB2 does it with a pool of server processes similar to the agent pool in DB2
In storage, there is a level between Extent and tablespace, called the Segment, which seems to map one to one to objects (tables, indexes, etc).
A table can exist and take up absolutely no storage until there is some data in the table.
Extents can be set at the Tablespace level, the object level, or Oracle can automatically control them. The same object can have different size extents. Last I checked, DB2 sets extent size at the tablespace level, and you can’t have different size extents for the same tablespace, much less the same object.
Reorgs don’t seem to be done with a “reorg” command
The segment advisor plays the same role as reorgchk
You can set a tablespace as read only.
Usernames can be up to 30 bytes long
A schema is more tightly tied to a user, and if you remove the user, all objects in their schema will be dropped.
An alias in DB2 is analogous to a synonym in Oracle.
In Oracle “connect” is a role that includes create session rather than a privilege itself
If you try to query a table you don’t have privileges on in Oracle, you get a “table does not exist” error rather than one referencing privileges.
Authentication can happen in the database, at the OS level, via LDAP, or for a few select IDs via a password file (to cover authentication when the database is down). DB2 is almost always at the OS level or LDAP – though I wonder what the DB2 9.7 Oracle compatibility mode does with users.
Revokes of permissions cascade (I granted to Sue, she granted to Mike. I revoke from Sue and Mike loses his permissions too.)
There are no groups in Oralce – use Roles instead
Like DB2, there is a “Remote” authentication that should be disabled for security reasons.
Autocommit is a feature of sqlplus that you can turn on, and should not be assumed in all tools.
DDL generates an implicit commit in all cases
LOAD in DB2 is like Insert Append in Oracle
FORCE APPLICATION in DB2 is like ALTER SYSTEM KILL SESSION in Oracle
Locking is very different between DB2 and Oracle. In Oracle, there is no such thing as an uncommitted read. Default isolation levels make it so that reads are as of the time of the read, and never block writers. In Oracle, there IS NO LOCKTIMEOUT. That means that for “blocking sessions” you would have to configure the resource manager(like query patroller or workload manager in DB2).
The functionality provided by DB2 Transaction Logs is provided by two things in Oracle – Undo tablespace and redo logs. Undo handles guaranteeing read consistency, rollbacks, and flashback table. Redo handles roll-forward on restore only. Default retention for Undo is 15 minutes.
DB2 LSN = Oracle SCN (system change #), while Oracle LSN is like the log file name in DB2
runstats on DB2 = dbms_stats (pl/sql) on Oracle
CARD on DB2 = NUM_ROWS on Oracle
Oracle supports NFS for storage (last I checked db2 did not)
There are a number of pseudo columns in oracle, including rowid (unique), and rownum (assigned at fetch time).
Offline reorg consists of:
truncate or drop table
Online reorg consists of one of three methods:
Alter table move
alter table move (assigns new row ids and invalidates indexes)
shrink table (logical deletes and inserts – only fixes row migration if you have open space earlier in the table)
Automatically maintains indexes
Option to lower HWM
Requires ASSM (for-charge with the diagnostics pack)
Indexes can be an invalid, causing errors on inserts and table scans due to no indexes on select – you must run a command to rebuild indexes, and it’s not a bad idea to have a script running that looks for invalid indexes and rebuilds them
With supplemental logging, you can have human-readable redo logs with a number of different options
Flashback is a nifty feature, but there are many kinds – this is one of Oracle’s new favorite words
Flashback table – like rolling back a single table to a given point in time. Only works with row_movement on and with enough space in the undo tablespace
Flashback query – query tables as of a particular time
Flashback versions – view all changed during a specific time
Flashback transaction – view updates associated with a transaction
There are no prefetchers in Oracle
when archive logging is enabled, logs are not moved, but are rather copied during a log file switch. Destination is defined by db_recovery_file_dest
Backups have different options:
User Managed vs. Server Managed (RMAN)
Logical (exports) vs. Physical
Full or Incremental level 0 (full cannot be used as a basis for incrementals, so an incremental level 0 may be the best choice for future flexibility)
Incremental level 1 cumulative (like a DB2 incremental) or Incremental level 1 differential (like a DB2 delta)
Whole (all data files plus 1 ctrl file) or Partial
Image (bit for bit, cannot compress or do incremental) or set (allocated blocks)
Offline (much like DB2’s Offline) or Online (much like DB2’s Online)
Oracle does not have a quick, easy export to csv or fixed length file, only to proprietary files. Use of spool and concatenating with commas is about as good as it gets in that area.
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science.
Ember lives in Denver and work from home