DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01435 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01435: User Does Not Exist – Causes, Fixes & Prevention

What Is ORA-01435?

ORA-01435 is an Oracle database error triggered when a command references a user (schema) that does not exist in the database. It commonly appears during DDL operations such as GRANT, REVOKE, ALTER USER, DROP USER, or CREATE DATABASE LINK when the specified username cannot be found in the data dictionary. This error is especially prevalent during database migrations, environment deployments, and automated scripting tasks.

Top 3 Causes

1. Typo or Incorrect Username in DDL Commands

The most frequent cause is simply a misspelled or incorrectly cased username passed to a DDL statement. Oracle stores usernames in uppercase internally, so any mismatch will trigger this error.

-- Wrong: user doesn't exist or has a typo
GRANT CONNECT TO appp_user;  -- typo: 'appp_user'

-- Step 1: Verify the user exists first
SELECT username, account_status
FROM dba_users
WHERE username = UPPER('app_user');

-- Step 2: If missing, create the user
CREATE USER app_user IDENTIFIED BY "StrongPass#2024";
GRANT CONNECT, RESOURCE TO app_user;
Enter fullscreen mode Exit fullscreen mode

2. Referencing a Dropped or Non-Existent User in a Deployment Script

Deployment or maintenance scripts may attempt to grant privileges or create objects for a user that has already been dropped or was never created in the target environment.

-- Safe pattern: check before executing
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count
  FROM dba_users
  WHERE username = 'BATCH_USER';

  IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'CREATE USER batch_user IDENTIFIED BY "BatchPass#2024"';
    EXECUTE IMMEDIATE 'GRANT CONNECT, CREATE SESSION TO batch_user';
    DBMS_OUTPUT.PUT_LINE('User batch_user created successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('User batch_user already exists. Skipping creation.');
  END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Invalid User Specified in a Database Link

When creating a DATABASE LINK, specifying a user account that does not exist on the remote (or local) database will raise ORA-01435.

-- Verify existing database links
SELECT db_link, username, host
FROM user_db_links;

-- Drop an invalid link and recreate with correct credentials
DROP DATABASE LINK invalid_remote_link;

CREATE DATABASE LINK valid_remote_link
  CONNECT TO valid_user IDENTIFIED BY "RemotePass#2024"
  USING 'REMOTE_DB_ALIAS';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- 1. List all database users to find the correct username
SELECT username, account_status, created
FROM dba_users
ORDER BY username;

-- 2. Bulk check for required users before a deployment
SELECT 'MISSING: ' || u.username AS missing_users
FROM (
  SELECT 'APP_USER'      AS username FROM dual UNION ALL
  SELECT 'BATCH_USER'    AS username FROM dual UNION ALL
  SELECT 'READONLY_USER' AS username FROM dual
) u
WHERE NOT EXISTS (
  SELECT 1 FROM dba_users d
  WHERE d.username = u.username
);

-- 3. Safely drop a user only if it exists
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count
  FROM dba_users WHERE username = 'OLD_USER';
  IF v_count > 0 THEN
    EXECUTE IMMEDIATE 'DROP USER old_user CASCADE';
  END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Add Pre-flight Validation to All Deployment Scripts
Always include a validation block at the start of deployment scripts to confirm that all referenced users exist before executing any DDL. Integrate this check into your CI/CD pipeline as a pre-deploy step to catch issues early.

2. Enable DDL Auditing for User Management
Activate Oracle's built-in auditing for CREATE USER, DROP USER, and ALTER USER events. This gives you a reliable audit trail and helps track who deleted or modified a user account that caused downstream failures.

-- Enable auditing for user lifecycle events
AUDIT CREATE USER;
AUDIT DROP USER;
AUDIT ALTER USER;

-- Review the audit trail
SELECT username, timestamp, action_name, obj_name
FROM dba_audit_trail
WHERE action_name IN ('CREATE USER', 'DROP USER', 'ALTER USER')
ORDER BY timestamp DESC;
Enter fullscreen mode Exit fullscreen mode

Related Oracle Errors

Error Code Description
ORA-01917 User or role does not exist (GRANT/REVOKE context)
ORA-01918 User does not exist (similar, seen in some versions)
ORA-00942 Table or view does not exist (cascading from missing schema)
ORA-28000 The account is locked (user exists but is locked)

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)