How to Fix ORA-00959 Table Space Does Not Exist in Oracle?

I was sure my script was fine. I copied it into Oracle SQL Developer, pressed Run, and watched the log… then Oracle yelled:

Error Code

 Error: ORA-00959: tablespace '&TABLESPACE1' does not exist

A missing tablespace? I’d never even typed one! Let me walk you through the exact code I used, why Oracle got confused, how I fixed it, and a few short practice blocks you can try in your own database.

My Code

TABLESPACE1 = "&TABLESPACE1";

CREATE TABLE "DBUSER"
(
  "USER_ID"      VARCHAR2(100 BYTE),
  "USERNAME"     VARCHAR2(100 BYTE),
  "CREATED_BY"   VARCHAR2(100 BYTE),
  "CREATED_DATE" DATE
) TABLESPACE "&TABLESPACE1";

What I Thought Would Happen

DEFINE should stash a real tablespace name in the variable TABLESPACE1, and the CREATE TABLE statement should reuse it.

What Really Happened

Oracle searched for a tablespace literally named &TABLESPACE1. Because that string is only a placeholder, Oracle couldn’t find it and threw ORA-00959.

Why ORA-00959 Shows Up

Root Cause	What’s Going On
The variable never resolves.	`DEFINE TABLESPACE1 = "&TABLESPACE1"` stores another unresolved variable. After substitution, the text is still `&TABLESPACE1`.
No fallback tablespace.	If the final value is empty or wrong, Oracle can’t guess a default.
The real tablespace is absent.	Even with the right variable, the target tablespace must exist in the catalog.

A Quick, Safe Fix

-- 1  Store the real name—no ampersand inside the value
DEFINE TABLESPACE1 = USERS      -- change USERS to a tablespace that exists

-- 2  Use && to reuse it without another prompt
CREATE TABLE DBUSER
(
  USER_ID      VARCHAR2(100),
  USERNAME     VARCHAR2(100),
  CREATED_BY   VARCHAR2(100),
  CREATED_DATE DATE
) TABLESPACE &&TABLESPACE1;

Why double ampersand?
&&TABLESPACE1 tells SQL*Plus (and SQL Developer’s worksheet) to reuse the value you already defined—no extra pop-up.

Hands On Practice

See All Tablespaces

tablespace_name, status
FROM   dba_tablespaces
ORDER  BY tablespace_name;

Create a Tablespace Only If It’s Missing


  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count
  FROM   dba_tablespaces
  WHERE  tablespace_name = 'USERS';  -- adjust as needed

  IF v_count = 0 THEN
    EXECUTE IMMEDIATE '
      CREATE TABLESPACE USERS
      DATAFILE ''/u01/app/oracle/oradata/USERS01.dbf'' SIZE 100M
      AUTOEXTEND ON NEXT 10M';
  END IF;
END;
/

Reusable Procedure for Safe Table Creation

OR REPLACE PROCEDURE create_dbuser_in_ts (p_ts VARCHAR2)
IS
  ts_exists NUMBER;
BEGIN
  SELECT COUNT(*) INTO ts_exists
  FROM   dba_tablespaces
  WHERE  tablespace_name = UPPER(p_ts);

  IF ts_exists = 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Tablespace '||p_ts||' does not exist.');
  END IF;

  EXECUTE IMMEDIATE '
    CREATE TABLE dbuser (
      user_id      VARCHAR2(100),
      username     VARCHAR2(100),
      created_by   VARCHAR2(100),
      created_date DATE,
      CONSTRAINT pk_dbuser PRIMARY KEY (user_id)
    ) TABLESPACE '||p_ts;
END;
/
-- Run it
EXEC create_dbuser_in_ts('USERS');

Insert Sample Rows and Check

INTO dbuser VALUES
  ('U001', 'Ali',  'System', SYSDATE),
  ('U002', 'Sara', 'System', SYSDATE);

COMMIT;

SELECT * FROM dbuser;

Clean Up Quickly

TABLE dbuser PURGE;

Common Slip Ups

Quoting the variable – Wrapping &TABLESPACE1 in ' ' or " " locks it in as a literal string. Leave it unquoted.
Spelling inside DEFINE – Never place an extra & inside the value itself.
Case sensitivity – Oracle stores tablespace names uppercase by default, so pass them in uppercase to be safe.

Final Thought

ORA-00959 isn’t mysterious it’s Oracle’s blunt way of saying, “I can’t see that tablespace.” Once I defined the variable properly or created the tablespace first, the script sailed through. With the snippets above, I now spin up tables, run tests, and tear them down in minutes without seeing that error again. The best way to master Oracle quirks is to practice until they’re no longer quirks at all.