DEV Community: umzzil nng

PostgreSQL HV00P Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 27 Jul 2026 00:03:07 +0000

PostgreSQL Error HV00P: fdw_no_schemas Explained

The PostgreSQL error HV00P (fdw_no_schemas) occurs when a Foreign Data Wrapper (FDW) operation attempts to retrieve or enumerate schemas from a remote server but receives an empty or inaccessible schema list. This typically surfaces during IMPORT FOREIGN SCHEMA commands or FDW metadata introspection. While the connection to the foreign server may succeed, the schema-level discovery fails — usually due to permission issues or misconfiguration rather than network problems.

Top 3 Causes

1. Insufficient Privileges on the Remote Schema

The most common cause is that the user mapped in the FDW user mapping lacks USAGE privileges on the remote schema. The FDW driver cannot enumerate schemas it has no access to, resulting in an empty schema list that triggers HV00P.

-- Check current user mappings
SELECT srvname, umuser::regrole, umoptions
FROM pg_user_mappings;

-- On the REMOTE server: grant required privileges
GRANT USAGE ON SCHEMA target_schema TO fdw_user;
GRANT SELECT ON ALL TABLES IN SCHEMA target_schema TO fdw_user;

-- Apply to future tables as well
ALTER DEFAULT PRIVILEGES IN SCHEMA target_schema
  GRANT SELECT ON TABLES TO fdw_user;

2. Incorrect or Non-Existent Schema Name

Specifying a schema name that doesn't exist on the remote server — due to a typo, case mismatch, or a schema that was dropped — will cause the FDW to return no results, triggering this error.

-- Verify available remote schemas via dblink before importing
SELECT schema_name
FROM dblink(
  'host=remote_host port=5432 dbname=remote_db user=fdw_user password=secret',
  'SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE ''pg_%'''
) AS t(schema_name TEXT);

-- Use the confirmed correct schema name
IMPORT FOREIGN SCHEMA "verified_schema_name"
  FROM SERVER my_foreign_server
  INTO local_schema;

3. FDW Driver Version Mismatch or Incomplete Implementation

Some third-party FDW drivers do not fully implement schema enumeration. A version mismatch between the FDW extension and the remote server can also return an empty schema list, which PostgreSQL interprets as HV00P.

-- Check installed FDW extension versions
SELECT extname, extversion
FROM pg_extension
WHERE extname LIKE '%fdw%';

-- Update the FDW extension
ALTER EXTENSION postgres_fdw UPDATE;

-- Recreate the foreign server with correct options
DROP SERVER IF EXISTS my_foreign_server CASCADE;

CREATE SERVER my_foreign_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (
    host 'remote_host',
    port '5432',
    dbname 'remote_db',
    use_remote_estimate 'true'
  );

-- Recreate user mapping
CREATE USER MAPPING FOR current_user
  SERVER my_foreign_server
  OPTIONS (user 'fdw_user', password 'secure_password');

Quick Fix Solutions

If you need to resolve this immediately in production, use the following diagnostic sequence:

-- Step 1: Confirm foreign server configuration
SELECT srvname, srvoptions FROM pg_foreign_server;

-- Step 2: Test connection and verify remote schema list
SELECT * FROM dblink(
  'host=remote_host dbname=remote_db user=fdw_user password=secret',
  'SELECT schema_name FROM information_schema.schemata'
) AS t(schema_name TEXT);

-- Step 3: Re-run the import with the correct schema name
IMPORT FOREIGN SCHEMA "public"
  FROM SERVER my_foreign_server
  INTO local_fdw_schema;

Prevention Tips

Automate permission validation before deploying FDW configurations. Include a pre-deployment check that verifies the remote user has USAGE on the target schema. Add this to your CI/CD pipeline or runbooks.
Wrap IMPORT FOREIGN SCHEMA in exception-handling blocks to catch HV00P gracefully and log meaningful error messages instead of letting it bubble up as an unhandled failure in applications.

-- Graceful error handling example
DO $$
BEGIN
  IMPORT FOREIGN SCHEMA "public"
    FROM SERVER my_foreign_server
    INTO local_schema;
EXCEPTION
  WHEN fdw_no_schemas THEN
    RAISE WARNING 'No schemas found. Check remote schema name and user permissions.';
END;
$$;

Related Errors

Error Code	Name	Description
HV000	fdw_error	Generic FDW error parent class
HV00B	fdw_invalid_handle	Invalid FDW connection handle
HV00R	fdw_unable_to_create_reply	Cannot create reply from remote server
HV009	fdw_invalid_use_of_null_pointer	Null pointer in FDW internals, often with driver bugs

Oracle ORA-01720 Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 27 Jul 2026 00:01:35 +0000

ORA-01720: grant option does not exist for

ORA-01720 occurs when a user attempts to grant privileges on an object (typically a view) to another user, but the grantor does not hold the required WITH GRANT OPTION on the underlying base objects. This error is especially common in multi-schema environments where views reference tables owned by a different schema. Without WITH GRANT OPTION on all base tables, the privilege chain cannot be extended to third parties.

Top 3 Causes

1. Missing GRANT OPTION on View's Base Table

When a view is created using a table from another schema, the view owner must have received WITH GRANT OPTION on that base table. If not, granting SELECT on the view to a third user will trigger ORA-01720.

-- SCHEMA_B owns the base table
-- SCHEMA_A creates a view on it

-- Problematic: B granted SELECT to A without GRANT OPTION
GRANT SELECT ON schema_b.employees TO schema_a;  -- No WITH GRANT OPTION

-- SCHEMA_A tries to grant SELECT on the view to SCHEMA_C
-- This will raise ORA-01720
GRANT SELECT ON schema_a.emp_view TO schema_c;

-- Check GRANTABLE column to diagnose
SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE
FROM   DBA_TAB_PRIVS
WHERE  GRANTEE = 'SCHEMA_A'
  AND  TABLE_NAME = 'EMPLOYEES';
-- GRANTABLE = 'NO' confirms the problem

2. Re-granting Privileges Received Without GRANT OPTION

Any privilege granted without WITH GRANT OPTION is non-transferable. If a user received SELECT on a table directly and tries to pass it along, Oracle blocks it with ORA-01720.

-- DBA grants without WITH GRANT OPTION
GRANT SELECT ON hr.departments TO app_user;

-- app_user attempts to re-grant — this fails with ORA-01720
GRANT SELECT ON hr.departments TO report_user;

-- Fix: DBA must revoke and re-grant with GRANT OPTION
REVOKE SELECT ON hr.departments FROM app_user;
GRANT SELECT ON hr.departments TO app_user WITH GRANT OPTION;

-- Now app_user can successfully grant to others
GRANT SELECT ON hr.departments TO report_user;

3. Synonym-Based View With Indirect Object References

When a view references objects through a synonym, the privilege check still applies to the actual base object — not the synonym. If WITH GRANT OPTION is absent on the real table, ORA-01720 will occur.

-- Find base objects referenced by the view
SELECT NAME,
       REFERENCED_OWNER,
       REFERENCED_NAME,
       REFERENCED_TYPE
FROM   DBA_DEPENDENCIES
WHERE  NAME  = 'EMP_VIEW'
  AND  OWNER = 'SCHEMA_A'
  AND  REFERENCED_TYPE IN ('TABLE', 'SYNONYM');

-- Verify GRANT OPTION on all referenced objects
SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE
FROM   DBA_TAB_PRIVS
WHERE  GRANTEE = 'SCHEMA_A'
  AND  TABLE_NAME IN (
         SELECT REFERENCED_NAME
         FROM DBA_DEPENDENCIES
         WHERE NAME = 'EMP_VIEW' AND OWNER = 'SCHEMA_A'
       );

