CONNECT BY was originally intended (and still is used) for traversing a hierarchy. For example, if your employee table is:

SQL> desc scott.emp
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

Then an EMPNO might also be a MGR (manage) who is themselves an employee and so forth.

So query-wise, you want to be able to:

- start with an employee
- find their manager
- "loop back" and lookup that manager as an *employee*
- find *their* manager

and so forth. We use CONNECT BY for that

SQL> select empno, ename, mgr
  2  from   scott.emp
  3  start with ename = 'SMITH'
  4  connect by empno = prior mgr;

     EMPNO ENAME             MGR
---------- ---------- ----------
      7369 SMITH            7902
      7902 FORD             7566
      7566 JONES            7839
      7839 KING

4 rows selected.

So Smith reports to Ford, who reports to Jones, who reports to King.

Someone came up with the simple idea that if we use a CONNECT BY that always "succeeds", we can generate an infinitely large data set, eg

SQL> select rownum from dual
  2  connect by 1=1;           -- always true

    ROWNUM
----------
         1
         2
         3
         4
         5
 ...
 ...

The "connect by level" is just a variation on that them.

select level from dual connect by level <= n;

will return rows with values 1 .. n