【资料图】

需求：默认无法直接删除Oracle的users表空间，直接尝试删除会有报错如下：

SQL> drop tablespace users including contents and datafiles;drop tablespace users including contents and datafiles*ERROR at line 1:ORA-12919: Can not drop the default permanent tablespace

报错很明确，USERS目前作为数据库目前默认的永久表空间，暂不支持直接删除。进一步查询，看到有很多用户的确是默认用到了USERS表空间：

SQL>USERNAME       DEFAULT_TABLESPACE      CREATED------------------------------ ------------------------------ ---------SYSRAC       USERS      04-JAN-23SYSTEM       SYSTEM      04-JAN-23SYSBACKUP       USERS      04-JAN-23AUDSYS       USERS      04-JAN-23SYSKM       USERS      04-JAN-23SYS       SYSTEM      04-JAN-23SYSDG       USERS      04-JAN-23OUTLN       SYSTEM      04-JAN-23GSMADMIN_INTERNAL       SYSAUX      04-JAN-23GSMUSER        USERS      04-JAN-23DIP       USERS      04-JAN-23USERNAME       DEFAULT_TABLESPACE      CREATED------------------------------ ------------------------------ ---------XS$NULL        SYSTEM      04-JAN-23REMOTE_SCHEDULER_AGENT       USERS      04-JAN-23DBSFWUSER       SYSAUX      04-JAN-23ORACLE_OCM       USERS      04-JAN-23SYS$UMF        USERS      04-JAN-23DBSNMP       SYSAUX      04-JAN-23APPQOSSYS       SYSAUX      04-JAN-23GSMCATUSER       USERS      04-JAN-23GGSYS       SYSAUX      04-JAN-23XDB       SYSAUX      04-JAN-23ANONYMOUS       SYSAUX      04-JAN-23USERNAME       DEFAULT_TABLESPACE      CREATED------------------------------ ------------------------------ ---------WMSYS       SYSAUX      04-JAN-23OJVMSYS        USERS      04-JAN-23CTXSYS       SYSAUX      04-JAN-23MDSYS       SYSAUX      04-JAN-23ORDSYS       USERS      04-JAN-23ORDDATA        USERS      04-JAN-23ORDPLUGINS       USERS      04-JAN-23SI_INFORMTN_SCHEMA       USERS      04-JAN-23OLAPSYS        SYSAUX      04-JAN-23MDDATA       USERS      04-JAN-23APEX_180200       SYSAUX      04-JAN-23USERNAME       DEFAULT_TABLESPACE      CREATED------------------------------ ------------------------------ ---------FLOWS_FILES       SYSAUX      04-JAN-23APEX_PUBLIC_USER       USERS      04-JAN-23APEX_INSTANCE_ADMIN_USER       USERS      04-JAN-23PDBADMIN       USERS      04-JAN-23HR       SYSAUX      04-JAN-23JINGYU       USERS      06-JAN-23TEAM       USERS      01-MAR-2340 rows selected.

但现在XTTS测试，客户现有源端的users表空间也需要迁移（有用户数据，注意我们并不推荐在users存放用户数据）如果真想删除，也是可以的，就是修改默认表空间，但还需要考虑USERS里面是否有数据要迁移，比较麻烦。现场实施的兄弟提出能否重命名users表空间，这是个好主意，实测是OK，操作也很简单，一条语句搞定：

SQL> alter tablespace users rename to user1;Tablespace altered.

瞬间执行完成，然后再查用户的默认表空间，也已经从users已经变成user1：

SQL> select USERNAME, DEFAULT_TABLESPACE, CREATED from dba_users order by 3;USERNAME       DEFAULT_TABLESPACE      CREATED------------------------------ ------------------------------ ---------SYSRAC       USER1      04-JAN-23SYSTEM       SYSTEM      04-JAN-23SYSBACKUP       USER1      04-JAN-23AUDSYS       USER1      04-JAN-23SYSKM       USER1      04-JAN-23SYS       SYSTEM      04-JAN-23SYSDG       USER1      04-JAN-23OUTLN       SYSTEM      04-JAN-23GSMADMIN_INTERNAL       SYSAUX      04-JAN-23GSMUSER        USER1      04-JAN-23DIP       USER1      04-JAN-23USERNAME       DEFAULT_TABLESPACE      CREATED------------------------------ ------------------------------ ---------XS$NULL        SYSTEM      04-JAN-23REMOTE_SCHEDULER_AGENT       USER1      04-JAN-23DBSFWUSER       SYSAUX      04-JAN-23ORACLE_OCM       USER1      04-JAN-23SYS$UMF        USER1      04-JAN-23DBSNMP       SYSAUX      04-JAN-23APPQOSSYS       SYSAUX      04-JAN-23GSMCATUSER       USER1      04-JAN-23GGSYS       SYSAUX      04-JAN-23XDB       SYSAUX      04-JAN-23ANONYMOUS       SYSAUX      04-JAN-23USERNAME       DEFAULT_TABLESPACE      CREATED------------------------------ ------------------------------ ---------WMSYS       SYSAUX      04-JAN-23OJVMSYS        USER1      04-JAN-23CTXSYS       SYSAUX      04-JAN-23MDSYS       SYSAUX      04-JAN-23ORDSYS       USER1      04-JAN-23ORDDATA        USER1      04-JAN-23ORDPLUGINS       USER1      04-JAN-23SI_INFORMTN_SCHEMA       USER1      04-JAN-23OLAPSYS        SYSAUX      04-JAN-23MDDATA       USER1      04-JAN-23APEX_180200       SYSAUX      04-JAN-23USERNAME       DEFAULT_TABLESPACE      CREATED------------------------------ ------------------------------ ---------FLOWS_FILES       SYSAUX      04-JAN-23APEX_PUBLIC_USER       USER1      04-JAN-23APEX_INSTANCE_ADMIN_USER       USER1      04-JAN-23PDBADMIN       USER1      04-JAN-23HR       SYSAUX      04-JAN-23JINGYU       USER1      06-JAN-23TEAM       USER1      01-MAR-2340 rows selected.

查询数据库默认永久表空间也都变成了USER1：

SQL> SELECT PROPERTY_VALUEFROM DATABASE_PROPERTIESWHERE PROPERTY_NAME = "DEFAULT_PERMANENT_TABLESPACE";  2    3PROPERTY_VALUE------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------USER1

关键词：