Home » SQL & PL/SQL » SQL & PL/SQL » what is the difference between database & schema??
what is the difference between database & schema?? [message #226642] Mon, 26 March 2007 01:27 Go to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
1)What is the difference between database & schema?

Re: what is the difference between database & schema?? [message #226644 is a reply to message #226642] Mon, 26 March 2007 01:35 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
In oracle a database is the collection of files in wich the data is. (There is probably a much more accurate definition)

A schema is a oracle-user which owns objects (tables/indexes/procedures...) in the database.

So object and schema are quite different mathers
Re: what is the difference between database & schema?? [message #226657 is a reply to message #226642] Mon, 26 March 2007 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe you should read Concepts Manual, Chapter 1 "Introduction to the Oracle Database", section Oracle Database Architecture.

Regards
Michel
Re: what is the difference between database & schema?? [message #226660 is a reply to message #226642] Mon, 26 March 2007 02:17 Go to previous messageGo to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
In short

A Database can have many users.. in oracle each user is a schema
and each schema/user will have its own objects(tables,indexes etc)

Re: what is the difference between database & schema?? [message #226668 is a reply to message #226660] Mon, 26 March 2007 02:38 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
I believe that not every user is a schema.

Only users which owns objects are schemas.

The following is from the oracle documentation:

Collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links. A schema has the name of the user who controls it.


So...I think that when a user owns no objects it's not a schema.
Re: what is the difference between database & schema?? [message #226669 is a reply to message #226660] Mon, 26 March 2007 02:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Actualluy, what asherisfine and martijn describe is (in Oracle terminology) not a database, but an instance.
As Michel suggested: check the concepts guide.
Re: what is the difference between database & schema?? [message #226672 is a reply to message #226669] Mon, 26 March 2007 02:51 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
mmmm I will have to read the concepts also.

I think:
1) A database is a collection of (related) (data) files. (as said in the first reply)
2) An instance is a group of program's and memory structures (SGA). (no questions asked about instances)
3) A schema is all objects owned by a user. (as said in the first reply)

So I think now there are a few possibilitys:
1) I am wrong with my understandings (verry possible)
2) I worded it not clear, so my words are not understood. (also verry possible)
3) both of the above hahahaha

Anyway.... shine some ligth please
Re: what is the difference between database & schema?? [message #226673 is a reply to message #226669] Mon, 26 March 2007 02:53 Go to previous messageGo to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
Frank iam a little confused here..

An instance is just a memory with background processes and a database is an instance + control files + datafile + online redo log files CORRECT??

i am excluding the archieve log files as thats based on what mode the database is running in.

Please correct me if i am wrong actually many times this confusion between database and instance happens.
Re: what is the difference between database & schema?? [message #226678 is a reply to message #226673] Mon, 26 March 2007 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Don't waste your time posting here these kind of questions.
Just read the link I posted. It takes you half an hour to read the page and you get the correct answers of your questions and much more.

Regards
Michel


Re: what is the difference between database & schema?? [message #226685 is a reply to message #226672] Mon, 26 March 2007 03:44 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hm, Martijn, you forgot option 4:
I misread your post and got MY reply wrong..

Your assumptions are correct.
Previous Topic: how to retrive this?
Next Topic: How to calculate cumulative counters
Goto Forum:
  


Current Time: Fri Dec 09 19:43:19 CST 2016

Total time taken to generate the page: 0.13012 seconds