In addition to the Lateral and Cross apply , another option would be to use JSON_TABLE, something like this

demo@ORA26AI> select *
  2  from IIO_SYSTEM_PARAMETER ,
  3      json_table( json('['||replace(value,'/',',')||']') ,'$[*]'
  4          columns ( c1 number path '$' error on error) ) jt;

        ID VALUE                                  C1
---------- ------------------------------ ----------
       199 1027/1029/1000                       1027
       199 1027/1029/1000                       1029
       199 1027/1029/1000                       1000
       200 1027/1029/1000                       1027
       200 1027/1029/1000                       1029
       200 1027/1029/1000                       1000

6 rows selected.

i went with CTE MATERIALIZE method (after listening "Getting WITH it: Using CTEs for better performance")

with rws as (
select /*+ MATERIALIZE */ value as str FROM IIO_SYSTEM_PARAMETER t
                     WHERE    id=200
)
  select regexp_substr (
           str,
           '[^/]+',
           1,
           level
         ) dest
  from   rws
  connect by level <= 
    length ( str ) - length ( replace ( str, '/' ) ) + 1;

It is not clear to me why the CTE needs to be MATERIALIZED, can you elaborate?

If you only need the result for a single ID like 200, and if ID is primary key (so there are no duplicates in that column), you could run this query:

select  regexp_substr(value, '[^/]+', 1, level) dest
from    iio_system_parameter
start   with id = 200
connect by   level <= length(value) - length(replace(value, '/')) + 1
        and  id = 200

Note that the CONNECT BY engine (with the associated START WITH condition, if present) is processed before the WHERE clause, despite the weird choice in the SQL standard to require the WHERE clause to appear first. And before applying the WHERE condition, the CONNECT BY mechanism does exactly what Chris described (the reason you get so many rows in the output). Moving the condition id = 200 to the START WITH and CONNECT BY clauses has the effect you were trying to get with your WHERE condition.

If you only need the results for a single id, it certainly makes more sense to write a subquery (as CTE or inline view, it makes no difference) to select the row with id = 200 first (no need to materialize); and if you need to do this for more than one input row, to use a lateral subquery. The query I suggested above is more along the lines of what we needed to do before Oracle 12 - when there was no LATERAL subquery, and no JSON tools. If you need to split all the strings (for all ID) in a single query, and you don't have access to LATERAL subqueries and to JSON tools, you would write something like this:

select  id, regexp_substr(value, '[^/]+', 1, level) dest
from    iio_system_parameter
connect by   level <= length(value) - length(replace(value, '/')) + 1
        and  prior id = id
        and  prior sys_guid() is not null
;

There's no more START WITH clause anymore (you can add it back if you need to select multiple ID's but not all of them, for example). CONNECT BY is a bit more complicated; PRIOR ID = ID means that each new token will come from the same string as the preceding one (exactly what your query was NOT doing - what Chris explained), and the last condition is a hack, to prevent Oracle from finding cycles in the results. (Don't worry about that one - if you ever need to use something like that, you can read more about it at that time.)

In any case, in Oracle 12 and newer, there is no reason to use this old method. Chris already showed the right way to do it - and the JSON trick is also a good alternative (at least for splitting strings supposed to aggregate numbers).