ORA-00959：命令提示符下不存在表空间'EXAMPLE'

Question

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

---

下一步我应该做什么?

如下所示:

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;

Answer 1

表空间'EXAMPLE'不存在。可以修改用户创建命令，使用已有的表空间。USERS表空间几乎总是被创建的。

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;

检查表空间

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

Answer 2

显然EXAMPLE表空间不存在;您需要先创建它，然后才能将其作为默认值分配给用户。可以使用CREATE TABLESPACE命令创建。

精确的语法取决于您的存储类型、数据文件命名约定等。详见:https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLESPACE.html#GUID-51F07BF5-EFAF-4910-9040-C473B86A8BF9

您可能需要咨询您的DBA并让他们这样做，或者提供您可以使用的现有表空间的名称。