path: root/sql/procedures20141031.sql

---------------------------------------------------
-- Export file for user NMS                      --
-- Created by dongxiaoyan on 2014/10/31, 9:17:28 --
---------------------------------------------------

spool procedures.log

prompt
prompt Creating procedure PRO_ADD_PART_ONE_DAY
prompt =======================================
prompt
CREATE OR REPLACE PROCEDURE pro_add_part_one_day(v_table_name IN VARCHAR2,v_par_sequence IN VARCHAR2)
Authid Current_User----ʹ�洢���̿���ʹ��roleȨ��
AS
-------------------------------------------------------------------------------------------------------------
--�˹��̵Ĺ���
--     ���ݴ��ݵIJ����������ƶ��ı��ռ䣬�Զ����ӷ��������죩
--��ǰ�汾��1. 0
--��         �ߣ�hyx
--��         �ڣ�2013.10.31
---
-------------------------------------------------------------------------------------------------------------
---������
   --���ռ���= ���ռ���ǰ׺ + ���� + _ + ����
   c_table_name_prefix      CONSTANT VARCHAR2(100) :=''; --��Ҫ���ã�������Ϊ�գ��񳤶ȳ���������ƣ����ռ���ǰ׺��ע�⣺һ��Ҫ��д��
   c_oracle_data_dir             CONSTANT VARCHAR2(200) :='/data/d4/oradata/ict/nmspartition/'; --���ռ��ļ���ŵ�·��
   c_tablespace_init_size        CONSTANT VARCHAR2(10) :='10M'; --���ռ��ʼ����С
   c_tablespace_extend_size      CONSTANT VARCHAR2(10) :='5M'; --���ռ�ÿ���Զ�������С
   c_log_table_name              CONSTANT VARCHAR2(100) :='PRO_EXEC_LOG'; --��־��¼�ڴ˱���
   c_cur_pro_name                CONSTANT VARCHAR2(100) :='pro_add_part_one_day'; --��ǰ�洢���̵�����
---
-------------------------------------------------------------------------------------------------------------
   v_log_table_sql               VARCHAR2(2000);----��¼��־sql���
---
   v_last_tablespace_name_sql    VARCHAR2(2000);----��ȡ��ر���������ı��ռ���sql���
   v_last_tablespace_name        VARCHAR2(200);----��ر���������ı��ռ���
---
   v_tablespace_prefix_length    NUMBER;----��ر���������ı��ռ�����ȥ�����ڣ�����
---
   v_last_tablespace_name_date   NUMBER; --��ر���������ı��ռ�������
   v_next_tablespace_name_date   NUMBER; --�����ӱ��ռ�����
   v_create_tablespace_sql       VARCHAR2(2000); --������һ�����ռ��sql���
   v_partition_time_date         NUMBER; ----ȡ������Χֵ
   v_add_partition_sql           VARCHAR2(2000); --���ӱ���һ������sql���
   v_sub_table_name              VARCHAR2(200);--��������21ʱ����ȡ���������

---
-------------------------------------------------------------------------------------------------------------
BEGIN
   ----------------------------------------------------------
    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'1'',''1.�ж�'||v_table_name||'���������Ƿ����21��������ڣ���Ӻ���ǰ��ȡ21���ַ� begin'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;

    --�жϱ��������Ƿ����21��������ڣ���Ӻ���ǰ��ȡ21���ַ�:��Ϊ֮ǰ����ı��г��ȳ���21�ģ�22+9=31��������󳤶���
    v_sub_table_name := v_table_name;
    if length(v_table_name)>21 then
        v_sub_table_name := substr(v_table_name,-21);
    end if;

    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'2'',''2.��ȡǰ������'||v_table_name||',��ȡ�������'||v_sub_table_name||' end'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;
    ----------------------------------------------------------
    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'3'',''3.��ȡ'||v_table_name||'����������ı��ռ��� begin'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;

    --��ȡ����������ı��ռ���
    v_last_tablespace_name_sql := 'select name from (select * from v$tablespace where name like''%'||c_table_name_prefix||v_sub_table_name||'_2%'''||'order by name desc ) where rownum<2';
    execute immediate v_last_tablespace_name_sql into v_last_tablespace_name;

    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'4'',''4.��ȡ'||v_table_name||'����������ı��ռ��� end'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;
    ----------------------------------------------------------
    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'5'',''5.��ȡ'||v_table_name||'����������ı��ռ���ǰ׺����+1 begin'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;

    --��ȡ����������ı��ռ���ǰ׺����+1
    v_tablespace_prefix_length := LENGTH(CONCAT(c_table_name_prefix,v_sub_table_name)) + 2 ;

    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'6'',''6.��ȡ'||v_table_name||'����������ı��ռ���ǰ׺����+1 end'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;
    ----------------------------------------------------------
    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'7'',''7.��ȡ'||v_table_name||'����������ı��ռ������ڣ�ȥ�����ռ�����begin'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;

    --��ȡ����������ı��ռ������ڣ�ȥ�����ռ�����
    v_last_tablespace_name_date := to_number(substr(v_last_tablespace_name, v_tablespace_prefix_length, 8));
    --v_last_tablespace_name_date := to_number('20131106');--ָ��֮ǰ���������µ�����

    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'8'',''8.��ȡ'||v_table_name||'����������ı��ռ������ڣ�ȥ�����ռ�����:'||v_last_tablespace_name_date||' end'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;
    ----------------------------------------------------------
    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'9'',''9.��ȡ'||v_table_name||'�������ӱ��ռ����� begin'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;

    --�����ӱ��ռ�����
    v_next_tablespace_name_date := to_char(to_date(v_last_tablespace_name_date,'yyyymmdd') + '1','yyyymmdd');

    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'10'',''10.��ȡ'||v_table_name||'�������ӱ��ռ����� end'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;
    ----------------------------------------------------------
    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'11'',''11.Ϊ'||v_table_name||'���������ռ� begin'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;

    --�������ռ�    
    v_create_tablespace_sql := 'CREATE BIGFILE TABLESPACE '||c_table_name_prefix||v_sub_table_name||'_'||v_next_tablespace_name_date||' DATAFILE'||''''||c_oracle_data_dir||c_table_name_prefix||v_sub_table_name||'_'||v_next_tablespace_name_date||'.dbf'||''''||' SIZE '||c_tablespace_init_size||' AUTOEXTEND ON NEXT '||c_tablespace_extend_size||' MAXSIZE UNLIMITED';
    execute immediate v_create_tablespace_sql;

    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'12'',''12.Ϊ'||v_table_name||'���������ռ�:'||c_table_name_prefix||v_sub_table_name||'_'||v_next_tablespace_name_date||' end'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;
    ----------------------------------------------------------
    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'13'',''13.'||v_table_name||'�� ��ȡ��һ��ʱ�������Ӧ��ʱ�� begin'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;

    --��ȡ��һ��ʱ�������Ӧ��ʱ��
    v_partition_time_date := to_char(to_date(v_last_tablespace_name_date,'yyyymmdd') + '2','yyyymmdd');

    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'14'',''14.'||v_table_name||'�� ��ȡ��һ��ʱ�������Ӧ��ʱ�� end'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;
    ----------------------------------------------------------
    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'15'',''15.�ѱ��������ӵ�'||v_table_name||'�� begin'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;

    --�ѱ��������ӵ���ر�
    v_add_partition_sql := 'ALTER TABLE '||v_table_name||' ADD PARTITION '||v_sub_table_name||'_' ||
                 v_next_tablespace_name_date || ' values less than(' ||
                 'to_date('''||v_partition_time_date||''','||''''||'yyyymmdd'||''''||')'|| ') TABLESPACE '||c_table_name_prefix||v_sub_table_name||'_'||v_next_tablespace_name_date;
    execute immediate v_add_partition_sql;

    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_par_sequence||'16'',''16.�ѱ�����'||v_sub_table_name||'_' ||v_next_tablespace_name_date||'���ӵ�'||v_table_name||'�� end'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;
    ----------------------------------------------------------
EXCEPTION
    --�쳣�������ƣ���¼��ص�ora������Լ�����쳣��Ϣ
    WHEN OTHERS THEN
    BEGIN
         ROLLBACK;
         v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''-1'',''error:'||SQLERRM||''')';
         EXECUTE IMMEDIATE v_log_table_sql;
         COMMIT;
         --ROLLBACK;
    END;

