5 SQL Queries Every Oracle DBA Needs

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

See also:

• 5 SQL Queries Every Oracle Developer Needs