can you take a schema offline?

[rgvguplb]

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

[ddf]

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

[Shakespeare]

ddf schreef:

Too lazy/tired to look this up, but: can you drop users without dropping
their schema objects?

Shakespeare

[Maxim Demenko]

Shakespeare schrieb:

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

[rgvguplb]

On Mar 12, 1:54 pm, ddf <orat...@msn.com> wrote:
> 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.
>

I did consider that, but then the reason i wanted to make the schema
inaccessible was to see if anyone would complain after i did so. We use
(d) this schema as part of an application we did, so everyone else
reads/writes from/to its objects. Then the application was modified
and a second schema was created and all the tables and objects were
(presumably) transferred to the new schema. So the theory is, i should
be able to drop the old schema and nobody should scream at me.

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

This is what i'm hoping to avoid.

If there's no way to do this, and if there is no such creature as an
"alter schema x offline" statement, then i'll just try dropping the
user.

thank you.

[Mladen Gogala]

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.

--
http://mgogala.freehostia.com

[Michael Austin]

Having problems learning to be a DBA??

http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/auditing.htm

[johnb...@sbcglobal.net]

Have you tried this on a test system first?

[rgvguplb]

On Mar 12, 4:04 pm, johnbhur...@sbcglobal.net wrote:

> Have you tried this on a test system first?

No, i haven't. I wanted to know if there was another way to do this
other than dropping the user.

Auditing and checking dba_dependencies is something i had not
considered, so thank you for the advice.

you're a great bunch.

[Ed Prochak]

On Mar 12, 5:39 pm, rgvguplb <rgvgu...@gmail.com> wrote:

After you do a back up, right?

[Palooka]

They don't document, audit nor follow any kind of change control procedure.

So why should they bother backing up either?

Palooka

[rgvguplb]

On Mar 12, 7:57 pm, Palooka <nob...@nowhere.com> wrote:
> They don't document, audit nor follow any kind of change control procedure.
>
> So why should they bother backing up either?
>

You document???? Well good on you.

Sure, i may not be as good at this as you might be, but then that's
why i asked. If everyone on this group were an expert, wouldn't this
be a pretty quiet group?

[Palooka]

The sad thing is that most of the time it doesn't need expertise. Just
common sense and discipline.

Palooka

[rgvguplb]

On Mar 12, 9:55 pm, Palooka <nob...@nowhere.com> wrote:
> The sad thing is that most of the time it doesn't need expertise. Just
> common sense and discipline.
>
> Palooka

you are right, of course.

if i have wasted your time then i apologize.

thanks to all for the advice.

[R. Schierbeek]

"rgvguplb" <rgvg...@gmail.com> wrote in news:40c0413c-db79-433a-bf1d-

Hello rgvguplb(?)

A sure way to check usage is to rename all the schema's tables.
If you get complaining users - rename them back. And - if
there are any- recompile packages which access these tables.

Cheers

Roelof Schierbeek, DBA

[Shakespeare]

Maxim Demenko schreef:

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

[Mladen Gogala]

On Fri, 13 Mar 2009 04:55:34 +0000, Palooka wrote:

> The sad thing is that most of the time it doesn't need expertise. Just
> common sense and discipline.
>
> Palooka

Most of the time, common sense is not needed and is rarely found.
Frequently, the articles on this group could be used to get somebody
committed. Pun is intended.

--
http://mgogala.freehostia.com

[jgar the jorrible]

Quite the contrary, I assure you.

jg
--
@home.com is bogus.
http://groups.google.com/group/comp.databases.oracle.server/browse_frm/month/2000-01