END;
/

prompt
prompt Creating procedure PRO_CREATEINDEX
prompt ==================================
prompt
create or replace procedure pro_createIndex(tabName IN VARCHAR2,fileds IN VARCHAR2) is
--- 
     c_log_table_name              CONSTANT VARCHAR2(100) :='PRO_EXEC_LOG'; --��־��¼�ڴ˱���
     c_cur_pro_name                CONSTANT VARCHAR2(100) :='pro_createIndex'; --��ǰ�洢���̵�����
---
     v_log_table_sql               VARCHAR2(5000);
     sposition1                    INTEGER;
     sposition2                    INTEGER;
     mysubstr                      VARCHAR2(100);
     starttimes                    INTEGER;
     splitStr                      VARCHAR2(10);
     v_sql1                        VARCHAR2(100);
begin
        starttimes := 1;
        sposition1 := 1;
        splitStr := ':';
        loop
            sposition2 := instr(fileds, splitStr, 1, starttimes);
            mysubstr   := substr(fileds, sposition1, sposition2 - sposition1);
            
            if mysubstr is null
            then
               exit;
            end if;
            
            v_sql1:='create index '||tabName||'_'||starttimes ||' on '||tabName||'('||mysubstr||') local';
            EXECUTE IMMEDIATE v_sql1;
            
            sposition1 := sposition2 + 1;
            starttimes := starttimes + 1;
            dbms_output.put_line(mysubstr); 
            
            exit when sposition2 = 0;
        end loop;
        dbms_output.put_line('end');

EXCEPTION
       WHEN OTHERS THEN
       BEGIN
           ROLLBACK;
           v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''-1'',''error:'||SQLERRM||''')';
           EXECUTE IMMEDIATE v_log_table_sql;
           COMMIT;
       END;
end;
/

prompt
prompt Creating procedure PRO_CREATEQUTESPACE
prompt ======================================
prompt
create or replace procedure PRO_CREATEQUTESPACE(spaceName IN VARCHAR2,spacePath IN VARCHAR2)
Authid Current_User----ʹ�洢���̿���ʹ��roleȨ��
is
space_sql VARCHAR2(5000);
execParm number;
proexecinfoid number;
execption_sql VARCHAR2(5000);

begin
   execParm := 0;
 space_sql :='create tablespace '||spaceName||' datafile '''||spacePath||''' size 50M  autoextend on default storage(initial 10M next 10M minextents 1 maxextents unlimited pctincrease 1)';
    execParm := 1;
    EXECUTE IMMEDIATE space_sql;
 --EXECUTE IMMEDIATE 'create tablespace '||spaceName||' datafile '''||spacePath||''' size 50M default
 --  storage(initial 10M next 10M minextents 1 maxextents unlimited pctincrease 1)';
  --execParm := 2;
 EXCEPTION WHEN OTHERS THEN
    select SEQ_PROEXECINFO.NEXTVAL into proexecinfoid from dual;
    execption_sql := 'insert into PROEXECINFO(ID,proname,param,prodesc) values('||proexecinfoid||',''PRO_CREATEQUTESPACE'','||execParm||','''||to_char(sysdate,'YYYY-MM-DD HH:MI')||spaceName||spacePath||''')';
    EXECUTE IMMEDIATE execption_sql;
--
   --dbms_output.put_line(sqlerrm);
--ROLLBACK;
end PRO_CREATEQUTESPACE;
--end;
/

prompt
prompt Creating procedure PRO_CREATESEQ
prompt ================================
prompt
create or replace procedure pro_createSeq(tabName IN VARCHAR2) is
--- 
     c_log_table_name              CONSTANT VARCHAR2(100) :='PRO_EXEC_LOG'; --��־��¼�ڴ˱���
     c_cur_pro_name                CONSTANT VARCHAR2(100) :='pro_createSeq'; --��ǰ�洢���̵�����
---
     v_log_table_sql               VARCHAR2(5000);
     v_sql1                        VARCHAR2(5000);
begin
    v_sql1 :='create sequence SEQ_'||tabName||'
              minvalue 1
              maxvalue 999999999999
              start with 1
              increment by 1
              cache 20
              cycle
              order';    
    --������seq��sql��������־��
    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''1'',''1.����SEQ��SQL���:'||v_sql1||''')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;
   
    EXECUTE IMMEDIATE v_sql1;    

