Bad DBA 🙁

Are you an Oracle DBA who needs to free up disk space? Does your database have the recycle bin enabled? Did you know that the recycle bin just renames your segments when you drop them and doesn’t free up any space in your tablespace?

Here’s a query you can run to see the segments that are in your recycle bin that are still taking up space in your tablespaces.

set pages 999
set lines 200
column owner format a20
column original_name format a30
select r.owner, 
       r.original_name, 
       r.type, 
       r.ts_name, 
       r.droptime, (t.block_size * r.space)/1024/1024 as megabytes
  from dba_recyclebin r, 
       dba_tablespaces t
 where t.tablespace_name = r.ts_name
 order by r.ts_name, 
          r.owner,
          r.original_name;

In this query, we are joining dba_recyclebin to dba_tablespaces to get the block size, since tablespaces may have custom block sizes and we’re not relying on the db_block_size initialization parameter.

Do you need all of these segments? Can they be purged to free up space? Make sure you talk to your users before you purge your recycle bin.