ORA-00959 Tablespace Does Not Exist

ORA-00959: Tablespace Does Not Exist

Error Overview

Error Text: ORA-00959: tablespace 'tablespace_name' does not exist

The ORA-00959 error occurs when Oracle cannot find a tablespace that is referenced in a DDL or DML statement. This commonly happens during object creation, user management, or when importing data from another database.

Common Causes

1. Typo in Tablespace Name

• Misspelled tablespace name
• Wrong case used (though Oracle normalizes to uppercase)
• Extra spaces in name

2. Database Migration/Import Issues

• Source database had different tablespaces
• Import remap not configured
• Partial database clone

3. Tablespace Dropped or Never Created

• Tablespace accidentally dropped
• Deployment script not executed
• Different environment configurations

4. Schema Setup Errors

• User creation with non-existent default tablespace
• Table creation specifying wrong tablespace
• Index tablespace different from production

Diagnostic Queries

List All Tablespaces

-- All tablespaces in database
SELECT
  tablespace_name,
  status,
  contents,
  extent_management,
  segment_space_management
FROM dba_tablespaces
ORDER BY tablespace_name;

-- Tablespaces with space information
SELECT
  t.tablespace_name,
  t.status,
  ROUND(SUM(d.bytes)/1024/1024/1024, 2) as size_gb,
  ROUND(SUM(NVL(f.bytes, 0))/1024/1024/1024, 2) as free_gb
FROM dba_tablespaces t
JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
LEFT JOIN dba_free_space f ON t.tablespace_name = f.tablespace_name
GROUP BY t.tablespace_name, t.status
ORDER BY t.tablespace_name;

Search for Similar Names

-- Find tablespaces with similar names
SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name LIKE '%&partial_name%'
   OR SOUNDEX(tablespace_name) = SOUNDEX('&tablespace_name')
ORDER BY tablespace_name;

-- Check for case issues
SELECT tablespace_name
FROM dba_tablespaces
WHERE UPPER(tablespace_name) = UPPER('&tablespace_name');

Check User’s Tablespace References

-- User's default and temp tablespaces
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = UPPER('&username');

-- What tablespaces does user have quotas on
SELECT tablespace_name, bytes, max_bytes
FROM dba_ts_quotas
WHERE username = UPPER('&username');

-- Tables in specific tablespace
SELECT table_name, tablespace_name
FROM dba_tables
WHERE owner = UPPER('&owner')
  AND tablespace_name = UPPER('&tablespace_name');

Resolution Steps

1. Create the Missing Tablespace

-- Basic tablespace creation
CREATE TABLESPACE app_data
  DATAFILE '/u01/oradata/mydb/app_data01.dbf' SIZE 1G
  AUTOEXTEND ON NEXT 100M MAXSIZE 10G
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

-- Bigfile tablespace (single large file)
CREATE BIGFILE TABLESPACE app_bigdata
  DATAFILE '/u01/oradata/mydb/app_bigdata01.dbf' SIZE 10G
  AUTOEXTEND ON NEXT 1G MAXSIZE 100G;

-- Tablespace with specific block size (requires non-default buffer pool)
CREATE TABLESPACE app_16k
  DATAFILE '/u01/oradata/mydb/app_16k01.dbf' SIZE 1G
  BLOCKSIZE 16K
  EXTENT MANAGEMENT LOCAL;

2. Create Temporary Tablespace

-- Temporary tablespace
CREATE TEMPORARY TABLESPACE temp_new
  TEMPFILE '/u01/oradata/mydb/temp_new01.dbf' SIZE 2G
  AUTOEXTEND ON NEXT 500M MAXSIZE 10G
  EXTENT MANAGEMENT LOCAL
  UNIFORM SIZE 1M;

-- Make it default
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

3. Fix User’s Default Tablespace

-- Update user's default tablespace
ALTER USER username DEFAULT TABLESPACE existing_tablespace;

-- Update temporary tablespace
ALTER USER username TEMPORARY TABLESPACE temp;

-- Verify changes
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = UPPER('&username');

4. Import/Export Remap Tablespaces

-- Data Pump import with tablespace remapping
-- From command line:
-- impdp user/pass DIRECTORY=dump_dir DUMPFILE=export.dmp \
--   REMAP_TABLESPACE=old_ts:new_ts

-- Multiple remaps
-- impdp user/pass DIRECTORY=dump_dir DUMPFILE=export.dmp \
--   REMAP_TABLESPACE=old_data:new_data \
--   REMAP_TABLESPACE=old_index:new_index

-- Check current tablespace mapping before import
SELECT DISTINCT tablespace_name
FROM dba_segments
WHERE owner = UPPER('&owner');

