解决 Oracle 导入导出占用大量表空间的问题

一份执着✘

发布于 2020-02-17 10:59:32

1.5K0

发布于 2020-02-17 10:59:32

出现原因

对于 Oracle 而言, 每个表建立是, 都有一个属性为 initial, 表示此表占用的空间大小, 随着数据的新增, 此值也会一直增大, 但删除这个表的数据后, initial 也不会缩小. 所以使用时间越长, 每个表占用的空间都会很大.

且导出后, 其他机器再次导入, 也会占用其同样的 initial 大小.

解决过程

注意: 进行以下操作前, 要先对数据进行备份. 以防出错!

查询每个用户占用空间大小 (dba 用户执行)

select owner, sum(bytes / 1024 / 1024)  "MB", sum(bytes / 1024 / 1024 / 1024)  "GB"  from dba_segments group by owner order by GB DESC;

执行结果:

查询结果中 OWNER 表示用户, 后两列表示占用空间大小, 根据上图所知, DSP2 这个用户, 占用了 18.6GB 的空间. 那么这个用户导出数据后, 其他电脑导入, 也会占用 18.6GB 空间.

缩小表 initial 空间 (dba 用户执行)

select 'ALTER TABLE '||owner||'.'||table_name||' MOVE TABLESPACE '||tablespace_name||' STORAGE(INITIAL 64K NEXT 32K);' 
  from dba_tables 
 where owner='DSP2' and initial_extent>65536

执行结果:

注意 where owner='DSP2' 要中的用户改为你要缩小表空间的用户.

重建索引 (要导出的用户执行)

这里我切换到 DSP2 用户, 由于缩小表 initial 空间后, 索引会失效, 需要重建索引.

declare
  cursor c is 
    select index_name, owner 
    from dba_indexes 
    where status='UNUSABLE';
  
  owner dba_indexes.owner%type;
  index_name dba_indexes.index_name%type;
begin
  open c;
  loop 
       fetch c into index_name, owner; 
       exit when c%notfound;
       
       execute immediate 'alter index ' || owner || '.' || index_name || ' rebuild';
  end loop;
  close c;
end;

缩小表空间 (dba 用户执行)

查询表空间占用大小和可缩小大小:

select a.file#,
       a.name,
       a.bytes / 1024 / 1024 CurrentMB,
       ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
       (a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
       'alter database datafile ''' || a.name || ''' resize ' ||
       ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD
  from v$datafile a,
       (select file_id, max(block_id + blocks - 1) HWM
          from dba_extents
         group by file_id) b
 where a.file# = b.file_id(+)
   and (a.bytes - HWM * block_size) > 0
 order by CURRENTMB DESC

执行结果:

执行最后一列的命令，进行缩小表空间.

缩小完后, 再次查询用户占用空间大小 (dba 用户执行)

select owner, sum(bytes / 1024 / 1024)  "MB", sum(bytes / 1024 / 1024 / 1024)  "GB"  from dba_segments group by owner order by GB DESC;

可见由 18G 占用, 缩小到了 0.2G. 此时这个用户再导出的数据文件, 其他电脑再导入, 就只会占用 0.2G 空间, 而不是 18GB.

彻底清理空间

上述操作, 只能缩小这个用户部分占用空间, 仅能用于再次导出后, 导入时不会占用大量空间.

如想彻底清空表空间, 可以导出数据后, 删除此用户, 再执行清理命令, 然后再次建立用户导入即可.

一定要注意先备份数据, 且确定导出的数据无误, 再进行此操作.

删除用户

drop user 用户名 cascade;

查询表空间占用大小和可缩小大小

select a.file#,
       a.name,
       a.bytes / 1024 / 1024 CurrentMB,
       ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
       (a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
       'alter database datafile ''' || a.name || ''' resize ' ||
       ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD
  from v$datafile a,
       (select file_id, max(block_id + blocks - 1) HWM
          from dba_extents
         group by file_id) b
 where a.file# = b.file_id(+)
   and (a.bytes - HWM * block_size) > 0
 order by CURRENTMB DESC

操作步骤与上方一致. 执行最后一列的缩小表空间的 SQL 即可.

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

原始发表：2020-02-04，如有侵权请联系 cloudcommunity@tencent.com 删除

其他

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

其他

登录后参与评论

0 条评论

热度