Thứ Ba, 1 tháng 12, 2020

Script rebuild index theo ngày, tháng, năm trong Oracle Database

---index theo nam
DECLARE
   v_nam          varchar2(4) := '2012';
   v_tablespace   varchar2(50):='INDX';
   cursor c1 is  
--     select table_owner,table_name,max(partition_name)
--     from dba_tab_partitions group by table_owner,table_name having max(partition_name) like '%'||v_nam||'%' and length(max(partition_name))<9;
     select table_owner,table_name,max(partition_name)
     from dba_tab_partitions where table_name='table1' group by table_owner,table_name;
     
   cursor c2 (p_tablename varchar2,p_owner varchar2) is
     select * from DBA_PART_INDEXES where table_name = p_tablename and owner=p_owner;
BEGIN
   for r1 in c1 
   LOOP
    FOR r2 in c2(r1.table_name,r1.table_owner)
    LOOP
       DBMS_OUTPUT.put_line ('alter index '|| r1.table_owner||'.'||r2.index_name || ' REBUILD PARTITION DATA'||v_nam||' TABLESPACE '||v_tablespace||v_nam||';');
    END LOOP;  
   end loop;   
END;

-------------INDEX theo thang
DECLARE
   v_nam          varchar2(4) := '2012';
   v_thang_from   NUMBER (2)    := 01;
   v_thang_to     NUMBER (2)    := 12;
   v_tablespace   varchar2(50):='INDX';
   cursor c1 is  
--     select table_owner,table_name,max(partition_name)
--     from dba_tab_partitions group by table_owner,table_name having max(partition_name) like '%'||v_nam||'%' and length(max(partition_name))>9;
     select table_owner,table_name,max(partition_name)
     from dba_tab_partitions where table_name='table1' group by table_owner,table_name;
     
   cursor c2 (p_tablename varchar2,p_owner varchar2) is
     select * from DBA_PART_INDEXES where table_name = p_tablename and owner=p_owner;
BEGIN
   for r1 in c1 
   LOOP
    FOR r2 in c2(r1.table_name,r1.table_owner)
    LOOP
        FOR i IN v_thang_from .. (v_thang_to)
        LOOP
          DBMS_OUTPUT.put_line ('alter index '|| r1.table_owner||'.'||r2.index_name || ' REBUILD PARTITION DATA'||v_nam||LPAD (to_char(i), 2, '0')||' TABLESPACE '||v_tablespace||v_nam||LPAD (to_char(i), 2, '0')||';');
        END LOOP;    END LOOP;  
   end loop;   
END;

#######GEN INDX PARTITION --theo ngay, ca DB
DECLARE
   v_date_from   date    := '1/1/2013';
   v_date_to     date    := '31/12/2013';
   v_numday     number;
   v_tablespace varchar2(50):='INDX';
   cursor c1 is
     select a.* from DBA_PART_INDEXES a, DBA_TAB_PARTITIONS b where a.owner=B.TABLE_OWNER and a.table_name=B.TABLE_NAME
     and a.owner not like 'SYS%' and b.partition_name like '%20131231';
BEGIN
   v_numday:=v_date_to-v_date_from; 
   FOR i1 in c1
   LOOP
       FOR i IN 0 .. v_numday
       LOOP
            DBMS_OUTPUT.put_line ('alter index '||i1.owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' TABLESPACE '||v_tablespace||to_char(v_date_from+i,'YYYYMM')||';');
       END LOOP;
   END LOOP;
END;

#######GEN INDX PARTITION --theo ngay, 1 bang
DECLARE
   v_tablename    VARCHAR2 (50) := 'table1';
   v_owner        VARCHAR2 (50) := 'user1';
   v_date_from   date    := '1/11/2011';
   v_date_to     date    := '31/12/2011';
   v_numday     number(2);
   v_tablespace varchar2(50):='INDX';
   cursor c1 is
     select * from DBA_PART_INDEXES where table_name = v_tablename and owner=v_owner;
BEGIN
   v_numday:=v_date_to-v_date_from; 
   FOR i1 in c1
   LOOP
       FOR i IN 0 .. v_numday
       LOOP
            DBMS_OUTPUT.put_line ('alter index '||v_owner||'.'||i1.index_name || ' REBUILD PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' TABLESPACE '||v_tablespace||to_char(v_date_from+i,'YYYYMM')||';');
       END LOOP;
   END LOOP;
END;



KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH: 📧 Mail: binhoracle@gmail.com ☎️ Mobile: 0902912888 ⚡️ Skype: tranbinh48ca 👨 Facebook: https://www.facebook.com/BinhOracleMaster 👨 Inbox Messenger: https://m.me/101036604657441 (profile) 👨 Fanpage: https://www.facebook.com/tranvanbinh.vn 👨 Inbox Fanpage: https://m.me/tranvanbinh.vn 👨👩 Group FB: https://www.facebook.com/groups/OracleDBAVietNam 👨 Website: http://www.tranvanbinh.vn 👨 Blogger: https://tranvanbinhmaster.blogspot.com 🎬 Youtube: http://bit.ly/ytb_binhoraclemaster 👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi 👨 Linkin: https://www.linkedin.com/in/binhoracle 👨 Twitter: https://twitter.com/binhoracle 👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master