DEV Community: Uptime Architect

Oracle Privilege Analysis: You Granted DBA. Here's What They Actually Used.

Uptime Architect — Sat, 19 Sep 2026 12:45:34 +0000

Someone needed to read one table. The ticket said "just give the app the standard role so we can ship," the role already carried half a dozen ANY privileges, and it got granted. Three years later that account can DROP ANY TABLE in production and nobody alive remembers whether it needs to. Multiply by every service account, every "temporary" grant that became permanent, every role that grew by accretion, and you get the database most shops actually run: a pile of privilege nobody can safely revoke, because nobody can prove what's in use.

You don't have to guess. Privilege Analysis — the DBMS_PRIVILEGE_CAPTURE package, built into Enterprise Edition — records the privileges a session actually exercises over a window of real activity. Afterward you compare used against granted, and the difference is your revoke list: privileges held but never exercised, removable with evidence instead of a nervous guess. This post is what a capture is, how to run one, the one finding it produces almost every time (SELECT ANY TABLE, unused), and a lab that grants a user a dozen privileges, watches it use a handful, and prints the rest.

What Privilege Analysis actually records

A privilege capture watches sessions and notes every privilege that was needed to authorize an operation — the CREATE TABLE behind a CREATE TABLE statement, the SELECT object grant behind a query, the system privilege behind a DDL. It does not record what was granted; the data dictionary already knows that. It records what was exercised. Run a representative workload — a month-end, a full release cycle, a busy week — and you have an evidence-based map of the privileges an account can't do its job without.

Then you subtract. Everything granted but not captured is, by definition, unused over that window. That set is the least-privilege candidate list: revoke it and the account keeps working, minus the blast radius you were carrying for no reason. The whole discipline is "grant what's used, revoke what isn't" — and Privilege Analysis is what turns that from a slogan into a query.

How a capture works

Four steps, all through DBMS_PRIVILEGE_CAPTURE:

-- 1. define a capture. G_CONTEXT scopes it to just the sessions you care about.
BEGIN
  DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
    name        => 'APPUSER_CAP',
    description => 'What APPUSER actually uses vs what it was granted',
    type        => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
    condition   => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''APPUSER''');

-- 2. turn it on — BEFORE the workload runs
  DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(name => 'APPUSER_CAP');
END;
/

-- 3. ... the application runs its real workload as APPUSER ...

-- 4. stop capturing and compute the result
BEGIN
  DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(name => 'APPUSER_CAP');
  DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT(name => 'APPUSER_CAP');
END;
/

The step people miss is GENERATE_RESULT: until you call it, the DBA_USED_PRIVS / DBA_UNUSED_PRIVS views (and their *_SYSPRIVS / *_OBJPRIVS siblings) are empty. Enable, run the workload, disable, generate — then read the views. And there are four capture types for scoping:

G_DATABASE — every privilege use in the whole database. The broad sweep; only one can run at a time.
G_ROLE — only privileges that come from a named set of roles. Perfect for "is anyone actually using what DBA grants?"
G_CONTEXT — a SQL condition you supply, evaluated per session (the lab keys off SESSION_USER so it captures exactly one account).
G_ROLE_AND_CONTEXT — both, ANDed together.

A privilege capture, end to end. You enable it, the account runs a real workload, you disable and generate the result — and every granted privilege sorts into USED (keep it) or UNUSED (the revoke list). The value is the second pile: privileges held but never exercised, safe to remove because the evidence says so.

The finding you'll get almost every time: `SELECT ANY TABLE`, unused

There's one result Privilege Analysis surfaces so reliably it's worth calling out. When an account holds both a specific object grant (SELECT on one table) and the system privilege SELECT ANY TABLE, and it queries that table, Oracle authorizes the read with the object grant. The ANY privilege is never touched — so it lands in DBA_UNUSED_PRIVS.

That is the single most common over-grant in the wild: someone was handed the god-mode "read every table in the database" privilege when all they ever read was the two tables they were explicitly granted. Privilege Analysis catches it in black and white, which is exactly the ammunition you need to revoke it over the inevitable "but what if something breaks" objection. Nothing was using it. The evidence says so.

Now prove it

Grant a user a role that carries twelve privileges — ten system privileges including SELECT ANY TABLE, DROP ANY TABLE, CREATE ANY TABLE, and ALTER ANY TABLE, plus SELECT on two tables. Start a capture scoped to that user. Then have it do three ordinary things: log in, read one table it has an explicit grant on, and create one table in its own schema. Disable, generate, and read the result:

	Privileges
USED	`CREATE SESSION`, `CREATE TABLE`, `SELECT` on `PAOWN.CUSTOMERS`
UNUSED	`SELECT ANY TABLE`, `DROP ANY TABLE`, `CREATE ANY TABLE`, `ALTER ANY TABLE`, `CREATE VIEW`, `CREATE PROCEDURE`, `CREATE SEQUENCE`, `CREATE SYNONYM`, `SELECT` on `PAOWN.ORDERS`

Three privileges did the work. Nine sat unused — every ANY privilege among them. SELECT ANY TABLE is unused because the one table the account read, it was explicitly granted; the read never needed the god-mode version. DROP ANY TABLE and friends are unused because the account never dropped or altered anything outside its own schema. SELECT on PAOWN.ORDERS is unused because the workload simply never touched that table. The least-privilege regrant writes itself: keep the three, revoke the nine.

Don't take my word for it — run it. The Privilege Analysis lab stands up an Oracle Database Free container, grants APPUSER the 12-privilege role, enables a DBMS_PRIVILEGE_CAPTURE run scoped to it, executes the small workload, then generates the result and asserts the split: CREATE SESSION / CREATE TABLE / SELECT on CUSTOMERS come back used; SELECT ANY TABLE, DROP ANY TABLE, CREATE ANY TABLE, and the rest come back unused; and there are more unused than used. If the capture stops telling them apart, the run fails. It's proven on every CI push.

What teams get wrong

Guessing the revoke list instead of measuring it. The reason over-grants never get cleaned up is fear: nobody will revoke a privilege they can't prove is unused. Privilege Analysis removes the fear — you revoke from a captured-usage report, not a hunch. Measure first, then cut.
Too short a capture window. If you capture for an afternoon, month-end batch jobs, quarterly reports, and the annual archive purge never run — and their privileges look "unused" right up until you revoke them and break the quarter close. Capture across a representative period, and think about the rare-but-real jobs before you cut.
Forgetting GENERATE_RESULT. Reading DBA_UNUSED_PRIVS and seeing nothing, then concluding the account used everything. The views are empty until you generate the result for the capture. Disable → generate → read.
Revoking the privilege but leaving the role. If the unused privilege comes through a role, revoking it from the account does nothing — it's still in the role. Privilege Analysis tells you how each privilege was granted (directly, or via which role); fix it at the grant path the report names, and remember the role is shared, so trimming it affects everyone who holds it.
Treating one capture as forever. Usage changes: a new feature ships, a new report is added, and yesterday's "unused" becomes today's needed. Privilege Analysis is a periodic hygiene practice, not a one-time audit — recapture after significant change, and before you trust a stale result.
Confusing it with auditing. Privilege Analysis tells you which privileges could be revoked; it is not a record of who did what — that's unified auditing. Use analysis to shrink the grants, auditing to watch how the remaining ones get used. Different jobs.

Frequently asked questions

What is Oracle Privilege Analysis?

Privilege Analysis is an Oracle Database feature, driven by the DBMS_PRIVILEGE_CAPTURE package, that records the privileges a database session actually exercises over a period of activity. Instead of listing what an account was granted (which the data dictionary already shows), it captures what was used to authorize real operations, so you can compare used privileges against granted ones and identify the granted-but-never-used privileges that are candidates for revocation. It is the practical mechanism for enforcing least privilege on an existing database: rather than guessing which grants are safe to remove, you revoke based on captured evidence of what each account needs.

How do I run a privilege capture with DBMS_PRIVILEGE_CAPTURE?

Four steps. First, CREATE_CAPTURE to define the capture, giving it a name, a type (G_DATABASE, G_ROLE, G_CONTEXT, or G_ROLE_AND_CONTEXT), and for a context capture a SQL condition that scopes which sessions are recorded. Second, ENABLE_CAPTURE to start recording — this must happen before the workload you want to measure runs. Third, let a representative workload run for as long as it takes to exercise the account's real activity. Fourth, DISABLE_CAPTURE to stop and then GENERATE_RESULT to compute the outcome, which populates the reporting views. Only after GENERATE_RESULT do DBA_USED_PRIVS and DBA_UNUSED_PRIVS contain data for that capture.

What are the four privilege capture types?

G_DATABASE captures every privilege use across the whole database and is the broad sweep; only one database-wide capture can be enabled at a time. G_ROLE captures only privilege uses that derive from a specified set of roles, which is ideal for questions like whether anyone actually exercises what a powerful role grants. G_CONTEXT captures based on a SQL condition you supply that is evaluated per session, for example limiting the capture to a single application user or to connections from a particular program or IP. G_ROLE_AND_CONTEXT combines the role and context conditions so both must hold. Context and role captures can run alongside the always-present ORA$DEPENDENCY capture.

Why does SELECT ANY TABLE show up as an unused privilege?

When a session holds both a specific object privilege, such as SELECT on one table, and the system privilege SELECT ANY TABLE, and it queries that table, Oracle authorizes the operation using the object privilege. The system-wide SELECT ANY TABLE is not needed for that access, so Privilege Analysis records it as unused. In practice this is the most common over-grant it surfaces: accounts are frequently given SELECT ANY TABLE when they only ever read a small set of tables they were explicitly granted, so the powerful any-table privilege sits unexercised and is safe to revoke. The same pattern applies to other ANY privileges when narrower grants already cover the actual work.

Which views show the results of a privilege capture?

After GENERATE_RESULT, the used privileges appear in DBA_USED_PRIVS with system-privilege and object-privilege detail also split into DBA_USED_SYSPRIVS and DBA_USED_OBJPRIVS, and the granted-but-unused privileges appear in DBA_UNUSED_PRIVS with the corresponding DBA_UNUSED_SYSPRIVS and DBA_UNUSED_OBJPRIVS. There are also path views such as DBA_USED_PRIVS showing how a privilege was granted (directly or through which role) and role-usage views. The capture definitions themselves are listed in DBA_PRIV_CAPTURES. You typically filter these views by the capture name, and, since a context capture already scopes to specific sessions, the rows correspond to the account or accounts the capture targeted.

Does Privilege Analysis capture privileges used through roles?

Yes. Privilege Analysis records a privilege as used regardless of whether the account holds it directly or receives it through a role (including nested roles), and the reporting views show the grant path so you can see how each used or unused privilege reached the account. This matters for remediation: if an unused privilege comes through a role, revoking it directly from the user does nothing because the role still carries it, so you have to change the role, and because roles are shared, trimming a role affects every account that holds it. The report gives you the information to decide whether to adjust the role, replace it with a narrower one, or grant the small used set directly.

Does Privilege Analysis require the Database Vault option or a separate license?

No longer. When Privilege Analysis was introduced in Oracle Database 12c it was part of the Oracle Database Vault option, but since Oracle Database 18c it is a feature of Enterprise Edition and does not require Database Vault or a separate license. It is also available in Oracle Database Free, which is built on the Enterprise Edition feature set, so you can run captures for development and testing at no cost, which is what makes the companion lab reproducible with nothing but Docker. As always, confirm the entitlement for your specific edition, version, and platform before relying on it in production.

What are the limits of Privilege Analysis I should plan around?

The biggest one is coverage: a capture only knows about activity that happened while it was enabled, so a window that misses periodic work — month-end, quarter close, annual purges, rarely used admin paths — will report privileges as unused that are actually needed on a cadence you didn't observe. Capture across a representative period and account for rare jobs before revoking. Beyond that, treat results as a point-in-time snapshot that goes stale as the application changes, so recapture periodically; remediate at the correct grant path (role versus direct) rather than assuming a direct revoke works; and remember that Privilege Analysis identifies removable privileges but is not an audit trail of who did what, which is a separate concern handled by auditing.

Privilege Analysis is the layer of database security most shops skip because it feels impossible to do safely — so the grants only ever grow. It is the natural companion to the rest of the discipline: the hardening checklist sets the baseline for who can connect and how, VPD decides which rows each session sees, redaction narrows which values reach a screen, TDE protects the files, and unified auditing records who looked. Privilege Analysis works on the grants underneath all of them — shrinking what every account can do down to what it demonstrably needs to. Capture a representative window, generate the result, and revoke the unused pile with the evidence in hand. Then prove it the way that ends the argument: with the Privilege Analysis lab, where an account granted a dozen privileges is shown using just three.

Originally published at uptimearchitect.com.

Oracle Virtual Private Database: Row-Level Security You Can't Route Around

Uptime Architect — Wed, 16 Sep 2026 12:27:58 +0000

You wrote a view that filters each sales rep to their own region, granted the view, revoked the table, and called it row-level security. It worked — until the reporting team needed the base table, or a data export ran as a privileged account, or someone wrote an ad-hoc query straight against ORDERS. The moment anyone touches the table instead of the view, the filter is gone. A view is a detour, and a detour only works if everyone agrees to take it.

Virtual Private Database (VPD, also called Fine-Grained Access Control, implemented through the DBMS_RLS package) does it the other way around: it attaches the rule to the table. You write a policy function that returns a WHERE predicate, and Oracle transparently appends that predicate to every query against the table, per session — no view, no application change, and nothing to route around. Query the base table directly and the predicate is still there. This is the row-level counterpart to Data Redaction: redaction masks a column's value and leaves the row; VPD removes the row entirely.

This post is what VPD actually is, how the policy function and the exempt privilege work, where it applies (and where people forget it doesn't), and — the part that ends the argument — a lab where three users query the same table and each one provably sees only the rows they're allowed to.

What VPD is — a predicate the database adds for you

A VPD policy has two pieces: a policy function and the DBMS_RLS.ADD_POLICY call that binds it to a table. The function takes the schema and object name and returns a string — a SQL predicate. When a session queries the protected table, Oracle calls the function, takes whatever predicate it returns, and rewrites the query to include it. A SELECT * FROM orders issued by a sales rep silently becomes SELECT * FROM orders WHERE (region = 'EAST').

The crucial word is transparently. The application doesn't know, the SQL text doesn't change, and the predicate is enforced on the base table — so it applies to SELECT COUNT(*), to SUM(amount), to a join, to a WHERE id = 42 lookup, to an export, to everything. There is no version of the query that sees the unfiltered table (except for the exempt sessions below). That's the whole difference from a view: a view is a separate object you can choose not to use; a VPD predicate is part of every statement against the table itself.

Here's the function — it keys off the session user and returns each rep's region:

CREATE OR REPLACE FUNCTION vpdown.orders_rls(p_schema IN VARCHAR2, p_object IN VARCHAR2)
  RETURN VARCHAR2 IS
BEGIN
  RETURN CASE SYS_CONTEXT('USERENV','SESSION_USER')
           WHEN 'REP_EAST' THEN 'region = ''EAST'''
           WHEN 'REP_WEST' THEN 'region = ''WEST'''
           ELSE '1=2'                       -- deny by default: unknown users see no rows
         END;
END;
/

And the policy that binds it to the table, for SELECT:

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'VPDOWN',
    object_name     => 'ORDERS',
    policy_name     => 'ORDERS_REGION_POLICY',
    function_schema => 'VPDOWN',
    policy_function => 'ORDERS_RLS',
    statement_types => 'SELECT');
END;
/

That's it. No grants to juggle, no view to maintain, no trigger. From now on every SELECT against VPDOWN.ORDERS carries the predicate the function hands back for that session.

Key the predicate off something the user can't forge

The policy function above uses SYS_CONTEXT('USERENV','SESSION_USER') — the database account the session logged in as, which a user cannot spoof. That's the safe kind of input. The same rule applies here as in the redaction post: base the decision on a session fact the user can't set themselves.

In the real world, app servers often connect through one pooled database account and identify the end user separately. There the standard pattern is an application context: a logon trigger (or the connection pool) sets a context attribute like app_ctx.region for the session, and the policy function reads SYS_CONTEXT('APP_CTX','REGION'). Done right, the context is set by a trusted package the user can't call directly, so it's just as unforgeable as SESSION_USER. Done wrong — keying off a client identifier the application sets for convenience — it's a lock anyone can open by setting the same value. Same trap, same fix.

Who bypasses the policy

Two kinds of session are not subject to VPD:

SYS (and SYSDBA connections) are always exempt — they see the raw table.
Any user holding the EXEMPT ACCESS POLICY system privilege bypasses every VPD policy in the database, regardless of any function. This is how you build a "sees everything" role — a manager, a reporting account — and it is the exact analog of EXEMPT REDACTION POLICY. It is also a master key: grant it to as few accounts as possible and audit who holds it, because one such account defeats every row policy you've written.

-- MGR sees every row, in every region, on every VPD-protected table:
GRANT EXEMPT ACCESS POLICY TO mgr;

Where the row filter is applied — and where it isn't. For a subject session Oracle runs the policy function, gets a predicate, and appends it to the query against the base table, so only the allowed rows come back. An exempt session (EXEMPT ACCESS POLICY, or SYS) skips the function entirely and sees the whole table. There is no view in the middle to route around.

Now prove it

Put a policy like the one above on an ORDERS table of known shape — 3,000 rows, deterministically split 1,800 EAST / 1,200 WEST — then read it back as three users: REP_EAST, REP_WEST, and a MGR who holds EXEMPT ACCESS POLICY:

-- each of these is the SAME statement; only the connected user differs
SELECT COUNT(*), SUM(amount) FROM vpdown.orders;

user	rows returned	regions seen	`SUM(amount)`	`WHERE id = 3` (a WEST row)
`REP_EAST`	1,800	EAST only	180,000	0 rows
`REP_WEST`	1,200	WEST only	120,000	1 row
`MGR` (exempt)	3,000	EAST + WEST	300,000	1 row

Read that table slowly, because every column is a separate proof. The row counts show each rep is confined to their region. The SUM(amount) shows the predicate is applied to aggregates too — REP_EAST can't even learn the company-wide total, only their own 180,000. And the WHERE id = 3 probe is the one that kills the "I'll just ask for the row directly" idea: id 3 is a WEST order, and when REP_EAST asks for it by primary key, Oracle appends AND region = 'EAST' and returns nothing. There is no query shape that widens the result. MGR, holding the exempt privilege, sees all 3,000 — the intended bypass, working as designed.

Don't take my word for it — run it. The VPD lab stands up an Oracle Database Free container, builds ORDERS (3,000 rows, 1,800 EAST / 1,200 WEST) behind a DBMS_RLS policy, and reads it back as REP_EAST, REP_WEST, and an EXEMPT MGR. It asserts each rep sees only its region on the row count, on SUM(amount), and on a targeted by-id lookup, and that the exempt manager sees all 3,000. If any reader ever sees a row it shouldn't — or the aggregates aren't filtered — the run fails. It's proven on every CI push.

Don't forget the other three verbs

The lab policy covers SELECT, which is where most people start. But row-level security that only filters reads has a hole: what stops REP_EAST from inserting a WEST order, or updating an EAST order to move it to WEST? By default, nothing. VPD policies apply to the statement types you name, and for writes you almost always want the full set:

DBMS_RLS.ADD_POLICY(
  object_schema   => 'VPDOWN', object_name => 'ORDERS',
  policy_name     => 'ORDERS_REGION_POLICY',
  function_schema => 'VPDOWN', policy_function => 'ORDERS_RLS',
  statement_types => 'SELECT,INSERT,UPDATE,DELETE',
  update_check    => TRUE);           -- reject writes that would land a row outside the session's slice

update_check => TRUE is the important flag: without it, the predicate filters which rows an UPDATE or DELETE can see, but an INSERT (or an UPDATE that changes region) could still write a row the session wouldn't be allowed to read back. With it on, Oracle re-checks the new row against the predicate and raises an error if it falls outside — so a rep can't stash a row in another region.

What teams get wrong

