Home » RDBMS Server » Server Administration » Need to move a new datafile from one tablespace to another tablespace (Oracle Enterprise Manager 9i)
Need to move a new datafile from one tablespace to another tablespace [message #332514] Tue, 08 July 2008 15:38 Go to next message
msrite57
Messages: 3
Registered: February 2008
Location: High Point, NC
Junior Member
I forgot to select from the drop down list the appropriate tablespace name whenever I created a new datafile. The datafile path is the correct path information ... but it was put under the tablespace name of DI_DATA and it should be under tablespace name of MDATA_DATA. The path of the datafile name that is under the DI_DATA tablespace ... is /pds_oracle/u05/oradata/pdsprod/mdata04.dbf.

I have tried to take the datafile off line and it will not let me do this. I have tried to drag and drop the datafile into the correct tablespace name ... but I have had no luck.

Please advise.

Thank you ... Donna
Re: Need to move a new datafile from one tablespace to another tablespace [message #332519 is a reply to message #332514] Tue, 08 July 2008 15:57 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You can not drop a datafile from a tablespace with 9i. In this case, you can rename the file to something else and then create the file under the appropriate tablespace. The only way to delete the erroneous datafile is to recreate the tablespace.
Re: Need to move a new datafile from one tablespace to another tablespace [message #332556 is a reply to message #332514] Tue, 08 July 2008 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, a datafile cannot be moved from one tablespace to another one. A datafile is definitively owned by the tablespace in which it is created.

Regards
Michel
Re: Need to move a new datafile from one tablespace to another tablespace [message #332717 is a reply to message #332514] Wed, 09 July 2008 08:05 Go to previous messageGo to next message
msrite57
Messages: 3
Registered: February 2008
Location: High Point, NC
Junior Member
Thanks for the information ... I have one other question ... I am new to Oracle ...

I tried to change the name of the datafile:

From : /pds_oracle/u05/oradata/pdsprod/mdata04.dbf
To : /pds_oracle/u05/oradata/pdsprod/di_data02.dbf

I received the following message and it would not allow me to change the name ...

Note: I did not take the datafile offline ... because I did not know what kind of affect it would cause.

Message Received:

ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 29 - file is in use or recovery.
ORA-01110: data file 29:
/pds_oracle/u05/oradata/pdsprod/mdata04.dbf

My thoughts are ... maybe I should put the datafile in offline status ... first ... but I want to verify this before I move forward. Thanks for any advise. Donna
Re: Need to move a new datafile from one tablespace to another tablespace [message #332722 is a reply to message #332717] Wed, 09 July 2008 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you have to put offline the file before moving it.

Regards
Michel
Re: Need to move a new datafile from one tablespace to another tablespace [message #332729 is a reply to message #332514] Wed, 09 July 2008 08:49 Go to previous messageGo to next message
msrite57
Messages: 3
Registered: February 2008
Location: High Point, NC
Junior Member
I tried to put the datafile in the offline mode ... but whenever I selected apply ... I received the following message:

ORA-01145: offline immediate disallowed unless media recovery enabled.

I am not sure ... what this means ... I will say that I only have "Oracle Enterprise Manager" (Standalone version) working at this time. Is it possible for me to set the media recovery to enabled?

Thanks, Donna
Re: Need to move a new datafile from one tablespace to another tablespace [message #332733 is a reply to message #332514] Wed, 09 July 2008 09:01 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Accept previous events as a learning exercise & rebuild the database from scratch.
You've dug yourself a deep hole from which there is no extrication.
Previous Topic: Drop tablespace can't delete datafile
Next Topic: How to register archive log in second destination after failure of 2nd destination
Goto Forum:
  


Current Time: Wed Dec 07 22:42:54 CST 2016

Total time taken to generate the page: 0.27590 seconds