Skip to Main Content
  • Questions

  • How to move tables in different tablespaces without down time

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: October 23, 2004 - 10:38 pm UTC

Last updated: October 26, 2004 - 7:43 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,
I have one question for you. We are using Oracle 8.1.7. (OLTP environment) Currently we want to move some tables to different tablespaces. We have decided to move 2 important tables of our database to the different tablespace. Both the tables contain millions of rows and main problem is for transaction point of view both the tables are very much important so we can’t afford much down time.
We thought of using “MOVE” command but for that we have to make these tables unavailable for transactions. Another alternative was to create copy of tables and rebuild indexes but I think it would take too much time and as I said I can’t afford much down time. I think export-import won’t work. So could you please explain me some ways from which I can reduce down time or are there any ways that table will remain available for transaction and I can move tables without any down time? It would be great if you could providing answer with detail steps.
Thanks Tom. It is a kind of urgent so would you please try to reply soon?
Once again Tom thanks in advance.

Vicky


and Tom said...

see

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:17309172009134 <code>

there I demonstrate how you could use "materialized views" on prebuilt tables to mimick an online table redefinition (available in 9i and above as a package).

It uses the same basic techniques at an online redef does and incurrs minimum downtime.

Please do test this (practice it) in a testbed database before just doing it.

Rating

  (3 ratings)

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

Comments

Minimum down time to move tables in different tablespaces

Vicky, October 24, 2004 - 11:39 pm UTC

Thank you very much Tom for your fast reply. It helped me a lot.
Once again thanks.

What about Master Table?

Vicky, October 25, 2004 - 10:14 pm UTC

Dear Tom,
I implemented script that you provided it works fine. I had another question which is one of my tables is a Master table which is referenced by another tables so if I would use Materialised View to move it I wont be able to drop this old table as it is referenced by another tables. What should I do in this case?
Could you please provide me your expert solution?
Thanks in advance.

Vicky


Tom Kyte
October 26, 2004 - 7:46 am UTC

you would prevent updates to both the MASTER and the DETAIL table.

do the last sync (refresh)

you would drop table master cascade constraints;

alter table detail add constraint (with novalidate if you like, to make it really fast, in order to just repoint the constraint to the other new table -- since you know all of the primary keys are in fact there, it is a perfect copy)

Thanks Tom

Vicky, October 26, 2004 - 7:43 pm UTC

Thanks Tom for yout expert comment.
It is really helpful.




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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library