CODEWITHSUNDEEP
oracle-databasedatedatabase-partitioning
JohnnyO
JohnnyO

Reputation: 547

Create year date partitions on an existing unpartitioned table

I want to know an easy approach to partitioning an existing table. I think by Ranges and using a date field will work.

However I see two options on the Oracle site ( see link below )

https://docs.oracle.com/database/122/VLDBG/evolve-nopartition-table.htm#VLDBG00302

Converting a Non-Partitioned Table to a Partitioned Table seems easier than Using Online Redefinition to Partition Collection Tables, just by the steps involved.

However I see here ( see link below )

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9533887100346178338

we see

SQL> ALTER TABLE a MODIFY
  2  PARTITION BY RANGE(x) INTERVAL (10)
  3  ( PARTITION P1 VALUES LESS THAN (10),
  4  PARTITION P2 VALUES LESS THAN (20),
  5  PARTITION P3 VALUES LESS THAN (30)
  6  )
  7  update indexes
  8  (xpa local,
  9  xiea global partition by range(y)
 10  (partition ip1 values less than (MAXVALUE))
 11  ) ;

Table altered.

And all of that works only in 12.2. If you are on 12.1, you need to use DBMS_REDEFINITION. See here for an example

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:869096700346654484

So as I am using..

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    0
PL/SQL Release 12.1.0.2.0 - Production  0
"CORE   12.1.0.2.0  Production" 0
TNS for Linux: Version 12.1.0.2.0 - Production  0
NLSRTL Version 12.1.0.2.0 - Production  0

Am I only able to use (limited to) Online Redefinition to partition tables?

What's a clean way to create partitions by year?

Upvotes: 1

Views: 5492

Answers (1)

adimoise91
adimoise91

Reputation: 568

If you still need an answer for partitioning tips, you can find right here an explanation.

For second question, you can use automatic partition creation from Oracle 11g. Here you are some examples of code:

PARTITION BY RANGE (date_col) 
  INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-2016', 'DD-MM-YYYY')),
      PARTITION p1 VALUES LESS THAN (TO_DATE('01-01-2017', 'DD-MM-YYYY')),
      PARTITION p2 VALUES LESS THAN (TO_DATE('01-01-2018', 'DD-MM-YYYY')),
      PARTITION p3 VALUES LESS THAN (TO_DATE('01-01-2019', 'DD-MM-YYYY')) );

The high value of the range partitions is the transition point. The database automatically creates interval partitions for data beyond that transition point. For our example the transition point is 01-01-2019 because 01-01-2019 is the max date. You don't have to create MAX_VALUE partition.

How to enable or change interval? 

ALTER TABLE t_part SET INTERVAL(NUMTOYMINTERVAL(1,'YEAR'));

How to disable it? 

ALTER TABLE <TABLE name> SET INTERVAL ();

Here you can find some info about this function. If you need more info, let me know.

Upvotes: 3

Related Questions