SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Oracle for the SQL Server Guy - Instances and Databases


Oracle for the SQL Server Guy - Instances and Databases

Author
Message
Filipe
Filipe
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 351
I may not be remembering this correctly, but is it correct that schemas also differ between Oracle and SQL Server as the schema is unique to the instance in Oracle whereas in SQL Server it is unique to the user database e.g. the DBO schema?


Very true. The autor is saying the same thing in the article


The objects that a user owns are collectively called schema. A schema is bound to exactly one user (in Oracle). A SQL Server database has the features of an Oracle scheme because objects are created inside the database


In other words, there is 1:1 relationship between a user and a schema in Oracle, unlike in SQL Server. Therefore, a scehema in Oracle can be equated to a database in SQL Server.


Actually both statements are false.
In Oracle the user is not defined as unique in the instance, but unique in the database, as in SQL Server. Oracle's limitation of one database per instance is another issue.
Objects in SQL are created in a schema, not in a database. It looks like they are created in a schema because there's always a default schema for each database user.
The bigger difference is that SQL Server, starting with 2005, finally fixed the issue of confusing user with schema.
richj-826679
richj-826679
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 152
Filipe Miranda (7/2/2009)

Actually both statements are false.
In Oracle the user is not defined as unique in the instance, but unique in the database, as in SQL Server. Oracle's limitation of one database per instance is another issue.
Objects in SQL are created in a schema, not in a database. It looks like they are created in a schema because there's always a default schema for each database user.
The bigger difference is that SQL Server, starting with 2005, finally fixed the issue of confusing user with schema.


An Oracle instance can be thought of as the running database in memory. To say that the uniqueness of a user is in the DB and not in the instance is perhaps misleading, as the instance does not define any database structure.

I'm not sure why one DB/instance would considered a limitation, but it should be noted that a database can have many instances ("instances per DB" instead of "DBs per instance"), as when Oracle RAC is used, so the "1:1" ratio mentioned by an earlier poster is not always correct.

I'm not sure how SS is structured, but in Oracle, every object -- including schemas -- resides wholly within a database. For most intents and purposes, an Oracle user is synonymous with an Oracle schema. "user" = "schema" The difference is in the semantics. This does not appear to be the case for SS, although yes the concept of a schema was much more profound in 2005 than in 2000, as you mention.

A great place for Oracle information is in the documentation. http://tahiti.oracle.com has all docs for recent versions (no login or account required!). I'd recommend to anyone wanting to look at Oracle from a technical standpoint to start with the Concepts manual. Pay special attention to the SGA (a memory structure in the instance). It's your best friend and worst enemy at the same time.

In 13 years (already?) of Oracleness, the term "instance" is not often used, although it's very important conceptually. The common way to refer to it is to refer to specific (memory) constructs within the instance, such as the SGA, PGA, UGA, and more specifically, the buffer cache, shared pool (how could I forget?), library cache, redo log buffer, etc.

Rich
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 2445
Filipe Miranda (7/2/2009)
I may not be remembering this correctly, but is it correct that schemas also differ between Oracle and SQL Server as the schema is unique to the instance in Oracle whereas in SQL Server it is unique to the user database e.g. the DBO schema?


Very true. The autor is saying the same thing in the article


The objects that a user owns are collectively called schema. A schema is bound to exactly one user (in Oracle). A SQL Server database has the features of an Oracle scheme because objects are created inside the database


In other words, there is 1:1 relationship between a user and a schema in Oracle, unlike in SQL Server. Therefore, a scehema in Oracle can be equated to a database in SQL Server.


Actually both statements are false.
In Oracle the user is not defined as unique in the instance, but unique in the database, as in SQL Server.


What is meant by "In Oracle the user is not defined as unique in the instance" Can there be more than one user with the same name in an Oracle instance.

Sincerely,
Maz

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

richj-826679
richj-826679
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 152
richj (7/2/2009)
I'm not sure why one DB/instance would considered a limitation, but it should be noted that a database can have many instances ("instances per DB" instead of "DBs per instance"), as when Oracle RAC is used, so the "1:1" ratio mentioned by an earlier poster is not always correct.


Whoops! The "1:1" quote originally referred to user<->schema and not database<->instance. My bad!

Rich
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 2445
richj (7/2/2009)
richj (7/2/2009)
I'm not sure why one DB/instance would considered a limitation, but it should be noted that a database can have many instances ("instances per DB" instead of "DBs per instance"), as when Oracle RAC is used, so the "1:1" ratio mentioned by an earlier poster is not always correct.


Whoops! The "1:1" quote originally referred to user<->schema and not database<->instance. My bad!

Rich


Thanks Rich for putting me out of the dilemma Smile

Maz

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 2445
ta.bu.shi.da.yu (7/2/2009)
Mazharuddin Ehsan (7/2/2009)

See some more useful comparisons in the below links:

Oracle vs. SQL Server



Be careful - some of the comparisons are not entirely accurate!


The context here is to understand the working of the two DBMS products in comparison to each other and improve our efficiency in using them; not to debate what is available in one and lacking in other.

However, it wouild be nice if you could share examples showing how 'CONNECT BY' and 'COMMENT' equivalent features can be implemented in SQL Server.

I have been an Oracle developer for long and also worked on projects more recently where databases and applications had to be migrated from Oracle to SQL Server and vice versa.

Sincerely,
Maz

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

rambilla4
rambilla4
SSC Eights!
SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)SSC Eights! (965 reputation)

Group: General Forum Members
Points: 965 Visits: 777
Hi Jagan,

This is an excellent Article for a SQL DBA to start with ORACLE concepts....

well done..

Thank You
Ram
Mike Bishop
Mike Bishop
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 171
Excellent article. Look forward to a follow up comparing locking and transactions between Oracle and SQL Server. That should inspire some lively discussion.
ta.bu.shi.da.yu
ta.bu.shi.da.yu
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 494
Mike Bishop (7/2/2009)
Excellent article. Look forward to a follow up comparing locking and transactions between Oracle and SQL Server. That should inspire some lively discussion.


Oh man... you're not asking for much! lol!

Random Technical Stuff
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Right there with Babe
Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)Right there with Babe (715 reputation)

Group: General Forum Members
Points: 715 Visits: 2445
Thanks Jagan for a very 'to the point' writeup. Special thanks to Rich for the valuable inputs.

Maz

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search