Oracle中数据字典及相关查询应用

墨蓝 2024-03-17 09:10 30阅读 0赞

**目录**

一、概念

二、数据字典内容

三、数据字典分类

3.1数据字典表

3.2数据字典视图

Ⅰ、静态视图

Ⅱ、动态视图

四、数据字典用途

五、数据字典相关的查询

1. 查询当前用户拥有的表

2. 查询所有模式下的表（当前用户可以访问的）

3. 查询所有数据库对象的详细信息

4. 查询表的索引信息

5. 查询表列的详细信息

6. 查询序列信息

7. 查询当前用户可见的视图

8. 查询数据库中更改的所有日志记录

9. 查询正在使用的会话信息

10. 查询SGA缓存区的大小和使用情况

11. 查询表所占用的空间

12. 查询当前活动会话所执行的SQL语句

13. 查询索引是否被优化使用（如是否有大量回表操作）

14. 查询所有库对象的版本号

15. 查询归档日志和序列的使用情况

16. 查询不可用的约束和关系表

17. 查看数据库对象的依赖关系

18.查询自上次启动后运行的SQL语句计数器

19.查询正在使用的Redo日志文件和组信息

20.查询当前用户使用的默认表空间

21.查询Oracle数据库实例的版本号

--------------------

## 一、概念 ##

在Oracle数据库中，数据字典(data dictionary)是一组***描述数据库对象的元数据***，这些元数据可以用于管理和维护数据库。

***对用户来说是一组只读的表***，***用户可以访问的记录数据库和应用程序源数据的目录，***是一个**自动或手动存储**数据源定义和属性的文档，对数据的数据项、数据结构、数据流、数据存储、处理逻辑、外部实体等进行定义和描述，其内容包括数据库中所有模式对象的信息，如表、视图、簇及索引等。它记录了有关表、视图、索引、约束、存储过程和其他对象的信息，如其名称、所有者、结构、列、索引等。

*  主动数据字典是指在对数据库或应用程序结构进行修改时，其内容可以由DBMS自动更新的数据字典。
 *  被动数据字典是指修改时必须手工更新其内容的数据字典。

其***目的是对数据流程图中的各个元素做出详细的说明***。

数据字典可以***像其他数据库表或视图一样进行查询，但是它们不能被修改***。它们存储在系统表空间中。当用户在操作数据库中的数据时遇到困难，可以访问数据字典来查看详细信息。数据字典不仅是数据库的核心，也是所有用户，包括最终用户、程序员和数据库管理员的重要工具。因为数据字典是只读的，所以我们只能对其表或视图使用SQL查询语句。

数据库字典通常是*在创建和安装数据库时就被创建的，由一系列表和视图构成，这些表和视图对于所有的用户（包括DBA)都是只读的*。oracle数据字典是oracle数据库系统工作的基础，没有数据字典的支持，oracle数据库系统就不能进行任何工作。

*  数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。
 *  只有Oracle系统才可以对数据字典进行管理与维护。
 *  在Oracle数据库中，所有数据字典表和视图都属于SYS模式，存储于SYSTEM表空间中。

## 二、数据字典内容 ##

Oracle数据库中的数据字典包括一系列系统表和视图，其中记录了有关数据库对象（如表、列、索引、约束、触发器、存储过程等）的元数据信息。以下是Oracle数据字典常见的内容：

*  1. 表信息：包括表名、列名、数据类型、长度、空值属性、默认值、主键或唯一性约束、外键约束等。
 *  2. 索引信息：包括索引名称、所属表、是否为唯一索引、索引列顺序、索引类型等。
 *  3. 视图信息：包括视图名称、定义视觉的SQL语句、视图所关联的表等信息。
 *  4. 过程和函数信息：包括名称、输入参数、返回值类型和参数等信息。
 *  5. 用户和角色信息：包括用户名、密码、默认模式、创建日期、所拥有的角色等信息。
 *  6. 权限信息：包括用户对数据库对象（如表、视图和程序等）的权限信息，如SELECT、INSERT、UPDATE和DELETE等。
 *  7. 数据库配置信息：包括数据库名称、版本、字符集、日志信息等。

## 三、数据字典分类 ##

以Oracle数据库字典为例：数据字典分为数据字典表和数据字典视图。

### 3.1数据字典表 ###

Oracle数据库是基于表的，并且这些表都有其对应的数据字典表。数据字典表以“$”结尾的表名，它们位于SYSTEM或SYS模式下，并且只能由Oracle数据库管理工具连接到这些表进行查询。这些表可以提供与用户表相同的功能，但其主要目的是存储数据库元数据信息。

例如：

*  SYS.USER\_TABLES：包含所有用户都可以访问或当前用户可以访问的表的元数据信息。
 *  SYS.DBA\_TABLES：包含整个数据库中的所有表的元数据信息。
 *  SYS.OBJ$：包含与 Oracle 数据库实例创建的所有对象相关的详细信息。

而且数据字典表由$ORACLE HOME/rdbms/admin/sql.bsq脚本创建，这个脚本里又调用了其他的脚本来创建这些数据字典表。在那些创建脚本里有基表的创建SQL。

### 3.2数据字典视图 ###

数据字典视图是一组与数据字典表相关的虚拟表结构。这些视图可以被任何有效的Oracle数据库用户访问，而无需访问系统或管理员级别的功能。正如数据字典表一样，它们也保存了特定类别的元数据信息，可以通过SQL查询进行访问和操作。但是数据字典视图又可以分为静态和动态两种类型。

#### Ⅰ、静态视图 ####

静态视图是一种固定的，只包含元数据信息的数据字典视图。这些视图始终返回固定的列和行。静态视图通常***以 USER\_、DBA\_ 或 ALL\_ 开头***，并以底部的具体对象（如表、序列、索引等）的名称结尾。例如，USER\_TABLES、ALL\_INDEXES、DBA\_VIEWS 等。

