CODEWITHSUNDEEP
oracle-databaseoracle-xetablespacecreateuser
Denis Beurive
Denis Beurive

Reputation: 445

Oracle XE 21c: cannot associate tablespace with user

This is the first time that I am using Oracle.

I am using Oracle XE 21c, within a Docker container (gvenzl/oracle-xe:latest).

As SYSTEM, I create the tablespace CONCERTO_DATA. Everything is fine:

create TABLESPACE CONCERTO_DATA
    datafile 'concerto_data.dbf' size 10M
    autoextend on
    next 512K
    maxsize unlimited;

Then I create the user:

CREATE USER CONCERTO IDENTIFIED BY "password"
       TEMPORARY TABLESPACE temp;
GRANT DBA, CREATE ANY TYPE  TO CONCERTO;
GRANT EXECUTE ON DBMS_AQADM TO CONCERTO;
GRANT EXECUTE ON DBMS_AQ    TO CONCERTO;
GRANT AQ_ADMINISTRATOR_ROLE TO CONCERTO;
GRANT CONNECT TO CONCERTO;
GRANT CREATE TYPE TO CONCERTO;
select username, default_tablespace from dba_users where username = 'CONCERTO';

Everything is fine. The default tablespace associated with the user CONCERTO is USERS.

I try to associate the previously created tablespace CONCERTO_DATA:

alter user CONCERTO default tablespace CONCERTO_DATA;

Then, a get a strange error:

[99999][65048] ORA-65048: erreur détectée lors du traitement de l'instruction DDL en cours dans la base de données pluggable XEPDB1
ORA-00959: le tablespace 'CONCERTO_DATA' n'existe pas
Position: 0

It says that the tablespace CONCERTO_DATA does not exist.

However, the table space exists:

select tablespace_name, con_id, STATUS from cdb_tablespaces WHERE TABLESPACE_NAME='CONCERTO_DATA';

Result is:

CONCERTO_DATA, 1, ONLINE

I have tried to associate the tablespace to the user during the user's creation:

CREATE USER CONCERTO IDENTIFIED BY "password"
       DEFAULT TABLESPACE CONCERTO_DATA
       TEMPORARY TABLESPACE temp;

But, I get the same error.

I have tried to create the tablespace using the user CONCERTO (instead of SYSTEM). But, I get the same error.

I think that the error is related to the container ID (COND_ID=1).

SELECT CON_ID, NAME FROM V$CONTAINERS;

Result:

1, CDB$ROOT
2, PDB$SEED
3, XEPDB1

I think that the container ID for the tablespace CONCERTO_DATA should be 3 (instead of 1). But I have no idea how to change it.

Upvotes: 0

Views: 1093

Answers (1)

p3consulting
p3consulting

Reputation: 4670

You probably created the TABLESPACE in the CDB because you forgot to

alter session set container = XEPDB1 ;

Upvotes: 3

Related Questions