DEV Community: umzzil nng

PostgreSQL 22001 Error: Causes and Solutions Complete Guide

umzzil nng — Fri, 12 Jun 2026 14:03:08 +0000

PostgreSQL Error 22001: String Data Right Truncation

PostgreSQL error code 22001, string data right truncation, occurs when you attempt to insert or update a string value that exceeds the maximum length defined for a column. Unlike some other databases that silently truncate data, PostgreSQL strictly enforces column length constraints and raises this error to protect data integrity. This error is part of the data_exception (22000) error class and will immediately abort the current transaction.

Top 3 Causes

1. Inserting Data Longer Than Column Definition

The most common cause is simply trying to store a string that is longer than the column's defined limit.

-- This will fail if full_name is VARCHAR(20)
INSERT INTO users (full_name, email)
VALUES ('Alexander Christopher Johnson', 'alex@example.com');

-- ERROR: value too long for type character varying(20)

-- Fix: Check the column definition first
SELECT column_name, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'full_name';

-- Fix: Alter the column to accommodate larger data
ALTER TABLE users
  ALTER COLUMN full_name TYPE VARCHAR(100);

2. Schema Not Updated After Business Requirement Changes

As applications evolve, original column size assumptions often become outdated. For example, a phone number column designed for domestic numbers may need to support international formats.

-- Original column too small for international numbers
-- phone_number VARCHAR(11) can't store '+1-800-555-0199'

-- Check current problematic data
SELECT id, phone_number, LENGTH(phone_number) AS len
FROM customers
WHERE LENGTH(phone_number) > 11
ORDER BY len DESC;

-- Fix: Expand the column size safely
BEGIN;
ALTER TABLE customers
  ALTER COLUMN phone_number TYPE VARCHAR(20);
COMMIT;

-- Or remove the length restriction entirely using TEXT
ALTER TABLE products
  ALTER COLUMN description TYPE TEXT;

3. Data Migration or ETL Mismatches

When migrating data from another database system (e.g., MySQL, Oracle), column size definitions may differ, causing unexpected truncation errors on bulk loads.

-- Pre-migration validation: detect oversized data before loading
SELECT
    id,
    full_name,
    LENGTH(full_name) AS actual_length,
    20 AS target_column_limit
FROM staging_users
WHERE LENGTH(full_name) > 20
ORDER BY actual_length DESC;

-- Summary report before migration
SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN LENGTH(full_name) > 20 THEN 1 ELSE 0 END) AS rows_exceeding_limit,
    MAX(LENGTH(full_name)) AS max_length_found
FROM staging_users;

-- Handle the error gracefully using PL/pgSQL exception handling
DO $$
DECLARE
    v_name TEXT := 'This is an extremely long name that exceeds our limit';
BEGIN
    INSERT INTO users (full_name) VALUES (v_name::VARCHAR(20));
EXCEPTION
    WHEN string_data_right_truncation THEN
        RAISE NOTICE 'Truncation detected for value: %, length: %',
            v_name, LENGTH(v_name);
END;
$$;

Quick Fix Solutions

-- Option 1: Extend the column length (no data loss)
ALTER TABLE your_table
  ALTER COLUMN your_column TYPE VARCHAR(255);

-- Option 2: Convert to TEXT type (no length restriction)
ALTER TABLE your_table
  ALTER COLUMN your_column TYPE TEXT;

-- Option 3: Explicitly truncate before insert (use with caution)
INSERT INTO users (full_name)
VALUES (LEFT('Very long name that needs trimming', 20));

-- Option 4: Filter out oversized rows during migration
INSERT INTO users (full_name, email)
SELECT full_name, email
FROM staging_users
WHERE LENGTH(full_name) <= 20;

Prevention Tips

Use TEXT type when no business rule requires a length limit.
In PostgreSQL, TEXT and VARCHAR use identical internal storage, so there is no performance difference. If there is no meaningful business constraint on a string column's length, default to TEXT to avoid this error entirely.

Validate data length at both the application and database layers.
Do not rely solely on application-side validation. Add CHECK constraints or triggers at the database level so that all data entry paths — including direct SQL, ORMs, and batch jobs — are consistently enforced.

-- Add a CHECK constraint as an additional safety net
ALTER TABLE products
  ADD CONSTRAINT chk_product_name_length
  CHECK (LENGTH(product_name) <= 200);

-- Use a reusable domain type for consistent enforcement
CREATE DOMAIN short_label AS VARCHAR(50)
  CHECK (VALUE IS NOT NULL AND LENGTH(TRIM(VALUE)) > 0);

Related Errors

22000 (data_exception): Parent error class for 22001 and all data-related exceptions.
22P02 (invalid_text_representation): Raised when an invalid text format is cast to another type; often appears alongside 22001 during migrations.
23514 (check_violation): Triggered when a CHECK constraint is violated, which may occur if length limits are enforced via CHECK rather than column type definitions.

Oracle ORA-00474 Error: Causes and Solutions Complete Guide

umzzil nng — Fri, 12 Jun 2026 14:01:33 +0000

ORA-00474: SMON Process Terminated with Error

ORA-00474 is a critical Oracle database error that occurs when the SMON (System Monitor) background process terminates unexpectedly. SMON is responsible for essential tasks such as instance recovery, temporary segment cleanup, and extent coalescing in dictionary-managed tablespaces. When SMON goes down, Oracle typically brings down the entire instance, requiring immediate DBA intervention.

Top 3 Causes

1. Data Dictionary or System Tablespace Corruption

Block corruption in the SYSTEM or SYSAUX tablespace is one of the leading causes of SMON failure. Since SMON continuously accesses the data dictionary, any I/O error or corrupted block in these tablespaces can cause an immediate crash.

-- Check for block corruption
SELECT FILE#, BLOCK#, BLOCKS, CORRUPTION_TYPE
FROM V$DATABASE_BLOCK_CORRUPTION;

-- Check datafile status
SELECT FILE#, NAME, STATUS
FROM V$DATAFILE
WHERE STATUS != 'ONLINE';

2. Rollback / UNDO Segment Issues During Instance Recovery

After an abnormal shutdown, SMON performs instance recovery by rolling back uncommitted transactions. If an UNDO segment is corrupted or a specific transaction cannot be rolled back, SMON will fail and terminate with an error.

-- Check rollback segment status
SELECT SEGMENT_NAME, STATUS, TABLESPACE_NAME
FROM DBA_ROLLBACK_SEGS;

-- Temporarily offline a problematic UNDO segment via SPFILE
ALTER SYSTEM SET "_offline_rollback_segments" = '_SYSSMU3_123456789$'
  SCOPE=SPFILE;

-- Verify UNDO tablespace usage
SELECT TABLESPACE_NAME,
       ROUND(SUM(BYTES)/1024/1024, 2) AS TOTAL_MB,
       SUM(DECODE(STATUS,'ACTIVE',1,0)) AS ACTIVE_EXTENTS
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME;

3. OS-Level Resource Exhaustion (OOM / ulimit)

If the server runs out of physical memory or if OS-level limits (ulimit) are set below Oracle's recommended values, the OS OOM Killer may terminate Oracle background processes including SMON. Always check OS logs alongside Oracle alert logs.

