DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01129 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01129: User's Default or Temporary Tablespace Does Not Exist

ORA-01129 is raised when Oracle cannot locate the default or temporary tablespace assigned to a user account, meaning the tablespace entry exists in the data dictionary (DBA_USERS) but the actual tablespace no longer exists in DBA_TABLESPACES. This typically surfaces during user login, DDL operations, or after database migration and recovery tasks where tablespace configurations fall out of sync.

Top 3 Causes

1. Tablespace Dropped Without Reassigning Users

The most common cause: a DBA drops a tablespace without first updating user accounts that reference it as their default or temporary tablespace.

-- Check which users are affected before dropping a tablespace
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE default_tablespace = 'TABLESPACE_TO_DROP'
   OR temporary_tablespace = 'TABLESPACE_TO_DROP';

-- Identify all users pointing to non-existent tablespaces
SELECT u.username, u.default_tablespace, u.temporary_tablespace
FROM dba_users u
LEFT JOIN dba_tablespaces dt ON u.default_tablespace = dt.tablespace_name
LEFT JOIN dba_tablespaces tt ON u.temporary_tablespace = tt.tablespace_name
WHERE dt.tablespace_name IS NULL
   OR tt.tablespace_name IS NULL;
Enter fullscreen mode Exit fullscreen mode

2. Incomplete Database Migration or Import

When using Data Pump (expdp/impdp) or RMAN to restore only a partial schema, the user's assigned tablespaces may not be included in the restore scope, leaving orphaned references in the dictionary.

-- After migration, validate tablespace consistency
SELECT u.username,
       u.default_tablespace,
       u.temporary_tablespace,
       CASE WHEN dt.tablespace_name IS NULL THEN 'MISSING' ELSE 'OK' END AS dflt_status,
       CASE WHEN tt.tablespace_name IS NULL THEN 'MISSING' ELSE 'OK' END AS temp_status
FROM dba_users u
LEFT JOIN dba_tablespaces dt ON u.default_tablespace = dt.tablespace_name
LEFT JOIN dba_tablespaces tt ON u.temporary_tablespace = tt.tablespace_name
WHERE dt.tablespace_name IS NULL OR tt.tablespace_name IS NULL;
Enter fullscreen mode Exit fullscreen mode

3. Database Upgrade or Dictionary Corruption

During major version upgrades or improper manual dictionary modifications, the mapping between users and tablespaces in USER$ or TS$ system tables can become corrupted, triggering ORA-01129 on login.

-- Check database-level default tablespace settings
SELECT property_name, property_value
FROM database_properties
WHERE property_name IN ('DEFAULT_TEMP_TABLESPACE',
                        'DEFAULT_PERMANENT_TABLESPACE');
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Fix 1 – Reassign the user to an existing tablespace (fastest resolution)

-- Alter default tablespace
ALTER USER problem_user DEFAULT TABLESPACE USERS;

-- Alter temporary tablespace
ALTER USER problem_user TEMPORARY TABLESPACE TEMP;

-- Verify the fix
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = 'PROBLEM_USER';
Enter fullscreen mode Exit fullscreen mode

Fix 2 – Recreate the missing tablespace

-- Recreate missing permanent tablespace
CREATE TABLESPACE APP_DATA
  DATAFILE '/u01/oradata/ORCL/app_data01.dbf' SIZE 500M
  AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

-- Recreate missing temporary tablespace
CREATE TEMPORARY TABLESPACE APP_TEMP
  TEMPFILE '/u01/oradata/ORCL/app_temp01.dbf' SIZE 200M
  AUTOEXTEND ON NEXT 50M MAXSIZE 2G;
Enter fullscreen mode Exit fullscreen mode

Fix 3 – Bulk fix for multiple affected users

-- Auto-generate ALTER USER statements for all affected users
SELECT 'ALTER USER ' || u.username ||
       ' DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;'
FROM dba_users u
LEFT JOIN dba_tablespaces dt ON u.default_tablespace = dt.tablespace_name
LEFT JOIN dba_tablespaces tt ON u.temporary_tablespace = tt.tablespace_name
WHERE (dt.tablespace_name IS NULL OR tt.tablespace_name IS NULL)
  AND u.username NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN');
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always check dependencies before dropping a tablespace.
Make it a standard checklist item to query DBA_USERS for any users referencing the tablespace before executing DROP TABLESPACE. Reassign affected users first, then drop.

2. Add a post-migration validation step.
After every Data Pump import, RMAN restore, or upgrade, run a consistency check query against DBA_USERS joined with DBA_TABLESPACES. If the result count is greater than zero, remediate immediately before handing the database back to application teams.

-- Post-migration health check: should return 0 rows
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users u
WHERE NOT EXISTS (SELECT 1 FROM dba_tablespaces dt
                  WHERE dt.tablespace_name = u.default_tablespace)
   OR NOT EXISTS (SELECT 1 FROM dba_tablespaces tt
                  WHERE tt.tablespace_name = u.temporary_tablespace);
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-00959 – Tablespace does not exist (referenced at object creation time)
  • ORA-01647 – Tablespace is not a temporary tablespace (wrong type assigned)
  • ORA-25153 – Temporary tablespace is empty or has no tempfiles added

📖 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)