Saturday, July 23, 2011

What is Oracle metadata?

The short answer is that metadata is data about data. In other words Oracle needs to store data to keep track of what data you're storing in the database. It also needs to store data about the physical and logical structures of the database - such as the names, locations and sizes of all the database files - as well as the instance (the memory structures and the background processes) and the operation of the database such as recently executed sql statements, execution plans, status of objects, checkpoints, log file switches etc.

In fact Oracle devotes two schemas (users) to keeping track of the database and teh data within it. These schemas are called system and sys and will store information about the database schemas that have been created, the objects owned by these schemas, the privileges they have, etc. as well as the information about the structure of the database (both logical and physical), and the operation of the database.

The metadata is stored in its own sets of tables and is usually available to end users to query via views. The views about database objects are conveniently divided into three sets:
those prefixed with "DBA_" which are usually only accessible by sys or system and contain data about all tables, views, indexes and other objects in the database
those prefixed with "ALL_" which contain data about all objects in the database that your account has the privileges to access
those prefixed with "USER_" which contain data about all objects in the database that are owned by you.
The dynamic performance views - information about the instance and the operation of Oracle are prefixed with "V$".

These views present the data in a user-friendly manner and hide the complexity of the underlying tables as well as protecting the underlying tables from accidental update.

0 comments:

Post a Comment

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Design by Deep's | Bloggerized by Deep - Deep's Templates | ElearSQL-Server