1.CHECK
--Check các partition đã tạo cho DB
select table_name,partition_name,TABLESPACE_NAME,last_analyzed,high_value from dba_tab_partitions where partition_name LIKE '%2012%' and (table_name, partition_name) in (
SELECT table_name, MAX(partition_name)--, tablespace_name
FROM dba_tab_partitions
WHERE partition_name LIKE '%2012%'
--Check partition đã tạo theo schema
select table_name,partition_name,TABLESPACE_NAME,last_analyzed,high_value from dba_tab_partitions where table_owner = 'CDR_OWNER' AND partition_name LIKE '%2009%'
and (table_name, partition_name) in (
SELECT table_name, MAX(partition_name)--, tablespace_name
FROM dba_tab_partitions
WHERE table_owner = 'CDR_OWNER' AND partition_name LIKE '%2009%'
GROUP BY TABLE_NAME);
Select * from dba_tab_subpartitions;
Select * from dba_segments;
-- Size table
select segment_name,round(sum(bytes)/1024/1024/1024,2) GB from dba_segments where owner='CUS_OWNER' group by segment_name order by GB desc;
-- Check truong partition
SELECT *
FROM dba_part_key_columns
WHERE NAME in
( 'ACTION_AUDIT')
ORDER BY NAME;
--Check sub-partition thiếu
select table_owner, table_name, MAX (subpartition_name) from dba_tab_subpartitions
where subpartition_name like '%2022%'
GROUP BY table_owner, table_name
having MAX (subpartition_name) not in ('DATA2022','DATA202212','DATA20221231')
order by 1,2
;
--Check partition thiếu
SELECT table_owner, table_name, MAX (PARTITION_NAME) FROM dba_tab_partitions
WHERE PARTITION_NAME LIKE '%2022%' and table_name not like '%$%'
GROUP BY table_owner, table_name
having MAX (PARTITION_NAME) not in ('DATA2022','DATA202212','DATA20221231')
order by 1,2;
2.SELECT DỮ LIỆU
select * from tp1 partition(data20180816);
select * from tp1 where p_date>=sysdate-1 and p_date<=sysdate;
3.DML
DELETE FROM sales PARTITION (sales_q1_1998)
WHERE amount_sold > 1000
4.CÁC THAO TÁC DDL
CREATE TABLE PARTITION --Range Interval
CREATE TABLE salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
PARTITION BY RANGE(s_saledate)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) STORE IN (tbs1,tbs2,tbs3,tbs4)
(PARTITION sal05q1 VALUES LESS THAN (TO_DATE('01-APR-2005', 'DD-MON-YYYY')) TABLESPACE tbs1,
PARTITION sal05q2 VALUES LESS THAN (TO_DATE('01-JUL-2005', 'DD-MON-YYYY')) TABLESPACE tbs2,
PARTITION sal05q3 VALUES LESS THAN (TO_DATE('01-OCT-2005', 'DD-MON-YYYY')) TABLESPACE tbs3,
PARTITION sal05q4 VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY')) TABLESPACE tbs4,
PARTITION sal06q1 VALUES LESS THAN (TO_DATE('01-APR-2006', 'DD-MON-YYYY')) TABLESPACE tbs1,
PARTITION sal06q2 VALUES LESS THAN (TO_DATE('01-JUL-2006', 'DD-MON-YYYY')) TABLESPACE tbs2,
PARTITION sal06q3 VALUES LESS THAN (TO_DATE('01-OCT-2006', 'DD-MON-YYYY')) TABLESPACE tbs3,
PARTITION sal06q4 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY')) TABLESPACE tbs4);
From <https://docs.oracle.com/database/121/VLDBG/GUID-EAFD703C-EFA9-4819-85BD-79F63B761A96.htm#VLDBG1269>
--2.1.Tạo bảng partiton theo năm
CREATE TABLE binhtv.ACTION_AUDIT
(
ACTION_AUDIT_ID NUMBER(20),
ISSUE_DATETIME DATE,
col3 varchar2(50)
)
TABLESPACE DATA
PARTITION BY RANGE (ISSUE_DATETIME)
(
PARTITION DATA2003 VALUES LESS THAN (TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2003,
PARTITION DATA2004 VALUES LESS THAN (TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2004)
-- 2.1.1.Add partiton theo năm, tháng
CREATE TABLE binhtv.ACTION_AUDIT
(
ACTION_AUDIT_ID NUMBER(20),
ISSUE_DATETIME DATE,
col3 varchar2(50)
)
TABLESPACE DATA
PARTITION BY RANGE (ISSUE_DATETIME)
(
PARTITION DATA2006 VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2006,
PARTITION DATA2007 VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2007,
PARTITION DATA200801 VALUES LESS THAN (TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2008,
PARTITION DATA200802 VALUES LESS THAN (TO_DATE(' 2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2008,
PARTITION DATA200803 VALUES LESS THAN (TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2008,
PARTITION DATA200804 VALUES LESS THAN (TO_DATE(' 2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2008,
PARTITION DATA200805 VALUES LESS THAN (TO_DATE(' 2008-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2008,
PARTITION DATA200806 VALUES LESS THAN (TO_DATE(' 2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2008,
PARTITION DATA200807 VALUES LESS THAN (TO_DATE(' 2008-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION DATA200808 VALUES LESS THAN (TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2008,
PARTITION DATA200809 VALUES LESS THAN (TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2008,
PARTITION DATA200810 VALUES LESS THAN (TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2008,
PARTITION DATA200811 VALUES LESS THAN (TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2008,
PARTITION DATA200812 VALUES LESS THAN (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2008
)
-- Add partition
-- Rebuild index
select 'alter index user1.app_log_i1 rebuild partition ' || partition_Name || ' tablespace INDX2016 parallel 8 nologging online;' from dba_ind_partitions where index_name like 'app_log_I1';
-- Set nologging noparallel
alter index user1.log_i1 nologging noparellel;
-- Partition-level compression.
CREATE TABLE test_tab_2 (
id NUMBER(10) NOT NULL,
description VARCHAR2(50) NOT NULL,
created_date DATE NOT NULL
)
PARTITION BY RANGE (created_date) (
PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')) COMPRESS,
PARTITION test_tab_q2 VALUES LESS THAN (TO_DATE('01/04/2008', 'DD/MM/YYYY')) COMPRESS FOR DIRECT_LOAD OPERATIONS,
PARTITION test_tab_q3 VALUES LESS THAN (TO_DATE('01/07/2008', 'DD/MM/YYYY')) COMPRESS FOR ALL OPERATIONS,
PARTITION test_tab_q4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS
);
SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions;
TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------------
TEST_TAB_2 TEST_TAB_Q1 ENABLED DIRECT LOAD ONLY
TEST_TAB_2 TEST_TAB_Q2 ENABLED DIRECT LOAD ONLY
TEST_TAB_2 TEST_TAB_Q3 ENABLED FOR ALL OPERATIONS
TEST_TAB_2 TEST_TAB_Q4 DISABLED
-- Theo QUÝ
CREATE TABLE REPDB.SALES
(
PROD_ID NUMBER(6),
CUST_ID NUMBER,
TIME_ID DATE,
CHANNEL_ID CHAR(1 BYTE),
PROMO_ID NUMBER(6),
QUANTITY_SOLD NUMBER(3),
AMOUNT_SOLD NUMBER(10,2),
SUPPLEMENTAL LOG DATA (ALL) COLUMNS
)
NOCOMPRESS
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (TIME_ID)
(
PARTITION SALES_Q1_2006 VALUES LESS THAN (TO_DATE(' 2006-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 SALES_Q2_2006 VALUES LESS THAN (TO_DATE(' 2006-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 SALES_Q3_2006 VALUES LESS THAN (TO_DATE(' 2006-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 SALES_Q4_2006 VALUES LESS THAN (TO_DATE(' 2007-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;
-- 2.2.Theo thang
--DROP TABLE user1.app_log CASCADE CONSTRAINTS;
CREATE TABLE user1.app_log
(
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;
-- Tạo index
CREATE INDEX user1.app_log_I1 ON user1.app_log
(SUB_ID )
NOLOGGING
NOPARALLEL local online;
-- Rebuild index
select 'alter index user1.app_log_i1 rebuild partition ' || partition_Name || ' tablespace INDX2016 parallel 8 nologging online;' from dba_ind_partitions where index_name like 'app_log_I1';
-- Set nologging noparallel
alter index user1.app_log_i1 nologging noparellel;
-- 2.4.Theo ngay
-- 1.Tao bang
CREATE TABLE user1.table1
(imsi VARCHAR2(15) NOT NULL,
issue_datetime DATE NOT NULL,
sta_datetime DATE NOT NULL,
acc_profile VARCHAR2(20),
credit_charged NUMBER(10,2)
)
TABLESPACE TMP_DUMP
PARTITION BY RANGE (issue_datetime)
(
PARTITION DATA20161027 VALUES LESS THAN (TO_DATE(' 2016-10-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE TMP_DUMP
)
--2.Add them partitioin
DECLARE
v_nam NUMBER (4) := 2016; --2014
v_tablename VARCHAR2 (50) := 'table1';
v_date_from date := to_date('28/10/2016','dd/mm/yyyy');
v_date_to date := to_date('31/12/2016','dd/mm/yyyy');
v_numday number(5);
v_tablespace varchar2(50):='TMP_DUMP';
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;
--3.Tao index
create index user1.table1_I1 on user1.table1(calling_number) local parallel 8 nologging online;
alter index user1.table1_I1 nologging noparallel;
--3.Rebuild index ve tablespace INDX
DECLARE
v_date_from date := to_date('01/01/2017','dd/mm/yyyy');
v_date_to date := to_date('31/12/2017','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 a.owner='user1' and a.table_name='MF_SMS_CALL_TT' 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='user1' and a.index_name not like '%$%' and a.partition_name like '%20171231'
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;
3. ADD PARTITON CHO BANG
--3.1.Add partition theo nam
alter table user1.table1 add PARTITION DATA2016 VALUES LESS THAN (TO_DATE('2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) LOGGING TABLESPACE DATA2016;
--3.2.ADD PARTITION --theo thang, 1 bang
alter table table1 add PARTITION DATA201612 VALUES LESS THAN (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) LOGGING TABLESPACE DATA2016;
DECLARE
v_nam NUMBER (4) := 2010;
v_tablename VARCHAR2 (50) := 'ACTION_AUDIT';
v_thang_from NUMBER (2) := 10;
v_thang_to NUMBER (2) := 12;
v_tablespace varchar2(50):='DATA';
BEGIN
FOR i IN v_thang_from .. (v_thang_to-1)
LOOP
DBMS_OUTPUT.put_line ('alter table '|| v_tablename || ' 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||to_char(LPAD (i, 2, '0'))||';');
END LOOP;
DBMS_OUTPUT.put_line ('alter table '|| v_tablename || ' 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||to_char(v_thang_to)||';');
END;
-- Rebuild index
DECLARE
v_nam varchar2(4) := '2017';
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='user1' and table_name='table1'
group by table_owner,table_name having max(partition_name) like '%'||2017||'%' and length(max(partition_name))=10 order 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||' nologging parallel 8 online;');
END LOOP; END LOOP;
end loop;
END;
-- set nologing noparallel
--3.3.ADD PARTITION --theo ngay, 1 bang
alter table table1 add PARTITION DATA20160101 VALUES LESS THAN (TO_DATE(' 2016-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) LOGGING TABLESPACE DATA201601;
DECLARE
v_nam NUMBER (4) := 2013;
v_owner varchar2 (50) := 'user1';
v_tablename VARCHAR2 (50) := 'ACTION_AUDIT';
v_date_from date := to_date('27/11/2013','dd/mm/yyyy');
v_date_to date := to_date('31/12/2013','dd/mm/yyyy');
v_numday number(5);
v_tablespace varchar2(50):='DATA';
BEGIN
v_numday:=v_date_to-v_date_from;
FOR i IN 0 .. v_numday
LOOP
DBMS_OUTPUT.put_line ('alter table '||v_owner||'.'|| 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 DATA'|| to_char(v_date_from+i,'YYYYMM')||';');
END LOOP;
END;
-- Rebuild index
DECLARE
v_date_from date := to_date('01/01/2017','dd/mm/yyyy');
v_date_to date := to_date('31/12/2017','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='user1' and b.table_name='ACTION_AUDIT' and a.index_name not like '%$%' and b.partition_name like '%20171231' 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 noparallel nologging
select distinct a.index_owner, a.index_name from DBA_ind_partitions a where a.index_owner='CUS_OWNER' and a.index_name not like '%$%' and a.partition_name like '%20171231'
order by a.index_owner,a.index_name;
DECLARE
cursor c1 is
select distinct a.index_owner, a.index_name from DBA_ind_partitions a where a.index_owner='CUS_OWNER' and a.index_name not like '%$%' and a.partition_name like '%20171231'
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 || ' noparallel;');
END LOOP;
END;
Khi add partition cẩn thận bị event "library cache lock" có thể gây cao tải, treo DB do đó cần giám sát chặt chẽ và cao tải quá cần chạy sau giờ hành chính:
SELECT /*1.ActiveSession*/ 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/*,S.PREV_EXEC_START*/, s.logon_time, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,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','GGADMIN')
--AND username in 'PAYMENT'
--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'
and lower(ss.sql_text) not like lower('%***%')
--and s.sid=5923
--and s.machine like '%BINHTV%'
--and s.sql_id ='ccwg0nqr1zbu7'
ORDER BY username,sql_id
--order by S.PREV_EXEC_START;
;
17. Đánh partition tự động
– Vấn đề: Đối với các bảng dữ liệu có đánh partition theo tháng hoặc theo ngày, người QTHT thường xuyên phải đánh partition, nếu quên dữ liệu insert vào sẽ bị lỗi và có thể dẫn đến lỗi toàn hệ thống.
– Giải pháp: Để giải quyết vấn đề trên ta thường viết thủ tục đánh partition tự động cho bảng và đặt scheduler để chạy định kỳ. Việc đánh partition không ảnh hưởng tới các thao tác insert, select, update, delete.
– Tư tưởng: Để đảm bảo có thể đánh partition một cách tự động ta phải đặt tên partition theo một số quy ước:
Trong tên partition phải chứa thông tin về ngày tháng của dữ liệu
Phần đầu của tên partition phải giống nhau
– Dựa vào thông tin partition trong tên ta xác định thời gian cần đánh partition tiếp theo
Code:
PROCEDURE proc_create_partition
IS
CURSOR c_partition
IS
SELECT object_name,
MAX (SUBSTR (subobject_name,
LENGTH (subobject_name) - 5,
LENGTH (subobject_name)
)
) AS sub_partition
FROM user_objects
WHERE object_type = 'TABLE PARTITION'
AND object_name NOT LIKE 'BIN$%'
AND object_name NOT IN ('AGENT_COUNTER')
GROUP BY object_name;
v_loop_str VARCHAR (3000);
v_date DATE;
p_error VARCHAR (2000);
BEGIN
FOR v_partition IN c_partition
LOOP
v_date := TO_DATE (v_partition.sub_partition, 'yyMMdd');
WHILE v_date <=
ADD_MONTHS (TO_DATE (v_partition.sub_partition, 'yyMMdd'), 1)
LOOP
BEGIN
v_date := v_date + 1;
v_loop_str := '';
v_loop_str :=
'alter table '
|| v_partition.object_name
|| ' add partition DATA20'
|| TO_CHAR (v_date, 'yyMMdd')
|| ' values less than (to_date(''20'
|| TO_CHAR (v_date, 'yyMMdd')
|| ''',''yyyyMMdd''))';
DBMS_OUTPUT.put_line (v_loop_str);
EXECUTE IMMEDIATE v_loop_str;
v_loop_str := '';
EXCEPTION
WHEN OTHERS
THEN
p_error := 'Loi xay ra khi them partition: ' || SQLERRM;
DBMS_OUTPUT.put_line (p_error);
END;
END LOOP;
END LOOP;
END;
From <http://how.vndemy.com/databases/238-mot-ky-nang-lam-viec-voi-oracle/>
4.Rename partition alter table user1.table1 rename PARTITION DATA20090722 to DATA20090721;
--- script rename partition -------
DECLARE
v_nam NUMBER (4) := 2013;
v_owner varchar2 (50) := 'user1';
v_tablename VARCHAR2 (50) := 'table1';
v_date_from date := to_date('27/11/2013','dd/mm/yyyy');
v_date_to date := to_date('31/12/2013','dd/mm/yyyy');
v_numday number(5);
v_tablespace varchar2(50):='REGB_REQ_DATA01';
BEGIN
v_numday:=v_date_to-v_date_from;
FOR i IN 0 .. v_numday
LOOP
DBMS_OUTPUT.put_line ('alter table '||v_owner||'.'|| v_tablename || ' rename PARTITION DATA'
||to_char(v_date_from+i,'YYYYMMDD')||' to DATA'|| to_char(v_date_from+i-1,'YYYYMMDD')||';');
END LOOP;
END;
-- Move Partition
DECLARE
v_nam NUMBER (4) := 2014;
v_tablename VARCHAR2 (50) := 'table1';
v_thang_from NUMBER (2) := 01;
v_thang_to NUMBER (2) := 12;
v_tablespace varchar2(50):='DATA';
BEGIN
FOR i IN v_thang_from .. (v_thang_to-1)
LOOP
DBMS_OUTPUT.put_line ('alter table '|| v_tablename || ' move PARTITION DATA'||v_nam||LPAD (i, 2, '0')||' LOGGING TABLESPACE '||v_tablespace||v_nam||to_char(LPAD (i, 2, '0'))||';');
END LOOP;
DBMS_OUTPUT.put_line ('alter table '|| v_tablename || ' move PARTITION DATA'||v_nam||LPAD (to_char(v_thang_to), 2, '0')||' LOGGING TABLESPACE '||v_tablespace||v_nam||to_char(v_thang_to)||';');
END;
----5.Split partition--------
ALTER TABLE user1.table1
SPLIT PARTITION DATA20140410 AT (TO_DATE(' 2014-04-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
INTO (PARTITION DATA20140409,
PARTITION DATA20140410)
UPDATE GLOBAL INDEXES;
ALTER TABLE SPLIT PARTITION p0 INTO
(PARTITION p01 VALUES LESS THAN (25),
PARTITION p02 VALUES LESS THAN (50),
PARTITION p03 VALUES LESS THAN (75),
PARTITION p04);
From <https://docs.oracle.com/database/121/VLDBG/GUID-01C14320-0D7B-48BE-A5AD-003DDA761277.htm>
-- 6.Move partition ---
-- Với bảng cần online luôn: Chuyen partition va rebuild index luon
select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name|| ' tablespace tbs_name nologging parallel 8;', partition_name, tablespace_name from dba_tab_partitions
where table_owner='user1' and table_name = 'table1' and partition_name like 'DATA201401%'
union
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name
|| ' tablespace tbs_name nologging parallel 8 online;', partition_name, tablespace_name from dba_ind_partitions
where index_name in
(select index_name from dba_indexes where
table_owner='user1' and table_name = 'table1')
and partition_name like 'DATA201401%'
union
select 'alter index '||owner||'.'||index_name||' noparallel;', null, null from dba_indexes where
table_owner='user1' and table_name = 'table1'
order by 2, 1 desc, 3;
-- Move từng partition xong rebuild index partition luôn
set serverout on size 1000000
declare
cursor c_tab_partitions is
select * from dba_tab_partitions where tablespace_name='DUMP' and table_owner='BINHTV';
cursor c_ind_partitions is
select * from dba_ind_partitions where status='UNUSABLE';
cursor c_tables is
select * from dba_tables where tablespace_name='DUMP' and partitioned='NO';
cursor c_indexes is
select * from dba_indexes where status='UNUSABLE';
sql_move_tab_partition varchar2(1000);
sql_rebuild_ind_partition varchar2(1000);
begin
-- Chuyen bang partition va rebuild index partition UNUSABLE ngay sau tung partition
for r1 in c_tab_partitions
loop
sql_move_tab_partition := 'alter table ' || r1.table_owner || '.' || r1.table_name || ' move partition ' || r1.partition_name ||' tablespace USERS parallel 8 nologging ';
dbms_output.put_line(sql_move_tab_partition);
execute immediate sql_move_tab_partition;
for r2 in (select * from dba_ind_partitions where status='UNUSABLE')
loop
dbms_output.put_line('Before sql_rebuild_ind_partition');
sql_rebuild_ind_partition := 'alter index ' || r2.index_owner || '.' || r2.index_name || ' rebuild partition ' || r2.partition_name || ' nologging parallel 8 online';
dbms_output.put_line(sql_rebuild_ind_partition);
dbms_output.put_line('Before execute sql_rebuild_ind_partition');
execute immediate sql_rebuild_ind_partition;
end loop;
end loop;
send_sms_binhtv('Finish to move BINHTV from DUMP to users');
end;
-- Chuyen bang non-partition va rebuild index non-partition UNUSABLE ngay sau 1 partition
/* for
for c1 in (select owner,segment_name, segment_type,decode(segment_type,'TABLE','MOVE','REBUILD') operation,
case when owner='BAOCAO' and segment_type='TABLE' then 'usr_d_01'
when owner='BAOCAO' and segment_type='INDEX' then 'usr_x_01'
when owner='THAMSOHOA' and segment_type='TABLE' then 'usr_d_02'
when owner='THAMSOHOA' and segment_type='INDEX' then 'usr_x_02' end tablespace_name
from dba_segments
where owner in ('BAOCAO','THAMSOHOA')
and segment_type in ('TABLE','INDEX') ) loop
begin
dbms_output.put_line('alter '||c1.segment_type||'.'||c1.owner||'.'||
c1.segment_name||' '||c1.operation||' tablespace '||c1.tablespace_name);
/* Once you tested and ok with the output of this procedure as per your database requirements
remove the comments on the below execute immediate statement and run the procedure to perform the task */
/* execute immediate 'alter '||c1.segment_type||' '||c1.owner||'.'||
c1.segment_name||' '||c1.operation||' tablespace '||c1.tablespace_name; */
-- exception
-- when others then
-- dbms_output.put_line(c1.owner||' '||c1.segment_name||' '||c1.segment_type||' '||sqlerrm);
-- end;
-- end loop;*/
-- Chuyển partition depot2 thuộc bảng (ko quan tâm đang ở tablespace nào) sang tablespace ts094
ALTER TABLE parts MOVE PARTITION DATA201301 TABLESPACE DATA2013 nologging parallel 8;
ALTER TABLE parts MOVE PARTITION DATA201301 TABLESPACE DATA2013;
procedure mvTabPart (a_tname in varchar2, a_destTS in varchar2) is
cursor pCur(vTname varchar2, vTspName varchar2) is
select table_name, partition_name
from user_tab_partitions
where table_name = vTname
and tablespace_name not like vTspName
order by partition_position desc;
begin
for pRow in pCur(a_tname, a_destTS) loop
sqlStmnt := 'alter table '||pRow.table_name||
' move partition '||pRow.partition_name||
' tablespace '||a_destTS;
execute immediate sqlStmnt;
end loop;
end mvTabPart;
From <https://stackoverflow.com/questions/1998627/how-do-you-move-a-partitioned-table-from-one-tablespace-to-another-in-oracle-11g>
-- 7.DROP PARTITON
ALTER TABLE sales DROP PARTITION dec98;
--8.TRUNCATE PARTITON
TRUNCATE PARTITION p1 DROP STORAGE;
--9.COMPRESS PARTITION
ALTER TABLE user1.table1 MOVE PARTITION DATA20120229 COMPRESS TABLESPACE DATA201202 PARALLEL 12;
--10.Move and compress
ALTER TABLE user1.table1 MOVE PARTITION DATA20130701 COMPRESS TABLESPACE DATA201307 PARALLEL 8;
Set table default partition tablespace so new partitions are created there:
procedure setDefTabPart (a_tname in varchar2, a_destTS in varchar2) is
cursor tCur(vTname varchar2) is
select table_name
from user_part_tables
where table_name = vTname;
begin
for tRow in tCur(a_tname) loop
sqlStmnt := 'alter table '||tRow.table_name||
' modify default attributes '||
' tablespace '||a_destTS;
execute immediate sqlStmnt;
end loop;
end setDefNdxPart;
3) Set index default partition tablespace so new index partitions (if any) are created where you want them:
procedure setDefNdxPart (a_tname in varchar2, a_destTS in varchar2) is
cursor iCur(vTname varchar2) is
select index_name
from user_part_indexes
where index_name in (select index_name
from user_indexes where table_name = vTname);
begin
for iRow in iCur(a_tname) loop
sqlStmnt := 'alter index '||iRow.index_name||
' modify default attributes '||
' tablespace '||a_destTS;
execute immediate sqlStmnt;
end loop;
end setDefNdxPart;
From <https://stackoverflow.com/questions/1998627/how-do-you-move-a-partitioned-table-from-one-tablespace-to-another-in-oracle-11g>
When partitioning table Use partitioning:
• When a table reaches a "large" size. Large being defined relative to your environment. Tables greater than 2GB should always be considered for partitioning.
• When performance benefits outweigh the additional management issues related to partitioning.
• When the archiving of data is on a schedule and is repetitive. For instance, data warehouses usually hold data for a specific amount of time (rolling window). Old data is then rolled off to be archived
From <http://www.dba-oracle.com/t_partitioning_tables.htm>
