Re: moving datafiles

"craig" <someone_at_nowhere.com> wrote in message news:gBaXb.72411$fD.38968_at_fed1read02...
> I'm no pro to take it all with a grain of salt. As far as I know, making
a
> tablespace read only just means no more transactions can be made against
the
> database.
>
> Existing transactions could still be pending against that Tablespace?

No, because if there's a pending transaction, then you can't actually make it read-only. And if there are dirty buffers floating around from finished transactions, then those are flushed to disk before the thing becomes read-only (ie, making something read-only causes a checkpoint).

>Or not
> because you just update once a day, but does Oracle know this fact? What
> about forcing a checkpoint?

Irrelevant. There's already a checkpoint done when he makes it read-only.

> Have you putting the tablespace in backup mode to do this?

Er, it is actually impossible to put a read-only tablespace into hot backup mode, and there is in any case precisely zero need to do so. Since it is read-only, the data file header checkpoint change number is already locked, and users can't be modifying any blocks so there can be no possibility of block fracturing.

> Just bouncing off some ideas, but my best guess is that oracle is
preventing
> the move because it "thinks" the file is being used or "could" have
pending
> updates.

Close. The problem is actually that the control file is aware that the file is online, and you can't syntactically rename a file unless and until it is offline. The fact that it is read-only is irrelevant: suppose a user went to select some data just as you'd moved the file? The user wouldn't have a clue where to look... well, he would, because that's what the control file's job is. But the control file is either pointing at the old destination, or the new, or God knows what else.

You *have* to take the file offline before you can rename it. Read-only, read-write. Zilch difference I'm afraid.

Regards
HJR Received on Fri Feb 13 2004 - 15:17:42 CST