The power of scripting

So your system has a neat automated archive and purge function for your rolling partitions, driven by the PART_RETENTION table which holds the table name, partition type ('DAILY', 'MONTHLY', 'QUARTERLY' or 'YEARLY') and how many of each to keep. As a general rule, you want 35 dailies, 13 month-ends, 5 quarter-ends and 2 year-ends for each table. Let's say you have ten tables. All you need is a handy script to set that up. This is what someone actually came up with. (This is just an example. There were a lot more tables).

insert into part_retention (table_name, partition_type, retention ) values 'COUNTRIES', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'DEPARTMENTS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'EMPLOYEES', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'JOBS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'JOB_HISTORY', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'LOCATIONS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'REGIONS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'SALES', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'ORDERS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'ORDER_ITEMS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'CUSTOMERS', 'DAILY', 35);
insert into part_retention (table_name, partition_type, retention ) values 'COUNTRIES', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'DEPARTMENTS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'EMPLOYEES', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'JOBS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'JOB_HISTORY', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'LOCATIONS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'REGIONS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'SALES', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'ORDERS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'ORDER_ITEMS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'CUSTOMERS', 'MONTHLY', 13);
insert into part_retention (table_name, partition_type, retention ) values 'COUNTRIES', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'DEPARTMENTS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'EMPLOYEES', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'JOBS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'JOB_HISTORY', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'LOCATIONS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'REGIONS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'SALES', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'ORDERS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'ORDER_ITEMS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'CUSTOMERS', 'QUARTERLY', 5);
insert into part_retention (table_name, partition_type, retention ) values 'COUNTRIES', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'DEPARTMENTS', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'EMPLOYEES', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'JOBS', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'JOB_HISTORY', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'LOCATIONS', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'REGIONS', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'SALES', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'ORDERS', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'ORDER_ITEMS', 'YEARLY', 2);
insert into part_retention (table_name, partition_type, retention ) values 'CUSTOMERS', 'YEARLY', 2);