EXCEPTION
       WHEN OTHERS THEN
       BEGIN
           ROLLBACK;
           v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''-1'',''error:'||SQLERRM||''')';
           EXECUTE IMMEDIATE v_log_table_sql;
           COMMIT;
       END;
end pro_createSeq;
/

prompt
prompt Creating procedure PRO_CREATETRIGGER
prompt ====================================
prompt
create or replace procedure pro_createTrigger(tabName IN VARCHAR2) is
--- 
     c_log_table_name              CONSTANT VARCHAR2(100) :='PRO_EXEC_LOG'; --��־��¼�ڴ˱���
     c_cur_pro_name                CONSTANT VARCHAR2(100) :='pro_createTrigger'; --��ǰ�洢���̵�����
---
     v_log_table_sql               VARCHAR2(5000);
     v_sql1                        VARCHAR2(5000);
begin
    v_sql1 :='Create Or Replace Trigger B_I_'||tabName ||' before insert on '||tabName||' for each row
              declare
              
              begin
              if ( :new.id is null )   --insert
              then
                   select seq_'||tabName ||'.nextVal into :new.id from dual;
              end if;
                   end B_I_'||tabName||';';
    --��������������sql��������־��
    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''1'',''1.������������SQL���:'||v_sql1||''')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;    
    
    EXECUTE IMMEDIATE v_sql1;

EXCEPTION    
    WHEN OTHERS THEN
       BEGIN
           ROLLBACK;
           v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''-1'',''error:'||SQLERRM||''')';
           EXECUTE IMMEDIATE v_log_table_sql;
           COMMIT;
       END;
end pro_createTrigger;
/

prompt
prompt Creating procedure PRO_CREATETABLE
prompt ==================================
prompt
create or replace procedure pro_createTable(tabName IN VARCHAR2,filedAndType IN VARCHAR2,indexfileds in varchar2)
Authid Current_User----ʹ�洢���̿���ʹ��roleȨ��
is
---
     c_oracle_data_dir             CONSTANT VARCHAR2(200) :='/data/d4/oradata/ict/nmspartition/'; --���ռ��ļ���ŵ�·��
     c_tablespace_init_size        CONSTANT VARCHAR2(10) :='10M'; --���ռ��ʼ����С
     c_tablespace_extend_size      CONSTANT VARCHAR2(10) :='5M'; --���ռ�ÿ���Զ�������С
     c_log_table_name              CONSTANT VARCHAR2(100) :='PRO_EXEC_LOG'; --��־��¼�ڴ˱���
     c_cur_pro_name                CONSTANT VARCHAR2(100) :='pro_createTable'; --��ǰ�洢���̵�����
---
     v_create_tablespace_sql       VARCHAR2(5000);
     v_log_table_sql               VARCHAR2(5000);
     v_sql1                        VARCHAR2(5000);
---    
     part_time                     VARCHAR2(64);--��һ�������ĺ�׺��
     part_time_format              VARCHAR2(64);--��һ��������ʱ��Ƚ��ַ���
     part_time2                    VARCHAR2(64);--�ڶ��������ĺ�׺��
     part_time2_format             VARCHAR2(64);--�ڶ���������ʱ��Ƚ��ַ���
