Home » RDBMS Server » Server Administration » Object Movement Between Datafiles (oracle 9i windows)
Object Movement Between Datafiles [message #623904] Tue, 16 September 2014 02:47 Go to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi All,

Need a Help,

I just want to move around 250 objects between data files. then I will drop the old datafile, which corrupted.

How we can go for it.

Thanks and Regards
Muktha

Report message to a moderator

Re: Object Movement Between Datafiles [message #623908 is a reply to message #623904] Tue, 16 September 2014 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

See ALTER TABLE MOVE and ALTER INDEX REBUILD statements.

Report message to a moderator

Re: Object Movement Between Datafiles [message #623935 is a reply to message #623904] Tue, 16 September 2014 07:41 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
See Michael's response, but if the data file is corrupted, you very well might not be able to move all of the tables.

How did the corruption manifest itself?
Do you not have an rman backup from which you can recover the file?

Report message to a moderator

Re: Object Movement Between Datafiles [message #624044 is a reply to message #623935] Wed, 17 September 2014 07:38 Go to previous messageGo to next message
martens_b2014
Messages: 13
Registered: June 2014
Junior Member
Moving from one datafile to another is not an option. Creating a new tablespace and from there moving the objects from one tablespace to another maybe can be done, although you will not be sure if the data is consistent.

Point in time recovery just before the corruption is the best.

Report message to a moderator

Re: Object Movement Between Datafiles [message #624061 is a reply to message #624044] Wed, 17 September 2014 08:27 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
martens_b2014 wrote on Wed, 17 September 2014 07:38
Moving from one datafile to another is not an option.


By that I assume you mean moving to another data file within a given tablespace that has multiple data files. If so, true.

Because, of course, if you move from one TS to another you are - by definition - moving from one datafile to another.


... just to clarify for the OP Smile

Report message to a moderator

Re: Object Movement Between Datafiles [message #624083 is a reply to message #624061] Wed, 17 September 2014 10:28 Go to previous messageGo to next message
martens_b2014
Messages: 13
Registered: June 2014
Junior Member
Yes, that is correct.

Report message to a moderator

Re: Object Movement Between Datafiles [message #624138 is a reply to message #624083] Thu, 18 September 2014 04:29 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi All,

Thanks for your replies,

Yes, I just wanted to move the objects in a seperate datafile, but this option is not available in 9i version.
Hence I have moved to a different tablespace.

Now I have dropped the corrupted object and performing BACKUP VALIDATE DATABASE; command through RMAN.
Hope now the physical and logical corruption will get removed.

Thanks
Muktha

Report message to a moderator

Re: Object Movement Between Datafiles [message #624139 is a reply to message #624138] Thu, 18 September 2014 05:06 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi All,

And one more thing,

After moved all the objects to a new tablespace, I have dropped the entire corrupted tablespace.

Then when I execute the comand "v$database_block_corruption" still showing the Data File corruption, which was already dropped along with the Tablespace.

Any reason

Regards
Muktha

Report message to a moderator

Re: Object Movement Between Datafiles [message #624147 is a reply to message #624138] Thu, 18 September 2014 07:46 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
muktha_22 wrote on Thu, 18 September 2014 04:29


Yes, I just wanted to move the objects in a seperate datafile, but this option is not available in 9i version.


Moving them to a different data file within the same tablespace is not a matter of "not available in xx version". It is a fundamental part of Oracle architecture that you DO NOT control contents of individual data files. You can only control which tablespace is used. The tablespace abstracts the data files, and how the individual data files are used is strictly a matter of oracle's own algorithms.

Report message to a moderator

Re: Object Movement Between Datafiles [message #624151 is a reply to message #624147] Thu, 18 September 2014 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

[out of topic]
Quote:
t is a fundamental part of Oracle architecture that you DO NOT control contents of individual data files.


Actually you can allocate extends in a specific file.

Report message to a moderator

Re: Object Movement Between Datafiles [message #624153 is a reply to message #624151] Thu, 18 September 2014 08:51 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Michel Cadot wrote on Thu, 18 September 2014 08:10


Actually you can allocate extends in a specific file.



I stand corrected.

Though I doubt this would address the OP's issue at all.

Report message to a moderator

Re: Object Movement Between Datafiles [message #624160 is a reply to message #624153] Thu, 18 September 2014 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

No, this is why I marked it as "out of topic". Smile

Report message to a moderator

Re: Object Movement Between Datafiles [message #624211 is a reply to message #624160] Fri, 19 September 2014 02:09 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi,

Any one please answer to my question,

I dropped a fractured datafile by dropping the tablespace.

Still the view V$DATABASE_BLOCK_CORRUPTION view showing the block corruption which was already dropped.

Regards
Muktha

Report message to a moderator

Re: Object Movement Between Datafiles [message #624319 is a reply to message #624211] Sun, 21 September 2014 09:57 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Still the view V$DATABASE_BLOCK_CORRUPTION view showing the block corruption which was already dropped.

Is this still true after a database restart?

Report message to a moderator

Previous Topic: CPU usage for Oracle DB
Next Topic: Example about PCTUSED and PCTFREE in oracle 11g.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Feb 01 20:46:25 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.