DEV Community: Vahid Yousefzadeh

23% Faster Autovacuum with Parallel Workers in PostgreSQL 19

Vahid Yousefzadeh — Mon, 19 Oct 2026 07:31:39 +0000

Before PostgreSQL 19, autovacuum processed indexes without using parallel vacuum workers, even when a table had multiple large indexes. Although PostgreSQL already supported parallel vacuuming for manually executed VACUUM commands, this capability was not available to autovacuum.

For example, manual vacuuming can use parallel workers:

vahiddb=# VACUUM (PARALLEL 10) person;
VACUUM

PostgreSQL 19 introduces Parallel Autovacuum, allowing autovacuum to use parallel workers during index vacuuming and index cleanup.

This feature is particularly useful for tables with large indexes and substantial numbers of dead tuples. However, it does not parallelize the table’s heap-scanning phase. Consequently, tables without indexes cannot benefit from this feature, and tables with large indexes are promising candidates.

In this article, I will demonstrate the feature using a table containing five million rows and four indexes. I will compare the execution times of autovacuum with parallel workers disabled and enabled.

1. Inspecting the test table

My test environment contains a table named person in the vahiddb database.

First, I checked the number of rows:

vahiddb=# select count(*) from person;
  count
---------
 5000000
(1 row)

The table contains five million rows and has four indexes.

I used the following command to check their sizes:

vahiddb=# \dt+
                                     List of tables
 Schema |  Name  | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+--------+-------+----------+-------------+---------------+--------+-------------
 public | person | table | postgres | permanent   | heap          | 403 MB |
(1 row)

vahiddb=# \di+
                                               List of indexes
 Schema |        Name        | Type  |  Owner   | Table  | Persistence | Access method |  Size  | Description
--------+--------------------+-------+----------+--------+-------------+---------------+--------+-------------
 public | person_flname_idx  | index | postgres | person | permanent   | btree         | 41 MB  |
 public | person_full_idx    | index | postgres | person | permanent   | btree         | 366 MB |
 public | person_lfaname_idx | index | postgres | person | permanent   | btree         | 324 MB |
 public | person_pk          | index | postgres | person | permanent   | btree         | 107 MB |
(4 rows)

The four indexes occupy approximately 838 MB in total, which is more than twice the size of the table.

This workload is a good candidate for testing parallel autovacuum because index processing represents a substantial part of the maintenance work.

2. Configuring autovacuum logging

To measure the execution time, I enabled detailed autovacuum logging.

I set log_autovacuum_min_duration to 0 so that every autovacuum operation would be logged:

vahiddb=# ALTER SYSTEM SET log_autovacuum_min_duration = 0;
ALTER SYSTEM
vahiddb=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

3. Measuring autovacuum without parallel workers

In PostgreSQL 19, the autovacuum_max_parallel_workers parameter controls the maximum number of parallel workers that a single autovacuum worker can use.

Setting it to zero disables parallel vacuuming during autovacuum.

vahiddb=# ALTER SYSTEM SET autovacuum_max_parallel_workers = 0;
ALTER SYSTEM

vahiddb=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Next, I updated three million rows to generate dead tuples and trigger autovacuum:

vahiddb=# UPDATE person
SET
    fname = 'Vahid' || fname,
    lname = 'Yousefzadeh' || lname
        where id<=3000000;

UPDATE 3000000

After autovacuum completed, I examined the server log. The following excerpt contains the most relevant results:

2026-10-18 19:45:55.201 +03 [2323] LOG:  automatic vacuum of table "vahiddb.public.person": index scans: 1
        pages: 0 removed, 88581 remain, 67964 scanned (76.73% of total), 0 eagerly scanned
        tuples: 3000000 removed, 5000070 remain, 0 are dead but not yet removable
        removable cutoff: 771, which was 0 XIDs old when operation ended
        frozen: 37038 pages from table (41.81% of total) had 3000058 tuples frozen
        visibility map: 67964 pages set all-visible, 67964 pages set all-frozen (0 were all-visible)
        index scan needed: 30927 pages from table (34.91% of total) had 3000000 dead item identifiers removed
        index "person_pk": pages: 21938 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_flname_idx": pages: 11218 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_lfaname_idx": pages: 72221 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_full_idx": pages: 103264 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        avg read rate: 16.980 MB/s, avg write rate: 14.303 MB/s
        buffer usage: 84022 hits, 291632 reads, 245655 dirtied
        WAL usage: 313620 records, 245655 full page images, 1048438885 bytes, 1018664200 full page image bytes, 98 buffers full
        memory usage: dead item storage 2.33 MB accumulated across 1 reset (limit 64.00 MB each)
        system usage: CPU: user: 6.97 s, system: 1.77 s, elapsed: 134.18 s

The most important measurement is the elapsed time reported by PostgreSQL:

elapsed: 134.18 s

This is our baseline for comparison.

4. Enabling Parallel Autovacuum

Now I enabled parallel autovacuum by setting the maximum number of parallel workers to four:

vahiddb=# ALTER SYSTEM SET autovacuum_max_parallel_workers = 4;
ALTER SYSTEM

vahiddb=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

A configuration reload is sufficient for this setting; a server restart is not required.

PostgreSQL 19 also supports a table-level setting named . This parameter allows us to configure the maximum number of parallel workers for an individual table.

For the person table, I used:

vahiddb=# ALTER TABLE person SET (
    autovacuum_enabled = true,
    autovacuum_parallel_workers = 4
);

ALTER TABLE

The effective number of workers is subject to the configured limits and available worker capacity. Setting a value of four does not guarantee that four workers will always be launched.

Resource considerations
Parallel autovacuum should be configured carefully on production systems.

The relevant settings include:

· autovacuum_max_parallel_workers: maximum parallel workers per autovacuum operation.

· autovacuum_parallel_workers: table-level parallel-worker limit.

· max_parallel_workers: overall limit for parallel workers.

· autovacuum_work_mem: memory available to each autovacuum worker, subject to its configuration and applicable limits.

Increasing parallelism can increase CPU and I/O demand. Although parallel vacuum operations apply maintenance memory limits differently from parallel queries, administrators should still account for concurrent maintenance operations and the memory requirements of the cluster.

For this test, I used four as the configured maximum and allowed PostgreSQL to determine the actual worker count.

5. Repeating the test with Parallel Autovacuum

I repeated the same update against the person table:

vahiddb=# UPDATE person
SET
    fname = 'Vahid' || fname,
    lname = 'Yousefzadeh' || lname
        where id<=3000000;

  UPDATE 3000000

The resulting autovacuum log included the following information:

2026-10-18 19:33:49.737 +03 [2273] LOG:  automatic vacuum of table "vahiddb.public.person": index scans: 1
        pages: 0 removed, 88581 remain, 67964 scanned (76.73% of total), 0 eagerly scanned
        tuples: 3000000 removed, 5000046 remain, 0 are dead but not yet removable
        removable cutoff: 762, which was 0 XIDs old when operation ended
        new relfrozenxid: 762, which is 6 XIDs ahead of previous value
        frozen: 37037 pages from table (41.81% of total) had 3000000 tuples frozen
        visibility map: 67964 pages set all-visible, 67964 pages set all-frozen (0 were all-visible)
        index scan needed: 30927 pages from table (34.91% of total) had 3000000 dead item identifiers removed
        parallel workers: index vacuum: 3 planned, 3 launched in total
        index "person_pk": pages: 21938 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_flname_idx": pages: 11218 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_lfaname_idx": pages: 72221 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "person_full_idx": pages: 103264 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        avg read rate: 22.357 MB/s, avg write rate: 18.781 MB/s
        buffer usage: 83274 hits, 292440 reads, 245655 dirtied
        WAL usage: 313620 records, 245655 full page images, 1048438901 bytes, 1018664348 full page image bytes, 139 buffers full
        memory usage: dead item storage 3.00 MB accumulated across 1 reset (limit 64.00 MB each)
        system usage: CPU: user: 4.50 s, system: 0.93 s, elapsed: 102.18 s

This is the line that explicitly confirms parallel vacuum execution:

parallel workers: index vacuum: 3 planned, 3 launched in total
PostgreSQL planned three parallel index-vacuum workers and successfully launched all three. The elapsed time was:

elapsed: 102.18 s

That represents an approximately 23.9% reduction in elapsed time in this particular experiment.

The key takeaway is that Parallel Autovacuum can improve maintenance performance for tables with large indexes, but the actual benefit depends on the workload, index sizes, available resources.

Calculating WAL Generation for DML Operations in PostgreSQL

Vahid Yousefzadeh — Mon, 12 Oct 2026 17:13:34 +0000

PostgreSQL provides the EXPLAIN command with the ANALYZE and WAL options to measure the WAL generated by a DML operation. This feature helps database administrators investigate the impact of INSERT, UPDATE, and DELETE statements on WAL generation.

In this article, we use EXPLAIN (ANALYZE, WAL) to measure the WAL generated when updating 10 million rows, compare the results with and without an index, and investigate the effect of enabling WAL compression.

1. Test environment

The test uses a person table containing 10 million rows. The table occupies approximately 880 MB, and WAL compression is initially disabled.

vahiddb=# \dt+
                                     List of tables
 Schema |  Name  | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+--------+-------+----------+-------------+---------------+--------+-------------
 public | person | table | postgres | permanent   | heap          | 880 MB |
(1 row)

vahiddb=# show wal_compression;
 wal_compression
-----------------
 off
(1 row)

2. Calculating WAL generated by an UPDATE

To measure WAL generation, execute the UPDATE statement with the following options:

vahiddb=# EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF)
UPDATE person
SET name = 'Vahid',
    last_name = 'Yousefzadeh';

                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Update on person (actual rows=0.00 loops=1)
   Buffers: shared hit=72295238 read=319243 dirtied=315766 written=273030
   WAL: records=30258061 fpi=212178 bytes=4014818468 fpi bytes=1551338708 buffers full=144689
   ->  Seq Scan on person (actual rows=10000000.00 loops=1)
         Buffers: shared hit=298 read=205744 dirtied=72743 written=109115
         WAL: records=145390 fpi=72743 bytes=610946323 fpi bytes=590673276 buffers full=65677
 Planning Time: 0.116 ms
 Execution Time: 160129.955 ms
(8 rows)

The statement updates all 10 million rows. The WAL section of the execution plan reports the number of WAL records, full-page images (FPIs), and total WAL bytes generated during execution.

WAL: records=30258061 fpi=212178 bytes=4014818468 fpi bytes=1551338708 buffers full=144689

The reported WAL volume is approximately 4.01 GB.

3. Measuring the effect of indexes

Next, create a composite index on the columns being updated:

vahiddb=#  create index indname_lname on person(name,last_name);
CREATE INDEX

Execute the same UPDATE statement with WAL compression disabled:

vahiddb=#  EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF)
UPDATE person
SET name = 'Vahid',
    last_name = 'Yousefzadeh';

                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Update on person (actual rows=0.00 loops=1)
   Buffers: shared hit=104133558 read=452220 dirtied=265562 written=95009
   WAL: records=40487705 fpi=151040 bytes=4310991885 fpi bytes=986566516 buffers full=126317
   ->  Seq Scan on person (actual rows=10000000.00 loops=1)
         Buffers: shared read=278835 written=27966
 Planning:
   Buffers: shared hit=18 read=3
 Planning Time: 0.504 ms
 Execution Time: 262993.363 ms
(9 rows)

The reported WAL volume increases from approximately 4.01 GB to 4.31 GB, while execution time increases from approximately 160 seconds to 263 seconds in the supplied runs:

WAL: records=40487705 fpi=151040 bytes=4310991885 fpi bytes=986566516 buffers full=126317

4. Measuring the effect of WAL compression

Enable WAL compression and repeat the test:

vahiddb=# set wal_compression=on;
SET

The non-indexed test reports approximately 2.65 GB of WAL, compared with 4.01 GB when compression is disabled. This represents an approximately 33.94% reduction in reported WAL volume:

vahiddb=# EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF)
UPDATE person
SET name = 'Vahid',
    last_name = 'Yousefzadeh';

                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Update on person (actual rows=0.00 loops=1)
   Buffers: shared hit=73576559 read=479691 dirtied=272363 written=192157
   WAL: records=30146290 fpi=170676 bytes=2652681749 fpi bytes=211050036 buffers full=30525
   ->  Seq Scan on person (actual rows=10000000.00 loops=1)
         Buffers: shared hit=408 read=278428 dirtied=22268 written=76164
         WAL: records=45527 fpi=22268 bytes=33702425 fpi bytes=26333321 buffers full=2863
 Planning:
   Buffers: shared hit=11
 Planning Time: 0.112 ms
 Execution Time: 154784.519 ms
(10 rows)

WAL compression primarily compresses full-page images rather than all WAL records. Consequently, its effectiveness depends partly on how much of the generated WAL consists of compressible full-page images.

Prevent Accidental Duplicate Rows with JOIN TO ONE in Oracle AI Database 26ai

Vahid Yousefzadeh — Mon, 12 Oct 2026 06:57:54 +0000

Oracle AI Database 23.26.2 introduced the JOIN TO ONE syntax to address a common problem with SQL joins: a query can unexpectedly return multiple rows for a row in the main table.

This is especially useful when a query is designed to widen a table without increasing its number of rows. With JOIN TO ONE, Oracle raises an error when a subject row matches multiple rows in a joined table instead of silently returning the multiplied rows.

In this article, we will use three tables:

CUSTOMERS
    1
    |
    M
 ACCOUNTS
    1
    |
    M
TRANSACTIONS

The relationships are:

One customer can have multiple accounts.
Each account belongs to one customer.
One account can have multiple transactions.
Each transaction belongs to one account. For example:

