PARTITIONING

by

Adding partitions 11g/12c

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- SYNTAX :
 
ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> ADD PARTITION <PARTITION_NAME>
VALUES LESS THAN < HIGH_VALUE> TABLESPACE <TABLESPACE_NAME > < UPDATE GLOBAL
INDEXES(optional)>;
 
-- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present
 
ALTER TABLE
CMADMIN.DBACLASS ADD PARTITION DBACLASS_JAN VALUES
LESS THAN (TO_DATE('01-FEB-2016','DD-MON-YYYY')) TABLESPACE USERS UPDATE GLOBAL INDEXES;
 
-- In oracle 12c(new feature), we can add multiple partition in one command:
 
ALTER TABLE CMADMIN.DBACLASS ADD
PARTITION DBACLASS_JAN VALUES LESS THAN (TO_DATE('01-FEB-2021','DD-MON-YYYY')) TABLESPACE
USERS,
PARTITION DBACLASS_FEB VALUES LESS THAN (TO_DATE('01-MAR-2021','DD-MON-YYYY')) TABLESPACE
USERS,
PARTITION DBACLASS_MAR VALUES LESS THAN (TO_DATE('01-APR-2021','DD-MON-YYYY')) TABLESPACE
USERS,
UPDATE GLOBAL INDEXES;

Dropping partition 11g/12c

1
2
3
4
5
6
7
8
9
10
11
-- SYNTAX :
ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> DROP PARTITION <PARTITION_NAME> <UPDATE GLOBAL INDEXES(optional)>;
 
--- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present
 
ALTER TABLE CMADMIN.DBACLASS DROP PARTITION DBACLASS_JAN UPDATE GLOBAL INDEXES;
 
--- In oracle 12c, we can drop multiple partitions in one command
 
ALTER TABLE CMADMIN.DBACLASS DROP PARTITIONS DBACLASS_JAN, DBACLASS_FEB,
DBACLASS_MAR UPDATE GLOBAL INDEXES;

Truncate partitions

1
2
3
4
5
6
7
8
9
10
11
12
13
- SYNTAX :
 
ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> TRUNCATE PARTITION <
PARTITION_NAME> < UPDATE GLOBAL INDEXES(optional)>;
 
--- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present
 
ALTER TABLE CMADMIN.DBACLASS TRUNCATE PARTITION DBACLASS_JAN UPDATE GLOBAL INDEXES;
 
--- In oracle 12c, we can truncate multiple partitions in one command
 
ALTER TABLE CMADMIN.DBACLASS TRUNCATE PARTITIONS DBACLASS_JAN, DBACLASS_FEB,
DBACLASS_MAR UPDATE GLOBAL INDEXES;

Merge partition

1
2
3
4
5
6
7
8
9
10
-- MERGE PARTITION - FOR COMBINING MULTIPLE PARTITIONS TO A NEW ONE ( 12C ONWARS)
-- SYNTAX :
 
 ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> MERGE PARTITIONS <
PARTITION1,PARTITION2,...> < UPDATE GLOBAL INDEXES(optional)>;
 
--- NOTE: UPDATE GLOBAL INDEXES is required if GLOBAL INDEX is present
 
ALTER TABLE CMADMIN.DBACLASS MERGE PARTITIONS DBACLASS_JAN, DBACLASS_FEB,
DBACLASS_MAR INTO partition DBACLASS_Q1;

Make a partition ready only (12CR2)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- From oracle 12.2.0.1 Relase, we can make few partitions of a table read only.
 
SQL> alter table dbatest.ORDER_TAB modify partition CREATED_2105_P10 read only;
Table altered.
 
SQL> select partition_name,read_only from dba_tab_partitions where table_name='ORDER_TAB';
 
PARTITION_NAME READ
-------------------------------- ----
CREATED_2105_P10 YES
CREATED_2105_P11 NO
CREATED_2105_P12 NO
CREATED_2105_P8 NO
CREATED_2105_P9 NO
CREATED_MX NO
6 rows selected.

Split partition online (12cR2 only)

1
2
3
4
5
6
SQL> alter table order_tab split partition CREATED_MX into
(partition CREATED_2106_P2 VALUES LESS THAN (TO_DATE('01/03/2021', 'DD/MM/YYYY')),PARTITION
CREATED_MX) ONLINE;
Table altered.
 
SQL> select partition_name,read_only,high_value from dba_tab_partitions where table_name='ORDER_TAB';

Non-partition to partition

1
2
3
4
5
6
7
8
9
10
-- In Oracle 12cR2, we can convert non partitioned table to partitioned online using alter table command.
 
alter table BSSTDBA.ORDER_TAB modify
PARTITION BY RANGE (CREATED)
(partition created_2105_p8 VALUES LESS THAN (TO_DATE('01/09/2021', 'DD/MM/YYYY')),
partition created_2105_p9 VALUES LESS THAN (TO_DATE('01/10/2021', 'DD/MM/YYYY')),
partition created_2105_p10 VALUES LESS THAN (TO_DATE('01/11/2021', 'DD/MM/YYYY')),
partition created_2105_p11 VALUES LESS THAN (TO_DATE('01/12/2021', 'DD/MM/YYYY')),
partition created_2105_p12 VALUES LESS THAN (TO_DATE('01/01/2021', 'DD/MM/YYYY')),
PARTITION Created_MX VALUES LESS THAN (MAXVALUE)) ONLINE;

Rename a partition

1
ALTER TABLE employee RENAME PARTITION TAB3 TO TAB4;

Get row_count of partitions of a table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
set serverout on size 1000000
set verify off
declare
sql_stmt varchar2(1024);
row_count number;
cursor get_tab is
select table_name,partition_name
from dba_tab_partitions
where table_owner=upper('&&TABLE_OWNER') and table_name='&&TABLE_NAME';
begin
dbms_output.put_line('Checking Record Counts for table_name');
dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');
dbms_output.put_line('....');
for get_tab_rec in get_tab loop
BEGIN
sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name
||' partition ( '||get_tab_rec.partition_name||' )';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name
||'('||get_tab_rec.partition_name||')',50)
||' '||TO_CHAR(row_count)||' rows.');
exception when others then
dbms_output.put_line
('Error counting rows for table '||get_tab_rec.table_name);
END;
end loop;
end;
/
set verify on

Find the table partition keys

1
2
3
4
5
6
7
8
9
10
11
--- describes the partitioning key columns for all partitioned objects of a schema
set pagesize 200
set lines 200
set long 999
col owner for a12
col name for a20
col object_type for a20
col column_name for a32
SELECT owner, NAME, OBJECT_TYPE,column_name
FROM dba_part_key_columns where owner='&OWNER'
ORDER BY owner, NAME;

Move partition to new tablespace

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
- Move a single partition to a new tablespace
 
ALTER TABLE SCOTT.EMP MOVE PARTITION EMP_Q1 TABLESPACE
TS_USERS;
 
--- Move a single partition to a new tablespace WITH PARALLEL
 
ALTER TABLE SCOTT.EMP MOVE PARTITION
EMP_Q1 TABLESPACE TS_USERS PARALLEL(DEGREE 4) NOLOGGING;
 
- Dynamic script to move all partitions of a table
 
select 'ALTER TABLE '||TABLE_OWNER ||'.'||table_name||' MOVE
PARTITION '||partition_name||' TABLESPACE TS_USERS PARALLEL(DEGREE 4) NOLOGGING;'
from dba_tab_partitions where table_name='&TABLE_NAME' and table_owner='&SCHEMA_NAME';