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
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
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.