begin
--------------------------------------- 
--�޸�Ϊ�����������޸�     
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''1'',''1.�����Ա��������ı��ռ䣨�������������� begin'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT;    
     
     --�����Ա��������ı��ռ䣨��������������
     v_create_tablespace_sql := 'CREATE BIGFILE TABLESPACE '||tabName||' DATAFILE '''||c_oracle_data_dir||tabName||'.dbf'' SIZE '||c_tablespace_init_size||' AUTOEXTEND ON NEXT '||c_tablespace_extend_size||' MAXSIZE UNLIMITED';
     execute immediate v_create_tablespace_sql;
     
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''2'',''2.�����Ա��������ı��ռ䣨�������������� end'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT; 
---------------------------------------    
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''3'',''3.��ѯ��ǰʱ�� begin'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT;    
     
     --��ѯ��ǰʱ��
     select to_char(sysdate,'yyyyMMdd') into part_time from dual; 
     select to_char(sysdate,'yyyy-MM-dd HH24:MI:SS') into part_time_format from dual; 
     
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''4'',''4.��ѯ��ǰʱ��:'||part_time||' end'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT; 
---------------------------------------   
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''5'',''5.������һ�������ı��ռ� begin'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT; 
         
     --������һ�������ı��ռ�
     v_create_tablespace_sql := 'CREATE BIGFILE TABLESPACE '||tabName||'_'||part_time||' DATAFILE '''||c_oracle_data_dir||tabName||'_'||part_time||'.dbf'' SIZE '||c_tablespace_init_size||' AUTOEXTEND ON NEXT '||c_tablespace_extend_size||' MAXSIZE UNLIMITED';
     execute immediate v_create_tablespace_sql;
     
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''6'',''6.������һ�������ı��ռ� end'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT; 
---------------------------------------      
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''7'',''7.��ѯ�ڶ����ʱ�� begin'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT; 
         
     --��ѯ�ڶ����ʱ�䣬���������ʱ����������ִ��ʱ��
     select to_char(sysdate+1,'yyyyMMdd') into part_time2 from dual; 
     select to_char(sysdate+1,'yyyy-MM-dd HH24:MI:SS') into part_time2_format from dual; 
     
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''8'',''8.��ѯ�ڶ����ʱ��:'||part_time2||' end'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT; 
---------------------------------------     
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''9'',''9.�����ڶ��������ı��ռ� begin'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT;  
          
     --�����ڶ��������ı��ռ�
     v_create_tablespace_sql := 'CREATE BIGFILE TABLESPACE '||tabName||'_'||part_time2||' DATAFILE '''||c_oracle_data_dir||tabName||'_'||part_time2||'.dbf'' SIZE '||c_tablespace_init_size||' AUTOEXTEND ON NEXT '||c_tablespace_extend_size||' MAXSIZE UNLIMITED';
     execute immediate v_create_tablespace_sql;
     
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''10'',''10.�����ڶ��������ı��ռ� end'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT; 
--------------------------------------   
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''11'',''11.���� begin'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT; 
     
     --����
     v_sql1 :='create table '||tabName ||'(id number not null primary key,detection_info_id number not null ,'||filedAndType||',DETECTIONED_STATE varchar(2),SEQ_ID number,DETECTION_SET_INFO_ID number,data_check_time DATE,data_arrive_time DATE,data_check_time_digital number,data_arrive_time_digital number,foreign KEY(detection_info_id) REFERENCES detection_info(id))'||
              'partition by range(DATA_CHECK_TIME)'||'(partition '||tabName||'_'||part_time||' values less than(to_date('''||part_time_format||''',''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) tablespace '||tabName||'_'||part_time||' storage(initial 10M next 10M maxextents unlimited ),'||
                                                      'partition '||tabName||'_'||part_time2||' values less than(to_date('''||part_time2_format||''',''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) tablespace '||tabName||'_'||part_time2||' storage(initial 10M next 10M maxextents unlimited ))';
     EXECUTE IMMEDIATE v_sql1;
     
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''12'',''12.���� end'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT;       
---------------------------------------
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''13'',''13.����seq begin'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT; 
     
     --����seq
     pro_createSeq(tabName);
     
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''14'',''14.����seq end'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT; 
---------------------------------------
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''15'',''15.���������� begin'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT; 
     
     --����������
     pro_createTrigger(tabName);
     
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''16'',''16.���������� end'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT;  
---------------------------------------
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''17'',''17.�������� begin'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT;  
     
     --��������    
     pro_createindex(tabName,indexfileds);  
     
     v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''18'',''18.�������� end'')';
     EXECUTE IMMEDIATE v_log_table_sql;
     COMMIT; 
----------------------------------------
EXCEPTION
     --�쳣�������ƣ���¼��ص�ora������Լ�����쳣��Ϣ
     WHEN OTHERS THEN
     BEGIN
         ROLLBACK;
         v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''-1'',''error:'||SQLERRM||''')';
         EXECUTE IMMEDIATE v_log_table_sql;
         COMMIT;
     END;
end pro_createTable;
/

prompt
prompt Creating procedure PRO_CREATEWEEKSPACE
prompt ======================================
prompt
create or replace procedure PRO_CREATEWEEKSPACE(spaceName IN VARCHAR2,spacePath IN VARCHAR2)
Authid Current_User----ʹ�洢���̿���ʹ��roleȨ��
 is
space_sql VARCHAR2(5000);
execParm number;
proexecinfoid number;
execption_sql VARCHAR2(5000);