*  USER\_TABLES：包括所有用户都可以访问或当前用户可以访问的表的元数据信息。该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象）
 *  ALL\_TABLES：包括列出该数据库中任何模式下的表的元数据信息。该试图存储了当前用户能够访问的对象的信息，而不是当前用户拥有的对象。（与usr\_\*相比，all\_\*并不需要拥有该对象，只需要具有访问该对象的权限即可)
 *  DBA\_TABLES：包括整个数据库中的所有表的元数据信息。该视图存储了数据库中所有对象的信息。(前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限)

下表中列举一些常用的当前用户元数据信息：

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>user_tables</td> 
   <td>所有的表</td> 
  </tr> 
  <tr> 
   <td> <p>user_views &nbsp; &nbsp; &nbsp;</p> </td> 
   <td>所有的视图</td> 
  </tr> 
  <tr> 
   <td> <p>user_sequences &nbsp;</p> </td> 
   <td>所有的序列</td> 
  </tr> 
  <tr> 
   <td> <p>user_indexes &nbsp; &nbsp;</p> </td> 
   <td>所有的索引</td> 
  </tr> 
  <tr> 
   <td> <p>user_constraints</p> </td> 
   <td>所有的约束</td> 
  </tr> 
  <tr> 
   <td> <p>user_source &nbsp; &nbsp; &nbsp;</p> </td> 
   <td>所有的有名块(存过/函数/包..)</td> 
  </tr> 
  <tr> 
   <td> <p>user_recyclebin &nbsp;</p> </td> 
   <td>回收站</td> 
  </tr> 
  <tr> 
   <td>user_tab_cols &nbsp; &nbsp;</td> 
   <td>所有表所有列的属性</td> 
  </tr> 
  <tr> 
   <td>user_tab_columns &nbsp;</td> 
   <td>所有表所有列的属性</td> 
  </tr> 
  <tr> 
   <td>user_tab_comments</td> 
   <td>表注释</td> 
  </tr> 
  <tr> 
   <td>user_col_comments</td> 
   <td>列注释</td> 
  </tr> 
 </tbody> 
</table>

**优点：**查询速度非常快，因为它们固定且存储了完整的元数据信息。

**缺点：**由于它们没有对基础数据库对象实时地进行查询，所以无法提供某个时刻的精确信息。

#### Ⅱ、动态视图 ####

与静态视图不同，动态视图是模拟一个表来直接访问数据库中特定对象的信息。从本质上讲，它是一个带有查询功能的计算机程序，**时刻查询基础对象并提供当前最新的数据**。在Oracle数据库中，***V$ 系列视图是指提供动态数据的所有视图***。

例如：

v$session 当前有谁连接我的数据库,terminal是主机名

**动态视图存在于SGA（共享池内存）中**，而不是保存在数据字典表或常规内存中。这些视图可以随着系统活动的变化而变化，例如查询活动、等待事件、资源使用情况和锁定状态等。这使得动态视图非常适合对正在运行的活动进行监控和分析。

虽然动态视图**提供了实时信息**，但由于它们在查询系统对象时会带来额外的负担，所以**查询速度慢于静态视图**。另外需要注意的是，由于动态视图基于SGA中的数据操作，可能**存在一些并发性**问题。

> **总结：**
> 
> 静态视图可以快速获取实际和历史数据；
> 
> 动态视图提供了实时的数据，可以更好地监控正在运行的活动。

## 四、数据字典用途 ##

Oracle数据字典是一组视图和数据字典表的集合，它们为数据库管理员（DBA）、开发人员以及其他使用者提供了关于数据库对象、内部结构及性能的元数据信息。下面是Oracle数据字典的用途：

*  1. 数据库监控：通过访问数据字典视图，可以监控数据库的性能以及现有的连接。
 *  2. 数据库分析和管理：数据字典可以对数据库架构、表、列、约束、索引等进行查询，并支持DDL语句的生成，例如创建、修改、删除表或者索引。
 *  3. 系统信息和参数调优：数据字典是一个重要的资源，可以帮助您找到您的操作系统、网络配置、Oracle软件版本等各种信息。
 *  4. 安全审计：审计人员或应用程序开发人员可以通过数据库字典检查某些敏感字段是否在某些表中被包含和保留，从而确保符合安全通信和隔离标准。
 *  5. 备份和恢复：数据字典还存储着所有备份集、归档日志、控制文件等与数据库备份和恢复相关的信息，这些信息对于DBA来说非常重要。
 *  6. 对象依赖性分析：数据字典存储元数据信息，可以确定数据库模式中的表之间的依赖关系，以及图形展示对象之间的依赖关系。
 *  7. 编程：应用程序开发人员可以使用数据字典中的信息进行数据库编程，如查询表、列和索引的元数据或创建新的数据库对象等。

## 五、数据字典相关的查询 ##

以下是一些Oracle数据字典中常用的SQL查询语句：

### 1. 查询当前用户拥有的表 ###

SELECT * FROM USER_TABLES;

**注释：**

USER\_TABLES显示了当前用户下的表及其基本信息。

以下是我用Oracle11g查询后一些特别注意的相关列名和说明：

1.  TABLE\_NAME：表名称（主要）
2.  TABLESPACE\_NAME：表空间名称
3.  STATUS：状态（可填写ENABLED或DISABLED）
4.  PARTITIONED：表是否分区
5.  IOT\_TYPE：IOT（index-organized table）类型
6.  CLUSTER\_NAME：表所属的集群名称
7.  PCT\_FREE：空块中保留空间的百分比
8.  PCT\_USED：当前正在使用的块的百分比
9.  NUM\_ROWS: 表包含的行数
10. BLOCKS: 表占用的块数

