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