Skip to Main Content
  • Questions
  • Convert a regular table to system partitioned table ....

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 02, 2017 - 5:11 am UTC

Last updated: June 06, 2017 - 1:49 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

Hi,

Is there a way to convert a regular table to a system partitioned table?

Since there is a limitation to system partitioning wherein we cannot use CTAS, How can we replicate a regular table to system partitioned table?

Regards,
Manu

and Connor said...

If you want to put the entire table into 1 of the system partitions, you can do a direct exchange

SQL> create table t ( x int, y int );

Table created.

SQL>
SQL> insert into t select rownum, rownum from dual
  2  connect by level <= 1500;

1500 rows created.

SQL>
SQL> create table t1 ( x int, y int )
  2  partition by system
  3  (
  4    partition p1,
  5    partition p2
  6  )
  7  /

Table created.

SQL>
SQL> alter table t1 exchange partition p1 with table t;

Table altered.



If you have a rule that defines the system partitions, and the rule can be expressed by the data, then you could do it in a 2 step process. Let's say your system partition rule is:

"if x+y < 500 then partition p1, else p2"

you could so something like this:

SQL> create table t ( x int, y int );

Table created.

SQL>
SQL>
SQL> insert into t select rownum, rownum from dual
  2  connect by level <= 1500;

1500 rows created.

SQL>
SQL> alter table t add partcol varchar2(2) invisible generated always as ( case when x+y < 500 then 'p1' else 'p2' end ) ;

Table altered.

SQL>
SQL> alter table T modify
  2  partition by list (partcol)
  3   (
  4     partition p1 values ('p1'),
  5     partition p2 values ('p2')
  6  ) online
  7  /

Table altered.

SQL>
SQL> create table t1 ( x int, y int )
  2  partition by system
  3  (
  4    partition p1,
  5    partition p2
  6  )
  7  /

Table created.

SQL>
SQL> create table t2 ( x int , y int, partcol varchar2(2) invisible generated always as ( case when x+y < 500 then 'p1' else 'p2' end ) );

Table created.

SQL>
SQL> alter table t exchange partition p1 with table t2;

Table altered.

SQL> alter table t1 exchange partition p1 with table t2;

Table altered.

SQL>
SQL> alter table t exchange partition p2 with table t2;

Table altered.

SQL> alter table t1 exchange partition p2 with table t2;

Table altered.



which would be minimal downtime.

Rating

  (1 rating)

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

Comments

Need CTAS like functionality

A reader, June 05, 2017 - 9:15 am UTC

Hi,

The requirement is as follows -
1) have a table named A (whose structure can change from one version of product to another)
2) Need to create a table B which is SYSTEM partitioned based on the table A

If CTAS was possible it would have been an easy options -

CTAS B SELECT * FROM A;

However since CTAS is not possible and I do not know the structure of the table beforehand I am not sure how to create the system partitioned table ( DBMS_METADATA is an option but I was looking for something more simple).

1) Say like I create a regular table -CTAS B AS A;
2) Then modify the regular table to System partitioned table

Is there a possibility of any such approach?
Connor McDonald
June 06, 2017 - 1:49 am UTC

"1) have a table named A (whose structure can change from one version of product to another)
2) Need to create a table B which is SYSTEM partitioned based on the table A"

And my solution showed you how to do that. It went from

regular (T) to partitioned (T) online, then
partitioned (T) to system partitioned (T1)

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.