Re: can you take a schema offline?

From: Shakespeare <>
Date: Sat, 14 Mar 2009 08:02:16 +0100
Message-ID: <49bb5683$0$201$>

Maxim Demenko schreef:
> 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

Actually: I was not kidding, but reacting to this line:

"Presuming no one 'screams' afterwards you could then drop the users. "

If the user had tables that were accessed, people would start screaming *after* dropping the user, not before....

I agree with your suggestions, though.

Shakespeare Received on Sat Mar 14 2009 - 02:02:16 CDT

Original text of this message