<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Jagan Kumar
»
Oracle for the SQL Server Guy - Instances and...
21 posts, Page 2 of 3
««
1
2
3
»»
Oracle for the SQL Server Guy - Instances and Databases
Rate Topic
Display Mode
Topic Options
Author
Message
Filipe
Filipe
Posted Thursday, July 02, 2009 11:58 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 07, 2013 11:31 AM
Points: 158,
Visits: 234
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
richj-826679
richj-826679
Posted Thursday, July 02, 2009 1:21 PM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, April 03, 2013 8:43 AM
Points: 32,
Visits: 142
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
Mazharuddin Ehsan
Mazharuddin Ehsan
Posted Thursday, July 02, 2009 1:49 PM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378,
Visits: 2,378
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
richj-826679
richj-826679
Posted Thursday, July 02, 2009 1:56 PM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, April 03, 2013 8:43 AM
Points: 32,
Visits: 142
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
Mazharuddin Ehsan
Mazharuddin Ehsan
Posted Thursday, July 02, 2009 2:05 PM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378,
Visits: 2,378
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
Mazharuddin Ehsan
Mazharuddin Ehsan
Posted Thursday, July 02, 2009 2:06 PM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378,
Visits: 2,378
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
rambilla4
rambilla4
Posted Thursday, July 02, 2009 2:28 PM
Old 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
Mike Bishop
Mike Bishop
Posted Thursday, July 02, 2009 6:49 PM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:00 PM
Points: 26,
Visits: 136
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
ta.bu.shi.da.yu
ta.bu.shi.da.yu
Posted Friday, July 03, 2009 4:49 AM
SSC Veteran
Group: General Forum Members
Last Login: Sunday, July 03, 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
Mazharuddin Ehsan
Mazharuddin Ehsan
Posted Friday, July 03, 2009 9:38 AM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378,
Visits: 2,378
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 »
21 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