begin
   execParm := 0;
 space_sql :='create tablespace '||spaceName||' datafile '''||spacePath||''' size 100M  autoextend on default storage(initial 100M next 10M minextents 1 maxextents unlimited pctincrease 1)';
 --space_sql :='create tablespace '||spaceName||' datafile '''||spacePath||''' size 100M autoextend on next 10M maxsize unlimited ';
    execParm := 1;
    EXECUTE IMMEDIATE space_sql;
 --EXECUTE IMMEDIATE 'create tablespace '||spaceName||' datafile '''||spacePath||''' size 50M default
 --  storage(initial 10M next 10M minextents 1 maxextents unlimited pctincrease 1)';
  --execParm := 2;
 EXCEPTION WHEN OTHERS THEN
    select SEQ_PROEXECINFO.NEXTVAL into proexecinfoid from dual;
    execption_sql := 'insert into PROEXECINFO(ID,proname,param,prodesc) values('||proexecinfoid||',''PRO_CREATEQUTESPACE'','||execParm||','''||to_char(sysdate,'YYYY-MM-DD HH:MI')||spaceName||spacePath||''')';
    EXECUTE IMMEDIATE execption_sql;
--
   --dbms_output.put_line(sqlerrm);
--ROLLBACK;
end PRO_CREATEWEEKSPACE;
--end;
/

prompt
prompt Creating procedure PRO_RECORD_LOG_INFO
prompt ======================================
prompt
create or replace procedure PRO_RECORD_LOG_INFO(log_table_name in VARCHAR2,id in number, proname in VARCHAR2, sequence in VARCHAR2,log_cont in VARCHAR2)
is
   v_log_table_sql          VARCHAR2(2000);----��¼��־sql���
   pragma autonomous_transaction;
begin
   v_log_table_sql :=  'INSERT INTO '||log_table_name||'(id,proname,sequence,log_cont) VALUES('||id||','''||proname||''','''||sequence||''','''||log_cont||''')';
   EXECUTE IMMEDIATE v_log_table_sql;
   COMMIT;
end PRO_RECORD_LOG_INFO;
/

prompt
prompt Creating procedure PRO_DEL_PART_BY_DAY
prompt ======================================
prompt
create or replace procedure pro_del_part_by_day(v_table_name IN VARCHAR2,v_par_sequence IN VARCHAR2,v_partition_name IN VARCHAR2)
Authid Current_User----ʹ�洢���̿���ʹ��roleȨ��
AS
-------------------------------------------------------------------------------------------------------------

--�˹��̵Ĺ���
--ÿ��ִ�п����Զ��Զ�ɾ��������ɾ��ָ���ı��ռ䣬�����죩
--��ǰ�汾��1. 0
--��         �ߣ�hyx
--��         �ڣ�2013.11.01
---
-------------------------------------------------------------------------------------------------------------
--��������
   ----���ռ���= ���ռ���ǰ׺ + ���� + _ + ����
   c_table_name_prefix      CONSTANT VARCHAR2(100) :=''; ----���ռ���ǰ׺��ע�⣺һ��Ҫ��д��
   c_log_table_name         CONSTANT VARCHAR2(100) :='PRO_EXEC_LOG'; --��־��¼�ڴ˱���
   c_cur_pro_name           CONSTANT VARCHAR2(100) :='pro_del_part_by_day'; --��ǰ�洢���̵�����
-------------------------------------------------------------------------------------------------------------
   --v_log_table_sql          VARCHAR2(2000);----��¼��־sql���
   v_del_partition_sql      VARCHAR2(2000); --ɾ������sql���
   v_log_id                 NUMBER;--��־��¼id

-------------------------------------------------------------------------------------------------------------
BEGIN

   ----------------------------------------------------------

   select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
   PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,v_par_sequence||'3','3.ɾ��'||v_table_name||'������ begin');


   ----ɾ��ָ��������
   v_del_partition_sql := 'alter table '||v_table_name||' drop partition '||v_partition_name||' update indexes';

   execute immediate v_del_partition_sql;

   select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
   PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,v_par_sequence||'4','4.ɾ��'||v_table_name||'������ end');

   -------------------------- --------------------------------

   select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
   PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,v_par_sequence||'5','5.ɾ��'||v_table_name||'�� ���ռ� begin');


   ----ɾ��ָ�����ռ�
   execute immediate 'drop tablespace '||c_table_name_prefix||v_partition_name||' including contents and datafiles';

   select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
   PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,v_par_sequence||'6','6.ɾ��'||v_table_name||'�� ���ռ� end');


   ----------------------------------------------------------
EXCEPTION
    --�쳣�������ƣ���¼��ص�ora������Լ�����쳣��Ϣ
    WHEN OTHERS THEN
    BEGIN
         ROLLBACK;
         select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
         PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,'-1','error:'||SQLERRM);

    END;
END pro_del_part_by_day;
/

prompt
prompt Creating procedure PRO_DELTABSPACE
prompt ==================================
prompt
create or replace procedure PRO_DELTABSPACE(v_par_sequence IN VARCHAR2,v_cur_sql IN VARCHAR2)
Authid Current_User----ʹ�洢���̿���ʹ��roleȨ��
is
--------------------------------------------------------------
--������
  c_log_table_name              CONSTANT VARCHAR2(100) :='PRO_EXEC_LOG'; --��־��¼�ڴ˱���
  c_cur_pro_name                CONSTANT VARCHAR2(100) :='PRO_DELTABSPACE'; --��ǰ�洢���̵�����

--����
  v_table_name                  VARCHAR2(2000); --����
  v_partition_name              VARCHAR2(2000); --��������
  v_sequence_count              NUMBER :=0; --�˴洢���̵IJ�����
  v_full_sequence               VARCHAR2(100);--������ȫ��
  --v_log_table_sql          VARCHAR2(2000);----��¼��־sql���
  v_log_id                 NUMBER;--��־��¼id
--�α�
  type cur_type is ref cursor;
  cur cur_type;
  CT_COLUMNS  user_tab_partitions%ROWTYPE;--����CT_COLUMNS������%rowtype �������%rowtype
--------------------------------------------------------------
BEGIN
  OPEN cur for v_cur_sql;

    LOOP
      v_sequence_count := v_sequence_count + 1;
      FETCH cur INTO CT_COLUMNS;
      EXIT WHEN cur%NOTFOUND;
--------------------------------------------------------------
          v_table_name :=CT_COLUMNS.table_name;--����
          v_partition_name :=CT_COLUMNS.partition_name;--��������

          v_full_sequence := v_par_sequence||v_sequence_count;

          select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
          PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,v_full_sequence,v_sequence_count||'.ɾ��'||v_table_name||'��ָ���ı��ռ�ͱ����� begin');


          --ɾ��detection_info��ָ���ı��ռ�ͱ�����
          pro_del_part_by_day(v_table_name,v_full_sequence||'_',v_partition_name);

          v_sequence_count := v_sequence_count + 1;
          v_full_sequence := v_par_sequence||v_sequence_count;

          select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
          PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,v_full_sequence,v_sequence_count||'.ɾ��'||v_table_name||'��ָ���ı��ռ�ͱ����� end');

--------------------------------------------------------------
    END LOOP;
  CLOSE cur;
  EXCEPTION
      --�쳣�������ƣ���¼��ص�ora������Լ�����쳣��Ϣ
      WHEN OTHERS THEN
         BEGIN
           ROLLBACK;
           select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
           PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,'-1','error:'||SQLERRM);
         END;
END PRO_DELTABSPACE;
/

prompt
prompt Creating procedure PRO_DELTABSPACE_NAME
prompt =======================================
prompt
create or replace procedure PRO_DELTABSPACE_NAME(tempstr IN VARCHAR2,user_name IN VARCHAR2,tablename IN VARCHAR2)
Authid Current_User
----ʹ�洢���̿���ʹ��roleȨ��
is
space_sql VARCHAR2(5000);
--execParm number;
proexecinfoid number;

begin
   -- execParm := 1;
   space_sql :='alter table '||user_name||'.'||tablename||' drop partition '||tempstr||' update indexes';
   -- execParm := 2;
     EXECUTE IMMEDIATE space_sql;
   -- execParm := 3;
    --EXCEPTION
      --WHEN OTHERS THEN
     --    dbms_output.put_line(sqlerrm);
    --  ROLLBACK;
    EXCEPTION
      WHEN OTHERS THEN
            select SEQ_PROEXECINFO.NEXTVAL into proexecinfoid from dual;
          -- EXECUTE IMMEDIATE 'insert into PROEXECINFO(ID,proname,param,prodesc) values(seq_proexecinfo.nextval,''PRO_DELTABSPACE'','||CT_COLUMNS.segment_name||','''||to_char(sysdate,'YYYY-MM-DD HH:MI')||CT_COLUMNS.segment_name||tempstr||space_sql||''')';
          dbms_output.put_line(sqlerrm);
      ROLLBACK;
end PRO_DELTABSPACE_NAME;
/

prompt
prompt Creating procedure PRO_DEL_WEEK_TABSPACE
prompt ========================================
prompt
create or replace procedure PRO_DEL_WEEK_TABSPACE(tempstr IN VARCHAR2,user_name IN VARCHAR2)
Authid Current_User----ʹ�洢���̿���ʹ��roleȨ��
is
space_sql VARCHAR2(5000);
--execParm number;
proexecinfoid number;
--extendtabName VARCHAR2(30);--Ҫ�����·����ı�
--�α�
  --�Ӽ���������в�ѯ���Ѿ��������Զ���������
  Cursor cur is
  --SELECT distinct x.table_name
   -- FROM dba_tab_partitions x, dba_tab_partitions y
   --- WHERE x.tablespace_name =tempstr AND y.tablespace_name <> tempstr AND x.table_name=y.table_name
   -- order by x.table_name;
         select distinct segment_name from user_extents where  SEGMENT_TYPE='TABLE PARTITION' AND partition_name = tempstr and segment_name <> 'DETECTION_INFO';
        --select distinct segment_name from user_extents where partition_name = tempstr and segment_name <> 'DETECTION_INFO';
        -- select segment_name from dba_segments where tablespace_name = tempstr and segment_type like '%TABLE%';
  CT_COLUMNS cur%ROWTYPE;
begin
OPEN cur();
  LOOP
     FETCH cur INTO CT_COLUMNS;
    EXIT WHEN cur%NOTFOUND;
   -- execParm := 1;
   space_sql :='alter table '||user_name||'.'||CT_COLUMNS.segment_name||' drop partition '||tempstr||' update indexes';

   -- execParm := 2;
     EXECUTE IMMEDIATE space_sql;
   -- execParm := 3;
    --EXCEPTION
      --WHEN OTHERS THEN
     --    dbms_output.put_line(sqlerrm);
    --  ROLLBACK;
  end LOOP;
  CLOSE cur;
   -- EXCEPTION
   --   WHEN OTHERS THEN
    --        select SEQ_PROEXECINFO.NEXTVAL into proexecinfoid from dual;
    --       EXECUTE IMMEDIATE 'insert into PROEXECINFO(ID,proname,param,prodesc) values(seq_proexecinfo.nextval,''PRO_DEL_WEEK_TABSPACE'','||CT_COLUMNS.segment_name||','''||to_char(sysdate,'YYYY-MM-DD HH:MI')||CT_COLUMNS.segment_name||tempstr||space_sql||''')';
    --      dbms_output.put_line(sqlerrm);
   --   ROLLBACK;
end PRO_DEL_WEEK_TABSPACE;
/

prompt
prompt Creating procedure PRO_DEL_PARTITION
prompt ====================================
prompt
create or replace procedure pro_del_Partition
Authid Current_User----ʹ�洢���̿���ʹ��roleȨ��
 is
C_DATE            VARCHAR2(64);
tempstr           VARCHAR2(64);
user_name          VARCHAR2(64);
--space_path        varchar2(1000); --�洢�����ϴ�ִ�е�ʱ��
--end_date         VARCHAR2(32);--Ҫ�����·����ı�
space_sql          VARCHAR2(1024);
begin

--��ȡϵͳ��ǰ��
select to_char(sysdate+7, 'yyyy') into C_DATE from dual;
--��ȡϵͳ��һ��
select to_char(sysdate+7, 'iw') into tempstr from dual;
--�����ǰ�½�������һ��ĵ�һ������Ϊ��һ��
if tempstr = 1
   then select to_char(sysdate-7, 'yyyy') into C_DATE from dual;
end if;
--Ҫɾ������7��ǰ�ķ���
--tempstr := to_number(tempstr)-7;
select to_char(sysdate-6*12, 'iw') into tempstr from dual;
--Ҫɾ���������������
tempstr := 'DI_W'||C_DATE||''||tempstr;
--tempstr := 'DI_W201341';
user_name := 'NMS';
--���˱��ռ�ʹ��Ҫ����֮ǰ�������µı��ռ�֮��ɾ���ɵı��ռ䣺
--һ��ɾ��ʹ�øñ��ռ�ı�
PRO_DEL_WEEK_TABSPACE(tempstr,'NMS');
--space_sql :='alter table '||user_name||'.DETECTION_INFO drop partition '||tempstr||' update indexes';

--������ʱͣ�ñ���������������
--alter table NMS.detection_info disable primary key cascade;

space_sql :='alter table '||user_name||'.detection_info disable primary key cascade';
 EXECUTE IMMEDIATE space_sql;

--����ɾ������������ķ���
--alter table  NMS.detection_info drop partition '||tempstr||' update indexes;
--alter table  NMS.detection_info drop partition '||tempstr||' including contents;
space_sql :='alter table '||user_name||'.detection_info drop partition '||tempstr||' update indexes ';

 EXECUTE IMMEDIATE space_sql;
--�ġ��ָ�����������������
--alter table  '||user_name||'.detection_info enable primary key;
--alter index /*nms.detection_info.*/SYS_C0010650 rebuild;

space_sql :='alter table  '||user_name||'.detection_info enable primary key';
 EXECUTE IMMEDIATE space_sql;

--�塢ɾ������
--drop tablespace '||tempstr||' including contents and datafiles cascade constraints update indexes;
space_sql :='drop tablespace '||tempstr||' including contents and datafiles';--and datafiles cascade constraints update indexes;
 EXECUTE IMMEDIATE space_sql;

end pro_del_Partition;
/

prompt
prompt Creating procedure PRO_EXTENDTABSPACE
prompt =====================================
prompt
create or replace procedure PRO_EXTENDTABSPACE(v_par_sequence IN VARCHAR2)
Authid Current_User----ʹ�洢���̿���ʹ��roleȨ��
is
----------------------------------------------------------

--������
   c_cur_pro_name                CONSTANT VARCHAR2(100) :='PRO_EXTENDTABSPACE'; --��ǰ�洢���̵�����
   c_log_table_name              CONSTANT VARCHAR2(100) :='PRO_EXEC_LOG'; --��־��¼�ڴ˱���
--����
  v_table_name                   VARCHAR2(2000); --����
  v_sequence_count               NUMBER:=0 ; --�˴洢���̵IJ�����
  v_full_sequence                VARCHAR2(100);--������ȫ��
  v_log_table_sql               VARCHAR2(2000);----��¼��־sql���

--�α�
  --�Ӽ���������в�ѯ���Ѿ��������Զ���������
  --��Ҫ�ӱ������в鿴�����Ƿ񴴽���û������˵����ʶ�����⣬ʵ��û���������Ͳ��ô�������
  Cursor cur is
         select distinct cti.table_name  extendtabName
         from check_type_info cti
         left join user_tables ut on ut.table_name = cti.table_name
         where cti.crete_state = 0 and ut.table_name is not null;
  CT_COLUMNS cur%ROWTYPE;

-----------------------------------------------------------
BEGIN
  OPEN cur();
  LOOP
    v_sequence_count := v_sequence_count + 1;
    FETCH cur
      INTO CT_COLUMNS;
    EXIT WHEN cur%NOTFOUND;
-------------------------------------------------------------
    v_table_name :=CT_COLUMNS.extendtabName;--����
    v_full_sequence := v_par_sequence||v_sequence_count;

    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_full_sequence||''','''||v_sequence_count||'.Ϊ'||v_table_name||'���������ռ�ͱ����� begin'')';
    EXECUTE IMMEDIATE v_log_table_sql;
    COMMIT;

    --Ϊdetection_info���������ռ�ͱ�����
    pro_add_part_one_day(v_table_name,v_full_sequence||'_');
    
    v_sequence_count := v_sequence_count + 1;
    v_full_sequence := v_par_sequence||v_sequence_count;
    v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''','''||v_full_sequence||''','''||v_sequence_count||'.Ϊ'||v_table_name||'���������ռ�ͱ����� end'')';
    EXECUTE IMMEDIATE v_log_table_sql  ;
    COMMIT;
---------------------------------------------------------------
  END LOOP;
  CLOSE cur;
  EXCEPTION
      --�쳣�������ƣ���¼��ص�ora������Լ�����쳣��Ϣ
      WHEN OTHERS THEN
         BEGIN
           ROLLBACK;
           v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''-1'',''error:'||SQLERRM||')';
           EXECUTE IMMEDIATE v_log_table_sql;
           COMMIT;
           --ROLLBACK;
         END;
END;
/

prompt
prompt Creating procedure PRO_DRIVER_ADD_PARTITION
prompt ===========================================
prompt
create or replace procedure pro_driver_add_Partition
Authid Current_User----ʹ�洢���̿���ʹ��roleȨ��
IS
-------------------------------------------------------------------------------------------------------------

--�˹��̵Ĺ���:
--      ÿ��ִ�п����Զ������ƶ��ı��ռ䣬�Զ����ӷ��������죩
--�� ǰ �� ����1. 0
--��        �ߣ�hyx
--��        �ڣ�2013.10.31
-------------------------------------------------------------------------------------------------------------
---������
   c_log_table_name              CONSTANT VARCHAR2(100) :='PRO_EXEC_LOG'; --��־��¼�ڴ˱���
   c_cur_pro_name                CONSTANT VARCHAR2(100) :='pro_driver_add_Partition'; --��ǰ�洢���̵�����
---
-------------------------------------------------------------------------------------------------------------
---
   v_log_table_sql               VARCHAR2(2000);----��¼��־sql���
   v_table_name                  VARCHAR2(2000); --����--����һ��Ҫ��д
-------------------------------------------------------------------------------------------------------------

BEGIN
--Ϊ�̶����������ռ估����
-----------------------------------------------------------
   v_table_name :='DETECTION_INFO';
   v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''1'',''1.Ϊ'||v_table_name||'���������ռ�ͱ����� begin'')';
   EXECUTE IMMEDIATE v_log_table_sql;
   COMMIT;

   --Ϊdetection_info���������ռ�ͱ�����
   pro_add_part_one_day(v_table_name,'1_');

   v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''2'',''2.Ϊ'||v_table_name||'���������ռ�ͱ����� end'')';
   EXECUTE IMMEDIATE v_log_table_sql;
   COMMIT;
-----------------------------------------------------------
   v_table_name :='DETECTION_INFO_WARNING';
   v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''3'',''3.Ϊ'||v_table_name||'���������ռ�ͱ����� begin'')';
   EXECUTE IMMEDIATE v_log_table_sql;
   COMMIT;

   --Ϊdetection_info���������ռ�ͱ�����
   pro_add_part_one_day(v_table_name,'3_');

   v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''4'',''4.Ϊ'||v_table_name||'���������ռ�ͱ����� end'')';
   EXECUTE IMMEDIATE v_log_table_sql;
   COMMIT;

-----------------------------------------------------------
   v_table_name :='DI_SYSTEMINFO_DISK';
   v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''5'',''5.Ϊ'||v_table_name||'���������ռ�ͱ����� begin'')';
   EXECUTE IMMEDIATE v_log_table_sql;
   COMMIT;

   --Ϊdetection_info���������ռ�ͱ�����
   pro_add_part_one_day(v_table_name,'5_');

   v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''6'',''6.Ϊ'||v_table_name||'���������ռ�ͱ����� end'')';
   EXECUTE IMMEDIATE v_log_table_sql;
   COMMIT;
-----------------------------------------------------------
   v_table_name :='DI_SYSTEMINFO_NET';
   v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''7'',''7.Ϊ'||v_table_name||'���������ռ�ͱ����� begin'')';
   EXECUTE IMMEDIATE v_log_table_sql;
   COMMIT;

   --Ϊdetection_info���������ռ�ͱ�����
   pro_add_part_one_day(v_table_name,'7_');

   v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''8'',''8.Ϊ'||v_table_name||'���������ռ�ͱ����� end'')';
   EXECUTE IMMEDIATE v_log_table_sql;
   COMMIT;
-----------------------------------------------------------
--Ϊ��̬���������ռ估����
   v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''9'',''9.Ϊ��̬���������ռ估���� begin'')';
   EXECUTE IMMEDIATE v_log_table_sql;
   COMMIT;

   --Ϊ��̬���������ռ估����
   pro_extendtabspace('9_');

   v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''10'',''10.Ϊ��̬���������ռ估���� end'')';
   EXECUTE IMMEDIATE v_log_table_sql;
   COMMIT;
EXCEPTION
    --�쳣�������ƣ���¼��ص�ora������Լ�����쳣��Ϣ
    WHEN OTHERS THEN
    BEGIN
         ROLLBACK;
         v_log_table_sql :=  'INSERT INTO '||c_log_table_name||'(id,proname,sequence,log_cont) VALUES(SEQ_PRO_EXEC_LOG.nextval,'''||c_cur_pro_name||''',''-1'',''error:'||SQLERRM||''')';
         EXECUTE IMMEDIATE v_log_table_sql;
         COMMIT;
         --ROLLBACK;
    END;
