Oracle- dictionary

Oracle data dictionary, is the storage of database-related information data.

When creating a table, a space, user access, and so on, it will be saved in a dictionary to provide convenience for future search.

Data dictionaries are divided into two categories.

1, static dictionary

These dictionaries are mainly tables and views.

User_* saves the user's object information

Tables owned by the current user of user_tables

Information about objects owned by the current user of user_objects

Permissions for the current user of user_tab_privs on all tables

All_* saves the object information that the current user can access.

Dba_* saves the object information in the database

Dynamic data dictionary

V$access displays locked database objects session objects in the database

V$session lists the details of the current session

Information about the active instance under the current database of v$active_instance

V$context lists attribute information, space, name, attribute value, etc., for the current session.

Data dictionary is the basic content.

Common data dictionary

-basic information of database 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 about 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 files

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

-- basic information about all tables of DBA users

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

-- basic information about all views of DBA users

Desc dba_views

Select owner,view_name,read_only from dba_views

Basic information about all synonyms for DBA users

Desc dba_synonyms

Select owner,synonym_name,table_owner

Table_name,db_link

From dba_synonyms

-- Information of all sequences of DBA users

Desc dba_sequences

Select sequence_owner,sequence_name,min_value,max_value

Cycle_flag

From dba_sequences

-- Information on all constraints for DBA users

Desc dba_constraints

Select owner,constraint_name,constraint_type

Table_name,status

From dba_constraints

-- basic information about all indexes of DBA users

Desc dba_indexes

Select owner,index_name,index_type,table_owner,table_name

Table_type,uniqueness,compression,logging,status

From dba_indexes

-- basic information about all triggers of DBA users

Desc dba_triggers

Select owner,trigger_name,trigger_type

Table_owner,table_name,column_name

From dba_triggers

-- basic information about all stored procedures of DBA users

Desc dba_source

Select owner,name,type,line,text from dba_source

-- basic information of all segments of DBA users

Desc dba_segments

Select owner,segment_name,segment_type

Tablespace_name,blocks,extents

From dba_segments

-- basic information about all zones of DBA users

Desc dba_extents

Select owner,segment_name,segment_type

Tablespace_name,extent_id,file_id,blocks

From dba_extents

-- basic information about all objects of DBA users

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 the table information that makes up the data dictionary

Desc dict

Select table_name,comments from dict

Query 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 archived status

Desc v$archived_log

Select name,dest_id,blocks,block_size

Archived,status,backup_count

From 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