declare
sqlstatement varchar2(100);
large number;
begin
for rec in (select file_name,freebytes,bytes
from (select file_id,sum(bytes) freebytes
from dba_free_space
group by file_id) a,dba_data_files b
where a.file_id=b.file_id)
loop
large:=(rec.bytes-rec.freebytes)*1.2;
sqlstatement:='alter database datafile '||rec.file_name||' resize '||large||'M';
execute immediate sqlstatement;
end loop;
end;ORA-02236: 无效的文件名
ORA-06512: 在line 13我是想把所有文件的大小改为它现在实际大小的1.2倍,我不明白我这个动态sql到底错在哪
sqlstatement varchar2(100);
large number;
begin
for rec in (select file_name,freebytes,bytes
from (select file_id,sum(bytes) freebytes
from dba_free_space
group by file_id) a,dba_data_files b
where a.file_id=b.file_id)
loop
large:=(rec.bytes-rec.freebytes)*1.2;
sqlstatement:='alter database datafile '||rec.file_name||' resize '||large||'M';
execute immediate sqlstatement;
end loop;
end;ORA-02236: 无效的文件名
ORA-06512: 在line 13我是想把所有文件的大小改为它现在实际大小的1.2倍,我不明白我这个动态sql到底错在哪
...
sqlstatement:='alter database datafile '''||rec.file_name||''' resize '||large||'M';
...
ALTER DATABASE DATAFILE '<data_file_name>' RESIZE <n> K|M;你这一句有2处错误,应改成:
sqlstatement:='alter database datafile '''||rec.file_name
||''' resize '|| to_char(trunc(large,0)) || 'M';另外逻辑上注意一下,是否你的本意:
large:=(rec.bytes-rec.freebytes)*1.2; 这里的单位是字节,你下面改成的单位是M.