I've been doing a few database health checks recently, and I've found Toads DB Health Check to be rather helpful. However, there are a couple of missing (in my opinion) useful checks that might be able to be added at some future point. These are:
create global temporary table check_constraints on commit preserve rowsas ( -- SEARCH_CONITION is a LONG data type, hence the need for a table. select owner, table_name, constraint_name, to_lob(search_condition) as search_condition from dba_constraints where owner not in (select username from dba_user.daily_stats_exclusions) and table_name not like 'BIN$%' and constraint_type = 'C') ;
delete from check_constraints where upper(search_condition) not like '% IN %NULL%';commit;
select count(*) from check_constraints;select * from check_constraints;
truncate table check_constraints;drop table check_constraints;
I obviously have a good look at what's left in the table before I truncate and drop it - to see if any rows remaining are of the above listed and very broken construct.
These are some great ideas. I entered into our internal system as TOR-2766 for review by our Product Management.
Perhaps these should go into the Idea Pond or I'd love to hear others comment if these would be helpful to them.
I've added this to the Idea Pond at https://community.toadworld.com/products/toad-for-oracle/i/other/additional_checks_in_database_health_checker.
VOTE FOR ME!