-- Thu tuc chuyen bang user1.table1 partition theo bdate sang partition theo thang
--1.CHECK, lay bang partition
select * from dba_tab_partitions where length(partition_name)=10;
--Lay cau truc bang sau ra lam mau
--2.Tao bang, index
CREATE TABLE user1.table1_NEW
(
PROFILE_ID NUMBER,
IMSI VARCHAR2(20 BYTE),
BDATE DATE
)
NOCOMPRESS
TABLESPACE DATA_IR
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
LOGGING
PARTITION BY RANGE (BDATE)
(
PARTITION DATA201708 VALUES LESS THAN (TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA_IR
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201709 VALUES LESS THAN (TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA_IR
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201710 VALUES LESS THAN (TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA_IR
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201711 VALUES LESS THAN (TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA_IR
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201712 VALUES LESS THAN (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA_IR
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING;
--3.Insert du lieu < 25/04/2017 vao bang user1.table1_NEW: Dữ liệu tĩnh
--alter table user1.table1 rename to table1_OLD;
insert /*+ append, nologging*/ into user1.table1_NEW
select /*+ parallel(irc,8)*/ * from user1.table1 irc
--where trunc(DATE_SEND) < to_date('25/04/2017','dd/mm/yyyy');
commit;
--184.887.353 row(s) inserted
-- Monitoring
SELECT distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,
s.sql_id, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.logon_time,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3
FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE S.STATUS = 'ACTIVE' AND S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER'
--and s.username not in ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE')
AND username LIKE 'SYS'
--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'
--and lower(ss.sql_text) like lower('%parallel%')
--and s.sid=1234
and s.machine like '%BINHTV%'
--and s.sql_id ='ccwg0nqr1zbu7'
ORDER BY username,sql_id;
--4.Doi ten bang cu, insert du lieu con lai
alter table user1.table1 rename to table1_OLD;
insert /*+ append, nologging*/ into user1.table1_NEW
select /*+ parallel(irc,8)*/ * from user1.table1_OLD irc
where trunc(DATE_SEND) >= to_date('25/04/2017','dd/mm/yyyy');
commit;
--12.352.414 row(s) inserted
--5.Tao lai index
CREATE INDEX user1.table1_NEW_I1 ON user1.table1_NEW
(DATE_SEND)
NOLOGGING
TABLESPACE INDX_IR
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
local PARALLEL 8 online;
alter index user1.table1_NEW_I1 noparallel;
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'user1'
,TabName => 'table1_NEW'
,Estimate_Percent => 10
,Method_Opt => 'FOR ALL COLUMNS SIZE 1'
,Degree => 4
,Cascade => FALSE
,No_Invalidate => FALSE);
END;
/
--6.Đối soát dữ liệu
select count(*) from user1.table1_NEW;
COUNT(*)
----------
197239767
1 row selected.
select count(*) from user1.table1_OLD;
COUNT(*)
----------
197239767
1 row selected.
--> Đã đủ dữ liệu giữa 2 bảng
--6.Doi ten bang NEW thanh bang chinh thuc
alter table user1.table1_NEW rename to table1;
-- 7.Drop bảng OLD: Chờ 3-5 ngày cho an toàn
drop table user1.table1_OLD;
