Oracle Data Dictionary

I. Oracle DATA dictionary

A data dictionary is where Oracle stores information about a database and uses it to describe the data. For example, the creator information of a table, create time information, the table space information, user access rights information, and so on. When users encounter difficulties in manipulating data in the database, they can access the data dictionary to view detailed information.

Data dictionaries in Oracle have static and dynamic points. The static data dictionary is mostly not changed when the user accesses the data dictionary, but the Dynamic Data dictionary is dependent on the performance of the database and reflects some intrinsic information of the database running, so it is often not invariable to access such data dictionary. These two types of data dictionaries are discussed below.

1. static Data dictionary

This type of data dictionary consists primarily of tables and views, and it should be noted that tables in the data dictionary are not directly accessible, but can access the views in the data dictionary. Views in a static data dictionary are divided into three categories, each of which is made up of three prefixes: user_*, all_*, dba_*.

User_*

This view stores information about the objects that are owned by the current user. (That is, all objects in this user mode)

All_*

The attempt is made to store information about the objects that the current user can access. (all_* does not need to have the object as compared to user_*, only the right to access the object)

Dba_*

This view stores information about all the objects in the database. (If the current user has permission to access these databases, you must generally have Administrator privileges)

As can be seen from the above description, the data stored between the three will certainly overlap, in fact, they are in addition to the scope of access (because the permissions are different, so the scope of access to the object is not the same), others have consistency. Specifically, because the data dictionary view is owned by the SYS (System user), in the case of a state, only SYS and users with DBA system privileges can see all views. Users who do not have DBA authority can only see user_* and all_*. If the relevant SELECT permission is not granted, they cannot see the dba_* view.

Because of the similarity of the three, the following example introduces several common static views in User_:

User_users View

Mainly describes the current user's information, including the current user name, account ID, account status, tablespace name, creation time, and so on. For example, you can return this information by executing the following command.

SELECT * FROM User_users

User_tables View

This paper mainly describes the information of all the tables owned by the current user, including the table name, table space name, cluster name and so on. This view provides a clear understanding of what tables the current user can manipulate. The Execute command is: SELECT * from User_tables

User_objects View

It mainly describes the information of all objects owned by the current user, including tables, views, stored procedures, triggers, packages, indexes, sequences, and so on. This view is more comprehensive than the User_tables view. For example, to obtain information about an object type named "Package1" and its status, you can execute the following command:

　　

Select Object_type,status

From User_objects

where Object_name=upper (' package1 ');

Note: The use of upper, all objects in the data dictionary are uppercase, and PL/SQL is not case sensitive, so be sure to pay attention to case matching in practice.

User_tab_privs View

The view is primarily to store permission information for all tables under the current user. For example, to understand the current user's permissions to table1, you can execute the following command:

SELECT * from User_tab_privs where Table_name=upper (' table1 ')

Knowing the current user's permissions on the table gives you a clear idea of what actions can be performed and what actions cannot be performed.

The previous view is User_, but the All_ beginning is exactly the same, but the information listed is the object that the current user can access instead of the current user. For Dba_ to start with administrator privileges, other uses are exactly the same, here is not to repeat.

2. Dynamic Data dictionary

Oracle contains a number of potential tables and views that are maintained by system administrators such as SYS, which are called Dynamic Data dictionaries (or dynamic performance views) because they are constantly updated when the database is running. These views provide information about memory and disk operations, so we can only read access to them and not modify them.

These dynamic performance views in Oracle are views that start with v$, such as v$access. Here are a few of the main dynamic performance views.

V$access

This view displays the database objects that are locked in the database and the session objects that access them (the sessions object).

Run the following command:

SELECT * FROM V$access

The results are as follows: (because there are more records, so this is only part of the record)

Sid

OWNEROBJECTTYPE

DKHv$accessCURSOR

publicv$accesssynonym

SYSdbms_application_infoPackage

SYSgv$accessVIEW

V$session

This view lists the details of the current session. Because the view field is large, the detail field is not listed here, for more information, you can type it directly under the Sql*plus command line: Desc v$session.

V$active_instance

This view primarily describes the information for an instance of an activity under the current database. You can still use the SELECT statement to observe this information.

V$context

This view lists the property information for the current session. such as namespaces, property values, and so on.

3. Summary

The above is the Oracle data dictionary aspects of the basic content, there are many useful views because of the length of reasons can not be explained here, I hope everyone in peacetime use more attention. In short, the use of good data dictionary technology, can enable database developers to better understand the full picture of the database, so that the database optimization, management, etc. have a great help.

Ii. common data dictionaries in Oracle

The data dictionaries listed below are all tested on Oracle 11g R1 with Oracle SQL Developer. Many of these data dictionaries must be logged in to the system or SYSDBA user to be able to use them.

---basic information for a DB instance

Desc v$instance;

SELECT * from V$instance;

--Basic information of data files

Desc V$datafile;

SELECT * from V$datafile;

Desc Dba_data_files;

Select File_name,file_id,tablespace_name,bytes,blocks,

