select max(level) from tablename start with parentid = 0 connect by parentid = prior id
LEVEL For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root node, 2 for a child of a root, and so on. A root node is the highest node within an inverted tree. A child node is any non-root node. A parent node is any row that has children. A leaf node is any row without children. To define a hierarchical relationship in a query, you must use the START WITH and CONNECT BY clauses. For more information on using the LEVEL pseudocolumn, see the SELECT command.
from tablename
start with parentid = 0
connect by parentid = prior id
pseudocolumn returns 1 for a root node, 2 for a child of a root, and
so on. A root node is the highest node within an inverted tree. A
child node is any non-root node. A parent node is any row that has
children. A leaf node is any row without children. To define a hierarchical relationship in a query, you must use the
START WITH and CONNECT BY clauses. For more information on using
the LEVEL pseudocolumn, see the SELECT command.