-- Check current SGA usage
SELECT POOL, NAME, ROUND(BYTES/1024/1024, 2) AS MB
FROM V$SGASTAT
WHERE POOL IS NOT NULL
ORDER BY BYTES DESC
FETCH FIRST 10 ROWS ONLY;

-- Monitor background process status
SELECT PNAME, PID, SPID, BACKGROUND
FROM V$PROCESS
WHERE BACKGROUND = 1
ORDER BY PNAME;

Quick Fix Solutions

Step 1 – Check Alert Log and Trace Files First

-- Find trace file location
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

-- Find ADR home
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'ADR Home';

Step 2 – Run RMAN Validation

-- Connect to RMAN and validate the database
-- RMAN> VALIDATE DATABASE;
-- RMAN> VALIDATE DATAFILE 1;

-- After validation, review corruption details
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

Step 3 – Clean Up Temporary Segments Manually

-- Identify temporary segments
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'TEMPORARY';

-- Shrink TEMP tablespace (Oracle 11g+)
ALTER TABLESPACE TEMP SHRINK SPACE KEEP 100M;

Prevention Tips

1. Enable Block Checksum and Schedule Regular RMAN Validation

Enabling DB_BLOCK_CHECKSUM allows Oracle to detect corruption at the I/O level before it causes critical failures.

-- Enable block checksum
ALTER SYSTEM SET DB_BLOCK_CHECKSUM = TYPICAL SCOPE=BOTH;

-- Confirm the setting
SHOW PARAMETER db_block_checksum;

2. Monitor Background Processes and Tune UNDO Settings

Set up proactive monitoring for all Oracle background processes and ensure UNDO retention and tablespace sizing match your workload patterns.

-- Monitor UNDO health
SHOW PARAMETER undo_retention;

-- Adjust if needed
ALTER SYSTEM SET UNDO_RETENTION = 900 SCOPE=BOTH;

Related Errors

ORA-00470 – LGWR process terminated with error
ORA-00472 – PMON process terminated with error
ORA-00600 – Internal error, often accompanies ORA-00474 in trace files
ORA-01578 – Oracle data block corrupted, frequently seen alongside SMON failures

PostgreSQL 22026 Error: Causes and Solutions Complete Guide

umzzil nng — Fri, 12 Jun 2026 10:03:09 +0000

PostgreSQL Error 22026: String Data Length Mismatch

PostgreSQL error code 22026 (string_data_length_mismatch) occurs when the actual length of string or binary data does not match the expected length during data processing or type conversion. This error commonly surfaces when working with bytea data types, fixed-length char(n) columns, or custom type serialization/deserialization routines. Understanding the root cause quickly is essential, as this error can block data pipelines and integrations if left unresolved.

Top 3 Causes

1. Invalid `bytea` Encoding or Malformed Hex String

When inserting or converting bytea data, the input hex string must have an even number of hexadecimal digits. If the hex string has an odd number of characters or contains invalid characters, PostgreSQL cannot correctly determine the byte length, triggering error 22026.

-- BAD: Odd number of hex digits causes an error
SELECT decode('A1B2C', 'hex');
-- ERROR: invalid hexadecimal data: odd number of digits

-- GOOD: Even number of hex digits
SELECT decode('A1B2C3', 'hex');

-- Safe insertion of bytea data
INSERT INTO file_storage (file_id, file_data)
VALUES (1, decode('DEADBEEF01', 'hex'));

-- Verify stored bytea length
SELECT file_id, octet_length(file_data) AS bytes
FROM file_storage;

2. Fixed-Length `char(n)` Type Mismatch During Custom Casting

The char(n) type enforces a strict fixed-length storage contract. When custom cast functions or type conversion routines produce output that does not match the declared length n, the internal length validation fails and raises 22026. This frequently occurs in legacy data migrations or ETL pipelines where character encodings are mixed.

-- Check column definitions for fixed-length types
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'your_table'
  AND data_type = 'character';

-- Safely pad input to match char(n) length
INSERT INTO product_codes (code)
VALUES (lpad('ABC', 10, ' '));

-- If mismatches are frequent, consider migrating to varchar
ALTER TABLE product_codes
    ALTER COLUMN code TYPE varchar(10);

-- Inspect length distribution of existing data
SELECT char_length(code) AS len, COUNT(*) AS cnt
FROM product_codes
GROUP BY char_length(code)
ORDER BY len;

3. Custom or Composite Type Binary Layout Mismatch

When a user-defined type (UDT) or composite type is modified — especially after an extension upgrade — the binary layout stored on disk may no longer match what the new type definition expects. Reading old binary data through a new type definition fails at the length validation step, producing error 22026. This is the trickiest cause to diagnose and can risk data integrity.

-- Inspect custom type metadata
SELECT typname, typlen, typbyval, typsend, typreceive
FROM pg_type
WHERE typname = 'your_custom_type';

-- Back up data as text before modifying the type
CREATE TABLE backup_typed_data AS
SELECT id, your_col::text AS your_col_text
FROM your_table;

-- After redefining the type, restore with explicit cast
INSERT INTO your_table (id, your_col)
SELECT id, your_col_text::your_custom_type
FROM backup_typed_data;

-- Check installed extension versions
SELECT extname, extversion
FROM pg_extension;

Quick Fix Solutions

-- Validate hex strings before inserting bytea
CREATE OR REPLACE FUNCTION is_valid_hex(input text)
RETURNS boolean AS $$
BEGIN
    RETURN length(input) % 2 = 0 AND input ~ '^[0-9a-fA-F]*$';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Use this function as a CHECK constraint
ALTER TABLE file_storage
ADD CONSTRAINT chk_valid_hex
CHECK (is_valid_hex(encode(file_data, 'hex')));

-- Safely convert and verify bytea round-trip
SELECT
    original_hex,
    encode(decode(original_hex, 'hex'), 'hex') AS round_trip_hex,
    original_hex = encode(decode(original_hex, 'hex'), 'hex') AS is_valid
FROM (VALUES ('DEADBEEF'), ('A1B2C3')) AS t(original_hex);

Prevention Tips

Validate data length and format at the application layer before sending it to PostgreSQL. For bytea columns, always ensure hex strings have an even character count. For char(n) columns, explicitly pad or truncate strings to the correct length before insertion. Adding CHECK constraints at the database level provides an additional safety net.
Test type changes in a staging environment before production deployment. Whenever you modify a custom type, composite type, or upgrade an extension, run pg_upgrade --check for major version upgrades and always back up affected data as text before applying schema changes. Keeping a clear versioning strategy for custom types prevents binary layout conflicts from ever reaching production.

Related Errors

Code	Name	Brief Description
`22001`	`string_data_right_truncation`	String exceeds target column length
`22P02`	`invalid_text_representation`	Invalid text format for type conversion
`42804`	`datatype_mismatch`	Type-level mismatch (vs. length-level for 22026)
`22003`	`numeric_value_out_of_range`	Numeric value exceeds type range during conversion

Oracle ORA-00473 Error: Causes and Solutions Complete Guide

umzzil nng — Fri, 12 Jun 2026 10:01:29 +0000