> 如果查询的STATUS是VALID，与上面不同莫着急！！！
> 
> 当一个表或索引被创建时，它将会被分配一个状态值。VALID是其中之一。在结果中，如果STATUS显示为VALID，那么这个表被认为是有效的、可访问的，且可以被正常使用。也就是说，这个表已经成功创建并通过了Oracle系统的验证和检查。这是默认情况下，新创建的表所应该呈现的状态值。
> 
> 除此之外，Oracle还会把其他状态值分配给表和索引，包括INVALID、UNUSABLE和N/A等。如果一个表或索引的状态为INVALID或UNUSABLE，意味着它不能被正常地访问或使用，需要进行处理、修复或重建操作。而N/A通常表示状态未知或未适用于当前对象。
> 
> **总结**：
> 
> 在查询USER\_TABLES视图时，检查和关注表的**状态**是非常重要的，它可以帮助我们快速诊断和解决与表相关的问题。

### 2. 查询所有模式下的表（当前用户可以访问的） ###

SELECT * FROM ALL_TABLES;

**注释：**

ALL\_TABLES显示了所有用户有权访问的表及其基本信息。

以下是我用Oracle11g查询后一些特别注意的相关列名和说明：

1.  OWNER：表所属的用户的用户名
2.  TABLE\_NAME：表名称（主要）
3.  TABLESPACE\_NAME：表空间名称
4.  STATUS：状态（可选值：VALID、INVALID或N/A）
5.  TEMPORARY：是否为临时表
6.  DURATION：临时表的生命周期
7.  PARTITIONED：表是否分区
8.  IOT\_TYPE：IOT（index-organized table）类型
9.  CLUSTER\_NAME：表所属的集群名称
10. PCT\_FREE：空块中保留空间的百分比
11. PCT\_USED：当前正在使用的块的百分比
12. NUM\_ROWS ：表包含的行数
13. BLOCKS：表占用的块数

> 如果在查询ALL\_TABLES视图时，TEMPORARY列的结果为N，则表示此表不是临时表。
> 
> 临时表是一种可用于暂存数据的特殊表，与普通表不同，它们只在当前会话或事务中存在，并在操作完成后自动清除。因此，临时表通常用于执行高速插入、删除和排序等任务，以减轻大型表的负载。
> 
> 如果需要使用临时表来缓存数据而不会干扰其他用户或会话，则可以创建一个临时表。临时表是由'TEMP'前缀区分的名字命名的，例如：
> 
>     CREATE GLOBAL TEMPORARY TABLE temp_emp (id NUMBER, 
>                                             name VARCHAR2(50)) 
>     ON COMMIT DELETE ROWS;
> 
> 否则，如果要创建一个标准的普通表，则可以省略TEMPORARY选项或将其设置为N。
> 
> 因此如果查询ALL\_TABLES视图，当TEMPORARY列的结果为N时，则表示该表是一个普通的非临时表。

### 3. 查询所有数据库对象的详细信息 ###

SELECT * FROM ALL_OBJECTS;

**注释：**

ALL\_OBJECTS显示了数据库中包含的所有对象（表、视图、索引、触发器等）及其基本信息。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  OWNER：对象所属的用户名
2.  OBJECT\_NAME：对象名称（主要）
3.  SUBOBJECT\_NAME：如果对象有子对象，则为子对象的名称（例如分区表就有分区子项）
4.  OBJECT\_ID：对象标识符
5.  DATA\_OBJECT\_ID：数据对象标识符
6.  OBJECT\_TYPE：对象类型（如表格(TABLE)、视图(VIEW)、序列(SEQUENCE)、程序(PACKAGE)等）
7.  CREATED：对象创建日期
8.  LAST\_DDL\_TIME：最后一个DDL操作的时间（包括创建、修改、重命名等等）
9.  TIMESTAMP：时间戳
10. STATUS：状态 (可选值: VALID, INVALID, N/A)
11. TEMPORARY：是否为临时表
12. GENERATED：是否为“generated as”列
13. SECONDARY：是否为二级对象
14. NAMESPACE：对象所在的命名空间的类型
15. EDITION\_NAME：用于支持Oracle数据库在线架构变更功能的功能的名称
16. SHARING：库中此对象是否被视为共享或非共享
17. EDITIONABLE：是否支持在线修改

> 主要关注信息列为OBJECT\_NAME，OBJECT\_TYPE和CREATED。
> 
> 其中，OBJECT\_NAME表示对象名称，OBJECT\_TYPE表示对象类型，例如表、视图或序列，而CREATED则显示对象的创建时间。
> 
> **注意：**
> 
> 虽然ALL\_OBJECTS视图提供了大量有用的信息，但也仅限于当前用户可以访问到的对象。如果想要查询其他用户拥有的对象或数据库中所有对象的信息，则需要使用相应的权限或查询其他数据字典表或视图。
> 
> 此外，在实际操作中，通常情况下我们是只关注特定类别的对象。

### 4. 查询表的索引信息 ###

SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME
    FROM ALL_INDEXES
    WHERE TABLE_NAME='table_name';

**注释：**

ALL\_INDEXES是Oracle数据库中的一个系统视图，用于显示所有者拥有的表和索引的信息。它包含了当前用户及公共对象（如果允许访问）的所有索引信息。通过查询 ALL\_INDEXES 视图，您可以获得具体的索引信息，如索引名、索引类型、是否唯一、属于哪个表等等。此外，该视图还提供了其他有关索引的详细信息。

