Oracle10G下表空间监控，自动增加下月表空间，分区表增加下月表分区！供参考、讨论！...-CSDN博客

[i=s] 本帖最后由帅帅的动力于 2012-10-25 15:36 编辑

今天终于工作稍微不忙了一些，就做了对平台数据库的表空间监控！
由于每个月都要增加下月表空间和增加下月表分区，有时老忙，忘记！干脆就写个自增程序！每月25号自动执行！下面是代码，供需要的同学参考！不足地方望指教！
--调用存储过程
procedure control_tablespace_gsm as
  begin
gsm_data_other.create_view_month(to_char(sysdate,'yyyymm'),'GSM');--创建当月表空间及数据文件  视图
gsm_data_other.add_tablespace_partition(to_char(sysdate+10,'yyyymm'));
gsm_data_other.alter_table_partition;
commit;
  end;

--创建当月表空间及数据文件  视图
procedure create_view_month(dd in varchar2,flag in varchar2) as
  dd1 varchar2(20):='%'||dd||'%';
  v_flag varchar(20):='%'||flag||'%'; --v_flag  GSM/TD
  v_tb_type1 varchar2(20):='O%';
  v_tb_type2 varchar2(20):='BIN%';
  begin
--创建表空间动态视图，方便查询
if flag='GSM' then
execute immediate 'create or replace view  t_gsm_tablespace as select
b.file_id 文件ID,
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id and b.TABLESPACE_NAME like '''||dd1||''' and b.TABLESPACE_NAME like '''||v_flag||''' group by b.file_id, b.tablespace_name,b.file_name,b.bytes
order by  b.tablespace_name';
else
  execute immediate 'create or replace view  t_td_tablespace as select
b.file_id 文件ID,
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id and b.TABLESPACE_NAME like '''||dd1||''' and b.TABLESPACE_NAME like '''||v_flag||''' group by b.file_id, b.tablespace_name,b.file_name,b.bytes
order by  b.tablespace_name';
  end if;
  --创建分区表动态视图
  execute immediate 'create or replace view  t_gsm_tab_partitions as select * from user_tab_partitions t where t.partition_name like '''||dd1||''' and t.table_name not like '''||v_tb_type1||'''
               and t.table_name not like '''||v_tb_type2||''' order by t.table_name';
commit;
  end;
--每月增加下月表空间  针对分区表
procedure add_tablespace_partition(dd in varchar2) as
  v_sql varchar2(500);
  space_name varchar2(50);
  wjm varchar2(50);
  cursor space is select * from t_gsm_tablespace t;
  con int:=0;
  con1 int:=0;
  --传的参数为要建立的下月的表空间前缀  如201211
  begin
--创建表空间
for s in space loop
   space_name:=replace(s.表空间,to_char(to_number(dd)-1),dd);
   wjm:=replace(s.物理文件名,to_char(to_number(dd)-1),dd);
   select count(*) into con from dba_free_space t where t.tablespace_name=space_name;

   --表空间已经存在，则增加数据文件
   if con>0 then
   select count(*) into con1 from dba_data_files t where t.file_name=wjm;
      --如果数据文件已经存在，则说明已经建立成功，无需再创建
      if con1<=0 then
         v_sql:='alter tablespace '||space_name||' add datafile'''||wjm||'''
         size '||s.大小m||'M';
         execute immediate v_sql;
   end if;
   else
         v_sql:='create tablespace '||space_name||' datafile '''||wjm||'''
   size '||s.大小m||'M';
   execute immediate v_sql;
      end if;
   end loop;
commit;
  end;

--增加表分区
procedure alter_table_partition as
  v_sql varchar2(500);
  space_name varchar2(50);
  wjm varchar2(50);
  dd varchar2(50):=to_char(sysdate,'yyyy')||'-'||to_char(to_number(to_char(sysdate,'mm'))+2)||'-01 00:00:00';
  --dd varchar2(50):=to_char(to_date(to_char(sysdate,'yyyy-mm'),'yyyy-mm')+2,'yyyy-mm');
  sp1 varchar2(50):=to_char(to_number(to_char(sysdate,'yyyymm')));
  sp2 varchar2(50):=to_char(to_number(to_char(sysdate,'yyyymm'))+1);
  cursor tb is select * from t_gsm_tab_partitions t;
  con int;
  begin
--增加表分区
for s in tb loop
select count(*) into con from user_tab_partitions t where t.table_name=s.table_name
and t.partition_name=replace(s.partition_name,sp1,sp2);
--如果增加过，就不再增加
if con<=0 then
v_sql:='alter table '||s.table_name||' add partition '||replace(s.partition_name,sp1,sp2)||' values less than (TO_DATE('''||dd||''' , ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN''))
tablespace '||replace(s.tablespace_name,sp1,sp2)||'
pctfree 10
initrans 1
maxtrans 255
storage
(
   initial 64K
   next 1M
   minextents 1
   maxextents unlimited
)';
execute immediate v_sql;
end if;
   end loop;
--增加表分区
--commit;
  end;

完毕！用ETL做了个任务，定时执行！