DEV Community: umzzil nng

PostgreSQL 42804 Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 06 Jul 2026 14:03:02 +0000

PostgreSQL Error 42804: datatype mismatch

PostgreSQL error code 42804 (datatype mismatch) occurs when your SQL query, function, or expression involves incompatible data types that PostgreSQL cannot implicitly reconcile. Because PostgreSQL enforces a strict type system, any mismatch between expected and actual types will immediately halt execution to protect data integrity. This error is especially common in CASE expressions, UNION queries, and user-defined functions.

Top 3 Causes

1. CASE Expression or UNION Type Mismatch

The most frequent trigger: each branch of a CASE expression returns a different type, or UNION query columns don't align in type.

-- ERROR: CASE types integer and text cannot be matched
SELECT
    CASE
        WHEN score >= 90 THEN 1       -- integer
        ELSE 'Pass'                   -- text
    END AS result
FROM students;

-- FIX: Cast all branches to the same type
SELECT
    CASE
        WHEN score >= 90 THEN 1::TEXT
        ELSE 'Pass'
    END AS result
FROM students;

-- UNION mismatch fix
-- Bad
SELECT id, name FROM employees
UNION
SELECT id, salary FROM employees;  -- text vs numeric

-- Good
SELECT id, name FROM employees
UNION
SELECT id, salary::TEXT FROM employees;

2. Function Return Type Mismatch

When a PL/pgSQL function's declared RETURNS type doesn't match what the function body actually returns.

-- ERROR: return type mismatch in function declared to return integer
CREATE OR REPLACE FUNCTION get_code(emp_id INT)
RETURNS INTEGER AS $$
BEGIN
    RETURN 'EMP-' || emp_id::TEXT;  -- returning TEXT, not INTEGER
END;
$$ LANGUAGE plpgsql;

-- FIX: Align the RETURNS clause with actual return value
CREATE OR REPLACE FUNCTION get_code(emp_id INT)
RETURNS TEXT AS $$
BEGIN
    RETURN 'EMP-' || emp_id::TEXT;
END;
$$ LANGUAGE plpgsql;

3. Array Constructor or Composite Type Mismatch

Mixing types inside an ARRAY[...] constructor or inserting mismatched values into a composite/table type.

-- ERROR: array cannot be created from components of different types
SELECT ARRAY[1, 2, 'three'];

-- FIX: Cast all elements to the same type
SELECT ARRAY[1::TEXT, 2::TEXT, 'three'];

-- Table insert mismatch fix
-- Bad: inserting TEXT into a NUMERIC column
INSERT INTO products (price, quantity) VALUES ('fifteen', 100);

-- Good: use correct type or explicit cast
INSERT INTO products (price, quantity) VALUES (15.00, 100);
-- or
INSERT INTO products (price, quantity) VALUES ('15.00'::NUMERIC, 100);

Quick Fix Solutions

Scenario	Fix
`CASE` branch mismatch	Cast all `THEN`/`ELSE` values to the same type
`UNION` column mismatch	Add `::target_type` cast to mismatched columns
Function return mismatch	Update `RETURNS` clause or fix the return value
Array mixed types	Cast all array elements to a uniform type
JSONB vs plain column	Use `->>` (returns TEXT) and cast accordingly

-- Universal pattern: explicit cast using :: operator
SELECT some_column::TEXT FROM my_table;

-- Or using standard CAST()
SELECT CAST(some_column AS TEXT) FROM my_table;

-- Checking column types before writing queries
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'your_table_name';

Prevention Tips

1. Always use explicit casting — never rely on implicit coercion.
Make ::type or CAST() a standard part of your coding style whenever types might differ. Add a SQL linting step (e.g., using sqlfluff or pgTAP tests) to your CI/CD pipeline to catch type issues before they reach production.

2. Validate function signatures after schema changes.
Whenever you alter a column's data type, immediately audit all views, functions, and triggers that reference that column. Use the query below to find dependent objects:

-- Find all functions referencing a specific table
SELECT p.proname, pg_get_functiondef(p.oid)
FROM pg_proc p
WHERE pg_get_functiondef(p.oid) ILIKE '%your_table_name%';

-- Check return types of existing functions
SELECT p.proname AS function_name,
       pg_get_function_result(p.oid) AS return_type
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public';

Related Errors

42883 undefined_function — No operator or function exists for the given type combination.
22P02 invalid_text_representation — Raised when casting a TEXT value to another type fails due to bad formatting.
42P18 indeterminate_datatype — PostgreSQL cannot determine the type of a parameter or literal, common in dynamic queries.

Oracle ORA-01119 Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 06 Jul 2026 14:01:31 +0000

ORA-01119: Error in Creating Database File — Causes, Fixes & Prevention

ORA-01119 is an Oracle error that occurs when the database engine fails to create a physical datafile on the operating system level. It is most commonly triggered when adding a datafile to an existing tablespace or creating a new one, and it almost always appears alongside companion errors such as ORA-27040 or ORA-27041 that provide the underlying OS-level detail. Understanding the full error stack is essential for a fast and accurate resolution.

Top 3 Causes

1. Insufficient Disk Space

The most frequent cause. When the target filesystem has no room left, the OS rejects the file creation request.

Diagnosis:

-- Check datafile sizes and autoextend settings
SELECT
    tablespace_name,
    file_name,
    ROUND(bytes / 1024 / 1024, 2)     AS size_mb,
    ROUND(maxbytes / 1024 / 1024, 2)  AS max_size_mb,
    autoextensible
FROM dba_data_files
ORDER BY tablespace_name;

Fix — add a datafile on a volume with free space:

ALTER TABLESPACE users
ADD DATAFILE '/oradata2/PROD/users02.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 2G;

2. OS Permission / Ownership Problem

The oracle OS user lacks write permission on the target directory, or the directory was created under a different owner after a new mount point or storage expansion.

Fix — correct permissions at the OS level (run as root):

# Verify ownership and permissions
ls -ld /oradata/PROD/

# Fix ownership and permissions
chown oracle:oinstall /oradata/PROD/
chmod 755 /oradata/PROD/

Verify Oracle directory object grants if applicable:

-- Check directory object privileges
SELECT grantee, table_name AS dir_name, privilege
FROM dba_tab_privs
WHERE table_name IN (SELECT directory_name FROM dba_directories);

-- Grant write access if missing
GRANT READ, WRITE ON DIRECTORY oradata_dir TO oracle;

3. Invalid or Non-Existent File Path

A typo in the path, a missing directory, or a misconfigured DB_CREATE_FILE_DEST parameter will prevent Oracle from creating the file.

Diagnosis:

-- Check the default file destination parameter
SHOW PARAMETER db_create_file_dest;

Fix — create the missing directory (OS) then retry:

mkdir -p /oradata/PROD/new_location
chown oracle:oinstall /oradata/PROD/new_location
chmod 755 /oradata/PROD/new_location

Fix — correct the parameter and recreate the tablespace:

ALTER SYSTEM SET db_create_file_dest = '/oradata/PROD/'
SCOPE = BOTH;