> **注意：**
> 
> ALL\_INDEXES 只显示索引的信息，而不会显示表的全部数据。我们可能需要结合其他数据字典视图或自己编写 SQL 查询来获取完整的表和索引信息。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  INDEX\_NAME：索引名称（主要）
2.  TABLE\_OWNER：表的所有者
3.  TABLE\_NAME：索引所属表的名称（主要）
4.  UNIQUENESS：索引是否唯一
5.  STATUS：索引的状态
6.  PARTITIONED：索引是否分区
7.  COMPRESSION：索引是否压缩
8.  INI\_TRANS：初始化事务数
9.  MAX\_TRANS：事务最大值
10. PCT\_FREE：存储块保留百分比
11. TABLESPACE\_NAME：索引所属表空间的名称
12. INITIAL\_EXTENT：初始扩展大小
13. NEXT\_EXTENT：下一个扩展大小
14. MIN\_EXTENTS：最小扩展数量
15. MAX\_EXTENTS：最大扩展数
16. PCT\_INCREASE：扩展幅度增量
17. FREELISTS：段中空闲列表数量

> 我们主要关注信息列为 INDEX\_NAME、INDEX\_TYPE、TABLE\_OWNER 和 TABLE\_NAME。其中，INDEX\_NAME 表示索引名称和 TYPE 列表示索引的类型, 如B树(BTREE)、位图(BITMAP)等。而 TABLE\_OWNER 则是指表所属的用户，TABLE\_NAME 则代表该索引所属的表的名称。
> 
> **注意：**
> 
> 虽然 ALL\_INDEXES 视图提供了大量有用的信息，但它仅限于当前用户可以访问到的对象，并且所有列的含义在实际使用中可能因不同的数据库版本而有所变化。如果想要查询其他用户或所有者的索引信息，则需要使用相应的权限或查询其他数据字典表或视图。

### 5. 查询表列的详细信息 ###

SELECT *
    FROM ALL_TAB_COLUMNS
    WHERE OWNER = 'owner'
    AND TABLE_NAME = 'table_name';

**注释：**

ALL\_TAB\_COLUMNS 视图返回了当前用户和公共对象(如果允许访问)中的所有表及其包含的列信息。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  TABLE\_NAME： 表格名称（主要）
2.  COLUMN\_NAME： 列的名称（主要）
3.  DATA\_TYPE： 数据类型
4.  DATA\_LENGTH： 数据的长度（单位为字节）
5.  DATA\_PRECISION： 数值精度
6.  DATA\_SCALE： 数值小数位数
7.  NULLABLE： 列是否可空
8.  COLUMN\_ID： 序列号
9.  DEFAULT\_LENGTH： 默认长度
10. DEFAULT\_VALUE： 默认值

> 特别注意：
> 
> ALL\_TAB\_COLUMNS 还包括其他一些与列信息无关的列，如OWNER（表所有者）、TABLESPACE\_NAME（表空间名称）等等。并且一张表可能有多个列，并且每个字段值类型完全不一样。
> 
> 主要关注信息列为 TABLE\_NAME, COLUMN\_NAME, DATA\_TYPE, DATA\_LENGTH 和 NULLABLE，这些列给出了表和列的基本信息，例如表和列的名称、数据类型、长度和是否 nullable 等等。
> 
> 在实际操作中，通常我们只关注特定表或一组相关表的列。

### 6. 查询序列信息 ###

SELECT *
    FROM ALL_SEQUENCES
    WHERE SEQUENCE_OWNER = 'owner'
    AND SEQUENCE_NAME = 'sequence_name';

**注释：**

ALL\_SEQUENCES 是 Oracle 数据库系统的一个元数据视图，用于显示数据库中所有序列的信息。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  SEQUENCE\_NAME： 序列的名称（主要）
2.  MIN\_VALUE： 序列值的最小值
3.  MAX\_VALUE： 序列值的最大值
4.  INCREMENT\_BY： 序列的递增量（开始值为最小值)
5.  CYCLE\_FLAG： 是否允许循环序列，若允许则再到max\_value时从min\_value重新开始
6.  ORDER\_FLAG： 是否按顺序生成序列号
7.  CACHE\_SIZE： 缓存大小
8.  LAST\_NUMBER： 上次使用后更新的计数器

> **注意：**
> 
> ALL\_SEQUENCES 还包括其他一些与序列信息无关的列，如OWNER（序列所有者）等等。
> 
> 主要关注信息列为 SEQUENCE\_NAME、MIN\_VALUE、MAX\_VALUE 和 INCREMENT\_BY，这些列给出了序列的基本信息，例如序列的名称、最小值、最大值和递增量等等。
> 
> 在实际操作中，如果我们需要使用序列来生成唯一标识符或其他类型的自增值，则可以通过查询 ALL\_SEQUENCES 视图来查找特定序列对象的详细信息。同时，也可以使用其他相关的系统视图来获取更多序列信息以及关联表、视图或者存储过程信息。

### 7. 查询当前用户可见的视图 ###

SELECT * FROM USER_VIEWS;

**注释：**

USER\_VIEWS 是 Oracle 数据库系统的一个元数据视图，用于显示当前用户拥有的视图信息。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  VIEW\_NAME： 视图的名称（主要）
2.  TEXT： 创建视图的 SQL 语句
3.  TYPE\_TEXT\_LENGTH： 存储创建视图 SQL 语句的长度
4.  SUPERVIEW\_NAME： 若该视图存在封装，则是其包含于哪个封装的视图名称
5.  CONTAINER\_DATA\_OBJECT： 视图的类型

> **注意：**
> 
> USER\_VIEWS 还包括其他一些与视图信息无关的列，如OWNER（视图所有者）等等。
> 
> 主要关注信息列为 VIEW\_NAME 和 TEXT，这些列给出了视图的基本信息，例如视图的名称以及创建视图时使用的 SQL 文本。通过这些信息，可以深入了解视图的结构和逻辑。
> 
> 在实际操作中，如果需要对一个或多个表进行复杂的查询，但是不希望直接访问这些表，则可以使用视图来实现。通过查询 USER\_VIEWS 视图，可以查看当前用户定义的所有视图，从而更好地理解和使用这些对象。

