Find unwanted tables in Production

As DBA, you will find your database does not just storing valid production tables but also other tables like TMP, BKP, BACKUP, DDMMYYYY format etc etc.  These tables take up space and especially during backup it will slow down process.  Below script will identify these tables:

SELECT the_table.database,
        CASE WHEN the_table.objclass = 4905  THEN 'Table    '
             WHEN the_table.objclass = 4910  THEN 'MView    '
             WHEN the_table.objclass = 4940  THEN 'TempTable'
             WHEN the_table.objclass = 4953  THEN 'Sec Table'
             WHEN the_table.objclass = 4951  THEN 'Sec Table'
             WHEN the_table.objclass = 4959  THEN 'Sec Table'
             WHEN the_table.objclass = 4961  THEN 'Table    '
             WHEN the_table.objclass = 4963  THEN 'Table    '
                                             ELSE 'What?    '
        END AS "Object",
        MAX
        (   CASE WHEN the_table.objclass = 4910                THEN   SUBSTR(the_table.objname, 3)::nvarchar(128)
                 WHEN the_table.objclass in (4951,4959,4963)   THEN NULL
                 WHEN the_table.objclass =  4940 then the_table.objname::nvarchar(128)
                        || nvl( '/' || _vt_tbldict.tblname,'')
                 ELSE   the_table.objname::nvarchar(128)
            END
        )
        ||
        MAX( case when the_table.objclass in (4959,4963) then ' (Versioned)' else '' end)
        AS "Name"
  ,       TO_CHAR ( nvl(SUM (  used_bytes  ),0)                     , '999,999,999,999,999'  ) AS "Bytes"
  ,          TO_CHAR ( nvl(SUM (  used_bytes  ),0) / (          1024 ) ,     '999,999,999,999'  ) AS "KB"
  ,          TO_CHAR ( nvl(SUM (  used_bytes  ),0) / (       1048576 ) ,         '999,999,999'  ) AS "MB"
  ,          TO_CHAR ( nvl(SUM (  used_bytes  ),0) / (    1073741824 ) ,             '999,999.0') AS "GB"
  ,          TO_CHAR ( nvl(SUM (  used_bytes  ),0) / ( 1099511627776 ) ,                 '999.0') AS "TB"
FROM  _V_OBJ_RELATION_XDB the_table
      left outer join _V_SYS_OBJECT_DSLICE_INFO on
      (
               the_table.objid = _V_SYS_OBJECT_DSLICE_INFO.tblid   and
               _V_SYS_OBJECT_DSLICE_INFO.tblid > 200000
      )
    left outer join _VT_TBLDICT on the_table.objid = _VT_TBLDICT.tblid
WHERE              the_table.objclass in (4905,4910,4940,4959,4951,4953,4961,4963)
               and the_table.objid > 200000
and
(
the_table.objname like '%0%'
or the_table.objname like '%1%'
or the_table.objname like '%2%'
or the_table.objname like '%3%'
or the_table.objname like '%4%'
or the_table.objname like '%5%'
or the_table.objname like '%6%'
or the_table.objname like '%7%'
or the_table.objname like '%8%'
or the_table.objname like '%9%')
and (the_table.objname not like 'NZCC_%'
and the_table.objname not like '_V_%'
and the_table.objname not like 'UAC_%'
and the_table.objname not like 'NZORCH_%'
and the_table.objname not like '%#%'
and the_table.objname not like '%$%')
GROUP BY the_table.DATABASE,
        "Object",
        the_table.visibleid;