Using a view (or the application) as the security boundary. A view filters only if everyone goes through it; grant the base table for one report and it's over. Application-tier filtering is worse — every new client, script, or BI tool is a fresh way around it. VPD moves the rule into the table so there's one place, enforced for every path.
Filtering reads but not writes. A SELECT-only policy lets a user insert or move rows outside their slice. Add INSERT,UPDATE,DELETE with update_check => TRUE when the rows are meant to stay put.
Keying the predicate off something spoofable. A CLIENT_IDENTIFIER or context value the application sets for convenience is a lock the user can pick by setting the same value. Key off SESSION_USER, an enabled role, or an application context populated by a trusted package the user can't call.
Forgetting the exempt paths. EXEMPT ACCESS POLICY holders and SYS see everything; a full Data Pump export run by such an account carries every row; the object owner's own access can bypass the policy. Audit who is exempt and treat privileged exports as sensitive — the same discipline as EXEMPT REDACTION POLICY.
A heavy or non-deterministic policy function. The function runs as part of query parsing/execution, so a slow lookup or a function that returns different predicates unpredictably hurts performance and plan-sharing. Keep it cheap, and use the right policy type (STATIC, SHARED_STATIC, CONTEXT_SENSITIVE) so Oracle caches the predicate instead of re-running the function on every call.
Expecting VPD to hide a column. VPD filters rows. If you need to blank out a column's value for some users, that's Data Redaction (or VPD's column-sensitive mode with sec_relevant_cols, which applies the row predicate only when a sensitive column is selected). Match the tool to whether you're hiding a row or a value.
Reaching for VPD when you need labels or a full model. For classification-driven access (secret/confidential/public) or complex, centrally-managed policies across many tables, Oracle Label Security and Real Application Security exist and are built for that. VPD is the lightweight, write-your-own-predicate option — perfect for "each tenant sees their own rows," heavier machinery for more.

Frequently asked questions

What is Oracle Virtual Private Database (VPD)?

Virtual Private Database, also called Fine-Grained Access Control and implemented through the DBMS_RLS package, is an Oracle feature that enforces row-level (and optionally column-sensitive) security by attaching a policy to a table, view, or synonym. You write a policy function that returns a SQL predicate, and Oracle transparently appends that predicate to every query the session runs against the object, so each session sees only the rows the predicate allows. Because the rule lives on the base object rather than in a view or the application, it is enforced for every access path — ad-hoc SQL, reports, exports, and joins alike. It requires no changes to the application or the SQL text.

How is VPD different from using a view to hide rows?

A view is a separate object: it filters rows only for sessions that query the view, so the protection disappears the moment someone is granted and queries the underlying base table (a report, an export, an ad-hoc query). VPD attaches the predicate to the base table itself, so every statement against that table — through any path — carries the filter. There is nothing to route around. Views are fine for convenience and shaping data; VPD is the right tool when the row restriction is a security boundary that must hold no matter how the table is reached.

What is a VPD policy function and what must it return?

A VPD policy function is a PL/SQL function that takes two arguments — the schema and object name — and returns a VARCHAR2 containing a SQL predicate (the text that would go after WHERE), or an empty/NULL string to add no restriction. Oracle calls it for each session that queries the protected object and appends the returned predicate to the statement. The function typically bases the predicate on an unspoofable session fact such as SYS_CONTEXT('USERENV','SESSION_USER') or an application context set at logon. It should be lightweight and, ideally, deterministic for a given session so Oracle can cache the predicate using an appropriate policy type (STATIC, SHARED_STATIC, or CONTEXT_SENSITIVE).

Who is exempt from VPD policies?

The SYS user (and SYSDBA connections) are always exempt and see the unfiltered table. In addition, any user granted the EXEMPT ACCESS POLICY system privilege bypasses every VPD policy in the database, regardless of the policy functions. That privilege is how you build a role that sees all rows — for example a manager or a reporting account — and it is the exact analog of EXEMPT REDACTION POLICY for Data Redaction. Because a single exempt account defeats every row policy, grant it to as few users as possible and audit who holds it. Note also that a full Data Pump export run by a privileged or exempt account carries every row, so treat such exports as sensitive.

Does VPD apply to INSERT, UPDATE, and DELETE, or only SELECT?

VPD applies to whatever statement types you name in DBMS_RLS.ADD_POLICY. A SELECT-only policy filters reads but does nothing to stop a user inserting a row outside their slice or updating a row to move it there. For a complete boundary, add INSERT, UPDATE, and DELETE to statement_types, and set update_check => TRUE. The update_check flag makes Oracle re-evaluate the predicate against the new or changed row and reject the write if the row would fall outside what the session is allowed to see, so a user cannot write rows they could not read back. For INSERT and UPDATE you can also supply separate policy functions if the write rule differs from the read rule.

What is the difference between VPD, Data Redaction, and Oracle Label Security?

They protect different things. VPD (Virtual Private Database) filters ROWS: a per-session predicate decides which rows a query returns, and the row is simply absent for users who should not see it. Data Redaction masks COLUMN VALUES at read time: the row is still returned, but a protected column comes back masked, partial, or random — the data on disk is unchanged. Oracle Label Security (OLS) is a packaged, classification-driven form of row-level security built on VPD, where each row carries a sensitivity label and access is decided by comparing the label to the user's clearance, managed centrally rather than by hand-written predicates. A mature design can combine them: VPD or OLS to control which rows, redaction to mask sensitive columns within the rows a user can see, and TDE to encrypt the whole thing at rest.

Does VPD hurt query performance?

VPD adds a predicate to the query and calls the policy function, so the cost has two parts: running the function and executing the extra filter. The function overhead is controlled by the policy type — STATIC and SHARED_STATIC cache the predicate and run the function once, CONTEXT_SENSITIVE re-evaluates only when a relevant context changes, and DYNAMIC (the default) runs it on every parse/execute — so choosing the right type for how often the predicate actually changes matters. The predicate itself is just SQL: if it is selective and supported by an index (for example region = 'EAST' with an index on region), it can even reduce work; if it is written poorly it adds a filter like any other WHERE clause. Keep the function cheap and deterministic, index the columns the predicate uses, and pick the policy type that matches your predicate's volatility.

Is VPD available in Oracle Database Free and Standard Edition?

VPD is a feature of Oracle Database Enterprise Edition, and it is included with EE rather than being a separately licensed option (unlike Advanced Security, which covers TDE and Data Redaction). It is available in Oracle Database Free, which is built on the Enterprise Edition feature set, which is why you can build and run the companion lab on the zero-license Free image with nothing but Docker. It is not part of Standard Edition. As always, confirm your specific edition and entitlement before relying on it in production, since licensing depends on your platform and can change.

Row-level security is one layer of the same data-protection discipline as the rest: Data Redaction masks the values within the rows a user can see, TDE makes the files unreadable when they're stolen, unified auditing records who looked, and the hardening checklist and least-privilege grants decide who can connect at all. VPD decides which rows each session is even allowed to see — and, unlike a view, it decides it on the base table so there's no way around it. Put the rule where the data lives, key it off something the user can't forge, remember the write path, and then prove it the way that ends the argument: with the VPD lab, where three users query one table and each sees only what they should.

Originally published at uptimearchitect.com.

Oracle Data Pump, Done Right: A Migration You Can Prove

Uptime Architect — Sun, 13 Sep 2026 14:51:13 +0000

expdp full=y and hope is not a migration plan. It's how you find out at 3 a.m. that the dump ran out of space, or the import invented a half-populated schema, or the "quick refresh" locked the source for an hour because nobody set a consistency point.

Data Pump is the workhorse that moves Oracle data — schema refreshes, database migrations, the lift into the cloud, the copy into a test environment. Almost every Oracle shop runs expdp and impdp; far fewer run them deliberately, with the handful of options that turn "it seemed to work" into "the row counts match and I can prove it." This post is the architecture worth understanding, the flags that actually matter, the quoting trap that eats an afternoon, and — the part that ends the argument — a lab that exports a schema, drops it, imports it back, and asserts nothing was lost.

What Data Pump actually is

The first thing to internalize: Data Pump runs inside the database server, not on your client. When you launch expdp, the client just starts and monitors a job; the actual reading and writing is done by server processes, and the dump file is written to a path on the database server, referenced through a DIRECTORY object — never a client-side path. This is the big break from the legacy exp/imp tools (which streamed through the client and are deprecated for good reason): Data Pump is faster, parallelizable, restartable, and runs where the data is.

That architecture has direct consequences you plan around:

You need a DIRECTORY. CREATE DIRECTORY dp_dir AS '/path/on/the/server' and grant read/write on it. The dump lives there, not on your laptop.
It has modes. FULL=Y (whole database), SCHEMAS= (one or more schemas — the common one), TABLES=, and TABLESPACES=. Pick the narrowest mode that covers what you're moving.
It can skip the dump file entirely. With NETWORK_LINK=, impdp pulls straight from a source database over a database link — no dump file, no staging, no copying gigabytes twice. For a migration into a new database this is often the cleanest path.

Data Pump is server-side: expdp writes the dump to a DIRECTORY on the database host, and impdp reads it back — into the same schema (a round trip) or, with REMAP_SCHEMA, into a new one (the migration move). With NETWORK_LINK, impdp skips the dump file and pulls straight from the source over a database link.

The flags that actually matter

Most of Data Pump's power is in a dozen parameters. These are the ones that decide whether a migration is clean:

REMAP_SCHEMA=source:target — import the data into a different schema than it came from. This is the heart of most migrations and refreshes: export PROD_APP, import as TEST_APP. Its siblings REMAP_TABLESPACE and REMAP_DATAFILE do the same for storage when the target's layout differs.
PARALLEL=n — run the job with n worker processes. Essential for anything large, but with one catch people miss: give it multiple dump files with the %U wildcard (DUMPFILE=exp_%U.dmp) so the workers aren't all fighting over one file. PARALLEL=8 against a single dumpfile is mostly waiting.
INCLUDE / EXCLUDE / QUERY — move part of the data. INCLUDE=TABLE:"IN ('ORDERS')" takes one table; EXCLUDE=STATISTICS skips stats (regather them faster on the target); QUERY exports only rows matching a predicate. These are also the parameters with the notorious quoting problem — more on that below.
CONTENT= — DATA_ONLY, METADATA_ONLY, or ALL. Migrate structure first and data later, or refresh data into an existing schema without touching its objects.
TABLE_EXISTS_ACTION= — what impdp does when a table is already there: SKIP (the surprising default — it silently leaves existing tables alone), APPEND, TRUNCATE, or REPLACE. Getting this wrong is how a "refresh" ends up with yesterday's data quietly untouched.
FLASHBACK_TIME / FLASHBACK_SCN — export a consistent point in time. Without it, a long export of a busy database can capture different tables at different moments, and referential integrity in the dump is a coin flip. FLASHBACK_TIME=SYSTIMESTAMP costs nothing and buys you a consistent snapshot.
ENCRYPTION / ENCRYPTION_PASSWORD — the dump file is plaintext data sitting on disk. If it contains anything sensitive, encrypt it, or you've just made an unprotected copy of your production data — the same "least-protected copy" problem that undoes encryption at rest.
VERSION= — writing a dump a lower version can import (e.g., migrating down a release). Set it when the target is older than the source; forget it and the import simply refuses the file.

A minimal, deliberate schema export and remap looks like this:

# export one schema, consistent as of now, in parallel across 4 dump files
expdp system/*** DIRECTORY=dp_dir SCHEMAS=app \
      DUMPFILE=app_%U.dmp PARALLEL=4 FLASHBACK_TIME=SYSTIMESTAMP LOGFILE=exp.log

# import it into a DIFFERENT schema on the target
impdp system/*** DIRECTORY=dp_dir DUMPFILE=app_%U.dmp PARALLEL=4 \
      REMAP_SCHEMA=app:app_test TABLE_EXISTS_ACTION=REPLACE LOGFILE=imp.log

The quoting trap — use a parfile

The single most common way a Data Pump command fails is the shell mangling a filter. This looks right and almost never works from a normal shell:

expdp system/*** SCHEMAS=app INCLUDE=TABLE:"IN ('ORDERS')" DIRECTORY=dp_dir DUMPFILE=x.dmp

The double quotes, single quotes, and parentheses get eaten or reinterpreted by the shell before Data Pump ever sees them. The fix that always works is a parameter file — put the parameters in a text file, where no shell touches them:

# filt.par
SCHEMAS=app
INCLUDE=TABLE:"IN ('ORDERS')"
DIRECTORY=dp_dir
DUMPFILE=orders_only.dmp

expdp system/*** parfile=filt.par

If you take one operational habit from this post: any Data Pump job with an INCLUDE, EXCLUDE, or QUERY goes in a parfile. It's the difference between a repeatable command and a guessing game.

Now prove it

The whole promise of a migration is that the data arrives intact. That's checkable. Build a schema of known size — 5,000 customers and 20,000 orders — export it, drop it, and import it back:

after re-import: DPSHOP customers=5000  orders=20000
   -> lossless round trip: exact same 5,000 + 20,000 rows came back.

Then the actual migration move — import into a new schema with REMAP_SCHEMA:

impdp ... REMAP_SCHEMA=dpshop:dpclone
   DPCLONE customers=5000  orders=20000
   -> same data, new schema name.

And a selective export — INCLUDE just one table, via a parfile — lands only that table in the target:

DPONLY tables=[ORDERS]  orders=20000
   -> only ORDERS came across (no CUSTOMERS). INCLUDE filtered the export exactly.

Same rows, every time, with the counts asserted rather than eyeballed. That's a migration you can hand to a change-approval board.

Don't take my word for it — run it. The Data Pump lab stands up an Oracle Database Free container, builds the 5,000/20,000-row DPSHOP schema, and runs four drills: expdp the schema, drop it and impdp it back (asserting the row counts match), impdp with REMAP_SCHEMA into a new schema, and a parfile-driven INCLUDE export that asserts only the chosen table crosses over. If the round trip loses a row or the filter leaks a table, the run fails. It's proven on every CI push.

What teams get wrong

Command-line filters without a parfile. INCLUDE/EXCLUDE/QUERY typed at the shell get their quotes mangled. Put them in a parfile, always.
PARALLEL against one dump file. The workers serialize on the single file. Use DUMPFILE=name_%U.dmp so each worker writes its own, and match the dumpfile count to PARALLEL.
No consistency point on a busy source. A multi-minute export without FLASHBACK_TIME can capture tables at different SCNs, so foreign keys in the dump don't line up. Always set FLASHBACK_TIME=SYSTIMESTAMP (or an SCN) for anything transactional.
Trusting the default TABLE_EXISTS_ACTION. It's SKIP — existing tables are left as-is, so a "data refresh" into a populated schema can silently do nothing. Choose TRUNCATE, APPEND, or REPLACE on purpose.
A plaintext dump of sensitive data. The .dmp is readable data on disk and in whatever bucket you copy it to. Encrypt it (ENCRYPTION_PASSWORD) if it holds anything you'd protect in the database, and delete it when the move is done.
Forgetting the things that aren't rows. Grants, stored code, and statistics need to arrive too. Data Pump moves them by default, but people who script CONTENT=DATA_ONLY migrations often forget to also move privileges and to regather or import stats — and then the target is "there" but slow and half-broken.
Copying a dump when NETWORK_LINK would do. Exporting to a file, copying it to the target host, and importing is three steps and double the storage. For a straight database-to-database move, impdp NETWORK_LINK= pulls directly and skips the file entirely.

Frequently asked questions

What is Oracle Data Pump and how is it different from the old exp/imp?

Data Pump (the expdp and impdp utilities, introduced in Oracle 10g) is Oracle's tool for exporting and importing data and metadata. The key difference from the legacy exp/imp tools is that Data Pump runs server-side: the client only launches and monitors the job while server processes do the work and read or write the dump file on the database host through a DIRECTORY object. This makes it substantially faster, parallelizable with the PARALLEL parameter, restartable after a failure, and able to remap schemas, tablespaces, and datafiles during import. The original exp/imp streamed through the client, cannot be parallelized, and are deprecated; new work should use Data Pump. Dump files from the two tools are not interchangeable.

Why does Data Pump need a DIRECTORY object?

Because Data Pump reads and writes dump and log files on the database server, not on the client machine, it needs a server-side location it is allowed to use, and Oracle represents that as a DIRECTORY database object mapping a name to a filesystem path. You create it with CREATE DIRECTORY dp_dir AS '/path/on/server' and grant READ and WRITE on it to the user running the job. This indirection is a security feature: it means a user can only write dumps to paths a DBA has explicitly sanctioned, rather than anywhere on the server filesystem. On Autonomous Database and some cloud services you use a pre-provided directory (such as DATA_PUMP_DIR) or an object-store location instead of an arbitrary path.

How do I migrate a schema to a different name with Data Pump?

Use REMAP_SCHEMA=source_schema:target_schema on the impdp command. Data Pump imports all of the source schema's objects and data into the target schema, creating the target user if the dump was a schema-mode export (which includes the user definition). For example, impdp system/*** DIRECTORY=dp_dir DUMPFILE=app.dmp REMAP_SCHEMA=app:app_test loads the exported APP schema into APP_TEST. You can combine it with REMAP_TABLESPACE to redirect storage when the target database has a different tablespace layout, and with TABLE_EXISTS_ACTION to control what happens if objects already exist. This schema remap is the core of most refreshes and migrations between environments.

How do I export or import only some tables or rows?

Use INCLUDE, EXCLUDE, and QUERY. INCLUDE=TABLE:"IN ('ORDERS','CUSTOMERS')" exports only those tables; EXCLUDE=TABLE:"='AUDIT_LOG'" exports everything except one; EXCLUDE=STATISTICS skips optimizer statistics; and QUERY="WHERE created > DATE '2026-01-01'" exports only matching rows. The crucial practical point is quoting: these filters contain quotes and parentheses that a shell will mangle, so put them in a parameter file (parfile) and run expdp parfile=my.par rather than typing them on the command line. A parfile passes the filter to Data Pump verbatim, which is why it is the reliable way to use any of these parameters.

How do I make a Data Pump export consistent?

By default a Data Pump export is not guaranteed to be consistent across tables: on a busy database a long-running export can read different tables at different points in time, so foreign-key relationships in the dump may not line up. To get a single consistent snapshot, set FLASHBACK_TIME=SYSTIMESTAMP (or a specific timestamp) or FLASHBACK_SCN= on the expdp job, which makes every table read as of the same system change number using undo, the same mechanism behind Flashback Query. This is essential for any transactional system and effectively free, so it should be a default habit for production exports. The database must have enough undo retention to cover the duration of the export.

How do I speed up a large Data Pump job?

Use PARALLEL=n to run the job with multiple worker processes, and give it multiple dump files so the workers do not serialize on one file, using the %U wildcard in the dumpfile name (DUMPFILE=exp_%U.dmp), ideally with the dumpfile count at least equal to the parallelism. On import you can also EXCLUDE=STATISTICS and regather stats afterward, which is often faster than importing them. For a database-to-database migration, NETWORK_LINK lets impdp pull directly from the source over a database link, avoiding the write-copy-read cost of a dump file entirely. Finally, size the DIRECTORY location and undo appropriately, and put the dump on fast storage; Data Pump is frequently I/O bound.

Can I move data directly between databases without a dump file?

Yes, with NETWORK_LINK on impdp. You create a database link from the target to the source, then run impdp with NETWORK_LINK= and the usual SCHEMAS or FULL and REMAP parameters, and Data Pump transfers the objects and data straight over the link with no dump file written or copied. This is ideal for migrations where staging a dump would mean copying large files between hosts, and it composes with REMAP_SCHEMA and the filter parameters. The trade-offs are that it holds a connection to the source for the duration and that some data types and options behave slightly differently over a link than through a file, so test the specific objects you are moving.

Does Data Pump require a separate license?

No. Data Pump is a feature of Oracle Database at no additional license cost and is available in all editions, including Oracle Database Free, which is what makes it straightforward to practice with in a local container. The related but separate features you might layer on can have their own licensing: Advanced Security governs dump-file encryption with certain options (though ENCRYPTION_PASSWORD-based encryption is broadly available), and transportable tablespaces and some parallelism characteristics differ by edition. The core expdp/impdp workflow, including schema mode, remap, filters, network mode, and consistency, is available everywhere, so the techniques in this post apply from Free up to Exadata.

Data Pump is the connective tissue of the cloud-migration story: once you've chosen which migration method fits and where the database should live, Data Pump is usually how the data actually moves — into Autonomous or a new home. Do it deliberately: the right mode, a consistency point, a parfile for every filter, parallelism with real dump files, and encryption on anything sensitive. Then prove the move the way that ends the argument — with the Data Pump lab, where a schema goes out, comes back, and the row counts still match to the last row.

Originally published at uptimearchitect.com.

Oracle Partition Pruning: Read One Partition, Not the Whole Table

Uptime Architect — Wed, 09 Sep 2026 17:30:01 +0000

Someone partitions the big table, the migration ticket gets closed, and everyone moves on believing the queries are now fast. Then a report that should read one month of data reads all of it, the plan says PARTITION RANGE ALL, and the partitioning that was supposed to help is just extra objects to manage.

Partitioning doesn't make queries fast. Partition pruning does — and pruning is something the optimizer has to be allowed to do. It's the one payoff that justifies the whole feature for performance work: when a query only needs June, Oracle reads the June partition and skips the other twenty-three. Get it and a report drops from scanning the table to scanning a slice of it. Lose it — usually to one small mistake in the WHERE clause — and you've paid for partitioning and gotten none of the speed. This post is what pruning is, the mistake that quietly kills it, how to read it in the plan, and a lab that watches the same query read one partition or all of them depending on a single function call.

What pruning actually is

A partitioned table is one logical table stored as several physical segments — say, ORDERS split into one partition per month. Partition pruning is the optimizer working out, at parse or run time, which of those segments a query could possibly need, and reading only those. If a query filters on the partition key in a way Oracle can map to partition boundaries, it touches the matching partitions and ignores the rest.

That's the entire performance case for range partitioning. A query for one month out of two years reads 1/24th of the data. A DELETE of last quarter's rows becomes a partition drop. A full scan that was unavoidable becomes a scan of one segment. None of it depends on an index — pruning happens before access method selection, at the level of "which segments are even in play."

The plan tells you whether it happened, in two columns most people never look at: Pstart and Pstop.

The same query, two WHERE clauses. A range predicate on the partition key lets the optimizer map the filter to partition boundaries and read only June. Wrap that same key in a function and the optimizer can no longer see the boundaries, so it falls back to reading every partition — for the identical rows.

The mistake that silently defeats it

Here is the trap, and it's the reason a partitioned table can still scan itself end to end. The optimizer can only prune when it can see the raw partition key in the predicate. The moment you wrap that key in a function, the boundaries disappear and pruning is off:

-- PRUNES: the optimizer maps the range straight to partition boundaries
WHERE order_date >= DATE '2025-06-01' AND order_date < DATE '2025-07-01'

-- DOES NOT PRUNE: a function on the key hides the boundaries -> every partition is scanned
WHERE TO_CHAR(order_date, 'YYYY-MM') = '2025-06'
WHERE TRUNC(order_date) = DATE '2025-06-15'          -- same problem, TRUNC hides the key

Both of those return the right rows. The TO_CHAR version just does it by reading the entire table and throwing away 95% of what it read. It's an easy mistake precisely because it looks tidier — TO_CHAR(...) = '2025-06' reads like exactly what you mean. But the optimizer doesn't evaluate the function against partition metadata; it evaluates it against every row, which means it has to fetch every row first.

The fix is always the same shape: express the filter as a range on the bare column — >= start AND < end — so the boundaries stay visible. Same logic for TRUNC, TO_CHAR, NVL, arithmetic, an implicit type conversion because you compared a DATE column to a string — any of them can turn pruning off. When a partitioned table is scanning more than it should, a function on the partition key is the first thing to look for.

Static pruning vs dynamic pruning

Not all pruning looks the same in the plan, and the difference is worth knowing so you don't misread a healthy plan as a broken one:

Static pruning — the optimizer knows the partitions at parse time, because the predicate uses literals or is otherwise resolvable up front. Pstart/Pstop show actual partition numbers, e.g. 19 / 19. This is the cleanest case.
Dynamic pruning — the partitions can't be known until run time, typically because the predicate uses a bind variable (WHERE order_date >= :d). The plan shows Pstart/Pstop as KEY, which is not a problem — it means "pruning happens, but the specific partitions are decided at execution." Bind variables are still the right default for reuse; KEY is pruning working, just later.

The failure you're hunting is neither of those. It's Pstart = 1 and Pstop = the last partition — the plan operation PARTITION RANGE ALL. That's the optimizer telling you it gave up and read everything.

Now prove it

Put 1.2 million orders into a table with one partition per month — twenty-four monthly partitions across 2024 and 2025 — and run the same question ("how many orders in June 2025?") two ways. First the range predicate, then the function. Here are the real plans, straight from DBMS_XPLAN:

--- WHERE order_date >= DATE '2025-06-01' AND order_date < DATE '2025-07-01' ---
| Id  | Operation       | Name   | Pstart| Pstop |
|   2 |   PARTITION RANGE SINGLE|    |    19 |    19 |
|   3 |    TABLE ACCESS FULL    | ORDERS |    19 |    19 |

--- WHERE TO_CHAR(order_date,'YYYY-MM') = '2025-06' ---
| Id  | Operation        | Name   | Pstart| Pstop  |
|   2 |   PARTITION RANGE ALL|        |     1 |1048575 |
|   3 |    TABLE ACCESS FULL | ORDERS |     1 |1048575 |

Both queries return the same 50,000 rows. The first reads one partition (PARTITION RANGE SINGLE, Pstart = Pstop = 19) and burns 1,725 buffer gets. The second reads all of them (PARTITION RANGE ALL) and burns 41,448 — about 24× more work for the identical answer. That factor is not a coincidence: it's one partition versus twenty-four, which is exactly what pruning bought.

The odd-looking Pstop = 1048575 in the second plan is just Oracle's sentinel for "the maximum possible partition" on an interval-partitioned table — it means all partitions, not that a million of them exist. PARTITION RANGE ALL is the phrase that matters.

Don't take my word for it — run it. The partition pruning lab stands up an Oracle Database Free container, builds the 1.2M-row monthly-partitioned table, and runs both queries with GATHER_PLAN_STATISTICS. It asserts the range predicate gets PARTITION RANGE SINGLE on a single partition, the function predicate gets PARTITION RANGE ALL, both return the same 50,000 rows, and the pruned query reads at least 5× fewer buffers (it reads ~24× fewer). If pruning stops working, the run fails. It's proven on every CI push.

What teams get wrong

A function on the partition key. The headline mistake: TO_CHAR, TRUNC, NVL, or arithmetic around the key turns pruning off and the table scans itself. Filter with a bare-column range instead.
An implicit type conversion. Comparing a DATE partition key to a string literal (WHERE order_date = '2025-06-15') makes Oracle apply an internal conversion to the column — same effect as wrapping it in a function. Compare dates to DATE literals and numbers to numbers.
Reading KEY as broken. Seeing Pstart = KEY and assuming pruning failed, then ripping out bind variables to "fix" it. KEY is dynamic pruning — it's working. PARTITION RANGE ALL is the broken case.
Partitioning on the wrong key. Partitioning by a column the queries don't filter on. Pruning can only use the partition key; if your access pattern filters by customer_id but you partitioned by region, nothing prunes. Partition for how the data is queried, not how it's shaped.
Expecting pruning to replace an index. Pruning narrows which segments are read; within a partition you still scan or index as usual. A month's partition that's still millions of rows may need a local index on top of the pruning. The two work together — reading the plan tells you which one you're missing.
Global indexes that unravel on partition maintenance. Dropping or truncating a partition invalidates global indexes unless you maintain them (UPDATE INDEXES), quietly turning a fast partition drop into an index rebuild. Prefer local indexes with partitioning unless a global one is genuinely required.

Frequently asked questions

What is partition pruning in Oracle?

Partition pruning is the optimizer eliminating partitions that a query cannot possibly need and reading only the ones that remain. When a query filters on the partition key in a way Oracle can map to partition boundaries, it accesses just the matching partitions and skips the rest, which is the primary performance benefit of partitioning. It happens before access-method selection and does not depend on indexes: it operates at the level of deciding which physical segments are in play. In the execution plan it appears as operations such as PARTITION RANGE SINGLE, PARTITION RANGE ITERATOR, or PARTITION RANGE ALL, with Pstart and Pstop columns showing which partitions are touched. Reading one partition instead of the whole table is what turns partitioning into speed.

Why is my partitioned table still doing a full scan of every partition?

The most common cause is a predicate the optimizer cannot map to partition boundaries. Wrapping the partition key in a function such as TO_CHAR, TRUNC, or NVL, doing arithmetic on it, or triggering an implicit type conversion (for example comparing a DATE partition key to a string literal) all hide the key, so the optimizer falls back to PARTITION RANGE ALL and scans every partition even though the query returns the same rows. The fix is to filter with a range on the bare partition key, such as order_date >= DATE start AND order_date < DATE end, and to compare dates to DATE literals and numbers to numbers so no conversion is applied to the column. A plan showing Pstart = 1 and Pstop equal to the last partition is the signature of pruning being defeated.

What do Pstart and Pstop mean in an execution plan?

Pstart and Pstop are the first and last partitions the operation will access. When they show specific numbers that are equal, such as 19 and 19, the query is reading a single partition (PARTITION RANGE SINGLE). When they show a small range like 19 and 21, it is reading a contiguous set (PARTITION RANGE ITERATOR). When they show the value KEY, pruning is happening dynamically at run time, typically because a bind variable is involved, and the specific partitions are decided during execution rather than at parse time. When Pstart is 1 and Pstop is the last partition (often shown as a large sentinel number on interval-partitioned tables), the operation is PARTITION RANGE ALL, meaning no pruning occurred and every partition is read.

What is the difference between static and dynamic partition pruning?

Static pruning happens at parse time, when the optimizer can determine the exact partitions from the predicate because it uses literals or otherwise-resolvable values; the plan shows actual partition numbers in Pstart and Pstop. Dynamic pruning happens at execution time, most often because the predicate uses bind variables whose values are not known until the query runs; the plan shows KEY in Pstart and Pstop to indicate that pruning will occur but the specific partitions are chosen at run time. Dynamic pruning is not a problem and is fully expected with bind variables, which remain the right default for cursor reuse. Both are healthy; the case to worry about is PARTITION RANGE ALL, where no pruning of any kind takes place.

Does partition pruning replace indexes?

No. Partition pruning decides which partitions to read; it does not decide how to read within a partition. If a pruned partition is still large, a query that returns a small fraction of it may still need an index to avoid scanning the whole partition. Pruning and indexing are complementary: pruning narrows the segments, and an index (usually a local index, aligned with the partitioning) narrows the rows within them. A common tuning outcome is a query that prunes correctly to one partition but still scans that entire partition because the selective predicate on a non-key column has no supporting index. Reading the execution plan shows whether the cost is coming from touching too many partitions or from scanning too much within one.

Should I use a local or global index on a partitioned table?

Prefer local indexes with partitioning unless there is a specific reason not to. A local index is partitioned the same way as the table, so each index partition corresponds to one table partition; this keeps partition maintenance operations such as dropping or truncating a partition fast and localized, and it aligns naturally with pruning. A global index spans all partitions and can be necessary when you need to enforce uniqueness on a column that is not the partition key or to support a query pattern that crosses partitions efficiently, but it becomes invalid when a partition is dropped or truncated unless you maintain it with the UPDATE INDEXES clause, which turns a fast metadata operation into an index rebuild. Choose local by default and reach for global only for the cases that require it.

Does partitioning require a separate Oracle license?

The Oracle Partitioning option is a separately licensed option of Oracle Database Enterprise Edition on-premises, so using partitioning on-prem Enterprise Edition requires that license. In Oracle Cloud Infrastructure, including Autonomous Database and the Base Database and Exadata cloud services, partitioning is included. It is also available in Oracle Database Free for development and testing, which is what makes it straightforward to reproduce pruning behavior in a local container. Because licensing depends on edition and platform and can change, confirm your specific entitlement before relying on partitioning in production on-premises rather than assuming it is included.

Pruning is the same discipline as the rest of the performance series: know what the optimizer is actually doing and give it what it needs to do the fast thing. Reading the execution plan is how you catch PARTITION RANGE ALL in the first place; good statistics keep the row estimates that drive the plan honest; and SQL plan baselines keep a plan that prunes today from quietly regressing tomorrow. Partition for how the data is queried, keep functions off the partition key, and check Pstart/Pstop when a query reads more than it should. Then prove it the way that ends the argument — with the partition pruning lab, where the same query reads one partition or twenty-four depending on a single function call.

Originally published at uptimearchitect.com.

Oracle Data Redaction: Mask at Read Time, Prove It Isn't Access Control

Uptime Architect — Sat, 05 Sep 2026 19:08:34 +0000

The support rep sees ****-****-****-0001 on their screen and everyone relaxes. The card number is "masked," the demo looks compliant, the box gets ticked. Then someone runs an export, or a report built on the same table, or reads the datafile off disk — and the full number is right there, exactly where it always was, unchanged.

That gap between what redaction looks like and what it does is where teams get burned. Data Redaction (the DBMS_REDACT package, part of Advanced Security) is a genuinely useful, near-zero-friction control: it masks sensitive columns as a query returns them, with no application change. But it is constantly mistaken for two things it is emphatically not — encryption at rest, and access control. Get that wrong and you've put a sticker over the problem. This post is what redaction actually is, the four ways to mask, who gets masked and who doesn't, and — the part that ends the argument — a lab that proves the boundary by reading the real data straight off the disk while the screen still says ****.

What redaction is — and the two things it isn't

Redaction is a read-time transform. When a query returns a protected column, Oracle rewrites the value on the way out — to a mask, a partial value, a pattern, or a random stand-in — based on a policy you attach to the table. The application doesn't change, the SQL doesn't change, and, crucially, the stored data doesn't change at all. Nothing is written. The bytes in the block are the same before and after you add the policy; only the result the client receives is different.

That single fact defines both edges of what redaction is good for:

It is not encryption at rest. The real value is still sitting in the datafile, in every RMAN backup, in redo, and in a Data Pump export taken by a privileged user. Redaction never touches the bytes on disk. If the threat is a stolen datafile or a lost backup, redaction does nothing — that's what Transparent Data Encryption is for, and the two are complementary, not alternatives.
It is not access control. The WHERE clause is evaluated against the real value, not the masked one, so a user who only ever sees ****-****-****-0001 can still confirm a full card number by guessing it in a predicate. Exports, expressions, and inference all route around the mask. Redaction reduces casual exposure — the over-the-shoulder look, the screenshot, the ad-hoc report — and shrinks who sees full values by default. It does not stop a determined, authorized user.

The right mental model: redaction is the last-mile display filter. It sits on top of privileges, TDE, and auditing — not instead of them.

The four ways to mask

A redaction policy attaches to a table and names one or more columns, each with a function type that decides how the value is masked:

FULL — replace the whole value with a fixed default: 0 for numbers, a single space for character data. The blunt instrument; use it when no part of the value should show.
PARTIAL — keep some of the value and mask the rest. This is the one you'll use most: show the last four digits of a card, the last four of an SSN, the area code of a phone number. Oracle ships built-in format constants for the common cases (REDACT_CCN16_F12 for a 16-digit card, REDACT_US_SSN_F5 for a US SSN) so you don't hand-roll the position math.
REGEXP — pattern-based masking, for values without a fixed layout. The classic is an email address: mask the name part, keep the domain, so user0001@example.com becomes xxxx@example.com.
RANDOM — replace the value with a random one of the same type. Useful when a real-looking value keeps a screen or a downstream process happy but the actual data must not leak.

(There's also NULLIFY, which returns NULL, and NONE, a pass-through you can use to stage a policy before turning it on.) Building the policy is two calls — ADD_POLICY creates it on the first column, ALTER_POLICY adds the rest:

BEGIN
  -- card: PARTIAL, using the built-in 16-digit credit-card format -> shows only the last 4 digits
  DBMS_REDACT.ADD_POLICY(
    object_schema       => 'REDOWN',
    object_name         => 'CUSTOMERS',
    policy_name         => 'RED_CUSTOMERS',
    column_name         => 'CARD',
    function_type       => DBMS_REDACT.PARTIAL,
    function_parameters => DBMS_REDACT.REDACT_CCN16_F12,
    expression          => '1=1');                 -- who to redact; see below

  -- ssn: PARTIAL, built-in US-SSN format -> XXX-XX-6789
  DBMS_REDACT.ALTER_POLICY(
    object_schema       => 'REDOWN', object_name => 'CUSTOMERS', policy_name => 'RED_CUSTOMERS',
    action              => DBMS_REDACT.ADD_COLUMN, column_name => 'SSN',
    function_type       => DBMS_REDACT.PARTIAL,
    function_parameters => DBMS_REDACT.REDACT_US_SSN_F5);

  -- email: REGEXP -> mask the name, keep the domain
  DBMS_REDACT.ALTER_POLICY(
    object_schema         => 'REDOWN', object_name => 'CUSTOMERS', policy_name => 'RED_CUSTOMERS',
    action                => DBMS_REDACT.ADD_COLUMN, column_name => 'EMAIL',
    function_type         => DBMS_REDACT.REGEXP,
    regexp_pattern        => DBMS_REDACT.RE_PATTERN_EMAIL_ADDRESS,
    regexp_replace_string => DBMS_REDACT.RE_REDACT_EMAIL_NAME);

  -- salary: FULL -> a NUMBER redacts to 0
  DBMS_REDACT.ALTER_POLICY(
    object_schema => 'REDOWN', object_name => 'CUSTOMERS', policy_name => 'RED_CUSTOMERS',
    action        => DBMS_REDACT.ADD_COLUMN, column_name => 'SALARY',
    function_type => DBMS_REDACT.FULL);
END;
/

One policy per table, as many columns as you like. Note there's no GRANT, no view, no application change — the table is the same table it was a moment ago.

Who gets redacted, and who doesn't

The expression parameter is the whole game for targeting. It's a SQL predicate evaluated per session: when it's true, the session is redacted; when it's false, that session sees the real value. 1=1 above redacts everyone who is subject to the policy — fine for a demo, wrong for production. A real policy targets by identity or context:

-- redact for everyone EXCEPT sessions that hold a role trusted to see full PII
expression => 'SYS_CONTEXT(''SYS_SESSION_ROLES'', ''PII_FULL'') IS NULL'

You can key the expression off the session user, an enabled role, an application context, the client identifier, or the connection's IP — anything you can express in SQL. The one rule: base it on something the user can't spoof. Redacting on a CLIENT_IDENTIFIER that the application sets for its own convenience is a mask anyone can lift by setting the identifier themselves.

Above the expression sits a hard override: the EXEMPT REDACTION POLICY system privilege. A user who holds it bypasses every redaction policy in the database, regardless of any expression. SYS is always exempt. Grant the privilege deliberately, to as few accounts as possible, and audit who has it — it is the master key to every mask you've set.

Where redaction happens in the read path — and where it doesn't. The WHERE clause is evaluated against the real stored value (so predicates and inference see plaintext); only the projected column that comes back is masked, and only for a session that isn't exempt. The datafile, backups, and exports are never touched.

Now prove it

Put the same rows behind the policy above, then read them back as two different users — an ordinary CLERK, who is subject to the policy, and an AUDITOR, who holds EXEMPT REDACTION POLICY:

-- as CLERK (subject to the policy)          -- as AUDITOR (EXEMPT REDACTION POLICY)
SELECT card, ssn, email, salary              SELECT card, ssn, email, salary
FROM   redown.customers WHERE id = 1;        FROM   redown.customers WHERE id = 1;

column	`CLERK` (subject)	`AUDITOR` (exempt)
`card`	`**--**-0001`	`4111-2222-3333-0001`
`ssn`	`XXX-XX-0001`	`123-45-0001`
`email`	`xxxx@example.com`	`user0001@example.com`
`salary`	`0`	`50001`

Same row, same instant, one policy. The clerk sees masks; the auditor sees plaintext. That's the feature working exactly as intended — masking decided per session, at read time, with the stored data untouched underneath. The proof that it's untouched is right there in the auditor's column: nothing was destroyed to produce the mask.

What it doesn't do — the same lab, two more reads

Here's where the myths die. First, redaction is not encryption at rest. Read the datafile off disk the way a thief with a copy of your storage would — not through the database, which redacts, but straight off the filesystem:

# the real card prefix, written to the ordinary datafile in the clear:
grep -a -c '4111-2222-3333' /opt/oracle/oradata/FREE/FREEPDB1/red_data.dbf   # -> 162   (still there)

162 hits. The real card numbers are sitting in the datafile in plaintext, exactly where redaction left them — because redaction never touched the disk. Contrast that with the TDE lab, where the same kind of grep finds zero hits in the encrypted datafile. That's the whole distinction in one command: encryption removes the data from the file; redaction leaves it and masks the query.

Second, redaction is not access control. Two ways to see it. Try to peel the mask off with an expression and Oracle stops you — a redacted column used inside most functions returns NULL, a deliberate anti-tamper:

-- as CLERK:
SELECT 'the card is ' || card FROM redown.customers WHERE id = 1;   -- returns NULL, not the real value

But that same protection doesn't extend to the predicate, and that's the hole. The WHERE clause runs against the real stored value, so a user who has only ever seen **** can still confirm — or enumerate — the true value by guessing it in a filter:

-- as CLERK, who "can't see" full card numbers:
SELECT COUNT(*) FROM redown.customers WHERE card = '4111-2222-3333-0001';   -- 1  -> confirmed

The count comes back 1. The mask on the screen never mattered; the filter saw the real data. Redaction masks the output, not the query. A user you don't trust with the data shouldn't be able to query the table at all — that's a GRANT, not a mask.

Don't take my word for it — run it. The Data Redaction lab stands up an Oracle Database Free container, builds one table behind a four-column DBMS_REDACT policy, and reads it back as a subject CLERK and an EXEMPT AUDITOR. It asserts the clerk sees masks and the auditor sees plaintext, then greps the datafile and asserts the real card numbers are still on disk (redaction ≠ encryption), and asserts a WHERE clause on the true value still matches (redaction ≠ access control). If the clerk ever sees a real value, or the data isn't on disk, the run fails. It's proven on every CI push.

What teams get wrong

Treating redaction as encryption. "The PII is redacted, so we don't need TDE." The datafile, the backup, and the export all still hold the real values. Redaction is a screen filter; the disk is plaintext. Pair it with TDE — one protects the file, the other narrows the view.
Treating redaction as access control. Letting users who shouldn't see PII query the table because "it's redacted anyway." Inference through predicates, full Data Pump exports, and any exempt account route straight around the mask. If someone shouldn't have the data, revoke the access — don't mask it.
Redacting on a spoofable expression. Basing the policy on a CLIENT_IDENTIFIER or context value the application sets for convenience. Anyone who can set the same value lifts the mask. Key the expression off roles or session facts the user can't forge.
Forgetting the bypass paths. EXEMPT REDACTION POLICY holders and SYS see everything; a full Data Pump export carries the real data; a CREATE TABLE AS SELECT run by an exempt user materializes plaintext into a new, unprotected table. Audit who is exempt, and treat exports as sensitive.
Masking a column the application computes on. Because a redacted column returns NULL inside expressions, redacting a value the app concatenates, hashes, or does arithmetic on can silently turn results into NULL. Redact what's displayed, not what's processed — and test the app against the policy.
Confusing it with the Data Masking pack. Redaction is dynamic — it happens live, on production, at read time, and the data stays real. Static data masking (subsetting) permanently rewrites values, and is for building non-production copies. Different tools for different jobs; don't reach for redaction to sanitize a test clone.

Frequently asked questions

What is Oracle Data Redaction (DBMS_REDACT)?

Data Redaction is an Oracle Advanced Security feature, managed through the DBMS_REDACT package, that masks the values of sensitive columns as a query returns them. It is a read-time, or dynamic, control: when a session subject to a redaction policy selects a protected column, Oracle rewrites the returned value to a mask, a partial value, a regular-expression replacement, or a random stand-in, based on a policy attached to the table. The application, the SQL, and — most importantly — the data stored on disk are unchanged; only the result delivered to the client is transformed. It requires no application changes and is commonly used to keep full PII such as card numbers, national IDs, and salaries off screens and reports for staff who only need to see part of the value.

Does Data Redaction change or encrypt the stored data?

No. Redaction never modifies the data at rest. The real value stays in the datafile, in redo, in RMAN backups, and in Data Pump exports taken by a privileged user; redaction only alters what a query returns to a subject session. This is the single most important thing to understand about it: redaction is not encryption at rest and provides no protection if the datafile or a backup is stolen. You can prove it by reading the datafile off disk with grep, where the real values are plainly visible. To protect the data in the files and backups you need Transparent Data Encryption (TDE), which is a separate and complementary control — TDE encrypts the bytes on disk, redaction narrows who sees full values on screen.

What is the difference between Data Redaction, TDE, and Data Masking?

The three solve different problems. Transparent Data Encryption encrypts data at rest — the bytes in datafiles and backups — and defends against theft of the files. Data Redaction masks values dynamically at read time on the live database, leaving the stored data intact, and reduces who sees full sensitive values by default. Static Data Masking (subsetting) permanently rewrites sensitive values to create sanitized non-production copies, so a test or development database contains realistic but fake data. A mature setup uses all three: TDE so a stolen file is unreadable, redaction so production screens show only what each role needs, and static masking so non-production environments never contain real PII in the first place.

What redaction function types does DBMS_REDACT support?

There are several. FULL replaces the entire value with a fixed default — zero for numbers, a single space for character data. PARTIAL keeps part of the value and masks the rest, such as showing only the last four digits of a credit-card number or a Social Security number, and Oracle provides built-in format constants for common cases. REGEXP uses a regular expression to mask by pattern, for example masking the name portion of an email address while keeping the domain. RANDOM substitutes a random value of the same datatype, useful when a realistic-looking but fake value is needed. NULLIFY returns NULL, and NONE is a pass-through used to stage or temporarily disable a policy. A single policy can apply different function types to different columns of the same table.

How do I control which users see redacted data versus real data?

Two mechanisms. First, every policy has an expression, a SQL predicate evaluated per session: when it is true the session is redacted, when false it sees the real value. You typically key the expression off the session user, an enabled role, an application context, or another session fact — but it must be something the user cannot spoof, so roles are safer than a client identifier the application sets. Second, the EXEMPT REDACTION POLICY system privilege overrides everything: any user holding it bypasses all redaction policies regardless of their expressions, and SYS is always exempt. Grant that privilege to as few accounts as possible and audit who holds it, because it is effectively the master key to every mask in the database.

Can a user bypass Oracle Data Redaction?

Redaction is not access control, and an authorized user who can query the table has several routes around the mask. The WHERE clause is evaluated against the real value, so a user can confirm or enumerate true values by testing them in a predicate even though the projected column is masked. A user with the EXEMPT REDACTION POLICY privilege, or SYS, sees everything. A full Data Pump export by a privileged account carries the real data, and a CREATE TABLE AS SELECT run by an exempt user copies plaintext into a new, unprotected table. Redaction reduces casual and default exposure of full values; it does not stop a determined user who is authorized to query the data. The control that actually restricts who can read the data is privileges — least-privilege grants — not the mask.

Does Data Redaction slow down queries or affect indexes?

The masking transform itself is lightweight, applied to the rows as they are projected back to the client, so the direct overhead is small. Importantly, because the WHERE clause and joins operate on the real underlying values rather than the masked output, redaction does not prevent the optimizer from using indexes or change which rows match — the query plan is unaffected by the policy. The practical performance and correctness caveat is different: because a redacted column returns NULL when used inside most SQL expressions and functions, application code that computes on a redacted column can silently produce NULL results. The guidance is to redact columns that are displayed rather than columns the application processes, and to test the application against the policy before enabling it.

Does Oracle Data Redaction require a separate license?

Data Redaction is part of the Oracle Advanced Security option. On-premises Enterprise Edition, Advanced Security is a separately licensed pack, so using redaction there requires that license — the same pack that covers TDE. In Oracle Cloud Infrastructure, including Autonomous Database and the Base Database and Exadata cloud services, Advanced Security is included. It is also available in Oracle Database Free for development and testing, which is what makes it straightforward to try in a local container. Because licensing depends on your edition and platform and can change, confirm your specific entitlement before enabling redaction in production on-premises rather than assuming it is included.

Redaction is the third leg of the same data-security discipline as the rest: TDE makes the files unreadable when they're stolen, unified auditing records who looked, and the hardening checklist and least-privilege grants decide who can query at all. Redaction sits on top of those, narrowing what full values reach a screen or a report by default. Use it for exactly that — and never as a stand-in for the layer beneath it. Then prove the boundary the way that ends the argument: with the Data Redaction lab, where the screen says **** and the datafile, read off disk, says otherwise.

Originally published at uptimearchitect.com.

Oracle Flashback: Undo at the Database Level

Uptime Architect — Sun, 30 Aug 2026 20:21:09 +0000

Someone runs UPDATE accounts SET balance = 0 and forgets the WHERE. Commits it. A thousand balances are gone. The instinct is to reach for the backup — find last night's copy, restore it somewhere, extract the rows, lose everything that happened since. Hours, if it works at all.

There's a faster answer, and it's built into the database: undo the mistake in place. Oracle Flashback is a family of features that reverse human error at point-in-time — a query, a table, a dropped object, or the entire database — in seconds to minutes, without restoring anything. And here's the part that catches teams off guard: your standby doesn't help. A Data Guard replica faithfully replicated that UPDATE to the standby within seconds. Replication is not a backup; it copies your mistakes as diligently as your data. Flashback is what actually saves you.

The confusion is that "Flashback" is not one thing — it's half a dozen, built on different plumbing, with different reach. Knowing which one to grab, and where each one stops, is the whole skill.

The family, and what each is built on

Every Flashback feature reverses time, but they read from different places, and that determines their limits:

Flashback Query / Version Query — read a table's past directly, from UNDO. Nothing is changed; you just SELECT as of a past time. Bounded by undo_retention.
Flashback Table (to SCN/time) — rewind a table's rows to a past point, also from UNDO. Actually changes the data back. Bounded by undo_retention, and needs row movement.
Flashback Drop — recover a dropped table from the recycle bin, where dropped objects sit until space is needed.
Flashback Database — rewind the entire database to a past point, from flashback logs (or a guaranteed restore point). The heavy hammer; needs ARCHIVELOG.
Flashback Data Archive — long-term history for chosen tables, so you can query years back, long after UNDO is gone. (Different problem — compliance history, not oops-recovery.)

The rule of thumb: the smaller the blast radius, the cheaper and faster the fix. Reach for the narrowest tool that covers what you broke.

What did you break, and which Flashback undoes it. Start narrow: a query to look, a table-level flashback for one object, the whole-database rewind only when the damage is broad. The heavier the tool, the more it needs set up in advance.

Look before you leap: Flashback Query

Before you change anything, you can see the past. Flashback Query reads a table as it was, straight from UNDO — no restore, no downtime, just a SELECT:

-- how many rows matched five minutes ago?
SELECT COUNT(*) FROM accounts AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '5' MINUTE;

-- or as of a precise SCN you captured before the change
SELECT * FROM accounts AS OF SCN 12345678 WHERE id = 42;

Its sibling, Flashback Version Query, shows you the history of a row — every version between two points, with who-did-what pseudo-columns — which is how you find when the damage happened before you undo it:

SELECT versions_startscn, versions_operation, balance
FROM   accounts VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE AND SYSTIMESTAMP
WHERE  id = 42;

versions_operation reads I/U/D — insert, update, delete. This is often all you need: read the good values as of the right SCN and put them back yourself. But for a whole table, there's a cleaner way.

Undo a bad DML: Flashback Table to SCN

Someone zeroed every balance and committed. You don't need to reconstruct anything — rewind the table itself to the moment before the mistake:

-- capture the SCN BEFORE the change (or find it with a version query afterward)
SELECT dbms_flashback.get_system_change_number FROM dual;   -- e.g. 12345678

-- ... the bad UPDATE happens, gets committed ...

-- rewind just this table to that point
FLASHBACK TABLE accounts TO SCN 12345678;

Two requirements bite people here. First, the table must have been created with ENABLE ROW MOVEMENT — Flashback Table physically re-inserts the recovered rows, changing their rowids, and Oracle refuses unless row movement is on. It's the single most common reason this fails. Second, you can't flash back across a structural change (a DDL) to the table, and Oracle won't let you flash back to within a few seconds of the table's creation (ORA-01466). Within those limits it's instant, keeps working within undo_retention, and leaves triggers and indexes intact.

Recover a dropped table: Flashback Drop

DROP TABLE doesn't (by default) destroy the table — it renames it and moves it to the recycle bin, a per-user holding area where it waits until the tablespace actually needs the space. So a drop is reversible until then:

-- oops
DROP TABLE accounts;

-- bring it back, rows and all, from the recycle bin
FLASHBACK TABLE accounts TO BEFORE DROP;

-- what's in the bin?
SELECT object_name, original_name, droptime FROM recyclebin;

Two things to know. DROP TABLE accounts PURGE bypasses the recycle bin — the object is gone immediately, no flashback. And the recycle bin is not infinite insurance: objects are purged automatically under space pressure, so "it was in the recycle bin last week" is not a recovery plan. It's a safety net for the recent accidental drop, which is exactly when you need it.

Rewind everything: Flashback Database

Sometimes the damage is broad — a bad deployment, a truncate spree, a dropped schema. Table-level tools won't cut it. Flashback Database rewinds the entire database to a past SCN, timestamp, or named restore point, using flashback logs it has been keeping since you turned the feature on. It's the closest thing to a database-wide undo button — and the one that must be set up before the disaster:

-- prerequisites (once): ARCHIVELOG + a flashback recovery area
-- then, before a risky change, drop a marker you can rewind to:
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

-- ... the disaster: someone drops the whole app schema ...

-- rewind the WHOLE database to the restore point
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
ALTER DATABASE OPEN RESETLOGS;

A guaranteed restore point is the seatbelt you fasten before anything risky — an upgrade, a big data load, a schema migration. It pins the flashback logs so you can always get back to that exact point, no matter how much changes. In a multitenant database you can even rewind a single pluggable database with FLASHBACK PLUGGABLE DATABASE, leaving the others untouched. The catch is the setup: Flashback Database is only available if the database was already in ARCHIVELOG mode with flashback logging (or a guaranteed restore point) in place. Turn it on when things are calm; you can't retrofit it mid-crisis.

Don't take my word for it — run it. The Flashback lab builds a 1,000-row ACCOUNTS table and stages three disasters, each reversed and asserted: a committed UPDATE with no WHERE zeroes all 1,000 balances → FLASHBACK TABLE ... TO SCN brings them back (0 still zero); the table is dropped → FLASHBACK TABLE ... TO BEFORE DROP restores it with all 1,000 rows; and the entire LABUSER schema is dropped → the lab enables ARCHIVELOG, creates a guaranteed restore point, and FLASHBACK DATABASE TO RESTORE POINT rewinds the whole database, bringing the schema back with its 1,000 rows. If any recovery doesn't restore the data, the run fails. All three are proven on every CI push.

What teams get wrong

Believing a standby is a backup. Data Guard replicates your DELETE to the standby in seconds. A replica protects against hardware loss, not human error — for that you need Flashback (or a delayed standby apply). Don't confuse the two.
Creating tables without ENABLE ROW MOVEMENT. Then the day you need FLASHBACK TABLE ... TO SCN, it's refused. Turn row movement on for tables you'd ever want to rewind.
Assuming Flashback Query reaches back forever. It's bounded by undo_retention and the size of the undo tablespace — under pressure, old undo is overwritten and you get ORA-01555. For guaranteed reach, set RETENTION GUARANTEE on undo, or use a Flashback Data Archive.
DROP ... PURGE out of habit, or a full recycle bin. PURGE skips the recycle bin entirely, and the bin is auto-purged under space pressure. Neither is a bug — just know that "flashback drop" only works while the object is still in the bin.
Never enabling Flashback Database — or never testing it. It's unavailable unless ARCHIVELOG + flashback logging were on before the disaster. Turn it on, size the FRA, and actually run a flashback-to-restore-point drill, so it's not the first time when it counts.
A guaranteed restore point quietly filling the FRA. Guaranteed restore points pin flashback logs forever; leave one lying around and the recovery area fills, which can hang the database. Drop the restore point once the risky change is safely done.
Reaching for Flashback Database when a table flashback would do. Rewinding the whole database to fix one table punishes everyone. Match the tool to the blast radius — narrowest first.

Frequently asked questions

What is Oracle Flashback?

Oracle Flashback is a family of features that let you reverse the effects of human error at a point in time without restoring a backup. It includes Flashback Query and Version Query (read a table as it was in the past, from undo data), Flashback Table (rewind a table’s rows to a past SCN or time), Flashback Drop (recover a dropped table from the recycle bin), Flashback Database (rewind the entire database to a past point using flashback logs or a restore point), and Flashback Data Archive (retain long-term history for chosen tables). Each reads from different underlying data — undo, the recycle bin, or flashback logs — which determines how far back it can go and what it requires.

What is the difference between Flashback and restoring from a backup?

Restoring from a backup (for example with RMAN) copies datafiles from a saved backup and rolls forward, which is slower, usually needs a separate location or downtime, and typically loses everything since the backup unless you also apply archived logs. Flashback undoes changes in place using data the database already keeps — undo, the recycle bin, or flashback logs — so recovery is fast and surgical, from seconds to minutes, with no restore. Flashback is the right tool for recent human error (a bad UPDATE, a dropped table, a bad deployment); RMAN restore is the fallback for media loss, corruption, or when the change is older than your flashback and undo retention allows.

Why does FLASHBACK TABLE ... TO SCN require row movement?

Flashback Table recovers rows by physically deleting the current rows and re-inserting the past versions, which gives them new rowids. Oracle only permits an operation that changes rowids if the table has row movement enabled, so FLASHBACK TABLE ... TO SCN (or TO TIMESTAMP) fails on a table created without it. You enable it with CREATE TABLE ... ENABLE ROW MOVEMENT or ALTER TABLE name ENABLE ROW MOVEMENT. This is the most common reason a table-level flashback is refused. Note that Flashback Drop (TO BEFORE DROP) and Flashback Query do not need row movement — only the table-to-SCN rewind does.

How far back can Flashback Query go?

Flashback Query and Flashback Table read from undo, so they can go back only as far as undo for the changed blocks still exists — governed by the UNDO_RETENTION parameter and the size of the undo tablespace. Under write pressure Oracle may overwrite unexpired undo to avoid failing DML, so a query too far back can return ORA-01555 (snapshot too old). To make the reach reliable, set RETENTION GUARANTEE on the undo tablespace (which prevents overwriting unexpired undo) and size it accordingly, or use a Flashback Data Archive for tables that need long-term, guaranteed history measured in months or years.

What is FLASHBACK TABLE ... TO BEFORE DROP and the recycle bin?

When you DROP a table, Oracle does not immediately destroy it by default — it renames the object and its dependents and moves them to the recycle bin, a per-user logical holding area, where they remain until the tablespace needs the space. FLASHBACK TABLE name TO BEFORE DROP restores the table and its data from the recycle bin, optionally renaming it with RENAME TO. Two caveats: DROP TABLE name PURGE bypasses the recycle bin and destroys the object immediately (no flashback), and objects in the recycle bin are purged automatically under space pressure, so it is a safety net for a recent accidental drop, not a long-term backup.

What does Flashback Database require?

Flashback Database rewinds the entire database to a past SCN, timestamp, or restore point using flashback logs, and it must be set up in advance. The database must be in ARCHIVELOG mode with a fast recovery area (FRA) configured, and either flashback logging turned on (ALTER DATABASE FLASHBACK ON) or a guaranteed restore point created, so the flashback logs exist to rewind through. The operation itself is run with the database mounted (not open): SHUTDOWN, STARTUP MOUNT, FLASHBACK DATABASE TO ..., then ALTER DATABASE OPEN RESETLOGS. Because these prerequisites cannot be added after the fact, enable Flashback Database before you need it and test a flashback-to-restore-point drill.

Can I flash back a single pluggable database (PDB)?

Yes. In a multitenant database you can rewind one PDB independently with FLASHBACK PLUGGABLE DATABASE, leaving the other PDBs and the rest of the CDB untouched, provided the CDB is in ARCHIVELOG mode and local undo is in use (the default in recent releases). You can flash a PDB back to a PDB-level restore point, an SCN, or a timestamp, and you can create guaranteed restore points scoped to a single PDB. This is far less disruptive than flashing back the whole container database, so for damage confined to one PDB it is the right level to operate at — the same "match the tool to the blast radius" principle as choosing between table and database flashback.

Does a Data Guard standby protect against an accidental DELETE?

No, not by itself. A standby applies the same redo the primary generated, so a committed accidental DELETE or a bad UPDATE is replicated to the standby within seconds, just like legitimate changes — replication cannot tell a mistake from intended work. Standbys protect against media and site failure, not logical or human error. The defenses for human error are Flashback (Query, Table, Drop, or Database) on the primary, and, at the standby level, a deliberately delayed apply (DELAY on the apply, or a snapshot standby) that gives you a window to intervene before the mistake reaches the standby. Treat replication and point-in-time undo as complementary, not interchangeable.

Flashback is the recovery discipline’s scalpel, and it belongs next to the heavier tools, not instead of them: RMAN restore and recovery is the fallback for media loss and corruption, Data Guard protects against site and hardware failure, and the HA decision tree places each where it belongs. Human error is the failure they don’t cover — and it’s the most common one. Turn Flashback Database on while things are calm, enable row movement on the tables you’d ever want to rewind, and know which tool matches which mistake. Then prove all three recoveries end to end with the Flashback lab, so the first time you undo a disaster isn’t in production with everyone watching.

Originally published at uptimearchitect.com.

Oracle Transparent Data Encryption: Prove the Datafile Is Unreadable

Uptime Architect — Wed, 26 Aug 2026 12:23:29 +0000

"The data is encrypted at rest." Everyone says it. Almost nobody checks it. It goes in the compliance questionnaire, the auditor ticks the box, and the actual bytes on disk stay a mystery until the day a backup tape, a decommissioned disk, or a cloned VM image walks out the door and someone finds out the hard way whether it was true.

It's a checkable claim. Take a database, write a recognizable string into a table, and read the datafile off disk with grep. If the string is sitting there in plaintext, the data is not encrypted at rest, whatever the questionnaire says. If it's gone — replaced by ciphertext — it is. Transparent Data Encryption is how you get the second answer, and the whole point of this post is that you don't have to take it on faith. You can watch it happen.

But first, the part most TDE write-ups skip, and the part that decides whether it's protecting you or just making you feel protected: what it actually defends against.

What TDE protects — and what it doesn't

TDE encrypts data at rest: in the datafiles, in RMAN backups, in redo and undo and temp on disk, in Data Pump exports (with the right flag). It does this transparently — the application sees plaintext, the SQL doesn't change, and a user with the right privileges reads the data exactly as before. That transparency is the feature and the boundary:

It protects the files. A stolen datafile, a lost backup, a snapshot copied out of the storage array, a disk sent back to the vendor without being wiped — all unreadable without the key. This is the threat TDE is built for, and it's a real one: the data breach that starts with "someone got a copy of the storage" is common precisely because it bypasses every database control.
It does not protect against a logged-in user. An attacker who has valid credentials, or a SQL injection hole that runs queries as the app, sees plaintext — because the database decrypts for anyone authorized to read, which is the entire design. TDE is not access control. Stopping that is what privileges, hardening, and auditing are for.
It does not encrypt data in the buffer cache or on the wire. Blocks in the SGA are plaintext; TDE is about disk. Encrypting the connection is a separate setting (native network encryption or TLS).

Get that boundary right and TDE is one of the highest-value, lowest-friction controls you can turn on. Get it wrong — treat it as a magic "now we're secure" switch — and you've encrypted the disk while leaving the front door open.

The keystore is the whole game

TDE is a two-level key hierarchy, and understanding it is understanding TDE. The actual data is encrypted with tablespace (or column) encryption keys. Those keys live in the datafiles themselves — but they're encrypted, wrapped by a single master encryption key. The master key is the one thing that does not live with the data. It lives in a separate keystore (historically called the Oracle wallet).

The two-level hierarchy, and why the keystore is separate. The data keys travel with the datafiles; the master key that unlocks them does not. Steal the .dbf and you have ciphertext plus a locked box. You need the keystore too.

That separation is the entire security model, and it has a sharp consequence: if the keystore is stolen along with the datafiles, TDE has protected nothing. Which is exactly the mistake an auto-login keystore invites. A software keystore comes in three flavors:

Password keystore — the database can't open it without a human (or a script) supplying the password. Safest, most operationally annoying: someone has to open the wallet after every restart.
Auto-login keystore — the database opens it automatically at startup. Convenient, and how most production databases run. But the auto-login file, if it sits next to the datafiles and gets copied with them, hands the thief the key. Keep it off the data volume.
Local auto-login keystore — auto-login, but tied to the host it was created on. Copy it to another machine and it won't open. This is usually the right default: the database opens its own wallet, but a stolen copy is useless elsewhere.

For anything beyond a single box, the master key belongs in a real key manager — Oracle Key Vault or an HSM — not a file on the same server at all. The file keystore is where you start; centralized key management is where a fleet ends up.

Modern Oracle (19c and up) configures all of this through two settings — WALLET_ROOT (a static parameter, so setting it needs one restart) and TDE_CONFIGURATION:

-- one-time: tell the database where keystores live, then turn on the FILE keystore
ALTER SYSTEM SET WALLET_ROOT = '/etc/oracle/wallets/prod' SCOPE = SPFILE;  -- then restart
ALTER SYSTEM SET TDE_CONFIGURATION = 'KEYSTORE_CONFIGURATION=FILE' SCOPE = BOTH;

-- create the keystore, open it, and set the master key
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY "<strong-pw>";
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<strong-pw>" CONTAINER = ALL;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "<strong-pw>" WITH BACKUP;

That WITH BACKUP is not optional decoration. Lose the master key and the data is gone — not locked, gone, as unrecoverable as if you'd deleted it. Back the keystore up, separately from the database backups, and guard it like the crown jewel it is. More teams have lost data to a lost wallet than to a stolen one.

Tablespace encryption, not column encryption (usually)

TDE has two modes, and the choice is easier than it looks:

Tablespace encryption encrypts an entire tablespace — every table, index, and LOB in it — with AES. It's fully transparent, imposes a small and roughly fixed CPU cost, and has no functional surprises: every query, every index, every join works exactly as before. This is the default answer.
Column encryption encrypts specific columns. It sounds surgical and appealing — "just encrypt the card number" — but it comes with real limitations: an encrypted column can't be a foreign key, can't be indexed with a normal range scan, and breaks some optimizations. Reach for it only when you genuinely need one or two columns encrypted and can't encrypt the tablespace.

Creating an encrypted tablespace is one clause:

CREATE TABLESPACE app_secure
  DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/app_secure.dbf' SIZE 100M
  ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);

And you don't have to rebuild the world to encrypt what you already have. Since 12.2, you can convert an existing tablespace online, with the application still running:

ALTER TABLESPACE users ENCRYPTION ONLINE ENCRYPT;   -- no outage; encrypts in the background

New data written to an encrypted tablespace is encrypted; the online convert handles the existing blocks. There's no "half-encrypted" state a query can trip over — the transparency holds throughout.

Now prove it

Here's the part that separates "we enabled TDE" from "we checked." Put the same recognizable rows into an encrypted tablespace and an ordinary one, flush them to disk, and read the raw datafiles — not through the database, which would helpfully decrypt for you, but straight off the filesystem:

# a distinctive canary string is written into BOTH tablespaces' tables, then flushed to disk.
# read the bytes on disk directly (grep -a treats the binary datafile as text):
grep -a -c 'CANARY_TDE' /opt/oracle/oradata/FREE/FREEPDB1/tde_plain.dbf   # -> 133   (plaintext, right there)
grep -a -c 'CANARY_TDE' /opt/oracle/oradata/FREE/FREEPDB1/tde_enc.dbf     # ->   0   (ciphertext, gone)

Same rows, same fake card numbers, same everything — the only difference is one tablespace was created ENCRYPTION USING 'AES256'. In the ordinary datafile the canary is sitting there in the clear, 133 times over. In the encrypted one it's simply not there; the bytes are AES ciphertext. That's encryption at rest, demonstrated rather than asserted — and it's exactly what a thief with a copy of your storage would find.

The keystore is what stands between those two outcomes. Close it and the database itself goes blind:

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "<strong-pw>";
SELECT SUM(amount) FROM app.secrets_enc;
-- ORA-28365: wallet is not open

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<strong-pw>";
-- readable again, no data lost

With the wallet closed, the encrypted table throws ORA-28365 on any read — the master key is out of memory, so the tablespace key can't be unwrapped, so the blocks can't be decrypted. The ordinary table keeps reading fine. That's the whole model in one gesture: the key, not the file, is the thing you're protecting.

Don't take my word for it — run it. The TDE lab stands up an Oracle Database Free container, configures a software keystore, and builds one encrypted tablespace and one ordinary one holding identical canary rows. It then reads both datafiles off disk and asserts the canary appears in the plaintext file (133 hits) and is absent from the encrypted one (0 hits) — encryption at rest, proven on the actual bytes. Then it closes the keystore and asserts the encrypted read fails with ORA-28365 while the plaintext read still works, and that reopening the wallet restores access. If the canary shows up in the encrypted datafile, or closing the wallet doesn't block the read, the run fails. The whole thing is proven on every CI push.

What teams get wrong

Auto-login wallet next to the datafiles. The single most common way to make TDE pointless: the auto-login keystore gets backed up or copied alongside the data it's supposed to protect. Use a local auto-login keystore (host-tied), and keep it off the data volume.
Not backing up the keystore — or losing it. Lose the master key and the data is unrecoverable. The keystore needs its own backup, stored separately from the database backups (putting both in the same place recreates the theft problem). WITH BACKUP on every key operation, and a real archive of the wallet.
Thinking TDE stops a logged-in attacker. It doesn't. Encryption at rest is orthogonal to access control. A stolen password or a SQL-injection hole reads plaintext all day. TDE is one layer; auditing and least-privilege are the others.
Forgetting the copies. Encrypting the tablespace but not the backups, or exporting with Data Pump without ENCRYPTION, leaves plaintext copies lying around. The data is only as encrypted as its least protected copy.
Column encryption where a tablespace would do. Reaching for column encryption and then fighting its limits — no range-scan index, no foreign keys — when tablespace encryption would have been transparent and simpler. Encrypt the tablespace unless you have a specific reason not to.
Never rotating the master key. The master key can and should be rekeyed periodically (and immediately if you suspect exposure) with ADMINISTER KEY MANAGEMENT SET KEY. A key that never changes is a key with an ever-growing blast radius.

Frequently asked questions

What is Transparent Data Encryption (TDE) in Oracle?

Transparent Data Encryption is an Oracle feature that encrypts data at rest — in datafiles, backups, redo, undo, and temp on disk — without requiring any change to the application. It is called transparent because authorized users and the SQL they run see plaintext exactly as before; the encryption and decryption happen automatically at the storage layer. TDE uses a two-level key hierarchy: tablespace or column encryption keys encrypt the data and are stored (encrypted) in the datafiles, while a single master encryption key that unwraps them is kept separately in a keystore. TDE protects against theft of the physical files or backups, not against a user who is already authenticated to the database.

What does TDE protect against, and what does it not?

TDE protects against threats where someone obtains the files rather than a database session: a stolen or lost backup, a decommissioned disk, a copied storage snapshot, or a cloned VM image. In all of those the data is unreadable without the master key. TDE does not protect against an attacker who has valid credentials or exploits SQL injection, because the database decrypts transparently for anyone authorized to read — that is what access control, hardening, and auditing are for. It also does not encrypt data in the buffer cache (which is plaintext in memory) or on the network connection, which needs native network encryption or TLS configured separately.

What is the Oracle keystore (wallet) and why is it kept separate?

The keystore, historically called the Oracle wallet, is where the TDE master encryption key is stored. The tablespace and column keys that actually encrypt data live inside the datafiles, but they are themselves encrypted by the master key, and the master key lives only in the keystore. This separation is the entire security model: a stolen datafile contains ciphertext and a locked, wrapped key, but not the master key needed to open it. If the keystore is stolen together with the datafiles, TDE protects nothing, which is why an auto-login keystore should never sit on the same volume as the data and why fleets move the master key into Oracle Key Vault or an HSM.

What is the difference between an auto-login and a local auto-login keystore?

A password keystore must be opened by supplying its password, so the database cannot read encrypted data after a restart until a human or script opens the wallet. An auto-login keystore lets the database open the wallet automatically at startup, which is convenient but means anyone who copies that auto-login file can open it anywhere. A local auto-login keystore is also opened automatically, but it is tied to the host on which it was created — copied to a different machine it will not open. For most production databases a local auto-login keystore, kept off the data volume, is the right balance of convenience and safety.

Should I use tablespace encryption or column encryption?

Tablespace encryption is the default choice for almost all cases. It encrypts an entire tablespace — every table, index, and LOB — transparently, with a small and predictable CPU cost and no functional limitations: all queries, indexes, and joins behave exactly as before. Column encryption encrypts specific columns and sounds more surgical, but it carries real restrictions: an encrypted column cannot be a foreign key or be used in a normal range-scan index, and it interferes with some optimizations. Use column encryption only when you need to encrypt one or two specific columns and cannot encrypt the whole tablespace; otherwise encrypt the tablespace.

Can I encrypt an existing tablespace without downtime?

Yes. Since Oracle 12.2 you can convert an existing tablespace to encrypted online, while the application keeps running, with ALTER TABLESPACE ENCRYPTION ONLINE ENCRYPT. Oracle encrypts the existing blocks in the background and encrypts new data as it is written, and there is no half-encrypted state that queries can trip over. This means you do not have to create a new encrypted tablespace and migrate objects into it; you can encrypt the data where it already lives. You do need enough auxiliary space for the online conversion, and the master key and keystore must be set up first.

What happens if I lose the TDE keystore or master key?

If you lose the keystore and have no backup of it, the encrypted data is permanently unrecoverable — not merely locked, but effectively destroyed, because the tablespace keys inside the datafiles can never be unwrapped again. This is the most important operational risk of TDE and it causes more data loss than theft does. You must back up the keystore whenever the master key changes (the WITH BACKUP clause creates a backup at each key operation), store that keystore backup separately from the database backups so a single compromise cannot capture both, and test that you can actually open a restored keystore. Treat the keystore as the most critical artifact in the environment.

Does Oracle TDE require an extra license?

On-premises Enterprise Edition, TDE is part of the Advanced Security Option, which is a separately licensed pack, so using it on-prem EE requires that license. In Oracle Cloud Infrastructure, including Autonomous Database and the Base Database and Exadata cloud services, TDE is included and is typically enabled by default, so cloud databases are encrypted at rest out of the box. Because licensing terms change and depend on your edition and platform, confirm your specific entitlement before enabling TDE in production on-premises rather than assuming it is included.

Encryption at rest is the fourth leg of the same security-and-ops discipline as the rest: patching closes known vulnerabilities, the hardening checklist closes the configuration gaps, unified auditing tells you when either is being tested, and TDE makes sure that when the files themselves are the target, what walks out the door is unreadable. Do it deliberately: the right keystore type, kept off the data volume and backed up separately, tablespace encryption over column encryption, and the master key rotated on a schedule. Then prove it the way that ends the argument — with the TDE lab, where you can watch the canary vanish from the datafile and watch the database go blind the moment the wallet closes.

Originally published at uptimearchitect.com.

Oracle SQL Plan Management: Stop a Good Plan From Going Bad

Uptime Architect — Wed, 19 Aug 2026 19:25:20 +0000

A query that ran in fifty milliseconds for a year is suddenly taking thirty seconds. Nobody changed the code. Nobody changed the query. And that's exactly the problem: the optimizer is allowed to change its mind. New statistics, a bind variable it peeked at differently, an upgrade, a dropped index — any of them can hand the same statement a new plan, and the new plan can be worse. Most of the time you never notice. The time you notice, it's a pager at 2 a.m.

The two posts before this one were about making the optimizer's estimate right — reading the plan to find where the estimate went wrong, and fixing the statistics behind it. This one is about the plan you've already got right and want to keep. Because "get the estimate right" is a moving target: statistics get re-gathered, data shifts, and a plan that was correct on Tuesday is not guaranteed on Wednesday. SQL Plan Management is the seatbelt. It lets the optimizer keep improving plans everywhere else while refusing to let a known-good plan silently regress on the statements you can't afford to have go wrong.

What a SQL plan baseline actually is

A SQL plan baseline is a stored set of accepted execution plans for one SQL statement. It lives in the SQL Management Base in SYSAUX, and it's keyed by the statement's signature — a hash of the normalized SQL text (case- and whitespace-insensitive, but literal-sensitive, which is one more reason to use bind variables). When a statement with a baseline parses, the optimizer does something subtly different from its usual job:

It builds its best-cost plan the way it always does, from the current statistics.
Then it checks the baseline. If that best-cost plan is already accepted, it just uses it — nothing to protect against.
If the best-cost plan is not accepted, the optimizer sets it aside as a non-accepted plan (kept for later, in case it's genuinely better) and instead runs the best accepted plan that still reproduces against today's schema.
Only if no accepted plan can be reproduced — say the index it needs was dropped — does it fall back to the best-cost plan.

The effect is a ratchet. The optimizer is free to find new plans, but it can't use one you haven't blessed. A plan you captured while things were good stays in force even when the cost model, fed newer statistics, starts preferring something else.

What the optimizer does when an enabled baseline exists. It still computes its best-cost plan — it just isn't allowed to run it unless you've accepted it. A newly found plan is parked as non-accepted for later verification, not used on the spot.

The three flags that decide everything

Every plan in a baseline carries three independent states, and mixing them up is the single most common source of "SPM isn't working":

ENABLED — the plan is eligible for consideration at all. Disable it and the optimizer ignores it.
ACCEPTED — the plan is allowed to run. This is the one that matters. A captured plan can be enabled but not accepted (it's a candidate, waiting to prove itself); the optimizer will not use it until it's accepted.
FIXED — the plan is preferred. When a baseline has any fixed plans, the optimizer chooses only among those and stops adding new candidates. A fixed baseline is how you say "this, and nothing else, until I say otherwise."

The mental model: ENABLED is "in the pool," ACCEPTED is "cleared to fly," FIXED is "and it's the captain." Most of SPM is moving plans between those states deliberately instead of letting it happen to you.

Capturing the plan you want to keep

There are three ways plans get into a baseline. The one you'll reach for most is loading a plan you already have in the cursor cache — you found the good plan, now you pin it:

-- pin the plan currently in the cursor cache for one SQL_ID
DECLARE
  n NUMBER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'a1b2c3d4e5f6g');
  DBMS_OUTPUT.PUT_LINE(n || ' plan(s) loaded');
END;
/

Plans loaded this way arrive enabled and accepted — ready to enforce immediately. The other two sources:

Automatic capture. Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE and Oracle records a baseline for every repeatable statement it sees. The first plan for a statement is auto-accepted (it becomes the reference); any different plan found later is captured as non-accepted, waiting for you to verify it. Powerful, but it's a blanket — turn it on around a risky change (an upgrade, a big data load), capture what you need, then turn it back off rather than leaving it on forever.
From a SQL tuning set / AWR. DBMS_SPM.LOAD_PLANS_FROM_SQLSET pulls plans out of a tuning set, which is how you seed baselines from AWR history — including from the old release before an upgrade, so the plans that worked last quarter are on file before the new optimizer ever runs.

That last one is the killer use case: capture baselines from the current release, upgrade, and the optimizer inherits a floor. New plans still get found and parked as candidates, but nothing regresses on day one because every statement already has its old, proven plan accepted.

Confirm it's actually in force

Two checks. First, does the baseline exist and what state is it in:

SELECT sql_handle, plan_name, enabled, accepted, fixed, origin
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%shop.orders%';

Then the check that matters — that the plan the optimizer actually built came from the baseline. The plan's Note section says so outright:

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));
-- ...
-- Note
-- -----
--    - SQL plan baseline SYS_SQL_PLAN_1a2b3c4d used for this statement

If that line is there, the baseline held. If it isn't — and you expected it to — the usual reasons are OPTIMIZER_USE_SQL_PLAN_BASELINES turned off, the plan isn't accepted (only enabled), or the accepted plan can no longer be reproduced because the schema changed underneath it.

Evolving: how a baseline gets better without getting worse

A baseline that never changes eventually holds a plan that's genuinely stale — a new index really would be faster now. That's what evolution is for. DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE takes the non-accepted candidates piling up in the baseline, runs them, and accepts one only if it actually performs better than the current accepted plan by a real margin. This is the crucial difference from just letting the optimizer loose: a plan is promoted on measured performance, not on estimated cost.

-- verify the parked candidates and accept only the ones that prove faster
SELECT DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SQL_1a2b3c4d5e6f7g8h') FROM dual;

From 19c on, the automatic SPM evolve advisor task does exactly this in the maintenance window: it evaluates the candidates that accumulated during the day and auto-accepts the ones that verify faster. So the healthy steady state is: baselines protect you from regressions, and evolution quietly promotes real improvements after they've proven themselves. You get stability and progress, instead of choosing.

Don't take my word for it — run it. The SQL Plan Management lab builds a million-row ORDERS table with a rare, indexed status and a histogram, so WHERE status = 'OPEN' gets the cheap index plan. It captures that plan as an accepted baseline, then drops the histogram to simulate the everyday stats drift that breaks plans — now the optimizer estimates a third of the table and costs a full scan as cheaper. The lab runs the same query twice and asserts both outcomes: with baselines off, the plan regresses to a full table scan; with baselines on, the accepted index plan is held (it's still reproducible because the index exists) and DBMS_XPLAN reports SQL plan baseline ... used for this statement. If the regression doesn't happen, or the baseline doesn't stop it, the run fails. The whole before/after is proven on every CI push.

What teams get wrong

Confusing enabled with accepted. A captured candidate plan is enabled but not accepted, and the optimizer won't run it. People load a plan, see it in DBA_SQL_PLAN_BASELINES, and assume it's in force — then wonder why nothing changed. Check the ACCEPTED column, not just that a row exists.
Fixing a baseline and forgetting. A FIXED baseline stops accepting new candidates entirely. That's correct for a statement you never want touched — and a slow poison for one whose data grew tenfold since, because the plan can no longer improve even when it should. Fixed plans need a review date.
Leaving automatic capture on forever. Blanket capture around an upgrade is smart; leaving it on fills the SQL Management Base with baselines for one-off and ad-hoc statements and quietly grows SYSAUX. Turn it on with intent, capture, turn it off.
Baselining statements full of literals. Signatures are literal-sensitive. A statement that inlines WHERE id = 48213 has a different baseline from WHERE id = 48214. Without bind variables you're baselining a million near-identical statements, none of which recurs. Bind first, baseline second.
Treating a baseline as a fix for bad statistics. SPM freezes a plan; it doesn't make the estimate correct. If you pin a plan to paper over stale stats, you've hidden the problem, not solved it — and every other statement on that table still gets the bad estimate. Fix the number, then baseline the good plan it produces.
Capturing on the new release, after the regression. The highest-value moment to capture is before an upgrade, from the release that still works. Seed baselines from AWR on the old version and the new optimizer inherits a floor instead of a surprise.

Frequently asked questions

What is SQL Plan Management in Oracle?

SQL Plan Management (SPM) is an Oracle feature that prevents execution plans from regressing by controlling which plans the optimizer is allowed to use for a statement. It stores accepted plans as SQL plan baselines in the SQL Management Base. When a statement that has a baseline is parsed, the optimizer still computes its best-cost plan, but it will only execute a plan that has been accepted into the baseline; a newly found plan is recorded as a non-accepted candidate for later verification rather than used immediately. This lets the optimizer keep finding better plans while guaranteeing that a known-good plan cannot be silently replaced by a worse one.

What is the difference between enabled, accepted, and fixed plans in a baseline?

Enabled means the plan is eligible for the optimizer to consider at all. Accepted means the plan is allowed to actually run — this is the state that determines whether a plan is used, and a plan can be enabled but not accepted, in which case it is only a candidate. Fixed means the plan is preferred: when a baseline contains any fixed plans, the optimizer chooses only among the fixed plans and stops adding new candidates to the baseline. A plan loaded from the cursor cache arrives enabled and accepted; a plan captured automatically as an alternative arrives enabled but not accepted until it is evolved or manually accepted.

How do I capture a SQL plan baseline?

There are three main ways. First, load a plan already in the cursor cache with DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE for a specific SQL_ID — this loads the plan enabled and accepted, ready to enforce. Second, enable automatic capture by setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE, which records a baseline for every repeatable statement (the first plan is auto-accepted and later alternatives are captured as non-accepted). Third, load plans from a SQL tuning set with DBMS_SPM.LOAD_PLANS_FROM_SQLSET, which is how you seed baselines from AWR history, including from an older release before an upgrade.

How do I know if a SQL plan baseline is being used?

Run the statement and pull its plan with DBMS_XPLAN.DISPLAY_CURSOR using a format such as ALLSTATS LAST or TYPICAL. If a baseline built the plan, the Note section at the bottom reads "SQL plan baseline used for this statement." You can also query V$SQL.SQL_PLAN_BASELINE for the cursor, which holds the baseline plan name when one was applied. If you expected a baseline and neither appears, check that OPTIMIZER_USE_SQL_PLAN_BASELINES is TRUE, that the plan is accepted and not merely enabled, and that the accepted plan is still reproducible against the current schema.

What does it mean to evolve a SQL plan baseline?

Evolving a baseline means testing the non-accepted candidate plans that have accumulated for a statement and accepting one only if it actually performs better than the current accepted plan. DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE runs the candidates and promotes them based on measured performance, not estimated cost, which is what makes evolution safe. From Oracle 19c on, the automatic SPM evolve advisor task performs this verification in the maintenance window and auto-accepts candidates that prove faster, so baselines protect against regressions while still allowing genuinely better plans to be adopted after they have been verified.

Does a SQL plan baseline fix bad statistics?

No. A baseline freezes which plan a statement uses; it does not correct the optimizer cardinality estimate. If you pin a plan to work around stale or missing statistics, you have hidden that one symptom while every other statement against the same table still gets the wrong estimate. The correct order is to fix the statistics first — a histogram for a skewed column, extended statistics for correlated columns, a fresh gather where the data moved — and then capture a baseline of the good plan that results, so it is protected going forward. SPM is a stability tool, not a statistics tool.

Should I use SQL plan baselines before an Oracle upgrade?

Yes — an upgrade is one of the strongest use cases. A new optimizer version can change plans for statements that were previously fine, and some of those changes are regressions. If you capture baselines from the current release before upgrading — for example by loading plans from AWR history into a SQL tuning set and then into baselines — the upgraded optimizer inherits an accepted plan for each of those statements and cannot silently regress them on day one. New and potentially better plans are still found and parked as candidates, so you can evolve them deliberately after the upgrade rather than discovering regressions in production.

What is the difference between a SQL plan baseline and a SQL profile?

A SQL plan baseline stores one or more complete, accepted execution plans and constrains the optimizer to use an accepted plan, which makes it a plan-stability mechanism. A SQL profile does not store a plan; it stores corrective information — essentially adjustment factors for the optimizer estimates — that helps the optimizer build a better plan, but it does not lock a specific plan in place. In practice you use a SQL profile to help the optimizer estimate correctly and a baseline to guarantee a specific proven plan is used. They can coexist: a profile improves the estimate while a baseline fixes the resulting plan.

SQL Plan Management is the last stop in the same performance discipline as the rest: an AWR report points you at the expensive SQL, wait events tell you what a session is stuck on, the execution plan shows where the estimate went wrong, and statistics are usually why. Once you've done that work and the plan is right, a baseline is how you keep it right — the optimizer stays free to improve everything else, and the plan you fought for doesn't quietly unravel the next time the numbers move. Prove it end to end with the SQL Plan Management lab: watch a plan regress to a full scan when it's unprotected, and watch the baseline hold the line when it isn't.

Originally published at uptimearchitect.com.

Oracle Optimizer Statistics, Demystified

Uptime Architect — Mon, 17 Aug 2026 01:59:32 +0000

The optimizer doesn't guess. It calculates — and every number in that calculation comes from statistics you either gathered on purpose or left to rot.

That's the part the "the optimizer is dumb" stories always skip. Reading an execution plan, you find the line where the estimate parted ways with reality — the step that expected five rows and got five million, and dragged the whole plan down with it. That's not the optimizer being stupid. That's the optimizer doing correct arithmetic on a wrong number. It thought five rows because the statistics said five rows. Fix the plan and you're treating a symptom. Fix the statistics and the bad plan never gets built.

So here's the reframe: tuning statistics isn't a nightly ritual of gathering everything and hoping. Most of that is cargo cult — full re-gathers that burn a maintenance window to re-confirm numbers that hadn't moved. The actual skill is narrow. Gather the right things — a histogram where a column is skewed, an extended statistic where columns are correlated — keep them fresh where the data actually moves, and let the automatic job handle the rest instead of fighting it. Everything below is which numbers matter and how to keep them honest.

What the optimizer is actually reading

When the optimizer estimates how many rows a step returns — its cardinality — it's doing one sum: rows × selectivity. The row count and the selectivity both come from stored statistics, gathered by DBMS_STATS and kept in the data dictionary:

Table statistics — number of rows, number of blocks, average row length. This is the rows in the sum.
Column statistics — for each column: number of distinct values (NDV), low and high value, number of nulls, and density. This is where basic selectivity comes from: for col = :x, the optimizer assumes 1/NDV of the rows match.
Index statistics — height, leaf blocks, clustering factor (how well index order matches table order). This decides whether an index access is actually cheaper than a scan.
Histograms — the shape of a column's data when it isn't evenly distributed. Without one, the optimizer assumes every value is equally common. (More below.)

Get those right and the optimizer makes good choices. Feed it a stale row count, a missing histogram, or two columns it thinks are independent, and it makes a bad one — confidently, because the math checks out.

Histograms: when "1/NDV" lies

The 1/NDV assumption falls apart the moment a column is skewed — a few values far more common than the rest. If status is 99% CLOSED and 0.1% OPEN, but the optimizer only knows there are two distinct values, it estimates half the table for either one. A histogram records the real distribution so the optimizer stops assuming and starts knowing. You need one when a column is skewed and shows up in WHERE clauses — and Oracle's default METHOD_OPT of FOR ALL COLUMNS SIZE AUTO creates them only for exactly those columns, using its record of which columns get queried.

This is the single most common estimate bug, and it's the one the execution-plans lab reproduces end to end — a missing histogram turning a 500-row query into a full-table scan. So I won't relitigate it here. The second most common bug gets far less airtime and is just as destructive:

Correlated columns: the estimate that quietly collapses

Give the optimizer two predicates on the same table and, by default, it assumes the columns are independent — it multiplies their selectivities together. WHERE make = 'TOYOTA' AND model = 'CAMRY' becomes selectivity(make) × selectivity(model). That's fine when the columns really are unrelated. It's a disaster when they aren't — because every Camry is a Toyota. The model predicate already implies the make; multiplying by selectivity(make) a second time divides the estimate by the number of makes for no reason at all.

The result is a severe under-estimate — the mirror image of the skew problem. The optimizer thinks a few dozen rows match when tens of thousands do, so it reaches for an index and a nested loop that would be perfect for a few dozen rows and catastrophic for tens of thousands. You can't fix this with a histogram; neither column is individually skewed. You fix it by telling the optimizer the two columns travel together — an extended statistic on the column group:

-- create the column group AND gather it in one step
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'SALES',
    tabname    => 'CARS',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS (make, model)');
END;
/

-- confirm the extension exists
SELECT extension_name, extension
FROM   user_stat_extensions
WHERE  table_name = 'CARS';

Now the optimizer stores a real NDV for the combination of make and model, and the estimate snaps to the truth. Extended statistics also cover expressions — if you query WHERE UPPER(last_name) = 'SMITH', a statistic on (UPPER(last_name)) gives the optimizer a real selectivity for the expression instead of a blind guess. (There's an automation for this too: the preference AUTO_STAT_EXTENSIONS, off by default, lets Oracle create column groups on its own from the predicates it sees. Useful, but I like knowing exactly which extensions exist rather than discovering them.)

The estimate that rots: stale, and out of range

The other way good statistics go bad is simply time. Stats are a snapshot; the data keeps moving. Two symptoms matter most:

Stale stats on a changing table. Oracle flags a table's stats stale once roughly 10% of its rows have changed since the last gather. Past that line, the optimizer is reasoning about a table that no longer exists.
The out-of-range predicate. This one's sneakier. Gather stats today, and the optimizer's high_value for order_date is today. Query tomorrow for order_date > SYSDATE - 1 and you're asking about values past the edge of what the stats know — so the optimizer estimates almost nothing matched and under-reads. On any ever-growing table (orders, events, logs), the newest and most-queried data is exactly the data the last gather never saw.

Both are why you don't just gather once and forget. But they're also why you shouldn't gather everything nightly — most tables don't cross the staleness line most nights. Which is what the automatic job is for.

Let the automatic job do the boring part

Modern Oracle gathers statistics for you, and it's good at it. The automatic optimizer statistics task runs in the maintenance window and gathers stats for any object whose stats have gone stale — not every object, just the ones that moved. Two newer pieces make it sharper, both from 19c on:

High-frequency automatic statistics — a lightweight task that checks for stale objects every 15 minutes rather than waiting for the nightly window, so fast-moving tables don't spend all day on yesterday's numbers.
Real-time statistics — the optimizer maintains basic stats as conventional DML runs, so a table loaded this morning isn't invisible until tonight's gather. (Availability varies by edition and platform — know whether yours has it.)

Your job is mostly to not get in the way. The DBMS_STATS defaults are the product of two decades of the optimizer team's scar tissue — AUTO_SAMPLE_SIZE (a fast, accurate scan rather than a guessed sample percent) and SIZE AUTO (histograms only where they're earned). Overriding them with a hand-picked estimate_percent or a blanket SIZE 254 is how people make stats worse while feeling productive. Leave the defaults, and reach for manual gathering only in the specific cases the automation can't see:

-- a good manual gather looks like the defaults, on purpose
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SALES',
    tabname          => 'ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE);
END;
/

Two overrides that are worth knowing. Lock the stats on a volatile or staging table so the automatic job doesn't gather it mid-load and catch it half-empty — you set representative stats once and freeze them:

EXEC DBMS_STATS.LOCK_TABLE_STATS('SALES', 'STAGING_LOAD');

And when you must re-gather a critical table but can't risk a plan regression, use pending statistics — gather them, test them in your own session, and publish only if they behave:

EXEC DBMS_STATS.SET_TABLE_PREFS('SALES', 'ORDERS', 'PUBLISH', 'FALSE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SALES', 'ORDERS');   -- lands as PENDING, unused by other sessions
ALTER SESSION SET optimizer_use_pending_statistics = TRUE;  -- test the new stats, just for you
-- happy? publish. unhappy? delete them, no one else ever saw them.
EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SALES', 'ORDERS');

Is it actually your statistics?

Before you gather anything, confirm the stats are the problem. Two checks:

-- how old, how big, and does Oracle think they're stale?
SELECT table_name, num_rows, last_analyzed, stale_stats
FROM   user_tab_statistics
WHERE  table_name = 'ORDERS';

Then the same check that reading any plan comes down to: run the statement with /*+ GATHER_PLAN_STATISTICS */, pull the plan with DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'), and compare E-Rows to A-Rows. If they track, your stats are fine and the work is genuinely large — a re-gather won't help. If they diverge by an order of magnitude, you've found the estimate to fix, and this post is the menu of why it's wrong. Reading that plan is the subject of its own post (Oracle Execution Plans, Decoded); statistics are where the fix usually lands.

Same loop as reading a plan, one level deeper: find the estimate that's wrong, then match the fix to the reason it's wrong. A re-gather is one branch, not the whole tree.

Don't take my word for it — run it. The optimizer-stats lab builds a million-row table where model determines make — perfectly correlated — and joins it to a second table. It runs WHERE make = ... AND model = ..., captures the real ALLSTATS LAST plan, and asserts the under-estimate — the optimizer multiplies the selectivities, expects ~200 rows, and picks a nested loop join that's right for a couple hundred rows and a disaster for the ~10,000 that actually match. Then it creates an extended statistic on the (make, model) column group, re-gathers, re-runs, and asserts the plan flipped to a hash join with E-Rows ≈ A-Rows. (It turns adaptive plans off first, so you see what the estimate alone decides — the runtime safety net is a different lesson.) If the under-estimate doesn't reproduce, or the column group doesn't correct it, the run fails. The whole before/after is proven on every CI push.

What teams get wrong

Gathering everything, every night. A full re-gather of tables that didn't change is a maintenance window spent re-confirming yesterday. Let the automatic task gather what's stale; intervene only where it can't see.
Overriding the defaults to feel productive. A hand-picked estimate_percent or a blanket SIZE 254 usually makes stats slower to gather and worse. AUTO_SAMPLE_SIZE and SIZE AUTO are the right answer far more often than not.
Never creating extended statistics. The correlated-columns under-estimate is invisible until you know to look for it — and no amount of re-gathering the individual columns fixes it. A column group does.
Deleting histograms because "they cause plan instability." The instability is usually a bind-peeking or sampling issue, not the histogram itself. Removing a histogram a skewed column needs just trades a visible problem for a quieter one.
Gathering stats, then not testing. On a critical table, a re-gather is a plan change. Use pending statistics to try it in one session before it becomes everyone's plan.
Leaving volatile and staging tables to the automatic job. Caught mid-load, they get stats that describe a table that only exists for ten minutes a day. Lock representative stats instead.

Frequently asked questions

What are optimizer statistics in Oracle?

Optimizer statistics are stored descriptions of your data that the cost-based optimizer uses to estimate how many rows each step of a query will process and how expensive each access path is. They include table statistics (row count, block count, average row length), column statistics (number of distinct values, high and low values, nulls, density), index statistics (height, leaf blocks, clustering factor), and histograms (the distribution of values in a skewed column). The optimizer combines these into a cardinality estimate; if the statistics are wrong or stale, the estimate is wrong and the plan can be poor even though the optimizer reasoned correctly.

How often should I gather optimizer statistics in Oracle?

For most tables, let the automatic optimizer statistics task do it. It runs in the maintenance window and gathers statistics only for objects whose statistics have gone stale (roughly 10% of rows changed), rather than re-gathering everything. From 19c, high-frequency automatic statistics can check for stale objects every 15 minutes, and real-time statistics maintain basic stats during DML. Gather manually only in specific cases the automation cannot handle well: right after a bulk load, on a volatile or staging table (where you usually lock stats instead), or when you need to create extended statistics. Blanket nightly re-gathers of unchanged tables waste resources without improving plans.

What are extended statistics and when do I need them?

Extended statistics are statistics on a group of columns or on an expression, rather than a single column. You need column group statistics when two or more columns are correlated and are used together in WHERE clauses — for example make and model, or country and city. By default the optimizer assumes columns are independent and multiplies their selectivities, which badly underestimates cardinality when the columns are related. A column group gives the optimizer a real number of distinct values for the combination. Expression statistics do the same for a function such as UPPER(last_name). Create them with DBMS_STATS, for example method_opt of FOR COLUMNS (make, model).

Why does Oracle underestimate rows when I filter on two related columns?

Because by default the optimizer treats predicates on different columns as independent and multiplies their selectivities. If you filter on make = TOYOTA and model = CAMRY, it multiplies the selectivity of make by the selectivity of model — but every Camry is already a Toyota, so the make predicate adds no additional filtering. Multiplying by it anyway divides the estimate by the number of makes for no reason, producing a large underestimate. The optimizer then picks a plan suited to far fewer rows than actually match, such as an index range scan and nested loop where a full scan and hash join were correct. Extended statistics on the column group fix the estimate.

What is the difference between a histogram and extended statistics?

A histogram describes the distribution of values within a single column, so the optimizer knows that some values are far more common than others rather than assuming an even spread. You need one when a single column is skewed and appears in predicates. Extended statistics describe a relationship the optimizer cannot see from single-column stats: either a correlation between multiple columns (a column group) or the selectivity of an expression. They solve different problems — a histogram fixes a per-value skew underestimate or overestimate on one column, while a column group fixes the underestimate caused by treating correlated columns as independent. Some queries need both.

Should I change the DBMS_STATS default parameters?

Usually not. The defaults — AUTO_SAMPLE_SIZE for the sample size and FOR ALL COLUMNS SIZE AUTO for method_opt — are the result of extensive tuning by the optimizer development team. AUTO_SAMPLE_SIZE gives near-full-scan accuracy at a fraction of the cost, and SIZE AUTO creates histograms only for columns that are skewed and actually used in predicates. Overriding them with a fixed estimate_percent or a blanket histogram size such as SIZE 254 typically makes gathering slower and the resulting statistics less accurate. Change preferences deliberately and per-table when you have a specific reason, not as a global habit.

What are pending statistics and how do I use them?

Pending statistics let you gather new statistics without immediately exposing them to the optimizer, so you can test them before they affect everyone. Set the table preference PUBLISH to FALSE, gather statistics (they are stored as pending, and other sessions keep using the old published stats), then in your own session set optimizer_use_pending_statistics to TRUE and run your critical queries to check the plans. If the new statistics produce good plans, publish them with DBMS_STATS.PUBLISH_PENDING_STATS; if not, delete them and no other session was ever affected. This is the safe way to re-gather statistics on an important table where a plan regression would be costly.

Why should I lock statistics on some tables?

You lock statistics on tables whose contents change so dramatically and so often that any automatically gathered snapshot is misleading — typically staging, load, or global temporary tables that are empty for most of the day and full for a few minutes. If the automatic statistics job happens to run while such a table is empty or half-loaded, the optimizer gets statistics describing a state the table is almost never in, and plans that reference it go wrong. Instead you gather representative statistics once, when the table holds a typical working set, and lock them with DBMS_STATS.LOCK_TABLE_STATS so the automatic job leaves them alone.

Statistics are the layer underneath every plan the optimizer builds, which is why they sit at the root of the same performance discipline as the rest: an AWR report points you at the expensive SQL, wait events tell you what a session is stuck on, the execution plan shows you where the estimate went wrong, and statistics are usually why. Don't gather more — gather what's earned: a histogram for skew, a column group for correlation, fresh numbers where the data actually moves, and the automatic job for everything else. Prove the correlated-columns fix end to end with the optimizer-stats lab, and the next time a plan goes wrong, you'll know whether to read the plan or fix the number behind it. And once the number is right and the plan is good, SQL Plan Management is how you keep it that way — pinning the plan so the next re-gather can't quietly undo the fix.

Originally published at uptimearchitect.com.

Oracle Execution Plans, Decoded: The One Number That Matters

Uptime Architect — Wed, 12 Aug 2026 16:16:38 +0000

EXPLAIN PLAN is a liar. Not on purpose — it just tells you what the optimizer hopes will happen, never what did. It's a forecast, printed with the confidence of a receipt.

And a plan is nothing but forecasts stacked on forecasts. The optimizer guesses how many rows each step will produce, and every choice after that — which table to lead with, whether to use an index, nested loop or hash join — rests on the guess before it. Get the first estimate wrong and the whole plan tips over: it thought a step would return five rows, it returned five million, and it picked a strategy that's a catastrophe at that scale. That's what a slow query almost always is. Not a mysterious optimizer mood — one estimate that missed, and a plan that trusted it.

So here's the part that changes how you tune: reading the estimates is nearly useless, because the estimate is exactly the thing that's wrong. The skill is reading the estimate against reality — putting the number the optimizer guessed next to the number it actually got, and finding the line where they diverge. Do that and the slow query stops being a mystery. Everything below is how.

Stop reading the guess. Get the real plan.

EXPLAIN PLAN, AUTOTRACE, the plan tab in your IDE — they all show you the estimate without ever running the statement. Worse, the plan they show you isn't guaranteed to be the plan that runs: bind variables get peeked, the real cursor may have been built for a different value, and EXPLAIN PLAN doesn't peek at all. You can spend an afternoon tuning a plan the database never actually uses.

Run the statement and ask the database what it actually did. Two pieces: the GATHER_PLAN_STATISTICS hint (or STATISTICS_LEVEL = ALL for the session), which tells Oracle to count real rows as it goes, and DBMS_XPLAN.DISPLAY_CURSOR with the ALLSTATS LAST format, which prints those counts next to the estimates for the statement you just ran:

SELECT /*+ GATHER_PLAN_STATISTICS */ SUM(amount)
FROM   shop.orders
WHERE  status = 'OPEN';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));

