How to quickly move data from one partition of a table to another partition table in orcale?

there are two tables in one of the 1.orcale11g libraries: REALTIME_TABLE (business real-time table) and HISTORY_TABLE (business history table), as follows (the fields of the two tables are consistent, and only some of the fields listed below):

REALTIME_TABLE                  HISTORY_TABLE
    ID        TIME                     ID         TIME
     1  2019-01-01 00:00:00             1   2018-12-31 23:59:58
     2  2019-01-01 00:00:01             2   2018-12-31 23:59:59

where "REALTIME_TABLE" is a partition table with TIME as the partition field and automatically creates the main partition on a daily basis. Each partition has about 80W of data, without any index, and only the data of the past two years are kept for users to use, including batch inserts, batch updates, and report statistics in a small range of time. "HISTORY_TABLE" is a partitioned table that uses TIME as its partition field and automatically creates a primary partition on a monthly basis, with five partition bitmap indexes used for data statistics.
2. Now there"s a scene. Since the number of partitions in orcale11g cannot exceed 1024, I have to transfer the data from more than two years in the REALTIME_ table to the HISTORY_ table. Do you have any good solutions? Is there a sql usage for partition-to-partition data exchange directly?

Note:

1.ORCALE JOBJOBorcale11g;

2. The current solution is to write the (day) partition data in the REALTIME_ table to the temp table, ensure that a temp table corresponds to a natural month of data, and then "partition exchange" between the temp table and the (month) partition in HISTORY_TABLE, and finally delete the temp table. The whole process is implemented in stored procedures, does not affect the related business online, and the JOB is only executed once a year. The advantage of this scheme is that the process is simple, because automatic partitioning is taken into account when building the table, so all partitioning operations are left to the database to handle; the disadvantage is that it takes a long time to write temp tables.

3."" 

has just thought about it. We can first merge the (day) partition of the REALTIME_TABLE table into a large partition PARTITION, then let the PARTITION partition and the temp table carry out "partition exchange", and then carry out the "partition exchange" of the temp table and the (month) partition in HISTORY_TABLE, which can avoid a lot of read and write operations and no longer waste a lot of resources on writing to the temp table. I just don't know if there are statements that partition and partition exchange directly.

Menu