ORACLE 自动扩充表空间

ORACLE 自动扩充表空间
create or replace procedure proc_autoextend_tbs
as
log_file1 utl_file.file_type;
cursor cur_tbs is
SELECT tablespace_name, SUM(bytes / 1024 / 1024 / 1024) as total_space,SUM(maxbytes/1024/1024/1024) as total_maxbytes
FROM dba_data_files
GROUP BY tablespace_name;
v_tbs v$datafile.name%type;
v_ratio number;
v_maxbytes number;
v_str varchar2(500);
v_date varchar2(20);
begin
open cur_tbs;
loop
fetch cur_tbs
into v_tbs, v_ratio, v_maxbytes;
exit when cur_tbs%notfound;
if v_ratio >= v_maxbytes - 1 then
select substr(file_name,0,instr(file_name,’’,-1,1)) || tablespace_name into v_str from dba_data_files WHERE tablespace_name = v_tbs and rownum=1 order by file_id;
select to_char(sysdate, ‘yyyymmdd’ ) into v_date from dual;
execute immediate ‘alter tablespace ’ || v_tbs || ’ add datafile’||’’’’|| v_str ||’’’’|| v_date || ‘.dbf’||’’’’||’ size 500m ’ ||
‘AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED’;
dbms_output.put_line(v_tbs || ’ ’ || v_ratio ||
’ free ratio is low,please add space’);
log_file1 := utl_file.fopen(‘DIR_TBS’, ‘test_log.txt’, ‘a’);
utl_file.new_line(log_file1, 1);
utl_file.put(log_file1,
‘tablespace ’ || v_tbs || ’ already extend space’);
utl_file.fclose(log_file1);
end if;
end loop;
close cur_tbs;
end;