Moving Tablespace/data file

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.

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

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.

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

[email protected]

Great quick replies from you great guys!

I would implement it and come back if needed.

Thanks

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]

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.

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

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

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

A man's ethical behavior should be based effectually on sympathy, education, and social ties; no religious basis is necessary. Man would indeed be in a poor way if he had to be restrained by fear of punishment and hope of reward after death.

Thnnx kavitha dont encorage this type of devotees