we have a huge DB with daily 20Mil records,
We have an interval partitioned table on creation time column(filled with Sysdate at before insert trigger)
As this data are important and cannot be purged and storage runs out after some time, We have used impdp and expdp to archive old data(we keep few months) and monthly export one partition and import it at archive DB
Disadvantage of this scenario is Dropping exported partition after complete import, Does not completely free the storage, it seems our tablespaces are the problem
Another disadvantage is the data is keep growing and export and import time has reached more than mere hours and it's nearly 2 days, which affects our service quality
Newly we're thinking about using Transportable Tablespaces
I'm not what scenario to use here
Is this right to do this:
default tablespace
to our new oneexpdp transport_tablespaces={our new tablespace}
impdp transport_datafiles='/path/to/data/{data file name}.dbf'
Personally, I'm not sure if my scenario is right, did I understand Transportable Tablespace correctly?
If my scenario is correct, Can you provide a shell-script to automate this to be done
First, there's no such a thing as 'make a tablespace transportable' in Oracle. You can do what you outline, but, there are some modifications: