Oracle schema vs. SQL Server database

[nmadd]

Are these two things very similar in nature? I'm a bit confused as in my studies I'm comparing a SQL Server "database" to an Oracle "database" and I'm thinking I should be comparing a SQL Server "database" to an Oracle "schema."

I ask because I'm looking at a SQL Server server that contains 23 databases but based upon my reading, that is very unfeasible with Oracle - thought it appears 23 schemas makes a lot more sense.

Am I making any sense?

[techgnome]

sort of, but not really... it's a matter of terminology... some DBMSs refer to their databases as "schemas" while others call them "databases" ... in others they are "files" .... and yet they are all basically the same thing.

You have a "server" that contains one or more "databases" or "Schemes" each of which then contain one or more "tables" which contain one or more "rows" ....

-tg

[GaryMazzone]

Schemas in Oracle are exactly the same as Schemas in SQL Server. They are a logical serperation of things in a database. You can have more then on database in an Oracle server the same as you do in SQL Server.

When you look are SQL Server you see the SQL Server Manager view of the databases. In Oracle there is nothing that corresponds to that. There is only one set of master database per instance or SQL server this is similar to the Oracle instance in Oracle. The Oracel instance is the files and process that handle the database access for you, this is the System databases in SQL Server. There is only one Instance of SQL Server running in the system (sqlserver.exe) there might be 15 databases (or only 1) running in that instance of the server. In Oracle it is the same concept. There in one instalce of the Oracle executable (ora.exe) running on the server. That server instance can access many indivdual oaracle database running in the instance of Oracle.

A schema is something that resides inside a database and is used to logically breakup parts of a database structure/functionality. Look at the DMV warehouse application for SQL Server 2005 (look in codeplex for DMVStats). There are 4 different schemas in the Database DMVStats that breaks the application into different logical parts.

[nmadd]

Am I then wrong in saying that it seems more common to have multiple SQL Server databases running on the same server? While it seems much less common to have multiple Oracle databases running on the same server?

[GaryMazzone]

I have had multilpe running on one server before with out issues (for Oracle), it depends on the system that the Oracle database will be running on. Oracle does seem to be more resoure intensive the SQL Server.

In a small shop there will probably only be one instance of Oracle running on a server with 1 or 2 databases on that instance. There will probably only be one Oracle Server also. If you go to some place that has a server farm for databases (some large Data Center) there will probably sitll be only one instance of Oracle running on the server but there will be many databases ruinning on that server. Remeber in Oracle there is a concept called Oracle_SID? The Oracle_SID is the name of a database you would want to connect to. When I did a large migration job at a shipyard in the area I actually had 1 database with 4 Schemas, a second database with the same 4 schemas and one more with 2 schemas used to perform the migration tasks.

[nmadd]

Thanks for the additional reply.

We use both SQL Server and Oracle at my work and I think I'm having trouble learning both at the same time and understanding all the terminology.

I just read some posts on the Oracle forum saying that if you know SQL Server and want to create a new database you may actually only want to create a new schema in Oracle.

I need to do some more reading.