2

We are planning a new system for a client in ORACLE 11g. I've been mostly in the Sql Server world for several years, and am not really current on the latest ORACLE updates.

One particular feature I'm wondering if ORACLE has added in by this point is some sort of logical "container" for database objects, akin to Sql Server's SCHEMA.

Trying to use ORACLE's schemas like Sql Server winds up being a disaster for code comparisons when trying to push from dev > test > live.

Packages are sort of similar, except that you can't put tables into a package (so they really only work for logical code grouping).

The only other option I am aware of is the archaic practice of having to prefix object names with a "schema" prefix, i.e. RPT_REPORTS, RPT_PARAMETERS, RPT_LOGS, RPT_USERS, RPT_RUN_REPORT(), with the prefix RPT_ denoting that these are all the objects dealing with our reporting engine say. Writing a system like this feels like we never left the 8.3 file-naming age.

Is there by this point in time any cleaner, more direct way of logically grouping related objects together in ORACLE?

eidylon
  • 7,068
  • 20
  • 75
  • 118

1 Answers1

6

Oracle's logical container for database objects IS the schema. I don't know how much "cleaner" and "more direct" you can get! You are going to have to do a paradigm shift here. Don't try to think in SQL Server terms, and force a solution that looks like SQL Server on Oracle. Get familiar with what Oracle does and approach your problems from that perspective. There should be no problem pushing from dev to test to production in Oracle if you know what you're doing.

It seems you have a bit of a chip on your shoulder about Oracle when you use terms like "archaic practice". I would suggest you make friends with Oracle's very rich and powerful feature set by doing some reading, since you're apparently already committed to Oracle for this project. In particular, pick up a copy of "Effective Oracle By Design" by Tom Kyte. Once you've read that, have a look at "Expert Oracle Database Architecture" by the same author for a more in-depth look at how Oracle works. You owe it to your customer to know how to use the tool you've been handed. Who knows? You might even start to like it. Think of it as another tool in your toolchest. You're not married to SQL Server and you're not being unfaithful by using Oracle ;-)

EDIT:

In response to questions by OP:

I'm not sure why that is a logistical problem. They can be thought of as separate databases, but physically they are not. And no, you do not need a separate data file for each schema. A single datafile is often used for all schemas.

If you want a "nice, self-contained database" ala SQL Server, just create one schema to store all your objects. End of problem. You can create other users/schemas, just don't give them the ability to create objects.

There are tools to compare objects and data, as in the PL/SQL Developer compare. Typically in Oracle you want to compare schemas, not entire databases. I'm not sure why it is you want to have multiple schemas each with their own objects anyway. What does is buy you to do that? Keep your objects (tables, triggers, code, views, etc.) in one schema.

DCookie
  • 42,630
  • 11
  • 83
  • 92
  • 1
    In addition, code comparison across differently named schemas will not be a problem if you use synonyms to access objects in other schemas, rather than hard-coding the schema name in procedural code. – Allan Nov 07 '11 at 18:58
  • 1
    Exactly. It's all about knowing what you're doing. – DCookie Nov 07 '11 at 19:17
  • The logistical problem with ORACLE's schemas though is that they are essentially separate databases, no? So if you have 5 schemas, you need to maintain 5 sets of data files, need to do 5 different code comparisons, it wasn't one nice self-contained database. So the complexity of an ORACLE project grows quickly if you want to try to use multiple schemas. Unless I'm missing something, which is possible. Are there database comparison tools for ORACLE which compare a set of schemas all in one go? The one I use (PL/SQL Developer) does a schema/database at a time. – eidylon Nov 07 '11 at 19:49
  • I know though ORACLE does have some nice constructs. The biggest one (for my uses) being pre-DML `BEFORE` triggers. GOSH I wish MS would bring that over to Sql Server! – eidylon Nov 07 '11 at 19:50