--DROP TABLE user1.table1 CASCADE CONSTRAINTS;
CREATE TABLE user1.table1
(
SUB_ID NUMBER(20),
ISDN VARCHAR2(15 BYTE),
REG_DATE DATE,
SYS_DATE DATE,
COMMAND VARCHAR2(50 BYTE)
TABLESPACE DATA
PARTITION BY RANGE (REG_DATE)
(
PARTITION data201601 VALUES LESS THAN (TO_DATE('01/02/2016', 'dd/mm/yyyy')) tablespace DATA2016,
PARTITION data201602 VALUES LESS THAN (TO_DATE('01/03/2016', 'dd/mm/yyyy')) tablespace DATA2016,
PARTITION data201603 VALUES LESS THAN (TO_DATE('01/04/2016', 'dd/mm/yyyy')) tablespace DATA2016,
PARTITION data201604 VALUES LESS THAN (TO_DATE('01/05/2016', 'dd/mm/yyyy')) tablespace DATA2016,
PARTITION data201605 VALUES LESS THAN (TO_DATE('01/06/2016', 'dd/mm/yyyy')) tablespace DATA2016,
PARTITION data201606 VALUES LESS THAN (TO_DATE('01/07/2016', 'dd/mm/yyyy')) tablespace DATA2016,
PARTITION data201607 VALUES LESS THAN (TO_DATE('01/08/2016', 'dd/mm/yyyy')) tablespace DATA2016,
PARTITION data201608 VALUES LESS THAN (TO_DATE('01/09/2016', 'dd/mm/yyyy')) tablespace DATA2016,
PARTITION data201609 VALUES LESS THAN (TO_DATE('01/10/2016', 'dd/mm/yyyy')) tablespace DATA2016,
PARTITION data201610 VALUES LESS THAN (TO_DATE('01/11/2016', 'dd/mm/yyyy')) tablespace DATA2016,
PARTITION data201611 VALUES LESS THAN (TO_DATE('01/12/2016', 'dd/mm/yyyy')) tablespace DATA2016,
PARTITION data201612 VALUES LESS THAN (TO_DATE('01/01/2017', 'dd/mm/yyyy' )) tablespace DATA2016
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX user1.table1_I1 ON user1.table1
(SUB_ID ) tablespace INDX2016
NOLOGGING
NOPARALLEL local online;
-- Rebuidl index ve tablespace INDX2016 (optional)
select 'alter index user1.table1_i1 rebuild partition ' || partition_Name || ' tablespace INDX2016 online;' from dba_ind_partitions where index_name like 'table1_I1';
DECLARE
v_nam NUMBER (4) := 2021;
v_thang_from NUMBER (2) := 1;
v_thang_to NUMBER (2) := 12;
v_tablespace varchar2(50):='DATA';
CURSOR c1
IS
select table_owner, table_name,max(partition_name)
from dba_tab_partitions
WHERE PARTITION_NAME LIKE '%2020%'
and table_name not like '%$%'
and table_name like 'TAB1'
and table_owner='TEST_OWNER'
GROUP BY table_owner, table_name
having length(max(partition_name))>9 and length(max(partition_name))<11 and max(partition_name) like '%202012';
BEGIN
for r1 in c1 loop
FOR i IN v_thang_from .. (v_thang_to-1)
LOOP
DBMS_OUTPUT.put_line ('alter table '||r1.table_owner ||'.'|| r1.table_name || ' add PARTITION DATA'||v_nam||LPAD (i, 2, '0')||' VALUES LESS THAN (TO_DATE('''|| v_nam ||'-'||LPAD (i+1, 2, '0')||'-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE '||v_tablespace||v_nam||';');
END LOOP;
DBMS_OUTPUT.put_line ('alter table '|| r1.table_owner ||'.'|| r1.table_name || ' add PARTITION DATA'||v_nam||LPAD (to_char(v_thang_to), 2, '0')||' VALUES LESS THAN (TO_DATE('''|| (v_nam +1) ||'-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE '||v_tablespace||v_nam||';');
end loop;
END;
DECLARE
v_nam varchar2(4) := '2021';
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
where table_owner='TEST_OWNER'
and table_name='TAB1'
group by table_owner,table_name having max(partition_name) like '%'||v_nam||'%' and length(max(partition_name))>9 and length(max(partition_name))<11;
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||' nologging parallel 8 online;');
END LOOP; END LOOP;
end loop;
END;
