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 Sunday, June 21, 2009 2:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 16, 2009 5:41 AM
Points: 45, Visits: 37
Comments posted to this topic are about the item Oracle for the SQL Server Guy - Instances and Databases


Post #739003
Posted Thursday, July 2, 2009 12:51 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 6:47 PM
Points: 523, Visits: 557
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?
Post #746016
Posted Thursday, July 2, 2009 6:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
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



Post #746204
Posted Thursday, July 2, 2009 7:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 1:25 PM
Points: 203, Visits: 394
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.
Post #746224
Posted Thursday, July 2, 2009 7:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:30 AM
Points: 2,741, Visits: 113
Great article Jagan. Nice to see how they stack up against each other from the technical side.
Post #746259
Posted Thursday, July 2, 2009 8:02 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
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
Post #746291
Posted Thursday, July 2, 2009 8:41 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: 233, 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
Post #746329
Posted Thursday, July 2, 2009 8:55 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: 233, 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
Post #746346
Posted Thursday, July 2, 2009 8:56 AM


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
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
Post #746347
Posted Thursday, July 2, 2009 9:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 6, 2010 1:30 PM
Points: 34, 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!
Post #746385
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse