Automating drop partitions for set of Partition tables(Retention based on Selected Dates)

Automating drop partitions for set of Partition tables(Retention based on Selected Dates)

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

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_TAG 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 :

Create another table which can keep Required  dropped dates

STEP 6 :

Create a procedure

The below procedure will actual drop  partitions tables from Driving table called TEMP_PARTITIONS_TABLES.

The dates will be taken from TEMP_DATES table.

STEP 7 :

Execute the procedure

Now check partitions after dropping

I hope the above examples are helpful to drop partitions (based on SELECTED DATES).

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 (No Ratings Yet)
Loading...

Add Comment