CREATE TABLESPACE new_tbs
DATAFILE '/oradata/PROD/new_tbs01.dbf'
SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Quick Fix Checklist

Read the full error stack — ORA-27040/27041 will tell you the exact OS error code.
Run df -h on the target mount point to rule out disk-full conditions.
Confirm the directory exists and is owned/writable by the oracle OS user.
Double-check the path for typos before reissuing the DDL statement.
Review DB_CREATE_FILE_DEST if you are relying on Oracle Managed Files (OMF).

Prevention Tips

Monitor tablespace usage proactively. Schedule the query below as a cron job or OEM alert to catch filesystems approaching capacity before they cause outages.

-- Alert when tablespace usage exceeds 85%
SELECT
    a.tablespace_name,
    ROUND((1 - NVL(b.free_mb,0) / a.total_mb) * 100, 2) AS used_pct
FROM (
    SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
    FROM dba_data_files GROUP BY tablespace_name
) a
LEFT JOIN (
    SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
    FROM dba_free_space GROUP BY tablespace_name
) b ON a.tablespace_name = b.tablespace_name
WHERE ROUND((1 - NVL(b.free_mb,0) / a.total_mb)*100,2) >= 85
ORDER BY used_pct DESC;

Standardize directory structure and enforce MAXSIZE. Define a naming convention and directory layout for all datafiles, document it, and always set a MAXSIZE on AUTOEXTEND to prevent any single tablespace from consuming an entire filesystem. In RAC environments, verify that every node shares the same valid path before issuing storage DDL.

Related Oracle Errors

Error Code	Brief Description
ORA-27040	File create error — OS-level detail accompanying ORA-01119
ORA-27041	Unable to open file — path or permission issue
ORA-01110	Identifies the specific datafile number and name involved
ORA-01116	Error opening database file — file missing or inaccessible
ORA-19502	Write error on RMAN file — similar filesystem-level root cause

PostgreSQL 42939 Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 06 Jul 2026 10:02:56 +0000

PostgreSQL Error 42939: Reserved Name

PostgreSQL error code 42939 (reserved_name) occurs when a user attempts to create a database object — such as a type, function, or schema — using a name that PostgreSQL has reserved for internal system use. These reserved names are protected to ensure the stability and consistency of the database engine's core functionality.

Top 3 Causes

1. Using a Built-in Type Name for a Custom Type

PostgreSQL reserves names like record, array, trigger, and all built-in data type names. Attempting to create a custom type with these names triggers error 42939.

-- Bad: 'record' is a reserved internal type name
CREATE TYPE record AS (
    id INTEGER,
    label TEXT
);
-- ERROR:  42939: type name "record" is reserved

-- Good: use a descriptive, application-specific name
CREATE TYPE app_record AS (
    id INTEGER,
    label TEXT
);

2. Redefining a Reserved System Function Name

System function names such as now, user, and current_date are reserved by PostgreSQL and cannot be used as user-defined function names without conflict.

-- Bad: 'now' is a reserved system function
CREATE OR REPLACE FUNCTION now()
RETURNS TIMESTAMP AS $$
BEGIN
    RETURN CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql;
-- ERROR: 42939: function name "now" is reserved

-- Good: use a meaningful custom name
CREATE OR REPLACE FUNCTION get_server_timestamp()
RETURNS TIMESTAMP AS $$
BEGIN
    RETURN CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql;

3. Creating Objects with the `pg_` Prefix

PostgreSQL internally reserves all names beginning with pg_ for system catalog objects. Creating user objects with this prefix will raise an error or warning.

-- Bad: pg_ prefix is reserved for system use
CREATE TABLE pg_audit_log (
    id SERIAL PRIMARY KEY,
    action TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
-- ERROR or WARNING: 42939: unacceptable table name "pg_audit_log"

-- Good: use an application-specific prefix or schema
CREATE SCHEMA audit;

CREATE TABLE audit.log (
    id SERIAL PRIMARY KEY,
    action TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Quick Fix Solutions

Rename the conflicting object using an application-specific prefix or suffix.
Use a dedicated schema to namespace your objects and avoid global name collisions.
Check reserved keywords before DDL execution using the built-in function:

-- Check if a word is reserved in PostgreSQL
SELECT word, catcode, catdesc
FROM pg_get_keywords()
WHERE word = 'your_keyword_here';

-- Check if a type name is already taken by the system
SELECT typname, typtype
FROM pg_catalog.pg_type
WHERE typname = 'your_type_name';

Prevention Tips

Adopt a strict naming convention: Prefix all custom objects with an application identifier (e.g., app_, svc_, fn_). This virtually eliminates conflicts with PostgreSQL reserved names and makes objects instantly identifiable as user-defined.

-- Recommended naming pattern
CREATE TYPE app_user_status AS ENUM ('active', 'inactive', 'suspended');
CREATE FUNCTION app_get_active_users() RETURNS SETOF app_users AS $$ ... $$ LANGUAGE plpgsql;

Isolate all user objects in a dedicated schema: Never create objects directly in public or any system schema. Enforce this through role-level permissions so developers cannot accidentally pollute reserved namespaces.

-- Set up a safe application schema
CREATE SCHEMA myapp;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA myapp TO myapp_admin;

By following these practices, you can avoid 42939 errors entirely and maintain a clean, conflict-free PostgreSQL environment.

Oracle ORA-01118 Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 06 Jul 2026 10:01:36 +0000

ORA-01118: cannot add any more database files: limit exceeded

ORA-01118 is thrown when Oracle cannot add any more datafiles to the database because the configured limit has been reached. This limit is controlled by either the DB_FILES initialization parameter or the MAXDATAFILES setting stored in the control file, whichever is lower. You'll typically encounter this error when executing ALTER TABLESPACE ... ADD DATAFILE or CREATE TABLESPACE statements.

Top 3 Causes

1. DB_FILES Parameter Limit Reached

The DB_FILES parameter defines the maximum number of datafiles the instance can have open simultaneously. When the number of datafiles in V$DATAFILE equals this value, Oracle refuses to add more.

-- Check current datafile count vs. DB_FILES limit
SELECT COUNT(*) AS current_files,
       (SELECT TO_NUMBER(VALUE)
        FROM V$PARAMETER
        WHERE NAME = 'db_files') AS db_files_limit
FROM V$DATAFILE;

2. MAXDATAFILES in Control File Exceeded

The control file has its own hard limit set by MAXDATAFILES at database creation time. Even if DB_FILES is large enough, this control file-level limit can block new datafile additions independently.

-- Check MAXDATAFILES limit recorded in the control file
SELECT CF_MAX_FILES AS maxdatafiles_controlfile
FROM V$DATABASE;

-- Review all current datafiles and their status
SELECT FILE#, STATUS, NAME, BYTES/1024/1024 AS SIZE_MB
FROM V$DATAFILE
ORDER BY FILE#;

3. Accumulated Unused Datafiles

Over time, test tablespaces, migration leftovers, and offline datafiles consume slots without providing business value. These stale entries still count against the limit even if the tablespace is no longer active.

-- Identify offline or unused datafiles
SELECT d.FILE#, d.STATUS, d.NAME, t.TABLESPACE_NAME, t.STATUS AS TS_STATUS
FROM V$DATAFILE d
JOIN DBA_DATA_FILES f ON d.NAME = f.FILE_NAME
JOIN DBA_TABLESPACES t ON f.TABLESPACE_NAME = t.TABLESPACE_NAME
WHERE d.STATUS = 'OFFLINE'
   OR t.STATUS = 'OFFLINE'
ORDER BY d.FILE#;

Quick Fix Solutions

Increase DB_FILES (Requires Restart)

-- Increase DB_FILES to 500 using SPFILE
ALTER SYSTEM SET DB_FILES = 500 SCOPE = SPFILE;

-- Restart the database to apply
SHUTDOWN IMMEDIATE;
STARTUP;

-- Verify
SHOW PARAMETER DB_FILES;

Recreate the Control File (MAXDATAFILES Fix)

-- Generate a controlfile recreation script
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
  AS '/tmp/cf_recreate.sql' REUSE NORESETLOGS;

-- Edit the script: set MAXDATAFILES to a larger value, then run:
STARTUP NOMOUNT;

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024    -- increase this value
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
    GROUP 1 '/u01/oradata/ORCL/redo01.log' SIZE 200M BLOCKSIZE 512,
    GROUP 2 '/u01/oradata/ORCL/redo02.log' SIZE 200M BLOCKSIZE 512
DATAFILE
    '/u01/oradata/ORCL/system01.dbf',
    '/u01/oradata/ORCL/sysaux01.dbf',
    '/u01/oradata/ORCL/undotbs01.dbf',
    '/u01/oradata/ORCL/users01.dbf'
CHARACTER SET AL32UTF8;

Drop Unused Tablespaces

-- Drop an unused tablespace and its physical files
DROP TABLESPACE old_test_ts INCLUDING CONTENTS AND DATAFILES;

Enable AUTOEXTEND to Reduce Need for New Files

-- Enable autoextend on an existing datafile
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf'
AUTOEXTEND ON NEXT 512M MAXSIZE 32767M;

Prevention Tips

1. Monitor usage proactively. Set up an alert when datafile count exceeds 80% of DB_FILES.

-- Usage monitoring query
SELECT CURRENT_COUNT,
       DB_FILES_LIMIT,
       ROUND(CURRENT_COUNT / DB_FILES_LIMIT * 100, 2) AS PCT_USED
FROM (
    SELECT COUNT(*) AS CURRENT_COUNT,
           TO_NUMBER((SELECT VALUE FROM V$PARAMETER WHERE NAME='db_files')) AS DB_FILES_LIMIT
    FROM V$DATAFILE
);

2. Use BigFile Tablespaces where possible. A BigFile tablespace uses exactly one datafile (up to 128TB), drastically reducing the total datafile count and the risk of hitting this limit.

-- Create a BigFile tablespace (one file = one tablespace)
CREATE BIGFILE TABLESPACE app_data_ts
DATAFILE '/u01/oradata/ORCL/app_data01.dbf'
SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

Related Errors

ORA-00059 – Maximum number of DB_FILES exceeded; closely related to ORA-01118.
ORA-01119 – Error creating a database file; often appears alongside ORA-01118.
ORA-01565 – Error identifying a file; can accompany datafile addition failures.

PostgreSQL 42622 Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 06 Jul 2026 06:02:54 +0000

PostgreSQL Error 42622: Name Too Long

PostgreSQL error code 42622 occurs when an identifier—such as a table name, column name, index name, or constraint name—exceeds PostgreSQL's maximum allowed length of 63 bytes. This limit is a compile-time constant (NAMEDATALEN - 1) and cannot be changed without recompiling PostgreSQL from source. It commonly appears in applications using ORMs that auto-generate long identifier names or in schemas with verbose naming conventions.

Top 3 Causes

1. Object Names Exceeding 63 Bytes

The most direct cause is simply naming a database object with more than 63 bytes. Note that multibyte characters (e.g., UTF-8 encoded text) consume more than 1 byte per character.

-- This will trigger error 42622 or silent truncation
CREATE TABLE user_account_personal_information_detail_extended_history (
    id SERIAL PRIMARY KEY
);
-- identifier "user_account_personal_information_detail_extended_history"
-- will be truncated to 63 bytes

-- Check byte length before creating
SELECT
    octet_length('user_account_personal_information_detail_extended_history')
        AS byte_length;
-- Returns 57 in this case — but longer names WILL fail

2. Auto-Generated Index or Constraint Names

When you don't explicitly name indexes or foreign key constraints, PostgreSQL auto-generates names by combining table and column names. This combination can easily exceed 63 bytes.

-- Auto-generated name would be:
-- order_management_system_transactions_customer_account_reference_number_idx
-- which is clearly over 63 bytes

CREATE TABLE order_management_system_transactions (
    id SERIAL PRIMARY KEY,
    customer_account_reference_number VARCHAR(50)
);

-- This auto-generated index name will exceed the limit:
CREATE INDEX ON order_management_system_transactions
    (customer_account_reference_number);

-- Fix: Always specify an explicit, shorter name
CREATE INDEX idx_oms_txn_cust_ref
    ON order_management_system_transactions (customer_account_reference_number);

3. ORM or Migration Tools Auto-Generating Long Names

ORMs like Django, SQLAlchemy, or Hibernate generate identifier names based on model and field names. Long class or field names can result in auto-generated SQL identifiers that exceed PostgreSQL's limit.

-- What Django might auto-generate (problematic):
-- CREATE INDEX "myapp_useraccountpersonalinfo_user_profile_id_a1b2c3d4_idx"
-- ON "myapp_useraccountpersonalinfo" ("user_profile_id");

-- Explicitly override in migration to produce this instead:
CREATE INDEX idx_user_acct_info_profile_id
    ON myapp_useraccountpersonalinfo (user_profile_id);

-- Verify all current index name lengths in your schema
SELECT
    indexname,
    tablename,
    octet_length(indexname) AS byte_len
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY byte_len DESC;

Quick Fix Solutions

-- 1. Rename an existing object with a too-long name
ALTER TABLE old_extremely_long_table_name_that_is_problematic
    RENAME TO short_table_name;

-- 2. Replace a long-named index
DROP INDEX IF EXISTS long_auto_generated_index_name_that_exceeds_the_limit;
CREATE INDEX idx_short_meaningful_name ON target_table (target_column);

-- 3. Rename a constraint
ALTER TABLE my_table
    RENAME CONSTRAINT long_auto_generated_constraint_name TO fk_short_name;

-- 4. Check byte length of any string before using as identifier
SELECT octet_length('your_proposed_identifier_name') AS byte_length;
-- Must be <= 63

Prevention Tips

1. Enforce a naming convention with byte-length limits.
Establish team standards—for example, table names ≤ 30 chars, column names ≤ 25 chars, and index/constraint names ≤ 40 chars. Add an automated check in your CI/CD pipeline:

-- Run this as part of your schema validation
SELECT object_name, byte_length
FROM (
    SELECT table_name AS object_name,
           octet_length(table_name) AS byte_length
    FROM information_schema.tables
    WHERE table_schema = 'public'
) t
WHERE byte_length > 50
ORDER BY byte_length DESC;

2. Always explicitly name indexes and constraints.
Never rely on auto-generated names for indexes, foreign keys, or unique constraints. Explicitly providing short, meaningful names prevents both the 42622 error and hard-to-debug truncation issues down the line.

-- Good practice: always name your constraints and indexes explicitly
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id);

CREATE UNIQUE INDEX uidx_orders_ref_num
    ON orders (reference_number);

Summary

PostgreSQL's 63-byte identifier limit is a hard boundary that applies to all object names. The key takeaways are: always verify identifier byte lengths before deployment, explicitly name all constraints and indexes, and enforce a team-wide naming convention to avoid surprises in production.

Oracle ORA-01116 Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 06 Jul 2026 06:01:18 +0000

ORA-01116: Error in Opening Database File — Causes, Fixes & Prevention

ORA-01116 occurs when Oracle is unable to open a database datafile during startup, recovery, or normal operation. This error typically surfaces alongside ORA-01110 (which identifies the specific file) and ORA-27041 (an OS-level open failure). Immediate action is required, as this error can cause partial or complete database unavailability.

Top 3 Causes

1. Missing or Relocated Datafile

The most common cause — the physical file has been deleted, moved, or is inaccessible at the path recorded in the control file.

-- Check datafile status and paths
SELECT file#, status, name
FROM v$datafile
WHERE status != 'ONLINE';

-- If file was moved, rename in control file (run in MOUNT mode)
STARTUP MOUNT;

ALTER DATABASE RENAME FILE
  '/old/path/datafile01.dbf'
TO '/new/path/datafile01.dbf';

ALTER DATABASE OPEN;

2. OS-Level Permission or Mount Point Failure

Oracle processes lack read/write permission on the file, or an NFS mount point / ASM disk group has become unavailable.

-- Identify files needing recovery
SELECT d.file#, d.name, d.status, t.name AS tablespace_name
FROM v$datafile d
JOIN v$tablespace t ON d.ts# = t.ts#
WHERE d.status IN ('RECOVER', 'OFFLINE');

-- After fixing OS permissions, bring file online
ALTER DATABASE DATAFILE '/path/to/datafile01.dbf' ONLINE;

# Fix OS permissions (Linux/Unix)
# chown oracle:dba /path/to/datafile01.dbf
# chmod 640 /path/to/datafile01.dbf

3. Datafile Header Corruption

Hardware failure or abnormal shutdown can corrupt the file header, making it unreadable even though the file physically exists.

-- Offline the corrupted file and open DB
ALTER DATABASE DATAFILE '/path/to/datafile01.dbf' OFFLINE;
ALTER DATABASE OPEN;

-- Restore and recover using RMAN
RMAN> RESTORE DATAFILE 5;
RMAN> RECOVER DATAFILE 5;

-- Bring the file back online
ALTER DATABASE DATAFILE 5 ONLINE;

-- Verify final status
SELECT file#, name, status FROM v$datafile;

Quick Fix Summary

-- Step 1: Mount the database if it won't open
STARTUP MOUNT;

-- Step 2: Identify the problem file
SELECT file#, name, status FROM v$datafile;

-- Step 3a: Rename if file was moved
ALTER DATABASE RENAME FILE '/old/path/file.dbf' TO '/new/path/file.dbf';
ALTER DATABASE OPEN;

-- Step 3b: Full DB restore if file is lost
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

Prevention Tips

1. Schedule regular RMAN backups and validate them:

-- Validate all datafiles for physical corruption
RMAN> BACKUP VALIDATE DATABASE;

-- List available backups
RMAN> LIST BACKUP OF DATABASE SUMMARY;

2. Automate datafile health monitoring:

-- Run this query regularly via a scheduled job or OEM alert
SELECT file#, name, status,
       bytes/1024/1024 AS size_mb
FROM v$datafile
WHERE status NOT IN ('ONLINE', 'SYSTEM')
ORDER BY file#;

Related Errors

Error Code	Description
ORA-01110	Identifies the specific file number and name involved
ORA-27041	OS-level failure to open the file
ORA-01157	Cannot identify/lock datafile — similar cause
ORA-00376	File cannot be read in its current state
ORA-01578	Physical block corruption within the datafile

PostgreSQL 42602 Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 06 Jul 2026 00:02:58 +0000

PostgreSQL Error 42602: Invalid Name — Causes, Fixes, and Prevention

PostgreSQL error 42602 (invalid_name) is raised when an identifier — such as a table name, column name, index, or function — violates PostgreSQL's naming rules. This typically happens during DDL operations, dynamic SQL execution, or when identifiers are constructed programmatically from external input. Understanding the root cause quickly is essential, as this error can block deployments and migrations if left unresolved.

Top 3 Causes

1. Identifiers Starting with a Digit or Containing Illegal Characters

PostgreSQL identifiers must start with a letter (a-z, A-Z) or an underscore (_). Everything after can include letters, digits, underscores, or dollar signs. Names starting with numbers or containing hyphens, spaces, or special characters will trigger 42602 without double-quoting.

-- Causes 42602 error
CREATE TABLE reports (
    1st_quarter INT,     -- starts with digit
    total-amount NUMERIC -- contains hyphen
);

-- Fix: rename to valid identifiers
CREATE TABLE reports (
    first_quarter INT,
    total_amount NUMERIC
);

-- If you must keep the original name, use double quotes
CREATE TABLE reports (
    "1st_quarter" INT,
    "total-amount" NUMERIC
);

-- Rename existing problematic columns
ALTER TABLE reports RENAME COLUMN "1st_quarter" TO first_quarter;

2. Unsanitized User Input in Dynamic SQL

When building dynamic SQL using EXECUTE or string concatenation, passing unvalidated strings as identifiers is a common source of 42602. If the input contains spaces, special characters, or is empty, PostgreSQL cannot parse it as a valid identifier.

-- Dangerous and error-prone approach
DO $$
DECLARE
    tbl TEXT := 'some-bad name!';
BEGIN
    EXECUTE 'SELECT * FROM ' || tbl; -- 42602 triggered
END;
$$;

-- Safe approach using format() with %I specifier (recommended)
DO $$
DECLARE
    tbl TEXT := 'employees';
    col TEXT := 'department';
BEGIN
    EXECUTE format('SELECT %I FROM %I WHERE %I IS NOT NULL', col, tbl, col);
END;
$$;

-- Also valid: quote_ident()
DO $$
DECLARE
    tbl TEXT := 'employees';
BEGIN
    EXECUTE 'SELECT * FROM ' || quote_ident(tbl);
END;
$$;

3. Empty String or NULL Passed as an Identifier

When a variable intended to hold a table or column name is NULL or an empty string '', PostgreSQL cannot construct a valid identifier and raises 42602. This often happens when application-side logic fails to initialize variables before passing them to database functions.

-- Triggers 42602: empty or null identifier
DO $$
DECLARE
    col_name TEXT := '';  -- or NULL
BEGIN
    EXECUTE format('ALTER TABLE my_table ADD COLUMN %I INT', col_name);
END;
$$;

-- Fix: add a guard before executing dynamic SQL
CREATE OR REPLACE FUNCTION safe_add_column(p_table TEXT, p_col TEXT)
RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
    IF p_table IS NULL OR p_table = '' THEN
        RAISE EXCEPTION 'Table name cannot be NULL or empty.';
    END IF;

    IF p_col IS NULL OR p_col = '' THEN
        RAISE EXCEPTION 'Column name cannot be NULL or empty.';
    END IF;

    IF p_col !~ '^[a-zA-Z_][a-zA-Z0-9_$]*$' THEN
        RAISE EXCEPTION 'Invalid identifier: %', p_col;
    END IF;

    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS %I INT', p_table, p_col);
END;
$$;

Quick Fix Summary

Cause	Fix
Illegal characters in name	Rename using snake_case or wrap in double quotes
Dynamic SQL with raw strings	Use `format()` with `%I` or `quote_ident()`
NULL or empty identifier	Add NULL/empty checks before executing dynamic SQL

Prevention Tips

1. Enforce a snake_case naming convention from day one.
All identifiers — tables, columns, indexes, functions — should use lowercase letters, digits, and underscores only. Enforce this through code reviews, migration linters, and CI/CD pipeline checks. Avoiding double-quoted identifiers entirely eliminates an entire class of 42602 errors.

2. Always use format() with %I for dynamic SQL identifiers.
Make it a non-negotiable team standard: never concatenate raw strings to build identifier names in PL/pgSQL. The %I specifier in format() automatically quotes and escapes the identifier correctly, preventing both 42602 errors and SQL injection vulnerabilities at the same time.

Related Errors

42601 (syntax_error) — Often appears alongside 42602 during SQL parsing failures.
42939 (reserved_name) — Raised when a PostgreSQL reserved keyword is used as an identifier without quoting.
42P01 (undefined_table) — Can follow 42602 in dynamic SQL scenarios where a malformed name leads to a missing object reference.

Oracle ORA-01115 Error: Causes and Solutions Complete Guide

umzzil nng — Mon, 06 Jul 2026 00:01:28 +0000

ORA-01115: IO Error Reading Block from File — Causes, Fixes & Prevention

ORA-01115 is a critical Oracle error that occurs when the database engine fails to read a specific block from a datafile due to an I/O error at the OS or storage layer. This error often signals physical storage failures, file system corruption, or block-level data corruption within the datafile itself. Immediate investigation is essential, as this error can indicate potential data loss.

Top 3 Causes

1. Physical Storage or Disk Failure (Bad Sectors / SAN Issues)

The most common root cause is a failing disk, bad sector, or SAN/NAS storage malfunction. When Oracle attempts to read a block and the storage subsystem cannot fulfill the I/O request, ORA-01115 is raised.

-- Check datafile status immediately
SELECT FILE#, NAME, STATUS, BYTES/1024/1024 AS SIZE_MB
FROM V$DATAFILE
ORDER BY FILE#;

-- Check datafile header status
SELECT FILE#, STATUS, FUZZY, CHECKPOINT_CHANGE#
FROM V$DATAFILE_HEADER;

Always check OS-level logs (/var/log/messages on Linux, Event Viewer on Windows) alongside Oracle's alert log to correlate storage errors.

2. Block Corruption (Logical or Physical)

A block may be physically read but contain an invalid checksum or corrupted header, resulting in a logical block corruption. This often appears alongside ORA-01578.

-- Validate database blocks using RMAN
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

-- Check for corrupted blocks after validation
SELECT FILE#, BLOCK#, BLOCKS, CORRUPTION_TYPE
FROM V$DATABASE_BLOCK_CORRUPTION;

-- Recover specific corrupted blocks
RMAN> RECOVER DATAFILE 5 BLOCK 100;

-- Recover all listed corrupted blocks at once
RMAN> RECOVER CORRUPTION LIST;

3. Missing or Inaccessible Datafile (Permissions / Accidental Deletion)

If a datafile has been accidentally deleted, moved, or had its OS-level permissions changed, Oracle cannot read from it, triggering ORA-01115.

-- Identify the affected file path
SELECT FILE#, NAME, STATUS
FROM V$DATAFILE
WHERE STATUS != 'ONLINE';

-- Attempt to bring an offline file back online
ALTER DATABASE DATAFILE '/oracle/oradata/prod/users01.dbf' ONLINE;

-- If the file was moved, rename it in the control file
ALTER DATABASE RENAME FILE '/old_path/users01.dbf'
  TO '/new_path/users01.dbf';

Quick Fix Solutions

Step 1 — Identify the exact file and block from the alert log:

-- Find alert log and trace file locations
SELECT NAME, VALUE FROM V$DIAG_INFO
WHERE NAME IN ('Diag Trace', 'Diag Alert');

Step 2 — Use DBMS_REPAIR if RMAN recovery is not immediately possible:

-- Create repair table
BEGIN
  DBMS_REPAIR.ADMIN_TABLES(
    TABLE_NAME => 'REPAIR_TABLE',
    TABLE_TYPE => DBMS_REPAIR.REPAIR_TABLE,
    ACTION     => DBMS_REPAIR.CREATE_ACTION,
    TABLESPACE => 'SYSAUX'
  );
END;
/

-- Soft-mark corrupt blocks to allow table access (skip corrupt blocks)
BEGIN
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS(
    SCHEMA_NAME       => 'SCOTT',
    OBJECT_NAME       => 'EMP',
    REPAIR_TABLE_NAME => 'REPAIR_TABLE'
  );
END;
/

Step 3 — Restore from RMAN backup if corruption is unrecoverable:

RMAN> RESTORE DATAFILE 5;
RMAN> RECOVER DATAFILE 5;

Prevention Tips

Enable Block Checksumming:

Configure Oracle to automatically validate block integrity on every write, catching corruption early before it causes read failures.

-- Enable block checksum (recommended: TYPICAL or FULL)
ALTER SYSTEM SET DB_BLOCK_CHECKSUM = TYPICAL SCOPE=BOTH;

-- Enable block checking for additional write-time validation
ALTER SYSTEM SET DB_BLOCK_CHECKING = MEDIUM SCOPE=BOTH;

Schedule Regular RMAN Validation:

Run BACKUP VALIDATE at least once a week to proactively detect block corruption before it causes production outages. Integrate this into your standard DBA maintenance scripts.

-- Weekly validation job (run via RMAN script)
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;
RMAN> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

Related Errors

ORA-01578 — Identifies the specific corrupted block (file # and block #); almost always appears alongside ORA-01115.
ORA-01110 — Reports the datafile name and number associated with the error.
ORA-27072 / ORA-27091 — OS-level I/O failure errors that appear below ORA-01115 in the error stack, providing additional OS-level diagnostics.
ORA-00376 — Raised when Oracle cannot read a datafile because it is offline or requires recovery.

Oracle ORA-01114 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 05 Jul 2026 14:01:27 +0000

ORA-01114: IO Error Writing Block to File — Causes, Fixes & Prevention

ORA-01114 is a serious Oracle database error that occurs when the database engine fails to write a data block to a datafile due to an I/O failure at the operating system or storage level. This error is often accompanied by ORA-27072, which provides the underlying OS-level error code for more precise diagnosis. Because this error directly threatens data integrity, it requires immediate investigation and action.

Top 3 Causes & SQL Examples

Cause 1: Disk Space Exhausted

The most common cause is the filesystem running out of space, preventing Oracle from extending or writing to a datafile.

-- Check tablespace usage to identify full tablespaces
SELECT
    df.tablespace_name,
    ROUND(df.total_mb, 2)                          AS total_mb,
    ROUND(df.total_mb - NVL(fs.free_mb, 0), 2)    AS used_mb,
    ROUND(NVL(fs.free_mb, 0), 2)                   AS free_mb,
    ROUND((df.total_mb - NVL(fs.free_mb,0))
          / df.total_mb * 100, 1)                   AS used_pct
FROM
    (SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
     FROM dba_data_files GROUP BY tablespace_name) df
LEFT JOIN
    (SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
     FROM dba_free_space GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
ORDER BY used_pct DESC;

-- Fix: Add a new datafile to the tablespace
ALTER TABLESPACE USERS
ADD DATAFILE '/oradata/ORCL/users02.dbf'
SIZE 5G AUTOEXTEND ON NEXT 512M MAXSIZE 20G;

-- Fix: Resize an existing datafile
ALTER DATABASE DATAFILE '/oradata/ORCL/users01.dbf' RESIZE 10G;

Cause 2: Hardware / Storage Failure

Bad disk sectors, HBA failures, or broken SAN/NAS connectivity can cause I/O errors at the block level. Always check OS-level logs (dmesg, /var/log/messages) alongside Oracle's alert log.

-- Identify the affected file using the file# from the error message
SELECT file#, name, status, bytes/1024/1024 AS size_mb
FROM v$datafile
WHERE file# = <file_number_from_error>;

-- Check for physically corrupted blocks
SELECT * FROM v$database_block_corruption;

-- Validate the datafile integrity via RMAN
-- Run in an RMAN session:
-- RMAN> VALIDATE DATAFILE '/oradata/ORCL/users01.dbf';
-- RMAN> RECOVER DATAFILE '/oradata/ORCL/users01.dbf';

-- If restore is needed:
-- RMAN> RESTORE DATAFILE '/oradata/ORCL/users01.dbf';
-- RMAN> RECOVER DATAFILE '/oradata/ORCL/users01.dbf';

Cause 3: Datafile Offline or Permission Issue

A datafile taken offline manually or a permissions change on the OS level will also trigger ORA-01114.

-- Find offline datafiles
SELECT file#, name, status
FROM v$datafile
WHERE status != 'ONLINE';

-- Bring a datafile back online
ALTER DATABASE DATAFILE '/oradata/ORCL/users01.dbf' ONLINE;

-- Or bring the entire tablespace online
ALTER TABLESPACE USERS ONLINE;

-- Confirm the fix
SELECT file#, name, status FROM v$datafile ORDER BY file#;

Quick Fix Checklist

Check Oracle alert log for the exact file# and accompanying ORA-27072 message.
Verify filesystem free space with df -h on Linux/Unix.
Confirm datafile status using v$datafile.
Check OS logs for hardware errors.
Use RMAN to validate and recover corrupted datafiles.

Prevention Tips

Automate capacity monitoring: Schedule a script or use Oracle Enterprise Manager (OEM) to alert when any tablespace exceeds 80% usage. Enable AUTOEXTEND with a reasonable MAXSIZE as a safety net.

-- Enable autoextend on existing datafiles
ALTER DATABASE DATAFILE '/oradata/ORCL/users01.dbf'
AUTOEXTEND ON NEXT 256M MAXSIZE 30G;

Validate RMAN backups regularly: A verified backup is your only recovery option when datafile corruption occurs. Run BACKUP VALIDATE CHECK LOGICAL DATABASE; at least once a month to proactively detect block corruption before it causes an outage.

Related Errors

Error Code	Description
ORA-01113	Datafile needs media recovery
ORA-01115	IO error reading block from file
ORA-01116	Error opening datafile
ORA-27072	OS-level file I/O error detail
ORA-19502	Write error on file during RMAN backup

PostgreSQL 42P19 Error: Causes and Solutions Complete Guide

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

PostgreSQL Error 42P19: invalid recursion

PostgreSQL error code 42P19 (invalid_recursion) occurs when a recursive CTE (WITH RECURSIVE) violates the structural rules enforced by the PostgreSQL query planner. This error is caught at parse/plan time, meaning your query won't execute at all until the recursive structure is corrected. Understanding the strict rules around recursive CTEs is essential for any developer working with hierarchical or graph-style data in PostgreSQL.

Top 3 Causes

1. Using Aggregate Functions Inside the Recursive Term

PostgreSQL strictly forbids the use of GROUP BY, DISTINCT, HAVING, or aggregate functions (COUNT, SUM, etc.) within the recursive part of a WITH RECURSIVE query.

Broken:

-- ERROR 42P19: aggregate in recursive term
WITH RECURSIVE dept_tree AS (
    SELECT department_id, manager_id, 1 AS depth
    FROM departments
    WHERE manager_id IS NULL

    UNION ALL

    SELECT d.department_id, d.manager_id, COUNT(*) -- NOT allowed
    FROM departments d
    JOIN dept_tree dt ON d.manager_id = dt.department_id
    GROUP BY d.department_id, d.manager_id
)
SELECT * FROM dept_tree;

Fixed:

-- Move aggregation outside the recursive CTE
WITH RECURSIVE dept_tree AS (
    SELECT department_id, manager_id, 1 AS depth
    FROM departments
    WHERE manager_id IS NULL

    UNION ALL

    SELECT d.department_id, d.manager_id, dt.depth + 1
    FROM departments d
    JOIN dept_tree dt ON d.manager_id = dt.department_id
)
SELECT manager_id, COUNT(*) AS reports, MAX(depth) AS max_depth
FROM dept_tree
GROUP BY manager_id;

2. Nesting the Recursive Reference Inside a Subquery

The recursive CTE name must be referenced directly in the FROM clause of the recursive term. Placing it inside a subquery (IN, EXISTS, scalar subquery) triggers 42P19.

Broken:

-- ERROR 42P19: recursive reference inside subquery
WITH RECURSIVE emp_tree AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    WHERE e.manager_id IN (
        SELECT employee_id FROM emp_tree  -- NOT allowed here
    )
)
SELECT * FROM emp_tree;

Fixed:

-- Use a direct JOIN instead
WITH RECURSIVE emp_tree AS (
    SELECT employee_id, manager_id, name, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.name, et.level + 1
    FROM employees e
    JOIN emp_tree et ON e.manager_id = et.employee_id
)
SELECT employee_id, name, level
FROM emp_tree
ORDER BY level, name;

3. Missing Base Case or Wrong UNION Structure

A valid WITH RECURSIVE query must have a non-recursive base case and a recursive term joined by UNION or UNION ALL. Placing the recursive reference on the left side of UNION or omitting the base case entirely will trigger this error.

Broken:

-- ERROR 42P19: no base case, recursive reference on wrong side
WITH RECURSIVE category_path AS (
    SELECT cp.category_id, cp.name
    FROM category_path cp          -- recursive ref on LEFT side
    JOIN categories c ON c.parent_id = cp.category_id
)
SELECT * FROM category_path;

Fixed — with cycle detection for production safety:

WITH RECURSIVE category_path AS (
    -- Base case: start from root nodes
    SELECT category_id, parent_id, name,
           ARRAY[category_id] AS visited,
           0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive term: recursive ref on the RIGHT side of UNION ALL
    SELECT c.category_id, c.parent_id, c.name,
           cp.visited || c.category_id,
           cp.depth + 1
    FROM categories c
    JOIN category_path cp ON c.parent_id = cp.category_id
    WHERE NOT c.category_id = ANY(cp.visited)  -- cycle guard
      AND cp.depth < 50                         -- depth limit
)
SELECT category_id, name, depth
FROM category_path
ORDER BY depth, name;

Quick Fix Checklist

Symptom	Fix
Aggregate/GROUP BY in recursive term	Move aggregation to outer query
Recursive name inside subquery	Replace with direct `JOIN`
No base case	Add a non-recursive starting `SELECT`
Recursive ref on left of UNION	Swap — base case left, recursive right

Prevention Tips

1. Always follow the three-part recursive CTE template:
Every WITH RECURSIVE query should follow: ① non-recursive base case → ② UNION ALL → ③ recursive term with a direct JOIN to the CTE name. Keep aggregations, window functions, and subquery references out of the recursive term entirely.

2. Add depth limits and cycle detection on every recursive query in production:

-- Production-safe recursive query template
WITH RECURSIVE safe_tree AS (
    SELECT id, parent_id, name,
           ARRAY[id] AS path,
           0 AS depth
    FROM your_table
    WHERE parent_id IS NULL

    UNION ALL

    SELECT t.id, t.parent_id, t.name,
           st.path || t.id,
           st.depth + 1
    FROM your_table t
    JOIN safe_tree st ON t.parent_id = st.id
    WHERE NOT t.id = ANY(st.path)   -- prevents infinite loops
      AND st.depth < 100             -- hard depth cap
)
SELECT * FROM safe_tree;

Pair this with a statement_timeout setting at the session or role level to catch runaway queries before they impact production workloads.

Related Errors

42601 syntax_error — Malformed WITH RECURSIVE syntax before the planner even checks recursion rules.
42P20 windowing_error — Misuse of window functions, sometimes co-occurring when mixing window functions with CTEs.
54001 statement_too_complex — Can appear when deeply nested recursive CTEs exhaust planner resources.

Oracle ORA-01113 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 05 Jul 2026 10:01:33 +0000

ORA-01113: File Needs Media Recovery — What It Means and How to Fix It

ORA-01113 is one of the most critical Oracle errors a DBA can encounter. It occurs when Oracle detects that a data file requires media recovery before it can be brought online or before the database can be opened. This typically happens because the file's SCN (System Change Number) is behind what the control file expects, meaning the file is out of sync with the rest of the database.

Top 3 Causes

1. Restoring a Backup Without Performing Recovery

When you restore a data file or database from a backup (via RMAN or OS copy), the restored file carries the SCN from the time of the backup. Without applying archived redo logs to bring the file current, Oracle refuses to open the database and throws ORA-01113.

-- Check which files need recovery
SELECT file#, name, status, checkpoint_change#
FROM v$datafile
WHERE status IN ('RECOVER', 'OFFLINE');

-- Check SCN mismatch in file headers
SELECT file#, error, recover, checkpoint_change#
FROM v$datafile_header
ORDER BY file#;

2. Abnormal Database Shutdown (Crash)

A power failure, OS crash, or SHUTDOWN ABORT can leave data files in an inconsistent state — particularly files that were offline at the time of the crash. Oracle detects the missing checkpoint and raises ORA-01113 when the database is restarted.

-- Start database in MOUNT mode to diagnose
STARTUP MOUNT;

-- Check file status
SELECT file#, name, status
FROM v$datafile
WHERE status != 'ONLINE';

3. Bringing an Offline Tablespace Online Without Recovery

If a tablespace or data file was taken offline and archived logs were generated during the offline period, Oracle requires those logs to be applied before the file can come back online.

-- Incorrect approach (will trigger ORA-01113)
ALTER TABLESPACE users ONLINE; -- Fails without recovery

-- Correct approach
RECOVER TABLESPACE users;
ALTER TABLESPACE users ONLINE;

Quick Fix Solutions

Fix 1: Using RMAN (Recommended)

-- Connect to RMAN
RMAN> CONNECT TARGET /

-- Recover a specific datafile
RMAN> RECOVER DATAFILE 5;

-- Or recover an entire tablespace
RMAN> RECOVER TABLESPACE users;

-- Open the database after recovery
RMAN> ALTER DATABASE OPEN;

Fix 2: Manual Recovery via SQL*Plus

-- Start in MOUNT mode
STARTUP MOUNT;

-- Recover specific datafile
RECOVER DATAFILE 5;

-- Or use full path
RECOVER DATAFILE '/u01/oradata/ORCL/users01.dbf';

-- Auto-apply archive logs
RECOVER AUTOMATIC DATAFILE 5;

-- Open the database
ALTER DATABASE OPEN;

Fix 3: Incomplete Recovery (When Archive Logs Are Missing)

-- Recover up to a specific SCN
RECOVER DATABASE UNTIL CHANGE 9876543;

-- Or recover to a point in time
RECOVER DATABASE UNTIL TIME '2024-06-01:12:00:00';

-- Open with RESETLOGS after incomplete recovery
ALTER DATABASE OPEN RESETLOGS;

Prevention Tips

1. Always Run in ARCHIVELOG Mode with Regular RMAN Backups

Enable ARCHIVELOG mode and schedule regular RMAN full and incremental backups. Validate your backups periodically to ensure they are usable in a real recovery scenario.

-- Confirm ARCHIVELOG mode
SELECT log_mode FROM v$database;

-- Validate backup in RMAN
-- RMAN> VALIDATE DATABASE;

2. Follow Safe Offline/Online Procedures

Always use OFFLINE NORMAL when taking a tablespace offline, and never skip the recovery step before bringing it back online. Document and enforce this as a standard DBA operating procedure.

-- Safe offline procedure
ALTER TABLESPACE users OFFLINE NORMAL;

-- Proper online procedure after maintenance
RECOVER TABLESPACE users;
ALTER TABLESPACE users ONLINE;

Related Errors

Error Code	Description
ORA-01110	Identifies the specific file name associated with ORA-01113
ORA-01157	Cannot identify/lock data file — often appears alongside ORA-01113
ORA-00283	Recovery session cancelled — occurs when archive logs are missing during recovery
ORA-00279	Change needed for thread — guides you to the required archive log sequence

PostgreSQL 42P20 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 05 Jul 2026 06:02:51 +0000

PostgreSQL Error 42P20: Windowing Error — Causes and Fixes

PostgreSQL error code 42P20 is a windowing error that occurs when window functions are used incorrectly in a SQL query. Window functions rely on the OVER() clause and include functions like ROW_NUMBER(), RANK(), LAG(), LEAD(), and aggregate functions used in a windowed context. This error typically surfaces due to illegal nesting, invalid frame specifications, or misplaced window function usage within query clauses.

Top 3 Causes

1. Nesting Window Functions Inside Each Other

PostgreSQL does not allow a window function to be directly nested inside another window function. Attempting to do so triggers 42P20 immediately at parse time.

-- ❌ WRONG: Nested window functions cause 42P20
SELECT
    employee_id,
    SUM(ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC))
        OVER (PARTITION BY dept_id) AS bad_query
FROM employees;

-- ✅ CORRECT: Use a subquery or CTE to separate the steps
WITH ranked AS (
    SELECT
        employee_id,
        dept_id,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT
    dept_id,
    SUM(rn) OVER (PARTITION BY dept_id) AS sum_of_ranks
FROM ranked;

2. Invalid ROWS/RANGE Frame Specification

When defining a window frame using ROWS BETWEEN or RANGE BETWEEN, the start boundary must always come before the end boundary. Reversing this order (e.g., CURRENT ROW AND UNBOUNDED PRECEDING) causes PostgreSQL to raise 42P20.

-- ❌ WRONG: Frame boundaries are reversed
SELECT
    order_id,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING
    ) AS bad_frame
FROM orders;

-- ✅ CORRECT: Start boundary precedes end boundary
SELECT
    order_id,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

3. Using Window Functions in WHERE or GROUP BY Clauses

Window functions are evaluated during the SELECT phase of query execution — after WHERE, GROUP BY, and HAVING. Using a window function directly inside a WHERE or GROUP BY clause is illegal and triggers 42P20.

-- ❌ WRONG: Window function in WHERE clause
SELECT employee_id, salary
FROM employees
WHERE ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) = 1;