END;
/

prompt
prompt Creating procedure PRO_DRIVER_DEL_PARTITION
prompt ===========================================
prompt
create or replace procedure pro_driver_del_Partition
Authid Current_User----ʹ�洢���̿���ʹ��roleȨ��
is
------------- ----------------------------------------
--������
     c_log_table_name         CONSTANT VARCHAR2(100) :='PRO_EXEC_LOG'; --��־��¼�ڴ˱���
--   c_user_name              CONSTANT VARCHAR2(100) :='NMS'; --�û���
     c_cur_pro_name           CONSTANT VARCHAR2(100) :='pro_driver_del_Partition'; --��ǰ�洢���̵�����
     c_day                    CONSTANT NUMBER :=2;----ָ��ɾ��������ǰ����ķ����ͱ��ռ�
     c_count                  CONSTANT NUMBER :=3;----����ͣ�û�ָ������������������������ʱ��ͣ�û���������ᱨ�쳣��
     c_sleep_count            CONSTANT NUMBER :=5;----���γ���ͣ�û���������ļ��ʱ��,��λ����
     
---
     v_del_date               VARCHAR2(200);----��Ҫɾ������������
     --v_log_table_sql          VARCHAR2(2000);----��¼��־sql���
     v_cur_sql                VARCHAR2(2000);----�α�sql
     space_sql                VARCHAR2(2000);----sql������
     v_table_name             VARCHAR2(2000); --����
     v_count                  NUMBER :=0; --��¼�ѳ���ͣ�û�ָ�����Ĵ���������������ʱ��ͣ�û���������ᱨ�쳣��
     v_log_id                 NUMBER;--��־��¼id
