Oracle SQLPlus queries for Nuclos operations: determine tablespace, schema and table sizes and gather schema statistics.

On this page

SELECT df.tablespace_name "Tablespace", totalusedspace "Used MB",
       (df.totalspace - tu.totalusedspace) "Free MB", df.totalspace "Total MB",
       ROUND(100 * ((df.totalspace - tu.totalusedspace) / df.totalspace)) "Pct. Free"
FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) totalspace
      FROM dba_data_files GROUP BY tablespace_name) df,
     (SELECT ROUND(SUM(bytes) / 1048576) totalusedspace, tablespace_name
      FROM dba_segments GROUP BY tablespace_name) tu
WHERE df.tablespace_name = tu.tablespace_name;

SELECT ROUND(SUM(bytes)/1024/1024) AS size_in_mb, owner
FROM dba_segments GROUP BY owner ORDER BY size_in_mb;

BEGIN
  dbms_stats.gather_schema_stats('<schema>');
END;
/

SELECT owner, table_name, ROUND((num_rows * avg_row_len)/(1024*1024)) MB
FROM all_tables WHERE owner = '<schema>' AND num_rows > 0
ORDER BY MB ASC;