Quick Fix Solutions

Step 1 — Identify the broken privilege chain:

-- Find views missing GRANT OPTION on their base tables
SELECT D.OWNER, D.NAME AS VIEW_NAME,
       D.REFERENCED_OWNER, D.REFERENCED_NAME,
       NVL(P.GRANTABLE, 'MISSING') AS STATUS
FROM   DBA_DEPENDENCIES D
LEFT JOIN DBA_TAB_PRIVS P
       ON  P.GRANTEE    = D.OWNER
       AND P.OWNER      = D.REFERENCED_OWNER
       AND P.TABLE_NAME = D.REFERENCED_NAME
       AND P.PRIVILEGE  = 'SELECT'
WHERE  D.TYPE = 'VIEW'
  AND  D.REFERENCED_TYPE = 'TABLE'
  AND  NVL(P.GRANTABLE, 'NO') = 'NO';

Step 2 — Fix by re-granting with GRANT OPTION:

-- Base table owner executes this
REVOKE SELECT ON schema_b.employees FROM schema_a;
GRANT SELECT ON schema_b.employees TO schema_a WITH GRANT OPTION;

-- View owner can now propagate the privilege
GRANT SELECT ON schema_a.emp_view TO schema_c;

Prevention Tips

1. Always document and enforce GRANT OPTION requirements at design time.
Before deploying any cross-schema view, confirm that all base table owners have granted privileges WITH GRANT OPTION. Maintain a privilege matrix and run a monthly audit script to detect missing GRANT OPTIONs before they cause runtime errors.

2. Use Role-based access control to reduce direct GRANT complexity.
Rather than building long privilege chains between individual users, centralize permissions through roles. This minimizes the need for WITH GRANT OPTION and makes privilege management far easier to audit and maintain. Note that role-based privileges are not active inside stored procedures or views — for those contexts, direct grants with WITH GRANT OPTION remain necessary.

Related errors: **ORA-01031* (insufficient privileges during object access), ORA-01749 (cannot grant privileges to yourself during privilege restructuring).*

PostgreSQL HV001 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 26 Jul 2026 14:03:06 +0000

PostgreSQL Error HV001: FDW Out of Memory

The HV001: fdw out of memory error occurs when a Foreign Data Wrapper (FDW) extension in PostgreSQL fails to allocate the memory it needs while communicating with or processing data from an external data source. This typically happens during large data fetches, complex joins involving foreign tables, or when too many FDW connections are held open simultaneously. Understanding the root cause is essential because this error can silently degrade query performance before it fully fails.

Top 3 Causes and Fixes

1. Insufficient `work_mem` for FDW Query Processing

When PostgreSQL retrieves data from a foreign table, it performs local sorting, joining, and aggregation that consumes work_mem. The default 4MB is far too low for large result sets from remote servers.

-- Check current work_mem
SHOW work_mem;

-- Increase work_mem for the current session before running FDW queries
SET work_mem = '256MB';

-- Use LOCAL to scope it to a single transaction
BEGIN;
SET LOCAL work_mem = '512MB';
SELECT * FROM foreign_sales WHERE sale_date > NOW() - INTERVAL '30 days';
COMMIT;

-- Verify memory-heavy FDW queries with EXPLAIN ANALYZE
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM foreign_orders WHERE status = 'OPEN';

Fix: Increase work_mem at the session level before executing heavy FDW queries, or tune it globally in postgresql.conf if the workload consistently demands it.

2. FDW Connection Leaks and Too Many Concurrent Connections

Each open FDW connection consumes memory on the PostgreSQL server. Applications that don't properly close transactions or that open many parallel FDW sessions can exhaust available memory over time.

-- Find long-running or idle FDW-related sessions
SELECT pid, usename, state, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state IN ('idle', 'active')
  AND query_start < NOW() - INTERVAL '10 minutes'
ORDER BY duration DESC;

-- Terminate idle connections older than 20 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND query_start < NOW() - INTERVAL '20 minutes';

-- Disable persistent FDW connections to free memory after each transaction
ALTER SERVER my_remote_server OPTIONS (SET keep_connections 'false');

Fix: Set keep_connections to false on the foreign server definition and use a connection pooler like PgBouncer to cap the total number of FDW connections.

3. Fetching Too Much Data at Once (Missing `fetch_size` Tuning)

By default, postgres_fdw fetches rows in batches of 100. However, complex queries can still buffer enormous result sets in memory. Not configuring fetch_size appropriately leads to memory spikes.

-- Check current fetch_size on foreign tables
SELECT relname, ftoptions
FROM pg_foreign_table ft
JOIN pg_class c ON ft.ftrelid = c.oid;

-- Set fetch_size at the foreign table level
ALTER FOREIGN TABLE foreign_transactions OPTIONS (SET fetch_size '500');

-- Process large foreign datasets in chunks to avoid memory pressure
DO $$
DECLARE
    v_offset INT := 0;
    v_batch  INT := 5000;
    v_rows   INT;
BEGIN
    LOOP
        INSERT INTO local_transactions_staging
        SELECT * FROM foreign_transactions
        LIMIT v_batch OFFSET v_offset;

        GET DIAGNOSTICS v_rows = ROW_COUNT;
        EXIT WHEN v_rows < v_batch;
        v_offset := v_offset + v_batch;
    END LOOP;
END;
$$;

Fix: Tune fetch_size to a reasonable batch size and always process large foreign datasets in paginated chunks rather than a single unbounded query.

Quick Prevention Tips

Monitor FDW session memory regularly. Schedule a periodic check using pg_stat_activity to catch connection leaks early and alert before memory is fully exhausted.

-- Quick health check for FDW session count
SELECT count(*), state
FROM pg_stat_activity
WHERE query ~* 'foreign'
GROUP BY state;

Always wrap FDW queries in explicit transactions and commit or rollback promptly. This ensures FDW cursors and associated memory buffers are released immediately, preventing gradual memory accumulation especially in long-running batch jobs.

Related Error Codes

Code	Name	Relation
`HV000`	FDW Error (general)	Parent class of HV001
`53200`	Out of Memory	Server-level OOM, often co-occurs with HV001
`08006`	Connection Failure	Can follow HV001 when memory exhaustion prevents new connections
`HV00B`	FDW Invalid Handle	Another FDW resource management error

Oracle ORA-01719 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 26 Jul 2026 14:01:30 +0000

ORA-01719: Outer Join Operator (+) Not Allowed in OR or IN Operands

ORA-01719 is thrown when Oracle's legacy outer join operator (+) is used within an OR or IN condition in a WHERE clause. Oracle's traditional (+) syntax has strict limitations — it cannot be combined with OR branching logic or IN lists because of how the Oracle parser processes outer join conditions internally. The fix is straightforward: migrate to ANSI-standard JOIN syntax, which removes all such restrictions.

Top 3 Causes

1. Using `(+)` with an `OR` Condition

The most common cause is mixing (+) outer join conditions with OR in the WHERE clause.

-- Triggers ORA-01719
SELECT e.employee_id, d.department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id(+)
OR     e.location_id   = d.location_id(+);

-- Fix: Use ANSI LEFT OUTER JOIN
SELECT e.employee_id, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON    (e.department_id = d.department_id
       OR e.location_id = d.location_id);

2. Using `(+)` Inside an `IN` Clause

Applying (+) to a column used in an IN condition also raises this error because Oracle internally expands IN into multiple OR predicates.

-- Triggers ORA-01719
SELECT e.employee_id, d.department_name
FROM   employees e, departments d
WHERE  e.department_id(+) IN (10, 20, 30);

