StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do you move a partitioned table from one tablespace to another in Oracle 11g?
text
Body
copied!<p>I have a partitioned table that belongs to tablespace <em>report</em>. I want to move it to tablespace <em>record</em> instead.</p> <p>One possibility is to drop the table and recreate it in the new tablespace, but that is not an option for me, since there is data in the table that needs to survive the move.</p> <p>I started by checking that the partitions actually belong to tablespace report with:</p> <pre><code>SELECT * FROM user_tab_partitions WHERE table_name = 'REQUESTLOG'; </code></pre> <p>Then I just tried:</p> <pre><code>ALTER TABLE requestLog MOVE TABLESPACE record; </code></pre> <p>But that gives me error ORA-145111 “cannot perform operation on a partitioned object”.</p> <p>Then I found out that I can move individual partitions using:</p> <pre><code>ALTER TABLE requestLog MOVE PARTITION "2009-12-29" TABLESPACE report; </code></pre> <p>But since there are 60 partitions of the table (based on date), and because I may have to do this for several systems, I would like to loop over all the partition names, moving each to the new tablespace. I tried that, but couldn’t quite get the SQL to work.</p> <p>Even if I move all the existing partitions to the new tablespace, there is still a problem when creating new partitions. The new partitions are still created in the old tablespace <em>report</em>. How do I change so that new partitions are created in the new tablespace <em>record</em>?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload