Summary of common data dictionaries in Oracle

Original source: Little BMW's father-the dream home

In front of, but also intermittently introduced some such as sql*plus and so on Oracle's basic content,

For a large database such as Oracle, its own operation and maintenance is also a great thing,

So there are more than n data dictionaries in Oracle, and a data dictionary as a meta-data,

The role in Oracle is to hold the basic information of the entire database instance, essentially a set of tables,

Many of these data dictionaries are owned by the SYS user.

This blog post is a more comprehensive introduction to Oracle's data dictionary,

The way to do this is to list some commonly used data dictionaries,

These data dictionaries, which are listed below, are on the Oracle 11g R1,

Tested through Oracle Sql Developer, all through.

Many of these data dictionaries must be logged in to the system or SYSDBA user to be able to use them.

Basic information---A DB instance desc V$instance;select * From v$instance;--data file basic Information desc V$datafile;select * from V$datafile;desc dba_data _files;select File_name,file_id,tablespace_name,bytes,blocks, Status,online_statusfrom dba_data_files;-- Basic information for temporary files desc dba_temp_files;select file_name,file_id,tablespace_name,status, bytes/1024/1024 size Mbfrom dba_temp_fil es;--basic information about control files desc v$controlfile;select name,status,is_recovery_dest_file, Block_size,file_size_blksfrom v$ controlfile;--basic information for log files desc v$logfile;select group#,status,type,member,is_recovery_dest_filefrom v$logfile;-- Basic information about the database desc V$database;select * from V$database;select Dbid,name,created,resetlogs_time,log_mode, Open_mode,checkpo       int_change#,archive_change#, Controlfile_created,controlfile_type, controlfile_sequence#,controlfile_change#, Controlfile_time,protection_mode,database_rolefrom 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;se Lect name,value,description from v$parameter;--background process information desc V$bgprocess;select paddr,pserial#,name,description,error Basic information for all tables of the from V$BGPROCESS;--DBA user desc Dba_tables;desc dba_tab_columns;select owner,table_name,column_name,data_ Type,data_length, Global_stats,data_upgraded,histogramfrom dba_tab_columns;--dba The basic information of all views of the user desc dba_views; Select Owner,view_name,read_only Basic information for all synonyms of the from DBA_VIEWS;--DBA user desc dba_synonyms;select owner,synonym_name, Table_owner, Table_name,db_linkfrom dba_synonyms;--dba All the sequence information of the user desc Dba_sequences;select sequence_owner,sequence _name,min_value,max_value, Cycle_flagfrom dba_sequences;--dba all the constraint information of the user desc Dba_constraints;select Owner,constrai Nt_name,constraint_type, Table_name,statusfrom dba_constraints;--dba basic information for all indexes of users desc dba_indexes;select owner,ind Ex_name,index_type,table_owner,table_name, Table_type,Uniqueness,compression,logging,statusfrom DBA_INDEXES;--DBA The basic information for all triggers of the user desc Dba_triggers;select owner,trigger_ Name,trigger_type, Table_owner,table_name,column_namefrom dba_triggers;--dba basic information for all stored procedures of the user desc Dba_source;select Owner,name,type,line,text basic information for all segments of the user from Dba_source;--dba desc dba_segments;select owner,segment_name,segment_ Type, Tablespace_name,blocks,extentsfrom DBA_SEGMENTS;--DBA the basic information for all the extents of the user desc Dba_extentsselect owner,segment_name,s Egment_type, Tablespace_name,extent_id,file_id,blocksfrom dba_extents;--dba basic information for all objects of the user desc dba_objects;select ow Ner,object_name,subobject_name, Object_id,data_object_id,object_type, Created,status,namespacefrom dba_objects ;--all base tables that the current user can access DESC cat;select table_name from cat;--the current user can access all base tables, views, synonyms desc system.tab;select tname,tabtype, Clusterid from system.tab;--All table information that constitutes a data dictionary desc dict;select table_name,comments from dict;--querying some basic data dictionary about tablespace desc dba_       Tablespaces;select Tablespace_name,block_size,status,Logging,extent_managementfrom dba_tablespaces; Desc dba_free_space;select tablespace_name,file_id,block_id, bytes/1024/1024 size Mb,blocks,relative_fnofrom 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_countfrom v$ archived_log;--some information about 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 data dictionary in Oracle,

It should be mentioned that there are many data dictionaries that begin with "Dba_".

For example, there are dba_tables, in fact, this is a list of all the data in the database,

For example, using 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, and of course there are some that don't correspond to it,

This will only list the data tables that are allowed to be viewed by the current logged-on user.

For the "Dba_" is basically the corresponding "user_" and "All_",

where "user_" corresponds to the object that records the current logged-in user,

and "All_" is to record the current logged on user's object information and authorized access to the object information,

and "Dba_" is all the object information that contains the DB instance!!!

Summary of common data dictionaries in Oracle