Use shell to check whether the Oracle partition table contains default partitions

Use shell to check whether the Oracle partition table contains default partitions

In a system with large data volumes, partition tables are common. There are multiple types of partitions. You can select the partitions you need based on your business needs. However, for data compatibility, you need to consider setting a default table partition for the partition table. If no partition meets the conditions when data is inserted into the table partition, it will be inserted into the default table partition.

This can be set based on your own needs. If you do have strict requirements on data, you can even not add the default partition.

The following script scans the partition information. If no default partition is detected, the script is generated.

You can decide whether to add a partition as needed.

Sqlplus-s $1/$2 @ $ SH_DB_SID <EOF
Set head off
Sets pages 100
Set linesize 200
/* Bad performance
Select name, Count (*) from user_part_key_columns where object_type = 'table' and name in
(
Select table_name from user_tables where partitioned = 'yes'
Minus
(Select distinct table_name
From user_tab_partitions where partition_name like '% MAX %'
)
)
Group by name;
*/
Select 'alter table' | table_name | 'add partition PMAXVALUE values less than ('|
Decode (col_count, 1, 'maxvalue ',
2, 'maxvalue, maxvalue ',
3, 'maxvalue, MAXVALUE, maxvalue ',
4, 'maxvalue, MAXVALUE, maxvalue', 'partition out of range') | ');'
From
(
(
Select t1.table _ name, count (t2.name) col_count from user_tables t1, user_part_key_columns t2
Where t1.partitioned = 'yes'
And t1.table _ name = t2.name
And t2.object _ type = 'table'
Group by t1.table _ name
)
Minus
(
Select t2.name table_name, count (t2.name) col_count
From user_tab_partitions t1, user_part_key_columns t2
Where t1.partition _ name like '% MAX %'
And t1.table _ name = t2.name
And t2.object _ type = 'table'
Group by t2.name
)
)
/

EOF
Exit

After a simple operation, we can see that some partition tables destroy default partitions. You can decide based on your own situation.

[Ora11g @ rac1 dbm_lite] $ ksh getmaxpar. sh system Oracle

Alter table LOGMNRC_GSBA add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNRC_GSII add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNRC_GTCS add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNRC_GTLO add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNRP_CTAS_PART_MAP add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNR_ATTRCOL $ add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNR_ATTRIBUTE $ add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNR_CCOL $ add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNR_CDEF $ add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNR_COL $ add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNR_COLTYPE $ add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNR_DICTIONARY $ add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNR_DICTSTATE $ add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNR_ENC $ add partition PMAXVALUE values less than (MAXVALUE );
Alter table LOGMNR_ICOL $ add partition PMAXVALUE values less than (MAXVALUE );

Introduction to range partitions of Oracle partition tables

Oracle Partition Table migration

Oracle Partition Table instance

Create and manage Oracle Partition tables