-- ✅ CORRECT: Wrap in a subquery and filter in the outer query
SELECT employee_id, salary
FROM (
    SELECT
        employee_id,
        dept_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM employees
) sub
WHERE rn = 1;

Quick Fix Summary

Situation	Fix
Nested window functions	Use CTE or subquery to separate evaluation steps
Invalid frame boundaries	Ensure start frame < end frame (`PRECEDING → FOLLOWING`)
Window function in WHERE	Wrap query in a subquery; filter in outer `WHERE`

Prevention Tips

1. Always write window functions in CTEs for complex queries.
Breaking your query into named steps using WITH clauses makes it far easier to spot logical errors and avoids illegal nesting or misplaced function usage. Treat each CTE as one logical transformation step.

2. Always explicitly specify your frame clause.
When omitted, PostgreSQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Being explicit prevents accidental misuse and makes your intent clear to both the database engine and your teammates reviewing the code.

-- Best practice: always be explicit about window frames
SELECT
    order_id,
    order_date,
    amount,
    AVG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- explicit 7-row moving average
    ) AS moving_avg_7
FROM orders;

Related Errors

42601 — syntax_error: Malformed OVER() clause syntax before PostgreSQL can evaluate window semantics.
42883 — undefined_function: Calling a window function name that does not exist.
42803 — grouping_error: Often appears alongside 42P20 when mixing aggregate and window functions improperly with GROUP BY.

