Partitioning FAQ

Oracle Partitioning Option FAQ:

How does one enable partition support for a database?

Partitioning is only available with Oracle Enterprise Edition as a cost option (you need to buy licenses before you can use it), or with OracleXE 18c (which is free and includes partitioning support).

Start the Oracle installer and check if "Oracle Partitioning" is installed. If it is, you can just start using it.

If not, you will get error ORA-00439: feature not enabled: Partitioning. If you get this error, upgrade to Enterprise Edition and/or install the partitioning option.

What partitioning types does Oracle support?

Oracle support the following partitioning methods:

• Range partitioning - data is mapped to partitions based on a range of column values (usually a date column)
• Hash partitioning - data is mapped to partitions based on a hashing algorithm, evenly distributing data between the partitions.
• List partitioning - data is mapped to partitions based on a list of discrete values.
• Interval partitioning - data is mapped to partitions based on an interval
• Composite partitioning - combinations of the above methods
  • Range-Hash partitioning - data is partitioned by range, then hashed to sub-partitions.
  • Range-List partitioning - data is partitioned by range, then to sub-partitions based on a value list.
  • Range-range partitioning
  • List-range partitioning
  • List-hash partitioning
  • List-list partitioning
• Interval partitioning, an extension of range partitioning. New partitions are automatically added.
• System partitioning, application controlled partitioning.
• Reference partitioning, partitioned like a parent table (referenced in a referential constraint).

How do I know if an object is partitioned or not?

A simple query against the data dictionary will show if a given table or index is partitioned or not:

SELECT * FROM dba_part_tables;
SELECT * FROM dba_part_indexes;

How does one list the partitions of a partitioned object?

One can list the partitions of a table with:

SELECT * FROM dba_tab_partitions WHERE table_name = '<table_name>';

One can show the partitions of an given index with:

SELECT * FROM dba_ind_partitions WHERE index_name = '<index_name>';