That gives you a plan with columns the estimate-only tools can't:

E-Rows — rows the optimizer estimated a step would return.
A-Rows — rows it actually returned.
Starts — how many times the step ran (crucial for nested loops).
Buffers — logical I/O, the truest measure of work done (more on this below).

This is the difference between a weather forecast and looking out the window. Everything that follows is reading that window.

The one number: E-Rows vs A-Rows

If you learn to read exactly one thing in a plan, read this. Go down the plan and compare E-Rows to A-Rows on every line. Where they track each other, the optimizer understood the data. Where they diverge by an order of magnitude or more, you've found the lie — and it's almost always the root of the slowness, because every operation above that line was planned for the wrong number of rows.

------------------------------------------------------------------------------------
| Id | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.02 |    8616 |
|  1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.02 |    8616 |
|* 2 |   TABLE ACCESS FULL| ORDERS |      1 |    500K|    500 |00:00:00.02 |    8616 |
------------------------------------------------------------------------------------

Line 2 is the whole story. The optimizer estimated 500K rows for status = 'OPEN' and got 500 — a thousandfold overshoot. Believing half the table matched, it did the sensible thing for that belief and full-scanned. But only 500 rows matched, so it read over eight thousand buffers to find a few hundred rows an index would have fetched in ten. The full scan isn't the bug. The 500K estimate is the bug; the full scan is just what a rational optimizer does when you feed it a bad number.

