Oracle metadata

Oracle Database provides information about all of the tables, views, columns, and procedures in a database. This information about the information is known as metadata. 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.

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:

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.

Find all Tables that have PATTERN in the table name

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

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

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.

Find view columns

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.

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:

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

Example

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.

...
Wikipedia