[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做了个任务,定时执行!
未命名.jpg!这里不会ETL的同学,可以用数据库Job来做!效果一样!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26940217/viewspace-747208/,如需转载,请注明出处,否则将追究法律责任。