Status,online_status

From Dba_data_files;

--basic information for temporary files

Desc Dba_temp_files;

Select File_name,file_id,tablespace_name,status,

bytes/1024/1024 size MB

From Dba_temp_files;

--Basic information of control files

Desc V$controlfile;

Select Name,status,is_recovery_dest_file,

Block_size,file_size_blks

From V$controlfile;

--Basic information of log files

Desc V$logfile;

Select Group#,status,type,member,is_recovery_dest_file

From V$logfile;

--Basic information of the database

Desc v$database;

SELECT * from V$database;

Select Dbid,name,created,resetlogs_time,log_mode,

open_mode,checkpoint_change#,archive_change#,

Controlfile_created,controlfile_type,

controlfile_sequence#,controlfile_change#,

Controlfile_time,protection_mode,database_role

From V$database;

--Log file parameter information

Show parameter log_archive_dest;

--Access parameter file

Desc V$parameter;

Select Num,name,type,value,display_value,

Isdefault,isses_modifiable,

Issys_modifiable,isinstance_modifiable

From V$parameter;

SELECT * from V$parameter;

Select Name,value,description from V$parameter;

--Background process information

Desc v$bgprocess;

Select Paddr,pserial#,name,description,error from V$bgprocess;

--DBA basic information for all tables of the user

Desc Dba_tables;

Desc Dba_tab_columns;

Select Owner,table_name,column_name,data_type,data_length,

Global_stats,data_upgraded,histogram

From Dba_tab_columns;

--DBA basic information for all views of the user

Desc dba_views;

Select Owner,view_name,read_only from Dba_views;

--DBA basic information for all synonyms of the user

Desc dba_synonyms;

Select Owner,synonym_name,table_owner,

Table_name,db_link

From Dba_synonyms;

--DBA information for all the sequences of users

Desc dba_sequences;

Select Sequence_owner,sequence_name,min_value,max_value,

Cycle_flag

From Dba_sequences;

--DBA information about all the constraints of the user

Desc dba_constraints;

Select Owner,constraint_name,constraint_type,

Table_name,status

From Dba_constraints;

--DBA basic information for all indexes of the user

Desc dba_indexes;

Select Owner,index_name,index_type,table_owner,table_name,

Table_type,uniqueness,compression,logging,status

From Dba_indexes;

--DBA basic information for all triggers of the user

Desc dba_triggers;

Select Owner,trigger_name,trigger_type,

Table_owner,table_name,column_name

From Dba_triggers;

--DBA basic information for all stored procedures of the user

Desc Dba_source;

Select Owner,name,type,line,text from Dba_source;

--DBA basic information for all segments of the user

Desc dba_segments;

Select Owner,segment_name,segment_type,

Tablespace_name,blocks,extents

From Dba_segments;

--DBA basic information for all areas of the user

Desc dba_extents

Select Owner,segment_name,segment_type,

Tablespace_name,extent_id,file_id,blocks

From Dba_extents;

--DBA basic information for all objects of the user

Desc dba_objects;

Select Owner,object_name,subobject_name,

Object_id,data_object_id,object_type,

Created,status,namespace

From Dba_objects;

--all base tables that the current user can access

Desc Cat;

Select table_name from Cat;

--all base tables, views, synonyms that the current user can access

Desc System.tab;

Select Tname,tabtype,clusterid from System.tab;

--all table information that makes up the data dictionary

Desc dict;

Select Table_name,comments from Dict;

--Querying some basic data dictionaries about tablespaces

Desc dba_tablespaces;

Select Tablespace_name,block_size,status,

Logging,extent_management

From Dba_tablespaces;

Desc Dba_free_space;

Select tablespace_name,file_id,block_id,

bytes/1024/1024 size Mb,blocks,relative_fno

From Dba_free_space;

--Some basic information about the status of the archive

Desc V$archived_log;

Select Name,dest_id,blocks,block_size,

Archived,status,backup_count

From V$archived_log;

--Some information about the memory structure

Desc V$SGA;

Select name,value/1024/1024 size MB from V$SGA;

Desc V$sgastat;

Select Pool,name,bytes from V$sgastat;

Desc V$db_object_cache;

Select Owner,name,db_link,type,namespace,locks from V$db_object_cache;

Desc V$sql;

Select Sql_text,sql_id,cpu_time from V$sql;

The above is the more commonly used in Oracle data dictionary, it is necessary to mention that there are many are "Dba_" the beginning of the data dictionary, such as the dba_tables, in fact, this is a list of all the data in the database, such as the use of dba_tables Will list all the data tables in the database (n more), but you can use User_tables or all_tables instead of dba_tables, of course, some of them do not correspond to, so, will only list the current logged-in user allowed to view the data table, for "Dba_ "Basically there is the corresponding" User_ "and" All_ ", where" user_ "corresponds to the record of the current user's object, and" All_ "is to record the current logged on the user's object information and authorized access to the object information, and" Dba_ "is a database instance contains all the object information!!!

Oracle Data Dictionary