Oracle 表存储位置迁移

生有可恋 2021-09-28

680

开发把业务表存到USERS表空间去了，现在要将表从USERS表空间迁移至其它表空间。在工作中经常见到用户把表建到SYSTEM表空间或USERS表空间，这是因为创建用户时没有指定默认表空间，建议不要把生产数据放在SYSTEM表空间或USERS表空间。

迁移前我们要对表信息做些收集

表的规模（行数，大小）
表中是否有LOB字段
是否是分区表
索引信息
当前表空间和目标表空间的可用空闲空间

先查看表的定义

-- Create table
create table XYHIS.MET_MRS_BASE_IMAGE
(
  inpatient_no VARCHAR2(20),
  page_no      VARCHAR2(5),
  base_image   BLOB
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table XYHIS.MET_MRS_BASE_IMAGE
  is '病案首页图片存储表';
-- Add comments to the columns 
comment on column XYHIS.MET_MRS_BASE_IMAGE.inpatient_no
  is '住院流水号';
comment on column XYHIS.MET_MRS_BASE_IMAGE.page_no
  is '首页页码';
comment on column XYHIS.MET_MRS_BASE_IMAGE.base_image
  is '首页图片';
-- Create/Recreate check constraints 
alter table XYHIS.MET_MRS_BASE_IMAGE
  add check ("INPATIENT_NO" IS NOT NULL)
  disable
  novalidate;

XYHIS.MET_MRS_BASE_IMAGE 这个表所使用的表空间是USERS表空间，有一个BLOB字段。我们再查一下LOB字段的信息。

select t.table_name,
       t.column_name,
       t.segment_name,
       t.tablespace_name,
       t.index_name,
       t.partitioned
  from dba_lobs t
 where t.table_name = 'MET_MRS_BASE_IMAGE'
   AND T.owner = 'XYHIS';

LOB字段也存放在USERS表空间，并且LOB字段有个索引，表没有采用分区

。同时我们也查看一下LOB字段的索引信息。

select t.index_name,
       t.index_type,
       t.status,
       t.partitioned,
       t.table_name,
       t.tablespace_name
  from DBA_INDEXES T
 WHERE T.index_name = 'SYS_IL0000118138C00003$$';

select count(*) from xyhis.met_mrs_base_image t;

表以及LOB字段的关系我们梳理完了之后，我们再对表的规模做个统计，表有5000多行，规模不大。LOB字段和表所占的空间要单独统计，统计表的大小并不包含LOB字段。

select t.segment_name,
       t.segment_type,
       t.bytes  (1024 * 1024 * 1024) BYTES_G,
       t.tablespace_name
  from dba_segments t
 where t.segment_name in ('SYS_LOB0000118138C00003$$',
                          'SYS_IL0000118138C00003$$',
                          'MET_MRS_BASE_IMAGE')
   and t.owner = 'XYHIS';

我们统计了表的大小、与之相关的LOB字段的大小及索引的大小

迁移前我们记录一下表空间的大小

select t.tablespace_name,
       round(sum(t.bytes)  (1024 * 1024 * 1024), 2) || 'G' 实际大小,
       round(sum(t.maxbytes)  (1024 * 1024 * 1024)) || 'G' as 最大值
  from dba_data_files t
 WHERE T.tablespace_name = 'HISDATA'
    or t.tablespace_name = 'USERS'
 group by t.tablespace_name;

下一步开始将表、LOB字段由原USERS表空间移至HISDATA目标表空间。

--表的迁移：
alter table XYHIS.MET_MRS_BASE_IMAGE move tablespace HISDATA;

表的数据量较小，不到1秒就迁移完成。LOB字段有1.1G，稍微有点大。

--LOB字段
alter table XYHIS.MET_MRS_BASE_IMAGE move lob(BASE_IMAGE) store as(tablespace HISDATA);

实际执行时间为36.853秒，比测试库上执行的4秒多了近9倍的时长。像表空间级别的表的挪动，还是应该选择业务低峰时执行。

执行完后，索引自动移到了目的表空间。

最后再检查一下索引状态

select t.index_name,
       t.index_type,
       t.status,
       t.partitioned,
       t.table_name,
       t.tablespace_name
  from DBA_INDEXES T
 WHERE T.index_name = 'SYS_IL0000118138C00003$$';

oracle

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

Oracle 表存储位置迁移

评论