For nested loops, apply the same idea with Starts: the inner step's real output is Starts × A-Rows. A step showing Starts = 50000 is an inner probe that ran fifty thousand times because the optimizer thought the outer row source would return a handful. Same disease — a low estimate on the driver — different symptom.

Read it inside-out

Plans read like nested parentheses, not top to bottom. The most-indented line runs first; a parent consumes what its children produce. To find where a plan goes wrong, start at the leaves (the table and index accesses), walk outward, and watch two columns: the A-Rows that balloon and the Buffers that accumulate. The line where the row count first explodes past its estimate is your driving row source — the step that set the plan's fate. You don't need to understand all forty lines of a hairy plan. You need the one where reality parted ways with the forecast.

When a full scan is right — and when it's a symptom

A full table scan is not a failure. Reading 40% of a table, it's faster than an index — an index would mean 400,000 scattered single-block reads where a scan does clean multi-block ones. The optimizer switches between index access and full scan on selectivity: how much of the table your predicate keeps. Below roughly a few percent, the index wins; above it, the scan does.

Which means a full scan is only a symptom when the optimizer reached for it because it misjudged selectivity — the exact case above, where a missing histogram made a 0.05%-selective predicate look 50%-selective. So don't reflexively "add an index" or paste in an INDEX hint. A hint that forces the index papers over the wrong estimate and leaves a landmine for the next value. Fix the number, and the optimizer picks the index on its own — for every value, not just the one you tested.

