I'll tell you why it happens... August 12, 2002 - 2:12 pm UTC Reviewer: Randy from Texas 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.



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



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!! August 12, 2002 - 2:13 pm UTC Reviewer: Rajesh Hi Tom,



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.



Thanks again!!





Rajesh





Oops! I was late!! I didn't see the other review and follow up. August 12, 2002 - 2:39 pm UTC Reviewer: Rajesh



Responding to Randy's Post August 12, 2002 - 5:19 pm UTC Reviewer: Shrek from MD IMHO:

No access on Production -- no problem

(no SELECT only also, some queries can really

affect performance)

Test (QA, Demo, or similar)

Limited access through roles,

or execute privileges on packages OR as needed basis

Development:

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.



Thanks,



Shrek



Followup 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







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

Third Party tools August 13, 2002 - 12:33 am UTC Reviewer: Gary Myers from Australia 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 January 12, 2006 - 3:29 am UTC Reviewer: Mobra from Norway Hi Tom,



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.









declare



l_proc_used boolean := false;

l_proc_name varchar2(255);



cursor l_proc_cursor is

select trim(replace(replace(lower(text), 'procedure ', ''), 'function ', '')) proc_name

from user_source

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'

from user_source

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 || '%';



l_src_rec l_src_cursor%rowtype;



function strip_params (p_proc in varchar2) return varchar2

as

l_returnvalue varchar2(255);

begin



l_returnvalue:=trim(copy_str(p_proc, 1, instr(p_proc, '(') - 1));



return l_returnvalue;



end strip_params;



begin



for l_proc_rec in l_proc_cursor loop



l_proc_name:=strip_params (l_proc_rec.proc_name);

l_proc_used:=false;



open l_src_cursor (l_proc_name);



fetch l_src_cursor

into l_src_rec;



l_proc_used:=l_src_cursor%found;



close l_src_cursor;



if l_proc_used = false then

dbms_output.put_line('Procedure/function ' || l_proc_name || ' is NOT in use...');

end if;



end loop;



end;





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



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... January 14, 2006 - 4:25 am UTC Reviewer: Mobra from Norway ... 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?







Followup 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 2 as 3 procedure p1; 4 procedure p2; 5 end; 6 / Package created. ops$tkyte@ORA10GR2> ops$tkyte@ORA10GR2> create or replace package body foo 2 as 3 4 procedure p1 5 is 6 begin 7 p2; 8 end; 9 10 procedure p2 11 is 12 begin 13 p1; 14 end; 15 16 procedure p3 17 is 18 begin 19 null; 20 end; 21 22 end; 23 / Package body created. ops$tkyte@ORA10GR2> with subroutines 2 as 3 ( 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 %' 14 or 15 lower(text) like '%function %') 16 and type = 'PACKAGE BODY' 17 ) 18 ), 19 packages 20 as 21 ( 22 select name, lower(text) text 23 from user_source 24 where type in ( 'PACKAGE BODY', 'PACKAGE' ) 25 ) 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 || '%' 32 ) 33 where p.name is null 34 / NAME PROC_NAME ------------------------------ ---------- FOO p3



Unused Procedures January 24, 2006 - 5:07 am UTC Reviewer: Pradikan from INDIA Hi Tom,



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



Pradikan



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



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' September 08, 2009 - 11:12 am UTC Reviewer: Alex from London, UK Tom,



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.



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



... 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 itI would like to cover the rest 5% ...I will say againYou 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 September 09, 2009 - 10:55 pm UTC Reviewer: John Lim from Malaysia Hi,



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:



select

timestamp,

p.object_owner owner,

p.object_name name,

p.operation opn,

p.options options,

(EXECUTIONS_TOTAL) recent_execs,

dbms_lob.substr(t.sql_text, 4000 ) sqltxt

from

dba_hist_sql_plan p

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 */

order by

2,3,6,1



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?

Followup 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"

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"

September 11, 2009 - 5:33 am UTC Reviewer: David Aldridge from Friday



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 (



So, start by having a look at this link:



/*

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

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 September 15, 2009 - 10:55 am UTC Reviewer: John Lim from Malaysia > 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.

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

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.

September 18, 2009 - 2:19 am UTC Reviewer: David Aldridge from Friday



Docs here:



Very nice, but an enhancement that includes database object usage would be very dear to me.



not that it solves the problem entirely of course.

Just to drift slightly further off topic, I just read about PL/Scope in 11g, that tracks identifier usage.Docs here: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10471/adfns_plscope.htm Very nice, but an enhancement that includes database object usage would be very dear to me.not that it solves the problem entirely of course.

removing unused tables: May 23, 2010 - 10:32 pm UTC Reviewer: Jeemon from Singapore 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'));

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

why - they reporta) only for modificationsb) only when the modifications exceed some thresholdc) are emptied when we feel like emptying themthat 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 August 27, 2013 - 9:49 am UTC Reviewer: Amit from UK Hello Tom,



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.



Best Regards

Amit Shukla

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

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 September 02, 2013 - 5:27 am UTC Reviewer: A reader 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 ?

Followup September 04, 2013 - 6:34 pm UTC

as they say on reddit, you get an upvote

as they say on reddit, you get an upvote