ORA-00959: tablespace 'EXAMPLE' does not exist in command prompt-CodePudding

SQL> create user alois identified by alois

  2  default tablespace example password expire;

create user alois identified by alois

*
ERROR at line 1:

ORA-00959: tablespace 'EXAMPLE' does not exist

What should I do next step?

below next step like this:

create user alois identified by alois

default tablespace example password expire;

create user afra identified by oracle

default tablespace example quota unlimited on example;

create user anja identified by oracle;

CodePudding user response：

Apparently the EXAMPLE tablespace doesn't exist; you need to create it before you can assign it to a user as a default. It can be created with the CREATE TABLESPACE command.

Precise syntax will depend on your storage type, datafile naming conventions, etc. See here for details: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLESPACE.html#GUID-51F07BF5-EFAF-4910-9040-C473B86A8BF9

You may need to check with your DBA and have them do this, or provide you with the name of an existing tablespace you can use instead.

CodePudding user response：

The tablespace 'EXAMPLE' does not exist. It is possible to change the user creation command and use the existing tablespace. The USERS tablespace is almost always created.

create user alois identified by alois

default tablespace USERS password expire;

create user afra identified by oracle

default tablespace USERS quota unlimited on example;

create user anja identified by oracle;

Check Tablespace

select FILE_NAME,FILE_ID, TABLESPACE_NAME  from dba_data_files

/u01/oradata/DEV/system01.dbf       1   SYSTEM
/u01/oradata/DEV/sysaux01.dbf       3   SYSAUX
/u01/oradata/DEV/undotbs01.dbf      4   UNDOTBS1
/u01/oradata/DEV/users01.dbf        7   USERS
/u01/oradata/DEV/users_ind01.dbf    5   USERS_IND

 select a.tablespace_name , 
        round(a.used_space*8192/(1024*1024*1024),2) as USED_GB, 
        round(a.tablespace_size*8192/(1024*1024*1024),2) as  MAX_SIZE_GB, 
        round(a.used_percent ,2) as USED_PERCENT 
   from dba_tablespace_usage_metrics a


TABLESPACE_NAME  USED_GB  MAX_SIZE_GB  USED_PERCENT       
-------------- ------ -------------- ---------
SYSAUX        6,9         32        21,58
SYSTEM        1,11        32        3,48
TEMP          0           32        0,01
UNDOTBS1      0,04        32        0,14
USERS         23,06       32        72,07
USERS_IND     11,04       32        34,49