Re: can you take a schema offline?

From: Maxim Demenko <>
Date: Thu, 12 Mar 2009 22:28:42 +0100
Message-ID: <>

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 Received on Thu Mar 12 2009 - 16:28:42 CDT

Original text of this message