ORA-00473: ARCH Process Terminated with Error — Causes, Fixes & Prevention

ORA-00473 occurs when Oracle's ARCH (Archiver) background process terminates unexpectedly during the archiving of online redo log files. This error is critical in ARCHIVELOG mode databases because if archiving fails, Oracle cannot reuse online redo logs, which can cause the entire database to hang. Immediate investigation and remediation are essential to restore normal operations.

Top 3 Causes

1. Archive Log Destination Disk Full

The most common cause. When the filesystem holding archive logs runs out of space, the ARCH process fails immediately.

-- Check archive log destination and space usage
SELECT DEST_ID, DEST_NAME, STATUS, DESTINATION, ERROR
FROM V$ARCHIVE_DEST
WHERE STATUS != 'INACTIVE';

-- Check Fast Recovery Area usage
SELECT SPACE_LIMIT / 1024 / 1024 / 1024 AS LIMIT_GB,
       SPACE_USED  / 1024 / 1024 / 1024 AS USED_GB,
       ROUND(SPACE_USED / NULLIF(SPACE_LIMIT,0) * 100, 2) AS USED_PCT
FROM V$RECOVERY_FILE_DEST;

-- Delete old archive logs via RMAN (run inside RMAN client)
-- RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

2. Invalid or Inaccessible Archive Destination Path

If LOG_ARCHIVE_DEST_n points to a non-existent directory, an unmounted NFS share, or a path with incorrect OS permissions, the ARCH process will terminate with ORA-00473.

-- Review current archive destination parameters
SHOW PARAMETER LOG_ARCHIVE_DEST;

-- Fix: update destination to a valid path
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/oradata/archive' SCOPE=BOTH;

-- Confirm destination status
SELECT DEST_ID, STATUS, ERROR, DESTINATION
FROM V$ARCHIVE_DEST_STATUS
WHERE DEST_ID IN (1, 2);

3. Underlying I/O or Hardware Error

Bad disk sectors, SAN/NAS failures, or OS-level filesystem corruption can all cause write failures during archiving. Always cross-check Oracle alert logs with OS system logs (/var/log/messages).

-- Find ORA-00473 entries in the alert log (Oracle 11g+)
SELECT ORIGINATING_TIMESTAMP, MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE '%ORA-00473%'
   OR MESSAGE_TEXT LIKE '%ARCH%'
ORDER BY ORIGINATING_TIMESTAMP DESC
FETCH FIRST 30 ROWS ONLY;

-- Identify the alert log location
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

Quick Fix Solutions

After resolving the root cause (free up disk space, fix path, repair I/O), restart the ARCH process:

-- Verify current archive mode
SELECT LOG_MODE FROM V$DATABASE;

-- Restart the archiver
ALTER SYSTEM ARCHIVE LOG STOP;
ALTER SYSTEM ARCHIVE LOG START;

-- Force archiving of all unarchived logs
ALTER SYSTEM ARCHIVE LOG ALL;

-- Force a log switch to confirm archiving works
ALTER SYSTEM SWITCH LOGFILE;

-- Check ARCH process status
SELECT PROCESS, STATUS, SEQUENCE#
FROM V$MANAGED_STANDBY
WHERE PROCESS LIKE 'ARC%';

Prevention Tips

Monitor archive log space proactively. Set up automated alerts when archive space exceeds 75% capacity using OEM, Zabbix, or a custom cron script. Configure RMAN retention policy to auto-clean old logs:

-- RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
-- RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

Redundant archive destinations on separate disks. Configure at least two archive destinations (LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2) on physically separate storage to eliminate single points of failure and buy recovery time when one destination fails.

Related Errors

Error Code	Description
ORA-00257	Archiver stuck — all non-internal connections blocked
ORA-00255	Error archiving log — often appears alongside ORA-00473
ORA-19809	Fast Recovery Area space limit exceeded
ORA-16014	Archive destination invalid or unavailable
ORA-00270	Error creating archive log file (I/O related)

PostgreSQL 2200H Error: Causes and Solutions Complete Guide

umzzil nng — Fri, 12 Jun 2026 06:03:00 +0000

PostgreSQL Error 2200H: Sequence Generator Limit Exceeded

PostgreSQL error code 2200H occurs when a sequence object reaches its defined MAXVALUE (or MINVALUE for descending sequences) and has no CYCLE option to wrap around. This is most commonly seen on tables using SERIAL (INT4) primary keys, which cap out at approximately 2.1 billion. Once the limit is hit, every subsequent INSERT attempting to use that sequence will fail immediately.

Top 3 Causes

1. `SERIAL` Column Hitting the INT4 Ceiling (~2.1 Billion)

SERIAL uses a 4-byte integer under the hood. High-volume systems — logging tables, event trackers, order systems — can exhaust this faster than expected.

-- Check how close your sequences are to their limit
SELECT
    sequencename,
    last_value,
    max_value,
    ROUND((last_value::NUMERIC / max_value) * 100, 2) AS used_pct,
    (max_value - last_value) AS remaining
FROM pg_sequences
WHERE schemaname = 'public'
ORDER BY used_pct DESC;

2. Custom `MAXVALUE` Set Too Low

When a sequence is manually created with an artificially low MAXVALUE and NO CYCLE (the default), it will throw 2200H as soon as the cap is reached.

-- Example of a problematic sequence definition
CREATE SEQUENCE bad_sequence
    START 1
    INCREMENT 1
    MAXVALUE 10000   -- Way too low for production use
    NO CYCLE;

-- Check a specific sequence definition
SELECT * FROM pg_sequences WHERE sequencename = 'bad_sequence';

3. Rollbacks Silently Consuming Sequence Values

PostgreSQL sequences are non-transactional by design — rolled-back transactions do not return their consumed values. In retry-heavy applications or batch jobs, sequence values can be consumed far faster than actual committed rows suggest.

-- Demonstrate sequence consumption on rollback
BEGIN;
SELECT nextval('orders_order_id_seq'); -- value consumed
ROLLBACK;
-- The value is gone. nextval will skip it permanently.

-- You can check current sequence value without advancing it
SELECT last_value FROM orders_order_id_seq;

Quick Fix Solutions

Fix 1: Expand the sequence's MAXVALUE immediately (zero downtime)

-- Immediate relief with no table lock required
ALTER SEQUENCE orders_order_id_seq MAXVALUE 9223372036854775807;

Fix 2: Change the column type to BIGINT

-- Upgrade the column and its backing sequence
ALTER TABLE orders ALTER COLUMN order_id TYPE BIGINT;
ALTER SEQUENCE orders_order_id_seq MAXVALUE 9223372036854775807;

Fix 3: Reset sequence to current max (emergency recovery)

-- If inserts are already failing, resync the sequence
SELECT SETVAL(
    'orders_order_id_seq',
    (SELECT MAX(order_id) FROM orders),
    true
);

Fix 4: Migrate to IDENTITY column (recommended long-term)

