Automating drop partitions for set of Partition tables(Retention based on No OF DAYS)

Automating drop partitions for set of Partition tables(Retention based on No OF DAYS)

We care for your speed. Below is an easy way to perform  Automating drop partitions for set of Partition tables(Retention based on No OF DAYS)

To Automate the script follow below steps

Step 1 :

Create  required tables with partitions

STEP 2:

Insert records into table

While inserting put different dates on MIC_ID column  to test more efficiently.

Connect as SYS and execute below lines .

Now check count of records in table

STEP 3:

Check  Partitions available on MIC_INS_PART table

Totally we have 26 partitions

Now Our requirement is  to keep only latest Ten days partitions(10 Days).

STEP 4 :

To implement above requirement Create on driving table like below

STEP 5:

Insert a record into driving table that how many no of days partitions to keep.

STEP 6:

Now we all set to create procedure  to drop partitions (based on No of days )

Note :

The above script will first touch the driving table to collect table name  and No of days to keep partitions Then it will execute drop partitions command as per the driving table.

If we want to to drop multiple drop partitions then we can insert  multiple tables details  into DRIVING TABLE CALLED   TEMP_PARTITIONS_TABLES

STEP 7:

Now time to execute Procedure

Note :

On Above execution 15 partitions have been dropped Which are older (EXCEPT THE FIRST PARTITION)

STEP 8:

Now on table we have latest partitions Only

To check execute below command

I hope the above examples are helpful to drop partitions (based on NO OF DAYS).

Thank you for giving your valuable time to read the above information.If you want to be updated with all our articles send us the Invitation or Follow us :

Facebook Page: KTexperts

Ajay Kumar’s Linkedin : https://www.linkedin.com/in/ajay-kumar90/

 

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

Add Comment