Home » SQL » 8 Recommended Weekly Procedures for Oracle Database Health Check

8 Recommended Weekly Procedures for Oracle Database Health Check

By

Every organization running Oracle Database as a backend software, used to run daily, nightly, and weekly procedures/batches to monitor Oracle Database health. And this is the responsibility of every Oracle DBA working in that organization. Here I am sharing 8 SQL queries which are recommended to be set as a weekly procedure for Oracle database health check to make sure Oracle Database is working properly.

1. SQL Query to Find Tables That Don't Match the Tablespace Default for NEXT Extent

The implicit rule here is that every table in a given tablespace should use the exact same value for NEXT, which should also be the tablespace's default value for NEXT. Note: Change the schema name with your schema to check.

  SELECT segment_name,
         segment_type,
         ds.next_extent AS Actual_Next,
         dt.tablespace_name,
         dt.next_extent AS Default_Next
    FROM dba_tablespaces dt, dba_segments ds
   WHERE     dt.tablespace_name = ds.tablespace_name
         AND dt.next_extent != ds.next_extent
         AND ds.owner = 'HR'
ORDER BY tablespace_name, segment_type, segment_name;

2. SQL Query to Check Existing Extents

This tells us how many of each object's extents differ in size from the tablespace's default size. If this report shows a lot of differently sized extents, your free space is likely to become fragmented. If so, this tablespace is a candidate for reorganizing.

  SELECT segment_name,
         segment_type,
         COUNT ( * ) AS nr_exts,
         SUM (DECODE (dx.bytes, dt.next_extent, 0, 1)) AS nr_illsized_exts,
         dt.tablespace_name,
         dt.next_extent AS dflt_ext_size
    FROM dba_tablespaces dt, dba_extents dx
   WHERE dt.tablespace_name = dx.tablespace_name AND dx.owner = 'HR' 
GROUP BY segment_name,
         segment_type,
         dt.tablespace_name,
         dt.next_extent;

 Output

Existing extents output Oracle

3. SQL Query to Find Tables Without PK Constraint

SELECT table_name
  FROM all_tables
 WHERE owner = 'HR'
MINUS
SELECT table_name
  FROM all_constraints
 WHERE owner = 'HR' AND constraint_type = 'P';

4. SQL Query to Find Out Which Primary Keys are Disabled

SELECT owner,
       constraint_name,
       table_name,
       status
  FROM all_constraints
 WHERE owner = 'HR' AND status = 'DISABLED' AND constraint_type = 'P';

5. SQL to Find Tables with Non-unique PK Indexes

SELECT c.constraint_name, i.tablespace_name, i.uniqueness
  FROM all_constraints c, all_indexes i
 WHERE     c.owner = UPPER ('HR')
       AND i.uniqueness = 'NONUNIQUE'
       AND c.constraint_type = 'P'
       AND i.index_name = c.constraint_name;

6. SQL Query to Generate Alter Index Statement to Rebuild indexes to Have Correct Storage Parameters

SELECT 'alter index ' || index_name || ' rebuild ',
       'tablespace INDEXES storage '
       || ' ( initial 256 K next 256 K pctincrease 0 ) ; '
  FROM all_indexes
 WHERE (tablespace_name != 'INDEXES' OR next_extent != (256 * 1024))
       AND owner = 'HR';

Output

SQL output in Toad for Oracle

7. SQL Query to Check Datatype Consistency Between Two Environments

SELECT table_name,
       column_name,
       data_type,
       data_length,
       data_precision,
       data_scale,
       nullable
  FROM all_tab_columns              -- first environment
 WHERE owner = 'HR'
MINUS
SELECT table_name,
       column_name,
       data_type,
       data_length,
       data_precision,
       data_scale,
       nullable
  FROM [email protected]my_db_link  -- second environment
 WHERE owner = 'HR'
ORDER BY table_name, column_name;

8. SQL Query to Find Out Any Difference in Objects Between Two Instances

SELECT object_name, object_type FROM user_objects
MINUS
SELECT object_name, object_type FROM [email protected]my_db_link;

NOTE: Please don't forget to change the values highlighted in bold with your Oracle database values in SQL queries.

See also: