了解Oracle中的Lob分段(SYS_LOB)? [英] Understanding Lob segments (SYS_LOB) in oracle?

问题描述

大多数表(如果不是全部)都在其中包含"blob"字段.存储用户操作日志的表之一现已增长到8 GB(约500万条记录).

Most of the tables (if not all) have a "blob" field in it. One of the table that stores the Logs of the user's action has now grown to 8 GB (about 5 million records).

并且我们的DBA已经看到此模式现在正在成倍地占用空间.我们进行了调查，发现其中有一个表(SYS_LOB)占用了约116GB的140GB数据库.

And our DBA has seen that this schema is now eating space exponentially. We investigated a bit and found out that there is one of the tables with (SYS_LOB) which takes around 116GB of 140GB database.

我们的数据库管理员告诉我们，该表与保存用户操作日志(8GB)的表相关.

Our DBA has told us that this table is related to the table that saves the logs of user actions (which is 8GB)

有人知道此SYS_LOB表做什么吗? 将实际的blob保存在我们创建的表中，还是oracle将这些blob实际存储在另一个表中(如果是，则SYS_LOB将是该表)?

Does anyone know what this SYS_LOB tables does? Is the actual blob saved in the table we created OR oracle actually stores these blob this in a different table (if yes, then SYS_LOB would be that table)?

推荐答案

Oracle数据库中没有表SYS_LOB(至少，基本数据库安装中没有这样的表.存在一个表) DBA_LOBS显示有关数据库中所有LOB列的信息，但实际上不存储任何LOB数据，仅存储元数据，数据库中的实际LOB段具有系统生成的名称，格式为SYS_LOB identifier $$.

There is no table SYS_LOB in the Oracle database (at least, there is no such table that is part of a basic database install. There is a table DBA_LOBS that displays information about all the LOB columns in the database, but it doesn't actually store any LOB data, just metadata. The actual LOB segments in the database have system-generated names that take the form SYS_LOBidentifier$$.

我的猜测是您的DBA已经标识了一个名为SYS_LOB identifier $$的段，该段正在消耗116 GB的空间.假设这是正确的，您可以使用DBA_LOBS表(即

My guess is that your DBA has identified a segment named SYS_LOBidentifier$$ that is consuming 116 GB of space. Assuming that is correct, you can find out what column of what table that LOB column maps to using the DBA_LOBS table, i.e.

SELECT owner, table_name, column_name
  FROM dba_lobs
 WHERE segment_name = 'SYS_LOB<<identifier>>$$'

这篇关于了解Oracle中的Lob分段(SYS_LOB)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！