Declutter Your Database The Right Way

I don’t like clutter, whether it is in my home or database. So when a project manager (PM) came to me and said, we have a project to cleanup some databases of invalid and unused objects (clutter that is) I was jumping up in joy.

Having invalid objects in the database it’s like having broken toys and bicycles left on your front lawn, it clutters your space, and your database.
Having unused object in the databases it’s like having all the clothes in your closet you haven’t wore in the past 5 years, and keeping them just in case. It clutters your space and your database.

Decluttering your databases is like giving a Fengshui makeover to your systems!

This is a great idea, but it can be daunting and also risky if you drop the wrong objects. That is why people are afraid of proceeding with it. So here is the question: “Where and how do I start?”.

1. Getting Rid Of Invalid Objects
2. Getting Rid Of Unused Objects
3. Preparing To Declutter Your Database
4. Executing Your Decluttering Plan

1. Getting Rid Of Invalid Objects

What are invalid objects?
These are objects in the database such as views, synonyms, procedures, triggers and so on, that will not compile successfully.
When you compile them, they will compile with errors.
Sometimes objects become invalid temporarily, when you are modifying a dependent object. If that is the case, the object should compile successfully at the next attempt.

One thing is for sure with invalid objects (the ones that will not compile): nobody can use them, as these objects will return an error if you try to use them.

Thus there are two ways to deal with invalid objects:

a) Validate them. Investigate why they are invalid, and fix the root cause, if it’s something that can be fixed.
b) Drop them. If these objects cannot be validated, the best is to get rid of them.

2. Getting Rid Of Unused Objects

What are unused objects? This is a tricky question. What I might think an unused object is, might be different from what the Application/Developer Group might think.
In my opinion, unused objects are objects that have been identified as not being used, by the team that is responsible for these objects, and also have been confirmed with proof from the DBA that are not used.
How does a DBA confirm that an object is not used? By turning on auditing.

Warning!!!

You cannot drop objects in the database because someone says these are not used! Never assume the objects are not used. As a matter of fact A Good DBA never assumes anything!!!

Once you collect the evidence that the objects are not used, then and only then you can plan to drop them.

Usually when I tell people, auditing needs to be turned on, they get worried that it will affect performance. But if indeed these objects are not used/accessed, then no audit records should be created, thus no overhead.
Once an object is identified as used, through an audit record, you can disable auditing for that object.

In order to get rid of the unused objects, the DBA needs to work very closely with the Application/Developer Group to identify the list.

3. Preparing To Declutter Your Database

You should not proceed with this process, unless you have a plan. How are you going to prove that the objects are not used? I suggest you create a repeatable process for all your databases.

Step 1 – DBA to identify invalid objects, and provide list to Application/Developer Group
Step 2 – Application/Developer Group to determine the faith of invalid objects (either fix them or decide to drop them)
Step 3 – Application/Developer Group to determine the list of unused objects in the database. This is usually done through analyzing the code. Provide list of objects to the DBA.
Step 4 – DBA to turn on auditing for any operation on the objects identified in Step 3.
Step 5 – DBA to create a script to notify you in case an entry for the objects in Step 3 has been generated in the audit trail.
Step 6 – DBA and Application/Developer Group work together and review audit entries if applicable
Step 7 – Leave auditing on for 1-3-6-12 months depending on the business processes. Are there any processes that would run only once a quarter/year? The longer the auditing is on, the better your proof is.
Step 8 – DBA and Application/Developer Group finalize the list of objects to be retired.

This is your pre execution plan. The success of your decluttering process, depends on how well you implemented these steps.

4. Executing Your Decluttering Plan

You have identified the objects that will be dropped (invalid and/or not used).
You can prepare to drop them. But don’t proceed without a plan!
Any plan that you create should always have an option of rolling back. No matter if the objects are not used, you need to be able to get them back quickly.
This is why you will start with the end in mind, and ask yourself the question: “How can I rollback my changes quickly?”
You will also execute your plan in the test environment first, and not production.

Step 1 – create your rollback plan.
Step 2 – backup all your objects, for tables use datapump export, or rename the table and all dependent objects. For views, procedures, triggers and other objects use dbms_metadata.get_ddl to extract the object ddl, grants and dependencies.
Step 3 – drop invalid objects, drop unused objects
Step 4 – test. test. and test again to confirm everything is working
Step 5 – extra step in the test environment: rollback your change, by executing your rollback plan created in Step 1.

There you have it, how to declutter your database!

Have you ever decluttered your databases? Leave a comment in the comments section, I read every comment!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana