Category Archives: Partitioning

How to Check and Modify Default Attributes of Tables and Indexes Partitions

Sometimes the default attributes require to be modified for future partitions, or sub-partitions, specially in the situation when tables and indexes are refreshed from different environment. Modifying Default Attributes of a Table SQL>alter table OWNER.TABLE_NAME modify default attributes tablespace TABLESPACE_NAME; … Continue reading

Posted in Partitioning | Tagged , , , , , , , , , , , , , | Leave a comment

Dropping an Empty Partition Without “Update Indexes” Necessary

As we know  the ‘update indexes’ clause is used  to drop the local indexes and update the global index  when we drop a partition. So the indexes will still be VALID” The below example shows when we drop an empty … Continue reading

Posted in Partitioning | Tagged , , , | Leave a comment


Issues: When split a partition of a reference parent table, the compression attribute “compress for OLTP” is not inherited by child partitions. Instead,it changes into “compress fo BASIC’ by default. The test case below is on database. We have two … Continue reading

Posted in oracle advanced compression, Oracle DBA Practice, Partitioning | Tagged , , , | Leave a comment

Sample script to drop interval partitions

Situation: The big benefit of using interval partition is automatic partition creation when new records are inserted. But we need purge the history partitions by developing in-house scripts. SQL> desc user_tab_partitions; Name Null? Type ———————– ——– —————- TABLE_NAME VARCHAR2(30) COMPOSITE … Continue reading

Posted in Partitioning | Tagged , , | Leave a comment

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

Situation: We created an interval partition table structure only, then try to import the data from exported dump file. It causes ORA-14300 error. The same error occures when insert records from another online backup table: SQL> insert into tbl_test select * … Continue reading

Posted in Oracle DBA Practice, Partitioning | Tagged , | 2 Comments