Oracle metadata

From Wikipedia, the free encyclopedia

The introduction to this article provides insufficient context for those unfamiliar with the subject matter.
Please help improve the introduction to meet Wikipedia's layout standards. You can discuss the issue on the talk page.

The Oracle application server and Oracle relational database keeps metadata in two areas: tables and a metadata registry.

The Oracle RDBMS system keeps its data in several tables and creates several views:

ALL_TABLES - list of all tables in the current database
ALL_TAB_COLS - list of all columns in the database

The views have prefixes "USER_", "ALL_" and "DBA_".

1 Example 1: finding tables
2 Example 2: finding columns
3 Example 3: counting rows of columns
4 Use of underscore in table and column names
5 Oracle Metadata Registry
6 See also
7 External links

[edit] Example 1: finding tables

Find all Tables that have PATTERN in the table name and are not backup or temporary tables

  SELECT
     TABLE_NAME
  FROM
     ALL_TABLES
  WHERE
     TABLE_NAME LIKE '%PATTERN%'
  ORDER
     BY TABLE_NAME;

[edit] Example 2: finding columns

Find all tables that have at least one column that matches a specific PATTERN in the column name

  SELECT
     TABLE_NAME,
     COLUMN_NAME
  FROM
     ALL_TAB_COLUMNS
  WHERE
     COLUMN_NAME LIKE '%PATTERN%';

[edit] Example 3: counting rows of columns

Count number of total number of rows in all tables that with a column name that matches PATTERN ==

  COLUMN DUMMY NOPRINT
  COMPUTE SUM OF NUM_ROWS ON DUMMY
  BREAK ON DUMMY
  SELECT
     NULL DUMMY,
     T.TABLE_NAME,
     C.COLUMN_NAME,
     T.NUM_ROWS
  FROM
     ALL_TABLES T,
     ALL_TAB_COLUMNS C
  WHERE
     T.TABLE_NAME = C.TABLE_NAME
     AND C.COLUMN_NAME LIKE '%PATTERN%'
  ORDER BY T.TABLE_NAME;

[edit] Use of underscore in table and column names

The underscore is a special SQL pattern match to a single character and should be escaped if you are in fact looking for an underscore character in the LIKE clause of a query.

Just add the following after a LIKE statement:

  ESCAPE _

And then each literal underscore should be a double underscore: __

[edit] Oracle Metadata Registry

The Oracle product Oracle Enterprise Metadata Manager (EMM) is an ISO/IEC 11179 compatible metadata registry. It stores administered metadata in a consistent format that can be used for metadata publishing. As of January 2006, EMM is available only through Oracle consulting services.