DEV Community: umzzil nng

PostgreSQL 42804 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42804: datatype mismatch

Top 3 Causes

1. CASE Expression or UNION Type Mismatch

2. Function Return Type Mismatch

3. Array Constructor or Composite Type Mismatch

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-01119 Error: Causes and Solutions Complete Guide

ORA-01119: Error in Creating Database File — Causes, Fixes & Prevention

Top 3 Causes

1. Insufficient Disk Space

2. OS Permission / Ownership Problem

3. Invalid or Non-Existent File Path

Quick Fix Checklist

Prevention Tips

Related Oracle Errors

PostgreSQL 42939 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42939: Reserved Name

Top 3 Causes

1. Using a Built-in Type Name for a Custom Type

2. Redefining a Reserved System Function Name

3. Creating Objects with the pg_ Prefix

Quick Fix Solutions

Prevention Tips

Oracle ORA-01118 Error: Causes and Solutions Complete Guide

ORA-01118: cannot add any more database files: limit exceeded

Top 3 Causes

1. DB_FILES Parameter Limit Reached

2. MAXDATAFILES in Control File Exceeded

3. Accumulated Unused Datafiles

Quick Fix Solutions

Increase DB_FILES (Requires Restart)

Recreate the Control File (MAXDATAFILES Fix)

