Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andrey.

Asked: April 16, 2018 - 4:21 pm UTC

Last updated: April 17, 2018 - 9:47 am UTC

Version: 12 c

Viewed 1000+ times

You Asked

Hi Tom!
I use Oracle 12c version. I have partitioned by list table. How can I change non automatic partitioning to automatic? Thank you!

and Connor said...

This is a 12c Release 2 feature. But assuming you are on 12.2, it is as easy as:

SQL> alter table PEOPLE set partitioning automatic;

Table altered.

SQL> alter table PEOPLE set partitioning manual;

Table altered.


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Andrey Golyandin, April 17, 2018 - 9:42 am UTC

Thank you for your response! But I use 12.1 version. How can I do auto partitioning on this version? Can you give me advice?
Chris Saxon
April 17, 2018 - 9:47 am UTC

You can't do auto-list partitioning on 12.1. It's a 12.2 feature.

You can do interval partitioning from 11.1. This automatically creates new range partitions.

Andrey Golyandin, April 17, 2018 - 10:39 am UTC

Thank you!

Hash partitioning trick.

A reader, April 26, 2018 - 6:31 am UTC

Hi

One way to try to have same effects as automatic list partitioning is to have table partitioned by hash partitioning and creating more than enough partitions.
Tj

Then (in most cases) partitions have only one value of partitioned column.

This seems to work when partitioned column is referenced with equality (=).
Range clauses (>= and <=) propably are problematic (haven't actually tested this).

Some diskspace will be wasted, because empty partitions are created.


lh

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.