Oracle

Edition, Version, and Features That Are On By Default

Version matters less than people expect for application work and more than expected for support. 18c and 19c are patch sets of the 12.2 family, 12.2.0.2 and 12.2.0.3, which is why moving 12.2 to 19c is usually undramatic while moving 11.2 to 19c is a different exercise. 21c was an innovation release with a short window; 23ai is the current long term release, and the current error correction date for 19c sits in Oracle's Lifetime Support Policy document, which has been revised more than once. One part of an upgrade is not reversible in place: raising the COMPATIBLE parameter enables on-disk format changes, and lowering it again means restoring a backup.

Edition and options are two different things, and only one of them is visible in the software. Standard Edition 2 and Enterprise Edition are separate products, capped differently in hardware and missing whole features, and no setting turns those features on. Options and management packs behave the opposite way. They ship inside the Enterprise Edition binaries and are enabled by default, so partitioning a table, running a SQL Tuning Advisor task, or pulling an AWR report all work regardless of what was purchased. DBA_FEATURE_USAGE_STATISTICS records that they worked.

• Standard Edition 2 is licensed on servers of at most two sockets, and the database uses a maximum of 16 CPU threads no matter how many the machine has. Past that point, a bigger box does not make an SE2 database faster.
• Not present in SE2 at all: partitioning, parallel query and parallel DML, bitmap indexes, materialized view query rewrite, Flashback Database, online index rebuild, and, since 19c, RAC. A data model that depends on partitioning is an Enterprise Edition data model, and that is a licensing decision made at design time rather than a tuning decision made later.
• Separately licensed Enterprise Edition options include Partitioning, Advanced Compression, Advanced Security and its transparent data encryption, Database In-Memory, Active Data Guard, Real Application Testing, and Multitenant beyond the pluggable databases the base license already covers.
• Diagnostics Pack covers AWR, ADDM, active session history and the DBA_HIST_* views. Tuning Pack covers SQL Tuning Advisor, SQL Access Advisor, and Real-Time SQL Monitoring. A single SELECT against V$ACTIVE_SESSION_HISTORY is a Diagnostics Pack use, which is why pack questions come up during troubleshooting rather than during procurement.
• CONTROL_MANAGEMENT_PACK_ACCESS governs all of it, with values NONE, DIAGNOSTIC, and DIAGNOSTIC+TUNING. The default is DIAGNOSTIC+TUNING, so the packs are live unless somebody set the parameter. Statspack is the pack-free alternative and still ships with the database.
• Autonomous Database and the Enterprise Edition cloud tiers include the management packs in the service. A diagnostic routine that is entirely normal there is not automatically appropriate on the on-premises instance it was copied from.

Getting the Plan That Actually Ran

The plan that matters is the one in the cursor cache, not the one EXPLAIN PLAN prints. EXPLAIN PLAN, and the plan button in most tools, re-optimize the statement without peeking at real bind values, so they can show a plan the database never chose. Find the SQL_ID in V$SQL, then run DBMS_XPLAN.DISPLAY_CURSOR against it. When you can execute the statement yourself, add the GATHER_PLAN_STATISTICS hint and the ALLSTATS LAST format, which prints estimated and actual rows for every row source. The first line where those two diverge by an order of magnitude is usually where the problem starts, and everything below it is consequence.

Separate the questions before choosing a tool. One statement being slow is answered per statement, from the plan and the row source timings. The instance being slow is answered from the instance-wide wait profile and usually names something the statement had no control over. There is a third case neither tool shows: the database reports forty milliseconds for a statement the user experiences as four seconds, and the gap is round trips and fetch size on the client. Comparing the client's own timing against the database's own timing is what tells the three apart.

