PARTITIONING
4 min readAdding partitions 11g/12c
-- 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
-- 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
- 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
-- 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)
-- 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)
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
-- 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
ALTER TABLE employee RENAME PARTITION TAB3 TO TAB4;
Get row_count of partitions of a table
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
--- 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
- 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';
