Click here to monitor SSC
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
Jagan Kumar
Jagan Kumar
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 37
Comments posted to this topic are about the item Oracle for the SQL Server Guy - Instances and Databases



David in .AU
David in .AU
Mr or Mrs. 500
Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)Mr or Mrs. 500 (540 reputation)

Group: General Forum Members
Points: 540 Visits: 561
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?
Rudy Panigas
Rudy Panigas
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 1306
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



Andrew Peterson
Andrew Peterson
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 714
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.
Jon Monahan
Jon Monahan
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3557 Visits: 160
Great article Jagan. Nice to see how they stack up against each other from the technical side.
Mohammad Mazharuddin Ehsan
Mohammad Mazharuddin Ehsan
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 2443
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:

Oracle vs. SQL Server

Migrating from Oracle to SQL Server


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

ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 494
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.

Random Technical Stuff
ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 494
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!

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.


Random Technical Stuff
richj-826679
richj-826679
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 152
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
don_goodman
don_goodman
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 82
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!
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