### 8. 查询数据库中更改的所有日志记录 ###

SELECT SCN, TIMESTAMP, OPERATION, TABLE_NAME, SQL_REDO, STATUS
    FROM V$LOGMNR_CONTENTS
    WHERE SEG_OWNER = 'owner'
    AND SEG_NAME = 'table_name';

**注释：**

查询 Redo Log （重做日志）中的信息，用于支持数据恢复和更改跟踪。

以下是一些相关列名和说明以及主要关注信息列的注释：

1.  SCN：System Change Number标识每次提交事务时的序列号（主要）
2.  TIMESTAMP：时间戳
3.  OPERATION：执行操作 INSERT、UPDATE 或 DELETE（主要）
4.  TABLE\_NAME：表的名称
5.  SQL\_REDO：重做 SQL 语句
6.  STATUS：当前行的状态，有可能是 ACTIVE、COMMITTED 或者 ROLLBACK 等。

> **注意：**
> 
> V$LOGMNR\_CONTENTS 视图中的数据来自于 Redo Log 文件，这些文件记录了数据库中发生的所有更改，并在恢复数据时使用。因此，这个视图主要用于实现数据恢复、数据审计和数据极限可怕情况下的分析等操作。
> 
> 主要关注信息列为 SCN、OPERATION 和 SQL\_REDO，这些列给出了重做日志文件的基本信息，例如系统变化序列号、执行的操作类型，以及执行操作所对应的重做 SQL 语句等等。
> 
> 在实际操作中，可以使用该语句来了解系统的修改历史并确定数据库的安全性。同时，通过对 V$LOGMNR\_CONTENTS 视图进行高级筛选和分析，可以获取更准确、更完整的信息，并快速定位到故障或异常情况。

### 9. 查询正在使用的会话信息 ###

SELECT SID, SERIAL#, USERNAME, MACHINE, PROGRAM, SQL_ID
    FROM V$SESSION
    WHERE STATUS = 'ACTIVE';
    
    --显示所有会话和连接信息
    SELECT * FROM V$SESSION;
    
    --查询当前连接的用户数以及它们的状态
    SELECT STATUS, COUNT(*) AS NUM_SESSIONS
    FROM V$SESSION
    GROUP BY STATUS;

**注释：**

查询当前所有用户会话详细信息。

> **会话定义理解**：
> 
> 会话（Session）是指连接至 Oracle 数据库系统的用户进程和数据库实例之间的一种交互方式。当*用户通过应用程序或者其他工具以某个用户名与密码登录到 Oracle 系统*时，就创建了一个会话。
> 
> 在一个会话中，用户可以执行 SQL 语句、访问和修改数据库对象、进行事务控制等各种操作。在会话开始时，会分配一个唯一的 Session ID（SID），该 ID 可以用于标识和跟踪该会话。每个会话都有自己的私有 SQL 区域和临时表空间等资源，这些资源在会话关闭后被释放。
> 
> **会话作用如下**：
> 
>  *  1. 提供并发性：Oracle 可以同时支持多个会话，使得多个用户可以以安全、高效的方式共享数据库资源。
>  *  2. 实现事务控制：会话提供了启动、提交和回滚事务的机制，协助保证数据库数据的完整性和一致性。
>  *  3. 存储会话状态信息：会话记住了对数据库所做的所有更改，并根据它们自动更新其内部状态。例如，在查询结果集时，它可以知道当前位置和下一行可以读取的位置。
>  *  4. 提供错误处理机制：如果会话出现意外故障（如断电、网络问题等），Oracle 可以保证在恢复后重新连接并恢复该会话。
> 
> 会话是提供了很多关键功能和操作，为用户提供了安全、可靠、方便等一系列优秀的交互体验。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  SID：会话 ID（主要）
2.  SERIAL\#：序列号
3.  USERNAME：用户名
4.  STATUS：会话状态 ACTIVE、INACTIVE、KILLED 等等（主要）
5.  OSUSER：操作系统用户名
6.  MACHINE：客户端机器名
7.  TERMINAL：终端名
8.  PROGRAM： 已打开的程序名
9.  LOGON\_TIME：登录时间戳
10. LAST\_CALL\_ET：最后一个调用结束的时间，以秒为单位
11. SQL\_ID：正在执行或最近执行完的 SQL 语句的 SQL\_ID

> **注意：**
> 
> V$SESSION 视图中包含了当前会话的所有信息，还包括了与应用程序相关的信息。同时，在一个 Oracle 数据库实例中可以有多个会话同时存在，因此，这个视图可以方便您监视计算机上的所有活动进程，以便随时调整和优化数据库系统的行为。
> 
> 主要关注信息列为 SID、STATUS 和 SQL\_ID，这些列给出了会话的基本信息，例如会话 ID、会话状态以及当前正在执行的 SQL 语句的 SQL\_ID。
> 
> 在实际操作中，需要监视和管理数据库系统会话时，可以通过执行该语句来了解当前数据库的所有活动会话，并检查会话 ID、用户名、程序名等信息以便进一步处理。

### 10. 查询SGA缓存区的大小和使用情况 ###

SELECT NAME, BYTES, BYTES/1024/1024 AS MB
    FROM V$SGAINFO;

**注释：**

查询当前数据库系统的 SGA（系统全局区）信息。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  NAME：SGA 组件的名称（主要）
2.  BYTES：SGA 组件当前的大小，以字节为单位（主要）

> **注意：**
> 
> SGA 是用来存储数据库实例在内存中管理的数据结构，包括共享 SQL 区、数据缓冲池区、重做日志缓冲区等等。这些区域都位于用户进程和 Oracle 数据库实例之间，可用于共享典型的 Oracle 数据库资源。
> 
> 主要关注信息列为 NAME 和 BYTES，这些列给出了 SGA 组件的名称和当前大小值。通过使用这些信息，可以检查数据库系统的当前内存使用情况，确保数据库系统正常运行，并优化各种活动以提高性能。
> 
> 在实际操作中，可以执行该语句来获取 SGA 的详细信息，例如检查各个 SGA 组件（如共享池、缓存区等）的当前大小值，然后据此调整数据库系统内存资源并优化其性能。

