/*
    Oracle database data dictionary script.
    Returns: schema, table, column position, column name, data type, length, row count, and allocated data storage.
    Notes: Access to DBA_SEGMENTS may require additional privileges. Please remove field data_space and join dba_segments if not available.
*/

SELECT
     t.owner,
     t.table_name,
     c.column_id,
     c.column_name,
     c.data_type,
     CASE
     WHEN c.data_type IN ('VARCHAR2','CHAR','NCHAR','NVARCHAR2')
     THEN c.char_length ELSE c.data_length END AS data_length,
     c.data_precision,
     c.data_scale,
     c.nullable,
     t.num_rows,
     ROUND(NVL(s.bytes,0) / 1024) AS data_space

FROM all_tables t

JOIN all_tab_columns c
     ON t.owner = c.owner
     AND t.table_name = c.table_name

JOIN dba_segments s
     ON t.owner = s.owner
     AND t.table_name = s.segment_name
     AND s.segment_type IN ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')

--WHERE t.owner IN ('APPLICATION_SCHEMA','OTHER_SCHEMA') -- include/exclude schema

ORDER BY
     t.owner,
     t.table_name,
     c.column_id;