How to Fix ORA-00959: Tablespace ‘TEST_TBS’ Does Not Exist

If you’re working with Oracle and see this error:

ORA-00959: tablespace 'TEST_TBS' does not exist

It means you’re trying to use a tablespace that hasn’t been created in your database yet. This error commonly appears during user creation, table creation, or while running SQL scripts that reference missing tablespaces.

Let’s understand the causes, how to fix it, and how to avoid it in the future.

When and Why This Error Happens

Oracle shows this error when the name of the tablespace you are trying to use doesn’t exist. Here are common situations where this occurs:

1. Creating a Table with a Missing Tablespace

CREATE TABLE employees (
  id NUMBER,
  name VARCHAR2(100)
) TABLESPACE TEST_TBS;

If TEST_TBS hasn’t been created yet, Oracle throws the error.

2. Creating an Index in a Non-Existent Tablespace

CREATE INDEX emp_id_idx 
ON employees (id) 
TABLESPACE TEST_TBS;

3. Creating a User with a Default or Temporary Tablespace That Doesn’t Exist

CREATE USER test_user IDENTIFIED BY mypassword
DEFAULT TABLESPACE TEST_TBS
TEMPORARY TABLESPACE TEMP_TBS;

If TEST_TBS or TEMP_TBS doesn’t exist, the error will occur.

4. Running an Import or SQL Script

Many SQL or schema dump files include tablespace references:

CREATE TABLE orders (
  order_id NUMBER
) TABLESPACE TEST_TBS;

If the destination database doesn’t have TEST_TBS, it will fail.

✅ How to Fix ORA-00959

Step 1: Check if the Tablespace Exists

Use the following SQL:

SELECT tablespace_name 
FROM dba_tablespaces 
WHERE tablespace_name IN ('TEST_TBS', 'TEMP_TBS');

If no rows are returned, the tablespaces are missing.

Step 2: Create the Required Tablespace(s)

Create permanent tablespace:

CREATE TABLESPACE TEST_TBS 
DATAFILE '/u01/app/oracle/oradata/ORCL/test_tbs01.dbf' 
SIZE 100M 
AUTOEXTEND ON 
NEXT 10M 
MAXSIZE 1G;

Create temporary tablespace:

CREATE TEMPORARY TABLESPACE TEMP_TBS 
TEMPFILE '/u01/app/oracle/oradata/ORCL/temp_tbs01.dbf' 
SIZE 50M 
AUTOEXTEND ON;

Step 3: Modify the SQL If You Can’t Create the Tablespace

If you don’t have permission to create tablespaces or want to keep things simple, update the SQL to use existing tablespaces.

Example for table creation:

CREATE TABLE employees (
  id NUMBER,
  name VARCHAR2(100)
) TABLESPACE USERS;

Example for user creation:

CREATE USER test_user IDENTIFIED BY mypassword
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

Or remove the TABLESPACE clause to use the default.

Tips to Avoid This Error

• ✅ Always check tablespace names before running scripts
• ✅ Include tablespace creation commands in schema setup
• ✅ Use environment-safe defaults like USERS and TEMP
• ✅ Avoid hardcoding tablespace names unless needed

For more tablespace commands and options, see:
🔗 Oracle Tablespace Management