ALTER TABLE MOVE advice

[sjwales]

Hi,

I'm in the process of doing a slight reorganization to my database moving from auto allocated LMT's to Uniform Extent LMT's.

I've pretty much got it all scripted and tested but I've seen some conflicting articles on whether you can do it with the database open and with users updating the database.

Anyone able to provide me with a definitive reference to whether I need to be doing this in restricted mode or not?

Trying to work out if I need to arrange an outage for this or not.

Thanks
Steve

stephen.wales@riotinto.com

 

[SantaMufasa]

Steve,

You can move tables whilst the database is open in non-restricted mode, but you are far better off if you do it in RESTRICTED mode, just because of locking issues. I have done precisely what you plan, and I ran into far fewer issues when I had no competition from updaters.

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:35 (17Sep04) UTC (aka "GMT" and "Zulu"), 15:35 (17Sep04) Mountain Time)

 

[sjwales]

Yeah, that's what I was thinking, but it's a matter of talking management into a 4 hour outage :-(

My initial testing was doing it with logging, I'm going to be doing it again on Monday with "nologging parallel" specified and see if that shortens the window down a bit.

I don't know that I like the nologging option that much because in the event of some form of failure I'm up that famed stinky creek...

Comments on that one?

Steve

stephen.wales@riotinto.com

 

[SantaMufasa]

Also, Steve, remember that you cannot MOVE tables with LONG columns...those you must export then import into the new tablespace. You should have absolutely no problem running "PARALLEL"; although I have never had trouble running MOVEs with NOLOGGING, if you wish to eliminate your risks, you can "exp" the schema prior to the MOVEs.

For a complete roadmap, including scripts and code to do what you want, I refer to you thread186-900459, the fifth post down, posting of 16:31 (16Aug04) Mountain Time.

Let us know your results,

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:57 (17Sep04) UTC (aka "GMT" and "Zulu"), 15:57 (17Sep04) Mountain Time)