• V$SESSION.EVENT and STATE say what a session is waiting on right now. Active session history, sampled once per second into V$ACTIVE_SESSION_HISTORY, says what it was waiting on ten minutes ago, which is the question you actually have once somebody complains. It is Diagnostics Pack.
• Statspack installs from rdbms/admin/spcreate.sql and needs no pack license. It takes instance-level snapshots and reports the delta between two of them. It has no per-session history, so it can tell you the database was busy and cannot tell you which session waited on what.
• Real-Time SQL Monitoring, through DBMS_SQLTUNE.REPORT_SQL_MONITOR, captures statements automatically once they pass a few seconds of CPU or IO or go parallel, and shows elapsed time per plan line while the statement is still running. It requires the Tuning Pack.
• "It got slow overnight and nothing changed" is usually a plan change. Compare PLAN_HASH_VALUE for the SQL_ID over time. More than one child cursor for the same statement means the database decided the cursors could not be shared, and V$SQL_SHARED_CURSOR has a column set to Y giving the reason.
• ORA-01555, snapshot too old, is a query duration problem rather than a connection or memory problem. A long-running query needs a consistent read of blocks that concurrent DML has since changed, and the undo holding the older version has been overwritten. The levers are undo retention and the size of the undo tablespace, or making the query finish sooner; rerunning it unchanged on a busier system just fails earlier.
• Gathering statistics invalidates cursors and can change plans, which makes it a fix and a risk in the same action. DBMS_STATS.SET_TABLE_PREFS with PUBLISH set to FALSE lets you gather into pending statistics, test them in one session with OPTIMIZER_USE_PENDING_STATISTICS, and publish only after the plans are known.

Drivers, Character Sets, and Types That Do Not Map Cleanly

Most current languages offer both a thin driver that speaks the Oracle network protocol directly and a thick mode that loads Oracle Instant Client: JDBC thin, managed ODP.NET, python-oracledb, node-oracledb. Thin is the default worth starting from. There is no native library in the container image, no Instant Client version to keep matched to anything, and the driver performs its own character set conversion rather than depending on NLS_LANG being set correctly in the environment. Thick mode is for the features thin does not implement.

What thin leaves out shrinks with each release, so the driver's own feature matrix is the authority rather than anything written a year ago. The recurring reasons to fall back to thick mode are Advanced Queuing, continuous query notification, and external authentication such as Kerberos. Deciding this early is worth the fifteen minutes, because the decision shows up in the base image, the build, and the deployment, not in application code.

• Oracle stores an empty string in a VARCHAR2 as NULL. A driver that binds an empty string writes NULL, the row reads back NULL, and a NOT NULL constraint rejects a value the application thought it had supplied.
• Check NLS_CHARACTERSET before assuming column widths. Under AL32UTF8 a character can occupy up to four bytes, and VARCHAR2(30) means 30 bytes unless the column was declared VARCHAR2(30 CHAR) or NLS_LENGTH_SEMANTICS is CHAR. A 30-character name that was fine on a single-byte database fails with ORA-12899 after the character set changes.
• DATE carries a time to the second, with no fractional seconds and no time zone. TIMESTAMP WITH LOCAL TIME ZONE converts on the way in and out using the session time zone, so the same stored row reads back differently for two sessions in different zones, which is either exactly what you wanted or a bug that only appears in one office.
• NUMBER is decimal, not binary floating point. Mapping it to a double in the client loses precision on money. Java maps it to BigDecimal correctly and then the JSON serializer turns it into a float, which is where the rounding usually enters.
• Every fetch is a round trip. JDBC defaults to a fetch size of 10 rows and python-oracledb to an array size of 100, so ten thousand rows across a link with thirty milliseconds of latency is dominated by round trips, not by the query. The database's own timing for that statement looks fine, because it is fine.
• The connection errors distinguish themselves. ORA-12154 means the client could not resolve the connect identifier, so it is a tnsnames or EZConnect problem before the network is involved. ORA-12541 means nothing is listening on the port. ORA-12514 means the listener answered but does not know that service name. ORA-12170 is a timeout, which is usually a firewall dropping the connection attempt rather than a database that is down.

Connection Pools, Process Limits, and Sessions That Vanish