### 11. 查询表所占用的空间 ###

SELECT TABLE_NAME, SUM(BYTES) / 1024 / 1024 AS MB
    FROM USER_EXTENTS
    GROUP BY TABLE_NAME;

**注释：**

查询当前用户（即当前登录用户）的所有表的 Extent（扩展）详细信息。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  TABLE\_NAME：表的名称（主要）
2.  PARTITION\_NAME：分区名称
3.  SEGMENT\_NAME：在其上定位段（主要）
4.  BYTES：分配给该段的字节数
5.  BLOCKS：分配给该段的块数

> **注意：**
> 
> Extent 是 Oracle 中存储数据的最小物理单位，在一个表空间中包含若干个 Extent。在数据库中创建表时，Oracle 会通过合并几个 Extent，从而创建出新表的 Segment，用于存储表对象的实际数据。
> 
> 主要关注信息列为 TABLE\_NAME、SEGMENT\_NAME、BYTES 和 BLOCKS 等列，这些列给出了有关每个表分配给其的 Extents 的详细信息，例如分配给每个表的物理存储大小值，所使用的 Extent 数量以及相关位置等。
> 
> 在实际操作中，可以执行该语句来获取数据库系统分配给该用户的每个表的详细信息，协助掌握表对象的实际存储情况，包括分配给每个表的存储空间大小、所使用的 Extent 数量等等。同时，这也对监控和调整数据库资源分配非常有用，例如，检查我们的表是否需要更多或更少的物理存储资源（通过增加/减少 Extent 设置）等操作。

### 12. 查询当前活动会话所执行的SQL语句 ###

SELECT A.SQL_ID, B.SQL_TEXT 
    FROM V$SESSION A, V$SQL B 
    WHERE A.STATUS = 'ACTIVE' AND A.SQL_ID = B.SQL_ID;

**注释：**

指定要查询的视图 VSESSION 和 VSESSION和VSQL，通过将 SQL\_ID 列映射到它们之间连接会话和应用程序 SQL 的相关信息，从而检索所有活动会话的详细信息。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  SESSION\_ID：Session 的唯一标识符
2.  SERIAL\#：由 Oracle 数据库分配给每个 session 的唯一序列号
3.  USERNAME：当前会话所属的用户名
4.  STATUS：当前会话状态
5.  SQL\_ID：与会话关联的 SQL\_ID
6.  SQL\_TEXT：标识 SQL 查询文本

> 注意：
> 
> 这个查询主要是为了查找当前数据库中正在进行活跃操作的会话，并以此来分析用户、应用程序和系统资源之间的交互。同时，它还提供了有关正在运行的查询以及它们所消耗的资源的有用信息，如运行时间、 I/O 消费、CPU 消费等。
> 
> 主要关注信息列为 SESSION\_ID，USERNAME，STATUS，SQL\_ID 和 SQL\_TEXT 等列，这些列提供有关当前会话和相关 SQL 查询的详细信息。这些数据可帮助 DBA 和开发人员诊断当前数据库性能问题，确定正在进行的查询是否正在阻塞其它操作或滥用资源等信息。
> 
> 在实际设置中，经常使用 VSESSION 视图来检查与当前会话相关联的活动连接数、打开文件计数、工作负载概要等。而 VSESSION视图来检查与当前会话相关联的活动连接数、打开文件计数、工作负载概要等。而VSQL 经常用于检测并分析当前运行的 SQL 语句，并为优化和调整系统资源提供数组支持。

### 13. 查询索引是否被优化使用（如是否有大量回表操作） ###

SELECT INDEX_NAME, COUNT(*) OPERATIONS
    FROM V$OBJECT_USAGE
    WHERE OBJECT_TYPE = 'INDEX'
    GROUP BY INDEX_NAME
    HAVING COUNT(*) > 1000;

**注释：**

指定要查询的视图V$OBJECT\_USAGE，该视图跟踪了一个对象最近被访问的时间和使用计数的相关信息。它包含了跟踪表、索引、分区和闪回等数据库对象的从创建到最近出现使用活动过程中的统计信息和历史数据。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  OBJECT\_TYPE：对象类型（主要）
2.  OBJECT\_NAME：对象名称
3.  LAST\_USED：上次访问对象时间
4.  TIMESTAMP\#：发生对象最后一个对用户可见的使用事件的时间戳
5.  REFERENCE\_COUNT：对象被其他对象引用的次数计数器

> **注意：**
> 
> 这个查询可以提供有关数据库对象（表、索引、分区、闪回等）上次访问的时间、使用频率，直到它们被删除前的历史记录等详细信息。因此，可以用来跟踪数据库活动和性能问题，并为数据库对象的审计和优化活动做出准确的决策。
> 
> 主要关注信息列为 OBJECT\_TYPE，OBJECT\_NAME，LAST\_USED，REFERENCE\_COUNT 等列，而其中最重要的信息一般是对象类型和名称（OBJECT\_TYPE 和 OBJECT\_NAME 列）。这些数据可以帮助 DBA 确定哪些对象是最常访问的，哪些索引可能可以优化等信息。
> 
> 在实际应用中，基于 V$OBJECT\_USAGE 视图的查询语句可以与其他性能调整和优化技术（如 SQL 规则、图形执行计划等）结合使用，以提高数据库系统性能并更准确地衡量系统资源消耗。

### 14. 查询所有库对象的版本号 ###

SELECT OWNER, OBJECT_NAME, STATUS, EDITION_NAME 
    FROM ALL_OBJECTS 
    WHERE STATUS = 'INVALID';

