Useful selects(queries)

Displaying results from a sub-query in a single row
 

------------------ Variant 1------------------
CREATE OR REPLACE TYPE ntt_varchar2 AS TABLE OF VARCHAR2(4000); 

CREATE OR REPLACE FUNCTION to_string ( nt_in IN ntt_varchar2, 

                                                                             delimiter_in IN VARCHAR2 DEFAULT ',' ) 

RETURN VARCHAR2 IS
v_idx PLS_INTEGER;
v_str VARCHAR2(32767);
v_dlm VARCHAR2(10);
BEGIN
    v_idx := nt_in.FIRST;
     WHILE v_idx IS NOT NULL
           LOOP
                v_str := v_str || v_dlm || nt_in(v_idx);
                v_dlm := delimiter_in;
                v_idx := nt_in.NEXT(v_idx);
            END LOOP; 

      RETURN v_str;
END to_string;

And a quick example:
SQL> select deptno
                         , cast(collect(ename) as ntt_varchar2) as vals
               from emp
               group by deptno
DEPTNO VALS -----------------------------------------------------
10 NTT_VARCHAR2('CLARK','KING','MILLER')
20 NTT_VARCHAR2('SMITH','JONES','SCOTT','ADAMS')
30 NTT_VARCHAR2('ALLEN','WARD','MARTIN','BLAKE') NTT_VARCHAR2('JAMES')

SQL>select deptno
                       ,to_string(cast(collect(ename) as ntt_varchar2)) as vals
               from emp group by deptno;
DEPTNO VALS -------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES JAMES

-------Variant 2 with DBMS_UTILITY-------
DECLARE
plist VARCHAR2(50) := 'A,B,C,D,E,F,G,H,I,J';
ptablen BINARY_INTEGER;
ptab DBMS_UTILITY.uncl_array;
BEGIN
DBMS_UTILITY.comma_to_table(
list => plist,
tablen => ptablen,
tab => ptab);
FOR i IN 1 .. ptablen
LOOP
INSERT INTO TEMPTABLE VALUES (ptab(i));
END LOOP;
END;

Display DDL of a Table Using DBMS_METADATA
to Create New one with the Same Constraints

SQL>select DBMS_METADATA.GET_DDL('TABLE', 'REGIONS','HR')
              from dual;

How do I find the high water mark (for sessions)

SELECT sessions_highwater 

FROM v$license;