From the Database Geek.

Part of the ANSI SQL standard calls for an INFORMATION_SCHEMA. This schema contains a standardized data dictionary that is (or is supposed to be) common across various databases. Most database vendors offer a native data dictionary and a sub-set INFORMATION_SCHEMA (called info schema from this point on). Oracle is the only major database vendor (that I know of) that doesn’t even offer a subset of the info schema.

Oracle’s data dictionary (especially if you include the V$ views in that) is the most robust of all the databases I have used.

I get a lot of email, and comments on blog entries, asking questions about many different topics. I am going to try to answer some of the more common questions as FAQs. Today I am starting with some NUMBER FAQs. I am specifically talking about Oracle and I am specifically talking about the NUMBER data type, not numerics in general. I’ve written about NUMBERs before.

Q: What is the difference between an INTEGER and a NUMBER.

A: INTEGER is a subtype of NUMBER. INTEGER is declared as NUMBER(38,0). That means it is a “constrained” NUMBER.

LewisC’s An Expert’s Guide To Oracle Technology

The datastore in Postgres (which is what Advanced Server is based on) is quite a bit different than the way Oracle stores data. Gone are the usual logical storage components. Data blocks, extents and segments just don’t exist in a Postgres database. Actually, segments do exist when a table gets bigger than 1GB but that’s a story for a different entry.

Postgres stores data as files and directories. Tablespaces are directories and tables (and indexes) are files inside that directory. Postgres ships with two tablespaces installed by default: pg_default and pg_global. The tablespaces cannot be changed or dropped.

Have you wondered what features are compatible between Oracle and EnterpriseDB? Have you wondered if EnterpriseDB is REALLY compatible with Oracle?

EnterpriseDB has compiled a new document called the Oracle Compatibility Developer’s Guide. You can download the Oracle Compatibility Developer’s Guide for EntepriseDB 8.2 or the Oracle Compatibility Developer’s Guide for 8.3 Beta.

This new document should help answer one of the most common questions I get asked, i.e., what is compatible and what is not compatible between EnterpriseDB and Oracle. If you are interested in EnterpriseDB but haven’t taken the plunge, check out this document.

EnterpriseDB Advanced Server’s enterprise-class, cross-platform developer and DBA console now includes EDB*Plus, a command-line terminal interface that emulates Oracle’s SQL*Plus. Like SQL*Plus, EDB*Plus allows users to run SQL and PL/SQL commands interactively. Oracle users accustomed to SQL*Plus will find EDB*Plus immediately familiar. In addition, EnterpriseDB’s new EDB*Loader emulates Oracle’s SQL*Loader and provides even deeper compatibility.

EnterpriseDB Advanced Server 8.3 adds several new features that enhance the database’s ability to run, unchanged, applications written for Oracle. For example, there are more than 20 new Oracle-compatible system views, Oracle-compatible packages now support comments, and ROWNUM can now be used in sub-queries and views. In addition, EnterpriseDB’s new bulk binding feature, which allows collections of SQL statements to be collected and run together to improve performance, further improves Oracle compatibility.

Other updates to the new software include:

• A DBA monitoring console that provides real-time charting for CPU and memory usage, disk I/O, and cache hit ratios across multiple EnterpriseDB databases

• Support for embedded hints, which can alter program execution

• Transaction error recovery support, which enables implicit statement-level transactions

• Updateable cursors

• Definer/invoker rights to provide controlled access to database objects

oracle enterprisedb

A question that I am asked very often it exactly what features are compatible between EnterpriseDB and Oracle. The short story is that the areas that you think of as “development”, i.e. SQL, Code, etc are the areas where the compatibility exists.

Maintenance tasks are generally familiar as is the overall architecture. Any modern database will have logging, recovery, backups, etc. In EnterpriseDB, the way you code an application will be compatible with Oracle and the maintenance of an EnterpriseDB database will be familiar.

EnterpriseDB has published a white paper called, Delivering Oracle Compatibility.