Oracle metadata
From Wikipedia, the free encyclopedia
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.
<script type="text/javascript"><!----></script>
[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.
[edit
]
See also
[edit
]
External links
分享到:
相关推荐
深入了解Oracle元数据表,按类别列出一些ORACLE用户常用数据字典的查询使用方法。
JDBC Metadata MySQL and Oracle Recipes
以下是对Oracle中dbms_metadata.get_ddl的用法进行了详细的分析介绍,需要的朋友参考下
Metadata—information that describes data—is the foundation of all information...technical brief defines metadata and describes how Oracle Data Integrator can help companies manage it more efficiently.
Allows you to query data Oracle data from Presto.Table names and column metadata is hardcoded right now for performance reasons. You can put in your table metadata for the Oracle table to be queried. ...
下面小编就为大家带来一篇java 查询oracle数据库所有表DatabaseMetaData的用法(详解)。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
ddl_metadata.txt dml_get fra_get param_get.txt redo_switch.txt segment_size.txt session_kill.txt session_sid.txt session_spid.txt shared_pool_free.txt sql_monitor.txt sql_profile.txt sqlhis_awr.txt ...
通过PL/SQL 自己手动生成DDL,不过目前我只写了 表、主键、注释、索引。其他的分区、约束还没写。后期加上 缺陷:每个字段后面都有","需要手动剔除,修改过后的已上传,csdn中不知道怎么修改,只好重新上传
oracle 11g的监听日志和警告日志都是在/u01/oracle/diag/tnslsnr/oracle/listener目录和/u01/oracle/diag/rdbms/db1/db1目录下都有以下分别简称listener目录和db1目录。这两目录下都有如下目录:alert cdump hm ...
9i低版本需要注意 <br>oracle.express.mdm.MdmMetadataProvider与 <br>oracle.olapi.metadata.mdm.MdmMetadataProvider之间的转换,可使用 <br>oracle.express.mdm.MdmMetadataProvider....
29 Oracle OLAP Catalog Metadata Messages (AMD) Part VII Network Messages 30 Oracle Net Messages (TNS) 31 Oracle Names Client Messages (NNC) 32 Oracle Names Server Messages (NNO) 33 Oracle Names ...
Which two statements are true about space - saving features in an Oracle Database? A.An index created with the UNUSABLE attribute has no segment. B.Private Temporary Tables(PTTs)store metadata in ...
oracle数据库中XML字段的操作,--通过查询节点id返回以父节点为根节点的整棵节点树 SELECT extract(value(t),'/chapter').getstringval() ChapterName FROM t_content_metadata,TABLE( xmlsequence ( extract(XML_...
我们都知道在9i之前,要想获得建表和索引的语句是一件很麻烦的...在 Oracle 9i中,我们可以直接通过执行dbms_metadata从数据字典中查处DDL语句。使用这个功能强大的工具,我们可以获得单个对象或整个SCHEMA 的DDL语句。
MetaData元数据管理小工具。作用:可以自动将所有的表信息和字段信息存入元数据表中,便于统一查阅。(注释需要保证库本身已经包含了对于表和字段的注释)方式用于为数据库提供两张表。 meta_field, meta_model。表...
stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' || ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' '; EXECUTE IMMEDIATE stmt; end if; end; --- create or replace trigger SYS.aurora$server$...
ORACLE OSB开发指南,英文版。 目录: Part I IDE Help for Oracle Service Bus 1 Introduction to Oracle Service Bus 2 Tasks Working with Projects, Folders, Resources, and Configurations .....................
它说明了 Informatica Metadata Manager&Business; Glossary如何帮助IT组织在变更 OBIEE/OBIA环境时,将损害关键业务数据的风险降至最低。该集成的元数据管理产品拥有两个用户界面:Metadata Manager(供 IT 用户...
DBMS_METADATA.GET_DDL 包 6. 怎样查看数据库引擎的报错 解答:alert log. 7. 比较truncate和delete 命令 解答:两者都可以用来删除表中所有的记录。区别在于:truncate是DDL操作,它移动HWK,不...