All tables consisting of numbers less than a fixed number

I am trying to find out all the tables where table names consist of numbers less than a fixed number 16284961 at the end preceded by an underscore for example LOG_16282961.

Sample User_segments table:

Segment_name                     Bytes
---------------------------------------
LOG_16282961                     34
BAL1_16282961                    78
BIN$xIDte/qXAFbgU4IeBEeQpw==$0   12
EXCH_16282961                    28
C$_0LOG_16282961                 17
LOG_16283961                     89
BAL1_16283961                    10
BIN$xIDte/qWAFbgU4IeBEeQpw==$0   19
EXCH_16283961                    90
C$_0LOG_16283961                 45
LOG_16284961                     21
BAL1_16284961                    81
BIN$w1RLAvSeAWjgU4IeBEe2Mw==$0   33
EXCH_16284961                    67
C$_0LOG_16284961                 39
.......................................
.......................................

Expected Output:

Segment_name     Bytes
----------------------
LOG_16282961     34
BAL1_16282961    78
EXCH_16282961    28
C$_0LOG_16282961 17
LOG_16283961     89
BAL1_16283961    10
EXCH_16283961    90
C$_0LOG_16283961 45
.......................
.......................

Query:

SELECT segment_name, bytes/1024/1024 AS "SIZE in MB" FROM user_segments WHERE segment_type='TABLE' AND to_number(regexp_substr(segment_name, '[0-9]+')) < 16284961;

Using above query, although I am getting my result but additionally it also includes following tables which are not required in my output:

BIN$xIDte/qXAFbgU4IeBEeQpw==$0   12
BIN$xIDte/qWAFbgU4IeBEeQpw==$0   19
BIN$w1RLAvSeAWjgU4IeBEe2Mw==$0   33

Can you please help fix my query to get the desired output? Thanks.

Answer

Here’s one way – using regexp_substr to isolate one or more consecutive digits at the end of the input string, only if immediately preceded by underscore. (If the string does not have that structure, regexp_substr returns null and the filter condition becomes null < [something], which is never true.)

Create mock-up table for testing:

create table test_data (segment_name, bytes) as
  select 'LOG_16282961'                  , 34 from dual union all
  select 'BAL1_16282961'                 , 78 from dual union all
  select 'BIN$xIDte/qXAFbgU4IeBEeQpw==$0', 12 from dual union all
  select 'EXCH_16282961'                 , 28 from dual union all
  select 'C$_0LOG_16282961'              , 17 from dual union all
  select 'LOG_16283961'                  , 89 from dual union all
  select 'BAL1_16283961'                 , 10 from dual union all
  select 'BIN$xIDte/qWAFbgU4IeBEeQpw==$0', 19 from dual union all
  select 'EXCH_16283961'                 , 90 from dual union all
  select 'C$_0LOG_16283961'              , 45 from dual union all
  select 'LOG_16284961'                  , 21 from dual union all
  select 'BAL1_16284961'                 , 81 from dual union all
  select 'BIN$w1RLAvSeAWjgU4IeBEe2Mw==$0', 33 from dual union all
  select 'EXCH_16284961'                 , 67 from dual union all
  select 'C$_0LOG_16284961'              , 39 from dual
;

Query and output:

select *
from   test_data
where  to_number(regexp_substr(segment_name, '_(d+)$', 1, 1, null, 1))
          < 16284961
;

SEGMENT_NAME                        BYTES
------------------------------ ----------
LOG_16282961                           34
BAL1_16282961                          78
EXCH_16282961                          28
C$_0LOG_16282961                       17
LOG_16283961                           89
BAL1_16283961                          10
EXCH_16283961                          90
C$_0LOG_16283961                       45