-----------------------------------------------------
begin
------------------------------------------------------  

   select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
   PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,'1','1.��ȡ��Ҫɾ������������ begin');

   ----��ȡ��Ҫɾ������������--��ʱδ�õ�
   select to_char(sysdate-c_day,'yyyymmdd') into v_del_date from dual;

   select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
   PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,'2','2.��ȡ��Ҫɾ������������:'||v_del_date||' end');
   
------------------------------------------------------
--ɾ��ָ���ı��ռ�
------------------------------------------------------

   select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
   PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,'3','3.ɾ����̬���ı��ռ�ͷ��� begin');


   --һ��ɾ����̬���ı��ռ�ͷ���
   v_cur_sql :='select * from user_tab_partitions utp where  utp.table_name <> ''DETECTION_INFO'' and  to_date(substr(utp.partition_name,-8),''yyyy-MM-dd'')<=to_date('||v_del_date||',''yyyy-MM-dd'')';
   PRO_DELTABSPACE('3_',v_cur_sql);

   select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
   PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,'4','4.ɾ����̬���ı��ռ�ͷ��� end');

------------------------------------------------------
   v_table_name :='DETECTION_INFO';
 
   select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
   PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,'5','5.ɾ��'||v_table_name||'������������ķ��������ռ� begin');


   --����ɾ������������ķ��������ռ�
   v_cur_sql :='select * from user_tab_partitions utp where  utp.table_name = ''DETECTION_INFO'' and  to_date(substr(utp.partition_name,-8),''yyyy-MM-dd'')<=to_date('||v_del_date||',''yyyy-MM-dd'')';
   PRO_DELTABSPACE('5_',v_cur_sql);
   
   select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
   PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,'6','6.ɾ��'||v_table_name||'������������ķ��������ռ� end');

------------------------------------------------------

    EXCEPTION
      --�쳣�������ƣ���¼��ص�ora������Լ�����쳣��Ϣ
      WHEN OTHERS THEN
      BEGIN
           ROLLBACK;

           select SEQ_PRO_EXEC_LOG.nextval into v_log_id from dual;
           PRO_RECORD_LOG_INFO(c_log_table_name,v_log_id,c_cur_pro_name,'-1','error:'||SQLERRM);

      END;
end pro_driver_del_Partition;
/


-- Create sequence 
create sequence SEQ_PRO_EXEC_LOG
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 20
cycle
order; 
/
spool off