Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name ( column_name ) AS
SELECT 'REST_API_6007' FROM DUAL UNION ALL
SELECT 'REST_API_6'    FROM DUAL UNION ALL
SELECT 'REST_API_4006' FROM DUAL UNION ALL
SELECT 'REST_API_4000' FROM DUAL UNION ALL
SELECT 'REST_API_3000' FROM DUAL UNION ALL
SELECT 'REST_API_3'    FROM DUAL UNION ALL
SELECT 'REST_API_2'    FROM DUAL UNION ALL
SELECT 'REST_API_1'    FROM DUAL;
8 rows affected

SELECT *
FROM   table_name
ORDER BY SUBSTR(column_name, 1, 9) DESC,
         TO_NUMBER(SUBSTR(column_name, 10)) DESC;

COLUMN_NAME

REST_API_6007

REST_API_4006

REST_API_4000

REST_API_3000

REST_API_6

REST_API_3

REST_API_2

REST_API_1

SELECT *
FROM   table_name
ORDER BY SUBSTR(column_name, 1, INSTR(column_name, '_', -1)) DESC,
         TO_NUMBER(SUBSTR(column_name, INSTR(column_name, '_', -1) + 1)) DESC;

COLUMN_NAME

REST_API_6007

REST_API_4006

REST_API_4000

REST_API_3000

REST_API_6

REST_API_3

REST_API_2

REST_API_1

SELECT *
FROM   table_name
ORDER BY REGEXP_SUBSTR(column_name, '^(\D+)(\d+)$', 1, 1, NULL, 1) DESC,
         TO_NUMBER(REGEXP_SUBSTR(column_name, '^(\D+)(\d+)$', 1, 1, NULL, 2)) DESC;

COLUMN_NAME

REST_API_6007

REST_API_4006

REST_API_4000

REST_API_3000

REST_API_6

REST_API_3

REST_API_2

REST_API_1