Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Puring Ancient Scheme Objects
melliott42_at_yahoo.com (Michael) wrote in message news:<91721cf.0408010954.50fb7961_at_posting.google.com>...
> Hello,
>
> In analyzing an 8.17 database that has been in production for serveral
> years starting from Oracle version 6, how can I determine the last
> time a table was accessed or data in it changed?
>
> I'd like to remove any unused tables etc.
>
> If you have any other suggestions on purging ancient scheme objects
> please do.
>
> Thanks,
>
> Michael
Do you have any old backups or exports? Do you have a lot of disk space?
A rough initial guess can be made if you can get a database as it was more than a year ago. Then create a link and do a select * minus select * for each table, and that gives some idea. But the idea may be incorrect if an app requires a table even if it is empty, or if there were table mods. You can do a similar thing by importing each table to another schema and doing the minus, using a lot less disk space.
You might also check which tables have a count(*) of 0. But they tend not to take up much space, so it may not be worth the bother.
If you are on unix and have source code for your app, you can grep for references to tables.
Does your application have a purging scheme that hasn't been used? Find out why, sometimes people actually do want historical data.
Most of this type of analysis just means digging around and seeing what is there.
jg
-- @home.com is bogus. linux rac ticket vendor: http://www.eweek.com/article2/0,1759,1630538,00.aspReceived on Tue Aug 03 2004 - 17:49:07 CDT