For anyone who's interested, here is the query I put together which uses
the AUD$. Does some rollups on the week, looks back 12 months and only
shows the most recent 99 lines
SELECT *
FROM ( SELECT (SELECT v.host_name
FROM v$instance v)
Host_Name,
(SELECT v.instance_name
FROM v$instance v)
DB_Name,
x_Weeks_ago,
COUNT (OS_USERNAME) occurances,
OS_USERNAME,
USERHOST,
Weekly_Rollup_Date,
ACTION_NAME,
USERNAME
FROM (SELECT ROUND (ROUND (SYSDATE - TRUNC (NEXT_DAY
(A.TIMESTAMP, 'Sun') - 7), 0) / 7, 0) x_Weeks_ago,
A.OS_USERNAME,
A.USERHOST,
TRUNC (NEXT_DAY (A.TIMESTAMP, 'Sun') - 7)
Weekly_Rollup_Date,
CASE A.ACTION_NAME WHEN 'LOGOFF' THEN
'LOGON/LOGOFF' WHEN 'LOGON' THEN 'LOGON/LOGOFF' ELSE a.action_name END
ACTION_NAME,
A.USERNAME
FROM DBA_AUDIT_TRAIL A
WHERE TRUNC (A.EXTENDED_TIMESTAMP) > TRUNC (SYSDATE)
- 365
AND action_name NOT IN ('LOGOFF BY CLEANUP',
'LOGOFF BY CLEANUP')
AND username NOT IN ('DBSNMP',
'SYS',
'SYSTEM',
'OEMGC',
'SYSMAN')) sexy_internal
GROUP BY x_Weeks_ago,
OS_USERNAME,
USERHOST,
Weekly_Rollup_Date,
ACTION_NAME,
USERNAME
ORDER BY Weekly_Rollup_Date DESC) sexier_external
WHERE ROWNUM < 100;
On 14 April 2016 at 05:46, Oracle DBA <justanotheroracledba@xxxxxxxxx>
wrote:
Thanks everyone,
I won't do individual replies but some good info.,
Mladen, I had thought of your idea and I like it very much, but upper
management will take that as a cowboy tactic and in the event of actual
downtime if its still being used then they will get stoppy, its already
hard enough to promote IT as having good service, but that will give the
end users more ammo against IT. But yes switch off and wait for the screams.
Iggy is also correct there are a few edge cases that for legal reasons
(contracts) some are required for 7 years after project close, but I'm sure
there are more and it will be a slog to once identify a unused database to
then locate the correct people who know about the contract. Can always
backup and restore if they need it down the track.
Ian, DBA_AUDIT_TRAIL is great, but does miss 40% of our estate. Work is
now in progress to turn on DB Auditing on those remaining also our 9i
databases appear to be empty AUD$ table
Niall already using logons_Current but some only have a single App Server
user connected so while its a good indicator of how many connections are
connected, it does miss a bit, but combined with the redo generation does
give us some candidates.
Raza, a lot of the database accounts cannot be mapped back to their
Windows accounts so thats a bit tricky, I suppose I could start digging
into the application tables and hope to find internal app logins and email
addresses and then start emailing the users for clarification.
Documentation is being kept for new install, but our Company has gone
through a number of acquisitions and has also acquired a number of other
companies over the last 10 years and through redundancies etc MOST of that
local knowledge is now gone. Documentation is wonderful given you have
enough time to do it.
On 14 April 2016 at 05:00, Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
wrote:
Some companies (e.g. Big Pharma) keep some databases around for
compliance reasons.
Iggy
------------------------------
From: mark.powell2@xxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: How to determine if a database is in use?
Date: Wed, 13 Apr 2016 19:12:28 +0000
I think it might be wiser to leave the database in restricted mode for a
while before removing it permanently. Some databases are only kept around
for historic use and as such may only be accessed infrequently. I would
want to get through a month-end / quarter-end period as part of my removal
process. It depends on how well you were able to monitor the database to
get a feel for usage.
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Mladen Gogala
*Sent:* Wednesday, April 13, 2016 1:52 PM
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: How to determine if a database is in use?
On 04/12/2016 08:33 PM, Oracle DBA wrote:
Question – How to determine if a database is in use.
Well, here is a simple way:
1. Ask around.
2. Take a full offline backup. If someone objects, the database is in
use.
3. If nobody complains by time the backup is finished, start it up
using the following command: "startup mount restrict" and when the
database
comes up, execute "drop database" command. That will provide immediate
savings in space and machine resources.
4. If nobody complains, the database is not used.
5. If somebody does complain, you have a full offline backup and can
bring it back.
--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com
