4 Steps to Spring Clean Your Databases
It’s spring time, and that comes with spring cleaning not only your house and closets, but your databases too! What do I mean by spring cleaning your database?
Well, look at the objects, schemas and users that are no longer in use and start retiring them! You could even check tablespaces that have no segments, and retire those as well!
Today’s post will focus on finding old tables created by you or someone else, with the sole purpose of backing up the original table.
How and when do these tables get created? Usually, during a deployment exercise when either the table structure or table data gets altered, you create a backup of a table with the CTAS method, and add a suffix to it, in the format of a date, partial date, _old, _backup, _bkup, _bck … you name it! Everyone has good intentions of dropping these tables after a set period of a time… but you know what happens? We forget about them! So they take up space in the database, they get backed up to disk, they get backed up to tape, they get backed up to the cloud.
Every now and then, it is good practice to identify these objects and drop them! Below you will find the 4 steps I use, let’s go through them!
Step 1. Identify the unused tables.
Look for patterns. How do you name the backup tables? Do you add a backup suffix, or a date? I’ll show you the SQL statement I use.
I look for the following strings: old, bck, bkp, xx, zz, or numbers in the table name. I also like to include the global_name of the database, especially if I run the script in multiple databases. When I save the output, I know exactly where the results are coming from. In this example, I only excluded SYS and SYSTEM (for simplicity), but feel free to exclude all the other Oracle delivered schemas.
col global_name for A20
col owner for A15
col table_name for A20
set linesize 100
set pagesize 100
select global_name, owner, table_name
from dba_tables, global_name
where owner not in ('SYS','SYSTEM')
and (table_name like '%OLD%'
or table_name like '%BCK%'
or table_name like '%BKP%'
or table_name like '%BK%'
or table_name like '%ORIG%'
or table_name like '%ZZ%'
or table_name like '%XX%'
or regexp_like(table_name, '[0-9]'))
order by owner, table_name;
GLOBAL_NAME OWNER TABLE_NAME
-------------------- --------------- ----------------------
HRDEV.MYDOMAIN DIANA LOCATIONS_20220212
HRDEV.MYDOMAIN HR BKJULY16_EMPLOYEES
HRDEV.MYDOMAIN HR BKJULY16_DEPARTMENTS
HRDEV.MYDOMAIN SCOTT EMP_MAR2022
HRDEV.MYDOMAIN SCOTT DEPT_2
...
Step 2. Confirm the tables.
You must confirm with other users, developers and DBAs, that the list of objects above, can be dropped indeed.
This step is crucial. You do not want to drop something by mistake or negligence.
The above statement will also return object that might be in use (see DEPT_2 table), as they have a number in the name. Make sure you exclude these objects from your cleanup list!
Step 3. Backup the tables
The tables you want to drop, must be backed up with datapump export to cover your back! Remember A Good DBA Always Has A Rollback Plan! This is your rollback, do not skip it, especially for production databases.
If you are paranoid about dropping these tables, you could also turn on auditing for a set period of time, to confirm that nobody is using the tables. And only then drop them.
Step 4. Drop the unused objects, identified in Step 1 and confirmed in Step 2!
Now you have a nice a clean database!
Do you “spring clean” your database? How did you do it? Remember there are many ways to do something, this is just one way!
If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!
If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $13.99 CAD !