-- Fix: Switch join direction and use WHERE for IN filtering
SELECT e.employee_id, d.department_name
FROM   departments d
LEFT OUTER JOIN employees e
ON     e.department_id = d.department_id
WHERE  d.department_id IN (10, 20, 30);

3. Inadvertently Adding `OR` to a Legacy Query Containing `(+)`

When maintaining legacy code, developers often append OR conditions to an existing WHERE clause that already uses (+), unknowingly triggering this error.

-- Triggers ORA-01719 (OR added to existing outer join query)
SELECT e.employee_id, e.employee_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id(+)
AND    e.status = 'ACTIVE'
OR     d.dept_code = 'HQ';           -- This breaks it!

-- Fix: Use ANSI JOIN and move all conditions cleanly
SELECT e.employee_id, e.employee_name, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON     e.department_id = d.department_id
WHERE  e.status = 'ACTIVE'
OR     d.dept_code = 'HQ';

Quick Fix Solutions

Option 1 — ANSI JOIN (Recommended): Replace all (+) syntax with LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN. This is the cleanest, most portable solution.

Option 2 — UNION ALL: Split OR branches into separate queries joined with UNION ALL, each using (+) independently.

-- Splitting OR into UNION ALL
SELECT e.employee_id, d.department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id(+)
UNION ALL
SELECT e.employee_id, d.department_name
FROM   employees e, departments d
WHERE  e.location_id = d.location_id(+);

Option 3 — CTE Refactoring: Break complex queries into CTEs to isolate join logic from filter conditions.

WITH joined_data AS (
    SELECT e.employee_id, e.employee_name, d.department_name, d.dept_code
    FROM   employees e
    LEFT OUTER JOIN departments d
    ON     e.department_id = d.department_id
)
SELECT employee_id, employee_name, department_name
FROM   joined_data
WHERE  employee_id < 200
OR     dept_code = 'HQ';

Prevention Tips

Adopt ANSI JOIN syntax as a team standard. Ban the (+) operator in your SQL coding guidelines and enforce it through code reviews or SQL linting tools in your CI/CD pipeline. ANSI syntax is more readable, portable, and free from the restrictions that cause ORA-01719.
Always audit the full WHERE clause before adding new conditions to legacy queries. Before appending any OR condition, scan the entire predicate for (+) operators. Include an outer join syntax check in your code review checklist to catch issues before they reach testing or production.

Related Errors

ORA-01417 — A table may be outer joined to at most one other table. Commonly encountered alongside ORA-01719 when working with complex (+) syntax.
ORA-01416 — Two tables cannot be outer joined to each other (circular outer join).
ORA-00905 — Missing keyword; can appear when (+) syntax is malformed before ORA-01719 is even reached.

PostgreSQL HV014 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 26 Jul 2026 10:03:05 +0000

PostgreSQL HV014: fdw_too_many_handles — Causes, Fixes & Prevention

What Is HV014?

The PostgreSQL error HV014 (fdw_too_many_handles) occurs when a Foreign Data Wrapper (FDW) exceeds the maximum number of handles it is allowed to open simultaneously. Each FDW connection or cursor internally allocates a handle to manage communication with the external data source, and when these handles are not properly released or too many concurrent sessions use FDW simultaneously, this error is triggered. It can affect any FDW implementation including postgres_fdw, oracle_fdw, and ODBC-based wrappers.

Top 3 Causes

1. FDW Connection Handle Leaks

Handles are created each time an FDW query opens a connection or cursor. If transactions are not properly committed or rolled back, or if cursors are left open, handles accumulate over time without being released.

-- Check for long-running or idle FDW-related sessions
SELECT pid, usename, state, query_start, state_change, left(query, 80) AS query_snippet
FROM pg_stat_activity
WHERE query ILIKE '%foreign%' OR query ILIKE '%fdw%'
ORDER BY query_start ASC;

-- Terminate idle FDW connections older than 10 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '10 minutes';

2. Too Many Concurrent FDW Sessions

Without proper connection pooling, each application thread or session opens its own FDW handle. As concurrent users grow, so does handle consumption, quickly hitting the FDW limit.

-- Check how many connections are actively using FDW servers
SELECT usename, COUNT(*) AS session_count, state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, state
ORDER BY session_count DESC;

-- Disconnect all cached postgres_fdw connections to reset handles
SELECT postgres_fdw_disconnect_all();

3. Insufficient Handle Limits in FDW Server Configuration

Some FDW drivers impose their own handle pool limits. A mismatch between max_connections on the PostgreSQL instance and the FDW server's allowed connections, or an undersized driver-level handle pool, can trigger HV014.

-- Review current FDW server configurations
SELECT s.srvname, s.srvoptions, f.fdwname
FROM pg_foreign_server s
JOIN pg_foreign_data_wrapper f ON f.oid = s.srvfdw;

-- Optimize postgres_fdw server options to reduce handle pressure
ALTER SERVER my_foreign_server
OPTIONS (SET fetch_size '500');

-- Enable connection reuse to reduce handle churn
ALTER USER MAPPING FOR current_user
SERVER my_foreign_server
OPTIONS (ADD keep_connections 'on');

Quick Fix Solutions

Disconnect and reset FDW handles immediately:

-- Disconnect a specific FDW server's cached connections
SELECT postgres_fdw_disconnect('my_foreign_server');

-- Reset all FDW server connections at once
SELECT postgres_fdw_disconnect_all();

Always use explicit transactions and close cursors:

BEGIN;
DECLARE my_fdw_cursor CURSOR FOR
    SELECT order_id, total FROM foreign_orders WHERE status = 'PENDING';

FETCH 100 FROM my_fdw_cursor;
-- process rows...
CLOSE my_fdw_cursor;  -- Always explicitly close cursors
COMMIT;

Reduce per-query handle lifetime with fetch_size tuning:

-- Reduce the number of rows fetched per round trip
ALTER FOREIGN TABLE foreign_orders OPTIONS (SET fetch_size '200');

Prevention Tips

1. Automate idle connection cleanup with pg_cron:

-- Schedule automatic cleanup of idle connections every 5 minutes
SELECT cron.schedule(
    'fdw-handle-cleanup',
    '*/5 * * * *',
    $$
    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE state = 'idle'
      AND state_change < NOW() - INTERVAL '5 minutes'
      AND backend_type = 'client backend';
    $$
);

2. Deploy connection pooling and enforce FDW query best practices:

Use PgBouncer in transaction pooling mode to cap the number of real PostgreSQL sessions. Always select only the columns you need from foreign tables, keep FDW queries inside short transactions, and avoid using SELECT * over foreign tables in high-concurrency environments.

-- Example: Targeted query to minimize handle hold time
SELECT order_id, order_date, total_amount
FROM foreign_orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
  AND status = 'COMPLETED';

Related Errors

HV000 – fdw_error: Generic FDW error, often a precursor to HV014.
HV001 – fdw_out_of_memory: Memory exhaustion during FDW operations, can co-occur with handle leaks.
HV00B – fdw_invalid_handle: Accessing an already-released or invalid handle.
53300 – too_many_connections: Instance-level connection limit exceeded, commonly occurs alongside FDW handle exhaustion.

Oracle ORA-01710 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 26 Jul 2026 10:01:24 +0000

ORA-01710: duplicate keyword — Causes, Fixes, and Prevention

What Is ORA-01710?

ORA-01710 is a syntax error thrown by the Oracle parser when the same keyword appears more than once within a single SQL statement where it is only permitted once. This commonly occurs in DDL statements like CREATE TABLE or CREATE INDEX, privilege commands like GRANT, or complex DML queries where clauses are accidentally duplicated. The fix is straightforward once you identify the offending keyword — simply remove the duplicate occurrence.

