Skip to Main Content
  • Questions

  • INTERVAL PARTITIONING with custom TABLESPACE names

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Praveen.

Asked: February 23, 2017 - 2:13 pm UTC

Last updated: June 19, 2023 - 4:38 am UTC

Version: 11.2.0.2.0

Viewed 10K+ times! This question is

You Asked

We have a range partitioned table on daily basis. Every year, we are splitting the MAX partition into 365* days partitions such as:
ALTER TABLE txn SPLIT PARTITION p_txn_max
AT (to_date('01/02/2017','MM/DD/YYYY'))
INTO (PARTITION p_txn_20170101 TABLESPACE p_txn_2017, PARTITION p_txn_max)...

ALTER TABLE txn SPLIT PARTITION p_txn_max
AT (to_date('01/03/2017','MM/DD/YYYY'))
INTO (PARTITION p_txn_20170102 TABLESPACE p_txn_2017, PARTITION p_txn_max)...

We have a code to do that, but is it possible to achieve this by using INTERVAL extension to automate? Here, the challenge is the TABLESPACE. We are using the tablespaces like p_txn_YYYY to store the yearly data.

and Connor said...

It can be done, but it requires a maintenance task to do it (which somewhat defeats one of the benefits of interval partitions).

A partition will be created even for an uncommitted transaction, so you can:

- alter the default tablespace for the table
- add an uncomitted row to create a partition
- rollback

For example


SQL>
SQL> create tablespace ts2016 datafile 'C:\ORACLE\ORADATA\NP12\TS2016.dbf' size 50m;

Tablespace created.

SQL> create tablespace ts2017 datafile 'C:\ORACLE\ORADATA\NP12\TS2017.dbf' size 50m;

Tablespace created.

SQL> create tablespace ts2018 datafile 'C:\ORACLE\ORADATA\NP12\TS2018.dbf' size 50m;

Tablespace created.

SQL>
SQL> create table t ( x date , y int ) tablespace ts2016
  2  partition by range ( x )
  3  interval ( numtoyminterval(1,'YEAR') )
  4  (
  5    partition p1 values less than ( date '2017-01-01' )
  6  );

Table created.

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
  2  where table_name = 'T';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------
P1                             TS2016

1 row selected.

SQL> alter table t modify default attributes tablespace ts2017 ;

Table altered.

SQL> insert into t values ( date '2017-06-01', 1);

1 row created.

SQL> rollback;

Rollback complete.

SQL>
SQL> alter table t modify default attributes tablespace ts2018 ;

Table altered.

SQL>
SQL> insert into t values ( date '2018-06-01', 1);

1 row created.

SQL> rollback;

Rollback complete.

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions
  2  where table_name = 'T';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------
P1                             TS2016
SYS_P21398                     TS2017
SYS_P21399                     TS2018

3 rows selected.

SQL>


Rating

  (3 ratings)

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

Comments

Maintenance Continues

Praveen Ray, February 24, 2017 - 9:52 am UTC

Ooh! this means we need a job to this and rename partitions - doesn't look dandy for my requirement but possible to automate, thanks

Mikhail Velikikh

Mikhail Velikikh, February 27, 2017 - 3:39 am UTC

Hello,

@ - add an uncomitted row to create a partition

We may also use the "LOCK TABLE PARTITION FOR" command for that. It is documented in VLDB and partitioning guide: https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#VLDBG1129
In one of my environments I use a sequence of the below commands to create a new partition within an interval partitioned table:
- insert a fictional row to create a partition
- commit
- delete the row
- commit
This is due to the fact that I use Oracle GoldenGate and want to get the new partition in other databases, which the changes are replicated to, as well. It is one of the downsides of the recursive SQL used to create a partition on source - the new partition remains in the source database even after rollback, that is not true for any destination databases in which data is replicated as either Streams or GoldenGate does not apply transactions that were rolled back. Generally, but not always, there is no sense in applying them. I raised a new Bug 25073307 - "lock table partition for DDL doesn't get captured" (unpublished) to tweak Oracle GoldenGate to properly handle the cases when new partitions were created as part of the "LOCK TABLE PARTITION FOR" command that was rolled back.
Connor McDonald
February 28, 2017 - 1:27 am UTC

Thanks for that info - very useful

INTERVAL PARTITIONING storing data into to specified TABLESPACE.

Ramki, June 16, 2023 - 10:45 am UTC

HI ,

we have similar issue
INTERVAL PARTITIONING storing data into to specified TABLESPACE.

For week partition we want to store data in specific week TBS.
Later want to make week TBS read-only so that to avoid backup these older week tbs .
since we having 52 week TBS ..skip in backup is very important for us
It would be good we have solution from oracle for this requirement .

Is there any channel to create such requirement to oracle DB ?

Thanks
Connor McDonald
June 19, 2023 - 4:38 am UTC

Forum post tagged as an idea

See

https://forums.oracle.com/ords/apexds/domain/dev-community/category/database-software?tags=database-ideas,database-ideas-ideas

for examples
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

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.