Relocation data from one data file to another datafile

[former_member249119]

Dear all,

I will perform big clean up of SAP system. And after reorganization i will need to remain only 2-3 datafiles of PSAPSR3 tablespace. I know that i can`t to drop not empty datafile. How to relocate data from not empty datafile to another, where space is enough?

BR, Albert

[Former Member]

Hi Albert,

Information provide by folks in replied are very helpful, go through the note.

In case you have LONG and LONG RAW columns in the tables system/oracle  need outage mean Oracle need to be offline unless you change LONG and LONG RAW to LOBs or CLOBs before hand.

Another point , you need a good amount space on Archive log, As sometimes backup process slower than Oracle created offline redo log file or turn off Archive log in case system is non-prod.

Good luck.

Thanks,

Hardeep

[manish_singh13]

Hi Albert,

How are you going to perform cleanup/reorganization? I believe you can achieve this directly within reorg process (Creation of new tablespace). Please check below SAP Note to check reorg processes with different options.

646681 - Reorganization of tables with BRSPACE

Thanks,

Manish

[Former Member]

Hello Albert,

The short answer is no you cannot move an object from one datafile to another. For what you are wanting to achieve you would have to:

1. Identify the objects in the datafile you want to reclaim.

2. Create a new tablespace (eg: PSAPSR3TMP)

3. Reorg the objects to the new tablespace

4. Perform the same check from step #1 to ensure that there are no objects left in the datafile.

5. Drop the datafile.

6. Reorg objects from step #3 back to PSAPSR3.

That's just a very high level overview of what you would have to do.

KR,

Amerjit