Twitter Error

Estimate size of tables in Oracle

dba_segments gives better estimate than user_segments but you may not have access to dba_segments view

SELECT segment_name, segment_type, bytes/1024/1024 MB
FROM user_segments

WHERE segment_name like 'PREFIX_%';


Following query provides number of rows and columns too

SELECT col.table_name,  col.col_cnt  AS column_count,  rc.row_cnt   AS row_count,  s.size_in_MB AS table_size_in_MB
FROM  (
  /* number of columns */  SELECT upper(table_name) table_name,    COUNT(*) col_cnt
  FROM user_tab_columns /* you can change it to dba_tab_columns */  --WHERE owner = 'OWNER' /* uncomment if you are using dba_tab_columns object*/  WHERE table_name like 'PREFIX_%'  GROUP BY upper(table_name)
  ) col
JOIN  (
  /* number of rows */  SELECT table_name,    to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '    ||table_name)),'/ROWSET/ROW/C')) AS row_cnt
  FROM user_tables  /* you can change it to dba_tables */  WHERE (iot_type != 'IOT_OVERFLOW'  OR iot_type     IS NULL)
 -- AND owner        = 'OWNER' /* uncomment if you are using dba_tables object */
  AND  table_name like 'PREFIX_%'
  ) rc
ON upper(col.table_name) = upper(rc.table_name)
JOIN  (
  /* table size in MB */  SELECT    --owner,    table_name,    (SUM(bytes)/1024/1024) size_in_MB
  FROM    (SELECT segment_name table_name,      bytes
    FROM user_segments  /* you can change it to dba_segments */    WHERE segment_type = 'TABLE'      --and owner = 'OWNER' /* uncomment if you are using dba_segments object */
    AND segment_name like 'PREFIX_%'
    )
  GROUP BY --owner,    table_name  ) s
ON upper(col.table_name) = upper(s.table_name);