I’m trying to create a small database in Oracle XE 18c using a slightly modified script for the same task as previously used in Oracle XE 11g.

The creation of the only tablespace works correctly as does the creation of two roles, one for an admin user the other for a normal user.

The create user SQL command throws an ORA-00959 error, probably because my creation of two tablespaces, one in the container database the other in the pluggable database, is not correctly co-ordinated.

What am I doing wrong?? It must be quite simple, basic to the new pluggable databases functionality.

The following shows how to reproduce the problem:

SQLPLUS /NOLOG
SQL> CONNECT SYS/<SYS pwd>@XE AS SYSDBA
connected.
SQL> SHOW CON_NAME
     CDB$ROOT

SQL> CREATE TABLESPACE "MY_TABLESPACE" SIZE 50M AUTOEXTEND ON
                       DATA FILE 'C:OracleAppORADATAXEMyFile.DBF';

SQL> ALTER SESSION SET CONTAINER = XEPDB1;

SQL> CREATE TABLESPACE "MY_TABLESPACE"
                       LOGGING
                       DATA FILE 'C:OracleAppORADATAXEXEPDB1MyFile.DBF'
                       SIZE 100M EXTENT MANAGEMENT LOCAL
                       SEGMENT SPACE MANAGEMENT AUTO;

/* Both tablespaces and data files were correctly created, and the files in the
/* correct folders: ORADATAXE AND ORADATAXEXEPDB1.

/* Create two roles: MY_ADMIN_ROLE and MY_NORMAL_ROLE, with GRANTS etc.   */

SQL> CREATE USER "MY_ADMIN_USER"
                       PROFILE "DEFAULT"
                       IDENTIFIED BY "<my admin pwd>"
                       DEFAULT TABLESPACE "MY_TABLESPACE"
                       TEMPORARY TABLESPACE "TEMP"
                       QUOTA UNLIMITED ON "MY_TABLESPACE"
                       ACCOUNT UNLOCK;

ORA-00959: tablespace 'MY_TABLESPACE' does not exist.