Oracle?Users表空間重命名問題解決

需求：默認無法直接刪除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-23GSMUSERUSERS 04-JAN-23DIP USERS 04-JAN-23USERNAME DEFAULT_TABLESPACE CREATED------------------------------ ------------------------------ ---------XS$NULLSYSTEM 04-JAN-23REMOTE_SCHEDULER_AGENT USERS 04-JAN-23DBSFWUSER SYSAUX 04-JAN-23ORACLE_OCM USERS 04-JAN-23SYS$UMFUSERS 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-23OJVMSYSUSERS 04-JAN-23CTXSYS SYSAUX 04-JAN-23MDSYS SYSAUX 04-JAN-23ORDSYS USERS 04-JAN-23ORDDATAUSERS 04-JAN-23ORDPLUGINS USERS 04-JAN-23SI_INFORMTN_SCHEMA USERS 04-JAN-23OLAPSYSSYSAUX 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里面是否有數據要遷移，比較麻煩?，F場實施的兄弟提出能否重命名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-23GSMUSERUSER1 04-JAN-23DIP USER1 04-JAN-23USERNAME DEFAULT_TABLESPACE CREATED------------------------------ ------------------------------ ---------XS$NULLSYSTEM 04-JAN-23REMOTE_SCHEDULER_AGENT USER1 04-JAN-23DBSFWUSER SYSAUX 04-JAN-23ORACLE_OCM USER1 04-JAN-23SYS$UMFUSER1 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-23OJVMSYSUSER1 04-JAN-23CTXSYS SYSAUX 04-JAN-23MDSYS SYSAUX 04-JAN-23ORDSYS USER1 04-JAN-23ORDDATAUSER1 04-JAN-23ORDPLUGINS USER1 04-JAN-23SI_INFORMTN_SCHEMA USER1 04-JAN-23OLAPSYSSYSAUX 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

到此這篇關于Oracle Users表空間重命名的文章就介紹到這了,更多相關Oracle Users表空間內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！