1

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...

  • For common user, "The DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA and PROFILE must all reference objects that exist in all containers." . I guess PAVEL_DATA exists only in one container. – a1ex07 Jan 2 '17 at 15:05
  • You are probably right. How to make it accessible for all containers? (I am typing this from my phone so if I don't get answers I will UTFG later) – Pavel Janicek Jan 2 '17 at 18:01
  • 2
    you first check in which of containers it doesn't exist : select tablespace_name, con_id from cdb_tablespaces . If you want to make PAVEL_DATA to be default for common user, you need to make sure it's created for all user containers (id>2) and CDB$ROOT (id =1). To do that you connect to each container and create tablespace as if it was non-pdb database. – a1ex07 Jan 2 '17 at 19:59
  • @a1ex07 I think I did that, see the edits. But still I am getting the same error. What the heck am I doing wrong? Thanks so much for help – Pavel Janicek Jan 3 '17 at 8:09
2

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.
    
    • That was the last bit I needed. Thanks a lot. Now everything seems to be working – Pavel Janicek Jan 3 '17 at 11:56

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

    Not the answer you're looking for? Browse other questions tagged or ask your own question.