Oracle metadata

Encyclopedia : O : OR : ORA : Oracle metadata

To meet Wikipedia's and make it more accessible to a general audience, this article may require [Cleanupcleanup].
The introduction to this article provides insufficient context for those unfamiliar with the subject matter.
Please help Wikipedia by improving the introduction according to the guidelines laid out at [Guide to layout]. You can discuss the issue on the [[Talk:|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_".

Contents

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

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;

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%';

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;

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: __

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.

External links

[article on Oracle Metadata]

From Wikipedia, the Free Encyclopedia. Original article here. Support Wikipedia by contributing or donating.
All text is available under the terms of the GNU Free Documentation License See Wikipedia Copyrights for details.