Can you Please advise on implementing below logic.
I need to create partition dynamically through script like adding partition to a table for that sysdate such as add partition to table A and partition name has convention like P1_yyyymmdd where yyyymmdd will be the date when script is running. After partition is created, then I need to load data to the table A for that day's data. Once data is loaded, I need to extract partition name which is equal to (sysdate - 2weeks) from Table A and load that partition's data(sysdate-2 weeks) to historical table. Once the count of rows for 2 weeks older data is matched from historical table (A_HIST ) with Regular table A, then drop that 2 week older partition in regular table A. This way retention of table A will have only 10 days of data at a time and older data will be archived to historical table(A_HIST).
Eg- Script run on 23rd oct 2009, so partition name will be P1_20091023 and data will be loaded for that day into table A. Now the date older than 2 weeks from this day(which is 9th oct) will be loaded from Table A to historical table(A_HIST).
Say 100 rows belong to 9thoct2009 partition(which is P1_20091009) which we checked from Table A and now the same 100 rows should be loaded to historical table from Table A. Once the number of rows 100 are loaded to historical table (A_HIST),the older partition P1_20091009 should be dropped from regular table A.
This way, we will always keep 10 days of data in Table A and historical data in Table A_HIST.
Thanks in advance