💾 Storage & Objects
Tablespace usage, invalid objects, segment sizes and recompiles.
| Snippet | |
|---|---|
| ☆Tablespace Usage — How full each tablespace is |
SELECT tablespace_name, ROUND(used_percent, 1) AS pct_used FROM dba_tablespace_usage_metrics ORDER BY used_percent DESC;
|
| ☆Invalid Objects — Objects needing recompile |
SELECT owner, object_name, object_type FROM dba_objects WHERE status = 'INVALID';
|
| ☆Recompile Object — Recompile one invalid object |
ALTER PROCEDURE proc_name COMPILE;
|
| ☆Recompile Schema — Recompile everything in a schema |
EXEC DBMS_UTILITY.COMPILE_SCHEMA('HR');
|
| ☆Largest Segments — Biggest tables and indexes in a schema |
SELECT segment_name, segment_type, ROUND(bytes/1048576) AS mb FROM dba_segments WHERE owner = 'HR' ORDER BY bytes DESC FETCH FIRST 20 ROWS ONLY;
|
| ☆Object Counts — Inventory of objects per type |
SELECT object_type, COUNT(*) FROM dba_objects WHERE owner = 'HR' GROUP BY object_type ORDER BY 2 DESC;
|