Top 3 Causes

1. Duplicate Storage or Physical Attribute Keywords in DDL

When writing CREATE TABLE or CREATE INDEX statements, repeating storage-related keywords such as PCTFREE, INITIAL, or INITRANS triggers ORA-01710. This often happens when merging scripts from different sources or copy-pasting DDL blocks.

-- ❌ Incorrect: PCTFREE specified twice
CREATE TABLE orders (
    order_id   NUMBER(10),
    order_date DATE
)
PCTFREE 10
PCTUSED 40
PCTFREE 20;   -- Duplicate keyword → ORA-01710

-- ✅ Correct: each keyword appears only once
CREATE TABLE orders (
    order_id   NUMBER(10),
    order_date DATE
)
PCTFREE 10
PCTUSED 40
INITRANS 2;

2. Duplicate Option Keywords in GRANT Statements

Using WITH GRANT OPTION or WITH ADMIN OPTION more than once in a single GRANT statement will raise ORA-01710. This typically results from scripting bugs or manual copy-paste errors in privilege management scripts.

-- ❌ Incorrect: WITH GRANT OPTION duplicated
GRANT SELECT ON hr.employees TO app_user
WITH GRANT OPTION
WITH GRANT OPTION;   -- ORA-01710

-- ✅ Correct
GRANT SELECT ON hr.employees TO app_user
WITH GRANT OPTION;

-- ❌ Incorrect: WITH ADMIN OPTION duplicated
GRANT CREATE SESSION TO reporting_user
WITH ADMIN OPTION
WITH ADMIN OPTION;   -- ORA-01710

-- ✅ Correct
GRANT CREATE SESSION TO reporting_user
WITH ADMIN OPTION;

3. Duplicate Clauses in SELECT Statements (Dynamic SQL)

Repeating clauses like ORDER BY or GROUP BY in a single query — often introduced by dynamic SQL builders in application code — will also cause ORA-01710.

-- ❌ Incorrect: ORDER BY appears twice
SELECT employee_id, last_name, salary
FROM   employees
WHERE  department_id = 50
ORDER BY salary DESC
ORDER BY last_name ASC;   -- ORA-01710

-- ✅ Correct: combine sort criteria in one ORDER BY
SELECT employee_id, last_name, salary
FROM   employees
WHERE  department_id = 50
ORDER BY salary DESC, last_name ASC;

-- ❌ Incorrect: GROUP BY duplicated
SELECT department_id, COUNT(*) AS headcount
FROM   employees
GROUP BY department_id
GROUP BY department_id;   -- ORA-01710

-- ✅ Correct
SELECT department_id, COUNT(*) AS headcount
FROM   employees
GROUP BY department_id;

Quick Fix Solutions

Read the full error message carefully — Oracle usually highlights the position of the problem token, making it easier to locate the duplicate.
Search and replace — In your SQL editor, search for the suspected keyword (e.g., PCTFREE, WITH GRANT OPTION, ORDER BY) and count its occurrences in the statement.
Use EXPLAIN PLAN before executing long scripts to catch syntax errors without side effects.

-- Pre-validate syntax with EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT department_id, COUNT(*)
FROM   employees
GROUP BY department_id
GROUP BY department_id;   -- ORA-01710 caught before actual execution

Prevention Tips

Lint your SQL scripts before execution using tools like Oracle SQL Developer, Toad, or SQLcl. Their built-in syntax checkers detect duplicate keywords before any damage is done.
Use SQL Builder patterns in application code — When generating dynamic SQL programmatically (Java, Python, etc.), use builder classes or flag variables to ensure each clause is appended only once. This prevents ORA-01710 and other syntax errors at the source.

Related Oracle Errors

Error Code	Description
ORA-00905	Missing keyword — the opposite problem (too few keywords)
ORA-00936	Missing expression — another common syntax error
ORA-00907	Missing right parenthesis — often appears alongside DDL issues

PostgreSQL HV009 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 26 Jul 2026 06:03:07 +0000

PostgreSQL Error HV009: fdw_invalid_use_of_null_pointer

HV009 is a PostgreSQL error that occurs within the Foreign Data Wrapper (FDW) subsystem when a NULL pointer is dereferenced or used where a valid pointer is expected. This typically surfaces when FDW objects such as Foreign Servers, User Mappings, or Foreign Tables are misconfigured or improperly initialized. If you're working with extensions like postgres_fdw, oracle_fdw, or mysql_fdw, this error is one you'll want to understand thoroughly.

Top 3 Causes

1. Missing or Incomplete Foreign Server / User Mapping Configuration

The most common trigger is a Foreign Server or User Mapping that lacks required options. The FDW layer manages connection metadata via pointers; if mandatory fields like host, port, or dbname are absent, those pointers remain NULL at runtime.

-- Bad: Missing required options
CREATE SERVER bad_server
  FOREIGN DATA WRAPPER postgres_fdw;  -- No host, port, or dbname!

-- Good: Fully specified server
DROP SERVER IF EXISTS bad_server CASCADE;

CREATE SERVER good_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.0.0.50', port '5432', dbname 'sales_db');

CREATE USER MAPPING FOR CURRENT_USER
  SERVER good_server
  OPTIONS (user 'app_user', password 'str0ngP@ss');

2. FDW Extension Version Mismatch or Corrupt Installation

After a PostgreSQL upgrade, FDW shared libraries may no longer align with the server binary. This causes handler functions to return NULL structs, leading directly to HV009 when any FDW operation is attempted.

-- Check for version mismatches
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
  AND name LIKE '%fdw%';

-- Fix: update the extension to match the current PostgreSQL version
ALTER EXTENSION postgres_fdw UPDATE;

-- If corruption is suspected, reinstall completely
DROP EXTENSION IF EXISTS postgres_fdw CASCADE;
CREATE EXTENSION postgres_fdw;

-- Verify handler is properly registered
SELECT fdwname, fdwhandler::regproc
FROM pg_foreign_data_wrapper;

3. Incorrect or Missing Foreign Table Options

When creating a Foreign Table without specifying required options such as table_name or schema_name, the FDW cannot resolve the remote object and internally holds a NULL reference to that metadata.

-- Bad: Missing table_name option
CREATE FOREIGN TABLE bad_remote_table (
    id      INTEGER,
    payload TEXT
)
SERVER good_server;  -- FDW doesn't know which remote table to map!

-- Good: All required options explicitly set
DROP FOREIGN TABLE IF EXISTS bad_remote_table;

CREATE FOREIGN TABLE remote_customers (
    customer_id   INTEGER NOT NULL,
    full_name     TEXT,
    email         TEXT,
    created_at    TIMESTAMP
)
SERVER good_server
OPTIONS (schema_name 'public', table_name 'customers');

-- Validate the foreign table works correctly
EXPLAIN VERBOSE SELECT * FROM remote_customers LIMIT 1;
SELECT * FROM remote_customers LIMIT 5;

Quick Fix Solutions

If you hit HV009 in production, run through this checklist:

-- Step 1: Audit all FDW objects
SELECT srvname, srvoptions FROM pg_foreign_server;
SELECT umuser::regrole, umoptions FROM pg_user_mappings;

SELECT ft.ftrelid::regclass,
       array_agg(fto.option_name || '=' || fto.option_value) AS options
FROM pg_foreign_table ft,
     LATERAL pg_options_to_table(ft.ftoptions) AS fto
GROUP BY ft.ftrelid;

-- Step 2: Enable debug logging to get more detail
SET log_min_messages = 'DEBUG1';

