Oracle INFORMATION_SCHEMA

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. If you are knowledgeable about the data dictionary, you would never have any reason to use the info schema. I’ve had discussions with people (inside and out of Oracle) and the general consensus is people ought to learn the Oracle dictionary if they plan to use Oracle. In my opinion, that’s a bad attitude. The info schema may be training wheels, but at least they would be available for newbies who need those wheels.
The info schema is also part of the standard and I think standards are a good thing. Anyway, whether it ought to be there or not is a moot point. All of the information that you would find in the info schema (at least those things that are pertinent to Oracle) are available in the
data dictionary. Seeing that that’s the case, I have started a new open source project called the Oracle Information Schema. It is hosted on sourceforge.net.
The first version of the package includes:

• CATALOG_NAME – The database name
• SCHEMATA – Info about database schema
• TABLES – all_tables type information
• VIEWS – all_views type information
• COLUMNS – all_tab_columns type information
• ROUTINES – a combination of all procedures and all source
• DOMAINS – all_types type information

I plan to add plenty more. If you will be working with Oracle databases (and are an Oracle newbie) or if you have newbies in your databases, or even if you just want to learn about the info schema, download the Oracle Information Schema package and start using it.
You need SYSDBA permissions to create the INFORMATION_SCHEMA user but you can install the views under your own schema if you don’t have that kind of access.
If you want a particular view, send me a request or write it up and submit it. Eventually, I would like to have a complete implementation of the INFORMATION_SCHEMA.

You can download the code here.