出现原因
对于 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 即可.