Oracle Error ORA-00959 – Tablespace Does Not Exist But Visible in Select

oracleoracle-12ctablespaces

So, I am connected as SYSDBA to Oracle and trying to run this command:

 create user C##demo identified by demopassword;

That ends with User created.

Then I run:

alter user C##demo default tablespace PAVEL_DATA temporary tablespace PAVEL_TEMP;

Which ends in error:

ORA-00959: tablespace 'PAVEL_DATA' does not exist

But if I run this command:

SELECT TABLESPACE_NAME, STATUS, CONTENTS
FROM USER_TABLESPACES;

I can see the tablespaces:

  TABLESPACE_NAME          STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                 ONLINE    PERMANENT
SYSAUX                 ONLINE    PERMANENT
TEMP                   ONLINE    TEMPORARY
USERS                  ONLINE    PERMANENT
UNDOTBS2               ONLINE    UNDO
PAVEL_DATA             ONLINE    PERMANENT
PAVEL_TEMP             ONLINE    TEMPORARY

So, why am I getting the error tablespace does not exist when it is created?

EDIT

Continued according the comments, this is what I see when I run select tablespace_name, con_id from cdb_tablespaces :

TABLESPACE_NAME            CON_ID
------------------------------ ----------
PAVEL_DATA              1
PAVEL_TEMP              1
SYSTEM                  4
SYSAUX                  4
TEMP                    4
USERS                   4

TABLESPACE_NAME            CON_ID
------------------------------ ----------
SYSTEM                  3
SYSAUX                  3
TEMP                    3
USERS                   3
EXAMPLE                 3    
PAVEL_TEMP              3
PAVEL_DATA              3

So, both tablespaces are created in DB$ROOT (id=1) and in pluggable datavase (id>2). But still, when connected to the pluggable database, I am getting the same error. It must be something stupid, but I am blind now…

Best Answer

While creating a common user, any default tablespace, temporary tablespace, or profile specified using the following clauses must exist in all the containers belonging to the CDB:

  • DEFAULT TABLESPACE
  • TEMPORARY TABLESPACE
  • QUOTA
  • PROFILE

    • More

    According to the output you have updated it appears that the PAVEL_DATA tablespace doesn't exist in container number 4.

    Moreover, the CREATE USER or ALTER USER command with DEFAULT TABLESPACE or DEFAULT TEMPORARY TABLESPACE clause may succeed if the default/temporary tablespace specified don't exist on the PDBS which are currently closed. But you may get errors when you try to open or try to connect these newly opened PDBs using the common user you have just created or altered.

    Demonstration:

    SQL> create user c##_sales_hr identified by password default tablespace tbs_sales;
create user c##_sales_hr identified by password default tablespace tbs_sales
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database ORAPDB1
ORA-00959: tablespace 'TBS_SALES' does not exist


SQL> select tablespace_name, con_id from cdb_tablespaces;

TABLESPACE_NAME                CON_ID
------------------------------ ----------
SYSTEM                         1
SYSAUX                         1
UNDOTBS1                       1
TEMP                           1
USERS                          1
TBS_SALES                      1
SYSTEM                         4
SYSAUX                         4
TEMP                           4
ORAPDB1_TBS1                   4
MGMT_ECM_DEPOT_TS              4

11 rows selected.

SQL> alter session set container=orapdb1;

Session altered.

SQL> create tablespace tbs_sales datafile '+DATA' size 50M;

Tablespace created.

SQL> conn / as sysdba
Connected.
SQL> create user c##_sales_hr identified by password default tablespace tbs_sales;

User created.
    Related Question