A. Data dictionary:Oracle's data dictionary records the metadata of database management, which is the core file of life and death for a database. 1. The data dictionary is placed in the system table space, and the user is sys. 2. Describe the definition of databases and objects,3: The data dictionary is read-only. 4. Is server-maintained, can only query. 5. Mostly created by Catalog.sql.
The commonly mentioned data dictionary consists of four parts:internal RDBMS (x$) tables, data dictionary tables, dynamic Performance (v$) views, and data dictionary views.
two. Data dictionary composition:
1. Internal RDBMS (x$) Table
The x$ table is a central part of the Oracle database, which is used to track internal database information and maintain database uptime . The x$ table is encrypted and named, and Oracle does not write a document stating that this knowledge is the technical secret of Oracle, andthatOracle builds a number of other views through these x$. For users to query the management database. But because the x$ table records a lot of useful information, it is constantly being explored by the global DBA, the most well-known are: X$BH,X$ksmsp and so on.
2. Data dictionary tableData dictionary table is used to store information about tables, indexes, constraints, and other database structures. These objects usually end with "$" ( for example, tab$, obj$, ts$, and so on), when the database is created by running SQL.BSQ script created.
3. Static Data dictionary Viewbecause x$ tables and data dictionary tables are usually not directly accessible, Oracle creates a static data dictionary view to provide users with access to data dictionary information, which is often relatively stable and cannot be modified directly, so it is also referred to as a static count dictionary view. The data dictionary view is created by the Catalog.sql script, which is located in the $ORACLE _home/rdbms/admin/directory, when the database is created .
①. The static data dictionary view is usually divided into three categories according to the prefix: A. The User_ Class View contains information about the objects that the user owns, and the user can use this view to query their ownThe object information that is owned. b The All_ Class View contains information about all objects that the user has permission to access. C The Dba_ Class View contains information about all related objects in the database, and the user needs SELECT any TABLE rightLimited access.
②. Examples of common data dictionary viewsA.dict/dictionary For easy retrieval, Oracle? For a view named Dictionary (DICTIONARY), based on this view, Oracle has created two synonyms named DICT and DICTIONARY:
B. Dict_columnssimilar to the DICT, the Dict_columns View records the Dictionary view column (column) and its related instructions:
c. obj$/dba_objects/objobj$ is a bottom-level dictionary table that records information about all objects in the database, dba_objects views are based on obj$, and same strain, all_objects, and user_objects views are built.
4. Dynamic Performance View:
Dynamic Performance (v$) view, which records database run-time information and statistics, most dynamic performance views are updated in real time to reflect the current state of the database. Oracle presents the status of Oracle databases to users and database administrators through a dynamic performance view, andOracle provides detailed documentation of the v$ view for development managers, which is the master of our research and management database. to rely on.
①.gv$ and v$ viewsOracle creates x$ tables on the fly when the database is started, and on top of that it has created gv$ and v$ views.beginning with Oracle8, the gv$ view is introduced, meaning Global v$. In addition to some exceptions, each v$ view has a corresponding gv$ view present. The gv$ view is generated to meet the needs of the OPS/RAC environment, where the query gv$ view is in the OPS/RAC environmentAll instance information is returned, and each v$ view is based on the gv$ view, increasing the WHERE condition limit for the inst_id columncreated, containing only the current connection instance information. To summarize, Oracle's gv$ View and v$ View are built in the database creation process and built into the data base, and Oracle presents these definitions to us through the v$fixed_view_definition view.
②.gv_$,v_$ views and v$,gv$ synonyms
After gv$ and v$, Oracle established the gv_$ and v_$ views and then established common synonyms for those views.all of this work is done through the Catalog.sql script (which is located in the $oracle_home/rdbms/admin/directory)the.
Excerpt from the Catalog.sql script: Create or Replace View v_$fixed_table as SELECT * from V$fixed_table; Create or replace public synonymv$fixed_tableForv_$fixed_table;Create or Replace view gv_$fixed_table as SELECT * from Gv$fixed_table; Create or replace public synonym gv$fixed_table for gv_$fixed_table;
in fact, the V$ object that most users access is not a view, but rather a synonym for the v_$ view, and the v_$ view is created based on the true v$ view, which is based on the x$ table.
5. Dynamic performance View and database startupbecause dynamic performance views are created automatically during database startup, the views that we can access at different stages of database startup are not the same. ①. In the Nomount phasewhen a database is booted into the Nomount state, it actually launches only the DB instance, at which point the instance information Masterto come from a parameter file, therefore, and parameter file records related information can be queried, the following is the stage can be obtainedmain view of information:V$parameter, V$spparameterV$sga, V$sgastat, V$BH, V$instancev$option, V$versionv$process, V$session②. In the Mount stagewhen the database is booted into the Mount state, the control file is read, and the view associated with the control file can be entered at this timerow queries, here is the main view that this phase can get information about:V$thread, V$controlfile, V$database,V$datafile, V$logfile, V$datafile_header
③.in the Open stagewhen the database is Open, all dynamic performance views and data dictionaries can be queried.
two. Synonyms in the data dictionary:
sql> Select File#,name from V$datafile; (synonyms)file# NAME---------- --------------------------------------------------1/u01/app/oracle/oradata/prod/disk3/system01.dbf2/u01/app/oracle/oradata/prod/disk3/sysaux01.dbf3/u01/app/oracle/oradata/prod/disk3/undotbs01.dbf4/u01/app/oracle/oradata/prod/disk3/users01.dbf
sql> Select File#,name from V_$datafile;
file# NAME---------- --------------------------------------------------1/u01/app/oracle/oradata/prod/disk3/system01.dbf2/u01/app/oracle/oradata/prod/disk3/sysaux01.dbf3/u01/app/oracle/oradata/prod/disk3/undotbs01.dbf4/u01/app/oracle/oradata/prod/disk3/users01.dbf
three. Fuzzy Query view nameselect * FROM dictionary where table_name like ' DBA ';
Four. View of the Data dictionary: (emphasis)
Classification of data dictionary views, statics and dynamics (dynamic)
1. Dynamic Viewmost can be accessed under mount, reflecting the state of the database in real timemost v$ begin with the singular, read from the control file and the memory summary. The name of all the dynamic views is found in this view from V$fixed_table. for tuning and database monitoring. v$_ Singular single-instance dynamic view. Dynamic view of the Gv$_ singular RAC environment
Sql> Select COUNT (*) from v$fixed_table;
COUNT (*)----------1396
Common static Views:select name from V$tablespace;select File#,name from V$datafiles;select * from V$log;select * from V$logfile;select name from V$archived_log;
2. Static Viewaccessed in the open state of the database to understand the physical structure information of the database. most start with DBA, all, user, and in plural formUser_: Stores the currentinformation about the objects owned by the userAll_: Stores objects that the current user can accessDba_: Store information for all user objects (only sys/system user access by default)
Example:View area InformationSelect Segment_name, Segment_type,tablespace_name, bytes/1024 k,extents,blocks from dba_segments;#查看数据文件Sql> select file_id,file_name,tablespace_name,bytes/1024/1024 m from Dba_data_files;
five. Character Set:
"oracle11g,8" Data dictionary and Character set