-- Re-run the failing query here to capture debug output

RESET log_min_messages;

-- Step 3: Test connectivity explicitly
SELECT * FROM dblink_connect(
    'hostaddr=10.0.0.50 port=5432 dbname=sales_db user=app_user password=str0ngP@ss'
);

Prevention Tips

Always validate FDW objects after creation or modification. Build a simple smoke-test query into your deployment scripts that exercises the Foreign Table immediately after setup. A broken FDW configuration caught at deploy time is far less painful than one discovered in production.

-- Add this to your post-deploy validation script
DO $$
BEGIN
    PERFORM 1 FROM remote_customers LIMIT 1;
    RAISE NOTICE 'FDW connection verified successfully.';
EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION 'FDW validation failed: %', SQLERRM;
END;
$$;

Maintain an upgrade checklist that includes FDW extension compatibility. Every time you upgrade PostgreSQL, verify that all installed FDW extensions have matching installed_version and default_version in pg_available_extensions, and run ALTER EXTENSION ... UPDATE proactively before any application traffic hits the upgraded instance.

Related Errors

Code	Name	Relationship
HV000	fdw_error	General FDW error; parent category of HV009
HV005	fdw_column_name_not_found	Often co-occurs with Foreign Table definition issues
HV00P	fdw_invalid_string_format	Malformed option values; similar configuration context
08001	sqlclient_unable_to_establish_sqlconnection	Remote connection failure; may precede HV009

Oracle ORA-01704 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 26 Jul 2026 06:01:34 +0000

ORA-01704: String Literal Too Long — Causes, Fixes, and Prevention

