Re: How to move objects from one datafile to another?

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Thu, 01 Mar 2007 12:41:28 -0600
Message-ID: <45e711cf$0$16396$88260bb3@free.teranews.com>

Eugene wrote:

> On Mar 1, 9:43 am, Brian Peasland <d..._at_nospam.peasland.net> wrote:

>> Eugene wrote:
>>> Hi all,
>>> I am running Oracle 8.1.6.3 on WinNT (I know...) and having I/O
>>> contention. I identified 2 datafiles that I'd like to split. I need to
>>> be able to move some tables out of one datafile into another
>>> tablespace on another drive.
>>> How do I do that?
>>> Thanks,
>>> Eugene
>> Create a new tablespace with the new datafile. Since this is 8i, the
>> ALTER TABLE MOVE command is not an option. So export the tables in
>> question. Then drop the tables. Precreate the tables in the appropriate
>> tablespace. Then import with IGNORE=Y. The data will be imported into
>> the tablespace you precreated the table in.
>>
>> That's the simple version!
>>
>> HTH,
>> Brian
>>
>> --
>> ===================================================================
>>
>> Brian Peasland
>> d...@nospam.peasland.nethttp://www.peasland.net
>>
>> Remove the "nospam." from the email address to email me.
>>
>> "I can give it to you cheap, quick, and good.
>> Now pick two out of the three" - Unknown
>>
>> --
>> Posted via a free Usenet account fromhttp://www.teranews.com

> 
> Thanks Brian for your reply,
> Actually, ALTER TABLE MOVE is available in 8.1.6. I've used it a
> couple of times.
> How would I do it using this option?
> 
> Eugene
> 

I couldn't remember which version the ALTER TABLE MOVE command first appeared in. My quick glance at the 8i docs did not show it...maybe I looked too fast. But that's why I gave the other option. In any case, it is probably easiest to move the data into that tablespace with this command, so long as the table does not contain a LONG or LONG RAW column. You can move tables as follows:

ALTER TABLE table_name MOVE TABLESPACE new_ts;

Similarly, you can move indexes too:

ALTER INDEX index_name REBUILD TABLESPACE new_ts;

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com

Received on Thu Mar 01 2007 - 12:41:28 CST