**注释：**

该视图列出了所有当前连接到数据库实例的对象、行类型以及其详细信息。

以下是一些相关列名和说明以及主要关注信息列的注释：

1.  OWNER：对象拥有者
2.  OBJECT\_NAME：对象名称（主要）
3.  CREATED/LAST\_DDL\_TIME：创建和最后修改时间
4.  STATUS：对象状态
5.  EDITION\_NAME：版别名称（12c+）

> 注意：
> 
> ALL\_OBJECTS 视图用于提供与所有对象相关的元数据和统计信息。通过该视图，可以获取对象的基本信息（如对象类型、拥有者等）、对象的状态（有效、无效等）、创建、修改时间和分区信息等内容。
> 
> 主要关注信息列为 OBJECT\_NAME 等列，这些列提供对象的名称。OBJECT\_NAME 列通常是 DBA、开发人员寻找功能实现点时最重要的信息之一。
> 
> 在实际使用中，此查询可帮助 DBA 确认哪些对象已经过时或未使用；同时，它也可以用来查看当前系统资源的状态，例如检查某个索引在多少表中被使用、某个数据库程序仍在运行等情况。

### 15. 查询归档日志和序列的使用情况 ###

SELECT ARCH.THREAD#, SEQUENCE#, BLOCK#, 
             TO_CHAR(FIRST_TIME, 'yyyy-mm-dd hh24:mi:ss') AS CREATE_DATE
    FROM V$ARCHIVED_LOG ARCH, V$LOG_HISTORY HIST
    WHERE ARCH.ARCHIVED = 'YES' 
    AND ARCH.THREAD# = HIST.THREAD# AND ARCH.SEQUENCE# = HIST.SEQUENCE#
    ORDER BY ARCH.THREAD#, ARCH.SEQUENCE#;

以下是一些相关列名和说明以及主要关注信息列的注释：

1.  THREAD\#：表示线程号，通常与实例编号相同
2.  SEQUENCE\#：表示日志文件的序列号，用途是为了防止人工操作导致失误或意外修改数据库而产生的恢复管理控制措施（主要）
3.  BLOCK\#：表示记录在哪个数据块中
4.  CREATE\_DATE：归档日志创建时间，方便归档管理人员查找特定的时间段。

> 此查询提供了在数据库中使用传统日志机制来进行自动备份和恢复时，监视已经归档日志的有效性和完整性。
> 
> 主要关注信息列为 SEQUENCE\# 列，这一列提供有用的序列编号，通常用于执行备份和恢复操作，并且应该监视并检查归档日志文件的状态以跟踪它们是否已成功存档。
> 
> 在实际使用中，DBA 可以根据归档日志记录监视和分析数据库的运行状况，以及进行数据库故障恢复等活动。

### 16. 查询不可用的约束和关系表 ###

SELECT CONSTRAINT_NAME, TABLE_NAME, STATUS
    FROM USER_CONSTRAINTS
    WHERE STATUS = 'DISABLED';

**注释：**

获取当前用户拥有的所有被禁用的约束（包括主键、唯一键、外键和检查约束）。

以下是一些相关列名和说明以及主要关注信息列的注释：

1.  CONSTRAINT\_NAME：表示约束名称，用于标识表格中的指定约束。
2.  TABLE\_NAME：表示应用约束的表格名称。
3.  STATUS：表示当前约束的状态。如果其值为 DISABLED，则表示当前约束被禁用。

> 该查询列出了当前用户全部被禁用的约束列表，对于开发人员和 DBA 非常有用，可帮助他们快速找到无效的约束，并进行修复。
> 
> 主要关注信息列为 CONSTRAINT\_NAME、TABLE\_NAME 和 STATUS 等字段。
> 
> 在实际应用中，例如在数据库维护或升级过程中，该 SQL 查询可以帮助 DBA 更好地搜索特定的约束是否已经被禁用，从而更迅速地定位和修复数据错误或完整性问题。

### 17. 查看数据库对象的依赖关系 ###

SELECT OBJECT_NAME, REFERENCED_OWNER, REFERENCED_NAME
    FROM USER_DEPENDENCIES
    WHERE REFERENCED_NAME = 'table_name'
    AND REFERENCED_TYPE = 'TABLE';

**注释：**

查询依赖于特定表格（table\_name）的关联对象（包括过程、函数、触发器等）。

> **注意：**
> 
> 查询 USER\_DEPENDENCIES 视图可以打印出数据库中一个对象的依赖层次结构，以及该对象对哪些程序（存储过程、触发器，视图等）有直接或间接的依赖关系。

以下是一些相关列名和说明以及主要关注信息列的注释：

1.  OBJECT\_NAME：被引用对象的名称；
2.  REFERENCED\_OWNER：所属用户的名称；
3.  REFERENCED\_NAME：所引用对象的名称； 通过这三个字段来查找引用了 "table\_name" 表格的依赖项。

> 该查询可帮助开发人员快速检索特定表格的依赖关系，以便在修改表格结构时更加安全、高效地进行开发或维护工作。

### 18.查询自上次启动后运行的SQL语句计数器 ###

SELECT VALUE AS COUNTER 
    FROM V$SYSSTAT 
    WHERE NAME = 'parse count (total)';

**注释：**

查询系统当前运行状态下的解析次数（total parse count）。解析是 SQL 查询语句实际执行之前所做的过程，主要完成语法检查以及生成与查询语句相关的执行计划。

以下是一些相关列名和说明以及主要关注信息列的注释：

1.  STATISTIC\#：每个计数器的唯一 ID 号。
2.  NAME：用于描述每个计数器的名称。
3.  VALUE：表示所选计数器的当前值。

> 该查询可以帮助 DBA 或开发人员追踪数据库解析次数的变化趋势，以便收集和分析关于数据库负载、优化、调整和升级等方面的信息，并决策是否进行必要的性能优化或其他操作。

