Copy Oracle database to my local database oracle xe problem with tablespace

Question

I am trying to copy my Oracle database to my local Oracle XE database. I am using Oracle SQL Developer.

I connected to my database, then exported a .sql file from my database. I then created a new local database and I tried running the .sql file against it.

I get an error when I did that:

  CREATE TABLE "user"."table_name" 
   :::
   :::
  TABLESPACE "tablespace name" ;

Error report -
ORA-00959: tablespace "tablespace name" does not exist
00959. 00000 - " tablespace '%s' does not exist

• Cause:
• Action:

What is "tablespace"? Should I remove it?

Answer 1

What is "tablespace"? Should I remove it?

CREATE TABLE isn't always a simple statement, the one which enumerates columns and their datatypes, possibly some constraints. Nope, it can get really complex - see documentation.

Just for example, this is what SQL Developer extracts from metadata for Scott's DEPT table:

  CREATE TABLE "SCOTT"."DEPT" 
   ("DEPTNO" NUMBER(2,0), 
    "DNAME" VARCHAR2(14 BYTE), 
    "LOC" VARCHAR2(13 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

TABLESPACE is one of parameters you can use, and it specifies ... well, tablespace in which Oracle creates that table. Usually, there's one tablespace we use for our data. Sometimes, there are more of them - in that case, you can choose which one to use. If you don't specify it, Oracle will use a default tablespace assigned to that user.

Therefore, can you omit it? Yes, you can - table will then be created in that default tablespace (regardless of its name).

Answer 2

Seems like you have the issue in the following line:

TABLESPACE "tablespace name" ;

If you wish to use the "tablespace name" with double quotes surrounded by the name then make sure that you use the case-sensitive tablespace name.

Whenever you use the double quotes, Oracle treats it as case-sensitive. tablespace names without double quotes are treated by Oracle with upper case names. Try removing the double quote and check the result OR use the double quotes with the upper case tablespace name.

TABLESPACE my_table_space_name;

OR

TABLESPACE "MY_TABLESPACE_NAME";