Re: alter table move... question (logging/nologging)

It is not dangerous to move the table NOLOGGING, as long as you:

• Rebuild all the indexes on the table
• alter the table back to LOGGING
• Take a backup after the move
• realize that there will be some loss of service during the move (object will be locked, indexes will be invalid until rebuilt)

Transactions performed against that table after the move cannot be applied to the previous backup. A new backup of at least the datafiles containing the table must be taken in order to have something to which you can apply transactions.

I don't know for sure a lot about the internals of move, but I think it is pretty simple - just ctas or insert /*+append*/ and some dictionary management behind the scenes.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Fri, 29 Nov 2002, Syltrem wrote:



> Oracle 81600

> VMS 721

> I need to move a (big) table from one tablespace to another. The table is

> NOT partitioned.

> The database is in archivelog mode, don't want to shut it down, don't want

> to generate tons of redo logs.

> 

> Is it safe to do ALTER TABLE MOVE ... NOLOGGING ?

> 

> What exactly happens then? You can make a reference to the manual, I'll be

> glad to read all the details in there.

> 

> Will the old table be dropped only when it is successfully moved to the

> other tablespace?

> If I use NOLOGGING, is the safeguard to a) EXPort data just in case, MOVE,

> backup old and new tablespaces.

> I would think backup old tablespace is required because it would be hard to

> apply archived logs to it (if needed for eventual recovery), when the table

> really is gone. Don't know. That's why I'm asking, I'd like to understand

> what goes on when I do this.