Oracle SQL selects to get familiar with table naming standard and to find junk tables in scheme

Run following selects to discover prefix and suffix hierarchies and to get count of particular prefix/suffix occurrences. That will help you become familiar with naming standard and to find suspicious prefixes/suffixes that belong to tables that probably store junk data.

with prefix_generator( prefix, p_prefix, object_name, lvl ) as (
  select regexp_substr( object_name, '.*?_' ) prefix
    , null as p_prefix
    , object_name as object_name 
    , 1 as lvl
  from ( 
    select table_name as object_name
    from all_tables
    where owner like 'YOUR_OWNER'
      and temporary like 'N'
  ) q0
  union all
  select g.prefix || regexp_substr( 
      substr( g.object_name, length( g.prefix ) + 1 ), '.*?_' 
    ) as prefix
    , g.prefix as p_prefix
    , g.object_name
    , g.lvl + 1
  from prefix_generator g
  where regexp_substr( 
      substr( g.object_name, length( g.prefix ) + 1 ), '.*?_' 
    ) is not null
) 
, groupped_generator as ( 
  select prefix, 
    p_prefix
  from prefix_generator
  group by prefix, 
    p_prefix
)
, forward_builder( 
  prefix
  , lvl
  , padded_prefix 
) as ( 
  select g.prefix
    , 0 as lvl
    , g.prefix padded_prefix
  from groupped_generator g
  where g.p_prefix is null
  group by g.prefix
  union all
  select n.prefix
    , p.lvl + 1 as lvl
    , lpad( ' ', ( p.lvl + 1 ) * 4 ) || n.prefix padded_prefix
  from groupped_generator n
  join forward_builder p
  on p.prefix = n.p_prefix
) search depth first by prefix set order_by 
, count_by_prefix as (
  select prefix
    , count( * ) cnt
  from prefix_generator g
  group by prefix
)
select b.padded_prefix
  , c.cnt
from forward_builder b
join count_by_prefix c
  on c.prefix = b.prefix
where 2 < c.cnt
order by order_by
;/

with postfix_generator( postfix, p_postfix, object_name, lvl ) as (
  select regexp_substr( object_name, '_.*' ) postfix
    , object_name as p_postfix
    , object_name as object_name 
    , 1 as lvl
  from ( 
    select table_name as object_name
    from all_tables
    where owner like 'YOUR_OWNER'
      and temporary like 'N'
  ) q0
  union all
  select regexp_substr( 
      substr( g.postfix, 2 ), '_.*' 
    ) as postfix
    , g.postfix as p_postfix
    , g.object_name
    , g.lvl + 1
  from postfix_generator g
  where g.postfix is not null -- recursion breaker
) 
, groupped_generator as ( 
  select postfix, 
    p_postfix
  from postfix_generator
  where p_postfix <> object_name
  group by postfix, 
    p_postfix
  
)
, backward_builder( 
  p_postfix
  , lvl
  , padded_p_postfix 
) as ( 
  select g.p_postfix
    , 0 as lvl
    , g.p_postfix padded_p_postfix
  from groupped_generator g
  where g.postfix is null
  group by g.p_postfix
  union all
  select n.p_postfix
    , p.lvl + 1 as lvl
    , lpad( ' ', ( p.lvl + 1 ) * 4 ) || n.p_postfix padded_p_postfix
  from groupped_generator n
  join backward_builder p
  on p.p_postfix = n.postfix
) search depth first by p_postfix set order_by 
, count_by_postfix as (
  select postfix
    , count( * ) cnt
  from postfix_generator g
  where postfix is not null
  group by postfix
)
select b.padded_p_postfix
  , c.cnt
from backward_builder b
join count_by_postfix c
  on c.postfix = b.p_postfix
where 2 < c.cnt
order by b.order_by
;/

To select all prefixes and suffixes (not only those having more than two occurrences) delete 'where 2 < c.cnt' clause.