oracle删除数据后表空间仍过大问题解决方法
-----亲测有效-------
--一、备份原始数据库库
--1、备份空表
--在plsql里面执行一下这句话 然后把结果集 再执行一把 再导数据
select 'alter table '||table_name||' allocate extent(size 64k);' from tabs t
where not exists (select segment_name from user_segments s where s.segment_name=t.table_name);
2、在cmd窗口(以管理员身份运行),执行如下备份数据库语句
exp userid=abc/abc@landdata222 file=D:\数据库备份\abc20170622.dmp log=D:\数据库备份\abc20170622.log
--二、降低每个表占用的大小
--在plsql命令窗口执行如下语句:
set heading off;
set echo off;
set feedback off;
set termout on;
spool C:\allocate.sql;
select 'ALTER TABLE ' || owner || '.' || table_name || ' MOVE TABLESPACE ' ||
tablespace_name || ' STORAGE(INITIAL 64K NEXT 1M);'
from dba_tables
where owner = 'abc'
and initial_extent > 1048576;
spool off;
--打开上一步的成果文件C:\allocate.sql,在plsql中执行
--由于以上过程虽然减少了表占用的大小,但是对应的索引和表空间大小没有减少,需要备份数据后重新还原即可解决问题。
三 、再次备份数据库
--1、备份空表
--在plsql里面执行一下这句话 然后把结果集 再执行一把 再导数据
select 'alter table '||table_name||' allocate extent(size 64k);' from tabs t
where not exists (select segment_name from user_segments s where s.segment_name=t.table_name);
2、在cmd窗口(以管理员身份运行),执行如下备份数据库语句
exp userid=abc/abc@landdata222 file=D:\数据库备份\abc20170622jianshao.dmp log=D:\数据库备份\abc20170622jianshao.log
四、还原第三步备份的数据库文件
1、在plsql中删掉用户abc、对应的表空间abc
a删除用户:在abc用户右键删除
b删除表空间:右键删除
2、创建新的表空间和用户
--创建表空间,用sys用户登录创建
Create tablespace abc datafile
'C:\Soft\app\lenovo\oradata\landdata222\abc.DBF'size 100m autoextend on next 100M maxsize 32000M;
(如果提示如下图,则在对应路径下先删除数据文件
)
---创建用户,权限包括dba、resource、connect
在plsql——users,右键“新建”
3、还原数据库
在cmd窗口(以管理员身份运行),执行如下还原数据库语句
imp userid=abc/abc@landdata222 file=D:\数据库备份\abc20170622jianshao.dmp fromuser=abc touser=abc log=D:\数据库备份\abc20170622huanyuan.log
(对于特殊自定义命名的主键,还原的时候发现还是会有问题,日志报错,需要删除索引后重建主键。根据日志内容重建即可。)
四、释放表空间大小
----释放表空间大小-------
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 5
--上述查询语句最后一个字段的内容复制出来执行一次