2014-07-15 清理数据并回收数据文件空间

select count(1) from etlmon.E_L_FR_FILES t where t.starttime < to_date('2014-01-01','yyyy-mm-dd hh24:mi:ss');
17129587

select count(1) from etlmon.E_L_TASK t where t.start_time < to_date('2014-01-01','yyyy-mm-dd hh24:mi:ss');
6785992

--清理数据
declare
  TYPE t_rowid IS TABLE OF rowid INDEX BY BINARY_INTEGER;
  v_rowid t_rowid;

  cursor c_cur is
    select rowid
      from etlmon.E_L_FR_FILES t
     where t.starttime < to_date('2014-01-01', 'yyyy-mm-dd hh24:mi:ss');

begin
  open c_cur;
  loop
    fetch c_cur bulk collect
      into v_rowid limit 5000;
    forall i in v_rowid.first .. v_rowid.last
    
      delete etlmon.E_L_FR_FILES where rowid = v_rowid(i);
    commit;
 
    exit when c_cur%notfound;
  end loop;
  close c_cur;

  commit;
end;
/


declare
  TYPE t_rowid IS TABLE OF rowid INDEX BY BINARY_INTEGER;
  v_rowid t_rowid;

  cursor c_cur is
    select rowid
      from etlmon.E_L_TASK t
     where t.start_time < to_date('2014-01-01', 'yyyy-mm-dd hh24:mi:ss');

begin
  open c_cur;
  loop
    fetch c_cur bulk collect
      into v_rowid limit 5000;
    forall i in v_rowid.first .. v_rowid.last
    
      delete etlmon.E_L_TASK where rowid = v_rowid(i);
    commit;
 
    exit when c_cur%notfound;
  end loop;
  close c_cur;

  commit;
end;
/

--回收空间
alter tablespace TBS_ETL_WEB coalesce;

--生成更新数据文件大小的语句
select 'alter database datafile ''' || a.file_name || ''' resize ' ||
round(a.filesize - (a.filesize - c.hwmsize - 100) * 0.8) || 'M;',
a.filesize || 'M' as "数据文件的总大小",
c.hwmsize || 'M' as "数据文件的实用大小"
from (select file_id, file_name, round(bytes / 1024 / 1024) as filesize
from dba_data_files) a,
(select file_id, round(max(block_id) * 8 / 1024) as HWMsize
from dba_extents
group by file_id) c
where a.file_id = c.file_id
and a.filesize - c.hwmsize > 100;

       'ALTERDATABASEDATAFILE'''||A.F    数据文件的总大小    数据文件的实用大小
1    alter database datafile '/opt/oracle/oradata/orcl/system01.dbf' resize 974M;    1000M    868M
2    alter database datafile '/home/oracle/oradata/etl/TBS_ETL_WEB04.dbf' resize 1168M;    4096M    336M
3    alter database datafile '/home/oracle/oradata/etl/TBS_ETL_CC.dbf' resize 413M;    1000M    166M
4    alter database datafile '/opt/oracle/oradata/orcl/sysaux01.dbf' resize 538M;    1000M    323M
5    alter database datafile '/home/oracle/oradata/etl/TBS_ETL_META.dbf' resize 291M;    1000M    14M

alter database datafile '/opt/oracle/oradata/orcl/system01.dbf' resize 974M;
alter database datafile '/home/oracle/oradata/etl/TBS_ETL_CC.dbf' resize 413M;
alter database datafile '/opt/oracle/oradata/orcl/sysaux01.dbf' resize 538M;
alter database datafile '/home/oracle/oradata/etl/TBS_ETL_META.dbf' resize 291M;
alter database datafile '/opt/oracle/oradata/orcl/users01.dbf' resize 100M;


oracle@ZJHZ-PS-CMREAD-BILLETL01-SV-BUS-SD:~> df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda2             10325780    548908   9252352   6% /
udev                  12303592       148  12303444   1% /dev
/dev/sda5              1035660     72840    910212   8% /boot
/dev/sda9             78446016  48748300  25712796  66% /home
/dev/sda10            15488716  12388948   2312988  85% /opt
/dev/sda8              5162796    132248   4768292   3% /tmp
/dev/sda6              8262036   2160040   5682300  28% /usr
/dev/sda7              5162796   3354716   1545824  69% /var
shm                    8388608    569384   7819224   7% /dev/shm

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/692830/viewspace-1219083/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/692830/viewspace-1219083/