0

I am generally a Sql Server coder, but we have a client who wants to move a system from Sql to ORACLE due to the new licensing model of Sql Server.

I know historically, ORACLE has no logical grouping of objects within a db/schema, along the lines of a Sql Server schema. It's been a while since I've done any real ORACLE work though, so I'm just wondering if somewhere along the line, they may have added such a construct?

The version of ORACLE we are porting the Sql Server database into is ORACLE 11g (11.2).

Traditionally, I've seen oracle developers do this using just a prefix on table/view/object names. So for example a Sql Server object users.OPTIONS might become USR_OPTIONS in ORACLE. This works to be sure, but it just feels really kludgey to me, as it's not so much an actual hierarchy, but is sort of "forcing" one in by simply using contorted names.

eidylon
  • 7,068
  • 20
  • 75
  • 118
  • "_ORACLE has no logical grouping of objects within a schema_" -- I believe this statement is incorrect. However, in Oracle a schema is tied to a user, so you'd need to create a user to have a schema with that name. – mustaccio Jun 23 '14 at 16:30
  • The structure you are referring to I believe corresponds to Sql Server's "database" item. But with in a database in Sql Server you can make a lower-level grouping of your objects... so you can have all order related tables in one group, user related tables in another, etc. Essentially another level of grouping BELOW ORACLE's idea of a "schema". – eidylon Jun 23 '14 at 16:32
  • No. Oracle only has schemas. This changed with 12c where you essentially have databases very similar to SQL Server (but only in the enterprise edition) –  Jun 23 '14 at 16:37
  • @a_horse_with_no_name, you can add yours at an answer... and I'll accept it eventually (unless something better comes up). I keep hoping they'll add this fine-grained organization eventually... guess I'm still waiting..... – eidylon Jun 23 '14 at 18:06

1 Answers1

0

Oracle has schema support in Oracle 11gR2. Oracle schemas are tied to a user. You'll have to (somewhat confusingly) create a user for each schema that you're creating. This isn't a big deal, but some people find it distasteful.

Oracle 12c Enterprise Edition has a feature called Multitenant that allows for multiple databases on the same Oracle server in much the same way that SQL Server allows out of the box.

  • Yeah, I did one system using ORACLE's schemas to kind of equate to Sql Servers... but it becomes a disaster when you need to do code pushes, because now instead of doing a database comparison on one target, you have to compare a half-dozen or more targets (depending on the complexity of your system). I was hoping for something that would give the nice organizational structure while keeping everything in one master container that you can easily compare or copy over. Guess for now still just stuck with "forcing" organization with name prefixes. – eidylon Jun 24 '14 at 16:22