5. Modify DDL to Use Existing Tablespace

-- Original failing statement
-- CREATE TABLE my_table (...) TABLESPACE non_existent_ts;

-- Modified to use existing tablespace
CREATE TABLE my_table (
  id NUMBER PRIMARY KEY,
  data VARCHAR2(100)
) TABLESPACE users;  -- Use existing tablespace

-- Create index in existing tablespace
CREATE INDEX idx_my_table ON my_table(data)
TABLESPACE users;

Prevention Strategies

1. Pre-Deployment Tablespace Verification

-- Script to verify required tablespaces exist
DECLARE
  TYPE t_ts_list IS TABLE OF VARCHAR2(30);
  v_required_ts t_ts_list := t_ts_list('APP_DATA', 'APP_INDEX', 'APP_LOB');
  v_count NUMBER;
BEGIN
  FOR i IN 1..v_required_ts.COUNT LOOP
    SELECT COUNT(*) INTO v_count
    FROM dba_tablespaces
    WHERE tablespace_name = v_required_ts(i);

    IF v_count = 0 THEN
      DBMS_OUTPUT.PUT_LINE('MISSING: ' || v_required_ts(i));
    ELSE
      DBMS_OUTPUT.PUT_LINE('OK: ' || v_required_ts(i));
    END IF;
  END LOOP;
END;
/

2. Standard Tablespace Creation Script

-- Environment setup script
-- Run before application deployment

-- Data tablespace
DECLARE
  v_path VARCHAR2(200) := '/u01/oradata/mydb/';
BEGIN
  FOR ts IN (
    SELECT 'APP_DATA' as name, 'app_data01.dbf' as file, 5 as size_gb FROM dual
    UNION ALL
    SELECT 'APP_INDEX', 'app_index01.dbf', 2 FROM dual
    UNION ALL
    SELECT 'APP_LOB', 'app_lob01.dbf', 10 FROM dual
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLESPACE ' || ts.name ||
        ' DATAFILE ''' || v_path || ts.file || ''' SIZE ' || ts.size_gb || 'G' ||
        ' AUTOEXTEND ON NEXT 500M MAXSIZE 50G' ||
        ' EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
      DBMS_OUTPUT.PUT_LINE('Created: ' || ts.name);
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -1543 THEN  -- Tablespace already exists
          DBMS_OUTPUT.PUT_LINE('Exists: ' || ts.name);
        ELSE
          RAISE;
        END IF;
    END;
  END LOOP;
END;
/

3. Document Tablespace Requirements

-- Generate tablespace documentation
SELECT
  'Tablespace: ' || tablespace_name || CHR(10) ||
  '  Status: ' || status || CHR(10) ||
  '  Contents: ' || contents || CHR(10) ||
  '  Size: ' || ROUND(bytes/1024/1024/1024, 2) || ' GB' || CHR(10) ||
  '  Autoextend: ' || DECODE(autoextensible, 'YES', 'Yes', 'No') || CHR(10) as documentation
FROM dba_tablespaces t
JOIN (
  SELECT tablespace_name, SUM(bytes) bytes, MAX(autoextensible) autoextensible
  FROM dba_data_files
  GROUP BY tablespace_name
) d ON t.tablespace_name = d.tablespace_name
ORDER BY t.tablespace_name;

Common Tablespace Types

Type	Purpose	Example
SYSTEM	Data dictionary	SYSTEM
SYSAUX	Auxiliary system	SYSAUX
UNDO	Transaction undo	UNDOTBS1
TEMP	Temporary segments	TEMP
USERS	Default user data	USERS
Custom	Application data	APP_DATA, APP_INDEX

Related Errors

• ORA-01950 - No privileges on tablespace
• ORA-01536 - Space quota exceeded
• ORA-01653 - Unable to extend table
• ORA-01652 - Unable to extend temp segment

Emergency Response

Quick Tablespace Creation

-- Minimal tablespace creation for emergency
CREATE TABLESPACE emergency_ts
  DATAFILE '/u01/oradata/mydb/emergency01.dbf' SIZE 1G
  AUTOEXTEND ON;

-- Then fix the reference
ALTER USER problem_user DEFAULT TABLESPACE emergency_ts;
-- or
ALTER TABLE problem_table MOVE TABLESPACE emergency_ts;

Find What’s Referencing Missing Tablespace

-- Check error message for tablespace name, then search
SELECT 'USER' as type, username as name, default_tablespace as ts
FROM dba_users WHERE default_tablespace = UPPER('&missing_ts')
UNION ALL
SELECT 'SEGMENT', owner || '.' || segment_name, tablespace_name
FROM dba_segments WHERE tablespace_name = UPPER('&missing_ts');