Oracle metadata

This article needs additional citations for verification. Please help improve this article by adding citations to reliable sources. Unsourced material may be challenged and removed.
Find sources: "Oracle metadata" – news · newspapers · books · scholar · JSTOR (July 2022) (Learn how and when to remove this message)

|

Oracle Database provides information about all of the tables, views, columns, and procedures in a database. This information about information is known as metadata.[1] It is stored in two locations: data dictionary tables (accessed via built-in views) and a metadata registry.

Other relational database management systems support an ANSI-standard equivalent called information schema.

## Views for metadata

[edit]

The total number of these views depends on the Oracle version, but is in a 1000 range.

The main built-in views accessing Oracle RDBMS data dictionary tables are few, and are as follows:

* ALL_OBJECTS – list of all objects in the current database that are accessible to the current user;
* ALL_TABLES – list of all tables in the current database that are accessible to the current user;
* ALL_VIEWS – list of all views in the current database that are accessible to the current user;
* ALL_TAB_COLUMNS – list of all columns in the database that are accessible to the current user;
* ALL_ARGUMENTS – lists the arguments of functions and procedures that are accessible to the current user;
* ALL_ERRORS – lists descriptions of errors on all stored objects (views, procedures, functions, packages, and package bodies) that are accessible to the current user;
* ALL_OBJECT_SIZE – included for backward compatibility with Oracle version 5;
* ALL_PROCEDURES – (from Oracle 9 onwards) lists all functions and procedures (along with associated properties) that are accessible to the current user;
* ALL_SOURCE – describes the text (i.e. PL/SQL) source of the stored objects accessible to the current user;
* ALL_TRIGGERS – list all the triggers accessible to the current user.

In addition there are equivalent views prefixed "USER_" which show only the objects owned by the current user (i.e. a more restricted view of metadata) and prefixed "DBA_" which show all objects in the database (i.e. an unrestricted global view of metadata for the database instance). Naturally the access to "DBA_" metadata views requires specific privileges.

### Example 1: finding tables

[edit]

Find all Tables that have PATTERN in the table name

SELECT Owner AS Schema_Name, Table_Name
FROM   All_Tables
WHERE  Table_Name LIKE '%PATTERN%'
ORDER  BY Owner, Table_Name;

### Example 2: finding columns

[edit]

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

SELECT Owner AS Schema_Name, Table_Name, Column_Name
FROM   All_Tab_Columns
WHERE  Column_Name LIKE '%PATTERN%'
ORDER  BY 1,2,3;

### Example 3: counting rows of columns

[edit]

Estimate a total number of rows in all tables containing a column name that matches PATTERN (this is SQL*Plus specific script)

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%'
AND T.OWNER = C.OWNER
ORDER BY T.TABLE_NAME;

Note that NUM_ROWS records the number of rows which were in a table when (and if) it was last analyzed. This will most likely deviate from the actual number of rows currently in the table.

### Example 4: finding view columns

[edit]

Find view columns

SELECT TABLE_NAME,
column_name,
decode(c.DATA_TYPE,
'VARCHAR2',
c.DATA_TYPE || '(' || c.DATA_LENGTH || ')',
'NUMBER',
DECODE(c.data_precision,
NULL,
c.DATA_TYPE,

c.DATA_TYPE,
c.DATA_TYPE || '(' || c.data_precision || DECODE(c.data_scale,
NULL,
')',

')' ,
', ' || c.data_scale || ')')),
c.DATA_TYPE) data_type
FROM cols c, obj o
WHERE c.TABLE_NAME = o.object_name
AND o.object_type = 'VIEW'
AND c.table_name LIKE '%PATTERN%'
ORDER BY c.table_name, c.column_id;

Warning: This is incomplete with respect to multiple datatypes including char, varchar and timestamp and uses extremely old, deprecated dictionary views, back to oracle 5.

### Use of underscore in table and column names

[edit]

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

Example

LIKE '%__G' ESCAPE '_'

## Oracle Metadata Registry

[edit]

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. In January 2006, EMM was available only through Oracle consulting services.

## See also

[edit]

* Information schema
* Metadata

## References

[edit]

1. ^ "Communications Data Model Implementation and Operations Guide". docs.oracle.com. Retrieved 2022-07-05.

## External links

[edit]

* article on Oracle Metadata