June 21, 2009 at 3:00 am
Comments posted to this topic are about the item Oracle for the SQL Server Guy - Instances and Databases
July 2, 2009 at 12:51 am
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?
July 2, 2009 at 6:38 am
Hello,
Really enjoyed the article and it's perfect timing as I'm currently a SQL server DBA and need to learn Oracle 10g as of yesterday. We have just install an Oracle environment and I'll be working withe the Oracle DBAs so it's important for us to cross train.
I hope you will continue to write more articles on this topic.
Thanks and keep up the good work!
Rudy
Rudy
July 2, 2009 at 7:07 am
Nice intro. I've been thinking about getting more up to speed on Oracle.
...I believe SQLsvr made the change to schema to be more ANSI compliant.
The more you are prepared, the less you need it.
July 2, 2009 at 7:31 am
Great article Jagan. Nice to see how they stack up against each other from the technical side.
July 2, 2009 at 8:02 am
David B (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.
See some more useful comparisons in the below links:
Migrating from Oracle to SQL Server
Sincerely,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
July 2, 2009 at 8:41 am
In Oracle, you can assign users to separate temporary tablespaces. Is this possible in SQL Server?
Also a short note on the Undo tablespace. The reason why the undo tablespace is seen to be equivalent to the Tempdb Database AND the Transaction Log is because the undo tablespace is used for multi-version consistency, which Oracle has had for ever and which Microsoft only implemented in SQL Server 2005 with snapshot isolation.
However, Microsoft uses TempDB to maintain version stores to allow this isolation level. Which really leads me back to my original question on whether you can assign users to different temporary spaces (not sure what you'd call it in SQL Server) so that you can seperate the version store processing from needing to use TempDB for users when they need to do things like sorts on large and complex queries.
July 2, 2009 at 8:55 am
Mazharuddin Ehsan (7/2/2009)
See some more useful comparisons in the below links:
Be careful - some of the comparisons are not entirely accurate!
For instance, it says that Oracle has compressed indexes, but SQL Server 2008 does not. That's not true - SQL Server 2008 does row and page compression for both tables and indexes. Suprisingly, it also says there is no equivalent for the CONNECT BY statement, which isn't true because a recursive CTE does the same thing... and is probably more compliant to ANSI SQL than the Oracle equivalent. And there is a COMMENT equivalent in SQL Server, I know because my firm needs to use it. I might be missing something here because it's under the trigger section.
I'd also be interested in hearing what people have to say about the following comment:
SQL Server has a totally different internal structure than Oracle has. In SQL Server, a table is basically a big linked-list and the data blocks are essentially the leaf-blocks of the cluster index. Those blocks are then doubly-linked back and forth so you can traverse the table in a full table scan or in an index range scan. In fact, an index range scan of the whole table is essentially (physically) the same as a full table scan. I'm not sure exactly why, but SQL Server has always had trouble with corruption of these link-list pointers. Run DBCC to check (and fix) problems with these pointers.
July 2, 2009 at 8:56 am
Nice article! Having gone the other direction (knowing Oracle and needing to support SS), something like this would've been helpful to me a few years ago. But I'd like to clarify a point or two.
"Having a database is not necessary to run an instance." While technically true, the only practical reason I can think of to have an instance w/o a DB is when creating the DB. When creating an Oracle DB, the instance must first be started (in "nomount" mode). The next step is generally the "CREATE DATABASE" statement.
Redo logs -- plural. The required minimum is 2 and it's common to see 5, 10, or more of them in a DB. When one fills to it's DBA-defined maximum, the next one takes over. In NOARCHIVELOG mode (SS's "SIMPLE" recovery), the logs switch seamlessly. In ARCHIVELOG mode (SS's "FULL" recovery), the full log must be archived off (backed up) before Oracle will use it again. If it's not and all redo logs fill, the DB pauses everything until it is. There is no truncating of the log -- ever!
Great stuff! I'd like to see more of this, especially at the object level. The interleaved nature of index storage in SS (Oracle's indexes have storage independent of their tables) still gets me...
Thanks!
Rich
July 2, 2009 at 9:32 am
Well written, well thought-out and explained. I especially enjoyed the association between filegroups and tablespaces vis-a-vis schemas and tablespaces.
Very enlightening. Well done!
July 2, 2009 at 11:58 am
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.
July 2, 2009 at 1:21 pm
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
July 2, 2009 at 1:49 pm
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
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
July 2, 2009 at 1:56 pm
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 userschema and not databaseinstance. My bad!
Rich
July 2, 2009 at 2:05 pm
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 userschema and not databaseinstance. My bad!
Rich
Thanks Rich for putting me out of the dilemma 🙂
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply