You are here: Home Administration Datapump
02 | 12 | 2023
Main Menu
Online database
Resources
Latest Articles
Popular Articles

Administration

Partition Administration

  • Print
User Rating:  / 0
PoorBest 
Details
Parent Category: Articles
Created on Thursday, 18 March 2010 20:18
Last Updated on Monday, 12 March 2012 13:54
Published on Thursday, 18 March 2010 20:18
Written by Guy Lambregts
Hits: 6461

Partition Administration

 

See also

Partioned tables : Overview

Puget Sound Oracle User Group : Partition Examples and overview

Today it is time for some partition gym. How do we add, split, drop partitions ? When become the local indexes unusable ? When become the global indexes unusable ?

Note that

1. So far I haven' t done any composite partition gym

2. We speak about 10G R2 here. ( not yet 11G )



Let us take a look on what we have.

SQL> select table_name,partitioning_type,partition_count from user_part_tables;


TABLE_NAME                    PARTITION PARTITION_COUNT

TABLE_T1                       HASH                  8
TABLE_T5                       LIST                    4
TABLE_T6                       LIST                   4
TABLE_T7                       RANGE                2
TABLE_T8                       RANGE                2
TABLE_T2                       HASH                  4
TABLE_T3                       HASH                  8
TABLE_T4                       HASH                  8

 

 

A. HASH PARTITIONING


Table_t3 is hash partitioned, Which are the partitions for table_t3 ?

SQL> select partition_name from user_tab_partitions where table_name='TABLE_T3';


PARTITION_NAME

SYS_P2707
SYS_P2708
SYS_P2709
SYS_P2710
SYS_P2711
SYS_P2712
SYS_P2713
SYS_P2714

 

8 rows selected.


MOVING HASH PARTITIONS

Let us move a partition to another tablespace and update the global indexes

SQL> alter table table_T3 move partition sys_P2707 tablespace coconut_data update global indexes;


Table altered.



We haven' t invalidated the global primary key index

SQL> select index_name,status from user_indexes where table_name='TABLE_T3';


INDEX_NAME                     STATUS

T3_PK                          VALID
T3_IDX1                        N/A



However the local index partition associated with the moved partition becomes invalid, we have to rebuild the unusable index partition

SQL> alter table table_T3 modify partition sys_P2707 rebuild unusable local indexes;


Table altered.



TRUNCATE TABLE PARTITION ( and update the global indexes )

 

 

SQL> alter table table_t3 truncate partition sys_p2707 update global indexes;


Table truncated.



MODIFICATION OF DEFAULT STORAGE ATTRIBUTES

SQL> alter table table_T3 modify default attributes tablespace coconut_data initrans 8;


COALESCING A HASH PARTITIONED TABLE table removes a partition and redistributes the data among the other partitions

SQL> alter table table_T3 coalesce partition;


Table altered.


ADDING A HASH PARTITION

SQL> alter table table_T3 add partition tablespace coconut_data update global indexes ;


Table altered.

 

 

 B. LIST PARTITIONING

 

 

Table_t5 is list partitioned, Which are the partitions for table_t5 ?

SQL> select partition_name from user_tab_partitions where table_name='TABLE_T5';

SQL> select partition_name,tablespace_name from user_tab_partitions where table_

name='TABLE_T5';

PARTITION_NAME                 TABLESPACE_NAME

BE                             USERS
FR                             USERS
NL                             USERS
OT                             USERS


MOVING LIST PARTITIONS

SQL> alter table table_T5 move partition be tablespace coconut_data initrans 4 update global indexes;


The update global indexes clause avoids the global indexes become unusable. ( very nice )

SQL> select index_name,status from user_indexes where table_name='TABLE_T5';


INDEX_NAME                     STATUS

PK_T5                          VALID
T5_IDX1                        N/A


However the local indexes become unusable

SQL> select index_name,partition_name,status from user_ind_partitions where index_name='T5_IDX1';


INDEX_NAME                     PARTITION_NAME           STATUS

