Mark C. Stock wrote:
> "Turkbear" <john.g_at_dot.spamfree.com> wrote in message
> news:7jnhe1lqsnl2q3bm5lapoancd2mv34t30m_at_4ax.com...
>
>>"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote:
>>
>>
>>><sybrandb_at_yahoo.com> wrote in message
>>>news:1122551255.818339.55450_at_g44g2000cwa.googlegroups.com...
>>>
>>>>No, one instance manages one database. Period.
>>>>You have
>>>> instance
>>>> |
>>>> |
>>>> database
>>>> |
>>>> ---------------------------------------
>>>> | |
>>>> scott schema hr schema
>>>>
>>>>That is 2 schemata in 1 database.
>>>>
>>>>
>>>>Users are connected to a *database*, NOT to a *schema*, and they can
>>>>access whatever schema, they have been allowed to access.
>>>>--
>>>>Sybrand Bakker
>>>>Senior Oracle DBA
>>>>
>>>
>>>depending on your DBMS background, 'connect to a database not a schema'
>>>might give the wrong impression. here's a bit more detail..
>>>
>>>users connect to a single schema in a single database -- ie, on connection
>>>they supply a schema/user name, password, and database service name (the
>>>schema/user credentials may be defaulted or be tied to the OS or network,
>>>and the database service name may also default)
>>>
>>>once connected, all access privileges are in the context of that original
>>>connection schema -- which likely includes access that has been granted to
>>>objects owned by other schemas, whether that access has been granted
>>>explicitly to the connect schema (grant select on emp to calvin) or via a
>>>role that the connect schema has had granted to it (including the PUBLIC
>>>role)
>>>
>>>all object references are assumed to be objects owned by the connection
>>>schema, unless a private or public synonym exists that encapsulates the
>>>objects owner (and perhaps a path to another database, ie, a database
>>>link)
>>>
>>>additionally, the ALTER SESSION SET CURENT_SCHEMA statement sets any other
>>>schema as the default context for unqualified object references -- but it
>>>does not change the session's privileges (privileges assigned to the
>>>connection schema remain in affect)
>>>
>>>there is also a confusing 'BECOME USER' system privilege, but this appears
>>>to only be used by Oracle utilities (such as EXP) and programs that access
>>>Oracle via the OCI (Oracle Call Interface)
>>>
>>>++ mcs
>>>
>>
>>The language does get circular and confusing sometimes..You connect to an
>>instance ( called a database, sometimes,
>>incorrectly. In Oracle, as has been posted by DA Morgan,a database is
>>physical files, datafiles, undo files,
>>tempfiles residing on a hard disk along with their associated control
>>files and log files.)
>>
>>The schemas are collections of objects 'owned' by USERs ( including SYS
>>and SYSTEM ) and those users can grant other users
>>rights to access, modify, remove or otherwise treat those objects as their
>>own .
>>
>>You connect to an instance as a USER ( not a schema, a schema is not a
>>person ) ...
>>
>>
>>
>>
>>
>>----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet
>>News==----
>>http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
>>Newsgroups
>>----= East and West-Coast Server Farms - Total Privacy via Encryption
>>=----
>
>
> Yeah, the language does get interesting... especially when you throw the
> term 'server' into the mix.
>
> I'll go with connecting to an instance (which has mounted and opened a
> database) using database credentials, with the instance (not database)
> identified by a TNS service name -- in other words, my use of the term
> database was a bit sloppy, but not totally inappropriate, since it is in the
> database that the user's credentials and validation method are defined.
>
> Regarding schema/user ... Oracle typically has treated these terms as
> synonomous; my usage of the terms was to show their basic equivalence in
> Oracle, yet, as you state, more specifically the connection is for a user
> and each user has a schema. To create a new schema, you must create a new
> user (note that the CREATE SCHEMA statement simply groups a set of DDL
> statements into a single transaction, it does not create a new named schema
> in the database).
>
> So more precisely, you connect as a user and each user has a schema. Once
> connected you access your connection user's schema by default, but can
> access any other user's schema (if privileges have been granted).
>
> ++ mcs
And in 10g you connect to a service ... not a server. Take a good look
at the last column in v$session in 10g.
And for those that haven't peered under the covers of 10g ... try this:
col service_name format a30
col program format a30
select service_name, program from v$session;
Welcome to a very different Oracle.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Thu Jul 28 2005 - 14:06:10 CDT
