Oracle 批量将表从SYSTEM表空间中移出

生有可恋 2022-08-04

1443

在安全检查中经常会检查违反常规的一些行为，比如默认表空间为SYSTEM表空间，以及把用户表建到了SYSTEM表空间中。

这两个问题实际上是一个问题，正是因为创建用户时默认表空间设置为SYSTEM表空间，所以后期创建表时才会把数据表创建在SYSTEM表空间中。

此时就需要把默认表空间改为用户表空间，同时把表从SYSTEM表空间中移出来。这里需要用到两个语句：

-- 重新设置用户的默认表空间
alter user NEWUSER default tablespace NEWTS;
-- 将表从SYSTEM表空间移出至新的表空间
alter table NEWUSER.USERSTABLE move tablespace NEWTS;

下面是批量操作需要做的一些准备，比如创建目的表空间，所需要用到的空间容量测算。

准备工作，创建新的表空间用来存放需要迁移的表：

SQL> show parameter db_create_file_dest
NAME                    TYPE        VALUE
-------------------  ----------- ---------------
db_create_file_dest    string      +HISDATA


SQL> create tablespace itdata;
SQL> select t.file_name, t.file_id, t.bytes, t.autoextensible from dba_data_files t where t.tablespace_name = 'ITDATA';

当使用OMF管理数据文件时，在创建表空间时不需要指定文件位置以及表空间大小，默认创建的表空间大小为100M，当需要时可以进行扩容。

在迁移前，先测算一下需要多少空间：

SELECT SUM(t.bytes)  1024  1024  as MB
  FROM dba_segments t
 WHERE t.owner NOT IN ('SYS', 'SYSTEM')
   AND t.tablespace_name IN ('SYSTEM');

然后对数据文件进行扩容：

SQL> alter database datafile 510 resize 6000M;

生成批量变更默认表空间的语句：

SELECT 'alter user ' || t.username || ' default tablespace ITDATA;' AS sqltext
  FROM dba_users t
 WHERE t.default_tablespace = 'SYSTEM'
   AND t.username NOT IN ('SYS', 'SYSTEM');

然后批量执行：

alter user CDSSFORHUIMEI default tablespace ITDATA;
alter user EM default tablespace ITDATA;
alter user "MJ-LIS" default tablespace ITDATA;
alter user TJCK default tablespace ITDATA;
alter user TSJF default tablespace ITDATA;
alter user XYTX_DATA default tablespace ITDATA;
alter user ANSHU default tablespace ITDATA;
alter user ZDBM default tablespace ITDATA;
alter user ZY_QUEUE default tablespace ITDATA;
alter user PACSINTERFACE default tablespace ITDATA;
alter user FOR_WXSPD default tablespace ITDATA;
alter user DBAAS_SYS default tablespace ITDATA;
alter user LIS_TGJ default tablespace ITDATA;

如果用户名带特殊字符，比如 "MJ-LIS" 需要用双引号引起来。

再生成需要迁移表的语句：

SELECT 'alter table ' || t.owner || '.' || t.segment_name ||
       ' move tablespace ITDATA;' AS sqltext,
       t.bytes  1024  1024 AS mb
  FROM dba_segments t
 WHERE t.owner NOT IN ('SYS', 'SYSTEM')
   AND t.tablespace_name = 'SYSTEM'
   AND t.segment_type = 'TABLE'
   AND t.owner NOT IN ('OUTLN', 'PDBADMIN');

然后批量执行：

alter table XYHIS.MET_IPM_HYPOTEST move tablespace ITDATA;
alter table XYHIS.EMR_CALENDAR move tablespace ITDATA;
alter table XYHIS.AIS_PATIENT_AREA move tablespace ITDATA;
alter table XYHIS.AIS_RESULT_MANUAL move tablespace ITDATA;
alter table XYHIS.AIS_MENU move tablespace ITDATA;
alter table XYHIS.AIS_ANESTHESIA_ARRANGEMENT move tablespace ITDATA;
alter table XYHIS.AIS_ANES_GROUP move tablespace ITDATA;
alter table XYHIS.AIS_ROLE move tablespace ITDATA;
alter table XYHIS.AIS_ROLE_MENU move tablespace ITDATA;
alter table XYHIS.AIS_ROLE_OPERATOR move tablespace ITDATA;
alter table XYHIS.MET_NUI_CIRCUIT move tablespace ITDATA;

需要注意的是，如果表中有 LONG 字段，在迁移时会报错。

此时需要使用导入、导出来迁移表，原因是 LONG 字段是早期的数据结构，在 11g 之后不推荐使用。在使用 move tablespace 操作时不支持这种操作。

全文完。

如果转发本文，文末务必注明：“转自微信公众号：生有可恋”。

文章转载自生有可恋，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 批量将表从SYSTEM表空间中移出

评论