4

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

 3 years ago
source link: http://www.zhaojun.im/oracle-tablespace-shrink/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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

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

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

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

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

执行结果:

loading2.gif

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

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

1
2
3
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

执行结果:

loading2.gif

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 用户执行)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

执行结果:

loading2.gif

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

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

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

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

彻底清理空间

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

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

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

1
drop user 用户名 cascade;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 即可.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK