数据文件自动扩展

的问题

 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   

查看更多关于数据文件自动扩展的详细内容...