Oracle Users表空间重命名

Alfred Zhao

发布于 2023-10-10 18:08:16

1540

发布于 2023-10-10 18:08:16

文章被收录于专栏：从ORACLE起航，领略精彩的IT技术。从ORACLE起航，领略精彩的IT技术。

需求：默认无法直接删除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-23
SYSTEM			       SYSTEM			      04-JAN-23
SYSBACKUP		       USERS			      04-JAN-23
AUDSYS			       USERS			      04-JAN-23
SYSKM			       USERS			      04-JAN-23
SYS			       SYSTEM			      04-JAN-23
SYSDG			       USERS			      04-JAN-23
OUTLN			       SYSTEM			      04-JAN-23
GSMADMIN_INTERNAL	       SYSAUX			      04-JAN-23
GSMUSER 		       USERS			      04-JAN-23
DIP			       USERS			      04-JAN-23

USERNAME		       DEFAULT_TABLESPACE	      CREATED
------------------------------ ------------------------------ ---------
XS$NULL 		       SYSTEM			      04-JAN-23
REMOTE_SCHEDULER_AGENT	       USERS			      04-JAN-23
DBSFWUSER		       SYSAUX			      04-JAN-23
ORACLE_OCM		       USERS			      04-JAN-23
SYS$UMF 		       USERS			      04-JAN-23
DBSNMP			       SYSAUX			      04-JAN-23
APPQOSSYS		       SYSAUX			      04-JAN-23
GSMCATUSER		       USERS			      04-JAN-23
GGSYS			       SYSAUX			      04-JAN-23
XDB			       SYSAUX			      04-JAN-23
ANONYMOUS		       SYSAUX			      04-JAN-23

USERNAME		       DEFAULT_TABLESPACE	      CREATED
------------------------------ ------------------------------ ---------
WMSYS			       SYSAUX			      04-JAN-23
OJVMSYS 		       USERS			      04-JAN-23
CTXSYS			       SYSAUX			      04-JAN-23
MDSYS			       SYSAUX			      04-JAN-23
ORDSYS			       USERS			      04-JAN-23
ORDDATA 		       USERS			      04-JAN-23
ORDPLUGINS		       USERS			      04-JAN-23
SI_INFORMTN_SCHEMA	       USERS			      04-JAN-23
OLAPSYS 		       SYSAUX			      04-JAN-23
MDDATA			       USERS			      04-JAN-23
APEX_180200		       SYSAUX			      04-JAN-23

USERNAME		       DEFAULT_TABLESPACE	      CREATED
------------------------------ ------------------------------ ---------
FLOWS_FILES		       SYSAUX			      04-JAN-23
APEX_PUBLIC_USER	       USERS			      04-JAN-23
APEX_INSTANCE_ADMIN_USER       USERS			      04-JAN-23
PDBADMIN		       USERS			      04-JAN-23
HR			       SYSAUX			      04-JAN-23
JINGYU			       USERS			      06-JAN-23
TEAM			       USERS			      01-MAR-23

40 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-23
SYSTEM			       SYSTEM			      04-JAN-23
SYSBACKUP		       USER1			      04-JAN-23
AUDSYS			       USER1			      04-JAN-23
SYSKM			       USER1			      04-JAN-23
SYS			       SYSTEM			      04-JAN-23
SYSDG			       USER1			      04-JAN-23
OUTLN			       SYSTEM			      04-JAN-23
GSMADMIN_INTERNAL	       SYSAUX			      04-JAN-23
GSMUSER 		       USER1			      04-JAN-23
DIP			       USER1			      04-JAN-23

USERNAME		       DEFAULT_TABLESPACE	      CREATED
------------------------------ ------------------------------ ---------
XS$NULL 		       SYSTEM			      04-JAN-23
REMOTE_SCHEDULER_AGENT	       USER1			      04-JAN-23
DBSFWUSER		       SYSAUX			      04-JAN-23
ORACLE_OCM		       USER1			      04-JAN-23
SYS$UMF 		       USER1			      04-JAN-23
DBSNMP			       SYSAUX			      04-JAN-23
APPQOSSYS		       SYSAUX			      04-JAN-23
GSMCATUSER		       USER1			      04-JAN-23
GGSYS			       SYSAUX			      04-JAN-23
XDB			       SYSAUX			      04-JAN-23
ANONYMOUS		       SYSAUX			      04-JAN-23

USERNAME		       DEFAULT_TABLESPACE	      CREATED
------------------------------ ------------------------------ ---------
WMSYS			       SYSAUX			      04-JAN-23
OJVMSYS 		       USER1			      04-JAN-23
CTXSYS			       SYSAUX			      04-JAN-23
MDSYS			       SYSAUX			      04-JAN-23
ORDSYS			       USER1			      04-JAN-23
ORDDATA 		       USER1			      04-JAN-23
ORDPLUGINS		       USER1			      04-JAN-23
SI_INFORMTN_SCHEMA	       USER1			      04-JAN-23
OLAPSYS 		       SYSAUX			      04-JAN-23
MDDATA			       USER1			      04-JAN-23
APEX_180200		       SYSAUX			      04-JAN-23

USERNAME		       DEFAULT_TABLESPACE	      CREATED
------------------------------ ------------------------------ ---------
FLOWS_FILES		       SYSAUX			      04-JAN-23
APEX_PUBLIC_USER	       USER1			      04-JAN-23
APEX_INSTANCE_ADMIN_USER       USER1			      04-JAN-23
PDBADMIN		       USER1			      04-JAN-23
HR			       SYSAUX			      04-JAN-23
JINGYU			       USER1			      06-JAN-23
TEAM			       USER1			      01-MAR-23

40 rows selected.

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

SQL> SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';  2    3

PROPERTY_VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USER1

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2023-06-08，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle