Partitioning – RANGE INTERVAL

1.) Create table:

drop table cmagno.test_pt_int;  
create table cmagno.test_pt_int (
  num number,
  text varchar2(1000),
  dt date
)
PARTITION BY RANGE (dt) INTERVAL (NUMTODSINTERVAL (1, 'DAY'))
( 
   PARTITION p1 VALUES LESS THAN (TO_DATE('08-04-2016', 'DD-MM-YYYY'))
); 

2.) Inserting Data:


declare

i number;
v_dt date;

begin

  for i in 1..100 loop    
      v_dt := trunc(sysdate+i);
      insert into cmagno.test_pt_int 
      values (i,'DATE: ' || to_char(v_dt) ,v_dt);
  end loop;
  commit;

end;
/

3.) Stats:

exec dbms_stats.gather_table_stats('CMAGNO','TEST_PT_INT');

4.) Partitions:

select count(*) 
from dba_tab_partitions 
where table_name='TEST_PT_INT';

            COUNT(*)
--------------------
                 101

1 row selected.

-- 1 row per partition

5.) Changing the interval, from 1 day to 1 week:

ALTER TABLE cmagno.TEST_PT_INT  SET INTERVAL(NUMTODSINTERVAL (7, 'DAY')) ;

16:17:50 SQL> L
  1  select OWNER, TABLE_NAME, PARTITIONING_TYPE, INTERVAL
  2* from dba_part_tables where table_name='TEST_PT_INT'
16:17:53 SQL> /

OWNER           TABLE_NAME                     PARTITION INTERVAL
--------------- ------------------------------ --------- ----------------------------------------
CMAGNO          TEST_PT_INT                    RANGE     NUMTODSINTERVAL (7, 'DAY')

1 row selected.

6.) Inserting Data:

declare

i number;
v_dt date;

begin

  for i in 102..202 loop
      v_dt := trunc(sysdate+i);
      insert into cmagno.test_pt_int 
      values (i,'DATE: ' || to_char(v_dt) ,v_dt);
  end loop;
  commit;

end;
/

7.) Stats:

exec dbms_stats.gather_table_stats('CMAGNO','TEST_PT_INT');

8.) Partitions:

select count(*), num_rows
from dba_tab_partitions
where table_name='TEST_PT_INT'
group by num_rows;

            COUNT(*)             NUM_ROWS
-------------------- --------------------
                 100                    1 --> Interval with 1 DAY
                   1                    6 --> Interval with 7 DAY
                   1                    4 --> Interval with 7 DAY
                  13                    7 --> Interval with 7 DAY
                   1                    0 --> Start partition

5 rows selected.


9.) Conclusion:

With INTERVAL option on partition tables is easy 
to adapt your data in a desired RANGE PARTITION.
You can start with a RANGE PARTITION by week and 
if you data are growing up fast you can change 
to RANGE PARTITION per day.

Partitioning – 1

--- CREATE TABLESPACE
CREATE TABLESPACE "USERS02" DATAFILE
 'C:\ORACLE\ORADATA\ORCL10\USERS02_1.DBF' SIZE 5242880
 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "USERS03" DATAFILE
 'C:\ORACLE\ORADATA\ORCL10\USERS03_1.DBF' SIZE 5242880
 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
--- PARTITIONED TABLE
CREATE TABLE CURSO.TESTE01
 (COD NUMBER,
 TEXTO VARCHAR2(100))
 PARTITION BY RANGE(COD)
 (PARTITION P1 VALUES LESS THAN (1) TABLESPACE USERS,
 PARTITION P2 VALUES LESS THAN (2) TABLESPACE USERS02,
 PARTITION P3 VALUES LESS THAN (3) TABLESPACE USERS,
 PARTITION P4 VALUES LESS THAN (MAXVALUE) TABLESPACE USERS02);
INSERT INTO CURSO.TESTE01 VALUES (0,'P1');
 INSERT INTO CURSO.TESTE01 VALUES (1,'P23');
 INSERT INTO CURSO.TESTE01 VALUES (3,'P23');
 INSERT INTO CURSO.TESTE01 VALUES (4,'P4');
 INSERT INTO CURSO.TESTE01 VALUES (99,'P99');
COMMIT;
--- CHECKING BLOCK AND PARTITIONS
SELECT
 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK,
 COD, TEXTO FROM CURSO.TESTE01;
--- CHECKING ROWS X PARTITIONS
select
 owner,
 segment_name     Name,
 segment_type     Tipo,
 block_id         c3,
 PARTITION_NAME,
 TABLESPACE_NAME
 from
 dba_extents
 where
 997 >= block_id
 and
 997 <= block_id+blocks AND SEGMENT_TYPE = 'TABLE PARTITION'
--- PARTITION BY HASH
CREATE TABLE CURSO.TESTE02
 (COD NUMBER, TEXTO VARCHAR2(100))
 PARTITION BY HASH (COD)
 PARTITIONS 2
 STORE IN (USERS,USERS02);
CREATE TABLE CURSO.TESTE03
 (COD NUMBER, TEXTO VARCHAR2(100))
 PARTITION BY HASH (COD)
 PARTITIONS 4
 STORE IN (USERS,USERS02);
 (USING 2 TABLESPACES)
--- PARTITION WITH LOB
CREATE TABLE curso.teste_lob
 ( idx NUMBER, txt CLOB )
 LOB ( txt ) STORE AS s_lob
 ( TABLESPACE users )
 PARTITION BY RANGE ( idx )
 ( PARTITION s_10 VALUES LESS THAN ( 10 )
 TABLESPACE users02
 LOB ( txt ) STORE AS st_10
 ( DISABLE STORAGE IN ROW
 TABLESPACE users )
 , PARTITION s_20 VALUES LESS THAN ( 20 )
 TABLESPACE users02
 ) ;
ALTER TABLE CURSO.TESTE01 ENABLE ROW MOVEMENT;
INSERT INTO CURSO.TESTE02 VALUES (0,'P1');
 INSERT INTO CURSO.TESTE02 VALUES (1,'P2');
 INSERT INTO CURSO.TESTE02 VALUES (2,'P3');
 INSERT INTO CURSO.TESTE02 VALUES (3,'P4');
COMMIT;
--- TEST WITH INDEX (LOCAL X GLOBAL)
-- LOCAL = PARTITIONED INDEX
 DROP INDEX CURSO.IX_TESTE01;
 CREATE INDEX CURSO.IX_TESTE01 ON CURSO.TESTE01(COD) TABLESPACE USERS
 LOCAL
 (PARTITION IX_01 TABLESPACE USERS,
 PARTITION IX_02 TABLESPACE USERS02,
 PARTITION IX_03 TABLESPACE USERS,
 PARTITION IX_04 TABLESPACE USERS02);
-- GLOBAL INDEX
 CREATE INDEX CURSO.IX_TESTE02 ON CURSO.TESTE01(texto) TABLESPACE USERS;
--- RENAMING PARTITION:
ALTER TABLE CURSO.TESTE02 RENAME PARTITION SYS_P21 TO HASH_01;
 ALTER TABLE CURSO.TESTE02 RENAME PARTITION SYS_P22 TO HASH_02;
--- MOVING PARTITION TO OTHER TABLESPACE
ALTER TABLE CURSO.TESTE01 MOVE PARTITION P2 TABLESPACE USERS;
--- MOVING INDEX PARTITION TO OTHER TABLESPACE
ALTER INDEX CURSO.IX_TESTE01 REBUILD PARTITION IX_02 TABLESPACE USERS;
--- TESTING ADD PARTITION
1.) ERROR TRYING ADDING A NEW PARTITION:
SQL> ALTER TABLE CURSO.TESTE01
 2  ADD PARTITION P4 VALUES LESS THAN (4) TABLESPACE USERS02;
 ADD PARTITION P4 VALUES LESS THAN (4) TABLESPACE USERS02
 *
 ERRO na linha 2:
 ORA-14074: limite da partição deve intercalar mais que o da última partição
2.) TRYING SPLIT PARTITION
SQL> ALTER TABLE CURSO.TESTE01
 2  split partition p99
 3  at (maxvalue) into
 4  (partition p4 ,
 5   partition p_max
 6  tablespace users);
 split partition p99
 *
 ERRO na linha 2:
 ORA-14080: partição não pode ser dividida ao longo do limite superior especificado
SOLUTION:
A.) EXPORT PARTITION
exp dbaprod/**** file=p99 tables=curso.teste01:p99 rows=y
B.) DROP PARTITION
alter table curso.teste01 drop partition p99;
C.) ADD NEW PARTITION
alter table curso.teste01 add partition p4 values less than (4)
 tablespace users02;
D.) CREATE AGAIN THE PARTITION
alter table curso.teste01 add partition p99 values less than (maxvalue)
 tablespace users02
E.) IMPORT
imp dbaprod/**** file=p99.DMP fromuser=curso touser=curso ignore=y rows=y indexes=n constraints=n grants=n
*** Merge | Coalesce ***
--Merge:
 alter table curso.teste01 merge partitions p2,p3 into partition p23;
-- COALESCE is usefull for HASH partitions the rows are equalized between the partitions
 Coalesce: ALTER TABLE PART COALESCE;
*** Split Partition
--- The partition P23 has values (1,2), divided in 2 parts:
ALTER TABLE curso.teste01 SPLIT
 PARTITION p23
 AT ( 2 ) INTO
 ( PARTITION p2
 , PARTITION p23
 TABLESPACE users ) ;
SQL> select * from curso.teste01 partition (p23);
COD TEXTO
 ---------- --------------------------------------------------------------------------------
 2 P3
 2 P3
SQL> select * from curso.teste01 partition (p2);
COD TEXTO
 ---------- --------------------------------------------------------------------------------
 1 P2
 1 P2
 1 P23
*** Merge Partitions
ALTER TABLE curso.teste01 MERGE
 PARTITIONS p2,p23
 INTO PARTITION p23
 TABLESPACE users;
select * from curso.teste01 partition (p23);
COD TEXTO
 ---------- --------------------------------------------------------------------------------
 1 P2
 1 P2
 1 P23
 2 P3
 2 P3
*** Exchange Partition
1.) the non-partitoned table must have the same structure
create table curso.exchange_table
 as
 select * from curso.teste01 where rownum<1;
2.)
ALTER TABLE curso.teste01
 EXCHANGE PARTITION p23
 WITH TABLE curso.exchange_table
 WITHOUT VALIDATION ;
3.)
ANALYZE TABLE curso.teste01
 PARTITION (p23)
 VALIDATE STRUCTURE
 INTO INVALID_ROWS ;