Using SQL to check free space for a user

[kenkangxgwe]

Oracle

• Possible way 1

  SELECT
  ts.tablespace_name,
  TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE
  FROM
  user_free_space fs,
  user_tablespaces ts,
  user_users us
  WHERE
  fs.tablespace_name(+)   = ts.tablespace_name
  AND ts.tablespace_name(+) = us.default_tablespace
  GROUP BY
  ts.tablespace_name;

• Possible way 2

  SELECT table_name as Table_Name, row_cnt as Row_Count, SUM(mb) as Size_MB
  FROM
  (SELECT in_tbl.table_name,   to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from ' ||ut.table_name)),'/ROWSET/ROW/C')) AS row_cnt , mb
  FROM
  (SELECT CASE WHEN lob_tables IS NULL THEN table_name WHEN lob_tables IS NOT NULL THEN lob_tables END AS table_name , mb
  FROM (SELECT ul.table_name AS lob_tables, us.segment_name AS table_name , us.bytes/1024/1024 MB FROM user_segments us
  LEFT JOIN user_lobs ul ON us.segment_name = ul.segment_name ) ) in_tbl INNER JOIN user_tables ut ON in_tbl.table_name = ut.table_name ) GROUP BY table_name, row_cnt ORDER BY 3 DESC;

• Possible way 3

  SELECT SUM(bytes)
  FROM user_segments

MySQL

select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;

[lvergergsk]

Each CISE user has 200MB.