Soham Banerjee Soham Banerjee - 3 months ago 15
SQL Question

SQL Error: Tablespace does not exist

I am running this script on sql oracle developer:-

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


I am getting this error:-

SQL Error: ORA-00959: tablespace '&TABLESPACE1' does not exist
00959. 00000 - "tablespace '%s' does not exist"

Answer

NOTE Not all SQL*Plus commands are necessary to run in SQL Developer. Also, it depends on the SQL Developer version, the latest versions have a lot of SQL*Plus commands support.

I tested in SQL Developer version 3.2.20.10

& is used for substitution variable in SQL*Plus .

For example,

In SQL*Plus

SQL> DEFINE TABLESPACE1 = &TABLESPACE1
Enter value for tablespace1: new_tablespace
SQL> SELECT '&TABLESPACE1' from dual;
old   1: SELECT '&TABLESPACE1' from dual
new   1: SELECT 'new_tablespace' from dual

'NEW_TABLESPAC
--------------
new_tablespace

SQL>

In SQL Developer

DEFINE TABLESPACE1 = &TABLESPACE1

<Enter the value when prompted> -- I entered "t"

old:DEFINE TABLESPACE1 = &TABLESPACE1
new:DEFINE TABLESPACE1 = t

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

  1. 00000 - "tablespace '%s' does not exist"

Are you sure you are executing it as a script in SQL Developer. You could press F5 to execute it as a script. Did you substitute the value for the variable?

For example,

SQL> DEFINE TABLESPACE1 = "&TABLESPACE1"
Enter value for tablespace1: new_tablespace
SQL> CREATE TABLE "DBUSER"
  2  (
  3  "USER_ID" VARCHAR2(100 BYTE),
  4  "USERNAME" VARCHAR2(100 BYTE),
  5  "CREATED_BY" VARCHAR2(100 BYTE),
  6  "CREATED_DATE" DATE
  7  ) TABLESPACE "&TABLESPACE1" ;
old   7: ) TABLESPACE "&TABLESPACE1"
new   7: ) TABLESPACE "new_tablespace"
) TABLESPACE "new_tablespace"
             *
ERROR at line 7:
ORA-00959: tablespace 'new_tablespace' does not exist


SQL>

In your case, if the value is properly substituted, and if tablespace exists, you should be able to create the table.