I'll tell you why it happens...
Randy, August 12, 2002 - 2:12 pm UTC
Project managers,directors,etc. that don't understand the little problems that arise many months later (after go-live) from having ambiguous crap in the production database won't allow us to control things the way we'd like. They just want the developers to be able to "do their job", which means the privs. to create objects w/o going through the DBA.
You are a rare specimen, Tom. I've realized that not many developers have respect for the database and will trash it on a whim.
August 12, 2002 - 2:16 pm UTC
but production? test, ok -- development -- ok, but production? that costs money (and time).
I'm against withholding things from people (not just developer vs dba) -- like "don't use feature X, we are afraid of it" but some amount of source code control is just necessary.
Thanks for your suggestions!!
Rajesh, August 12, 2002 - 2:13 pm UTC
Thank you very much for your responce. We thought the same thing to use USER_DEPENDENCIES.
You are right, we dont have access to the production machine.
It is a very big project, so many modules/layers/releases and we'll always maintain last 3 releases in production(end users can access any release).People created some new objects for new designs and some how they forgot to drop old and unused objects.
Oops! I was late!! I didn't see the other review and follow up.
Rajesh, August 12, 2002 - 2:39 pm UTC
Responding to Randy's Post
Shrek, August 12, 2002 - 5:19 pm UTC
No access on Production -- no problem
(no SELECT only also, some queries can really
Test (QA, Demo, or similar)
Limited access through roles,
or execute privileges on packages OR as needed basis
I think the developers need Connect, Resource, definitely PLUSTRACE, and some other privileges/roles on an as needed basis. Because they Can't do squat without it, and they need a destructive test env. Therefore, run in ARCHIVELOG mode, and give developers full freedom
On a separate note:
Tom: I hope you reconsider the Yahoo/HotMail questions. Many people (myself included) are nomads (contractors) who want to use a Yahoo or a HotMail account.
August 12, 2002 - 6:48 pm UTC
I haven't disabled them -- It is really teed me off this morning when over HALF of the questions came bouncing right back at me, and they are always from yahoo.com, hotmail.com, msn.com -- never from <real company here>.com
Third Party tools
Gary Myers, August 13, 2002 - 12:33 am UTC
I've used SQL*Impact for this sort of thing, as it can pick references to SQL from batch code, forms etc.
You'd probably need some idea of where to start looking. While SQL*Impact does have reports for 'unused' columns and other anomalies, nothing will help if you've got a form using a table when no-one ever uses the form.
>I'm always perplexed by this.
>How does one get into a production environment whereby
>they don't know what the objects are used by/for?
Easy. I've experienced the following
Make your documentation 'development' focused (ie 'create a procedure to mail clients latest offer' as opposed to 'Procedure MAIL_CLIENT is used during the annual STOCKTAKE reconciliation process...'). All too common. In one case, the documentation was discarded after the development as it had served its purpose.
Make it impossible for developers to keep documentation up to date ("You can't change it. It's already been signed off.") Especially applies to updates by support staff, often with the excuse that 'they only fix the code so the design won't be changed'
Don't have any procedures for dropping objects and programs, etc out of the application environment. Or just make it so hard that no-one bothers.
Don't differentiate between code written for a one-off task (eg data conversion) and regular, repeated, processing.
Script to find procedures/functions not being called
Mobra, January 12, 2006 - 3:29 am UTC
I've made this quick script to find code in a package that's not being used anywhere (in the same package or other packages).
[Note: Of course any public routine might be called from a client, but we have a naming convention that allows us to filter out these from the results.]
The script works by first finding and extracting all the function/procedure names within a single package, then looking through all the user's code (user_source) to see if this function/procedure is being called.
This code works, but it's quite slow.
The question is: Is there a way to optimize the second query ? I've tried with a first_rows hint, but it did not appear to make much difference.
l_proc_used boolean := false;
cursor l_proc_cursor is
select trim(replace(replace(lower(text), 'procedure ', ''), 'function ', '')) proc_name
where (lower(text) like '%procedure %'
or lower(text) like '%function %')
and name = 'MY_PACKAGE_PKG'
and type in ('PACKAGE BODY');
cursor l_src_cursor (p_proc_name in varchar2) is
select /*+ first_rows(1) */ 'x'
where lower(text) like '%' || p_proc_name || '%'
and lower(text) not like '%end ' || p_proc_name || '%'
and lower(text) not like '%procedure ' || p_proc_name || '%'
and lower(text) not like '%function ' || p_proc_name || '%';
function strip_params (p_proc in varchar2) return varchar2
l_returnvalue:=trim(copy_str(p_proc, 1, instr(p_proc, '(') - 1));
for l_proc_rec in l_proc_cursor loop
open l_src_cursor (l_proc_name);
if l_proc_used = false then
dbms_output.put_line('Procedure/function ' || l_proc_name || ' is NOT in use...');
January 12, 2006 - 11:00 am UTC
what is the point? so I have a procedure not called by anyother procedure - so? does that mean anything? It does NOT mean it is not in use, it just means someone is calling it from the client directly.
The point is...
Mobra, January 14, 2006 - 4:25 am UTC
... to be able to find "dead code".
Like I mentioned in the first post, we have a
naming convention that allows us to see (or filter out) functions and procedures that are called directly from the client.
Which means that any remaining functions/procedures in packages (including subroutines private to a package) that are not being called, are not being used any more ("dead code") and can/should be removed.
The script supplied finds such code, the question was: Is there a way to optimize the second query (second cursor) so that the script runs faster?
January 15, 2006 - 3:23 pm UTC
I don't know.... I've never tried. It seems that if you have to search for dead code, well, you only need to do that once (so what if it goes slow once) since you'll obviously put into place the necessary documentation framework in the future to avoid the issue :)
Guess if I had to do it, I would put the names of the procedures/functions into a collection or temporary table and then select out everything that wasn't in the rest (instead of running a query per function/procedure, run a single query that returns likely ones)
but again, I just plain wouldn't do it - dead code? if you have a serious problem with dead code over and over and over and over and over again, you need to fix the cause - not the effect. Your approach is fairly hit or miss as it is (procedure names embedded in other procedure names - you look across ALL packages instead of just one)...
But, if I had to do this for whatever reason, I'd go for the "query" route:
ops$tkyte@ORA10GR2> create or replace package foo
3 procedure p1;
4 procedure p2;
ops$tkyte@ORA10GR2> create or replace package body foo
4 procedure p1
10 procedure p2
16 procedure p3
Package body created.
ops$tkyte@ORA10GR2> with subroutines
4 select name, substr( text, 1, instr( text, '(')-1 ) proc_name
5 from (
6 select name,
7 trim( replace( replace(
8 lower( translate(text,chr(13)||chr(10),' ')),
9 'procedure ', '' ),
10 'function ', '' ) ) ||
11 '(' text
12 from user_source
13 where (lower(text) like '%procedure %'
15 lower(text) like '%function %')
16 and type = 'PACKAGE BODY'
22 select name, lower(text) text
23 from user_source
24 where type in ( 'PACKAGE BODY', 'PACKAGE' )
26 select s.*
27 from subroutines S left outer join packages P
28 on ( p.text like '%' || s.proc_name || '%'
29 and p.text NOT like '%end ' || s.proc_name || '%'
30 and p.text NOT like '%procedure ' || s.proc_name || '%'
31 and p.text NOT like '%function ' || s.proc_name || '%'
33 where p.name is null
Pradikan, January 24, 2006 - 5:07 am UTC
We are having a lot of procedures / packages (approx - 5000), which are used for various applications. The point is that we want to eliminate procedures / packages which were unused for a long time. Is there any tool which identified these procedures / packages, which we can delete from the database.
Also wanted to know about any automated tool which identifies junk code (code which is of no use to the actual requirement)in these procedures..
Please let me know
Thanks in advance
January 24, 2006 - 8:12 am UTC
I usually call that "documentation".
You have 5,000 procedures - and no idea what they are/do/who uses them/etc??
Sounds like time to stop, take a breath, document system to me.
Can we declare an existing proc 'INVALID'
Alex, September 08, 2009 - 11:12 am UTC
Documentation does not solve the problem completely. We did create some of our 1300 procs for different releases of different apps (we have 8 apps). The releases have gone out and have been superceeded by the new code by now. But you cannot realistically track what is used and what is not. You can answer the question 'is this proc definitely used' for any proc by spending 30 minutes of your time but you cannot do this 'in bulk' for all the procs.
I can say that a proc is not used 'with 95% confidence'. But before dropping it I would like to cover the rest 5%. And the only reliable way to find out is to ask oracle in some way if the proc was called or not over specified period of time...
I was thinking of something like 'ALTER procname UNCOMPILE'. That would make perfectly valid procs INVALID. Unfortunately there is no such option. If I were to do this for all the procs which I suspect are not used, I will be able to come back in a months time and see which ones have been called and are valid now.
September 08, 2009 - 9:35 pm UTC
... Documentation does not solve the problem completely. ...
Documentation solves the problem completely.
.. you cannot realistically track what is used and what is not. ...
why not? Much of the rest of the world can - it is called documentation. If you have a large system - you can and will do this. We do - we do for parameters, for functions for features. And trust me, the Oracle database is probably a little larger than your code. It not only can be done, it MUST be done.
... I can say that a proc is not used 'with 95% confidence'. But before dropping it
I would like to cover the rest 5% ...
I will say again you cannot do that. For you see, you will wait six months - then since no one used it for six months, you will drop it. However, at six months plus one day - I WILL USE THE FUNCTION/FEATURE OF YOUR APPLICATION THAT INVOKES THIS FUNCTION. Now what. You cannot just sit back and watch for things that are not "run" and then drop them. That is beyond naive.
Tell you what, since your developers keep track of nothing, ignore the code. just ignore it. Why worry about getting rid of it? Just IGNORE THE EXISTENCE OF IT, pretend it is all used and just live with it. What goal do you have by dropping it? what would you gain (NOTHING)? what would you lose (probably code that is actually necessary).
Just ignore it.
I won't comment on this any more. Just ignore it if you won't take the time to actually inventory what is used where, for which versions and why/when it is used.
If you can be sloppy in one place, you can do so anywhere - so just ignore it, it is not hurting you anymore than you already hurt.
Monitoring dead objects
John Lim, September 09, 2009 - 10:55 pm UTC
We have the same problem that some people have created 3000 tables over a period of 10+ years that no one is sure is in use any more.
At first, we were planning to use turn on audit and track it to clean it up, then we came up with the idea of using Oracle 10g's AWR and using the log of sql's like this to find out all table accesses:
dbms_lob.substr(t.sql_text, 4000 ) sqltxt
join dba_hist_sqlstat s on p.sql_id = s.sql_id
join dba_hist_sqltext t on t.sql_id=p.sql_id
where p.operation='TABLE ACCESS' /* HOW ABOUT MAT VIEWS -- not sure -- check */
We intend to log AWR for 3+ months and then do the purge of all tables not in the AWR listing. We are in the first month of logging.
Is this a good idea, or did we miss something?
September 14, 2009 - 10:30 am UTC
please re-read this page.
or just read the comment right above.
I feel I was rather unambiguous on this topic, completely and utterly and totally UNAMBIGUOUS.
but your use of the dba_hist tables - not even a tiny bit reliable in real life. They are populated with samples and will never every have a complete and total history.
I don't get it, just IGNORE THEM if you don't know them. It is bad that you know nothing about them, but they are not hurting your right now, dropping them could only leave you exactly where you are (no smarter) or in a world of hurt 3 months from now when someone says "hey, where did this go"
David Aldridge, September 11, 2009 - 5:33 am UTC
I think that if documenting code makes people sad then they ought to be in their bedroom writing card games in VB. The sad thing is that it doesn't have to be a huge overhead, it just has to be well thoughtout and #actually done#.
I think that it helps people's perspective to see how things are done at the high reliability end of the software engineering spectrum, and as Oracle developers we're in a great position for this as our primary programming language, PL/SQL, is derived from ADA.
SPARK is an ADA-based language that has been used to deliver extremely reliable software, with less than one error for every 10,000 lines of delivered code ( http://www.spectrum.ieee.org/computing/software/the-exterminators
So, start by having a look at this link: http://en.wikipedia.org/wiki/SPARK_(programming_language
) and see what sort of techniques you might learn. For example, the code is written with extensive metadata at the declaration level that tells automated verification tools exactly what is going on inside the code. So how about when you write a procedure you include a comment in it such as:
|| :META: selects employee
|| :META: inserts employee
|| :META: updates department
|| :META: updates address
|| :META: calls is_number
|| :META: calls log_entry
|| :META: calls dbms_application_info
|| :META: changes global_counter
Try doing that for SQL statements also, and include a unique name while you're at it ...
|| :META: name update_employee_job_title
|| :META: updates employee
|| :META: selects employee
|| :META: selects department
|| :META: uses regexp
People look at these issues and think "But it'll take forever!" and mope around like sullen teenagers prevaricating over picking up clothes from their bedroom floor, but you know what? It takes maybe a minute, maybe 30 seconds per statement.
Anyway, when you commit yourself to putting that in code it's probably pretty easy to tell if someone hasn't done it, and this will be checked this in the code reviews that someone who doesn't like you (ideally) will be doing.
Not fool proof.
Not a 100% solution to the "what code does what" problem.
But if it has value then give it a go, and in a year's time maybe someone will be saying "Wow, that null job title bug was sure easy to track down. Thanks goodness Jim is such a professional!". Fame and glory beckon.
Don't get it
John Lim, September 15, 2009 - 10:55 am UTC
> I don't get it, just IGNORE THEM if you don't know them.
I would agree with you and we tried to tell the customer this several times. But the customer thinks differently... These tables were not created by us, but they want it "fixed" during a migration to new database server that we have been employed to carry out, and we are trying to solve this issue without too much overhead.
We were afraid that table audit would take eat up too much disk space. As this seems to be the way to go, we will turn on table audit as you suggested. Thank you Tom.
September 15, 2009 - 11:01 am UTC
tell customer you have analyzed the situation and every table is necessary.
auditing isn't going to answer the question, this is going to be a disaster.
David Aldridge, September 18, 2009 - 2:19 am UTC
removing unused tables:
Jeemon, May 23, 2010 - 10:32 pm UTC
I think the views dba_tab_modifications/all_tab_modifications views
could help in this.
select * from all_tables a1
where not exists(select * from all_tab_modifications a2 where a1.table_name = a2.table_name) or
exists(select * from all_tab_modifications a2 where a1.table_name = a2.table_name and a2.timestamp < to_date('yourdate'));
May 24, 2010 - 1:08 pm UTC
why - they report
a) only for modifications
b) only when the modifications exceed some threshold
c) are emptied when we feel like emptying them
that is, a single row inserted every now and then - wouldn't show up. Or it might show up and then disappear because statistics were gathered.
I'll still say "this is a really dumb idea".
Unused Objects -- V$DB_OBJECT_CACHE
Amit, August 27, 2013 - 9:49 am UTC
Thanks for your points, based on that I'm able to convince my client to leave dead object removal activity.
Just curious to know whether v$DB_OBJECT_CACHE will serve the purpose to find object not being used for a long time, may be if we collect the OBJECT information from this view, 3-5 times daily for for 6-12 months, then can we say that we have all the objects which have been used and rest are unused ones.
August 28, 2013 - 7:06 pm UTC
that would be as flaky and unreliable as every other approach sort of actually - well - you know - documenting your system.
Don't drop unknown unused objects
A reader, September 02, 2013 - 5:27 am UTC
Dropping objects after finding them unused for a few months is a recipe for disaster. What about the operations only done at end of year - they weren't used in your sample, so now you can't roll over the year? What about the mid-year activities, if you caught the end of year in your window. What about when you remove a product line, or add one - perhaps once every couple of years? What about the components added for legal compliance "You shall be able to report on X" that won't be working when the lawyers call ? Or the pieces used only when a tax rate changes ?
September 04, 2013 - 6:34 pm UTC
as they say on reddit, you get an upvote
Oracle 12C and Beyond feature
AJ, December 06, 2021 - 9:26 pm UTC
Can we leverage HEAT_MAP feature available in 12C and above version to identify unused objects?
December 07, 2021 - 4:57 pm UTC