Vahid Yousefzadeh
        |
        +-- A1001
              |
              +-- Transaction 1
              +-- Transaction 2
              +-- Transaction 3

The Normal JOIN

Suppose we want to display each account together with its customer:

SELECT a.account_number, c.customer_name
  FROM accounts a
  JOIN customers c
    ON c.customer_id = a.customer_id;

ACCOUNT_NUMBER       CUSTOMER_NAME
-------------------- ------------------------------
A1001                Vahid Yousefzadeh
A1002                Ebne moljem moradi
A1003                Payan Rafat

There is one row for each account. This works because every account belongs to exactly one customer.

Now suppose we want to show the same customer information only for accounts that have transactions:

SELECT a.account_number, c.customer_name
  FROM accounts a
  JOIN customers c
    ON c.customer_id = a.customer_id
  JOIN transactions t
    ON t.account_id = a.account_id;

ACCOUNT_NUMBER       CUSTOMER_NAME
-------------------- ------------------------------
A1001                Vahid Yousefzadeh
A1001                Vahid Yousefzadeh
A1001                Vahid Yousefzadeh
A1002                Ebne moljem moradi
A1002                Ebne moljem moradi
A1003                Payan Rafat
6 rows selected.

As you can see, some rows are duplicated. The main reason is that A1001 has three transactions:

A1001
 |
 +-- Transaction 1
 +-- Transaction 2
 +-- Transaction 3

Therefore, the normal join returns the account once for each matching transaction.

This is normal behavior for a one-to-many join. The problem is that our query may have been intended to return one row per account.

Traditional joins do not automatically identify this as a problem.

Using JOIN TO ONE

Oracle AI Database 26ai provides JOIN TO ONE for exactly this type of requirement.

We can write:

SQL> SELECT a.account_number, c.customer_name
  FROM accounts a
  JOIN TO ONE(customers c, transactions t
    ON t.account_id = a.account_id);  

ERROR at line 3:
ORA-18640: JOIN TO ONE reached multiple rows joining to "T", resulting in a non-unique join
Help: https://docs.oracle.com/error-help/db/ora-18640/

We are saying that the rows from ACCOUNTS should remain one row per account after the join.

However, A1001 matches three rows in TRANSACTIONS.

Therefore Oracle raises an error instead of silently returning:

A1001  Vahid Yousefzadeh
A1001  Vahid Yousefzadeh
A1001  Vahid Yousefzadeh

This tells us that the JOIN TO ONE requirement has been violated.

The important point is that JOIN TO ONE does not mean an account cannot have multiple transactions. The ACCOUNTS → TRANSACTIONS relationship is still one-to-many. It means that when ACCOUNTS is the subject table, each account is expected to remain a single row in the result.

What If TRANSACTIONS Is Only a Filter?

Now consider a different requirement:

Show accounts that have at least one transaction. We don’t actually need any columns from TRANSACTIONS. Using a normal join would unnecessarily multiply the account rows.

Instead, we can use EXISTS:

SQL> SELECT a.account_number, c.customer_name
  FROM accounts a
  JOIN customers c
    ON c.customer_id = a.customer_id
 WHERE EXISTS
 (SELECT 1 FROM transactions t WHERE t.account_id = a.account_id);

ACCOUNT_NUMBER       CUSTOMER_NAME
-------------------- ------------------------------
A1001                Vahid Yousefzadeh
A1002                Ebne moljem moradi
A1003                Payan Rafat

It doesn’t matter that A1001 has three transactions. We only want to know whether a transaction exists.

When a many-side table is being used only to filter the subject table, a semijoin such as EXISTS is an appropriate approach.

What If We Actually Need Transaction Data?

Suppose our requirement changes. Now we want: Display every transaction together with its account.

In this case, TRANSACTIONS should become the subject table.

SQL> SELECT t.transaction_id,
       t.amount,
       a.account_number
FROM transactions t
JOIN TO ONE (
    accounts a
);

TRANSACTION_ID     AMOUNT ACCOUNT_NUMBER
-------------- ---------- --------------------
             1        500 A1001
             2        250 A1001
             3        100 A1001
             4        800 A1002
             5        150 A1002
             6       1150 A1003
6 rows selected.

Each transaction belongs to one account.

Therefore, each transaction can be widened with account information without multiplying the transaction row.

The order of the tables in JOIN TO ONE is important because Oracle processes the joins from the subject table from left to right.

How Does Oracle Find the Join Condition?

In our example, we created these foreign keys:

CONSTRAINT accounts_customer_fk
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
and:

CONSTRAINT transactions_account_fk
    FOREIGN KEY (account_id)
    REFERENCES accounts(account_id)

Oracle can use foreign-key relationships to infer the join conditions for JOIN TO ONE.

We can also specify the join condition explicitly:

SQL> SELECT a.account_number, c.customer_name
  FROM accounts a
  JOIN TO ONE(customers c
    ON c.customer_id = a.customer_id);

ACCOUNT_NUMBER       CUSTOMER_NAME
-------------------- ------------------------------
A1001                Vahid Yousefzadeh
A1002                Ebne moljem moradi
A1003                Payan Rafat

Using an explicit ON condition can make the intended relationship clearer, particularly when there are multiple foreign keys between tables or the schema changes later.

Verifying PostgreSQL Backups Using pg_verifybackup

Vahid Yousefzadeh — Sat, 10 Oct 2026 10:27:09 +0000

Pg_verifybackup was introduced in PostgreSQL 13 and is used to check the integrity of a base backup taken using Pg_basebackup against the backup_manifest file generated by the server.

The manifest is a JSON document containing information about the backup files, including their paths, sizes, last modification times, and, optionally, checksums. For example:

{ "PostgreSQL-Backup-Manifest-Version": 2,
"System-Identifier": 7694938432524117566,
"Files": [
{ "Path": "backup_label", "Size": 227, "Last-Modified": "2026-10-10 07:42:44 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "509e2e33" },
{ "Path": "global/1262", "Size": 8192, "Last-Modified": "2026-10-10 07:35:34 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "d7e25e67" },
{ "Path": "global/2964", "Size": 0, "Last-Modified": "2026-10-10 07:31:08 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "00000000" },
{ "Path": "global/1213", "Size": 8192, "Last-Modified": "2026-10-10 07:31:09 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "332b6c66" },
{ "Path": "global/1260", "Size": 8192, "Last-Modified": "2026-10-10 07:31:09 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "bd0707e0" },

pg_verifybackup uses the information in this file to verify the backup. However, this tool cannot guarantee that the database can be restored successfully. It checks specific aspects of the backup’s integrity and consistency.

Creating and Verifying a Backup

First, let’s create a backup using pg_basebackup:

[postgres@OEL98-PG ~]$ pg_basebackup -D /backup/ -v

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/4A000118 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_2003"
pg_basebackup: write-ahead log end point: 0/4A000328
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

Now, let’s verify the backup:

[postgres@OEL98-PG ~]$ pg_verifybackup /backup/ -P
740739/740739 kB (100%) verified
backup successfully verified

The output indicates that the verification completed successfully.

Identifying Missing and Extra Files

If we rename a file in the backup, pg_verifybackup will identify the changes. For example:

[postgres@OEL98-PG ~]$ mv /backup/global/1213 /backup/global/1213-old

Now, execute the command again:

[postgres@OEL98-PG ~]$ pg_verifybackup /backup/ -P
pg_verifybackup: error: "global/1213-old" is present on disk but not in the manifest
pg_verifybackup: error: "global/1213" is present in the manifest but not on disk
740731/740731 kB (100%) verified

Two errors occurred. The first indicates that an extra file (1213-old) is present on disk but not in the manifest, while the second indicates that an expected file (1213) is missing.

Although the progress reaches 100%, the verification has failed because errors were detected.

Now, let’s restore the original filename:

[postgres@OEL98-PG ~]$ mv /backup/global/1213-old /backup/global/1213
Identifying Modified Files

In the next step, I want to modify a file and execute this tool again.

First, let’s check the file’s details:

[postgres@OEL98-PG 16384]$ ll /backup/base/16384/3456
-rw-------. 1 postgres postgres 122880 Oct 10 10:42 /backup/base/16384/3456

Now, modify the file’s contents:

[postgres@OEL98-PG ~]$ printf 'curropt' |dd of=/backup/base/16384/3456 bs=1 seek=100 conv=notrunc status=none

Execute the tool again:

[postgres@OEL98-PG ~]$ pg_verifybackup /backup/ -P
pg_verifybackup: error: checksum mismatch for file "base/16384/3456"
740739/740739 kB (100%) verified

The checksum verification failed, identifying a file whose contents differ from those recorded in the manifest. This indicates that the file has been modified, although it does not necessarily mean that accidental corruption occurred.
However, we can skip data-file checksum verification using — skip-checksums:

[postgres@OEL98-PG ~]$ pg_verifybackup /backup/ -P --skip-checksums
backup successfully verified

Note that skipping checksums does not repair the modified file. It disables data-file checksum verification, so this successful result does not mean that the backup is intact. Other checks, such as file presence, file sizes, and WAL verification, are still performed unless separately disabled.

WAL Verification

One of the more complex features of pg_verifybackup is WAL verification. This tool verifies the required WAL records using the WAL-Ranges section in the manifest file. For example:

"WAL-Ranges": [
{ "Timeline": 1, "Start-LSN": "0/4A000118", "End-LSN": "0/4A000328" }
],

Each range contains three important fields: Timeline, Start-LSN, and End-LSN.
PostgreSQL’s pg_verifybackup invokes the companion utility pg_waldump to parse the required WAL records. It passes the timeline and the start and end LSNs obtained from the manifest, using quiet mode so that only errors are reported.
A successful WAL check does not prove that every record will perform the correct operation during recovery. It checks whether the required WAL records can be read and parsed, but it cannot guarantee that recovery will succeed in every situation.

PostgreSQL 19: Online Table Reorganization with REPACK (Similar to Oracle DBMS_REDEFINITION)

Vahid Yousefzadeh — Wed, 07 Oct 2026 10:52:54 +0000

For years, PostgreSQL DBAs have relied on VACUUM FULL to rewrite tables and reclaim unused space. However, when minimizing the impact of table reorganization is important, tools such as pg_repack provide an online-style alternative.

PostgreSQL 19 introduces the new REPACK command as a built-in solution for table reorganization, including REPACK CONCURRENTLY, which allows the table to remain available for normal database activity during most of the operation.

PostgreSQL 19 also extends this capability with support for reorganizing a table according to an index, providing functionality comparable to the physical ordering achieved with CLUSTER.

How REPACK Works

REPACK does not simply compact the existing table in place. Conceptually, it creates a new, compact copy of the table, copies the existing rows into it, tracks changes made to the original table while the operation is running, and applies those changes to the new table.

At the end, it performs a short final synchronization and swaps the old table with the reorganized one. This approach allows normal DML to continue during most of the operation.

This approach is conceptually similar to Oracle’s DBMS_REDEFINITION. In Oracle, an interim table is populated while the original table remains available, and a materialized view log can capture changes made to the original table during the reorganization. These changes are then synchronized to the interim table before the final DBMS_REDEFINITION.FINISH_REDEF_TABLE operation switches the objects.

Limitations of REPACK

Although REPACK provides online table reorganization with minimal downtime, it has some limitations:

The table must have a primary key or a suitable NOT NULL unique index.
DDL operations on the target table cannot be performed while REPACK is running.
Long-running transactions can delay the final lock required to complete the reorganization.
In addition, the operation requires additional disk space because a new copy of the table and its indexes is created.

Testing REPACK on a Large Table

In this article, I demonstrate how REPACK can reorganize a large table with minimal disruption to concurrent DML. I also demonstrate the option to reorganize a table according to an index, similar to CLUSTER, and compare its behavior with VACUUM FULL.

The test uses a table containing 30 million rows.

1. Initial Table Size

First, I checked the number of rows in jadval1:

postgres=#  select count(*) from jadval1;

  count
----------
 30000000
(1 row)

I then checked the physical size of the table and its indexes:

postgres=# SELECT
    pg_size_pretty(pg_table_size('jadval1')) AS table_size,
    pg_size_pretty(pg_indexes_size('jadval1')) AS index_size,
    pg_size_pretty(pg_total_relation_size('jadval1')) AS total_size;
 table_size | index_size | total_size
------------+------------+------------
 4824 MB    | 1071 MB    | 5895 MB
(1 row)

Therefore, the table occupied approximately 4.8 GB, while the total relation size, including indexes, was approximately 5.9 GB.

To investigate how much space was actually occupied by live tuples, I enabled the pgstattuple extension:

postgres=# CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATE EXTENSION

Then I ran:

postgres=# SELECT
    table_len,
    tuple_count,
    tuple_len,
    dead_tuple_count,
    dead_tuple_len,
    free_space,
    pg_size_pretty(table_len) AS table_size,
    pg_size_pretty(tuple_len) AS live_tuple_size,
    pg_size_pretty(dead_tuple_len) AS dead_tuple_size,
    pg_size_pretty(free_space) AS free_space_size
FROM pgstattuple('jadval1');

 table_len  | tuple_count | tuple_len  | dead_tuple_count | dead_tuple_len | free_space | table_size | live_tuple_size | dead_tuple_size | free_space_size
------------+-------------+------------+------------------+----------------+------------+------------+-----------------+-----------------+-----------------
 5056790528 |    30000000 | 2730000000 |                0 |              0 | 2040987872 | 4823 MB    | 2604 MB         | 0 bytes         | 1946 MB
(1 row)

This result is interesting.

There were:

30 million live tuples
Approximately 2.6 GB of live tuple data
Approximately 1.9 GB of free space

2. Running REPACK CONCURRENTLY

To demonstrate that REPACK (CONCURRENTLY) allows normal database activity during table reorganization, I started the operation from Session 1:

— session 1:

postgres=# REPACK (CONCURRENTLY) Jadval1;
executing...
While REPACK was running, I performed concurrent updates from Session 2:

— session 2

postgres=# UPDATE Jadval1 SET name = 'Vahid Yousefzadeh' WHERE id BETWEEN 20000001 AND 20010000;
UPDATE 10000
postgres=#  UPDATE Jadval1 SET name = 'Vahid Yousefzadeh' WHERE id BETWEEN 20010000 AND 20020000;
UPDATE 10001
postgres=# UPDATE Jadval1 SET name = 'Vahid Yousefzadeh' WHERE id BETWEEN 20020000 AND 20030000;
UPDATE 10001
postgres=# UPDATE Jadval1 SET name = 'Vahid Yousefzadeh' WHERE id BETWEEN 20030000 AND 20040000;
UPDATE 10001

At the same time, Session 3 continuously queried the table:

session 3:

postgres=# SELECT count(*) FROM Jadval1 WHERE name = 'Vahid Yousefzadeh';                                                                                                                
 count
-------
     0
(1 row)

postgres=# \g
 count
-------
 10000
(1 row)

postgres=# \g
 count
-------
 20000
(1 row)

postgres=# \g
 count
-------
 30000
(1 row)
postgres=# \g
 count
-------
 40000
(1 row)

The increasing count confirms that the table remained accessible for both UPDATE and SELECT operations while REPACK (CONCURRENTLY) was reorganizing it.

Monitoring REPACK Progress

The pg_stat_progress_repack view can be used to monitor the operation:

postgres=# SELECT
    pid,
    phase,
    heap_tuples_scanned,
    heap_tuples_inserted,
    heap_tuples_updated,
    heap_tuples_deleted
FROM pg_stat_progress_repack;
 pid  |       phase       | heap_tuples_scanned | heap_tuples_inserted | heap_tuples_updated | heap_tuples_deleted
------+-------------------+---------------------+----------------------+---------------------+---------------------
 1554 | seq scanning heap |            12727565 |             12727565 |                   0 |                   0
(1 row)

Checking for Blocking

I also checked whether the concurrent activity was blocked:

postgres=# SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocked.wait_event_type,
    blocked.wait_event
FROM pg_stat_activity blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity blocking
    ON blocking.pid = b.pid
WHERE blocked.datname = current_database();
 blocked_pid | blocked_query | blocking_pid | blocking_query | wait_event_type | wait_event
-------------+---------------+--------------+----------------+-----------------+------------
(0 rows)

No blocking session was reported during the check. Therefore, in this test, REPACK (CONCURRENTLY) allowed the table to remain available for normal SELECT and UPDATE activity while the table was being reorganized.

REPACK Completion Time

After the concurrent activity and monitoring tests were completed, REPACK (CONCURRENTLY) finished successfully:


postgres=# REPACK (CONCURRENTLY) Jadval1;
REPACK
Time: 211323.981 ms (03:31.324)

The table reorganization completed in approximately 3 minutes and 31 seconds, while the table remained available for concurrent database activity during the operation.

3. Verifying the Result of REPACK

After REPACK completed, I checked the table size again:

postgres=# SELECT
    pg_size_pretty(pg_table_size('jadval1')) AS table_size,
    pg_size_pretty(pg_indexes_size('jadval1')) AS index_size,
    pg_size_pretty(pg_total_relation_size('jadval1')) AS total_size;
 table_size | index_size | total_size
------------+------------+------------
 2894 MB    | 644 MB     | 3539 MB
(1 row)

The following tables hows the table and index sizes before and after REPACK:

Press enter or click to view image in full size

So the table and its indexes together released approximately 2.3 GB of physical storage.

Then, I used pgstattuple again to verify the result:

postgres=# SELECT
    table_len,
    tuple_count,
    tuple_len,
    dead_tuple_count,
    dead_tuple_len,
    free_space,
    pg_size_pretty(table_len) AS table_size,
    pg_size_pretty(tuple_len) AS live_tuple_size,
    pg_size_pretty(dead_tuple_len) AS dead_tuple_size,
    pg_size_pretty(free_space) AS free_space_size
FROM pgstattuple('jadval1');
 table_len  | tuple_count | tuple_len  | dead_tuple_count | dead_tuple_len | free_space | table_size | live_tuple_size | dead_tuple_size | free_space_size
------------+-------------+------------+------------------+----------------+------------+------------+-----------------+-----------------+-----------------
 3034136576 |    30000000 | 2728360000 |                0 |              0 |   26310320 | 2894 MB    | 2602 MB         | 0 bytes         | 25 MB

The following table shows the significant reduction in free space after REPACK:

Press enter or click to view image in full size

4.Comparing REPACK with VACUUM FULL

After completing the REPACK test, I ran VACUUM FULL on the same table:

postgres=# vacuum full jadval1;
VACUUM
Time: 82397.189 ms (01:22.397)

VACUUM FULL also rewrites the table and can significantly reduce its physical size. However, the main operational difference is table availability during the operation.

To demonstrate this, I executed a SELECT against jadval1 while VACUUM FULL was running:

postgres=# SELECT count(*) FROM Jadval1 WHERE name = 'Vahid Yousefzadeh';
 count
-------
 40000
(1 row)

Time: 70548.856 ms (01:10.549)

The blocking-session check showed:

postgres=# SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocked.wait_event_type,
    blocked.wait_event
FROM pg_stat_activity blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity blocking
    ON blocking.pid = b.pid
WHERE blocked.datname = current_database();
 blocked_pid |                         blocked_query                          | blocking_pid |    blocking_query    | wait_event_type | wait_event
-------------+----------------------------------------------------------------+--------------+----------------------+-----------------+------------
        1674 | SELECT count(*) FROM Jadval1 WHERE name = 'Vahid Yousefzadeh'; |         1554 | vacuum full jadval1; | Lock            | relation
(1 row)

VACUUM FULL and REPACK can both rewrite and compact a table, but their behavior in a production environment can be very different:

Press enter or click to view image in full size

5.REPACK … USING INDEX: Clustering the Table According to an Index

REPACK provides another useful capability: reorganizing a table according to the ordering defined by an index.
First, I created an index on customer_code:

postgres=# CREATE INDEX idx_jadval1_customer_code ON jadval1(customer_code);
CREATE INDEX

Then, I reorganized the table using this index:

postgres=# REPACK (CONCURRENTLY) jadval1 USING INDEX idx_jadval1_customer_code;
REPACK

In this case, REPACK does more than simply reclaim unused space. The table is physically reorganized according to the specified index, improving the physical locality of rows with similar customer_code values.

Nested WITH Clause in Oracle AI Database 26ai(23.26.2)

Vahid Yousefzadeh — Tue, 06 Oct 2026 08:26:42 +0000

The WITH clause, also known as a Common Table Expression (CTE), is widely used to make complex SQL statements easier to organize and understand. It allows a query to define an intermediate result and then reference that result from the main query.

Previously, Oracle did not support placing a WITH clause inside another WITH query block, resulting in ORA-32034. Oracle AI Database 26ai removes this restriction and supports nested WITH clauses.

This article demonstrates the enhancement by starting with a regular SQL query, rewriting it with a standard WITH clause, and finally using a nested WITH clause in Oracle AI Database 26ai.

1. Execute the Query Without a WITH Clause

Imagine we want to display each customer’s name and city, along with the total amount of their completed orders.

We can achieve this using a regular SQL statement:

SQL>SELECT c.customer_name, c.city, SUM(o.amount) AS total_amount
  FROM customers c
  JOIN orders o
    ON o.customer_id = c.customer_id
 WHERE o.status = 'COMPLETED'
 GROUP BY c.customer_name, c.city
 ORDER BY total_amount DESC;

CUSTOMER_NAME             CITY                      TOTAL_AMOUNT
------------------------- ------------------------- ------------
Vahid Yousefzadeh         Babol                              930
Kourosh Barmak            Sari                               450
Reza Baghmisheh           Ardebil                            320
Gholamreza Naalchegar     Tehran                             275

2. Rewrite the Query Using a WITH Clause

Now, I want to separate the completed orders from the rest of the query.

The purpose of this query is still to display each customer’s name and city, along with the total amount of their completed orders. However, this time, the completed orders are first defined in a CTE called completed_orders.

In Oracle Database 19c, we could execute the following query:

SQL> WITH completed_orders AS
 (SELECT customer_id, amount FROM orders WHERE status = 'COMPLETED')
SELECT c.customer_name, c.city, SUM(o.amount) AS total_amount
  FROM customers c
  JOIN completed_orders o
    ON o.customer_id = c.customer_id
 GROUP BY c.customer_name, c.city
 ORDER BY total_amount DESC;

CUSTOMER_NAME             CITY                      TOTAL_AMOUNT
------------------------- ------------------------- ------------
Vahid Yousefzadeh         Babol                              930
Kourosh Barmak            Sari                               450
Reza Baghmisheh           Ardebil                            320
Gholamreza Naalchegar     Tehran                             275

3. Use a Nested WITH Clause

Now, I will change the structure of the previous query.

The purpose of this query is again to display each customer’s name and city, along with the total amount of their completed orders. The difference is that the completed_orders CTE is now defined inside another CTE called customer_totals.

In Oracle AI Database 26ai (23.26.2), we can execute:

SQL> WITH customer_totals AS
 (WITH completed_orders AS
     (SELECT customer_id, amount FROM orders WHERE status = 'COMPLETED')
    SELECT customer_id, SUM(amount) AS total_amount
      FROM completed_orders
     GROUP BY customer_id)
SELECT c.customer_name, c.city, t.total_amount
  FROM customers c
  JOIN customer_totals t
    ON t.customer_id = c.customer_id
 ORDER BY t.total_amount DESC;

CUSTOMER_NAME             CITY                      TOTAL_AMOUNT
------------------------- ------------------------- ------------
Vahid Yousefzadeh         Babol                              930
Kourosh Barmak            Sari                               450
Reza Baghmisheh           Ardebil                            320
Gholamreza Naalchegar     Tehran                             275

The nested form shown above was not executable in previous Oracle Database releases. For example, consider Oracle Database 21c (21.16.0.0.0).

If we execute the same nested query:

SQL> WITH customer_totals AS
 (WITH completed_orders AS
     (SELECT customer_id, amount FROM orders WHERE status = 'COMPLETED')
    SELECT customer_id, SUM(amount) AS total_amount
      FROM completed_orders
     GROUP BY customer_id)
SELECT c.customer_name, c.city, t.total_amount
  FROM customers c
  JOIN customer_totals t
    ON t.customer_id = c.customer_id
 ORDER BY t.total_amount DESC;

ERROR at line 2:
ORA-32034: unsupported use of WITH clause

4. Rewrite the Query Using EXISTS

Although nested WITH clauses were not supported in previous Oracle Database releases, the same result can be achieved using EXISTS and regular subqueries.

For example, the nested WITH query can be rewritten as follows:

SELECT c.customer_name,
       c.city,
       (SELECT SUM(o.amount)
          FROM orders o
         WHERE o.customer_id = c.customer_id
           AND o.status = 'COMPLETED') AS total_amount
  FROM customers c
 WHERE EXISTS (SELECT 1
          FROM orders o
         WHERE o.customer_id = c.customer_id
           AND o.status = 'COMPLETED')
 ORDER BY total_amount DESC;

CUSTOMER_NAME             CITY                      TOTAL_AMOUNT
------------------------- ------------------------- ------------
Vahid Yousefzadeh         Babol                              930
Kourosh Barmak            Sari                               450
Reza Baghmisheh           Ardebil                            320
Gholamreza Naalchegar     Tehran                             275

Duplicating a PDB into Another CDB

Vahid Yousefzadeh — Wed, 30 Sep 2026 08:25:09 +0000

In Oracle Database 12c, it is possible to use the DUPLICATE command at the PDB level. However, there is a limitation: a new CDB must also be created as part of the duplication process. Otherwise, the operation fails with an error.

[oracle@cdb2 ~]$ rman target sys/sys@cdb1  auxiliary sys/sys@cdb2
connected to target database: cdb1 (DBID=4178530773)
connected to auxiliary database: cdb2 (DBID=839691519)

RMAN> DUPLICATE DATABASE TO CDB2 PLUGGABLE DATABASE pdb12c;
RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE command

Starting with Oracle Database 18c, a PDB can be duplicated into an existing CDB. This is a new capability in Oracle Database 18c. The following example demonstrates how to perform this operation.

In this example, the PDB18C PDB in CDB1 is duplicated into CDB2.

During the duplication process, changes may occur in the source PDB (CDB1). At the end of the duplication, these changes are applied to the target PDB (CDB2) through archived redo logs.

Therefore, before starting the duplication, you must specify the location where the archived redo logs transferred from CDB1 to CDB2 will be stored. This can be configured using the REMOTE_RECOVERY_FILE_DEST parameter.

CDB2

[oracle@cdb2 ~]$ mkdir /18c/remote_arch

SQL> alter system set REMOTE_RECOVERY_FILE_DEST=’/18c/remote_arch’;

System altered.

After setting this parameter, use RMAN to duplicate PDB18C by running the DUPLICATE PLUGGABLE DATABASE command.

CDB2

[oracle@cdb2 ~]$ rman target sys/sys@CDB1 auxiliary sys/sys@cdb2
Recovery Manager: Release 18.0.0.0.0 – Production on Wed Jun 27 10:14:44 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CDB1 (DBID=961316182)
connected to auxiliary database: CDB2 (DBID=1453270123)

RMAN>DUPLICATE PLUGGABLE DATABASE Pdb18c TO CDB2 FROM ACTIVE DATABASE;

Starting Duplicate PDB at 27-JUN-18
contents of Memory Script:
{
   set newname for clone datafile  60 to new;
   set newname for clone datafile  61 to new;
   set newname for clone datafile  62 to new;
   restore  from  nonsparse   clone foreign pluggable database “PDB18C”  from service  ‘CDB1’   ;
}
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring foreign file 62 to /u02/18c/base/oradata/CDB2/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_undotbs1_fjt6gfbd_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 27-JUN-18
contents of Memory Script:
{
   set archivelog destination to  ‘/18c/remote_arch’;
   restore clone force from service  ‘CDB1’ foreign archivelog from scn  19590084;
}
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 27-JUN-18
archived log destination=/18c/remote_arch
channel ORA_AUX_DISK_1: using network backup set from service CDB1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=203
Finished Duplicate PDB at 27-JUN-18

Changes in the Source CDB

The alert log of CDB1 shows that redo was generated and archived while the PDB duplication was in progress:

CDB1 — Alert Log

2018-06-27T13:12:33.969208+04:30

ALTER SYSTEM ARCHIVE LOG

2018-06-27T13:13:10.553587+04:30

NET  (PID:24220): Archived Log entry 263 added for T-1.S-202 ID 0x3959dcd8 LAD:1

2018-06-27T13:13:10.582768+04:30

ALTER SYSTEM ARCHIVE LOG

2018-06-27T13:13:10.588942+04:30

Thread 1 advanced to log sequence 204 (LGWR switch)

  Current log# 2 seq# 204 mem# 0: /u01/18c_oradata/CDB1/onlinelog/o1_mf_2_fd0wlk11_.log

2018-06-27T13:13:10.591765+04:30

NET  (PID:24220): Archived Log entry 264 added for T-1.S-203 ID 0x3959dcd8 LAD:1

These archived redo logs are transferred to CDB2 and used during the duplication process.

Changes in the Target CDB

The alert log of CDB2 shows the creation and initialization of the cloned PDB.

CDB2 — Alert Log

Full restore complete of datafile 62 to datafile copy /u02/18c/base/oradata/CDB2/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_undotbs1_fjt6gfbd_.dbf.  Elapsed time: 0:00:00

  checkpoint is 19590167

  last deallocation scn is 19577386

  Undo Optimization current scn is 19587396

2018-06-27T13:13:24.011604+04:30

CREATE PLUGGABLE DATABASE Pdb18c AS CLONE USING ‘/u02/18c/home/dbs/_rm_pdb_pitr_7_cdb2.xml’        SOURCE_FILE_NAME_CONVERT = ( ‘/u01/18c_oradata/CDB1/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_temp_fm67zx39_.dbf’ , ‘/u02/18c/base/oradata/CDB2/6F99DAD756624AF9E0530488200A4A05/datafile/cl_Bm_mf_temp_fm67zx39_.dbf’,‘/u01/18c_oradata/CDB1/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_system_fm67zx30_.dbf’ , ‘/u02/18c/base/oradata/CDB2/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_system_l0t6gfam_.dbf’  ,  ‘/u01/18c_oradata/CDB1/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_sysaux_fm67zx38_.dbf’ , ‘/u02/18c/base/oradata/CDB2/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_sysaux_lvt6gfau_.dbf’  ,  ‘/u01/18c_oradata/CDB1/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_undotbs1_fm67zx39_.dbf’ , ‘/u02/18c/base/oradata/CDB2/6F99DAD756624AF9E0530488200A4A05/datafile/o1_mf_undotbs1_fjt6gfbd_.dbf’ ) NOCOPY REFRESH MODE MANUAL

2018-06-27T13:13:24.226142+04:30

PDB18C(5):ALTER SYSTEM SET remote_recovery_file_dest=’/18c/remote_arch’ SCOPE=SPFILE PDB=’PDB18C’;

PDB18C(5):alter pluggable database refresh

PDB18C(5):Completed: alter pluggable database refresh

PDB18C(5):alter pluggable database refresh mode none

PDB18C(5):Undo initialization recovery: err:0 start: 351874245 end: 351874276 diff: 31 ms (0.0 seconds)

PDB18C(5):[7385] Successfully onlined Undo Tablespace 2.

PDB18C(5):Undo initialization online undo segments: err:0 start: 351874276 end: 351874287 diff: 11 ms (0.0 seconds)

PDB18C(5):Database Characterset for PDB18C is AL32UTF8

PDB18C(5):JIT: pid 7385 requesting stop

PDB18C(5):While transitioning the pdb 5 to clean state, clearing all its abort bits in the control file.

PDB18C(5):Completed: alter pluggable database refresh mode none

PDB18C(5):alter pluggable database open

PDB18C(5):Undo initialization recovery: err:0 start: 351875056 end: 351875060 diff: 4 ms (0.0 seconds)

PDB18C(5):[7385] Successfully onlined Undo Tablespace 2.

PDB18C(5):Undo initialization online undo segments: err:0 start: 351875061 end: 351875085 diff: 24 ms (0.0 seconds)

PDB18C(5):Undo initialization finished serial:0 start:351875056 end:351875086 diff:30 ms (0.0 seconds)

Opatch validation is skipped for PDB PDB18C (con_id=5)

PDB18C(5):Adding new file#37 to file$(old file#60).             fopr-0, newblks-32000, oldblks-19200

PDB18C(5):Adding new file#38 to file$(old file#61).             fopr-0, newblks-47360, oldblks-15360

PDB18C(5):Adding new file#39 to file$(old file#62).             fopr-0, newblks-12800, oldblks-12800

PDB18C(5):Successfully created internal service PDB18C at open

Pluggable database PDB18C opened in upgrade mode

PDB18C(5):Completed: alter pluggable database open

PDB18C(5):alter pluggable database close immediate

Pluggable database PDB18C closed

PDB18C(5):Completed: alter pluggable database close immediate

PDB18C(5):alter pluggable database open

PDB18C(5):[7385] Successfully onlined Undo Tablespace 2.

PDB18C(5):Database Characterset for PDB18C is AL32UTF8

Pluggable database PDB18C opened read write

PDB18C(5):Completed: alter pluggable database open

Verify the Duplicated PDB

You can connect to CDB2 and verify that PDB18C has been created successfully and is open in read/write mode:

CDB2
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                       READ ONLY  NO

         5 PDB18C                         READ WRITE NO

As shown above, PDB18C is available in CDB2 and is open in READ WRITE mode.

Remove the Archived Redo Logs

After the duplication is complete, you can remove the archived redo logs that were transferred to CDB2 for the duplication operation.

[root@cdb2 ~]# cd /18c/remote_arch
[root@cdb2 remote_arch]# ls -l
-rw-r—– 1 oracle oinstall 24064 Jun 27 13:13 1_202_972296216.dbf
-rw-r—– 1 oracle oinstall  1024 Jun 27 13:13 1_203_972296216.dbf

[root@cdb2 remote_arch]# rm -rf /18c/remote_arch/*

Changing the PDB Name During Duplication

You can also specify a different name for the duplicated PDB in CDB2.

For example, the following command duplicates PDB18C from CDB1 and creates the PDB as PDB18C_NEW in CDB2:

DUPLICATE PLUGGABLE DATABASE Pdb18c as pdb18c_new TO CDB2 FROM ACTIVE DATABASE;

Oracle AI Database 26ai(23.26.3) - Controlling PDB Redo Generation with REDO_GENERATION_KBPS_MAX

Vahid Yousefzadeh — Mon, 28 Sep 2026 19:29:01 +0000

In a container database environment, one PDB can sometimes generate a huge amount of redo in a short period of time. This excessive redo generation can consume shared resources and potentially affect the performance of other PDBs.

In other words, one PDB can become a resource bully, while other PDBs become the victims.

Oracle AI Database 26ai introduces REDO_GENERATION_KBPS_MAX, a parameter that allows us to control the target redo generation rate of an individual PDB.

When a PDB exceeds the configured target, Oracle can throttle its sessions to keep the redo generation rate close to the configured limit.

To demonstrate this behavior, I used a simple CTAS operation and compared its execution time with and without a redo-generation limit.

First, I ran the operation without any redo-generation limit.

SQL> create table tb2 as select * from tb1;
Table created
Executed in 18.315 seconds

The operation completed in only 18.315 seconds. This session had generated more than 719,802 MB of redo:

SELECT round(value / 1024 / 1024) REDO_SIZE_KB
FROM v$sesstat st
JOIN v$statname sn
ON sn.statistic# = st.statistic#
WHERE st.sid = 221
AND sn.name = 'redo size';

REDO_SIZE_KB
 - - - - - - 
719802

This gives us a baseline for the workload.

Limiting Redo Generation
To demonstrate this feature, I wanted to strictly limit the PDB so that the CTAS operation would take at least three minutes to complete. I configured the PDB with a target maximum redo generation rate of 4,000 KB/s:

SQL> ALTER SYSTEM SET redo_generation_kbps_max = 4000;
System altered

I removed the table and ran exactly the same CTAS operation again:

SQL> drop table tb2;
Table dropped

SQL> create table tb2 as select * from tb1;
Table created
Executed in 190.017 seconds

The result was very different. The same operation that previously required:

18.315 seconds

now required:

190.017 seconds

That’s more than 10 times slower.

While the second CTAS was running, I checked the session wait event:

SQL> select event from v$session_wait where sid=221;
EVENT
 - - - - - - - - - - - - -
resmgr: redo throttle

This is the key observation in this experiment.

I I generated an AWR report, and the results were as follows:

84.7% of the database time was spent on resmgr: redo throttle.

This clearly shows that the session was spending most of its database time being throttled by Resource Manager because it exceeded the configured redo generation limit.

PostgreSQL Table Bloat and Defragmentation: Autovacuum vs VACUUM vs VACUUM FULL

Vahid Yousefzadeh — Mon, 28 Sep 2026 19:20:37 +0000

When rows are updated or deleted in PostgreSQL, the database does not immediately remove the old row versions from the table. Instead, PostgreSQL uses MVCC (Multi-Version Concurrency Control) and creates new row versions when necessary.

As a result, a table can contain dead tuples and occupy significantly more disk space than the amount required by its current live data.

In this article, we will use the same table and workload to demonstrate how PostgreSQL handles this situation with:

Autovacuum: Automatically removes dead tuples in the background and makes their space reusable. It does not require an exclusive table lock and normally does not shrink the table on disk.
Manual VACUUM: Does the same cleanup as autovacuum, but you start it manually. It removes dead tuples and makes space reusable, but normally does not shrink the table.It does not require an exclusive table lock.
VACUUM FULL: Rebuilds the table using only live rows and physically releases unused disk space. It requires an ACCESS EXCLUSIVE lock, blocking concurrent access to the table while it runs.
The important point is that these operations do not have the same effect on table size.

1. Initial Table

First, we create a table containing 2 million rows.


postgres=# CREATE TABLE tbl_fragmentation (
postgres(#     id          BIGSERIAL PRIMARY KEY,
postgres(#     name        TEXT,
postgres(#     description TEXT,
postgres(#     amount      NUMERIC(12,2),
postgres(#     created_at  TIMESTAMP DEFAULT now()
postgres(# );
CREATE TABLE


postgres=# INSERT INTO tbl_fragmentation (name, description, amount)
postgres-# SELECT
postgres-#     'User_' || gs,
postgres-#     repeat('X', 500),
postgres-#     (random() * 10000)::numeric(12,2)
postgres-# FROM generate_series(1, 2000000) AS gs;
INSERT 0 2000000
Time: 48542.364 ms (00:48.542)


postgres=# SELECT count(*) FROM tbl_fragmentation;
  count
---------
 2000000
(1 row)

The table initially occupies approximately 1116 MB.


postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)

At this point, the table contains 2 million live rows and there is no significant dead-tuple accumulation.

2. Table Defragmentation Using Autovacuum

PostgreSQL enables autovacuum by default.

postgres=# SHOW autovacuum;
 autovacuum
------------
 on

Now we delete 1.8 million rows.

postgres=# DELETE FROM tbl_fragmentation WHERE id <= 1800000;
DELETE 1800000
Time: 27884.584 ms (00:27.885)

Only 200,000 rows remain:

postgres=# SELECT count(*)FROM tbl_fragmentation;
 count
--------
 200000
(1 row)

However, the physical table size is still approximately 1116 MB.

postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)

A DELETE does not immediately return the occupied pages to the operating system. The deleted rows become dead tuples, and the space can later be reused by PostgreSQL.

Autovacuum detects the dead tuples and starts a vacuum operation.


postgres=# SELECT    pid,    backend_type,    state,    wait_event_type,    wait_event,    now() - query_start AS running_for,    query FROM pg_stat_activity WHERE backend_type LIKE 'autovacuum%' and state='active';
  pid  |   backend_type    | state  | wait_event_type | wait_event  |   running_for   |                    query
-------+-------------------+--------+-----------------+-------------+-----------------+---------------------------------------------
 14080 | autovacuum worker | active | Timeout         | VacuumDelay | 00:07:26.277602 | autovacuum: VACUUM public.tbl_fragmentation

We can check the table statistics:


SELECT
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup |         last_autovacuum          | autovacuum_count
------------+------------+----------------------------------+------------------
      19889 |          0 | 2026-09-15 22:17:42.548493+03:30 |                2

The important observation is that n_dead_tup is now zero after autovacuum.

However, the table size has not decreased.


postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)

This illustrates an important PostgreSQL behavior:

VACUUM makes space occupied by dead tuples reusable, but normally does not shrink the physical table file.

We can demonstrate this by inserting another 1.8 million rows.


 INSERT INTO tbl_fragmentation (name, description, amount)
 SELECT
     'User_' || gs,
     repeat('X', 500),
     (random() * 10000)::numeric(12,2)
 FROM generate_series(2000001, 3800000) AS gs;
INSERT 0 1800000


postgres=# select count(*) from tbl_fragmentation;
  count
---------
 2000000


postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |

The space that previously belonged to deleted rows was made available for reuse, so PostgreSQL could insert new rows without requiring the table file to grow.

The table statistics also show that the dead tuples were cleaned:


 SELECT
     n_live_tup,
     n_dead_tup,
     last_autovacuum,
     autovacuum_count
 FROM pg_stat_user_tables
 WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup |         last_autovacuum          | autovacuum_count
------------+------------+----------------------------------+------------------
    1999955 |          0 | 2026-09-15 22:27:41.696749+03:30 |                3
(1 row)

What happens with UPDATE?

UPDATE is particularly important because PostgreSQL normally implements an update by creating a new row version rather than modifying the existing row in place.

We now update 1.8 million rows:


postgres=# select count(*) from tbl_fragmentation;
  count
---------
 2000000
(1 row)



postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)



postgres=# UPDATE tbl_fragmentation
postgres-# SET description = repeat('Y', 500)
postgres-# WHERE id <= 3600000;
UPDATE 1800000
Time: 76530.884 ms (01:16.531)


postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 2121 MB |
(1 row)



postgres=# select count(*) from tbl_fragmentation;
  count
---------
 2000000
(1 row)



postgres=# SELECT    pid,    backend_type,    state,    wait_event_type,    wait_event,    now() - query_start AS running_for,    query FROM pg_stat_activity WHERE backend_type LIKE 'autovacuum%' and state='active';
  pid  |   backend_type    | state  | wait_event_type | wait_event  |   running_for   |                        query
-------+-------------------+--------+-----------------+-------------+-----------------+-----------------------------------------------------
 12604 | autovacuum worker | active | Timeout         | VacuumDelay | 00:08:47.232972 | autovacuum: VACUUM ANALYZE public.tbl_fragmentation
(1 row)



postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 2121 MB |
(1 row)



SELECT
     n_live_tup,
     n_dead_tup,
     last_autovacuum,
     autovacuum_count
 FROM pg_stat_user_tables
 WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup |         last_autovacuum          | autovacuum_count
------------+------------+----------------------------------+------------------
    2005317 |      53580 | 2026-09-15 22:45:08.785006+03:30 |                4
(1 row)

The key observation is that the update generated dead tuples. Autovacuum can clean these dead tuples and make their space reusable, but it does not normally compact the entire table and return the unused space to the operating system.

3. Manual VACUUM

Now we disable autovacuum for the test table so that we can manually control when vacuum occurs.


postgres=# DROP TABLE IF EXISTS tbl_fragmentation;
DROP TABLE

postgres=# CREATE TABLE tbl_fragmentation (
postgres(#     id          BIGSERIAL PRIMARY KEY,
postgres(#     name        TEXT,
postgres(#     description TEXT,
postgres(#     amount      NUMERIC(12,2),
postgres(#     created_at  TIMESTAMP DEFAULT now()
postgres(# );
CREATE TABLE

postgres=# ALTER TABLE tbl_fragmentation SET (autovacuum_enabled = false);
ALTER TABLE


postgres=# INSERT INTO tbl_fragmentation (name, description, amount)
postgres-# SELECT
postgres-#     'User_' || gs,
postgres-#     repeat('X', 500),
postgres-#     (random() * 10000)::numeric(12,2)
postgres-# FROM generate_series(1, 2000000) AS gs;
INSERT 0 2000000
Time: 49524.589 ms (00:49.525)


postgres=# SELECT count(*) FROM tbl_fragmentation;
  count
---------
 2000000
(1 row)

The table is approximately 1116 MB:


postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)

Now we update 1.8 million rows.


postgres=# UPDATE tbl_fragmentation
postgres-# SET description = repeat('Y', 500)
postgres-# WHERE id <= 1800000;
UPDATE 1800000
Time: 69229.523 ms (01:09.230)


postgres=# SELECT count(*) FROM tbl_fragmentation;
  count
---------
 2000000
(1 row)

The table grows to 2121 MB:


postgres=# \dt+
                                           List of tables
 Schema |       Name       | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 2121 MB |
(1 row)

The statistics show approximately 1.79 million dead tuples:


postgres=# SELECT
postgres-#     n_live_tup,
postgres-#     n_dead_tup,
postgres-#     last_vacuum,
postgres-#     vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup | last_vacuum | vacuum_count
------------+------------+-------------+--------------
    2006910 |    1793096 |             |            0
(1 row)

Now we manually execute:


postgres=# VACUUM tbl_fragmentation;
VACUUM
Time: 24971.367 ms (00:24.971)
After vacuum:

postgres=# SELECT
postgres-#     n_live_tup,
postgres-#     n_dead_tup,
postgres-#     last_vacuum,
postgres-#     vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup |           last_vacuum            | vacuum_count
------------+------------+----------------------------------+--------------
    1904225 |          0 | 2026-09-15 23:05:24.632805+03:30 |            1
(1 row)

The dead tuples have been cleaned. However, the table is still 2121 MB:


postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 2121 MB |
(1 row)

This is the same fundamental behavior we observed with autovacuum.

Manual VACUUM and autovacuum both reclaim dead-tuple space for reuse; neither is intended to compact the table file and return all unused space to the operating system.

Now we delete 1.8 million rows:

postgres=# DELETE FROM tbl_fragmentation WHERE id <= 1800000;
DELETE 1800000
Time: 29234.591 ms (00:29.235)
postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 2121 MB |
(1 row)


postgres=# SELECT
postgres-#     n_live_tup,
postgres-#     n_dead_tup,
postgres-#     last_vacuum,
postgres-#     vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup |           last_vacuum            | vacuum_count
------------+------------+----------------------------------+--------------
     105605 |    1800000 | 2026-09-15 23:22:02.167342+03:30 |            1
(1 row)


postgres=# VACUUM tbl_fragmentation;
VACUUM
Time: 25668.475 ms (00:25.668)

postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)

postgres=# SELECT count(*) FROM tbl_fragmentation;
 count
--------
 200000
(1 row)

The table size is shown as 1116 MB after this operation in the test output:

2121 MB →1116 MB

4. VACUUM FULL

VACUUM FULL has a fundamentally different purpose.

It physically rewrites the table and creates a compact version containing the live rows.

Again, we create the test table with autovacuum disabled and repeat the scenario:


postgres=# DROP TABLE IF EXISTS tbl_fragmentation;
DROP TABLE
Time: 269.546 ms
postgres=# CREATE TABLE tbl_fragmentation (
postgres(#     id          BIGSERIAL PRIMARY KEY,
postgres(#     name        TEXT,
postgres(#     description TEXT,
postgres(#     amount      NUMERIC(12,2),
postgres(#     created_at  TIMESTAMP DEFAULT now()
postgres(# );
CREATE TABLE
Time: 7.112 ms

postgres=# INSERT INTO tbl_fragmentation (name, description, amount)
postgres-# SELECT
postgres-#     'User_' || gs,
postgres-#     repeat('X', 500),
postgres-#     (random() * 10000)::numeric(12,2)
postgres-# FROM generate_series(1, 2000000) AS gs;
INSERT 0 2000000
Time: 48542.364 ms (00:48.542)
postgres=# SELECT count(*) FROM tbl_fragmentation;
  count
---------
 2000000
(1 row)


Time: 28598.005 ms (00:28.598)
postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)


postgres=# UPDATE tbl_fragmentation
postgres-# SET description = repeat('Y', 500)
postgres-# WHERE id <= 1800000;
UPDATE 1800000
Time: 67174.851 ms (01:07.175)

postgres=# SELECT count(*) FROM tbl_fragmentation;
  count
---------
 2000000
(1 row)


Time: 54238.243 ms (00:54.238)
postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 2121 MB |
(1 row)


postgres=# SELECT
postgres-#     n_live_tup,
postgres-#     n_dead_tup,
postgres-#     last_vacuum,
postgres-#     vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup | last_vacuum | vacuum_count
------------+------------+-------------+--------------
    1990950 |    1809056 |             |            0
(1 row)


Time: 1.124 ms

Now we execute VACUUM FULL command:

postgres=# VACUUM FULL tbl_fragmentation;
VACUUM
Time: 34663.258 ms (00:34.663)
After VACUUM FULL, the table is physically compacted:

postgres=# SELECT
postgres-#     n_live_tup,
postgres-#     n_dead_tup,
postgres-#     last_vacuum,
postgres-#     vacuum_count
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'tbl_fragmentation';
 n_live_tup | n_dead_tup | last_vacuum | vacuum_count
------------+------------+-------------+--------------
    1990950 |    1809056 |             |            0
(1 row)



postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)

In this particular test, the table returns to approximately its original size.

Now we delete 1.8 million rows again:


postgres=# DELETE FROM tbl_fragmentation WHERE id <= 1800000;
DELETE 1800000
Time: 29704.323 ms (00:29.704)
postgres=# SELECT count(*)FROM tbl_fragmentation;
 count
--------
 200000
(1 row)


Time: 25574.595 ms (00:25.575)
postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1116 MB |
(1 row)


postgres=# VACUUM FULL tbl_fragmentation;
VACUUM
Time: 4336.966 ms (00:04.337)
postgres=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+-------------------+-------+----------+-------------+---------------+--------+-------------
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 112 MB |
(1 row)

The table has now shrunk from approximately 1116 MB to 112 MB.

This is the main difference between normal VACUUM and VACUUM FULL.

VACUUM FULL physically rebuilds the table using only the remaining live rows, allowing PostgreSQL to release the unused disk space associated with the old table.

Space Reclamation and Execution Time Comparison

In this test, autovacuum took several minutes because it runs in the background and intentionally throttles its work to reduce its impact on normal database activity. Manual VACUUM completed much faster because it was started directly without waiting for autovacuum scheduling. VACUUM FULL also completed quickly in this test and physically compacted the table, significantly reducing its size.

Migrating PostgreSQL 14 to 18 Using pg_dumpall

Vahid Yousefzadeh — Mon, 28 Sep 2026 18:55:11 +0000

A major PostgreSQL version upgrade is not only a matter of installing the new server. The migration also requires a clear understanding of the existing environment, a verified backup, careful restoration, and post-upgrade validation. In this article, I walk through a PostgreSQL 14 to PostgreSQL 18 migration using pg_dumpall, based on a real environment.

1. Review the Current PostgreSQL 14 Environment

Before starting the upgrade, I record the current PostgreSQL version, data directory, databases, database sizes, tables, indexes, and installed extensions. These checks give me a reference point for validating the PostgreSQL 18 environment later.


--Current version:
postgres=# select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 14.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)


--data directory:
postgres=# show data_directory;
     data_directory
------------------------
 /var/lib/pgsql/14/data
(1 row)

--list of databases:
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 usef      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 vahid     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

The total database size is useful for estimating the amount of data involved in the migration and for checking whether the restored environment is in the expected range.

At first, I want to verify the total size of all databases:


postgres=# SELECT
    ROUND(SUM(pg_database_size(datname)) / 1024.0 / 1024.0, 2) AS total_size_mb
FROM pg_database;
 total_size_mb
---------------
       3494.09
(1 row)

It’s approximately 3.5GB, in addition, the following query shows exact size of each database:

postgres=# SELECT
    datname AS database_name,
    ROUND(pg_database_size(datname) / 1024.0 / 1024.0, 2) AS size_mb
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
 database_name | size_mb
---------------+---------
 vahid         | 3460.68
 usef          |    8.49
 postgres      |    8.38
 template1     |    8.23
 template0     |    8.23
(5 rows)

I then drill into the largest database and record the size of its tables and indexes. This also helps identify the objects that should be checked most carefully after the restore.

The biggest database is VAHID and we check the tables which are in this database:

postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".
vahid=# \d+
                                                List of relations
 Schema |           Name           |   Type   |  Owner   | Persistence | Access method |    Size    | Description
--------+--------------------------+----------+----------+-------------+---------------+------------+-------------
 public | tb_emsal                 | table    | postgres | permanent   | heap          | 2233 MB    |
 public | tbl_fragmentation        | table    | postgres | permanent   | heap          | 1116 MB    |
 public | tbl_fragmentation_id_seq | sequence | postgres | permanent   |               | 8192 bytes |
(3 rows)

vahid=# \di+
                                                     List of relations
 Schema |          Name          | Type  |  Owner   |       Table       | Persistence | Access method | Size  | Description
--------+------------------------+-------+----------+-------------------+-------------+---------------+-------+-------------
 public | indname                | index | postgres | tbl_fragmentation | permanent   | btree         | 60 MB |
 public | tbl_fragmentation_pkey | index | postgres | tbl_fragmentation | permanent   | btree         | 43 MB |
(2 rows)

Moreover, we can examine tables and indexes which are in USEF database:

vahid=# \c usef
You are now connected to database "usef" as user "postgres".
usef=# \dt+
                                  List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method | Size  | Description
--------+------+-------+----------+-------------+---------------+-------+-------------
 public | tbl  | table | postgres | permanent   | heap          | 16 kB |
(1 row)

usef=# \di+
                                        List of relations
 Schema |   Name   | Type  |  Owner   | Table | Persistence | Access method | Size  | Description
--------+----------+-------+----------+-------+-------------+---------------+-------+-------------
 public | indname  | index | postgres | tbl   | permanent   | btree         | 16 kB |
 public | tbl_pkey | index | postgres | tbl   | permanent   | btree         | 16 kB |
(2 rows)

usef=# select * from tbl;
 id | fname |    lname
----+-------+-------------
  1 | Vahid | Yousefzadeh
(1 row)

Finally, I check the POSTGRES database:

postgres=# \dt+
                                    List of relations
 Schema |  Name   | Type  |  Owner   | Persistence | Access method | Size  | Description
--------+---------+-------+----------+-------------+---------------+-------+-------------
 public | person  | table | postgres | permanent   | heap          | 16 kB |
 public | tb_1405 | table | postgres | permanent   | heap          | 16 kB |
(2 rows)

postgres=# \di+
                                          List of relations
 Schema |    Name     | Type  |  Owner   | Table  | Persistence | Access method | Size  | Description
--------+-------------+-------+----------+--------+-------------+---------------+-------+-------------
 public | person_pkey | index | postgres | person | permanent   | btree         | 16 kB |
(1 row)

postgres=# select * from person;
 id |       name
----+-------------------
  1 | Vahid Yousefzadeh
(1 row)

2.Check Installed Extensions

Extensions deserve a separate check because they are installed per database. A successful database restore does not remove the need to verify that the required extension objects are available in the target PostgreSQL installation.

[postgres@OEL98-PG ~]$ psql
psql (14.19)
Type "help" for help.

postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".
vahid=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

vahid=# \c usef
You are now connected to database "usef" as user "postgres".
usef=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

3. Take the Logical Backup

Once the baseline has been recorded, I stop application activity and take the final logical backup. For this migration, pg_dumpall is used to capture all databases and cluster-level objects supported by the logical dump.

pg_dumpall --verbose >>/backup/ALL_Databases_14.sql

During the backup process, pg_dumpall displays the objects and data being processed. The following is a sample of the output:

pg_dump: creating DATABASE "vahid"
pg_dump: connecting to new database "vahid"
pg_dump: creating TABLE "public.tb_emsal"
pg_dump: creating TABLE "public.tbl_fragmentation"
pg_dump: creating SEQUENCE "public.tbl_fragmentation_id_seq"
pg_dump: creating SEQUENCE OWNED BY "public.tbl_fragmentation_id_seq"
pg_dump: creating DEFAULT "public.tbl_fragmentation id"
pg_dump: processing data for table "public.tb_emsal"
pg_dump: dumping contents of table "public.tb_emsal"
pg_dump: processing data for table "public.tbl_fragmentation"
pg_dump: dumping contents of table "public.tbl_fragmentation"

The backup was successfully created, and its size is approximately 3.2 GB:

[postgres@OEL98-PG ~]$ ls -lh /backup/ALL_Databases_14.sql
-rw-r--r--. 1 postgres postgres 3.2G Sep 28 18:57 /backup/ALL_Databases_14.sql

4.Stop PostgreSQL 14

After the backup is complete, PostgreSQL 14 is stopped. Keeping the old data directory untouched at this stage is important because it provides a rollback point while PostgreSQL 18 is being validated.

There are two ways to stop the PostgreSQL 14 instance. The first is to use pg_ctl directly with the PostgreSQL 14 data directory:

[postgres@OEL98-PG ~]$ pg_ctl -D /var/lib/pgsql/14/data -l logfile stop
waiting for server to shut down.... done
server stopped

Alternatively, if PostgreSQL 14 is managed by systemd, we can stop the service using:

[root@OEL98-PG ~]# systemctl stop postgresql-14
[root@OEL98-PG ~]# systemctl status postgresql-14
○ postgresql-14.service - PostgreSQL 14 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; disabled; preset: disabled)
     Active: inactive (dead)
       Docs: https://www.postgresql.org/docs/14/static/

Sep 28 19:01:11 OEL98-PG systemd[1]: Starting PostgreSQL 14 database server...
Sep 28 19:01:11 OEL98-PG postmaster[2163]: 2026-09-28 19:01:11.296 +03 [2163] LOG:  redirecting log output to logging collector process
Sep 28 19:01:11 OEL98-PG postmaster[2163]: 2026-09-28 19:01:11.296 +03 [2163] HINT:  Future log output will appear in directory "log".
Sep 28 19:01:11 OEL98-PG systemd[1]: Started PostgreSQL 14 database server.
Sep 28 19:01:15 OEL98-PG systemd[1]: Stopping PostgreSQL 14 database server...
Sep 28 19:01:15 OEL98-PG systemd[1]: postgresql-14.service: Killing process 2164 (postmaster) with signal SIGKILL.
Sep 28 19:01:15 OEL98-PG systemd[1]: postgresql-14.service: Deactivated successfully.
Sep 28 19:01:15 OEL98-PG systemd[1]: Stopped PostgreSQL 14 database server.

5.Install and Initialize PostgreSQL 18

PostgreSQL 18 was installed and initialized beforehand. At this stage, I switch to the PostgreSQL 18 environment and verify the configuration, including the PostgreSQL version, data directory, and listening port.

This confirms that the new PostgreSQL 18 instance is ready for the migration and subsequent restoration of the backup.


[postgres@OEL98-PG ~]$ export PG_VERSION=18
[postgres@OEL98-PG ~]$ export PGHOME=/usr/pgsql-18
[postgres@OEL98-PG ~]$ export PGDATA=/var/lib/pgsql/18/data
[postgres@OEL98-PG ~]$ export PGPORT=5418
[postgres@OEL98-PG ~]$ export PATH=$PGHOME/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin


[postgres@OEL98-PG ~]$ psql
psql (18.6)
Type "help" for help.

postgres=# select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)


postgres=# show data_directory;
     data_directory
------------------------
 /var/lib/pgsql/18/data
(1 row)


postgres=# show port;
 port
------
 5418
(1 row)

6.Restore the PostgreSQL 14 Backup

The backup is now restored into PostgreSQL 18. Because this is a logical migration, PostgreSQL 18 recreates the databases, tables, sequences, and indexes from the dump rather than reusing the PostgreSQL 14 data directory.

Note: Server configuration files such as postgresql.conf and pg_hba.conf are separate from the logical dump. They should therefore be reviewed and migrated deliberately rather than assumed to be included in pg_dumpall.

The backup is restored using the PostgreSQL 18 psql executable:

/usr/pgsql-18/bin/psql -f /backup/ALL_Databases_14.sql

During the restore, psql executes the SQL statements generated by pg_dumpall. The following is a sample of the output:

You are now connected to database "vahid" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 4000000
COPY 2000000
 setval
---------
 2000000
(1 row)

ALTER TABLE
CREATE INDEX

7. Validate PostgreSQL 18

After restoring the backup, I verify the PostgreSQL 18 environment against the information collected from PostgreSQL 14. This includes checking the PostgreSQL version, confirming that the expected databases exist, and comparing their sizes.

[postgres@OEL98-PG ~]$ psql
psql (18.6)
Type "help" for help.


postgres=# select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)


postgres=# \l
                                                     List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
           |          |          |                 |             |             |        |           | postgres=CTc/postgres
 usef      | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
 vahid     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
(5 rows)

The expected databases are present in PostgreSQL 18. I then check their sizes:

postgres=# SELECT
    datname AS database_name,
    ROUND(pg_database_size(datname) / 1024.0 / 1024.0, 2) AS size_mb
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
 database_name | size_mb
---------------+---------
 vahid         | 3460.72
 postgres      |    7.59
 usef          |    7.59
 template1     |    7.56
 template0     |    7.49
(5 rows)

Finally, I verify the actual objects and sample data. For a production migration, this validation should also include representative row counts, application connectivity, critical queries, permissions, and scheduled jobs.

postgres=# \d
              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 public | person        | table    | postgres
 public | person_id_seq | sequence | postgres
 public | tb_1405       | table    | postgres
(3 rows)

postgres=# select * from person;
 id |       name
----+-------------------
  1 | Vahid Yousefzadeh
(1 row)


postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".
vahid=# \dt+
                                           List of tables
 Schema |       Name        | Type  |  Owner   | Persistence | Access method |  Size   | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
 public | tb_emsal          | table | postgres | permanent   | heap          | 2233 MB |
 public | tbl_fragmentation | table | postgres | permanent   | heap          | 1117 MB |
(2 rows)

vahid=# \di+
                                                      List of indexes
 Schema |          Name          | Type  |  Owner   |       Table       | Persistence | Access method | Size  | Description
--------+------------------------+-------+----------+-------------------+-------------+---------------+-------+-------------
 public | indname                | index | postgres | tbl_fragmentation | permanent   | btree         | 60 MB |
 public | tbl_fragmentation_pkey | index | postgres | tbl_fragmentation | permanent   | btree         | 43 MB |
(2 rows)

vahid=# \c usef
You are now connected to database "usef" as user "postgres".
usef=# \dt+
                                    List of tables
 Schema | Name | Type  |  Owner   | Persistence | Access method | Size  | Description
--------+------+-------+----------+-------------+---------------+-------+-------------
 public | tbl  | table | postgres | permanent   | heap          | 16 kB |
(1 row)

usef=# \di+
                                         List of indexes
 Schema |   Name   | Type  |  Owner   | Table | Persistence | Access method | Size  | Description
--------+----------+-------+----------+-------+-------------+---------------+-------+-------------
 public | indname  | index | postgres | tbl   | permanent   | btree         | 16 kB |
 public | tbl_pkey | index | postgres | tbl   | permanent   | btree         | 16 kB |
(2 rows)

usef=# select * from tbl;
 id | fname |    lname
----+-------+-------------
  1 | Vahid | Yousefzadeh
(1 row)

Oracle Deep Data Security: Data Grants

Vahid Yousefzadeh — Mon, 28 Sep 2026 05:20:25 +0000

Last week, I published an article about Oracle Deep Data Security (Deep Sec) in Oracle AI Database 26ai, where I covered End Users and Data Roles and demonstrated how they work together. In this article, I want to continue from that point and focus on Data Grants.

1. What is a Data Grant?

A Data Grant is one of the main mechanisms in Oracle Deep Data Security for defining fine-grained access to data.

Instead of simply granting access to an entire table:

GRANT SELECT ON table TO user;

a Data Grant can define which rows a user can access, which columns are available, and which operations the user can perform, such as SELECT, UPDATE, or DELETE.

2. Test Environment

For the practical tests, I created an ADMIN.DATABASE_ACCESS_REQUESTS table containing access requests from several end users. This is the main table used throughout the examples.

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_NAME PDB_NAME        ENVIRONMENT          ACCESS_TYPE       REQUEST_STATUS       REQUESTED
---------- ---------- ------------- --------------- -------------------- ----------------- -------------------- ---------
      1001 VAHID_EU      PRODDB        PARS_PDB        PRODUCTION           DBA               APPROVED             01-SEP-26
      1002 VAHID_EU      PRODDB        DERAZKASH_PDB   PRODUCTION           SELECT ANY TABLE  APPROVED             02-SEP-26
      1003 RAMZON_EU     PRODDB        MAZANDARAN_PDB  PRODUCTION           RESOURCE          APPROVED             02-SEP-26
      1004 SARA_EU       TESTDB        NEKA_PDB        TEST                 DEVELOPER_ROLE    APPROVED             03-SEP-26
      1005 REZA_EU       PRODDB        EZZARON_PDB     PRODUCTION           READ ONLY         PENDING              04-SEP-26
      1006 VAHID_EU      TESTDB        DERAZKASH_PDB   TEST                 DEVELOPER_ROLE    APPROVED             05-SEP-26
      1007 ALI_EU        PRODDB        MARZIKOLA_PDB   PRODUCTION           READ WRITE        PENDING              06-SEP-26
      1008 RAMZON_EU     PRODDB        BABOL_PDB       PRODUCTION           DBA               APPROVED             07-SEP-26
      1009 REZA_EU       TESTDB        MAZANDARAN_PDB  TEST                 DEVELOPER_ROLE    REJECTED             08-SEP-26
      1010 VAHID_EU      PRODDB        DERAZKOLAH_PDB  PRODUCTION           READ ONLY         APPROVED             09-SEP-26

3. Create the End Users and Roles

I first create VAHID_EU, the Data Role, and the standard database role used in the examples. The same Data Role is then assigned to the other end users.

SQL> create end user vahid_eu identified by a;
End user created.

SQL> CREATE DATA ROLE db_data_role_vahid;
Data role created.

SQL> CREATE ROLE db_standard_role;
Role created.

SQL> GRANT CREATE SESSION TO db_standard_role;
Grant succeeded.

SQL> GRANT db_standard_role TO db_data_role_vahid;
Grant succeeded.

SQL> GRANT DATA ROLE db_data_role_vahid TO vahid_EU;
Grant succeeded.

In addition, I will create three other users that will be useful for some of the examples later in this article:

SQL> create end user RAMZON_EU identified by r;
End user created.
SQL> GRANT DATA ROLE db_data_role_vahid TO RAMZON_EU;
Grant succeeded.

SQL> create end user ALI_EU identified by a;
End user created.
SQL> GRANT DATA ROLE db_data_role_vahid TO ALI_EU;
Grant succeeded.

SQL> create end user SARA_EU identified by s;
End user created.
SQL> GRANT DATA ROLE db_data_role_vahid TO SARA_EU;
Grant succeeded.

4. Row-Level Access

4.1 Start with No Data Grant

Before creating a Data Grant, VAHID_EU cannot access the table:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.

SQL> select * from admin.database_access_requests;
ERROR at line 1:
ORA-00942: table or view "ADMIN"."DATABASE_ACCESS_REQUESTS" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/

This gives us a clear starting point: the end user has the Data Role, but there is no Data Grant allowing access to this table.

4.2 Restrict Access to a User’s Rows

Now, I will give VAHID_EU SELECT access only to the rows related to this user.

SQL> conn admin/a@OL10:1521/pdb1
Connected.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS SELECT
ON admin.database_access_requests
WHERE username='VAHID_EU'
TO VAHID_EU;  
Data grant created.

When VAHID_EU queries the table, only rows where USERNAME is VAHID_EU are returned.

SQL> conn admin/a@OL10:1521/pdb1
Connected.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS SELECT
ON admin.database_access_requests
WHERE username='VAHID_EU'
TO VAHID_EU;  
Data grant created.

After creating the Data Grant, I will connect again as VAHID_EU and query the table:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_N PDB_NAME        ENVIRONMENT     ACCESS_TYPE          REQUEST_STATUS       REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
      1001 VAHID_EU   PRODDB     PARS_PDB        PRODUCTION      DBA                  APPROVED             01-SEP-26
      1002 VAHID_EU   PRODDB     DERAZKASH_PDB   PRODUCTION      SELECT ANY TABLE     APPROVED             02-SEP-26
      1006 VAHID_EU   TESTDB     DERAZKASH_PDB   TEST            DEVELOPER_ROLE       APPROVED             05-SEP-26
      1010 VAHID_EU   PRODDB     DERAZKOLAH_PDB  PRODUCTION      READ ONLY            APPROVED             09-SEP-26

4.3 Add More Conditions

The Data Grant can be made more restrictive by combining conditions. For example, the following grant allows VAHID_EU to see only its rows from the TEST environment.

SQL> conn admin/a@OL10:1521/pdb1
Connected.

SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS SELECT
ON admin.database_access_requests
WHERE username='VAHID_EU' and ENVIRONMENT='TEST'
TO VAHID_EU;

Data grant created.

After connecting as VAHID_EU again:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_N PDB_NAME        ENVIRONMENT     ACCESS_TYPE          REQUEST_STATUS       REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
      1006 VAHID_EU   TESTDB     DERAZKASH_PDB   TEST            DEVELOPER_ROLE       APPROVED             05-SEP-26

Now, only the row that satisfies both conditions is returned.

5. Column-Level Access

Data Grants can also control which columns are available to an end user. For example, we can exclude specific columns while also applying row conditions.

SQL> conn admin/a@OL10:1521/pdb1
Connected.

SQL>  drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS SELECT(ALL COLUMNS EXCEPT ACCESS_TYPE,REQUEST_STATUS)
ON admin.database_access_requests
WHERE REQUEST_ID>=1006 and REQUESTED_DATE >= '04-SEP-26'
TO VAHID_EU;

Data grant created.

When VAHID_EU queries the table, the Data Grant controls both the rows and the columns available to the user.

6. Controlling UPDATE and DELETE

Data Grants are not limited to SELECT. They can also control DML operations such as UPDATE and DELETE.

SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.


SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS delete,update,select
ON admin.database_access_requests
WHERE username='VAHID_EU' and ENVIRONMENT='TEST'
TO VAHID_EU;
Data grant created.

6.1 UPDATE

The following UPDATE has no WHERE clause, but only the row permitted by the Data Grant is affected:

SQL> update admin.database_access_requests set REQUEST_STATUS='REJECTED' ;

1 row updated.

We can verify the result:

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_N PDB_NAME        ENVIRONMENT     ACCESS_TYPE          REQUEST_STATUS       REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
      1006 VAHID_EU   TESTDB     DERAZKASH_PDB   TEST            DEVELOPER_ROLE       REJECTED             05-SEP-26

The REQUEST_STATUS of the accessible row has changed to REJECTED.

Press enter or click to view image in full size

6.2 DELETE

The same principle applies to DELETE:

SQL> delete admin.database_access_requests;

1 row deleted.

Only the row accessible through the Data Grant is deleted. Rows outside that scope are not affected.

SQL>  select * from admin.database_access_requests;

no rows selected

7. Column-Specific DML Privileges

A Data Grant can also specify different column permissions for different operations. In this example, VAHID_EU can update ENVIRONMENT and ACCESS_TYPE, while SELECT can expose ENVIRONMENT, ACCESS_TYPE, and REQUEST_STATUS.

SQL>  conn admin/a@OL10:1521/pdb1
Connected.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS UPDATE (ENVIRONMENT, ACCESS_TYPE), 
SELECT(ENVIRONMENT,ACCESS_TYPE,REQUEST_STATUS)
ON admin.database_access_requests
WHERE username='VAHID_EU'
TO VAHID_EU;
Data grant created.

For example, updating ACCESS_TYPE is allowed because that column is included in the UPDATE portion of the Data Grant:

UPDATE admin.database_access_requests
SET ACCESS_TYPE='DBA';

4 rows updated.

This example demonstrates that row-level and column-level restrictions can be combined with operation-specific permissions.

8. Using ORA_END_USER_CONTEXT for Multiple End Users

In the previous examples, the Data Grant was created specifically for VAHID_EU. However, we can also create a single Data Grant that works for multiple end users.

For this purpose, I will use ORA_END_USER_CONTEXT.USERNAME. This allows the Data Grant to identify the current end user and return only the rows that belong to that user.

First, I will connect as ADMIN and verify the original data:

SQL> conn admin/a@OL10:1521/pdb1
Connected.
SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_N PDB_NAME        ENVIRONMENT     ACCESS_TYPE          REQUEST_STATUS       REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
      1001 VAHID_EU   PRODDB     PARS_PDB        PRODUCTION      DBA                  APPROVED             01-SEP-26
      1002 VAHID_EU   PRODDB     DERAZKASH_PDB   PRODUCTION      SELECT ANY TABLE     APPROVED             02-SEP-26
      1003 RAMZON_EU  PRODDB     MAZANDARAN_PDB  PRODUCTION      RESOURCE             APPROVED             02-SEP-26
      1004 SARA_EU    TESTDB     NEKA_PDB        TEST            DEVELOPER_ROLE       APPROVED             03-SEP-26
      1005 REZA_EU    PRODDB     EZZARON_PDB     PRODUCTION      READ ONLY            PENDING              04-SEP-26
      1006 VAHID_EU   TESTDB     DERAZKASH_PDB   TEST            DEVELOPER_ROLE       APPROVED             05-SEP-26
      1007 ALI_EU     PRODDB     MARZIKOLA_PDB   PRODUCTION      READ WRITE           PENDING              06-SEP-26
      1008 RAMZON_EU  PRODDB     BABOL_PDB       PRODUCTION      DBA                  APPROVED             07-SEP-26
      1009 REZA_EU    TESTDB     MAZANDARAN_PDB  TEST            DEVELOPER_ROLE       REJECTED             08-SEP-26
      1010 VAHID_EU   PRODDB     DERAZKOLAH_PDB  PRODUCTION      READ ONLY            APPROVED             09-SEP-26

10 rows selected.

The table contains requests for different end users.

Now, I will create the Data Grant:

SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS DELETE,SELECT, 
UPDATE (ACCESS_TYPE,REQUEST_STATUS)
ON admin.database_access_requests
WHERE username = ORA_END_USER_CONTEXT.username
TO VAHID_EU,RAMZON_EU,SARA_EU,ALI_EU;  

Data grant created.

The important part of this Data Grant is:

WHERE username = ORA_END_USER_CONTEXT.username

Instead of specifying one particular username, the condition uses the username of the currently connected end user.

Now, I will connect as each end user and query the table.

For VAHID_EU:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_N PDB_NAME        ENVIRONMENT     ACCESS_TYPE          REQUEST_STATUS       REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
      1001 VAHID_EU   PRODDB     PARS_PDB        PRODUCTION      DBA                  APPROVED             01-SEP-26
      1002 VAHID_EU   PRODDB     DERAZKASH_PDB   PRODUCTION      SELECT ANY TABLE     APPROVED             02-SEP-26
      1006 VAHID_EU   TESTDB     DERAZKASH_PDB   TEST            DEVELOPER_ROLE       APPROVED             05-SEP-26
      1010 VAHID_EU   PRODDB     DERAZKOLAH_PDB  PRODUCTION      READ ONLY            APPROVED             09-SEP-26

VAHID_EU can see only the rows belonging to VAHID_EU.

For SARA_EU:

Press enter or click to view image in full size

For RAMZON_EU:

The two rows belonging to RAMZON_EU are returned.

Finally, for ALI_EU:

9. When Standard Database Privileges Can Bypass the Data Grant(USE DATA GRANTS ONLY)

By default, Data Grants are applied in addition to standard database privileges. This means that if a privilege is granted directly to an end user through a database role, the user can use that privilege even when a Data Grant imposes a more restrictive condition.

For example, I remove the existing Data Grant and grant SELECT ANY TABLE through the standard role:

SQL> drop DATA GRANT admin.Dgran_dar_tbl;
Data grant dropped.

SQL> GRANT select any table TO db_standard_role;
Grant succeeded.

SQL> GRANT db_standard_role TO db_data_role_vahid;
Grant succeeded.

SQL> GRANT DATA ROLE db_data_role_vahid TO vahid_EU;
Grant succeeded.

VAHID_EU can now query ADMIN.DATABASE_ACCESS_REQUESTS without a row-level Data Grant restriction:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.
SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_NAME PDB_NAME        ENVIRONMENT          ACCESS_TYPE       REQUEST_STATUS       REQUESTED
---------- ---------- ------------- --------------- -------------------- ----------------- -------------------- ---------
      1001 VAHID_EU      PRODDB        PARS_PDB        PRODUCTION           DBA               APPROVED             01-SEP-26
      1002 VAHID_EU      PRODDB        DERAZKASH_PDB   PRODUCTION           SELECT ANY TABLE  APPROVED             02-SEP-26
      1003 RAMZON_EU     PRODDB        MAZANDARAN_PDB  PRODUCTION           RESOURCE          APPROVED             02-SEP-26
      1004 SARA_EU       TESTDB        NEKA_PDB        TEST                 DEVELOPER_ROLE    APPROVED             03-SEP-26
      1005 REZA_EU       PRODDB        EZZARON_PDB     PRODUCTION           READ ONLY         PENDING              04-SEP-26
      1006 VAHID_EU      TESTDB        DERAZKASH_PDB   TEST                 DEVELOPER_ROLE    APPROVED             05-SEP-26
      1007 ALI_EU        PRODDB        MARZIKOLA_PDB   PRODUCTION           READ WRITE        PENDING              06-SEP-26
      1008 RAMZON_EU     PRODDB        BABOL_PDB       PRODUCTION           DBA               APPROVED             07-SEP-26
      1009 REZA_EU       TESTDB        MAZANDARAN_PDB  TEST                 DEVELOPER_ROLE    REJECTED             08-SEP-26
      1010 VAHID_EU      PRODDB        DERAZKOLAH_PDB  PRODUCTION           READ ONLY         APPROVED             09-SEP-26

The query returns all rows from the table, including rows belonging to other end users.

Now, let’s create a Data Grant that restricts VAHID_EU to rows where the USERNAME matches the current end user:

SQL> conn admin/a@OL10:1521/pdb1
Connected.

SQL> CREATE OR REPLACE DATA GRANT admin.Dgran_dar_tbl
AS DELETE,SELECT, UPDATE (ACCESS_TYPE,REQUEST_STATUS)
ON admin.database_access_requests
WHERE username = ORA_END_USER_CONTEXT.username
TO VAHID_EU;
Data grant created.

After creating the Data Grant, I will connect again as VAHID_EU and query the table:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.

SQL> select * from admin.database_access_requests;

REQUEST_ID USERNAME   DATABASE_NAME PDB_NAME        ENVIRONMENT          ACCESS_TYPE       REQUEST_STATUS       REQUESTED
---------- ---------- ------------- --------------- -------------------- ----------------- -------------------- ---------
      1001 VAHID_EU      PRODDB        PARS_PDB        PRODUCTION           DBA               APPROVED             01-SEP-26
      1002 VAHID_EU      PRODDB        DERAZKASH_PDB   PRODUCTION           SELECT ANY TABLE  APPROVED             02-SEP-26
      1003 RAMZON_EU     PRODDB        MAZANDARAN_PDB  PRODUCTION           RESOURCE          APPROVED             02-SEP-26
      1004 SARA_EU       TESTDB        NEKA_PDB        TEST                 DEVELOPER_ROLE    APPROVED             03-SEP-26
      1005 REZA_EU       PRODDB        EZZARON_PDB     PRODUCTION           READ ONLY         PENDING              04-SEP-26
      1006 VAHID_EU      TESTDB        DERAZKASH_PDB   TEST                 DEVELOPER_ROLE    APPROVED             05-SEP-26
      1007 ALI_EU        PRODDB        MARZIKOLA_PDB   PRODUCTION           READ WRITE        PENDING              06-SEP-26
      1008 RAMZON_EU     PRODDB        BABOL_PDB       PRODUCTION           DBA               APPROVED             07-SEP-26
      1009 REZA_EU       TESTDB        MAZANDARAN_PDB  TEST                 DEVELOPER_ROLE    REJECTED             08-SEP-26
      1010 VAHID_EU      PRODDB        DERAZKOLAH_PDB  PRODUCTION           READ ONLY         APPROVED             09-SEP-26

The result is still the complete table. The Data Grant does not restrict the rows because VAHID_EU already has the SELECT ANY TABLE database privilege.

This demonstrates an important point: by default, Data Grants are additive with standard database privileges. A direct database privilege can therefore provide access beyond the restrictions defined in a Data Grant.

Enforcing Data Grants
USE DATA GRANTS ONLY is particularly useful when we want to ensure that the row- and column-level restrictions defined by Data Grants cannot be bypassed by other database privileges or alternative access paths such as views.

If we want the Data Grant to be mandatory for a specific table or view, we can enable USE DATA GRANTS ONLY.

I will enable it on ADMIN.DATABASE_ACCESS_REQUESTS:

SQL> conn admin/a@OL10:1521/pdb1
Connected.

SQL> SET USE DATA GRANTS ONLY ON admin.database_access_requests ENABLED;
Use data grants only set.

With this setting enabled, access to the table by Deep Sec end users must be authorized through a Data Grant. Standard database privileges such as SELECT ANY TABLE are no longer sufficient for accessing this object.

Now, I will connect again as VAHID_EU:

SQL> conn vahid_eu/a@OL10:1521/pdb1
Connected.

SQL> select * from admin.database_access_requests;
REQUEST_ID USERNAME   DATABASE_N PDB_NAME        ENVIRONMENT     ACCESS_TYPE          REQUEST_STATUS       REQUESTED
---------- ---------- ---------- --------------- --------------- -------------------- -------------------- ---------
      1001 VAHID_EU   PRODDB     PARS_PDB        PRODUCTION      DBA                  APPROVED             01-SEP-26
      1002 VAHID_EU   PRODDB     DERAZKASH_PDB   PRODUCTION      SELECT ANY TABLE     APPROVED             02-SEP-26
      1006 VAHID_EU   TESTDB     DERAZKASH_PDB   TEST            DEVELOPER_ROLE       APPROVED             05-SEP-26
      1010 VAHID_EU   PRODDB     DERAZKOLAH_PDB  PRODUCTION      READ ONLY            APPROVED             09-SEP-26

As you can see, even though VAHID_EU has the SELECT ANY TABLE privilege, the access to this table is now controlled by the Data Grant.

This is the main purpose of USE DATA GRANTS ONLY: it enables mandatory access control for the specified table or view. For Deep Sec users, the database privilege alone is no longer enough; the required access must be provided through a Data Grant.

We can disable this feature when it is no longer required:

SQL>  SET USE DATA GRANTS ONLY ON admin.database_access_requests DISABLED;
Use data grants only set.

After disabling the setting, the normal access behavior is restored, and standard database privileges can again provide access to the object.

PDB switchover

Vahid Yousefzadeh — Fri, 25 Sep 2026 18:47:54 +0000

The procedure for remotely refreshing a PDB in Oracle Database 12c was previously discussed in Creating and Refreshing a PDB Remotely. Oracle Database 18c introduced a new capability in this area that enables the roles of the source and destination PDBs to be switched, in other words, it facilitates a switchover between the two PDBs.

In this article, we first create a PDB remotely and then demonstrate how to perform a switchover between the source and destination PDBs.

1. Create a User, PDB, and Database Link on the Source and Destination

cdb1 (source)

SQL> create pluggable database pdbsource admin user u identified by u;

Pluggable database created.

SQL> alter pluggable database PDBSOURCE  open;

Pluggable database altered.

SQL> create user db_usef  identified by pass;

User created.

SQL> grant sysoper,dba,sysdba to db_usef container=all;

Grant succeeded.

SQL> create public database link LINKclone  connect to DB_USEF identified by pass  using ‘cdb18c’;

Database link created.

cdb2 (Destination)

SQL> create user db_usef  identified by pass;

User created.

SQL> grant sysdba,dba to db_usef container=all;

Grant succeeded.

SQL> create public database link LINK_SOURCE  connect to DB_USEF identified by pass  using ‘db18c’;

Database link created.

2. Create the PDB Remotely on cdb2

cdb2 (Destination)

SQL> CREATE PLUGGABLE DATABASE pdbclone FROM PDBSOURCE@LINK_SOURCE CREATE_FILE_DEST=’/u01/18c_oradata/CDB18C’  REFRESH MODE EVERY 1 MINUTES;

Pluggable database created.

3. Create a User and Table in PDBSOURCE for Testing

cdb1 (source)

SQL> alter session set container=PDBSOURCE;

Session altered.

SQL> create user usef identified by a;

User created.

SQL> grant dba to usef;

Grant succeeded.

SQL> create table usef.tbl1(last_name varchar2(9));

Table created.

SQL> insert into usef.tbl1 values(‘USEFZADEH’);

1 row created.

SQL> commit;

Commit complete.

The data in this table is also available in PDBCLONE after the refresh operation, which is performed every minute.

cdb2 (Destination)

SQL> alter pluggable database pdbclone open read only;

Pluggable database altered.

SQL> alter session set container=pdbclone;

Session altered.

SQL>  select last_name from usef.tbl1;

LAST_NAME

———

USEFZADEH

SQL>  alter session set container=cdb$root;

Session altered.

SQL> alter pluggable database pdbclone close;

Pluggable database altered.

4. Perform a Switchover Between PDBSOURCE and PDBCLONE

The goal is to switch the roles of PDBSOURCE and PDBCLONE so that PDBCLONE becomes the primary PDB and can accept changes in read/write mode, while PDBSOURCE can be used for reporting purposes when required.

Perform the following steps.

cdb2 (Destination)


SQL> alter pluggable database pdbclone open read only;

Pluggable database altered.

cdb1 (source)

SQL> alter session set container=PDBSOURCE;

Session altered.

The general syntax of the SWITCHOVER operation is:

ALTER PLUGGABLE DATABASE refresh_mode FROM clonepdb@dblink SWITCHOVER;

In this environment, execute the following command:

SQL> ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM pdbclone@linkclone SWITCHOVER;

Pluggable database altered.

The switchover completes successfully. The following commands demonstrate the resulting roles of the two PDBs.

cdb2 (clone)

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         7 PDBCLONE                       READ WRITE NO

cdb1 (source)

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         4 PDBSOURCE                      MOUNTED

During the switchover operation, the following messages can also be observed in the alert log.

cdb1 (source)

2018-04-29T16:46:31.417366+04:30

PDBSOURCE(4):ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM pdbclone@LINKclone SWITCHOVER

2018-04-29T16:46:31.809754+04:30

PDBSOURCE(4):JIT: pid 22466 requesting stop

PDBSOURCE(4):Buffer Cache flush started: 4

PDBSOURCE(4):Buffer Cache flush finished: 4

2018-04-29T16:46:32.170496+04:30

PDBSOURCE(4):While transitioning the pdb 4 to clean state, clearing all its abort bits in the control file.

Pluggable database PDBSOURCE closed

2018-04-29T16:46:35.836492+04:30

Deleted Oracle managed file /u01/18c_oradata/DB18C/6AFC495838351172E0530288200A1577/datafile/o1_mf_temp_fgcfdd7k_.dbf

Deleted Oracle managed file /u01/18c_oradata/DB18C/6AFC495838351172E0530288200A1577/datafile/o1_mf_undotbs1_fgcfdd7j_.dbf

Deleted Oracle managed file /u01/18c_oradata/DB18C/6AFC495838351172E0530288200A1577/datafile/o1_mf_sysaux_fgcfdd7h_.dbf

Deleted Oracle managed file /u01/18c_oradata/DB18C/6AFC495838351172E0530288200A1577/datafile/o1_mf_system_fgcfdd7f_.dbf

2018-04-29T16:46:38.386672+04:30

Opatch validation is skipped for PDB PDBSOURCE (con_id=4)

2018-04-29T16:46:40.648340+04:30

PDBSOURCE(4):Endian type of dictionary set to little

****************************************************************

Pluggable Database PDBSOURCE with pdb id – 4 is created as UNUSABLE.

If any errors are encountered before the pdb is marked as NEW,

then the pdb must be dropped

local undo-1, localundoscn-0x00000000000000fb

****************************************************************

2018-04-29T16:46:41.872741+04:30

Applying media recovery for pdb-4099 from SCN 7824847 to SCN 7825420

Remote log information: count-1

thr-1, seq-52, logfile-/u01/arch/parlog_1_52_6363a705_974028799.arc, los-7791131, nxs-18446744073709551615

PDBSOURCE(4):Media Recovery Start

2018-04-29T16:46:41.875469+04:30

PDBSOURCE(4):Serial Media Recovery started

PDBSOURCE(4):max_pdb is 8

2018-04-29T16:46:41.922623+04:30

PDBSOURCE(4):Media Recovery Log /u01/arch/parlog_1_52_6363a705_974028799.arc

2018-04-29T16:46:42.249189+04:30

PDBSOURCE(4):Incomplete Recovery applied until change 7825420 time 04/29/2018 16:45:01

2018-04-29T16:46:42.250989+04:30

PDBSOURCE(4):Media Recovery Complete (db18c)

PDBSOURCE(4):Completed: ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM pdbclone@LINKclone SWITCHOVER

2018-04-29T16:46:42.310450+04:30

PDBSOURCE(4):alter pluggable database refresh

cdb2 (clone)

2018-04-29T16:44:43.238462+04:30

PDBCLONE(7):alter pluggable database refresh

PDBCLONE(7):Completed: alter pluggable database refresh

2018-04-29T16:44:52.219206+04:30

PDBCLONE(7):Opatch XML is skipped for PDB PDBCLONE (conid=7)

PDBCLONE(7): AUDSYS.AUD$UNIFIED (SQL_TEXT) – CLOB populated

PDBCLONE(7):JIT: pid 6896 requesting stop

PDBCLONE(7):Buffer Cache flush started: 7

PDBCLONE(7):Buffer Cache flush finished: 7

Pluggable database PDBCLONE closed

2018-04-29T16:44:55.325901+04:30

Applying media recovery for pdb-4099 from SCN 7824084 to SCN 7824621

Remote log information: count-1

thr-1, seq-158, logfile-/u01/arch/parlog_1_158_26eb8840_972296216.arc, los-7815768, nxs-18446744073709551615

PDBCLONE(7):Media Recovery Start

2018-04-29T16:44:55.327301+04:30

PDBCLONE(7):Serial Media Recovery started

PDBCLONE(7):max_pdb is 7

PDBCLONE(7):WARNING: process USER (ospid: 6896) was unable to attach SMR.

2018-04-29T16:44:55.371061+04:30

PDBCLONE(7):ORA-27300: OS system dependent operation:open failed with status: 2

ORA-27301: OS failure message: No such file or directory

ORA-27302: failure occurred at: sskgmsmr_7

2018-04-29T16:44:55.384489+04:30

PDBCLONE(7):Media Recovery Log /u01/arch/parlog_1_158_26eb8840_972296216.arc

2018-04-29T16:44:55.891033+04:30

PDBCLONE(7):Incomplete Recovery applied until change 7824621 time 04/29/2018 16:46:33

2018-04-29T16:44:55.893566+04:30

PDBCLONE(7):Media Recovery Complete (cdb18c)

PDBCLONE(7):Undo initialization recovery: err:0 start: 1567231498 end: 1567231507 diff: 9 ms (0.0 seconds)

PDBCLONE(7):[6896] Successfully onlined Undo Tablespace 2.

PDBCLONE(7):Undo initialization online undo segments: err:0 start: 1567231508 end: 1567231520 diff: 12 ms (0.0 seconds)

PDBCLONE(7):Undo initialization finished serial:0 start:1567231498 end:1567231522 diff:24 ms (0.0 seconds)

PDBCLONE(7):Database Characterset for PDBCLONE is AL32UTF8

PDBCLONE(7):Buffer Cache flush started: 7

PDBCLONE(7):Buffer Cache flush finished: 7

2018-04-29T16:44:56.270556+04:30

PDBCLONE(7):While transitioning the pdb 7 to clean state, clearing all its abort bits in the control file.

2018-04-29T16:44:56.756133+04:30

PDBCLONE(7):Undo initialization recovery: err:0 start: 1567232232 end: 1567232233 diff: 1 ms (0.0 seconds)

PDBCLONE(7):[6896] Successfully onlined Undo Tablespace 2.

PDBCLONE(7):Undo initialization online undo segments: err:0 start: 1567232233 end: 1567232286 diff: 53 ms (0.1 seconds)

PDBCLONE(7):Undo initialization finished serial:0 start:1567232232 end:1567232289 diff:57 ms (0.1 seconds)

Opatch validation is skipped for PDB PDBCLONE (con_id=7)

PDBCLONE(7):Deleting old file#43 from file$

PDBCLONE(7):Deleting old file#44 from file$

PDBCLONE(7):Deleting old file#45 from file$

PDBCLONE(7):Adding new file#28 to file$(old file#43).             fopr-1, newblks-32000, oldblks-19200

PDBCLONE(7):Adding new file#29 to file$(old file#44).             fopr-1, newblks-44800, oldblks-15360

PDBCLONE(7):Adding new file#30 to file$(old file#45).             fopr-1, newblks-12800, oldblks-12800

PDBCLONE(7):Successfully created internal service PDBCLONE at open

****************************************************************

Post plug operations are now complete.

Pluggable database PDBCLONE with pdb id – 7 is now marked as NEW.

****************************************************************

PDBCLONE(7):Database Characterset for PDBCLONE is AL32UTF8

2018-04-29T16:44:57.367254+04:30

PDBCLONE(7):JIT: pid 6896 requesting full stop

2018-04-29T16:44:58.275048+04:30

Violations: Type: 2, Count: 3

PDBCLONE(7):***************************************************************

PDBCLONE(7):WARNING: Pluggable Database PDBCLONE with pdb id – 7 is

PDBCLONE(7):         altered with errors or warnings. Please look into

PDBCLONE(7):         PDB_PLUG_IN_VIOLATIONS view for more details.

PDBCLONE(7):***************************************************************

2018-04-29T16:44:58.788594+04:30

PDBCLONE(7):Opening pdb with no Resource Manager plan active

Pluggable database PDBCLONE opened read write

2018-04-29T16:44:59.007884+04:30

PDBCLONE(7):Opatch XML is skipped for PDB PDBCLONE (conid=7)

PDBCLONE(7): AUDSYS.AUD$UNIFIED (SQL_TEXT) – CLOB populated