--Thủ tục tạo partition theo ngày trong Oracle Database
-- 1.Tao bang
CREATE TABLE cus.table1
(imsi VARCHAR2(15) NOT NULL,
bill_datetime DATE NOT NULL,
acc_profile VARCHAR2(20),
credit_charged NUMBER(10,2)
)
TABLESPACE DATA
PARTITION BY RANGE (bill_datetime)
(
PARTITION DATA20210101 VALUES LESS THAN (TO_DATE('2021-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2021
)
--2.Add them partition
DECLARE
v_nam NUMBER (4) := 2021; --2014
v_tablename VARCHAR2 (50) := 'table1';
v_date_from date := to_date('02/01/2021','dd/mm/yyyy');
v_date_to date := to_date('31/12/2021','dd/mm/yyyy');
v_numday number(5);
v_tablespace varchar2(50):='DATA2021';
BEGIN
v_numday:=v_date_to-v_date_from;
FOR i IN 0 .. v_numday
LOOP
DBMS_OUTPUT.put_line ('alter table '|| v_tablename || ' add PARTITION DATA'||to_char(v_date_from+i,'YYYYMMDD')||' VALUES LESS THAN (TO_DATE('''|| to_char(v_date_from+i+1,'YYYY-MM-DD')||' 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) LOGGING TABLESPACE '||v_tablespace||';');
END LOOP;
END;
-- Vao muc DBMS_Ouput cua TOAD:
--3.Tao index
create index cus.table1_I1 on cus.table1(imsi) local parallel 8 nologging online
create index cus.table1_I2 on cus.table1(acc_profile) parallel 8 nologging online
alter table cus.table1 truncate partition data20210101
alter table cus.table1 drop partition data20210101
select * from dba_indexes where owner='CUS'
and table_name='TABLE1';
alter table cus.table1 truncate partition data20210102
select * from dba_ind_partitions where index_owner='CUS'
and index_name='TABLE1_I1'
and status!='USABLE';
alter index cus.table1_I1 nologging noparallel;
--3.Rebuild index ve tablespace INDX
DECLARE
v_date_from date := to_date('01/01/2021','dd/mm/yyyy');
v_date_to date := to_date('31/12/2021','dd/mm/yyyy');
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 b.table_owner='CUS' and b.table_name='TABLE1' and a.index_name not like '%$%' and b.partition_name like '%20211231' order by a.owner,a.index_name;
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,'YYYY')||' nologging parallel 8 online;');
END LOOP;
END LOOP;
END;
-- Set nologging noparallel
DECLARE
cursor c1 is
select distinct a.index_owner, a.index_name from DBA_ind_partitions a where a.index_owner='CUS' and a.index_name not like '%$%' and a.partition_name like '%20211231'
order by a.index_owner,a.index_name;
BEGIN
FOR i1 in c1
LOOP
DBMS_OUTPUT.put_line ('alter index '||i1.index_owner||'.'||i1.index_name || ' nologging noparallel;');
END LOOP;
END;
