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

出现原因

对于 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;

执行结果:

查询结果中 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

执行结果:

**注意 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

执行结果:

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

缩小完后, 再次查询用户占用空间大小 (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;

可见由 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 即可.