Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Oracle for the SQL Server Guy - Instances and Databases Expand / Collapse
Author
Message
Posted Thursday, July 2, 2009 11:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:10 AM
Points: 176, Visits: 294
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.
Post #746524
Posted Thursday, July 2, 2009 1:21 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 2:10 PM
Points: 35, Visits: 151
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
Post #746585
Posted Thursday, July 2, 2009 1:49 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:03 PM
Points: 378, Visits: 2,403
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
Post #746602
Posted Thursday, July 2, 2009 1:56 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 2:10 PM
Points: 35, Visits: 151
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
Post #746607
Posted Thursday, July 2, 2009 2:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:03 PM
Points: 378, Visits: 2,403
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 :)

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
Post #746617
Posted Thursday, July 2, 2009 2:06 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:03 PM
Points: 378, Visits: 2,403
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
Post #746618
Posted Thursday, July 2, 2009 2:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 17, 2010 11:02 AM
Points: 347, Visits: 777
Hi Jagan,

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

well done..

Thank You
Ram
Post #746631
Posted Thursday, July 2, 2009 6:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:50 AM
Points: 26, Visits: 149
Excellent article. Look forward to a follow up comparing locking and transactions between Oracle and SQL Server. That should inspire some lively discussion.
Post #746715
Posted Friday, July 3, 2009 4:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 258, 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
Post #746916
Posted Friday, July 3, 2009 9:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:03 PM
Points: 378, Visits: 2,403
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
Post #747039
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse