Newbie differences between DB2 and Oracle

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.

1. Handling of dates (see post on this)
2. 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.
3. 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.
4. 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.
5. Oracle does not prefetch data into the buffer pools (buffer caches)
6. (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
7. Automatic memory tuning requires an additionally licensed product – the Diagnostics Pack
8. While configuration seems a bit more complicated, it is all in one location rather than the three that db2 uses (db, dbm, db2 registry)
9. LOBs can optionally be cached in the buffer cache
10. Rows can span blocks (don’t know the implications of that, but in DB2 a row must fit on a page)
11. While I think you can’t turn off logging as easily as db2 (load), you can choose not to log on say index creation
12. Network listeners are started separately from core database functionality, and one listener can do multiple instances
13. Db2 allows multiple databases per instance, where Oracle does not – the definition of an instance in Oracle is different from DB2
14. In Oracle, you must have certain users with the predefined names. SYS, SYSTEM, DB
15. 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)
16. Oracle has things you are not licensed for enabled by default
17. Oracle licensing is pretty complicated compared to DB2
18. You have to maintain the actual files for configuration and back them up and have copies of them for disaster recovery.
19. In Oracle, you can query the V$ views even if you don’t have a database connection
20. Start script should include:
• lsnrctl start
• connect / as sysdba
• startup
• emctl start dbconsole
21. Stop script should include:
• emctl stop dbconsole
• connect / as sysdba
• shutdown immediate
• lsnrctl stop
22. Databases are “cataloged” by an entry in tnsnames.ora
23. Use of port 1521 makes things easier for EM and also for several other things, so most don’t switch to other ports
24. 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)
25. Like the DB2 cca (client configuration assistant), Oracle has guis for cataloging – netmgr or netca, or you can manually edit tnsnames.ora
26. 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
27. 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).
28. A table can exist and take up absolutely no storage until there is some data in the table.
29. 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.
30. Reorgs don’t seem to be done with a “reorg” command
31. The segment advisor plays the same role as reorgchk
32. You can set a tablespace as read only.
33. Usernames can be up to 30 bytes long
34. A schema is more tightly tied to a user, and if you remove the user, all objects in their schema will be dropped.
35. An alias in DB2 is analogous to a synonym in Oracle.
36. In Oracle “connect” is a role that includes create session rather than a privilege itself
37. 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.
38. 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.
39. Revokes of permissions cascade (I granted to Sue, she granted to Mike. I revoke from Sue and Mike loses his permissions too.)
40. There are no groups in Oralce – use Roles instead
41. Like DB2, there is a “Remote” authentication that should be disabled for security reasons.
42. Autocommit is a feature of sqlplus that you can turn on, and should not be assumed in all tools.
43. DDL generates an implicit commit in all cases
44. LOAD in DB2 is like Insert Append in Oracle
45. FORCE APPLICATION in DB2 is like ALTER SYSTEM KILL SESSION in Oracle
46. 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).
47. 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.
48. DB2 LSN = Oracle SCN (system change #), while Oracle LSN is like the log file name in DB2
49. runstats on DB2 = dbms_stats (pl/sql) on Oracle
50. CARD on DB2 = NUM_ROWS on Oracle
51. Oracle supports NFS for storage (last I checked db2 did not)
52. There are a number of pseudo columns in oracle, including rowid (unique), and rownum (assigned at fetch time).
53. Offline reorg consists of:
• Export data
• truncate or drop table
• create table
• import data
• create indexes
54. Online reorg consists of one of three methods:
1. Alter table move
• alter table move (assigns new row ids and invalidates indexes)
• rebuild indexes
2. Shrink table
• 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)
3. DBMS_redefinition (PL/SQL)
55. 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
56. With supplemental logging, you can have human-readable redo logs with a number of different options
57. 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
58. There are no prefetchers in Oracle
59. 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
60. 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)
61. 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.
62. export and import do not work as SYS user.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home