Oracel DB Move huge size partition using transportable tablespace in shell script

Question

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:

1. daily create a tablespace with a datafile
2. make new tablespace transportable
3. alter user and set it's default tablespace to our new one
4. after some time when data are old, export table using:

expdp transport_tablespaces={our new tablespace}

5. copy data file from source DB to destination DB
6. import at destination DB using:

impdp transport_datafiles='/path/to/data/{data file name}.dbf'

7. if anything went well, drop source partition and free the space

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

Answer 1

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:

1. As each of your new tablespaces will host a partition, you cannot export it as such, you must exchange the partition with a table, with indexes, ... and then do the export.
2. you may run into the limit on the number of tablespaces you can create and manage, .... or the number of data files, ...unless you pay attention to what you're doing, ...
3. on the archive db, you'll have to import the tablespace, then do an exchange partition again. And depending on the number of partitions you need to keep, you may again run into the limit on the number of tablespaces. What you can do is to import the tablespace, do an alter table move to put the data from this tablespace into one that has other partitions, and the drop the imported tablespace.