InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 22:16:54 GMT20http://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspx Hi, Looks like it is very useful for beginners in Oracle DB. Fundamentals are explained very clearly . Congrats Jagan chetta !! Thanks, JithinThu, 16 Jul 2009 12:57:33 GMTmohanjithinhttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspxThanks Jagan for a very 'to the point' writeup. Special thanks to Rich for the valuable inputs.MazFri, 03 Jul 2009 09:38:23 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspx[quote][b]Mike Bishop (7/2/2009)[/b][hr]Excellent article. Look forward to a follow up comparing locking and transactions between Oracle and SQL Server. That should inspire some lively discussion.[/quote]Oh man... you're not asking for much! lol!Fri, 03 Jul 2009 04:49:30 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspxExcellent article. Look forward to a follow up comparing locking and transactions between Oracle and SQL Server. That should inspire some lively discussion.Thu, 02 Jul 2009 18:49:58 GMTMike Bishophttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspxHi Jagan,This is an excellent Article for a SQL DBA to start with ORACLE concepts....well done..Thank You RamThu, 02 Jul 2009 14:28:58 GMTrambilla4http://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspx[quote][b]ta.bu.shi.da.yu (7/2/2009)[/b][hr][quote][b]Mazharuddin Ehsan (7/2/2009)[/b][hr]See some more useful comparisons in the below links:[url=http://www.psoug.org/reference/sqlserver.html]Oracle vs. SQL Server[/url][/quote]Be careful - some of the comparisons are not entirely accurate![/quote]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,MazThu, 02 Jul 2009 14:06:05 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspx[quote][b]richj (7/2/2009)[/b][hr][quote][b]richj (7/2/2009)[/b][hr]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.[/quote]Whoops! The "1:1" quote originally referred to user<->schema and not database<->instance. My bad!Rich[/quote]Thanks Rich for putting me out of the dilemma :)MazThu, 02 Jul 2009 14:05:57 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspx[quote][b]richj (7/2/2009)[/b][hr]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.[/quote]Whoops! The "1:1" quote originally referred to user<->schema and not database<->instance. My bad!RichThu, 02 Jul 2009 13:56:26 GMTrichj-826679http://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspx[quote][b]Filipe Miranda (7/2/2009)[/b][hr][quote]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 articleThe 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 databaseIn 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.[/quote]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. [/quote]What is meant by "[b]In Oracle the user is not defined as unique in the instance[/b]" Can there be more than one user with the same name in an Oracle instance.Sincerely,MazThu, 02 Jul 2009 13:49:54 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspx[quote][b]Filipe Miranda (7/2/2009)[/b][hr]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.[/quote]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 [i]any[/i] 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[strike], so the "1:1" ratio mentioned by an earlier poster is not always correct.[/strike]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. [url]http://tahiti.oracle.com[/url] 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.RichThu, 02 Jul 2009 13:21:42 GMTrichj-826679http://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspx[quote]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 articleThe 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 databaseIn 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.[/quote]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.Thu, 02 Jul 2009 11:58:26 GMTFilipehttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspxWell 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!Thu, 02 Jul 2009 09:32:31 GMTdon_goodmanhttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspxNice 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 log[i]s[/i] -- [i]plural[/i]. 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 [i]everything[/i] until it is. There is no truncating of the log -- [i]ever![/i]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!RichThu, 02 Jul 2009 08:56:08 GMTrichj-826679http://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspx[quote][b]Mazharuddin Ehsan (7/2/2009)[/b][hr]See some more useful comparisons in the below links:[url=http://www.psoug.org/reference/sqlserver.html]Oracle vs. SQL Server[/url][/quote]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:[quote]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.[/quote]Thu, 02 Jul 2009 08:55:56 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspxIn 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.Thu, 02 Jul 2009 08:41:20 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspx[quote][b]David B (7/2/2009)[/b][hr]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?[/quote]Very true. The autor is saying the same thing in the article[quote]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[/quote]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:[url=http://www.psoug.org/reference/sqlserver.html]Oracle vs. SQL Server[/url][url=http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm]Migrating from Oracle to SQL Server[/url]Sincerely,MazThu, 02 Jul 2009 08:02:44 GMTMazharuddin Ehsanhttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspxGreat article Jagan. Nice to see how they stack up against each other from the technical side.Thu, 02 Jul 2009 07:31:58 GMTJon Monahanhttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspxNice 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.Thu, 02 Jul 2009 07:07:00 GMTAndrew Peterson-472853http://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspxHello,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!RudyThu, 02 Jul 2009 06:38:56 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspxI 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?Thu, 02 Jul 2009 00:51:27 GMTDavid in .AUhttp://www.sqlservercentral.com/Forums/Topic739003-1572-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/SQL+Server/67007/">Oracle for the SQL Server Guy - Instances and Databases</A>[/B]Sun, 21 Jun 2009 02:59:59 GMTJagan Kumar