Moving Tablespace/data file

[samdba]

Hi there,

We are on 8.1.7.3.0 / Windows NT.

I want to move my Index tablespaces from E: drive to D:.

Which is the best option available on above version to accomplish my task?

Thanks in Adv.

[noor]

Hello,

An option consist of rebuilding indexes :

You have to create the new TS;

Generate a script with alter index orders :


...
alter index index_name rebuild tablespace_name;
...


Hope this helps

[dbadoc61]

Take your index tablespace offline. Copy the datafiles that belong to the tablespace to your new destination. Rename the datafiles to *.old (by renaming instead of deleting the datafiles allows you to recover if you goof). Run sql command ALTER TABLESPACE rename datafile 'OLD DATAFILE' to 'NEW DATAFILE' ; for each datafile in your tablespace. Put the index tablespace online. Check your datafile names from v$datafiles to make sure they all have the path that you want. If everything looks good. go ahead and delete your *.old files.

[bensmail]

1. make TBS offline
2. Copy the datafiles to new Drive
3. alter database rename datafile 'old location' to 'new
location'
4 . make TBS online

sbensmail@yahoo.fr

[samdba]

Great quick replies from you great guys!

I would implement it and come back if needed.

Thanks

[samdba]

Hi,

When at the end, I say


alter database datafile 'Filename at new location' online;

It gives me foll. error.

ORA-01113: file 40 needs media recovery
ORA-01110: data file 40: 'File name'

Then, from svrmgr> I recovered the datafile and then it allowed me to make it online.

So, what care should be taken so that I do not get above error in future?

I did not close the database while renaming the datafile. Is that OK?







[Edited by samdba on 07-02-2002 at 11:31 AM]

[dbadoc61]

I have seen that happen when there are uncommited transactions that would have been written to an object contained in the tablespace. You can avoid this by

A: Disable user access by stopping the listener. Force a checkpoint, force a log switch, force another checkpoint. Yeah it's overkill but......

OR

B: Shutdown the database so that everything gets committed and checkpointed.

[kavithared]

Hiiii,noor this is only public weleducated forum.
But it is not religious forum.
I am very sorry to say,don't advtise relegious matters in this forum.
plsss i am requesting to all users,noor given religious link,plss don't accept this type of malpractice.

noor link http://www.islamway.com/eng/html/invitation.php

[patel_dil]

Have to Agree with kavitha. Plz dont use such kinds of links in this forum.

[SANJAY_G]

The database must be in mount state when you are using
ALTER DATABASE RENAME FILE .....

I think that is the reason you had to do a media recovery.

Sanjay