Oracle's default dedicated server model gives every session its own operating system process, and PROCESSES caps how many exist. When the instance runs out, ORA-00020 is raised for whoever asks next, which is frequently not the application that consumed them. That makes pool sizing an instance-wide concern rather than a per-application one, and it is worth writing the arithmetic down somewhere both sides can see it.

Sessions also disappear quietly. A pooled connection sits idle longer than a firewall's idle timeout, the firewall drops the state without telling either end, and the next borrow from the pool fails with ORA-03113, end-of-file on communication channel, or ORA-03135, connection lost contact. The database never noticed. The client-side fix is a validation query or the driver's built-in ping on borrow; the server-side fix is SQLNET.EXPIRE_TIME so the instance detects dead clients and releases their processes instead of holding them against the limit.

• Add up maximums, not averages. Every pool's maximum size, plus batch jobs, plus reporting tools and ad hoc sessions, plus background processes, has to fit under PROCESSES. Pools configured to grow under load all grow during the same five minutes.
• A pool larger than the database's CPU count usually lowers throughput. Queuing at the pool is visible and bounded; the same queuing pushed into the database appears as latch and buffer contention, which looks like an entirely different problem.
• Whatever does connect, the API tier, batch jobs, reporting tools, should connect under its own credentials, and the driver should set MODULE and ACTION through DBMS_APPLICATION_INFO or its own client-info calls. V$SESSION and the session history then say which caller a session belongs to. Thirty identical sessions under one shared username on one host are unattributable at three in the morning, when exactly one of them is the problem.
• A resource profile with IDLE_TIME snipes idle sessions. The session sits in V$SESSION marked SNIPED and the client eventually receives ORA-02396. A pool that holds connections open through a quiet night meets this every morning.
• Connect on a named application service rather than the SID or the default service. Resource Manager, tracing through DBMS_MONITOR, and later relocation of that workload all key off the service name, and none of the connect strings have to change when the database moves.
• Database Resident Connection Pooling is the answer when many short-lived client processes each want a connection, such as serverless functions or a per-request process model. DBMS_CONNECTION_POOL.START_POOL plus SERVER=POOLED in the connect descriptor puts the pool inside the database rather than one pool per client process.

Semantics That Change When the Data Moves

Application logic is the visible half of a migration. Underneath it sits a set of behaviors that change silently, because the new database accepts the same statement and returns a different answer. Nothing raises an error, so a test suite written against the old database passes unless the test data happens to contain the edge case.

The way to find these before users do is a dual run: point both databases at the same inputs, run the same workload, and compare outputs row by row instead of confirming that both finished. Differences cluster around NULLs, dates, numeric rounding, and sort order, which is also a reasonable order to check them in.

• Oracle stores an empty string as NULL; Postgres does not. After a migration a column can hold both, so IS NULL checks that used to be sufficient now miss rows, and a NOT NULL constraint stops catching the case it was added for.
• An Oracle DATE has a time component. Mapping it to a Postgres or SQL Server DATE truncates that silently, and it surfaces later as a BETWEEN filter that drops the last day or a sort that no longer breaks ties.
• Oracle's default sort is binary, so upper case sorts ahead of lower case. Postgres sorts by the database's collation, which in most locales does not. Every sorted screen changes, and any pagination that assumed a stable ORDER BY changes with it.
• DDL commits the open transaction in Oracle and is transactional in Postgres. Deployment and data-fix scripts written on the Oracle assumption behave differently, usually more safely, but anything that relied on an implicit commit partway through now succeeds or rolls back as one unit.
• Autonomous transactions, PRAGMA AUTONOMOUS_TRANSACTION, are typically used for logging that has to survive a rollback. There is no direct equivalent in Postgres, so each one becomes a separate connection or an out-of-band log, and the choice has to be made per use rather than once.
• Sequences with CACHE leave gaps after an instance restart, and NOORDER on RAC interleaves values across nodes. Code that treated the sequence as a gapless counter was already wrong; the migration is simply when someone finally reads the numbers.