Does your database have a lot of empty tables of (potentially) unknown origin? While the returned space is usually insignificant, there are some simple things you can do to help manage these.
An empty table may or may not have a segment in a tablespace. It does not need one, now that we have defered segment creation, so let’s get rid of those. This SQL is helpful with that:
— empty table management
SELECT ‘exec DBMS_STATS.GATHER_TABLE_STATS(”’||owner||”’,”’||table_name||”’);’ stats_cmd,
‘select count(*) from ‘||owner||’.’||table_name||’;’ cnt_cmd, ‘truncate table ‘||owner||’.’||table_name||’ drop all storage;’ trunc_cmd
FROM dba_tables WHERE num_rows = 0 AND temporary = ‘N’ AND nested = ‘NO’ AND segment_created = ‘YES’
AND owner NOT LIKE ‘%SYS%’ AND owner NOT LIKE ‘APEX%’ AND owner NOT IN (‘OUTLN’, ‘PUBLIC’, ‘DBSNMP’, ‘XDB’, ‘ORDDATA’);
There are 3 columns here, the first gathers new stats for the tables. This is very safe and very fast and will eliminate from consideration any tables that actually have rows.
The second column is a set of count commands, you can run these to verify that all the tables are empty, because the third column is DDL to truncate the tables and remove the segments. You want to be careful with this of course, hence the first 2 columns. I would visually check the listing before running it, and of course a backup of the tables is always a good idea. In fact, exporting the tables is an other way to confirm they are truly empty since the expdp logs show the records exported.
Note that if there are foreign key dependencies on any table, the truncate will fail. It’s up to you if it is worth disabling and re-enabling the dependent FK around the truncate.
If these tables were created by your own development process, you might want to give a list of tables to development for possible removal. You can check that the tables are not used by any objects inside the db with the following SQL. This does not prove that no outside code (or dynamic SQL inside PL/SQL) uses the tables, so you still must be cautious and remove these first in an environment where thorough testing takes place.
— empty tables with no “in db” dependencies
— empty tables
SELECT owner, table_name FROM dba_tables
WHERE num_rows = 0 AND temporary = ‘N’ AND NESTED = ‘NO’
AND owner NOT LIKE ‘%SYS%’ AND owner NOT LIKE ‘APEX%’ AND owner NOT IN (‘OUTLN’, ‘PUBLIC’, ‘DBSNMP’, ‘XDB’, ‘ORDDATA’)
MINUS
— empty tables with “in db” dependencies
SELECT referenced_owner, referenced_name FROM dba_dependencies d
WHERE (referenced_owner, referenced_name) IN
(SELECT owner, table_name FROM dba_tables
WHERE num_rows = 0 AND temporary = ‘N’ AND NESTED = ‘NO’ AND owner NOT LIKE ‘%SYS%’ AND owner NOT LIKE ‘APEX%’
AND owner NOT IN (‘OUTLN’, ‘PUBLIC’, ‘DBSNMP’, ‘XDB’, ‘ORDDATA’));