While you're on that line, read its predicate section (DISPLAY_CURSOR prints it under the plan). It splits into access predicates — what an index used to find rows — and filter predicates — what got applied after, throwing rows away. A fat filter where you expected an access predicate means the index isn't doing the work you think it is: rows are being read and then discarded, not skipped.

Buffers, not seconds

The instinct is to chase A-Time. Resist it. Elapsed time lies — it drops when data's cached, spikes when the box is busy, and changes every run. Buffers — logical I/O, the count of buffer accesses — is the work the query actually asked for, and it's stable across warm cache, cold cache, and a loaded server. When you tune, watch Buffers fall. A rewrite that "feels faster" but moves the same Buffers just cached the blocks; a rewrite that cuts Buffers 100× is genuinely less work, and it'll still be less work at 2am under load. Time is the thing users feel; Buffers is the thing you can trust while you're fixing it.

Why the estimate was wrong

Once E-Rows and A-Rows point you at the guilty line, there's one question left: why did the optimizer believe that? Almost always one of these:

Stale or missing statistics. The optimizer is reasoning about a table that no longer exists — last week's row counts, yesterday's high value. Re-gather with DBMS_STATS and the estimate often just fixes itself.
Column skew with no histogram. The classic. The optimizer assumes values are spread evenly across a column, so for status = 'OPEN' it divides row count by the number of distinct values. If OPEN is 0.05% of the table but there are two distinct values, it estimates 50%. A histogram on the column tells it the truth about the distribution. (This is the worked example below.)
Correlated columns. Two predicates the optimizer treats as independent when they aren't — WHERE make = 'Toyota' AND model = 'Camry'. It multiplies the two selectivities and lands far too low, because every Camry is a Toyota. Extended statistics on the column group teach it the correlation.
A function on the column. WHERE UPPER(name) = 'ACME' or an implicit type conversion (a number column compared to a string) makes the column unsargable — the index can't be used and the estimate falls apart. Rewrite to leave the column bare, or add a function-based index.
Bind peeking meets skew. With bind variables, the optimizer peeks at the first value and builds a plan for it — great until the next value has wildly different selectivity and inherits a plan built for someone else. Adaptive cursor sharing exists to catch this; skew plus binds is where it earns its keep.
A predicate past the edge of the stats. Query for a date newer than the newest value the stats know about and the optimizer estimates almost nothing matched — so it under-reads. Common on ever-growing tables between stats gathers.

Modern Oracle fights back on your behalf: adaptive plans (12c and up) let the optimizer switch join methods mid-flight when the real row counts contradict the estimate, and Real-Time SQL Plan Management in 23ai spots a plan regression as it happens and reinstates a known-good plan automatically. Both are real, and both are worth having on. But they're a safety net under the trapeze, not a reason to skip the routine: they catch some falls, they don't make your statistics tell the truth. The durable fix is still to correct the number the optimizer started from.

Watch it happen

Take the plan from earlier and give the optimizer what it was missing. Before, with no histogram on status, it split the table evenly between two values and estimated 500K rows for OPEN — so it full-scanned. Gather a histogram:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'SHOP',
    tabname    => 'ORDERS',
    method_opt => 'FOR COLUMNS SIZE 254 STATUS');  -- build the histogram it was missing
END;
/

Run the same query again, pull the same ALLSTATS LAST plan, and the guilty line has changed its mind:

--------------------------------------------------------------------------------------------------
| Id | Operation                            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |               |      1 |        |      1 |00:00:00.01 |      10 |
|  1 |  SORT AGGREGATE                      |               |      1 |      1 |      1 |00:00:00.01 |      10 |
|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS        |      1 |    500 |    500 |00:00:00.01 |      10 |
|* 3 |    INDEX RANGE SCAN                  | ORD_STATUS_IX |      1 |    500 |    500 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------

Now E-Rows is 500 and A-Rows is 500. The estimate matches reality, so the optimizer reaches for the index on its own — no hint, no forcing — and Buffers collapse from 8,616 to 10. (The elapsed times barely move, because a million cached rows scan fast — which is exactly why you read Buffers, not the clock. On a table too big to sit in memory, that Buffers gap is the difference between milliseconds and minutes.) You didn't tune the query. You corrected the one number it was wrong about, and the right plan fell out.

One loop, not a hundred knobs: get the real plan, find the line where estimate and reality part ways, fix the reason the estimate was wrong — not the symptom the optimizer picked because of it.

Don't take my word for it — run it. The execution-plans lab stands up an Oracle Database Free container, builds a million-row table where OPEN is deliberately rare, and gathers stats without a histogram. It runs the query, captures the real ALLSTATS LAST plan, and asserts the misestimate is there — a full scan with E-Rows a thousandfold over A-Rows. Then it gathers the histogram, re-runs, and asserts the plan flipped to an index range scan with E-Rows ≈ A-Rows. If the misestimate doesn't reproduce, or the fix doesn't correct it, the run fails. The whole before/after is proven on every CI push, not asserted in prose.

What teams get wrong

Tuning the estimate-only plan. EXPLAIN PLAN shows a guess and sometimes not even the guess that runs. Tune the plan the database actually executed — ALLSTATS LAST — or you're tuning fiction.
Reading E-Rows and stopping. The estimate is the suspect, not the evidence. Its value is only in the gap between it and A-Rows.
Forcing an index with a hint. A hint fixes today's value and hides the real defect — a broken estimate — until the next value walks into the same trap. Fix the stats; let the optimizer choose.
Chasing seconds. Elapsed time moves with cache and load. Chase Buffers; that's the work that doesn't lie between runs.
Blaming the optimizer. It's not moody. Given honest numbers it makes good choices; given a skewed column with no histogram it makes a bad one for a good reason. Feed it the truth.
Gathering stats blindly and hoping. A plain re-gather fixes stale numbers but not skew, not correlation, not a function on a column. Read why the estimate was wrong, then pick the matching fix.

Frequently asked questions

What is the difference between EXPLAIN PLAN and DBMS_XPLAN.DISPLAY_CURSOR?

EXPLAIN PLAN produces the optimizer's estimated plan without executing the statement, and because it does not peek at bind variables it can even show a different plan than the one that actually runs. DBMS_XPLAN.DISPLAY_CURSOR shows the plan for a cursor that really executed, and with the ALLSTATS LAST format it prints actual row counts (A-Rows), start counts, and logical I/O (Buffers) next to the optimizer's estimates (E-Rows). For tuning you want DISPLAY_CURSOR with real execution statistics, because the estimates alone are exactly what tends to be wrong.

How do I get actual row counts (A-Rows) in an Oracle execution plan?

Either add the /*+ GATHER_PLAN_STATISTICS */ hint to the query, or set STATISTICS_LEVEL = ALL for the session, so Oracle counts real rows as the statement runs. Then display the plan with SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST')). The output adds A-Rows (actual rows per step), Starts (how many times each step ran), and Buffers (logical I/O) alongside the estimated E-Rows, so you can compare what the optimizer predicted against what actually happened.

What does it mean when E-Rows and A-Rows are very different?

It means the optimizer's cardinality estimate for that step was wrong, and that is usually the root cause of a bad plan. The optimizer chooses join methods, join order, and access paths based on how many rows it expects each step to produce; if it expects five rows and gets five million (or the reverse), every decision above that line was made for the wrong scale. Find the plan line where E-Rows and A-Rows diverge by an order of magnitude or more, and you have found the estimate to fix.

Why is Oracle choosing a full table scan instead of my index?

Usually because it estimates your predicate matches a large fraction of the table, which would make a full scan genuinely faster than many single-block index reads. If the estimate is correct, the full scan is the right choice. If the estimate is wrong — often because a skewed column has no histogram, so the optimizer assumes even distribution — fix the estimate rather than forcing the index with a hint. A histogram, fresh statistics, or extended statistics will let the optimizer pick the index on its own for every value, not just the one you tested.

What are E-Rows, A-Rows, Starts, and Buffers in DISPLAY_CURSOR output?

E-Rows is the estimated number of rows the optimizer expected a step to return. A-Rows is the actual number it returned at run time. Starts is how many times that step executed — important for nested loops, where the inner step runs once per outer row, so its true output is Starts multiplied by A-Rows. Buffers is logical I/O, the number of buffer accesses the step performed, which is the most reliable measure of work because it does not vary with caching or server load the way elapsed time does.

Should I use a hint to fix a bad execution plan?

Usually not as the first move. A hint that forces an index or a join method fixes the specific case in front of you but masks the underlying cause — typically a wrong cardinality estimate — and can produce a worse plan for a different bind value or as the data grows. Diagnose why the estimate is wrong (stale statistics, missing histogram, correlated columns, a function on a column) and correct that. Then the optimizer makes the right choice for all values. Hints are a last resort or a temporary stabilizer, not the fix.

What is a histogram and when do I need one?

A histogram is a statistic that describes how values are distributed within a column, rather than assuming they are spread evenly. You need one when a column is skewed — a few values appear far more or far less often than the rest — and it is used in WHERE-clause predicates. Without a histogram the optimizer divides the row count by the number of distinct values, which badly misestimates selectivity for rare or dominant values. Gather one with DBMS_STATS.GATHER_TABLE_STATS using METHOD_OPT such as FOR COLUMNS SIZE 254 column_name.

Do adaptive plans and Real-Time SPM mean I can stop tuning statistics?

No. Adaptive plans (from 12c) can switch join methods at run time when actual row counts contradict the estimate, and Real-Time SQL Plan Management in 23ai can detect a plan regression as it happens and automatically reinstate a known-good plan. Both reduce the blast radius of a bad estimate, but neither makes your statistics accurate — they react to problems rather than prevent them. Correct statistics still produce better first plans across the whole workload; the adaptive features are a safety net beneath that, not a replacement for it.

Reading a plan is the third skill in the same performance discipline as the other two: an AWR report tells you which SQL is expensive across the whole database, wait events tell you what a session is stuck waiting on, and the execution plan tells you why a single statement is doing too much work. Start at the top, narrow to the statement, then open the plan and find the one line where the estimate and reality part ways. Prove the whole loop end to end with the execution-plans lab — a misestimate you can watch reproduce, and a histogram you can watch fix it.

Originally published at uptimearchitect.com.

Oracle Unified Auditing Without the Noise

Uptime Architect — Sun, 09 Aug 2026 21:15:15 +0000

Most Oracle audit trails are useless, and they fail in one of two ways. Either nobody ever enabled anything, so when a breach investigation asks "who altered that account," the answer is a shrug. Or someone once turned on everything — every statement, every user, every object — and the trail became a firehose that filled SYSAUX, slowed the database, and got quietly ignored until it was disabled again. Neither of those is auditing. One is blindness; the other is noise you can't read.

The skill isn't auditing more. It's auditing the few things that actually matter — privilege abuse, credential attacks, changes to your security configuration — in a trail you can trust and actually read. Oracle's Unified Auditing makes that easy, and there's a deadline attached: as of Oracle Database 23ai, traditional auditing is desupported. If your process still leans on AUDIT ... writing to AUD$, it has an expiry date. Here's how to do auditing right, and what changes underneath you as you move to 23ai.

The short version. Unified Auditing (the model since 12c) writes every audit record to a single protected, read-only internal table owned by AUDSYS, and you turn it on through policies, not statement by statement. Keep the baseline Oracle pre-enables — ORA_SECURECONFIG (privileged and structural changes) and ORA_LOGON_FAILURES — then add a few narrow policies of your own, not a firehose. 19c ships in mixed mode (traditional AUD$ and unified both active); move to pure unified auditing by relinking the binary (uniaud_on) and restarting. In 23ai, traditional auditing is gone — unified is all there is. Read the trail in UNIFIED_AUDIT_TRAIL; keep it from growing forever with DBMS_AUDIT_MGMT.

What Unified Auditing actually changed

Before 12c, auditing was a scatter of destinations — AUD$ in the database, FGA_LOG$ for fine-grained policies, OS files, XML files, the SYS operations audit — each configured differently and each with its own gaps. Unified Auditing collapses all of it into one trail and one way to configure it. Three properties make it worth adopting on purpose:

A single, protected trail. Every record lands in a read-only internal table in the AUDSYS schema. You cannot INSERT, UPDATE, or DELETE it with SQL — not even as SYS. An attacker who compromises a privileged account can't quietly scrub their tracks the way they could when audit rows lived in a normal table. You read it through the UNIFIED_AUDIT_TRAIL view.
Policy-based, not statement-by-statement. You define audit policies — named bundles of actions, privileges, or roles to watch — and enable them, optionally scoped to specific users. It's declarative and far easier to review than a pile of individual AUDIT statements.
Better performance, by default. Unified records are written in queued (asynchronous) mode: they buffer in the SGA and flush to disk in batches, rather than forcing a write on every audited action. (That buffering has one practical consequence for the impatient — see "reading the trail" below.)

The baseline is already on — don't turn it off

Here's the part that surprises people: a modern Oracle database is already auditing the right security-critical actions, because Oracle pre-enables a sensible baseline. Two policies do most of the work:

ORA_SECURECONFIG — audits the actions that change your security posture: CREATE/ALTER/DROP USER, GRANT, REVOKE, ALTER SYSTEM, CREATE/DROP of directories and database links, and more. This is enabled by default from 12.2 onward.
ORA_LOGON_FAILURES — audits failed logons, which is exactly the fingerprint of a credential attack or a misconfigured application hammering the listener.

Check what's actually enabled right now — the first query every audit review should start with:

SELECT policy_name, enabled_option, entity_name
FROM   audit_unified_enabled_policies
ORDER  BY policy_name;

If ORA_SECURECONFIG isn't in that list, someone disabled it, and that is your finding — a database that isn't recording who changes its own security is a database you can't investigate. The baseline is the floor, not the ceiling, but it is a floor most estates already have for free. Don't rip it out in the name of "reducing overhead"; the overhead is negligible and the coverage is exactly the high-value set.

Audit the few things that are specific to you

On top of the baseline, add narrow policies for the things that matter in your database — a sensitive table, a powerful privilege, a specific service account. This is where discipline pays: every policy you add is a policy someone has to read, so add few and make them sharp.

-- Watch reads of one sensitive table (and who did them)
CREATE AUDIT POLICY aud_salary_access
  ACTIONS SELECT, UPDATE, DELETE ON hr.employee_salary;
AUDIT POLICY aud_salary_access;

-- Watch any use of a skeleton-key system privilege
CREATE AUDIT POLICY aud_any_table
  PRIVILEGES SELECT ANY TABLE, DELETE ANY TABLE;
AUDIT POLICY aud_any_table;

Policies can be scoped and conditioned so they capture signal, not volume: AUDIT POLICY aud_x BY app_user limits it to one account, ... WHENEVER NOT SUCCESSFUL records only the failed attempts (often the interesting ones), and an audit_condition on a policy lets you fire only when a column or context matches. The instinct to "audit all DML on everything" is the firehose again — resist it. A handful of targeted policies plus the baseline is a trail a human will actually read during an incident.

Mixed mode, pure mode, and the 23ai cliff

Whether unified auditing is the only audit system running depends on your release, and this trips people up during upgrades.

12c–19c ship in mixed mode. Both the old traditional auditing (AUDIT → AUD$) and unified auditing are active. Mixed mode exists so you can adopt unified auditing gradually without breaking legacy scripts. It also means your audit data is split across two systems — the thing unified auditing was supposed to end.
Pure unified auditing turns traditional auditing off entirely. Moving to it isn't a SET — you relink the Oracle binary with unified auditing on and restart the instance:

-- database down, then from $ORACLE_HOME/rdbms/lib:
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
-- restart; it's reversible with uniaud_off if you must go back

23ai removes the choice. As of Oracle Database 23ai, traditional auditing is desupported. A freshly created 23ai database has no traditional audit at all — unified auditing is the audit system. On an upgrade, any traditional audit settings you were still using are carried forward for compatibility, but the direction is one-way: new work goes to unified, and the old model is on its way out. The practical takeaway — if your compliance runbook still says "AUDIT this, check AUD$," it needs rewriting before you reach 23ai, not after.

Getting Unified Auditing right is three decisions, not a hundred: keep the baseline, add a few sharp policies, and know which audit mode your release actually runs.

Prove your policies actually capture what you think. The unified-auditing lab stands up an Oracle Database Free container, enables the baseline plus a custom policy on a sensitive table, then triggers the real events — a failed login, a GRANT, a read of the protected table — flushes the audit buffer, and queries UNIFIED_AUDIT_TRAIL to assert every one was recorded. If a policy silently isn't capturing, the run fails. It's the difference between "we have auditing" and "we checked."

Reading and keeping the trail

A trail you never read is theater. Reading unified auditing is one view — UNIFIED_AUDIT_TRAIL — filtered by what you care about:

-- failed logons in the last day (credential-attack fingerprint)
SELECT event_timestamp, dbusername, userhost, return_code
FROM   unified_audit_trail
WHERE  action_name = 'LOGON' AND return_code <> 0
AND    event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER  BY event_timestamp;

Two operational details keep this working. First, because records are written in queued mode, the newest ones may still be in the SGA buffer, not yet in the view — force them out with DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL before you trust "nothing's there" (the lab does exactly this before it checks). Second, the trail grows forever unless you manage it: set a retention window and a scheduled purge with DBMS_AUDIT_MGMT so the audit table doesn't quietly consume your tablespace.

-- keep 180 days, then schedule automatic cleanup
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    last_archive_time => SYSTIMESTAMP - INTERVAL '180' DAY);
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    audit_trail_type       => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_purge_interval => 24,
    audit_trail_purge_name => 'unified_purge',
    use_last_arch_timestamp => TRUE);
END;
/

What teams get wrong

Auditing everything. "Audit all actions on all objects" is the firehose that gets muted and then disabled. Enable the baseline, add a few sharp policies, and review them. Coverage you never read is worse than none — it's cost with no benefit.
Turning off the baseline "for performance." ORA_SECURECONFIG is cheap and its coverage is exactly the security-critical actions. Disabling it is how a database loses the ability to explain who changed its own configuration.
Assuming traditional auditing still works forever. It's desupported in 23ai. A runbook built on AUDIT statements and AUD$ queries breaks on the upgrade you haven't scheduled yet.
Living in mixed mode by accident. On 19c, "we use unified auditing" is often only half true — the old system is still running alongside it, splitting your trail. Decide to go pure, and relink.
Forgetting to purge. The unified trail grows without bound. No retention policy means an audit table that eventually pressures SYSAUX/its tablespace — set a DBMS_AUDIT_MGMT purge job on day one.
Not flushing before you conclude "nothing happened." Queued-mode records lag. Flush the buffer before you trust an empty result during an investigation.

The one-paragraph version

Auditing isn't about volume, it's about signal. Use Unified Auditing — one protected, read-only trail in AUDSYS, configured by policies. Keep the baseline Oracle already enabled (ORA_SECURECONFIG + ORA_LOGON_FAILURES) and add a few narrow policies for your sensitive tables and powerful privileges — never a firehose. Know your mode: 19c runs mixed (traditional and unified together), you move to pure by relinking uniaud_on and restarting, and 23ai desupports traditional auditing outright, so rewrite any AUD$-based runbook before you get there. Read it through UNIFIED_AUDIT_TRAIL (flush the buffer first), and keep it bounded with a DBMS_AUDIT_MGMT purge job. A handful of policies you actually read beats a trail of everything you don't.

Frequently asked questions

What is the difference between traditional and Unified Auditing in Oracle?

Traditional auditing (the AUDIT command writing to SYS.AUD$, plus separate destinations for fine-grained auditing, OS files, and SYS operations) is configured statement by statement and scattered across multiple trails. Unified Auditing, the model since Oracle 12c, consolidates everything into a single protected, read-only internal table in the AUDSYS schema and is configured through named policies you enable or disable as a unit. Unified Auditing is easier to manage, harder to tamper with, and higher performance because records are written asynchronously. As of Oracle Database 23ai, traditional auditing is desupported.

Is Unified Auditing enabled by default in Oracle?

