Home » SQL & PL/SQL » SQL & PL/SQL » Diff Between Schema and user
Diff Between Schema and user [message #356643] Fri, 31 October 2008 13:04 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


Hi,

What is the difference between schema and user.

what is the sql used to create schema and user?

Can i have multiple users to one schema.

Is user and schema is same if not how it is different?


As I am a newbie. i am trying to create these things.

Thanks in advance
Re: Diff Between Schema and user [message #356649 is a reply to message #356643] Fri, 31 October 2008 13:42 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Search the Forum; it's been asked (and answered) quite a few times.
Re: Diff Between Schema and user [message #356745 is a reply to message #356643] Sat, 01 November 2008 23:27 Go to previous messageGo to next message
fairgame
Messages: 29
Registered: October 2008
Junior Member
What is the difference between schema and user?

User and Schema are same and refers to collection of Ojects like,
Tables, Procedures, views, DB Links & etc..


what is the sql used to create schema and user?

Create user <Schema/user_name> identified by <password>
Grant create session to <Schema/User_name>


Can i have multiple users to one schema.
Yes.. Give Grants..Grant < privilege > on <object> to <Schema/User>
<Schema.Object>


Is user and schema is same if not how it is different?
Same..
Re: Diff Between Schema and user [message #356750 is a reply to message #356745] Sun, 02 November 2008 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@fairgame

Reread your post, it contains inconsistencies.

I think you should also search for the other topics on the subject and read Database Concepts.

Regards
Michel

[Updated on: Sun, 02 November 2008 01:22]

Report message to a moderator

Re: Diff Between Schema and user [message #356825 is a reply to message #356750] Sun, 02 November 2008 11:51 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
To claify for the OP

Quote:
1) in Oracle, schema and user mean the same thing from a practical angle.

2) in database theory they are different, but again, in practice, oracle does not make the distinction. This means user=schema and schema=user in everyday use.

3) there are other relational databases that do not agree with oracle and infact have implemented schemas apart from users. Consult these different RDBMS systems for their interpretation of schema vs. user.


Technically (I did not read the post provided by Michel sorry) (and might I add, there are multiple meanings for schema not just this one I am providing), schema would be any named set of rowsources (table,view,synonym), and associated objects/privileges/etc. This could in theory include objects from many different users. Think of a schema as a subject area implementation rather than a set of tables owned by specific users. Indeed, in a distributed heterogenious environment, a schema could include many objects from different users on different database systems, even non-relational databases, so long as they were made to behave relationaly.

Oracle decided to simplify things and just not implement schemas in their database design (at least I don't see any CREATE SCHEMA statement do you?).

Oh... wait... there is in fact such a statement:

CREATE SCHEMA

ah... the documentation. Surely it will clear things up for us...

Quote:
Note:

This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user (see CREATE USER ). This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.


yes... that is much better now, thanks oracle documentation.

Kevin

[Updated on: Sun, 02 November 2008 11:54]

Report message to a moderator

Previous Topic: Finding Dates of a Given Month in Single Query...?
Next Topic: Variable
Goto Forum:
  


Current Time: Sat Dec 03 09:48:20 CST 2016

Total time taken to generate the page: 0.05060 seconds