Taking free space out after TDMS copy

[Former Member]

Hello Experts,

I have performed a TDMS copy from Production to one of our quality systems. Since I have taken only 6 months data the size of our quality system Database is reduced which was previously equal to Production.

1)At present around 1 TB of space is free in PSAPSR3 tablespace whichI want to bring to OS level filesystem. I do not wantto go for whole tablespace reorg due to some specific reasons like large no of tables with Long fields, space issues etc.

  If anyone of you have faced a similarsituation pleasedo guide.

Regards,

Umesh

[rajesh_kumar2]

Hi Umesh,

It seems you may have used Array delete in your TDMS copy process during data deletion , may be due to other active clients in the receiver system.

In such cases DB Reorg is the only way to release the free space from table spaces.

In case you do find a any other new way to achieve it, please do share for all the users

Thanks,

Rajesh

[Former Member]

Hello Rajesh ,

Thanks for the reply .

Would Like to update you that I did not changed the deletion methodoly and it was default i,e Drop-Insert as used by TDMS data deletion activity.

Also with some oracle queries I was able to take all the spaces in data files above HWM . But even after doing all this there is 1 TB showing free in PSAPSR3 .

Now this would have happened with every one who usses TDMS to refresh his Quality system .

So if anyone else had phased the same issue please do update us.

Regards,

Umesh

[rajesh_kumar2]

Hi Umesh,

You are correct, just by adjusting the data files to their respective HWM's will not be sufficient to

reclaim the logical free space at the DB level.

A DB re-org is surely required here.

Nonetheless ..in case you  have some constraints over running full table re-org, you could actually use some SQL command to pull out the top 50 tables and then run re-org for this group of tables.

Generally in most cases, a TDMS copy is followed by a full re-org, otherwise may also lead to slow performance and of course this situation of free space availability.

Thanks,

Rajesh

[Former Member]

Hello Rajesh,

I already found tables for reorg and tried reorg for some of those tables , but the space released after table reorg is at tablespace level and not at OS file system.  So again the issue remains the same to pull out space from tablespace without reorg.

I have searched various forums but everthing at the end comes to tablespace reorg . Since I have space contraints , I am searching for workaround for this.

Regards,

Umesh.