17,377
社区成员
发帖
与我相关
我的任务
分享
declare
cursor c_logfile is
select /*+ ordered use_hash(a,c) */
'alter database datafile '''||a.file_name||''' resize '
||round(a.filesize - (a.filesize - c.hwmsize-100) *0.8)||'M;' as sql_text,
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
for cmd in c_logfile
loop
execute immediate cmd.sql_text;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/