Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Move datafiles??

Re: Move datafiles??

From: Terry Ball <terry_ball_at_csgsystems.com>
Date: Fri, 05 Feb 1999 09:02:48 -0600
Message-ID: <36BB0818.11ECBEF7@csgsystems.com>


I am not sure exactly what you are asking. If you are asking, can a datafile with data in it be moved, the answer is yes. If you are asking can you move the data from one tablespace to another, the answer is again yes.

To move a datafile, you need to:

  1. alter tablespace offline.
  2. do an OS level copy of the datafile(s) to be moved
  3. alter database rename <full path old filename> to <full path new new filename>
  4. alter tablespace online. 5a. Because I'm paranoid, I check to see that the rename was successful by select from dba_data_files. 5b. Do an OS level remove of the old file.

To move data from one tablespace to another, there are several different methods. You can export the table(s), revoke the users rights to the first tablespace and grant them resource only on the tablespace to be moved to; then import. Or you can create a script to receate table/indexes. Do an export. Drop the table(s). Run the scripts. Import the data. Or you can create the table as <new name> in the new tablespace. Drop the old table and rename the new table to the old name for every table to be moved.

Terry Ball
Sr. DBA, CSG Systems

mquesada_at_renault.com.ar wrote:

> Hi:
> Im running 7.3.4 on HP-UX 10 and my question is... Is it posible to move
> datafiles with data from a tablespace to another??
> If i can.. How can i do it??
>
> Thanks in advance..
>
> Marcelo Quesada
> +54 351 4268015
> mquesada_at_renault.com.ar
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri Feb 05 1999 - 09:02:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US