Hi Team,
We have a requirement wherein there are 30 backup tables which are currently non partitioned and each table have data around 15-30TB's. Size of the table is very huge.
We have to write a script which will create partitioning of a backup table by just providing the table name. The script should be dynamic in a way that we will provide only table name and it will create dynamic partition based on backup_date column(which is there in each backup table).Each partition will be of 1 week time.For 1 week we have 1 partition and for another we have another partition.
I have written one script for this:
create or replace procedure table_part(p_table_name varchar2)
is
backup_date_col back_date%type;
cursor cur_part is(select table_name from all_tables where table_name like('bck%')) ;
begin
for i in cur_part loop
EXECUTE IMMEDIATE 'alter table '||x.table_name||' modify partition by range '
||backup_date_col||' interval(NUMTODSINTERVAL(7,'day'))'
||'partition '||i.table_name||'_'||'part_01'||' values less than(' "||to_date(sysdate,'dd-mm-yyyy'))||"
||' online';
end loop;
end ; Please let me know if this will work or not.
Also when 1 week partition are creating on its own then after 1 week is complete we have to drop the 1 week partition of all backup tables. But since I am using "INTERVAL" clause while creating partition then I don't know the exact partition name as it will have a system generated name.
Can you please help me in guiding , how to drop the last week partition of all backup tables dynamically. This script should also be dynamic in a way that whatever table_name we will provide , for that particular table last week partition should drop .
What indexes should I use while creating partition : "Local" or "Global"?
Which index will be beneficial and why?
In principle, provided you're on 12.2 yes you can do something like this. In 12.1 and earlier you'd have to go for a more complex dbms_redefinition approach.
But there are many bugs in that procedure!
Remember, the form of the alter table statement is:
alter table ...
modify partition by range ( ... )
interval ( NUMTODSINTERVAL ( 7, 'day' ) ) (
partition BCKT_part_01 values less than (
to_date ( '...', 'dd-mm-yyyy' )
)
) online;Check your statement carefully to ensure it matches this.
Also defining the upper bound for the initial partition as today means all past dates will go in a single partition. To avoid this, either find the lowest backup date in the table. And pass that to your alter table. Or pick a date which is before the first date in every table (e.g. 01-01-2000).
To drop the oldest partition, you can find the name of the "first" partition by querying *_tab_partitions for the position 1:
select partition_name
from user_tab_partitions
where table_name = :tab_name
and partition_position = 1
Be aware that there are issues with dropping the last "real" partition in an interval partitioned table. Connor discusses these at
https://connor-mcdonald.com/2015/11/25/ora-14758-last-partition-cannot-be-dropped/ There's no single answer when it comes to local vs. global indexes. This is a topic we've covered many times already on this site. Here's a couple for starters:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5931711000346922149 https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9503267200346596310