Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajesh.

Asked: August 12, 2002 - 12:22 pm UTC

Last updated: December 07, 2021 - 4:57 pm UTC

Version: 8.1.7.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

We are in a process of removing unused objects(tables/functions/procedures/packages) from the database. Is there any script(suggestions) or short-cut method to find these unused objects(tables/functions/procedures/packages not used in ddl/dml/select statements for more than 3 months).

There are more than 500 objects(tables/functions/procedures/packages) in our database.

At least PLEASE help me in finding unused TABLES.For other objects I'm thinking to check manually in the application code(using find and grep commands)


Please Help me.

Thanks in advance for your reply

and Tom said...

You'll have to enable auditing and then come back in 3 months to see.

We don't track this information by default -- also, even with auditing, it may be very possible to have an object that is INDIRECTLY accessed (eg: via a foreign key for example) that won't show up.

You can try USER_DEPENDENCIES but that won't tell you about objects referenced by code in client apps or via dynamic sql

I'm always perplexed by this. How does one get into a production environment where by they don't know what the objects are used by/for? No documentation or anything?



Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.

Tom Kyte
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

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.

Rajesh, August 12, 2002 - 2:39 pm UTC


Responding to Randy's Post

Shrek, August 12, 2002 - 5:19 pm UTC

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

Tom Kyte
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

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;


Tom Kyte
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?



Tom Kyte
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

Pradikan, January 24, 2006 - 5:07 am UTC

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

Tom Kyte
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

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.

Tom Kyte
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

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?
Tom Kyte
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.
Tom Kyte
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

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:

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'));
Tom Kyte
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

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
Tom Kyte
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 ?
Tom Kyte
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?
Chris Saxon
December 07, 2021 - 4:57 pm UTC

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library