oracle 10g 自动分区,Oracle10G下表空间监控，自动增加下月表空间，分区表增加下月表分区！供参考、讨论！...

[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/，如需转载，请注明出处，否则将追究法律责任。