---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)
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;
