Hi There,
I'm writing a custom monitoring script and one of the things that we want to monitor is the number of invalid objects in the database.
Here is the query that we use to identify the number of invalid objects:
select owner, object_type, count(*) from dba_objects where status = 'INVALID' group by owner, object_type
We would want to compare the results of the current number of invalid objects to the previous run results every time we execute the scripts. I created a table (with a sequence number) that can be used to store the run results:
CREATE TABLE mon_invalid_objects (
run_number NUMBER NOT NULL,
run_date DATE NOT NULL,
object_owner VARCHAR2(30),
object_type VARCHAR2(19),
object_count NUMBER);
Here is the insert statement used to populate the table (I have hard coded the number one for testing purposes):
INSERT INTO mon_invalid_objects (
RUN_NUMBER,
run_date,
object_owner,
object_type,
object_count)
SELECT 1,
sysdate,
owner,
object_type,
count(*)
FROM dba_objects
where status = 'INVALID'
group by owner, object_type;
Here is the part that I'm not sure about - how do we compare the results from the two queries? Obviously, we're interested in counts differences between the two queries as well as any new schemas that might be added to the list of invalid objects.
Does anyone have a plsql example or a sql example of how can this be achieved please.
We're on Oracle 11.2.0.4 ED.
Cheers