Multiple Schemas vs. Multiple Instances

Multiple Schemas vs. Multiple Instances

Post by Fuzz » Thu, 28 Jan 1999 04:00:00




>Since posting my original message, I discovered the undocumented feature of
>ALTER SESSION which allows default schema prefixing:

>ALTER SESSION SET CURRENT_SCHEMA=<whatever>

>This feature is unsupported, but supposedly in heavy use by imp/exp.  Whether
>that makes me feel any better about banking on it, I'm not sure just yet.

Does anyone know if Oracle has plans to support this?  It could make
my life SO much easier, it's not funny.

Ciao
Fuzzy
:-)

 
 
 

Multiple Schemas vs. Multiple Instances

Post by Thomas Ky » Fri, 29 Jan 1999 04:00:00



(if that email address didn't require changing)



>>Since posting my original message, I discovered the undocumented feature of
>>ALTER SESSION which allows default schema prefixing:

>>ALTER SESSION SET CURRENT_SCHEMA=<whatever>

>>This feature is unsupported, but supposedly in heavy use by imp/exp.  Whether
>>that makes me feel any better about banking on it, I'm not sure just yet.

>Does anyone know if Oracle has plans to support this?  It could make
>my life SO much easier, it's not funny.

>Ciao
>Fuzzy
>:-)

I entered enhancement request 808932 right now for this...  So, its under
consideration -- call support and tell them you want it.

Thomas Kyte

Oracle Service Industries
Reston, VA   USA

--
http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

 
 
 

Multiple Schemas vs. Multiple Instances

Post by Mike Burde » Fri, 29 Jan 1999 04:00:00


This seems good (and yes, I will contact Oracle support) but we need a bit more.

Why can't default settings like this be attached to a userid, or better still, can
you force a script to run when an ID logs on so these can be set manually?
The problem is I have no wish to embed this code in my app. I shouldn't need to.
This also applies to other session parameters like setting sql trace on. I know one
can set the trace on for an active session but sometimes the session is in and out
before you get a chance. Other times you have a pool of sessions and your not sure
which one the app will use. Also it may not be your code to allow the alter session
to be embedded.

A PL/SQL script that executes at logon time would be great and would solve these
problems and others without changing the alter user command.

Of course if we can do this now or soon then, please, someone enlighten me.



> (if that email address didn't require changing)


> >>Since posting my original message, I discovered the undocumented feature of
> >>ALTER SESSION which allows default schema prefixing:

> >>ALTER SESSION SET CURRENT_SCHEMA=<whatever>

> >>This feature is unsupported, but supposedly in heavy use by imp/exp.  Whether
> >>that makes me feel any better about banking on it, I'm not sure just yet.

> >Does anyone know if Oracle has plans to support this?  It could make
> >my life SO much easier, it's not funny.

> >Ciao
> >Fuzzy
> >:-)

> I entered enhancement request 808932 right now for this...  So, its under
> consideration -- call support and tell them you want it.

> Thomas Kyte

> Oracle Service Industries
> Reston, VA   USA

> --
> http://govt.us.oracle.com/    -- downloadable utilities

> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation

> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you.  Any bounced
> email will be treated the same way i treat SPAM-- I delete it.

 
 
 

Multiple Schemas vs. Multiple Instances

Post by Thomas Ky » Fri, 29 Jan 1999 04:00:00



(if that email address didn't require changing)


>This seems good (and yes, I will contact Oracle support) but we need a bit more.

>Why can't default settings like this be attached to a userid, or better still, can
>you force a script to run when an ID logs on so these can be set manually?

in 8i, yes you can have a 'script' run when a user logs in.  In 8i, you can have
triggers on database events such as:

login
logout
database startup
database shutdown
server errors

as well as when a

create
alter
drop

command is executed.

>The problem is I have no wish to embed this code in my app. I shouldn't need to.
>This also applies to other session parameters like setting sql trace on. I know one
>can set the trace on for an active session but sometimes the session is in and out
>before you get a chance. Other times you have a pool of sessions and your not sure
>which one the app will use. Also it may not be your code to allow the alter session
>to be embedded.

>A PL/SQL script that executes at logon time would be great and would solve these
>problems and others without changing the alter user command.

>Of course if we can do this now or soon then, please, someone enlighten me.



>> (if that email address didn't require changing)


>> >>Since posting my original message, I discovered the undocumented feature of
>> >>ALTER SESSION which allows default schema prefixing:

>> >>ALTER SESSION SET CURRENT_SCHEMA=<whatever>

>> >>This feature is unsupported, but supposedly in heavy use by imp/exp.  Whether
>> >>that makes me feel any better about banking on it, I'm not sure just yet.

>> >Does anyone know if Oracle has plans to support this?  It could make
>> >my life SO much easier, it's not funny.

>> >Ciao
>> >Fuzzy
>> >:-)

>> I entered enhancement request 808932 right now for this...  So, its under
>> consideration -- call support and tell them you want it.

>> Thomas Kyte

>> Oracle Service Industries
>> Reston, VA   USA

>> --
>> http://govt.us.oracle.com/    -- downloadable utilities

>> ----------------------------------------------------------------------------
>> Opinions are mine and do not necessarily reflect those of Oracle Corporation

>> Anti-Anti Spam Msg: if you want an answer emailed to you,
>> you have to make it easy to get email to you.  Any bounced
>> email will be treated the same way i treat SPAM-- I delete it.

Thomas Kyte

Oracle Service Industries
Reston, VA   USA

--
http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

 
 
 

Multiple Schemas vs. Multiple Instances

Post by Fuzz » Sat, 30 Jan 1999 04:00:00





>(if that email address didn't require changing)


>>>Since posting my original message, I discovered the undocumented feature of
>>>ALTER SESSION which allows default schema prefixing:

>>>ALTER SESSION SET CURRENT_SCHEMA=<whatever>

>>>This feature is unsupported, but supposedly in heavy use by imp/exp.  Whether
>>>that makes me feel any better about banking on it, I'm not sure just yet.

>>Does anyone know if Oracle has plans to support this?  It could make
>>my life SO much easier, it's not funny.

>>Ciao
>>Fuzzy
>>:-)

>I entered enhancement request 808932 right now for this...  So, its under
>consideration -- call support and tell them you want it.

>Thomas Kyte

>Oracle Service Industries
>Reston, VA   USA

Thomas,

Thank you very much!  I'll call right now.

Ciao
Fuzzy
:-)

 
 
 

1. Advice needed: Multiple instances vs Multiple users in 1 instance

Hello all,

we are planning to migrate several modestly sized
db-applications (eg. 250 MB of data, 5-15 concurrently users)
from Access/DB2 to Oracle.
Since the workload for those databases is not so high we would
like to put 3 of the databases on one server machine (HPUX 11).
Regarding the performance/memoryconsumption etc. of the Oracle
server processes which would be better:
  - running one instance for each db thus having 3 instances in total
or
 - running only 1 instance and let have each application its own
schema/user.

Thanks for any comments

Dirk

2. Cross-post: problem with cursors and join statement in SQL7 SP2

3. Multiple Instances Vs Multiple Servers

4. runtime version

5. Single-Instance Cluster vs Multiple-Instance Cluster

6. Loading shared library with ORA_FFI on HPUX

7. Multiple schemas versus multiple databases

8. Oracle Release

9. Multiple Databases/Multiple Instances

10. Multiple Instances or Multiple Servers

11. multiple users or multiple oracle instance of the same database

12. Multiple instances vs. users

13. Single vs Multiple instances on a server