### 19.查询正在使用的Redo日志文件和组信息 ###

SELECT GROUP#, THREAD#, SEQUENCE#, MEMBERS, STATUS 
    FROM V$LOG;

**注释：**

查询当前可用的重做日志组（redo log groups）信息，该视图显示了数据库实例中所有日志文件的信息，包括日志序列号、大小、状态等，无需添加WHERE条件。重做日志组是 Oracle 数据库关键特性之一，它保存了正在执行的事务的版本，以支持故障恢复并保障数据的完整性。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  GROUP\#：返回日志组 ID 号，按照组号顺序递增。
2.  THREAD\#：反映重做日志所属的进程 ID 号。
3.  SEQUENCE\#：表示当前日志组内对应的重做日志序列号，每插入一个新记录增加一次。
4.  BYTES：表示当前日志文件大小（单位为字节），该值可以通过 ALTER DATABASE 命令修改。

> 该查询可帮助 DBA 或系统管理员监控日志文件的状态，并计划和安排维护任务，例如清理旧的或过期的日志文件以确保不会耗尽磁盘空间。

### 20.查询当前用户使用的默认表空间 ###

SELECT DEFAULT_TABLESPACE 
    FROM USER_USERS 
    WHERE USERNAME = 'username';

**注释：**

1.  该视图包含有关当前用户及其相关属性的信息。
2.  无需添加WHERE条件。

> 表空间是 Oracle 数据库中数据存储的逻辑结构，它用于组织、组合和管理表、索引、扩展的数据以及其他对象。数据库可以包含多个表空间，每个表空间可以包含一到多个数据文件。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  USERNAME：表示当前用户名。
2.  USER\_ID：当前用户 ID 号。
3.  DEFAULT\_TABLESPACE：表示当前用户的默认表空间。
4.  ACCOUNT\_STATUS：反映当前账户的状态信息，例如是否被锁定、启用或禁用等。
5.  CREATED：表示当前用户账号创建的日期。

> 该查询可帮助 DBA 或开发人员更好地了解数据库的结构和用户配置，并通过修改默认表空间和其他配置信息来更好地管控数据库资源，以达到更高效、更灵活和更安全的数据库操作和管理。

### 21.查询Oracle数据库实例的版本号 ###

SELECT * 
    FROM V$VERSION;

**注释：**

该视图显示了某个 Oracle 数据库实例运行时版本信息以及配置参数等，例如操作系统版本、Oracle 版本号、网络组件版本、数据库字符集等等。查询结果中可能包含多条记录，每条记录代表一段或所有组件的版本信息。

以下是我用Oracle11g查询后一些相关列名和说明以及主要关注信息列的注释：

1.  BANNER：反映 Oracle 数据库版本具体信息，例如发布日期、版本号、补丁等级、操作平台等等。
2.  CON\_ID：表示当前连接对应的容器 ID 号。
3.  VERSION：表示当前 Oracle 实例版本号，通常由主版本号（Major Version）、次版本号（Minor Version）和修正版本号（Patch Version）构成。

> 在实际调试中，V$VERSION 视图是 Oracle 数据库关键性能数据跟踪和问题诊断的重要利器，在收集故障的数据过程中也可以较好地隔离出数据库版本的因素影响，从而更加有效地解决各种文件系统、安全性、兼容性等方面的问题。

下图就是我的数据库版本信息：

![e3d784d25b0e493b8371df8707b3e530.png][]

> 从上图可清楚以下信息：
> 
>  *  "Enterprise Edition"表示适用于中大型企业或复杂的 IT 应用场景；
>  *  "Release 11.2.0.4.0" 表示该版本的具体编号号为 11.2.0.4.0，其中第一个数字 "11.2.0" 是主版本号，后四位数字则代表补丁、修正版本号等。
>  *  "64bit Production" 指的是该版本的硬件平台是 64 位 CPU 架构，并且是可用于生产环境中的稳定版本。
>  *  "PL/SQL Release 11.2.0.4.0 - Production"：表示当前执行环境中所用 PL/SQL 的具体版本号为 11.2.0.4.0；"-Production" 表示该版本是一个正式版本，适用于生产环境。
>  *  "CORE 11.2.0.4.0 Production"：指基本内核模块的版本号是 11.2.0.4.0；"Production" 表示该版本是一个正式版本，适用于生产环境。
>  *  "TNS for 64-bit Windows: Version 11.2.0.4.0 - Production"：TNS 是 Oracle 连接数据库的网络协议，这里是指 TNS （Transparent Network Substrate）服务版本为 11.2.0.4.0，并且是一个正式版本，适用于生产环境。同时，它也清楚地说明了该 TNS 组件是在 64 位 Windows 环境中使用的。
>  *  "NLSRTL Version 11.2.0.4.0 - Production"：Oracle NLSRTL 是国际化支持模块，提供了多种不同语言、字符集和编码转换等功能，这里是指其版本号为 11.2.0.4.0，并且是一个正式版本，适用于生产环境。
> 
> 总结可得：这些信息可以用于向管理员或者开发人员提供有关当前 Oracle 应用程序运行环境的版本和部署信息。

这些SQL语句可以在多个场景中为DBA和开发人员提供有用的信息，例如监测会话的性能或查找问题会话，可以帮助DBA和开发人员更好地了解数据库系统，工具以及提供更有效的管理操作，也可以帮助DBA和开发人员更深入地了解Oracle数据字典视图 。

这些例子只是Oracle数据字典语句中的冰山一角，还有很多实用的查询语句帮助DBA和开发人员了解系统解决问题，我在这篇文章就不再一一举例啦！！！

[e3d784d25b0e493b8371df8707b3e530.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/c35580db543c49d4bfde1b6cb479b082.png