Drop Unused Tablespaces

Enable AUTOEXTEND to Reduce Need for New Files

Prevention Tips

Related Errors

PostgreSQL 42622 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42622: Name Too Long

Top 3 Causes

1. Object Names Exceeding 63 Bytes

2. Auto-Generated Index or Constraint Names

3. ORM or Migration Tools Auto-Generating Long Names

Quick Fix Solutions

Prevention Tips

Summary

Oracle ORA-01116 Error: Causes and Solutions Complete Guide

ORA-01116: Error in Opening Database File — Causes, Fixes & Prevention

Top 3 Causes

1. Missing or Relocated Datafile

2. OS-Level Permission or Mount Point Failure

3. Datafile Header Corruption

Quick Fix Summary

Prevention Tips

Related Errors

PostgreSQL 42602 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42602: Invalid Name — Causes, Fixes, and Prevention

Top 3 Causes

1. Identifiers Starting with a Digit or Containing Illegal Characters

2. Unsanitized User Input in Dynamic SQL

3. Empty String or NULL Passed as an Identifier

Quick Fix Summary

Prevention Tips

Related Errors

Oracle ORA-01115 Error: Causes and Solutions Complete Guide

ORA-01115: IO Error Reading Block from File — Causes, Fixes & Prevention

Top 3 Causes

1. Physical Storage or Disk Failure (Bad Sectors / SAN Issues)

2. Block Corruption (Logical or Physical)

3. Missing or Inaccessible Datafile (Permissions / Accidental Deletion)

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-01114 Error: Causes and Solutions Complete Guide

ORA-01114: IO Error Writing Block to File — Causes, Fixes & Prevention

Top 3 Causes & SQL Examples

Cause 1: Disk Space Exhausted

3. Creating Objects with the `pg_` Prefix