-- Best practice for new tables in PostgreSQL 10+
CREATE TABLE orders_new (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

Prevention Tips

1. Monitor sequence usage proactively.
Schedule the query below as a cron job or integrate it into your monitoring stack. Alert when usage exceeds 80%.

SELECT sequencename, last_value, max_value,
    ROUND((last_value::NUMERIC / NULLIF(max_value,0)) * 100, 2) AS used_pct
FROM pg_sequences
WHERE (last_value::NUMERIC / NULLIF(max_value,0)) > 0.8;

2. Standardize on BIGINT GENERATED ALWAYS AS IDENTITY or UUID for all new tables.
Treat SERIAL as a legacy type. BIGINT identity columns give you ~9.2 quintillion values, while UUID eliminates the exhaustion problem entirely.

-- Preferred modern pattern
CREATE TABLE events (
    event_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    payload JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

Related Errors

23505 unique_violation — Can occur if CYCLE is enabled and reused values collide with existing primary keys.
55000 object_not_in_prerequisite_state — Raised when calling nextval() on an already-exhausted sequence without CYCLE.

Oracle ORA-00472 Error: Causes and Solutions Complete Guide

umzzil nng — Fri, 12 Jun 2026 06:01:29 +0000

ORA-00472: PMON Process Terminated with Error

ORA-00472 is a critical Oracle database error that occurs when the PMON (Process Monitor) background process terminates abnormally. PMON is responsible for cleaning up failed user processes, releasing locks, rolling back uncommitted transactions, and freeing system resources — making it indispensable to Oracle's normal operation. When PMON goes down, the entire database instance typically crashes, requiring immediate attention.

Top 3 Causes

1. Operating System Resource Exhaustion

When the OS runs out of memory, file descriptors, or semaphores, PMON cannot allocate the resources it needs and terminates. Misconfigured kernel parameters are a very common root cause on Linux/Unix systems.

-- Check current process and session usage vs. limits
SELECT
    (SELECT COUNT(*) FROM v$process) AS current_processes,
    (SELECT value FROM v$parameter WHERE name = 'processes') AS max_processes,
    (SELECT COUNT(*) FROM v$session) AS current_sessions,
    (SELECT value FROM v$parameter WHERE name = 'sessions') AS max_sessions
FROM dual;

-- Review key memory parameters
SELECT name, value
FROM v$parameter
WHERE name IN ('sga_target','sga_max_size','pga_aggregate_target','memory_target')
ORDER BY name;

2. SGA Corruption or Memory Errors

Hardware faults or external processes writing into Oracle's shared memory can corrupt the SGA. When PMON reads a corrupted memory structure, it immediately crashes. This usually appears alongside ORA-00600 or ORA-07445 in the alert log.

-- Check alert log for related errors (Oracle 11g+)
SELECT originating_timestamp, message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%PMON%'
   OR message_text LIKE '%ORA-006%'
   OR message_text LIKE '%ORA-07445%'
ORDER BY originating_timestamp DESC
FETCH FIRST 30 ROWS ONLY;

-- Inspect SGA component health
SELECT name, bytes, resizeable
FROM v$sgainfo
ORDER BY bytes DESC;

3. Oracle Software Bug / Missing Patch

Certain Oracle releases contain known bugs that cause PMON to crash under specific conditions. Running without the latest PSU or Release Update exposes your instance to these defects.

-- Check Oracle version and applied patches
SELECT * FROM v$version;

-- List applied patches (Oracle 12c and above)
SELECT patch_id, version, action, status, description
FROM dba_registry_sqlpatch
ORDER BY action_time DESC;

-- Verify component registry status after patching
SELECT comp_name, version, status
FROM dba_registry
ORDER BY comp_name;

Quick Fix Solutions

Step 1 — Check the alert log and trace files first.

-- Find the diagnostic trace directory
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

Review alert_<SID>.log for the exact sequence of errors leading up to ORA-00472.

Step 2 — Restart the database (if it has not restarted automatically) and verify all background processes are running.

-- Confirm critical background processes are alive
SELECT pname, spid, background
FROM v$process
WHERE pname IN ('PMON','SMON','DBWR','LGWR','CKPT','RECO')
ORDER BY pname;

Step 3 — Validate OS kernel parameters against Oracle's installation guide (kernel.shmmax, kernel.sem, fs.file-max, ulimit settings for the oracle OS user).

Step 4 — Apply the latest Oracle Release Update (RU) if a known bug is identified via Oracle MOS (My Oracle Support). Open an SR if ORA-00600 or ORA-07445 appears alongside ORA-00472.

Prevention Tips

1. Continuous Background Process Monitoring

Schedule the following query via a cron job or Oracle Scheduler to detect anomalies early and trigger alerts before a full crash occurs.

-- Scheduled health check for critical background processes
SELECT
    p.pname,
    p.spid AS os_pid,
    NVL(s.status, 'NO SESSION') AS session_status,
    s.last_call_et AS idle_seconds
FROM v$process p
LEFT JOIN v$session s ON p.addr = s.paddr
WHERE p.background = 1
  AND p.pname IN ('PMON','SMON','DBWR','LGWR','CKPT','RECO')
ORDER BY p.pname;

2. Patch Regularly and Right-Size OS Parameters

Establish a quarterly patching cadence using Oracle's recommended PSU/RU releases. Always validate OS kernel parameters (shmmax, shmall, sem, file-max) against Oracle's platform-specific installation guide before go-live and after any infrastructure changes. Keeping both the OS and Oracle software in a supported, up-to-date state is the single most effective way to prevent ORA-00472 recurrence.

Related Errors

Error Code	Description
ORA-00470	LGWR process terminated with error
ORA-00471	DBWR process terminated with error
ORA-00474	SMON process terminated with error
ORA-00600	Internal Oracle error — often the root cause behind PMON crash
ORA-07445	OS signal exception — memory violation that can kill PMON

PostgreSQL 22003 Error: Causes and Solutions Complete Guide

umzzil nng — Fri, 12 Jun 2026 00:03:08 +0000

PostgreSQL Error 22003: Numeric Value Out of Range

PostgreSQL error code 22003 (numeric_value_out_of_range) is raised when you attempt to store or compute a value that exceeds the boundaries of a numeric data type. This can happen during a simple INSERT, an UPDATE, or even a complex arithmetic operation inside a query. It is one of the most common data integrity errors in production environments, especially during data migrations or high-volume batch processing.

Top 3 Causes

1. Inserting a Value Beyond the Column's Integer Range

Each PostgreSQL integer type has a hard limit: SMALLINT holds up to 32,767, INTEGER up to ~2.1 billion, and BIGINT up to ~9.2 quintillion.

-- Problematic: SMALLINT column can't hold 40000
CREATE TABLE orders (
    id       SERIAL PRIMARY KEY,
    quantity SMALLINT
);

INSERT INTO orders (quantity) VALUES (40000);
-- ERROR:  smallint out of range

-- Fix: Alter the column to a wider type
ALTER TABLE orders
    ALTER COLUMN quantity TYPE INTEGER;

INSERT INTO orders (quantity) VALUES (40000);
-- INSERT 0 1

2. NUMERIC Precision and Scale Overflow

A NUMERIC(p, s) column can only store values where the total significant digits fit within p. For example, NUMERIC(5, 2) maxes out at 999.99.

-- Problematic: NUMERIC(5,2) cannot store 1000.00
CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    price NUMERIC(5, 2)
);

INSERT INTO products (price) VALUES (1000.00);
-- ERROR:  numeric field overflow
-- DETAIL: A field with precision 5, scale 2 must round to an
--         absolute value less than 10^3.

-- Fix: Increase precision
ALTER TABLE products
    ALTER COLUMN price TYPE NUMERIC(12, 2);

INSERT INTO products (price) VALUES (1000.00);
-- INSERT 0 1

3. Arithmetic Overflow During Computation

Multiplying two INTEGER columns can silently overflow if the result exceeds ~2.1 billion. This is especially dangerous inside SUM() aggregates on large datasets.

-- Problematic: INTEGER * INTEGER can overflow
SELECT unit_price * quantity AS total FROM sales;
-- ERROR:  integer out of range

-- Fix: Cast operands to BIGINT before multiplying
SELECT unit_price::BIGINT * quantity::BIGINT AS total FROM sales;

-- Safe aggregate
SELECT SUM(unit_price::BIGINT * quantity::BIGINT) AS grand_total
FROM sales;

Quick Fix Solutions

-- 1. Check the actual max/min values before migrating
SELECT MAX(quantity), MIN(quantity) FROM orders;

-- 2. Detect out-of-range rows before altering a column
SELECT id, quantity
FROM orders
WHERE quantity > 32767 OR quantity < -32768;

-- 3. Use explicit casting in calculations
SELECT CAST(col_a AS BIGINT) + CAST(col_b AS BIGINT) FROM my_table;

-- 4. Check column type metadata
SELECT column_name, data_type, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_name = 'products';

Prevention Tips

1. Design schemas with growth in mind.
Always choose a type one level larger than your current maximum. Use BIGINT for counters and IDs, and NUMERIC(15, 2) or wider for monetary values. Refactoring a column type in production is costly and risky.

2. Add CHECK constraints and monitor range utilization.

-- Guard against business rule violations
ALTER TABLE products
    ADD CONSTRAINT chk_price_positive
    CHECK (price > 0 AND price < 1000000000);

-- Periodically monitor how close you are to the type limit
SELECT MAX(quantity)::NUMERIC / 2147483647 * 100 AS pct_used
FROM orders;

If pct_used exceeds 70–80%, it is time to plan a type upgrade before it becomes an outage.

Related Errors

Code	Name	Brief Description
`22001`	`string_data_right_truncation`	String value too long for the column
`22P02`	`invalid_text_representation`	Invalid cast from text to a numeric type
`22012`	`division_by_zero`	Arithmetic division by zero
`22023`	`invalid_parameter_value`	Function argument outside allowed range

Oracle ORA-00471 Error: Causes and Solutions Complete Guide

umzzil nng — Fri, 12 Jun 2026 00:01:39 +0000

ORA-00471: DBWR Process Terminated with Error — Causes, Fixes & Prevention

ORA-00471 is one of the most critical Oracle errors a DBA can encounter, occurring when the Database Writer (DBWR) background process terminates abnormally. DBWR is responsible for writing dirty buffers from the SGA buffer cache to datafiles on disk, and its failure immediately causes the entire database instance to crash. Prompt diagnosis and action are essential to minimize downtime and prevent data loss.

Top 3 Causes

1. OS-Level I/O Errors or Storage Failures

The most common cause is an I/O failure at the OS level while DBWR attempts to write blocks to datafiles. Faulty disk sectors, disconnected SAN/NAS volumes, or unmounted NFS shares will all cause DBWR to fail immediately.

Check for I/O errors in the alert log:

-- Find DBWR-related errors in alert log
SELECT ORIGINATING_TIMESTAMP, MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE '%DBWR%'
   OR MESSAGE_TEXT LIKE '%ORA-00471%'
   OR MESSAGE_TEXT LIKE '%I/O%'
ORDER BY ORIGINATING_TIMESTAMP DESC
FETCH FIRST 20 ROWS ONLY;

-- Check datafile status
SELECT FILE#, NAME, STATUS, BYTES/1024/1024 AS SIZE_MB
FROM V$DATAFILE
WHERE STATUS != 'ONLINE';

Also check OS-level logs (dmesg, /var/log/messages) for hardware-level I/O errors alongside Oracle alerts.

2. Tablespace / Filesystem Full or Permission Issues

If the filesystem hosting the datafiles is full, DBWR cannot write dirty blocks even if AUTOEXTEND is enabled. Changed file permissions on the Oracle account can also prevent writes entirely.

-- Check tablespace usage
SELECT
    TABLESPACE_NAME,
    ROUND(USED_PERCENT, 2) AS USED_PCT,
    CASE WHEN USED_PERCENT >= 90 THEN 'CRITICAL'
         WHEN USED_PERCENT >= 80 THEN 'WARNING'
         ELSE 'NORMAL' END AS STATUS
FROM DBA_TABLESPACE_USAGE_METRICS
ORDER BY USED_PERCENT DESC;

-- Add a datafile to resolve space issues
ALTER TABLESPACE USERS
ADD DATAFILE '/u01/app/oracle/oradata/ORCL/users02.dbf'
SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

3. SGA Misconfiguration or OS Memory Exhaustion

An oversized SGA that exceeds available physical memory can cause DBWR to fail when the OS (particularly Linux OOM Killer) forcibly terminates Oracle processes to reclaim memory.

-- Check current SGA configuration
SELECT COMPONENT,
       CURRENT_SIZE/1024/1024 AS CURRENT_MB,
       MAX_SIZE/1024/1024 AS MAX_MB
FROM V$SGA_DYNAMIC_COMPONENTS;

-- View SGA and PGA targets
SHOW PARAMETER SGA_TARGET;
SHOW PARAMETER PGA_AGGREGATE_TARGET;

-- Adjust SGA (keep within 60-70% of total RAM)
ALTER SYSTEM SET SGA_TARGET = 4G SCOPE=SPFILE;
ALTER SYSTEM SET DB_WRITER_PROCESSES = 4 SCOPE=SPFILE;

Quick Fix Solutions

-- Step 1: Take damaged datafile offline and recover
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' OFFLINE;

-- Step 2: Run RMAN recovery (from RMAN prompt)
-- RMAN> RESTORE DATAFILE '/u01/oradata/ORCL/users01.dbf';
-- RMAN> RECOVER DATAFILE '/u01/oradata/ORCL/users01.dbf';

-- Step 3: Bring datafile back online
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' ONLINE;

-- Step 4: Verify RMAN backup health
SELECT INPUT_TYPE, STATUS, START_TIME,
       ROUND(OUTPUT_BYTES/1024/1024/1024, 2) AS OUTPUT_GB
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY START_TIME DESC
FETCH FIRST 5 ROWS ONLY;

If the database cannot be opened, start in MOUNT mode, perform recovery, then open:

STARTUP MOUNT;
RECOVER DATABASE;
ALTER DATABASE OPEN;

Prevention Tips

Proactive monitoring: Schedule regular jobs to check tablespace usage, free disk space, and OS memory. Alert when tablespace utilization exceeds 80%. Use Oracle OEM or tools like Zabbix/Prometheus for real-time alerting.
Solid backup strategy: Configure RMAN with a tested backup schedule. Always run in ARCHIVELOG mode and maintain a properly sized Flash Recovery Area (FRA). Periodically run restore/recovery tests — a backup you haven't tested is a backup you can't trust.

Related Errors

Error Code	Description
ORA-00470	LGWR process terminated — often seen alongside ORA-00471
ORA-00472	PMON process terminated — cascading background process failure
ORA-01114	I/O error writing block to file — direct trigger for ORA-00471
ORA-27063	Bytes read/written mismatch — storage-level I/O inconsistency

When ORA-00471 occurs, always examine the full alert log context — the root cause error is almost always logged just before the ORA-00471 message itself.

PostgreSQL 22002 Error: Causes and Solutions Complete Guide

umzzil nng — Thu, 11 Jun 2026 14:03:00 +0000

PostgreSQL Error 22002: null value no indicator parameter

PostgreSQL error code 22002 occurs when a database column containing a NULL value is fetched into a host variable that has no associated indicator variable to signal the NULL condition. This error is most commonly encountered in Embedded SQL (ECPG) programs written in C, where the runtime cannot map a NULL database value to a plain C data type without an indicator. If left unhandled, it causes an abrupt runtime failure that can be difficult to diagnose in production.

Top 3 Causes

1. Missing Indicator Variable in ECPG Host Variable Binding

The most frequent cause is declaring a host variable for a nullable column without a paired indicator variable.

-- WRONG: No indicator variable for nullable salary column
EXEC SQL SELECT emp_name, salary
         INTO :emp_name, :salary       -- salary can be NULL!
         FROM employees WHERE emp_id = 1;

-- CORRECT: Declare and use indicator variables
EXEC SQL BEGIN DECLARE SECTION;
    char  emp_name[100];
    short emp_name_ind;   /* indicator for emp_name */
    float salary;
    short salary_ind;     /* indicator for salary */
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT emp_name, salary
         INTO :emp_name :emp_name_ind, :salary :salary_ind
         FROM employees WHERE emp_id = 1;

-- Check NULL with indicator (< 0 means NULL)
if (salary_ind < 0) {
    printf("Salary is NULL\n");
}

2. FETCH from Cursor with NULL Columns (e.g., OUTER JOINs)

Using a cursor to FETCH rows that include NULL values from LEFT/RIGHT JOINs or sparse tables without indicator variables triggers error 22002 at runtime.

-- Cursor that may produce NULL dept_name via LEFT JOIN
EXEC SQL DECLARE cur CURSOR FOR
    SELECT e.emp_id, e.emp_name, d.dept_name
    FROM employees e
    LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- CORRECT FETCH with indicator variables
EXEC SQL FETCH cur
    INTO :emp_id,
         :emp_name  :emp_name_ind,
         :dept_name :dept_name_ind;  /* dept_name can be NULL */

if (dept_name_ind < 0) {
    printf("No department assigned.\n");
}

3. Avoid NULL at the Database Level Using COALESCE or NOT NULL Constraints

If refactoring the host application is not immediately feasible, you can prevent NULLs from reaching the host variable by substituting defaults in the query itself.

-- Use COALESCE to replace NULL with a safe default
SELECT
    emp_id,
    COALESCE(emp_name, 'Unknown')     AS emp_name,
    COALESCE(salary,   0.0)           AS salary,
    COALESCE(dept_name, 'N/A')        AS dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- Or enforce NOT NULL at the schema level
ALTER TABLE employees
    ALTER COLUMN salary SET NOT NULL,
    ALTER COLUMN salary SET DEFAULT 0.0;

-- Identify all nullable columns in a table
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'employees'
  AND is_nullable = 'YES'
ORDER BY ordinal_position;

Quick Fix Solutions

Always pair indicator variables with every host variable that maps to a nullable column — make this a non-negotiable coding standard.
Use COALESCE in your SQL queries to substitute NULLs with sensible defaults when you cannot change the host program immediately.
Apply NOT NULL constraints at the schema level for columns that should never be NULL, preventing the issue at the data layer.
Audit your table schema with information_schema.columns to identify all nullable columns and ensure every corresponding host variable has an indicator.

Prevention Tips

Include NULL data in your test suite. Every integration test should contain at least one row with NULL values in nullable columns. This catches missing indicator variables before they hit production.
Add a code review checklist item. For any ECPG or low-level ODBC code, explicitly verify that every host variable bound to a nullable column has a corresponding indicator variable. Automate this check with static analysis tools where possible.

Related errors: **22001* (string data right truncation), 22003 (numeric value out of range), 22000 (data exception — parent class), 42804 (datatype mismatch).*

Oracle ORA-00470 Error: Causes and Solutions Complete Guide

umzzil nng — Thu, 11 Jun 2026 14:01:33 +0000

ORA-00470: LGWR Process Terminated with Error — Causes, Fixes & Prevention

ORA-00470 is a critical Oracle database error that occurs when the Log Writer (LGWR) background process terminates abnormally. LGWR is responsible for writing redo entries from the SGA's Redo Log Buffer to the online redo log files, and its failure causes an immediate database crash. This error is always accompanied by additional diagnostic messages in the alert.log and LGWR trace files that must be analyzed to determine the root cause.

Top 3 Causes

1. Redo Log File I/O Failure or Inaccessibility

The most common cause is the inability of LGWR to write to the online redo log files due to disk failure, filesystem issues, or permission errors. Without redo log multiplexing, a single disk failure becomes a single point of failure for the entire database.

-- Check redo log file status immediately after the error
SELECT a.group#, a.status, a.members, b.member, b.status AS file_status
FROM v$log a
JOIN v$logfile b ON a.group# = b.group#
ORDER BY a.group#;

-- Check for invalid or stale log members
SELECT group#, member, status
FROM v$logfile
WHERE status IN ('INVALID', 'STALE');

2. OS-Level Resource Exhaustion

Insufficient OS file descriptors, out-of-memory conditions, or misconfigured kernel parameters can prevent LGWR from performing normal file I/O. On Linux systems, the OOM Killer may forcibly terminate the LGWR process when system memory is critically low.

-- Check current Oracle initialization parameters related to LGWR
SELECT name, value, description
FROM v$parameter
WHERE name IN ('log_buffer', 'db_block_size', 'log_archive_dest_state_1');

-- Review archive log space usage
SELECT dest_name, status, target, archiver, schedule,
       destination, fail_count, error
FROM v$archive_dest
WHERE status != 'INACTIVE';

3. Oracle Internal Bug or Memory Corruption

Known Oracle bugs or SGA memory corruption can trigger unexpected LGWR termination. In these cases, ORA-00470 often appears alongside ORA-00600 (internal error) in the alert.log.

-- Identify recent incidents in the ADR (11g and above)
SELECT incident_id, create_time, problem_key, incident_status
FROM v$diag_incident
ORDER BY create_time DESC
FETCH FIRST 5 ROWS ONLY;

-- Locate the LGWR trace file path
SELECT value
FROM v$diag_info
WHERE name = 'Diag Trace';

Quick Fix Solutions

Step 1 — Check alert.log and identify the root error:
Always look at the errors recorded before ORA-00470 in the alert.log. The actual root cause (e.g., ORA-27063, ORA-00345) will be listed there.

Step 2 — Add redo log multiplexing if missing:

-- Add a second member to each redo log group (different disk)
ALTER DATABASE ADD LOGFILE MEMBER '/u02/redo/redo01b.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/u02/redo/redo02b.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/u02/redo/redo03b.log' TO GROUP 3;

Step 3 — Restart the database after resolving the underlying issue:

SHUTDOWN ABORT;
-- Resolve disk/OS issue at OS level first
STARTUP MOUNT;
-- Verify redo log files are accessible
SELECT group#, status, archived FROM v$log;
ALTER DATABASE OPEN;

Prevention Tips

1. Always multiplex redo log files across separate physical disks. A minimum of two members per group on different storage paths is mandatory in any production environment.

-- Audit redo log groups with only one member (run regularly)
SELECT group#, COUNT(*) AS member_count
FROM v$logfile
GROUP BY group#
HAVING COUNT(*) < 2;

2. Implement proactive monitoring with Oracle Enterprise Manager or custom scripts. Monitor redo log status, archive destination space, and OS metrics (disk I/O, memory) continuously. Set alerts for INVALID or STALE log file members before they escalate into an ORA-00470 outage.

Related Errors

Error Code	Description
ORA-00313	Failure to open redo log — often precedes ORA-00470
ORA-00345	Redo log write error during LGWR I/O
ORA-00321	Error updating log file header
ORA-00600	Internal error — may appear with ORA-00470 if a bug is involved
ORA-27063/27072	OS-level I/O errors recorded alongside LGWR failure

When ORA-00470 appears with ORA-00600, open an Oracle Support Service Request (SR) immediately and provide the full alert.log excerpt and the LGWR trace file.

PostgreSQL 22004 Error: Causes and Solutions Complete Guide

umzzil nng — Thu, 11 Jun 2026 10:03:12 +0000

PostgreSQL Error 22004: null value not allowed

PostgreSQL error code 22004 — "null value not allowed" — occurs when a NULL value is passed to a context that explicitly prohibits it, such as a STRICT function, a domain type with a NOT NULL constraint, or certain array operations. Unlike error 23502 (not_null_violation), which applies at the table column level, error 22004 is raised at the type or function parameter level. Understanding the distinction is key to diagnosing and fixing it quickly.

Top 3 Causes

1. Passing NULL to a STRICT Function

When a function is defined with the STRICT keyword, PostgreSQL automatically returns NULL if any argument is NULL. However, in some configurations or PL/pgSQL blocks, this can surface as a 22004 error.

-- STRICT function definition
CREATE OR REPLACE FUNCTION multiply(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
    RETURN a * b;
END;
$$;

-- Triggers 22004 or returns NULL unexpectedly
SELECT multiply(5, NULL);

-- Fix: Remove STRICT and handle NULL explicitly
CREATE OR REPLACE FUNCTION multiply_safe(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    IF a IS NULL OR b IS NULL THEN
        RETURN 0;  -- or return NULL based on business logic
    END IF;
    RETURN a * b;
END;
$$;

SELECT multiply_safe(5, NULL);  -- Returns 0, no error

2. Inserting NULL into a Domain Type with NOT NULL Constraint

User-defined domain types can carry a NOT NULL constraint. Any attempt to assign NULL to a column or variable of that domain type raises 22004.

-- Create a domain with NOT NULL
CREATE DOMAIN positive_amount AS NUMERIC NOT NULL CHECK (VALUE >= 0);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    amount positive_amount
);

-- Raises ERROR 22004
INSERT INTO orders (amount) VALUES (NULL);

-- Fix: Use COALESCE before casting
INSERT INTO orders (amount)
SELECT COALESCE(NULL, 0)::positive_amount;  -- Inserts 0 safely

-- Or alter the domain if NULL should be allowed
ALTER DOMAIN positive_amount DROP NOT NULL;

3. NULL Elements in a Domain-Typed Array

Inserting an array that contains NULL elements into a column typed as an array of a NOT NULL domain will also trigger 22004.

-- Domain with NOT NULL
CREATE DOMAIN strict_int AS INTEGER NOT NULL;

CREATE TABLE batch_data (
    id SERIAL PRIMARY KEY,
    values strict_int[]
);

-- Raises ERROR 22004 due to NULL in array
INSERT INTO batch_data (values) VALUES (ARRAY[1, 2, NULL]::strict_int[]);

-- Fix: Remove NULLs before inserting
INSERT INTO batch_data (values)
VALUES (ARRAY_REMOVE(ARRAY[1, 2, NULL, 3], NULL)::strict_int[]);

-- Verify domain constraints
SELECT domain_name, is_nullable
FROM information_schema.domains
WHERE domain_schema = 'public';

Quick Fix Solutions

Use COALESCE to replace NULL with a safe default before passing values to domain-typed columns or STRICT functions.
Handle exceptions in PL/pgSQL using the null_value_not_allowed condition name.
Remove STRICT from function definitions and implement explicit NULL checks inside the function body.

-- Exception handling example
DO $$
BEGIN
    PERFORM some_strict_function(NULL);
EXCEPTION
    WHEN null_value_not_allowed THEN
        RAISE NOTICE 'Caught 22004: NULL value was not allowed here.';
END;
$$;

Prevention Tips

Use a staging table with plain types to validate and clean data before loading it into tables with domain-typed columns. This creates a safe buffer zone where NULLs can be caught and handled before they cause errors.
Document domain types with COMMENT and always run NULL-scenario tests in a QA environment before deploying schema changes. Track domain dependencies using the pg_attribute and pg_type catalog tables to understand the blast radius of any constraint change.

-- Find all columns using a specific domain
SELECT c.relname AS table_name, a.attname AS column_name
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname = 'positive_amount'
  AND c.relkind = 'r';

Related Errors

23502 not_null_violation — Column-level NOT NULL constraint breach; often confused with 22004.
22023 invalid_parameter_value — Invalid value passed to a function parameter.
42804 datatype_mismatch — Type mismatch when working with domain types.

Oracle ORA-00447 Error: Causes and Solutions Complete Guide

umzzil nng — Thu, 11 Jun 2026 10:01:34 +0000

ORA-00447: Fatal Error in Background Process — Causes, Fixes & Prevention

ORA-00447 is one of Oracle's most severe errors, signaling that a critical failure has occurred within a background process such as PMON, SMON, LGWR, or DBWR. Unlike typical SQL errors, this error directly threatens instance stability and almost always results in an instance crash. It is typically found in the alert log alongside related errors like ORA-00603, ORA-07445, or ORA-00600.

Top 3 Causes

1. Memory Shortage or SGA/PGA Corruption

When the System Global Area (SGA) or Program Global Area (PGA) runs out of memory, or when memory becomes internally corrupted, background processes can encounter fatal conditions they cannot recover from. OS-level Out of Memory (OOM) events that force-kill Oracle processes are a common culprit.

-- Check SGA component sizes
SELECT NAME, BYTES/1024/1024 AS SIZE_MB
FROM V$SGAINFO
ORDER BY BYTES DESC;

-- Check PGA usage
SELECT NAME, VALUE/1024/1024 AS VALUE_MB
FROM V$PGASTAT
WHERE NAME IN ('total PGA inuse','total PGA allocated','maximum PGA allocated');

-- Adjust SGA if undersized (requires restart)
ALTER SYSTEM SET SGA_TARGET = 4G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 1G SCOPE=SPFILE;

2. Disk I/O Errors Affecting Redo Logs or Datafiles

The LGWR process writes continuously to redo log files. Any disk I/O failure on the storage hosting those files will cause LGWR to raise a fatal error, triggering ORA-00447. Storage firmware bugs and filesystem corruption fall into this category as well.

-- Check redo log file status
SELECT L.GROUP#, L.STATUS, L.ARCHIVED, LF.MEMBER, LF.STATUS AS FILE_STATUS
FROM V$LOG L
JOIN V$LOGFILE LF ON L.GROUP# = LF.GROUP#
ORDER BY L.GROUP#;

-- Add a new log member to a suspect group
ALTER DATABASE ADD LOGFILE MEMBER
'/u01/oradata/redo01b.log' TO GROUP 1;

-- Add a new redo log group for redundancy
ALTER DATABASE ADD LOGFILE GROUP 5
('/u01/oradata/redo05a.log', '/u01/oradata/redo05b.log')
SIZE 500M;

3. Oracle Software Bug (Internal Error)

A bug within Oracle's own codebase can cause a background process to hit an unhandled exception and terminate fatally. These cases are typically identified by ORA-00600 or ORA-07445 errors appearing alongside ORA-00447 in the alert log.

-- Check recent incidents in ADR
SELECT INCIDENT_ID, CREATE_TIME, PROBLEM_KEY, INCIDENT_STATUS
FROM V$DIAG_INCIDENT
ORDER BY CREATE_TIME DESC
FETCH FIRST 10 ROWS ONLY;

-- Check installed patch history
SELECT PATCH_ID, VERSION, ACTION, STATUS, ACTION_TIME
FROM DBA_REGISTRY_SQLPATCH
ORDER BY ACTION_TIME DESC;

-- Find ADR trace file location
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

Quick Fix Solutions

-- Step 1: Review alert log for error context
SELECT ORIGINATING_TIMESTAMP, MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE '%ORA-%'
  AND ORIGINATING_TIMESTAMP >= SYSDATE - 1
ORDER BY ORIGINATING_TIMESTAMP DESC;

-- Step 2: Check background process health
SELECT PNAME, DESCRIPTION, BACKGROUND
FROM V$PROCESS
WHERE BACKGROUND = 1
ORDER BY PNAME;

-- Step 3: Restart instance after root cause analysis
SHUTDOWN ABORT;
STARTUP;

-- Step 4: Perform media recovery if needed
RECOVER DATABASE;

Prevention Tips

Monitor proactively. Set up automated alerts for SGA/PGA usage thresholds, redo log I/O latency, and background process response times using Oracle Enterprise Manager or custom scripts.

-- Quick SGA usage check for monitoring scripts
SELECT ROUND((1 - (F.BYTES / T.BYTES)) * 100, 2) AS SGA_USED_PCT
FROM (SELECT SUM(BYTES) BYTES FROM V$SGASTAT WHERE NAME = 'free memory') F,
     (SELECT SUM(BYTES) BYTES FROM V$SGASTAT) T;

Patch regularly. Apply Oracle Critical Patch Updates (CPU) and Patch Set Updates (PSU) on a scheduled basis. Review My Oracle Support (MOS) for known bugs affecting background processes in your specific Oracle version, and prioritize those patches.

Related Errors

Error Code	Description
ORA-00603	Server session terminated by fatal error
ORA-07445	Exception encountered: core dump
ORA-00600	Internal error code — key diagnostic clue
ORA-04031	Unable to allocate shared memory (SGA exhaustion)
ORA-01114	I/O error writing block to file (disk failure)

DEV Community: umzzil nng

PostgreSQL 22001 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22001: String Data Right Truncation

Top 3 Causes

1. Inserting Data Longer Than Column Definition

2. Schema Not Updated After Business Requirement Changes

3. Data Migration or ETL Mismatches

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-00474 Error: Causes and Solutions Complete Guide

ORA-00474: SMON Process Terminated with Error

Top 3 Causes

1. Data Dictionary or System Tablespace Corruption

2. Rollback / UNDO Segment Issues During Instance Recovery

3. OS-Level Resource Exhaustion (OOM / ulimit)

Quick Fix Solutions

Prevention Tips

Related Errors

PostgreSQL 22026 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22026: String Data Length Mismatch

Top 3 Causes

1. Invalid bytea Encoding or Malformed Hex String

2. Fixed-Length char(n) Type Mismatch During Custom Casting

3. Custom or Composite Type Binary Layout Mismatch

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-00473 Error: Causes and Solutions Complete Guide

ORA-00473: ARCH Process Terminated with Error — Causes, Fixes & Prevention

Top 3 Causes

1. Archive Log Destination Disk Full

2. Invalid or Inaccessible Archive Destination Path

3. Underlying I/O or Hardware Error

Quick Fix Solutions

Prevention Tips

Related Errors

PostgreSQL 2200H Error: Causes and Solutions Complete Guide

PostgreSQL Error 2200H: Sequence Generator Limit Exceeded

Top 3 Causes

1. SERIAL Column Hitting the INT4 Ceiling (~2.1 Billion)

2. Custom MAXVALUE Set Too Low

3. Rollbacks Silently Consuming Sequence Values

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-00472 Error: Causes and Solutions Complete Guide

ORA-00472: PMON Process Terminated with Error

Top 3 Causes

1. Operating System Resource Exhaustion

2. SGA Corruption or Memory Errors

3. Oracle Software Bug / Missing Patch

Quick Fix Solutions

Prevention Tips

Related Errors

PostgreSQL 22003 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22003: Numeric Value Out of Range

Top 3 Causes

1. Inserting a Value Beyond the Column's Integer Range

2. NUMERIC Precision and Scale Overflow

3. Arithmetic Overflow During Computation

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-00471 Error: Causes and Solutions Complete Guide

ORA-00471: DBWR Process Terminated with Error — Causes, Fixes & Prevention

Top 3 Causes

1. OS-Level I/O Errors or Storage Failures

2. Tablespace / Filesystem Full or Permission Issues

3. SGA Misconfiguration or OS Memory Exhaustion

Quick Fix Solutions

Prevention Tips

Related Errors

PostgreSQL 22002 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22002: null value no indicator parameter

Top 3 Causes

1. Missing Indicator Variable in ECPG Host Variable Binding

2. FETCH from Cursor with NULL Columns (e.g., OUTER JOINs)

3. Avoid NULL at the Database Level Using COALESCE or NOT NULL Constraints

Quick Fix Solutions

1. Invalid `bytea` Encoding or Malformed Hex String

2. Fixed-Length `char(n)` Type Mismatch During Custom Casting

1. `SERIAL` Column Hitting the INT4 Ceiling (~2.1 Billion)

2. Custom `MAXVALUE` Set Too Low