T5_IDX1                            BE                                 UNUSABLE
T5_IDX1                            FR                                 USABLE
T5_IDX1                            NL                                 USABLE
T5_IDX1                            OT                                 USABLE


Hence we have to rebuild the unusable local indexes

SQL> alter table table_t5 modify partition be rebuild unusable local indexes;


Table altered.


ADDING A LIST PARTITION

SQL> alter table table_t5 add partition uk values ('044');

alter table table_t5 add partition uk values ('044')
            *
ERROR at line 1:
ORA-14323: cannot add partition when DEFAULT partition exists


Adding a list partition is only possible when there is no default partition, whenever there is a default partition we have to split partitions

SPLIT LIST PARTITIONS

SQL> alter table table_t5 SPLIT partition eg VALUES ('020') into (partition eg, partition al) update global indexes;


Table altered.



MERGE LIST PARTITIONS

SQL> alter table table_t5 MERGE partitions eg, al into partition eg update global indexes;



We need to rebuild the unusable local indexes after merging and splitting.

SQL> select a.index_name,a.partition_name,a.status from user_ind_partitions a,user_indexes b where a.index_name = b.index_name and b.table_name = 'TABLE_T5';


INDEX_NAME                     PARTITION_NAME                 STATUS

T5_IDX1                        BE                             USABLE
T5_IDX1                        NL                             USABLE
T5_IDX1                        FR                             USABLE
T5_IDX1                        UK                             UNUSABLE
T5_IDX1                        OT                             UNUSABLE
T5_IDX1                        EG                             UNUSABLE

SQL> alter table table_t5 modify partition uk rebuild unusable local indexes;
Table altered.

SQL> alter table table_t5 modify partition ot rebuild unusable local indexes;
Table altered.

SQL> alter table table_t5 modify partition eg rebuild unusable local indexes;


TRUNCATE A LIST PARTITION

SQL > alter table table_t5 truncate partition ot;

Table truncated.


DROP A LIST PARTITION

SQL> alter table table_t5 drop partition eg update global indexes;

Table altered.

 

ADDING AND DROPPING VALUES TO LIST PARTITIONS

 

SQL> alter table table_t5 modify partition be add values ('030','029') ;


Table altered.

SQL> alter table table_t5 modify partition be drop values ('030','029') ;

Table altered.

 

 

 

RANGE PARTITIONING

 

SPLIT RANGE PARTITION

If the upper boundary of the range partition table has the maxvalue attribute then adding partitions is done with the split partition command

SQL> select partition_name,high_value from user_tab_partitions where table_name = 'TABLE_T7';



T7_P0308
TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN' )


T7_P0408
MAXVALUE

SQL> alter table table_t7 split partition T7_P0408? at (to_date('01/05/2008','DD/MM/YYYY')) into ( partition T7_P0408? , partition T7_P0508? ) update global indexes;

Table altered.

 

ADD RANGE PARTITION

If the upper boundary of the range partition table has a real value then adding     partitions is done with the add partition command

 

SQL> alter table table_t8 add partition T8_P0508? values less than ( TO_DATE(  '01/06/2008' , 'DD/MM/YYYY')) tablespace coconut_data initrans 8 pctfree 5;


Table altered.

 

MERGING RANGE PARTITIONS

We need to rebuild the unusable local indexes afterwards

SQL> alter table table_t8 merge partitions T8_P0308?,T8_P0408? into partition     T8_P030408? update global indexes;


Table altered.

SQL> alter table table_t8 modify partition T8_P030408? rebuild unusable local indexes;

Table altered.

 

TRUNCATE RANGE PARTITIONS

 

 

SQL> alter table table_t8 truncate partition T8_P030408? update global indexes;


Table truncated.

 

 ADD AND REMOVE A RANGE PARTITION

 

 

SQL> alter table table_t8 add partition T8_P0608? values less than ( TO_DATE( '01

/07/2008' , 'DD/MM/YYYY')) tablespace coconut_data;

Table altered.

SQL> alter table table_t8 drop partition T8_P0608? update global indexes;

Table altered.

 

All Rights Reserved © 2023