[转][Oracle]数据文件自动扩展

create or replace procedure auto_add_datafile is  
ALL_file_name Varchar(500);  
file_name Varchar(500);  
tablespace_all varchar(500);
Vs_Sql Varchar2(500);  
cursor c_tablespace is   
SELECT total.tablespace_name, Round(total.MB, 2) AS Total_MB,Round(total.MB - free.MB, 2) AS Used_MB, 
       Round(( 1 - free.MB / total.MB ) * 100, 2) AS Used_Pct  
FROM (SELECT tablespace_name, Sum(bytes)/1024/1024 AS MB FROM dba_free_space GROUP BY tablespace_name) free,   
(SELECT tablespace_name,Sum(bytes) / 1024 / 1024 AS MB FROM dba_data_files GROUP BY tablespace_name) total  
WHERE  free.tablespace_name = total.tablespace_name 
       AND free.tablespace_name <> ‘EXAMPLE‘ 
       and free.tablespace_name <> ‘SYSTEM‘ 
       AND free.tablespace_name <> ‘SYSAUX‘ 
       --AND free.tablespace_name <> ‘USERS‘ 
       AND free.tablespace_name NOT LIKE ‘UNDOTBS%‘;  
Begin  
  for tablespace_all in c_tablespace loop  
        If tablespace_all.USED_PCT >=90 Then  
            ALL_file_name := ‘c:\oracle\oradata\数据库DB\‘ || tablespace_all.tablespace_name;  
            ALL_file_name := ALL_file_name||‘_‘||to_char(sysdate,‘yyyymmddhh24‘)||‘.dbf‘;  
            Vs_Sql := ‘alter tablespace "‘||tablespace_all.tablespace_name||‘" add datafile ‘‘‘||ALL_file_name||‘‘‘ size 100m autoextend on next 100m MAXSIZE UNLIMITED‘;              
            --dbms_output.put_line(Vs_Sql);  
            Execute Immediate Vs_Sql;  
       End If;  
  end loop;  
exception  
  when others then  
    dbms_output.put_line(sqlerrm);  
End auto_add_datafile;  

 添加定时执行（每天1点时）

SQL> variable jobid number;  
SQL> exec dbms_job.submit(:jobid,‘auto_add_datafile;‘,sysdate, ‘TRUNC(sysdate) + 1 +1/ (24)‘);  
--每天凌晨1点执行。  
SQL> exec dbms_job.run(:jobid); 

 如果需要手动执行，可以在 PL/SQL 里执行：

begin
   auto_add_datafile;
end;

[转][Oracle]数据文件自动扩展

标签：color   div   select   ada   arch   文件   art   procedure   toe