CODEWITHSUNDEEP
oracle-databasesplitpartitiontablespace
Rayees
Rayees

Reputation: 108

Oracle split partition to a different tablespace

Is it possible to split a partition and move the new sub partition to a different table space?

Lets say I have table space t1 and t2. For a table x I have only one partition p1 that sits in t1. Can I split p1 into p1 and p2 and move only p2 to tablespace t2? Will the following query work?

ALTER TABLE X
SPLIT PARTITION P1 -- currently in tablespace T1
AT (10, TO_DATE('20150916000000', 'YYYYMMDDHH24MISS'))
INTO (PARTITION P2
      tablespace T2,
      PARTITION P1) PARALLEL 10;

Upvotes: 1

Views: 1728

Answers (1)

Sangeeta Tulsiyan
Sangeeta Tulsiyan

Reputation: 34

Yes in Oracle 11g, it's possible to move an existing partition to a new tablespace.

So, you can probably do it in 2 steps-

  1. split the partition
  2. move the relevant partition to a new tablespace

Example -

Table name - Test_history_umts
partition name - sys_541
New table space name- data1

Query-

alter table test_history_umts move sys_541 to tablespace data1;

Hope it helps!!

Upvotes: 1

Related Questions