如何缩小oracle system表空间

Question

   急求，我是oracle新手，之前将表全部存储到system表空间，并且扩大到30G多，但实际只用了600m多，现在我删除了一些以前用的表，想把自己添加的表全部删除，但是好多表分不清楚了，请教如何解决这个问题？还有就是缩小system表空间，达到充分利用资源的目的？                                       
                                                                                有谁能解决这个问题，我会感到务必膜拜，阐述时希望给到具体方法？

Answer 1

表没删除之前有30g多，删除之后没有太大变化，通过查询空间利用才600M多 ，现在我想把表空间缩小1g左右

Answer 2

报ora-03297:文件包含在请求的RESIZE 值以外使用的数据 ，这个方法解决不了，还有其他什么方法能解决

Answer 3

你先要把数据文件缩小，应该要先压缩segment 所占的空间，然后再resize数据文件通过把表drop 或者 truncate 都能达到压缩segment 降低高水位的效果.

Answer 4

1.将system表空间里的非系统表drop 或者 truncate；2.将system表空间里的非系统表move到其他表空间里去。
最后再resize

Answer 5

怀疑有些表产生了许多碎片，造成resize不成功。
而比较头痛的是，system表空间，有许多系统表，全部迁移到另一表空间、再迁移回来的方式，可能会遇到些问题。建议，先对所有表进行一次整理，再试下resize。

Answer 6

请问要如何清理system表空间而不会影响system里的表，用resize时 是否在高水位线以上就可以？但是我才用600M多呀？

Answer 7

你用了600M，但这600M是分散的，不是连续的。你resize数据时缩小数据文件，在缩小的数据文件的那部分不能有数据段存在。
所以，你把所有非系统的表drop掉后，然后测试的resize数据文件，你这数据文件有30G
你可以先resize 到15G，如果可以执行，在resize 到10G，直到提示不能resize。
如果最终还是太大，你就要考虑重新建库了，通过exp/imp导出用户数据，重新建库后在imp数据。

Answer 8

你drop掉表时候加上purge，oracle 10g以后有个recyclebin机制，如果只是drop的话，高水位线不变的，你把system的数据文件resize一下应该就可以了

Answer 9

你可以查询到你system数据文件中的高水位的位置
通过dba_extents 表
select max(block_id)*8/1024 'M size'from dba_extents where tablespace_name='SYSTEM'

Answer 10

一般最好不要把自己创建的对象放到system表空间的，一旦把system表空间撑满是比较麻烦的

Answer 11

你先用shrink命令对表进行缩小，降低HWM。
select  ds.tablespace_name,'alter table '||ds.owner||'.'||ds.segment_name||' shrink space;'
from dba_segments ds 
where ds.tablespace_name='system（你的表空间名为system）' and ds.owner='system（这里是你的用户名）'
  and ds.segment_type = 'TABLE';
查询后你可以看到自己那些表是自己当初建的可以用此命令
alter table 你的表名字 shrink space;来降低此表的HWM，之后便可通过resize命令来缩小你的表空间，把文件从1g缩小到600M。
也可以在shrink后用我这段代码来实现自动缩小表空间的脚本：
SET SERVEROUTPUT ON;
declare
e VARCHAR(500);
d int;
f int;
g VARCHAR(200);
    cursor c_logfile is
        select 
         a.file_name,
          a.filesize,c.hwmsize
        from  
        (
        select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files
        ) a,
        (
        select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents
        group by file_id) c
        where a.file_id = c.file_id
          and a.filesize - c.hwmsize > 100;begin
    open c_logfile;
     LOOP
   fetch c_logfile into e,d,f;
   Exit when c_logfile%NOTFOUND;
   g:= ('alter database datafile '||'''' ||e||'''' || ' resize '||round(d-(d-f-100)*0.95)||'M');
   dbms_output.put_line(g); 
   execute immediate(g); 
    end loop;
   close c_logfile;
end;
/
我也是开始学oracle 这些问题我都遇到过，自己也是查过好多资料才实现的，祝你成功