一旦数据块大小为8k的数据文件达到32g将无法再拓展,我们就需要手动添加数据文件,很麻烦。所以,下面介绍自动为表空间添加数据文件的存储过程,并将其加入到job中定时执行。
存储过程:
<span style="font-size:18px;">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.tab