-- Cách sử dụng: Copy vào TOAD hoặc SQL Navigator hoặc SQL Devloper để chạy script sau:
-- Áp dụng cho bảng lớn, dữ liệu tĩnh trong khoảng thời gian nhất định, còn dữ liệu động chuyển sau
-- Thu tuc
chuyen bang TAB1 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 partition theo thang (vao TOAD --> F4)
BINHTV TAB_TEMPLATE
--2.TAO
BANG
-- INDEX
tao sau de dam bao insert du lieu nhanh chong
CREATE
TABLE BINHTV.TAB1_NEW
(
DATE_SEND DATE NOT NULL,
TYPE VARCHAR2(1 BYTE) NOT NULL,
SERVICE_TYPE VARCHAR2(30 BYTE),
NUMBER_RECORDS NUMBER(12),
DURATION NUMBER(10),
CHARGE_USD NUMBER(21,5),
CELL_ID VARCHAR2(20 BYTE),
VOLUME_UP NUMBER(15),
VOLUME_DOWN NUMBER(15),
TADIGNAME VARCHAR2(5 BYTE),
NETWORK_NAME VARCHAR2(50 BYTE),
COUNTRY VARCHAR2(50 BYTE),
IMSI VARCHAR2(15 BYTE),
SUPPLEMENTAL LOG DATA (ALL) COLUMNS,
SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS,
SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,
SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS
)
NOCOMPRESS
TABLESPACE
DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
LOGGING
PARTITION
BY RANGE (DATE_SEND)
(
PARTITION DATA201601 VALUES LESS THAN
(TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201602 VALUES LESS THAN
(TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201603 VALUES LESS THAN
(TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201604 VALUES LESS THAN
(TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201605 VALUES LESS THAN
(TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201606 VALUES LESS THAN
(TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201607 VALUES LESS THAN
(TO_DATE(' 2016-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201608 VALUES LESS THAN
(TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201609 VALUES LESS THAN
(TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201610 VALUES LESS THAN
(TO_DATE(' 2016-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201611 VALUES LESS THAN
(TO_DATE(' 2016-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201612 VALUES LESS THAN
(TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201701 VALUES LESS THAN
(TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201702 VALUES LESS THAN
(TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201703 VALUES LESS THAN
(TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201704 VALUES LESS THAN
(TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201705 VALUES LESS THAN
(TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201706 VALUES LESS THAN
(TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION DATA201707 VALUES LESS THAN
(TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE DATA
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
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
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
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
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
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
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 (du lieu khong thay doi) VAO BANG BINHTV.TAB1_NEW
insert /*+
append, nologging*/ into BINHTV.TAB1_NEW
select /*+
parallel(irc,8)*/ * from BINHTV.TAB1 irc
where trunc(DATE_SEND) <
to_date('25/04/2017','dd/mm/yyyy');
commit;
--1848.87.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 NOT DU LIEU CON LAI
alter
table BINHTV.TAB1 rename to TAB1_OLD;
insert /*+
append, nologging*/ into BINHTV.TAB1_NEW
select /*+
parallel(irc,8)*/ * from BINHTV.TAB1_OLD irc
where trunc(DATE_SEND) >=
to_date('25/04/2017','dd/mm/yyyy');
commit;
--123.52.414
row(s) inserted
--5.TAO
LAI INDEX
CREATE
INDEX BINHTV.TAB1_NEW_I1 ON BINHTV.TAB1_NEW
(DATE_SEND)
NOLOGGING
TABLESPACE
INDX
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 BINHTV.TAB1_NEW_I1 noparallel;
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'BINHTV'
,TabName => 'TAB1_NEW'
,Estimate_Percent => 10
,Method_Opt => 'FOR ALL COLUMNS SIZE 1'
,Degree => 4
,Cascade => FALSE
,No_Invalidate => FALSE);
END;
/
--Gather
index
--6.SO
SANH DU LIEU: Dam bao khop nhau
select
count(*) /*+parallel(a,8)*/ from BINHTV.TAB1_NEW a;
COUNT(*)
----------
197239767
1 row
selected.
select
count(*) /*+paralellel(a,8)*/ from BINHTV.TAB1_OLD a;
COUNT(*)
----------
197239767
1 row
selected.
--7.DOI
TEN BANG NEW THANH BANG CHINH THUC SU DUNG
alter
table BINHTV.TAB1_NEW rename to TAB1;
