Thanks for the question, David.
Asked: June 27, 2018 - 8:42 am UTC
Last updated: June 27, 2018 - 10:13 am UTC
Version: 12c
Viewed 1000+ times
You Asked
Hello The Oracle Masters,
First, congratulations for this site and your answers, you are so good that I try my chance today.
Is it possible to find in ONE table or ONE view or with a PL/SQL package all the programs that can be executed in my database, at any time?
One precision : I don't talk about stored procedures but only programs that are "scheduled" or "triggered" by Oracle. Why not the stored procedures? Because I have no access to the java program which are calling them.
The reason? To see what is implemented in the database, to see what the developers put inside it and forgot to tell to the DBA... to say "Oh my god, what is that, it is too bad for performance, I cancel it".
My problem is that there are too many features that I can use to trigger actions and each ones have their own view to check.
For example,
For triggers, I will check DBA_TRIGGERS.
For jobs, I will check DBA_SCHEDULER_JOBS.
For the statistics management, I will check DBA_AUTOTASK_TASK.
For the Audit, I will check DBA_STMT_AUDIT_OPTS.
etc etc
But there are some features that I don't know and I cannot check their view.
For example, I discovered the DBMS_ADVANCED_REWRITE (can be very dangerous) with its DBA_REWRITE_EQUIVALENCES view.
But there are too many unknown features to do like that.
So, it is impossible to know every view associated with these triggered programs because there are too many features, that's why I am looking for a single view or table.
A friend told me that V$ACCESS is good. Yes, this is a good beginning but we see ONLY the programs accessed NOW and not ALL the programs that can be access in the future.
OK, I can create a job, run it every second to see in a period of one month what is triggered. But some programs may be triggered only in two months or at the end of the year... so it isn't enought.
So, TOM, do you have a best option than V$ACCESS? Maybe a PL/SQL package or a table from the data dictionary?
Best regards and one time again, thank you for your great job!
David DUBOIS
and Chris said...
I'm not aware of a single view you can use to find all things that are scheduled or will fire. I'm not sure how this would even work, as some are "on action" (triggers, on-commit MV refreshes, ...), others are scheduled (jobs), and arguably some such as VPD are passive.
And some of these will only happen conditionally. e.g. you need to set the query_rewrite_integrity to trusted for DBMS_ADVANCED_REWRITE effects to take place.
And this won't capture any client-based actions, such as cron jobs. Which are still an important part of what happens in your database.
It sounds like you need to get back in control of your environment. If anyone can run anything in your database with no record of what they did you're going to run into problems. Ensure everyone records details of scripts they ran in a change repository. Ideally with links back to source control and the relevant change ticket/project details.
To figure out what you have, look in dba_objects. Find all the different object_types installed in your database. For each object you find, dig into the object-specific views to find more detail.
Others may have more suggestions on how you can tackle this.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment