Re: can you take a schema offline?

From: Mladen Gogala <>
Date: Thu, 12 Mar 2009 21:42:35 +0000 (UTC)
Message-ID: <gpbvkb$k5g$>

On Thu, 12 Mar 2009 22:28:42 +0100, Maxim Demenko wrote:

> Shakespeare schrieb:

>> ddf schreef:
>>> On Mar 12, 3:25 pm, rgvguplb <> wrote:
>>>> i have a couple of schemas i suspect are not used any longer but am
>>>> not sure.
>>>> is there a way i can make them inaccessible before actually dropping
>>>> them?
>>>> or do i basically have no option other than drop them?
>>>> I thought i might be able to take the tablespace where its objects
>>>> are in offline, but another schema shares that tablespace, so taking
>>>> that offline does not seem to be an option.
>>>> I am using 10gR2 on windows server 2003.
>>>> thanks
>>> Have you considered locking the user accounts and expiring the
>>> passwords?  This wouldn't affect other users who may have access to
>>> those tables/views/etc but it would prevent any applications or
>>> individuals from connecting to the database with those accounts.
>>> Presuming no one 'screams' afterwards you could then drop the users.
>>> David Fitzjarrell
>> Too lazy/tired to look this up, but: can you drop users without
>> dropping their schema objects?
>> Shakespeare

> I assume, you are kidding, but back to OP question - i have impression,
> it is more about making schema objects unaccessible rather than making
> the accounts locked. In both cases, if schema objects are made
> inaccessible or accounts are locked, it may lead to loss of data if
> accounts/objects are actually in use and application can't handle this
> scenario. I would definitely consider it as organizational issue, every
> schema in production system should be properly documented. If this is a
> legacy system inherited *as is* without any documentation, one could try
> to setup auditing to find out whether accounts are used or schema
> objects are accessed and before drop schema objects take a proper
> backup.
> Best regards
> Maxim

In addition to that, checking DBA_DEPENDENCIES for any references to the objects owned by that particular user can also be helpful. Auditing is the only way to actually determnine whether the user has attempted to log in and whether its objects were accessed.

Received on Thu Mar 12 2009 - 16:42:35 CDT

Original text of this message