I'll tell you why it happens...
August 12, 2002 - 2pm Central time zone
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 - 2pm Central time zone:
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 - 2pm Central time zone
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 - 2pm Central time zone
Reviewer: Rajesh
Responding to Randy's Post
August 12, 2002 - 5pm Central time zone
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 - 6pm Central time zone:
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
August 13, 2002 - 12am Central time zone
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 - 3am Central time zone
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 - 11am Central time zone:
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 - 4am Central time zone
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 - 3pm Central time zone:
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 - 5am Central time zone
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 - 8am Central time zone:
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 8, 2009 - 11am Central time zone
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 8, 2009 - 9pm Central time zone:
... 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
September 9, 2009 - 10pm Central time zone
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 - 10am Central time zone:
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 - 5am Central time zone
Reviewer: David Aldridge from Friday
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 - 10am Central time zone
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 - 11am Central time zone:
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 - 2am Central time zone
Reviewer: David Aldridge from Friday
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.
|