Establishing Criteria to Retire Unused Application Schemas.
From: fmh <fmhabash_at_gmail.com>
Date: Thu, 11 Apr 2013 11:08:41 -0400
Message-ID: <CADpeV5xsQCUvKmof+xLo9dh5tKsfg2NKTsKt3cq6OM1H+tHSrA_at_mail.gmail.com>
an application schema dead.App schemas get upgraded on a routine basis and ,mostly, apps migrate to a newer one leaving behind the old schema. Such events are not communicated (and we try hard). As such, I estimate there are about 30% of our db storage wasted on such schemas.. I have to establish 2 facts about a schema to declare dead. 1) Schema last MODIFIED date >= 60 days. 2) Schema last ACCESS date >= 60 days.
Thank you.
Date: Thu, 11 Apr 2013 11:08:41 -0400
Message-ID: <CADpeV5xsQCUvKmof+xLo9dh5tKsfg2NKTsKt3cq6OM1H+tHSrA_at_mail.gmail.com>
an application schema dead.App schemas get upgraded on a routine basis and ,mostly, apps migrate to a newer one leaving behind the old schema. Such events are not communicated (and we try hard). As such, I estimate there are about 30% of our db storage wasted on such schemas.. I have to establish 2 facts about a schema to declare dead. 1) Schema last MODIFIED date >= 60 days. 2) Schema last ACCESS date >= 60 days.
I establish first one based on DBA_TAB_MODIFICATIONS and this is good
enough. The trick is in the 2nd one. If not modified, it does not mean not
accessed (by same or other schemas). My options are ...
- set auditing on: hard to sell due performance impact, I'll evaluate,
however.
- find application related table to reflect user
access. inconsistent results.
How are you doing it & what other options are there?
Thank you.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 11 2013 - 17:08:41 CEST