Oracle Metadata

最新推荐文章于 2022-04-06 21:01:00 发布

原创最新推荐文章于 2022-04-06 21:01:00 发布 · 270 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #SQL Server #SQL #C #C++

Bookmarks 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了Oracle数据库中元数据的存储方式及其查询方法。包括通过内置函数访问数据字典表，如列出所有表、列及存储过程等。同时，还提供了查询示例帮助理解如何查找特定模式的表和列。

Oracle metadata

From Wikipedia, the free encyclopedia

Jump to: navigation , search

The introduction to this article provides insufficient context for those unfamiliar with the subject.
Please help improve the article with a good introductory style .

The ORACLE application server and Oracle relational database keep metadata in two areas: data dictionary tables (accessed by built-in functions) and a metadata registry.

The global built-in functions accessing Oracle RDBMS data dictionary tables are:

ALL_TABLES - list of all tables 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

In addition there are equivalent functions 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 "DBA_" metadata functions require DBA privileges.

[hide ]

[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 the total number of rows in all tables containing 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: __

Example

  LIKE '%__G' ESCAPE '_'

[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.