Maximum number of partitions in an Oracle table

The maximum number of partitions that can be allowed in a table has not been much cared for before. If you don't check the document, is there a quick way to get the results?

At that time, manual is 11.2 environment, so first thought of the interval partition, so that only need to create a interval 1 partition table, and keep inserting data, Oracle will automatically expand the partition, only to the partition limit Oracle will error.

But found that this method not only time-consuming, and there are bug,oracle frequently through recursion to create partitions, will soon lead to system memory exhaustion, 6G of memory is almost all shared pool, System ORA-4031 error. Only less than 10,000 partitions were established.

With range partitioning, you can easily add partition to 20000 without causing errors.

So there's no better way to do that than a range partition, but you can get the answer to the problem faster by using a hash partition.

If you use 11.2 of the new features, you do not need to actually perform the creation:

Sql> SELECT * from V$version;

BANNER

--------------------------------------------------------------------------------

Oracle database11genterprise Edition release11.2.0.1.0-64bit Production

Pl/sql Release 11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl Version 11.2.0.1.0-production

sql> CREATE TABLE T_part_hash

&2 (ID number)

&3 SEGMENT Creation DEFERRED

&4 PARTITION by HASH (ID)

&5 partitions 1048576;

Partitions 1048576

*

Line 5th Error:

ORA-14299: Total number of partitions/Sub partitions exceeded maximum limit

sql> CREATE TABLE T_part_hash

&2 (ID number)

&3 SEGMENT Creation DEFERRED

&4 PARTITION by HASH (ID)

&5 partitions 1048575;

CREATE TABLE T_part_hash

*

Line 1th Error:

ORA-14223: This table does not support deferred creation of segments

Depending on the error message, it is possible to determine that the maximum number of partitions supported by Oracle is 1048575, that is, power (2, 20) –1.

Of course, if the other version, you can get similar results, the difference is that because you do not specify segment creation DEFERRED, then for the subsequent creation of the partition table statement, will begin execution.

This execution time, if it is not due to insufficient resources to make an error, it may take at least a few days, so the confirmation command can be executed, you should abort the session as soon as possible. In addition, do not execute the above SQL in any formal environment.

This column more highlights: http://www.bianceng.cn/database/Oracle/