Thứ Ba, 1 tháng 12, 2020

Quản lý bảng partition_FULL trong Oracle Database

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%'
GROUP BY TABLE_NAME);
--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;

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
2. 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),
  SHOP_CODE        VARCHAR2(20 BYTE),
  ISSUE_DATETIME   DATE,
  SUB_ID           NUMBER(20),
  ACTION_ID        VARCHAR2(2 BYTE),
  USER_NAME        VARCHAR2(50 BYTE),
  PC               VARCHAR2(20 BYTE),
  REASON_ID        NUMBER(10),
  DESCRIPTION      VARCHAR2(4000 BYTE),
  FILE_ID          NUMBER(20),
  STATUS           NUMBER(1)                    DEFAULT 0,
  FREE_SIM         VARCHAR2(1 BYTE),
  EMP_CODE         VARCHAR2(15 BYTE),
  VALID            VARCHAR2(1 BYTE)
)
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),
  SHOP_CODE        VARCHAR2(20 BYTE),
  ISSUE_DATETIME   DATE,
  SUB_ID           NUMBER(20),
  ACTION_ID        VARCHAR2(2 BYTE),
  USER_NAME        VARCHAR2(50 BYTE),
  PC               VARCHAR2(20 BYTE),
  REASON_ID        NUMBER(10),
  DESCRIPTION      VARCHAR2(4000 BYTE),
  FILE_ID          NUMBER(20),
  STATUS           NUMBER(1)                    DEFAULT 0,
  FREE_SIM         VARCHAR2(1 BYTE),
  EMP_CODE         VARCHAR2(15 BYTE),
  VALID            VARCHAR2(1 BYTE)
)
TABLESPACE DATA
PARTITION BY RANGE (ISSUE_DATETIME)
  PARTITION DATA20006 VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2006,
PARTITION DATA20007 VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2007,
PARTITION DATA20080101 VALUES LESS THAN (TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE DATA2008,
PARTITION DATA20080102 VALUES LESS THAN (TO_DATE(' 2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE DATA2008,
PARTITION DATA20080103 VALUES LESS THAN (TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE DATA2008,
PARTITION DATA20080104 VALUES LESS THAN (TO_DATE(' 2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE DATA2008,
PARTITION DATA20080105 VALUES LESS THAN (TO_DATE(' 2008-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE DATA2008,
PARTITION DATA20080106 VALUES LESS THAN (TO_DATE(' 2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE DATA2008,
PARTITION DATA20080107 VALUES LESS THAN (TO_DATE(' 2008-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION DATA20080108 VALUES LESS THAN (TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE DATA2008,
PARTITION DATA20080109 VALUES LESS THAN (TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE DATA2008,
PARTITION DATA20080110 VALUES LESS THAN (TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE DATA2008,
PARTITION DATA20080111 VALUES LESS THAN (TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  TABLESPACE DATA2008,
PARTITION DATA20080112 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;
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> 



KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH: 📧 Mail: binhoracle@gmail.com ☎️ Mobile: 0902912888 ⚡️ Skype: tranbinh48ca 👨 Facebook: https://www.facebook.com/BinhOracleMaster 👨 Inbox Messenger: https://m.me/101036604657441 (profile) 👨 Fanpage: https://www.facebook.com/tranvanbinh.vn 👨 Inbox Fanpage: https://m.me/tranvanbinh.vn 👨👩 Group FB: https://www.facebook.com/groups/OracleDBAVietNam 👨 Website: http://www.tranvanbinh.vn 👨 Blogger: https://tranvanbinhmaster.blogspot.com 🎬 Youtube: http://bit.ly/ytb_binhoraclemaster 👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi 👨 Linkin: https://www.linkedin.com/in/binhoracle 👨 Twitter: https://twitter.com/binhoracle 👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master