Every Oracle DBA have the responsibility to check certain things about Oracle Database every day. These things actually a daily procedure for every DBA to monitor the health of the Oracle Database. Especially, about the free space available in tablespaces, pct_free, and allocated space, etc.
Below I am giving the 5 SQL queries, which are helpful for every Oracle DBAs. Experienced DBAs might be using already but definitely helpful for new DBAs.
1. SQL Query to Verify Free Space in Tablespaces
SELECT tablespace_name, SUM (blocks) AS free_blk, TRUNC (SUM (bytes) / (1024 * 1024)) AS free_m, MAX (bytes) / (1024) AS big_chunk_k, COUNT ( * ) AS num_chunks FROM dba_free_space GROUP BY tablespace_name
Output
2. SQL Query to Check Free, pct_free, and Allocated Space within a Tablespace
SELECT tablespace_name, largest_free_chunk, nr_free_chunks, sum_alloc_blocks, sum_free_blocks, TO_CHAR (100 * sum_free_blocks / sum_alloc_blocks, '09.99') || '%' AS pct_free FROM ( SELECT tablespace_name, SUM (blocks) AS sum_alloc_blocks FROM dba_data_files GROUP BY tablespace_name), ( SELECT tablespace_name AS fs_ts_name, MAX (blocks) AS largest_free_chunk, COUNT (blocks) AS nr_free_chunks, SUM (blocks) AS sum_free_blocks FROM dba_free_space GROUP BY tablespace_name) WHERE tablespace_name = fs_ts_name
Output
3. Procedure to Analyze Tables and Indexes Quickly, Using a 5% Sample Size
BEGIN DBMS_UTILITY.analyze_schema ('&OWNER', 'ESTIMATE', NULL, 5); END; /
4. SQL Query to Find Out Any Object Reaching <threshold> Extents
SELECT e.owner, e.segment_type, e.segment_name, COUNT ( * ) AS nr_extents, s.max_extents, TO_CHAR (SUM (e.bytes) / (1024 * 1024), '999,999.90') AS MB FROM dba_extents e, dba_segments s WHERE e.segment_name = s.segment_name GROUP BY e.owner, e.segment_type, e.segment_name, s.max_extents HAVING COUNT ( * ) > :THRESHOLD OR ( (s.max_extents - COUNT ( * )) < :THRESHOLD) ORDER BY COUNT ( * ) DESC
Output
5. SQL Query to Check Space-Bound Objects
SELECT a.table_name, a.next_extent, a.tablespace_name FROM all_tables a, ( SELECT tablespace_name, MAX (bytes) AS big_chunk FROM dba_free_space GROUP BY tablespace_name) f WHERE f.tablespace_name = a.tablespace_name AND a.next_extent > f.big_chunk