ORA-01704 is thrown by Oracle Database when a string literal enclosed in single quotes (') within a SQL statement exceeds 4,000 bytes in length. This is a hard parser-level limit, meaning Oracle cannot even begin to process the statement before rejecting it. It commonly surfaces during data migrations, automated SQL script generation, or when developers attempt to hard-code large text values directly into DML statements.

Top 3 Causes

1. Hard-coded Long Strings in INSERT/UPDATE Statements

The most common cause. A developer or script tries to insert a large block of text (e.g., HTML content, JSON, or log data) directly as a SQL literal.

-- This will throw ORA-01704 if the string exceeds 4,000 bytes
INSERT INTO documents (doc_id, body)
VALUES (1, 'This is an extremely long string that goes well beyond
            four thousand bytes in total length ... [truncated]');

2. Auto-generated Migration Scripts with CLOB Data

ETL tools or export utilities often generate SQL scripts that wrap CLOB column values in single quotes without checking their length. Any exported value longer than 4,000 bytes will cause this error upon re-import.

-- Auto-generated script (problematic pattern)
INSERT INTO legacy_data (id, description)
VALUES (42, '[AUTO-EXPORTED TEXT EXCEEDING 4000 BYTES ...]');
-- ORA-01704: string literal too long

3. Dynamic SQL Construction in PL/SQL

When building dynamic SQL strings in PL/SQL, individual literal segments — not just the concatenated result — must each be under 4,000 bytes. Passing a long constant value into EXECUTE IMMEDIATE will still trigger ORA-01704.

DECLARE
    v_sql VARCHAR2(32767);
BEGIN
    -- Even though v_sql can hold more, the literal itself is too long
    v_sql := 'INSERT INTO logs (msg) VALUES (''' ||
             '[String literal segment exceeding 4000 bytes here...]' ||
             ''')';
    EXECUTE IMMEDIATE v_sql; -- ORA-01704 triggered by the literal
END;
/

Quick Fix Solutions

Fix 1: Use `TO_CLOB()` with Concatenation

Split the long string into chunks under 4,000 bytes each and join them with TO_CLOB().

INSERT INTO documents (doc_id, body)
VALUES (
    1,
    TO_CLOB('First chunk of text under 4000 bytes...') ||
    TO_CLOB('Second chunk of text under 4000 bytes...') ||
    TO_CLOB('Third chunk of text under 4000 bytes...')
);
COMMIT;

Fix 2: Use a PL/SQL CLOB Variable

Assign data to a CLOB variable in PL/SQL and bind it in the DML — no literal length limit applies to variable assignments.

DECLARE
    v_content CLOB;
BEGIN
    v_content := 'First portion of a very long text...';
    v_content := v_content || 'Second portion appended safely...';
    v_content := v_content || 'Third portion appended safely...';

    INSERT INTO documents (doc_id, body)
    VALUES (2, v_content);

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Inserted: ' || DBMS_LOB.GETLENGTH(v_content) || ' bytes');
END;
/

Fix 3: Use `DBMS_LOB.WRITEAPPEND` for Large Data

For very large payloads, write directly to the LOB locator using the DBMS_LOB package.

DECLARE
    v_clob  CLOB;
    v_part1 VARCHAR2(4000) := 'First data chunk...';
    v_part2 VARCHAR2(4000) := 'Second data chunk...';
BEGIN
    INSERT INTO documents (doc_id, body)
    VALUES (3, EMPTY_CLOB())
    RETURNING body INTO v_clob;

    DBMS_LOB.OPEN(v_clob, DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_part1), v_part1);
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_part2), v_part2);
    DBMS_LOB.CLOSE(v_clob);

    COMMIT;
END;
/

Prevention Tips

Design columns correctly from the start: Use CLOB or NCLOB for any column that may store content longer than 4,000 bytes (articles, logs, XML, JSON). Don't assume VARCHAR2(4000) is sufficient.
Always use bind variables in application code: Using PreparedStatement (Java), parameterized queries (Python/cx_Oracle), or :bind_variable syntax in SQL completely avoids string literal length restrictions and also improves performance through cursor sharing.
Add length validation in migration scripts: Any script that auto-generates SQL from data should check string lengths and automatically apply TO_CLOB() chunking when values exceed 4,000 bytes.

Related Errors

Error Code	Description
ORA-06502	PL/SQL value error — VARCHAR2 buffer too small
ORA-22835	CLOB to CHAR conversion buffer overflow
ORA-00910	Specified length too long for datatype (DDL)

PostgreSQL HV00A Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 26 Jul 2026 00:03:16 +0000

PostgreSQL Error HV00A: fdw invalid string format

The PostgreSQL error HV00A: fdw invalid string format occurs when a Foreign Data Wrapper (FDW) encounters a string value that doesn't match the expected format during connection setup or data retrieval. This error is common across various FDW implementations including postgres_fdw, file_fdw, and third-party wrappers. It typically surfaces during CREATE SERVER, CREATE USER MAPPING, or when querying a foreign table with mismatched data types.

Top 3 Causes

1. Invalid Option Values in FDW Server Definition

Providing incorrectly formatted values (e.g., non-numeric port, invalid boolean) in FDW server options is the most frequent cause.

-- BAD: Port value is not a valid integer string
CREATE SERVER bad_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.0.0.1', port 'not_a_number', dbname 'mydb');
-- ERROR: HV00A: fdw invalid string format

-- GOOD: Correct integer string for port
CREATE SERVER good_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.0.0.1', port '5432', dbname 'mydb');

-- Fix an existing server
ALTER SERVER bad_server OPTIONS (SET port '5432');

-- Verify server options
SELECT srvname, srvoptions FROM pg_foreign_server;

2. Column Type Mismatch in Foreign Table Definition

When the local foreign table definition uses incompatible types compared to the remote source, FDW fails to parse the incoming string data.

-- BAD: Type mismatch (remote uses DATE and NUMERIC)
CREATE FOREIGN TABLE bad_orders (
  order_id   INTEGER,
  order_date TEXT,        -- remote is DATE
  amount     VARCHAR(50)  -- remote is NUMERIC
)
SERVER good_server
OPTIONS (schema_name 'public', table_name 'orders');

-- GOOD: Types match the remote schema
CREATE FOREIGN TABLE good_orders (
  order_id   INTEGER,
  order_date DATE,
  amount     NUMERIC(15,2)
)
SERVER good_server
OPTIONS (schema_name 'public', table_name 'orders');

-- Best practice: auto-import schema to avoid manual type errors
IMPORT FOREIGN SCHEMA public
  LIMIT TO (orders, customers)
  FROM SERVER good_server
  INTO local_fdw_schema;

3. Special Characters in Connection Strings

Passwords or hostnames containing special characters (@, #, %, !) can break FDW string parsing if not handled correctly.

-- BAD: Special characters in password may cause parsing issues
CREATE USER MAPPING FOR current_user
  SERVER good_server
  OPTIONS (user 'remote_user', password 'P@ss#word!');

-- GOOD: Use dynamic SQL with format() to safely handle special chars
DO $$
DECLARE
  v_pass TEXT := 'P@ss#word!';
BEGIN
  EXECUTE format(
    'CREATE USER MAPPING FOR %I SERVER %I OPTIONS (user %L, password %L)',
    current_user,
    'good_server',
    'remote_user',
    v_pass
  );
END;
$$;

-- Verify user mapping (superuser only)
SELECT umuser::regrole, umoptions FROM pg_user_mappings;

Quick Fix Checklist

-- 1. Check existing FDW server configurations
SELECT srvname, srvfdw, srvoptions FROM pg_foreign_server;

-- 2. Check foreign table definitions
SELECT ft.ftrelid::regclass, ft.ftoptions, fs.srvname
FROM pg_foreign_table ft
JOIN pg_foreign_server fs ON fs.oid = ft.ftserver;

-- 3. Use IMPORT FOREIGN SCHEMA to auto-sync types
IMPORT FOREIGN SCHEMA public
  FROM SERVER good_server
  INTO local_fdw_schema;

-- 4. Test connection with a simple query
SELECT * FROM good_orders LIMIT 1;

Prevention Tips

Use IMPORT FOREIGN SCHEMA instead of manual CREATE FOREIGN TABLE.
This automatically aligns column types with the remote schema, eliminating the most common source of HV00A errors.

-- Automatically import all tables from remote schema
IMPORT FOREIGN SCHEMA public
  FROM SERVER good_server
  INTO local_fdw_schema;

Enable verbose logging to catch FDW errors early.
Set log_error_verbosity = 'VERBOSE' and log_min_messages = 'WARNING' in postgresql.conf to capture detailed FDW error context before they escalate to production incidents.

-- Check current logging settings
SHOW log_error_verbosity;
SHOW log_min_messages;

-- Monitor active FDW-related queries
SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE query ILIKE '%foreign%' OR query ILIKE '%fdw%';

Related Errors

Error Code	Name	Description
HV000	fdw error	Parent error class for all FDW errors
HV009	fdw invalid option name	Wrong option name (vs. HV00A which is wrong option value)
HV005	fdw column name not found	Column missing in remote source
HV001	fdw out of memory	Memory exhaustion during FDW operation

Oracle ORA-01700 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 26 Jul 2026 00:01:28 +0000

ORA-01700: Duplicate Username in List — Causes, Fixes & Prevention

ORA-01700 is an Oracle error that occurs when the same username appears more than once in a GRANT statement's recipient list. Oracle does not allow granting the same privilege to a user multiple times within a single statement and throws this error immediately upon detection. This error is most common in large-scale permission management scripts or automated deployment pipelines.

Top 3 Causes

1. Manually Duplicated Username in GRANT Statement

The most straightforward cause — accidentally typing the same username twice in the target list.

-- Error: SCOTT appears twice
GRANT SELECT ON hr.employees TO scott, james, scott;
-- ORA-01700: duplicate username in list

-- Fix: Remove the duplicate
GRANT SELECT ON hr.employees TO scott, james;

2. Duplicate Entries from Dynamic SQL or Automation Scripts

When user lists are built programmatically from multiple sources and merged without deduplication, duplicates can slip in unnoticed.

-- Problematic dynamic SQL
DECLARE
  v_sql VARCHAR2(1000);
BEGIN
  -- user_a appears twice due to merged lists
  v_sql := 'GRANT SELECT ON hr.employees TO user_a, user_b, user_a';
  EXECUTE IMMEDIATE v_sql;
  -- ORA-01700: duplicate username in list
END;
/

-- Safe fix: Grant individually with deduplication
DECLARE
  TYPE t_users IS TABLE OF VARCHAR2(30);
  v_users t_users := t_users('USER_A', 'USER_B', 'USER_A');
BEGIN
  FOR i IN 1 .. v_users.COUNT LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'GRANT SELECT ON hr.employees TO ' || v_users(i);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Skipped ' || v_users(i) || ': ' || SQLERRM);
    END;
  END LOOP;
END;
/

3. Mixed Case Username Treated as Duplicate

Oracle treats unquoted usernames as case-insensitive. Writing Scott and SCOTT in the same GRANT list causes ORA-01700 because Oracle normalizes both to SCOTT.

-- Error: Scott and SCOTT are the same user
GRANT SELECT ON hr.departments TO Scott, james, SCOTT;
-- ORA-01700: duplicate username in list

-- Fix: Use consistent uppercase
GRANT SELECT ON hr.departments TO SCOTT, JAMES;

-- Verify exact username from dictionary
SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE '%SCOTT%';

Quick Fix Solutions

Always audit existing privileges before running new GRANT statements to avoid both duplicates and redundant grants.

-- Check who already has privileges on a specific object
SELECT GRANTEE, PRIVILEGE, GRANT_OPTION
FROM   DBA_TAB_PRIVS
WHERE  TABLE_NAME = 'EMPLOYEES'
AND    OWNER      = 'HR'
ORDER BY GRANTEE;

-- Pre-check your user list for duplicates before granting
WITH target_users AS (
  SELECT 'SCOTT' AS username FROM DUAL UNION ALL
  SELECT 'JAMES' AS username FROM DUAL UNION ALL
  SELECT 'SCOTT' AS username FROM DUAL
)
SELECT username, COUNT(*) AS occurrences
FROM   target_users
GROUP  BY username
HAVING COUNT(*) > 1;
-- If this returns rows, fix your list before running GRANT

Prevention Tips

1. Always use individual GRANT statements in scripts.
Granting one user at a time eliminates the risk of list duplication entirely and makes audit logs cleaner.

GRANT SELECT ON hr.employees TO scott;
GRANT SELECT ON hr.employees TO james;
GRANT SELECT ON hr.employees TO adams;

2. Add a deduplication check before any bulk GRANT operation.
In automated pipelines, normalize all usernames to uppercase and run a duplicate check query before executing any GRANT statement. Treat a non-empty result from a HAVING COUNT(*) > 1 check as a hard blocker that prevents the script from proceeding.

Related Errors

ORA-01917 — User or role does not exist; often encountered alongside ORA-01700 when GRANT target lists contain errors.
ORA-01919 — Role does not exist; similar context involving invalid GRANT targets.
ORA-00990 — Missing or invalid privilege; occurs when an invalid privilege keyword is used in a GRANT statement.

PostgreSQL HV090 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 25 Jul 2026 14:03:36 +0000

PostgreSQL HV090: fdw_invalid_string_length_or_buffer_length

PostgreSQL error code HV090 (fdw_invalid_string_length_or_buffer_length) occurs when a Foreign Data Wrapper (FDW) encounters a mismatch between the defined string or buffer length and the actual data being transferred from a remote source. This error is common with drivers like postgres_fdw, oracle_fdw, and mysql_fdw, and it can silently break production data pipelines if left unaddressed.

Top 3 Causes and Fixes

1. Column Length Mismatch Between Foreign Table and Remote Source

This is the most frequent cause. When the foreign table definition uses a shorter VARCHAR length than what the remote server actually stores, any row exceeding that length triggers HV090.

-- Problematic definition (remote column is VARCHAR(500), but defined as VARCHAR(50))
CREATE FOREIGN TABLE foreign_users (
    id    INT,
    name  VARCHAR(50)   -- Too short! Remote DB has VARCHAR(500)
)
SERVER remote_pg_server
OPTIONS (schema_name 'public', table_name 'users');

-- Fix: Use IMPORT FOREIGN SCHEMA to auto-sync the remote schema
DROP SCHEMA IF EXISTS remote_schema CASCADE;
CREATE SCHEMA remote_schema;

IMPORT FOREIGN SCHEMA public
    LIMIT TO (users)
    FROM SERVER remote_pg_server
    INTO remote_schema;

-- Or manually correct the column definition
ALTER FOREIGN TABLE foreign_users
    ALTER COLUMN name TYPE TEXT;  -- Use TEXT when max length is uncertain

2. Multibyte Character Encoding Overflow

When a VARCHAR(n) column is defined with a character limit, multibyte characters (e.g., UTF-8 Korean, Japanese, Chinese) consume 2–4 bytes per character. A VARCHAR(100) column can overflow its buffer when storing 100 multibyte characters that actually require 300+ bytes internally.

-- Check actual byte length vs character length on a foreign table
SELECT
    id,
    LENGTH(description)        AS char_length,
    OCTET_LENGTH(description)  AS byte_length
FROM foreign_products
WHERE OCTET_LENGTH(description) > 200
LIMIT 20;

-- Fix: Switch VARCHAR columns to TEXT for multibyte-safe handling
DROP FOREIGN TABLE IF EXISTS foreign_products;

CREATE FOREIGN TABLE foreign_products (
    id          INT,
    name        TEXT,           -- Safe for any multibyte content
    description TEXT
)
SERVER remote_server
OPTIONS (schema_name 'public', table_name 'products');

3. FDW Driver Buffer/Option Misconfiguration

Some FDW drivers expose options like max_blob_size or fetch_size that control internal buffer sizes. If these are set too low — or left at defaults that no longer fit your data volume — HV090 can appear as your data grows over time.

-- Check current server options
SELECT srvname, srvoptions
FROM pg_foreign_server
WHERE srvname = 'remote_oracle_server';

-- Fix for oracle_fdw: increase max_blob_size (default is 512KB)
ALTER SERVER remote_oracle_server
OPTIONS (SET max_blob_size '10485760');  -- Set to 10MB

-- Fix for postgres_fdw: reduce fetch_size to lower memory pressure
ALTER SERVER remote_pg_server
OPTIONS (SET fetch_size '100');

-- Apply per-table fetch_size for high-volume foreign tables
ALTER FOREIGN TABLE foreign_large_orders
OPTIONS (SET fetch_size '50');

-- Verify the fix
SELECT COUNT(*) FROM foreign_large_orders;

Quick Fix Checklist

Always prefer TEXT over VARCHAR(n) for foreign table columns when the remote column size is unknown or variable.
Use IMPORT FOREIGN SCHEMA instead of manually writing CREATE FOREIGN TABLE — it mirrors the remote definition exactly.
Increase FDW buffer options (max_blob_size, fetch_size) if your data involves LOBs or large text fields.
Check encoding: ensure client, server, and remote database encodings are consistent (SHOW server_encoding;).

Prevention Tips

Automate schema synchronization. Remote schemas change — columns get extended, types change. Schedule a periodic IMPORT FOREIGN SCHEMA refresh (via pg_cron or a CI pipeline) so your foreign table definitions never fall out of sync.

Monitor column length utilization. Run a regular check to catch columns approaching their defined limit before they cause failures in production:

-- Alert when any value exceeds 80% of the defined VARCHAR limit
SELECT
    MAX(LENGTH(name))                        AS max_observed,
    200                                      AS defined_limit,
    ROUND(MAX(LENGTH(name))::NUMERIC / 200 * 100, 1) AS pct_used
FROM foreign_users
HAVING MAX(LENGTH(name)) > 200 * 0.8;

Related Errors

Code	Name	Relation
HV000	`fdw_error`	Parent error class for all FDW errors
HV005	`fdw_column_name_not_found`	Schema drift, often co-occurs with HV090
22001	`string_data_right_truncation`	Similar root cause in non-FDW contexts
HV002	`fdw_dynamic_parameter_value_needed`	Bad FDW option config, related to cause #3

Oracle ORA-01595 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 25 Jul 2026 14:01:55 +0000

ORA-01595: Error Freeing Extent of Rollback Segment

ORA-01595 is an Oracle database error that occurs when the database engine fails to release (free) an extent belonging to a rollback segment during transaction processing or segment shrink operations. Rollback segments store before-images of data to support transaction rollback and read consistency, making them critical to database stability. When this error appears, it often signals underlying storage corruption, misconfigured segment parameters, or tablespace space issues that require immediate attention.

Top 3 Causes

1. Rollback Segment Header Block Corruption

Physical corruption of the rollback segment header block prevents Oracle from reading the extent chain, causing ORA-01595. This is commonly triggered by abrupt instance shutdowns, hardware I/O failures, or storage-level errors.

-- Check rollback segment status and identify problematic segments
SELECT rn.name       AS segment_name,
       rs.extents,
       rs.rssize     AS current_size_bytes,
       rs.optsize    AS optimal_size_bytes,
       rs.hwmsize    AS hwm_bytes,
       rs.xacts      AS active_transactions,
       rs.status
FROM   v$rollstat rs
JOIN   v$rollname rn ON rs.usn = rn.usn
ORDER  BY rs.xacts DESC;

-- Check for block corruption using DBVERIFY (run at OS level)
-- dbv FILE=/oracle/oradata/orcl/rbs01.dbf BLOCKSIZE=8192

-- Verify segment details from data dictionary
SELECT segment_name,
       status,
       tablespace_name,
       file_id,
       block_id,
       initial_extent,
       next_extent
FROM   dba_rollback_segs
WHERE  status NOT IN ('OFFLINE', 'INVALID');

2. Aggressive OPTIMAL Parameter Setting

When an OPTIMAL storage parameter is set too small, Oracle repeatedly attempts to shrink the rollback segment back to that size. This constant shrinking cycle can cause extent-freeing failures, especially when active transactions are present during the shrink operation.

-- Identify rollback segments with excessive shrink activity
SELECT rn.name   AS segment_name,
       rs.shrinks,
       rs.wraps,
       rs.optsize / 1024 / 1024 AS optimal_mb,
       rs.hwmsize / 1024 / 1024 AS hwm_mb,
       rs.rssize  / 1024 / 1024 AS current_mb
FROM   v$rollstat rs
JOIN   v$rollname rn ON rs.usn = rn.usn
WHERE  rs.shrinks > 0
ORDER  BY rs.shrinks DESC;

-- Disable OPTIMAL to stop aggressive shrinking
ALTER ROLLBACK SEGMENT rbs_problematic STORAGE (OPTIMAL UNLIMITED);

-- Or set OPTIMAL to a safe value above HWM
ALTER ROLLBACK SEGMENT rbs_normal STORAGE (OPTIMAL 200M);

3. RBS Tablespace Space Shortage or Fragmentation

Insufficient free space or heavy fragmentation in the rollback segment tablespace prevents Oracle from reorganizing extents during the free operation. In Dictionary-Managed Tablespaces, this also involves failed updates to FET$ and UET$ dictionary tables.

-- Check free space and fragmentation in the RBS tablespace
SELECT tablespace_name,
       COUNT(*)                   AS free_chunks,
       SUM(bytes) / 1024 / 1024  AS total_free_mb,
       MAX(bytes) / 1024 / 1024  AS largest_chunk_mb,
       MIN(bytes) / 1024 / 1024  AS smallest_chunk_mb
FROM   dba_free_space
WHERE  tablespace_name = 'RBS'
GROUP  BY tablespace_name;

-- Add a datafile to resolve space shortage
ALTER TABLESPACE rbs
ADD DATAFILE '/oracle/oradata/orcl/rbs02.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 2G;

Quick Fix Solutions

-- Step 1: Take the problematic rollback segment offline immediately
ALTER ROLLBACK SEGMENT rbs_problematic OFFLINE;

-- Step 2: Drop and recreate the segment cleanly
DROP ROLLBACK SEGMENT rbs_problematic;

CREATE ROLLBACK SEGMENT rbs_new
TABLESPACE rbs
STORAGE (
    INITIAL    2M
    NEXT       2M
    MINEXTENTS 2
    MAXEXTENTS UNLIMITED
    OPTIMAL    UNLIMITED    -- Disable auto-shrink to prevent recurrence
);

ALTER ROLLBACK SEGMENT rbs_new ONLINE;

-- Step 3: Migrate to Automatic Undo Management (Oracle 9i+)
-- This is the definitive long-term fix
CREATE UNDO TABLESPACE undotbs_new
DATAFILE '/oracle/oradata/orcl/undotbs_new.dbf'
SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;

-- Set in SPFILE and restart
ALTER SYSTEM SET UNDO_MANAGEMENT  = AUTO    SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_TABLESPACE  = UNDOTBS_NEW SCOPE=SPFILE;
ALTER SYSTEM SET UNDO_RETENTION   = 3600    SCOPE=SPFILE;

-- Step 4: Verify UNDO health after migration
SELECT status,
       SUM(bytes) / 1024 / 1024 AS mb
FROM   dba_undo_extents
WHERE  tablespace_name = 'UNDOTBS_NEW'
GROUP  BY status;

Prevention Tips

1. Switch to Automatic Undo Management (AUM)
The most effective prevention is migrating from manual rollback segments to AUM (introduced in Oracle 9i). AUM eliminates manual extent management entirely, removing the root cause of ORA-01595. Always set UNDO_MANAGEMENT=AUTO in new database builds and migrations.

2. Monitor Alert Log and UNDO Statistics Proactively
Schedule regular checks on V$UNDOSTAT and the Alert Log to catch early warning signs before they escalate.

-- Monitor UNDO usage trends (run daily)
SELECT TO_CHAR(begin_time, 'MM/DD HH24:MI') AS period,
       undoblks                              AS undo_blocks_used,
       txncount                             AS transactions,
       maxquerylen                          AS longest_query_sec,
       ssolderrcnt                          AS ora01555_errors,
       nospaceerrcnt                        AS space_errors
FROM   v$undostat
WHERE  begin_time > SYSDATE - 1
ORDER  BY begin_time DESC;

-- Alert Log error scan (12c and above)
SELECT originating_timestamp,
       SUBSTR(message_text, 1, 200) AS error_message
FROM   v$diag_alert_ext
WHERE  message_text LIKE '%ORA-015%'
  AND  originating_timestamp > SYSDATE - 7
ORDER  BY originating_timestamp DESC;

DEV Community: umzzil nng

PostgreSQL HV00P Error: Causes and Solutions Complete Guide

PostgreSQL Error HV00P: fdw_no_schemas Explained

Top 3 Causes

1. Insufficient Privileges on the Remote Schema

2. Incorrect or Non-Existent Schema Name

3. FDW Driver Version Mismatch or Incomplete Implementation

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-01720 Error: Causes and Solutions Complete Guide

ORA-01720: grant option does not exist for

Top 3 Causes

1. Missing GRANT OPTION on View's Base Table

2. Re-granting Privileges Received Without GRANT OPTION

3. Synonym-Based View With Indirect Object References

Quick Fix Solutions

Prevention Tips

PostgreSQL HV001 Error: Causes and Solutions Complete Guide

PostgreSQL Error HV001: FDW Out of Memory

Top 3 Causes and Fixes

1. Insufficient work_mem for FDW Query Processing

2. FDW Connection Leaks and Too Many Concurrent Connections

3. Fetching Too Much Data at Once (Missing fetch_size Tuning)

Quick Prevention Tips

Related Error Codes

Oracle ORA-01719 Error: Causes and Solutions Complete Guide

ORA-01719: Outer Join Operator (+) Not Allowed in OR or IN Operands

Top 3 Causes

1. Using (+) with an OR Condition

2. Using (+) Inside an IN Clause

3. Inadvertently Adding OR to a Legacy Query Containing (+)

Quick Fix Solutions

Prevention Tips

Related Errors

PostgreSQL HV014 Error: Causes and Solutions Complete Guide

PostgreSQL HV014: fdw_too_many_handles — Causes, Fixes & Prevention

What Is HV014?

Top 3 Causes

1. FDW Connection Handle Leaks

2. Too Many Concurrent FDW Sessions

3. Insufficient Handle Limits in FDW Server Configuration

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-01710 Error: Causes and Solutions Complete Guide

ORA-01710: duplicate keyword — Causes, Fixes, and Prevention

What Is ORA-01710?

Top 3 Causes

1. Duplicate Storage or Physical Attribute Keywords in DDL

2. Duplicate Option Keywords in GRANT Statements

3. Duplicate Clauses in SELECT Statements (Dynamic SQL)

Quick Fix Solutions

Prevention Tips

Related Oracle Errors

PostgreSQL HV009 Error: Causes and Solutions Complete Guide

PostgreSQL Error HV009: fdw_invalid_use_of_null_pointer

Top 3 Causes

1. Missing or Incomplete Foreign Server / User Mapping Configuration

2. FDW Extension Version Mismatch or Corrupt Installation

3. Incorrect or Missing Foreign Table Options

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-01704 Error: Causes and Solutions Complete Guide

ORA-01704: String Literal Too Long — Causes, Fixes, and Prevention

Top 3 Causes

1. Hard-coded Long Strings in INSERT/UPDATE Statements

2. Auto-generated Migration Scripts with CLOB Data

3. Dynamic SQL Construction in PL/SQL

Quick Fix Solutions

Fix 1: Use TO_CLOB() with Concatenation

Fix 2: Use a PL/SQL CLOB Variable

Fix 3: Use DBMS_LOB.WRITEAPPEND for Large Data

Prevention Tips

Related Errors

PostgreSQL HV00A Error: Causes and Solutions Complete Guide

PostgreSQL Error HV00A: fdw invalid string format

Top 3 Causes

1. Invalid Option Values in FDW Server Definition

1. Insufficient `work_mem` for FDW Query Processing

3. Fetching Too Much Data at Once (Missing `fetch_size` Tuning)

1. Using `(+)` with an `OR` Condition

2. Using `(+)` Inside an `IN` Clause

3. Inadvertently Adding `OR` to a Legacy Query Containing `(+)`

Fix 1: Use `TO_CLOB()` with Concatenation

Fix 3: Use `DBMS_LOB.WRITEAPPEND` for Large Data