Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab( a INT, b VARCHAR2(100) )
CREATE TABLE tab2( d INT, e VARCHAR2(100) )
INSERT INTO tab2
SELECT 1, 'abcdefghijklmnoprstayuzndjdfg' FROM dual UNION ALL
SELECT 2, 'abcdefghijklmnoprstayuzndjdfg' FROM dual UNION ALL
SELECT 3, 'abcdefghijklmnoprstayuzndjdfg' FROM dual UNION ALL
SELECT 4, 'abcdefghijklmnoprstayuzndjdfg' FROM dual UNION ALL
SELECT 5, 'abcdefghijklmnoprstayuzndjdfg' FROM dual UNION ALL
SELECT 6, 'abcdefghijklmnoprstayuzndjdfg' FROM dual UNION ALL
SELECT 7, 'abcdefghijklmnoprstayuzndjdfg' FROM dual 
7 rows affected

SELECT 1, 'tables_job', 'TEST', 'GUEST', 
       LISTAGG('Table : '||segment_name||'- size: '|| SUM(bytes),', ') 
        WITHIN GROUP (ORDER BY segment_name) AS Detail_Text
  FROM USER_SEGMENTS
 WHERE SEGMENT_TYPE = 'TABLE'
 GROUP BY segment_name


1
'TABLES_JOB'
'TEST'
'GUEST'
DETAIL_TEXT


1
tables_job
TEST
GUEST
Table : TAB- size: 65536, Table : TAB2- size: 65536


SELECT 1, 'tables_job', 'TEST', 'GUEST', 
       RTRIM(XMLAGG(XMLELEMENT(e, 'Table : '||segment_name||'- size: '|| SUM(bytes), ',')
             .EXTRACT('//text()'))
             .GetClobVal(),
             ',') AS Detail_Text
  FROM USER_SEGMENTS
 WHERE SEGMENT_TYPE = 'TABLE'
 GROUP BY segment_name


1
'TABLES_JOB'
'TEST'
'GUEST'
DETAIL_TEXT


1
tables_job
TEST
GUEST
Table : TAB2- size: 65536,Table : TAB- size: 65536