Moving Data to New Datafile 1

[Michael42]

Hello,

I have a tablespace that is made up of 25 datafiles. A disk that holds some of the datafiles will be going away.

What are my options to move the corresponding data to another datafile?

If this could be done online that would be ideal.

Thanks,

Michael42

 

[carp]

One way would be to query dba_extents and dba_data_files to determine which segments have extents in the datafile that is going away.
Next, move the segments to a different tablespace.
Once the datafile is empty, drop it from your database.
Finish by moving the segments back to the original tablespace.

Kind of a blunt object approach and hopefully somebody (SantaMufasa comes to mind!) will have a better way to do it - but it's early and the coffee isn't ready yet!

 

[SantaMufasa]

Michael (and Dave aka "Carp"),

I would take a slightly different approach if all you are trying to do is to free up the drive that is going away. I would:

ALTER SYSTEM CHECKPOINT;
SHUTDOWN IMMEDIATE
(perform a cold backup of the entire database)
(move the data files that reside on the subject drive to some other drive)
STARTUP MOUNT
ALTER DATABASE RENAME FILE
     '<fully qualified previous filename reference 1>'
    ,'<fully qualified previous filename reference 2>'
    ...
    ,'<fully qualified previous filename reference n>'
    TO
     '<fully qualified new filename reference 1>'
    ,'<fully qualified new filename reference 2>'
    ...
    ,'<fully qualified new filename reference n>';
ALTER DATABASE OPEN;

Let us know what you end up doing and how it goes.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

Once the datafile is empty, drop it from your database.

...as a word of warning, whenever I have tried this approach, I have always regretted it as it produced unrecoverable database errors.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

...More coffee for my men and their horses!..."

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[carp]

Ha! I KNEW SM would have a better way!

"I should have thought of it with my brain!"
- Scarecrow

 

[Michael42]

Santa,

Very nicely done!

Thanks for posting,

Michael42