Partly. Unified Auditing as a system is available in every 12c and later database, and Oracle pre-enables a baseline of predefined policies — most importantly ORA_SECURECONFIG (security-relevant actions such as CREATE/ALTER USER, GRANT, and ALTER SYSTEM) from 12.2 onward, and ORA_LOGON_FAILURES for failed logons. However, in 12c through 19c the database runs in mixed mode, meaning traditional auditing is still active alongside unified auditing. Running pure unified auditing (unified only) requires relinking the Oracle binary and restarting.

How do I enable pure Unified Auditing?

Pure Unified Auditing is enabled by relinking the Oracle executable with unified auditing turned on, not by a parameter. Shut the database down, then from $ORACLE_HOME/rdbms/lib run: make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME, and restart the instance. After that, traditional auditing is disabled and only unified auditing records are produced. The change is reversible by relinking with uniaud_off. In Oracle Database 23ai this is moot because traditional auditing is desupported.

Where are Unified Audit records stored and can they be modified?

Unified audit records are stored in a read-only internal table owned by the AUDSYS schema, and you read them through the UNIFIED_AUDIT_TRAIL view. They cannot be changed with ordinary SQL — INSERT, UPDATE, and DELETE against the trail are not permitted, even for SYS. Records are managed only through the DBMS_AUDIT_MGMT package, which is used to archive and purge old records. This protection is a core reason to prefer unified auditing: a compromised privileged account cannot silently erase its own audit trail.

Why do my new audit records not appear in UNIFIED_AUDIT_TRAIL immediately?

Because unified audit records are written in queued (asynchronous) mode by default: they buffer in the SGA and are flushed to the audit table in batches for performance, so the most recent actions can lag before they appear in the view. To force them out immediately — for example during an investigation before concluding that nothing was recorded — run DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL, then re-query. This buffering is normal and is the tradeoff that keeps auditing cheap.

What audit policies should I actually enable?

Keep the baseline Oracle already enables — ORA_SECURECONFIG for security-configuration changes and ORA_LOGON_FAILURES for failed logons — and add a small number of narrow policies for what matters in your database: access to a specific sensitive table, use of powerful ANY privileges, or activity by a particular service account. Scope and condition them (BY user, WHENEVER NOT SUCCESSFUL, or an audit_condition) so they capture signal rather than volume. Auditing everything produces a trail nobody reads; a handful of sharp policies plus the baseline is what actually helps in an incident.

How do I stop the unified audit trail from growing forever?

Use the DBMS_AUDIT_MGMT package to set a retention window and a scheduled purge for the unified audit trail. Set a last-archive timestamp with DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP and create a recurring cleanup with DBMS_AUDIT_MGMT.CREATE_PURGE_JOB for audit_trail_type AUDIT_TRAIL_UNIFIED. Without a purge policy the audit table grows without bound and eventually pressures its tablespace. Decide a retention period that satisfies your compliance requirement and automate the cleanup from the start.

Is traditional auditing really gone in Oracle 23ai?

Traditional auditing is desupported in Oracle Database 23ai. A newly created 23ai database has no traditional auditing; unified auditing is the audit system. If you upgrade a database that still had traditional audit settings, those settings are carried forward for compatibility, but new auditing work uses unified auditing and the traditional model should not be relied on going forward. Any process, script, or compliance runbook built on the AUDIT command and the AUD$ table should be migrated to unified auditing before adopting 23ai.

Auditing is the third leg of the security-and-ops discipline, alongside patching and hardening — patching closes known vulnerabilities, hardening closes the configuration gaps, and auditing is how you know when either one is being tested. Do it the same way you do the other two: not exhaustively, but deliberately — the few high-value policies, enabled, purged, and actually read. Prove yours capture what you think with the unified-auditing lab, and put the review on the same cadence as your quarterly patch.

Originally published at uptimearchitect.com.

The Oracle Hardening Checklist That Actually Matters

Uptime Architect — Sat, 01 Aug 2026 22:15:45 +0000

Open any Oracle security benchmark and you'll find two hundred–plus line items, each with a rationale, a check, and a remediation. It is thorough, it is auditable, and it is where most hardening projects go to die — three weeks of setting obscure parameters that shave a rounding error off your risk while the two gaps that actually get databases breached sit untouched because they weren't near the top of the alphabet.

Hardening is not a completeness exercise. A short list of controls stops the overwhelming majority of real incidents: accounts that still have their default password, privileges handed out for convenience, a listener open to the world, unencrypted data at rest, and no audit trail to notice any of it. Here's that list — in priority order, each with why it matters, how it actually gets exploited, and the one query or command that tells you where you stand.

The short version. Do these six, roughly in order. (1) Find and close accounts with default passwords — DBA_USERS_WITH_DEFPWD names them for you. (2) Take back what you granted for convenience: EXECUTE on the network packages (UTL_HTTP, UTL_TCP, UTL_SMTP) from PUBLIC, any %ANY% system privilege, and DBA on application accounts. (3) Lock the listener — valid-node checking, admin restrictions, and never on the public internet. (4) Turn on TDE so a stolen datafile or backup is useless. (5) Give the DEFAULT profile a failed-login lockout and a password verify function. (6) Enable Unified Auditing for the few things worth watching (ORA_SECURECONFIG + ORA_LOGON_FAILURES), not everything. Everything past this is refinement.

1. Kill the default passwords

This is number one because it is the single most reliably exploited weakness in an Oracle estate, and it is free to fix. Every attacker's first move against an Oracle listener is to try known account/password pairs — SYSTEM/manager, DBSNMP/dbsnmp, SCOTT/tiger, an app account whose password equals its username. It requires no exploit, no CVE, nothing but a dictionary.

You do not have to guess which of your accounts are exposed, because Oracle ships a view that checks the password hashes against its own list of known defaults:

SELECT username, account_status FROM dba_users_with_defpwd ORDER BY username;

Every row is an account an attacker can walk into. For each one: if you don't need it, lock and expire it; if you do, give it a real password. Sample schemas (HR, OE, SCOTT) shouldn't be in a production database at all — they're not installed by default anymore, so if they're present, someone added them.

ALTER USER scott  ACCOUNT LOCK PASSWORD EXPIRE;   -- don't need it → close it
ALTER USER dbsnmp IDENTIFIED BY "<a real secret>"; -- need it → stop using the default

While you're here, look at what's simply open and unused: SELECT username FROM dba_users WHERE account_status = 'OPEN' and lock every account no human or application actually logs in as. An account that can't authenticate can't be the way in.

2. Least privilege: PUBLIC, the ANY privileges, and DBA-for-convenience

Almost every over-privilege problem in Oracle traces back to a grant someone made to stop being asked about it. Three of them do most of the damage.

PUBLIC means every user, including the one an attacker just compromised. Historically Oracle granted EXECUTE on the network and file packages — UTL_HTTP, UTL_TCP, UTL_SMTP, UTL_INADDR, UTL_FILE — to PUBLIC. Those packages let PL/SQL open outbound network connections and read files; in the hands of a low-privilege account that's been popped, they're an exfiltration and lateral-movement kit. Find what PUBLIC can execute that it shouldn't:

SELECT table_name, privilege
FROM   dba_tab_privs
WHERE  grantee = 'PUBLIC'
AND    table_name IN ('UTL_HTTP','UTL_TCP','UTL_SMTP','UTL_INADDR','UTL_FILE','DBMS_LOB');

Revoke from PUBLIC and grant back to the specific schemas that genuinely need it — usually far fewer than you expect.

The ANY privileges are a skeleton key. SELECT ANY TABLE, EXECUTE ANY PROCEDURE, ALTER ANY … — each one crosses every schema boundary in the database. They get granted to application accounts and reporting users because it was easier than enumerating objects. List the ones held by accounts you created (Oracle's own maintained accounts legitimately hold some):

SELECT p.grantee, p.privilege
FROM   dba_sys_privs p JOIN dba_users u ON u.username = p.grantee
WHERE  u.oracle_maintained = 'N'
AND    p.privilege LIKE '%ANY%';

DBA (or PDB_DBA) on an application account is the same mistake wearing a role. An app should own its objects and hold exactly the privileges its code needs — never the role that can read, alter, and drop everything. Check with DBA_ROLE_PRIVS filtered the same way, and replace the role with a tailored one.

3. Lock the front door: the listener and the network

The database can be perfectly hardened and still be trivially reachable if the listener in front of it isn't. Three controls matter more than the rest:

Valid-node checking. Restrict which hosts may even connect to the listener with TCP.VALIDNODE_CHECKING, TCP.INVITED_NODES, and TCP.EXCLUDED_NODES in sqlnet.ora. It's an allow-list at the network layer, before authentication.
Admin restrictions. Set ADMIN_RESTRICTIONS_<listener> = ON so nobody can reconfigure the listener remotely at runtime, and make sure it requires OS authentication for admin — a listener you can set remotely is a listener an attacker can set remotely.
Never on the public internet. A database listener on 0.0.0.0:1521 reachable from outside your network is the finding behind a large share of Oracle compromises. It belongs on a private subnet, behind a security group that allows only the application tier.

If you can encrypt the client-to-database traffic, do — either native network encryption in sqlnet.ora or TCPS/TLS. An unencrypted connection is credentials and data in cleartext on the wire.

4. Encrypt at rest: TDE

Everything above protects the running database. Transparent Data Encryption protects it when it isn't running — when a datafile, a backup piece, or a decommissioned disk leaves the building. Without TDE, a stolen datafile or RMAN backup is just a file an attacker strings; with it, it's noise without the keystore.

TDE encrypts at the tablespace (or column) level and is transparent to the application — no SQL changes. You set up a keystore once, then encrypt:

-- one-time: configure and open a keystore (WALLET_ROOT), then:
ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "<pwd>" WITH BACKUP;
ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES256' ENCRYPT;

New tablespaces should be encrypted from creation. And TDE is what makes an RMAN backup safe to store off-site — pair it with the recovery discipline in the RMAN Recovery Runbook so the backup you can restore is also the backup you don't have to worry about losing.

5. Password profiles: make brute force expensive

Default-password hygiene (item 1) closes the known passwords. A failed-login lockout closes the guessable ones by making an online brute-force attack die after a handful of tries. This is one ALTER PROFILE on the DEFAULT profile — which nearly every account inherits — and it's often left wide open:

SELECT resource_name, limit
FROM   dba_profiles
WHERE  profile = 'DEFAULT'
AND    resource_name IN ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME','PASSWORD_VERIFY_FUNCTION');

If FAILED_LOGIN_ATTEMPTS reads UNLIMITED, an attacker can guess forever. Set a finite lockout, a password lifetime, and attach a verify function so weak passwords can't be set in the first place:

ALTER PROFILE DEFAULT LIMIT
  FAILED_LOGIN_ATTEMPTS 10
  PASSWORD_LIFE_TIME 180
  PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;

(Balance the lockout against your reality: too aggressive a threshold plus a shared service account is a self-inflicted denial of service. Ten is a sane default; service accounts should use long, rotated secrets rather than relying on lockout at all.)

6. Audit the few things worth auditing

The reason most Oracle databases have no useful audit trail is that "audit everything" produces a firehose nobody reads, so eventually someone turns it off. Unified Auditing — the model since 12c, which writes to a protected internal table instead of OS files — makes selective, low-noise auditing easy through policies. You don't need dozens; you need the two that catch privilege abuse and credential attacks, which Oracle pre-defines:

-- what's actually enabled right now:
SELECT policy_name, enabled_option FROM audit_unified_enabled_policies ORDER BY policy_name;

-- the sensible baseline:
AUDIT POLICY ora_secureconfig;    -- privileged actions, structural changes (on by default — keep it)
AUDIT POLICY ora_logon_failures;  -- failed logins: the brute force from item 5, recorded

ORA_SECURECONFIG is enabled by default in a modern database — the failure mode is someone having turned it off. From that baseline, add narrow policies for the things specific to you (access to a sensitive table, use of a powerful role) rather than broad ones. If you're still on the legacy AUDIT/AUD$ model, plan the move to pure Unified Auditing — it's faster and its trail can't be tampered with from outside the database.

Hardening in priority order: each step closes a class of real attack. Do them top to bottom — a later control on an unhardened account is polish on a door that's already open.

Run the checklist against a live database. The hardening-audit lab stands up an Oracle Database Free container in a deliberately weak state, runs ./run.sh audit to score it against these controls — default passwords, PUBLIC/ANY grants, DBA-on-app-accounts, the failed-login profile, and the auditing baseline — then ./run.sh harden fixes them and re-audits to prove every check flips from FAIL to PASS. The CI matrix runs the whole cycle on every push, so the numbers aren't a claim, they're a test.

What teams get wrong

Chasing the whole benchmark. Two hundred controls sorted by document order means the critical five compete for attention with the trivial. Prioritize by exploitability, not by checklist completeness.
Auditing everything, reading nothing. A firehose gets muted. Enable the two baseline policies plus a few targeted ones, and actually review them.
Leaving default and unused accounts open. DBA_USERS_WITH_DEFPWD and account_status = 'OPEN' take one query each. There is no excuse for an open account with a known password.
Granting DBA (or %ANY%) for convenience. The app that "just needs it to work" becomes the account that can read and drop every schema once it's compromised.
Treating hardening as one-time. Grants accumulate, accounts get created, someone disables a policy to debug and forgets. Configuration drifts; re-audit on a cadence, the same way you patch on a cadence.
Encrypting nothing because "it's internal." Internal networks get breached and backups get lost. TDE is the control that makes those events non-events.

The one-paragraph version

Skip the two-hundred-item benchmark and do the six that matter, in order. Close accounts with default passwords (DBA_USERS_WITH_DEFPWD finds them). Take back convenience grants — EXECUTE on the network packages from PUBLIC, every %ANY% privilege, and DBA on application accounts. Lock the listener with valid-node checking and admin restrictions, and keep it off the public internet. Turn on TDE so stolen datafiles and backups are useless. Give the DEFAULT profile a failed-login lockout and a verify function. Enable Unified Auditing for ORA_SECURECONFIG and ORA_LOGON_FAILURES, then add narrow policies — not a firehose. Then re-audit on a schedule, because hardening drifts. That list stops the incidents that actually happen; everything else is refinement.

Frequently asked questions

What is the most important first step in Oracle database hardening?

Closing accounts that still have a default or known password. It is the most reliably exploited Oracle weakness and requires no exploit — an attacker simply tries known username/password pairs against the listener. Oracle provides the DBA_USERS_WITH_DEFPWD view, which lists every account whose password matches a known default; lock and expire the ones you do not need and set real passwords on the ones you do. It costs one query and a few ALTER USER statements and removes the easiest way in.

Should I revoke EXECUTE on packages from PUBLIC?

For the network and file packages — UTL_HTTP, UTL_TCP, UTL_SMTP, UTL_INADDR, UTL_FILE — yes, in most environments. Granted to PUBLIC, they let any authenticated account (including a compromised low-privilege one) open outbound network connections or read files, which is an exfiltration and lateral-movement capability. Revoke EXECUTE from PUBLIC and grant it back only to the specific schemas that genuinely require it. Test first, because application code occasionally relies on these grants.

What is the difference between traditional and Unified Auditing in Oracle?

Traditional auditing (the legacy AUDIT command writing to SYS.AUD$ or to OS files) is configured statement by statement and can be verbose and hard to protect. Unified Auditing, the model since Oracle 12c, consolidates all audit data into a single protected, read-only internal table and is configured through policies you enable or disable as a unit. Unified Auditing is faster, harder to tamper with, and easier to keep low-noise. Oracle recommends moving to pure Unified Auditing; enable the ORA_SECURECONFIG and ORA_LOGON_FAILURES policies as a baseline.

Does hardening an Oracle database require downtime?

Most of it does not. Locking accounts, revoking privileges, altering the DEFAULT profile, and enabling Unified Auditing policies are all online operations. Encrypting existing tablespaces with TDE can be done online in current releases, though the initial keystore configuration may involve a restart depending on how WALLET_ROOT is set. Listener and sqlnet.ora changes take effect on a listener reload, not a database restart. Plan TDE and network-encryption rollouts, but the high-value account and privilege work is all no-outage.

Is Transparent Data Encryption (TDE) necessary if my database is on an internal network?

It protects a different threat than network controls do. Listener and firewall rules protect the running database from unauthorized connections; TDE protects the data when it is at rest and leaves the running system — a stolen or misplaced datafile, an RMAN backup piece copied off-site, a decommissioned disk. Internal networks are breached and backups do get lost, so for any database holding sensitive or regulated data, TDE is the control that turns those events into non-events. It is transparent to the application.

How often should I re-audit an Oracle database against a hardening checklist?

On a defined cadence, the same way you patch — quarterly is a reasonable baseline, plus after any significant change. Hardening drifts: privileges get granted for a one-off task and never revoked, accounts get created, someone disables an audit policy to debug an issue and forgets to re-enable it. A one-time hardening project degrades steadily unless you re-check it. Automating the checks (a script or a lab-style audit that reports PASS/FAIL) makes the re-audit cheap enough to actually do.

What are the ANY privileges and why are they a hardening risk?

The system privileges containing ANY — SELECT ANY TABLE, EXECUTE ANY PROCEDURE, ALTER ANY TABLE, and others — grant an operation across every schema in the database, ignoring object ownership boundaries. They are commonly granted to application or reporting accounts as a shortcut instead of enumerating the specific objects needed. If such an account is compromised, the ANY privilege lets the attacker read, alter, or drop objects anywhere. Find them with DBA_SYS_PRIVS filtered to accounts where ORACLE_MAINTAINED = N, and replace them with grants on the specific objects the account actually uses.

Hardening and patching are the two halves of the security-and-ops discipline: patching closes the vulnerabilities Oracle tells you about, hardening closes the ones your own configuration opened. Neither is a one-time project — both are a cadence. Start with the six controls above, prove them with the hardening-audit lab, and put the re-audit on the same calendar as your quarterly Release Update.

Originally published at uptimearchitect.com.

DEV Community: Uptime Architect

Oracle Privilege Analysis: You Granted DBA. Here's What They Actually Used.

What Privilege Analysis actually records

How a capture works

The finding you'll get almost every time: SELECT ANY TABLE, unused

Now prove it

What teams get wrong

Frequently asked questions

What is Oracle Privilege Analysis?

How do I run a privilege capture with DBMS_PRIVILEGE_CAPTURE?

What are the four privilege capture types?

Why does SELECT ANY TABLE show up as an unused privilege?

Which views show the results of a privilege capture?

Does Privilege Analysis capture privileges used through roles?

Does Privilege Analysis require the Database Vault option or a separate license?

What are the limits of Privilege Analysis I should plan around?

Oracle Virtual Private Database: Row-Level Security You Can't Route Around

What VPD is — a predicate the database adds for you

Key the predicate off something the user can't forge

Who bypasses the policy

Now prove it

Don't forget the other three verbs

What teams get wrong

Frequently asked questions

What is Oracle Virtual Private Database (VPD)?

How is VPD different from using a view to hide rows?

What is a VPD policy function and what must it return?

Who is exempt from VPD policies?

Does VPD apply to INSERT, UPDATE, and DELETE, or only SELECT?

What is the difference between VPD, Data Redaction, and Oracle Label Security?

Does VPD hurt query performance?

Is VPD available in Oracle Database Free and Standard Edition?

Oracle Data Pump, Done Right: A Migration You Can Prove

What Data Pump actually is

The flags that actually matter

The quoting trap — use a parfile

Now prove it

What teams get wrong

Frequently asked questions

What is Oracle Data Pump and how is it different from the old exp/imp?

Why does Data Pump need a DIRECTORY object?

How do I migrate a schema to a different name with Data Pump?

How do I export or import only some tables or rows?

How do I make a Data Pump export consistent?

How do I speed up a large Data Pump job?

Can I move data directly between databases without a dump file?

Does Data Pump require a separate license?

Oracle Partition Pruning: Read One Partition, Not the Whole Table

What pruning actually is

The mistake that silently defeats it

Static pruning vs dynamic pruning

Now prove it

What teams get wrong

Frequently asked questions

What is partition pruning in Oracle?

Why is my partitioned table still doing a full scan of every partition?

What do Pstart and Pstop mean in an execution plan?

What is the difference between static and dynamic partition pruning?

Does partition pruning replace indexes?

Should I use a local or global index on a partitioned table?

Does partitioning require a separate Oracle license?

Oracle Data Redaction: Mask at Read Time, Prove It Isn't Access Control

What redaction is — and the two things it isn't

The four ways to mask

Who gets redacted, and who doesn't

Now prove it

What it doesn't do — the same lab, two more reads

What teams get wrong

Frequently asked questions

What is Oracle Data Redaction (DBMS_REDACT)?

Does Data Redaction change or encrypt the stored data?

What is the difference between Data Redaction, TDE, and Data Masking?

What redaction function types does DBMS_REDACT support?

How do I control which users see redacted data versus real data?

Can a user bypass Oracle Data Redaction?

Does Data Redaction slow down queries or affect indexes?

Does Oracle Data Redaction require a separate license?

Oracle Flashback: Undo at the Database Level

The family, and what each is built on

Look before you leap: Flashback Query

The finding you'll get almost every time: `SELECT ANY TABLE`, unused