OraFAQ Forum: Server Administration » Need to move a new datafile from one tablespace to another tablespace

Home » RDBMS Server » Server Administration » Need to move a new datafile from one tablespace to another tablespace (Oracle Enterprise Manager 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need to move a new datafile from one tablespace to another tablespace [message #332514]

Tue, 08 July 2008 15:38

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

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, you have to put offline the file before moving it.

Regards
Michel

Report message to a moderator

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

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

Report message to a moderator

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 01:45:47 CST 2024