LOB列之-如何降低高水位，及需要关注点

已于 2023-06-21 11:42:58 修改
阅读量305 收藏 1
点赞数
文章标签： 数据库 sql oracle
于 2023-05-18 18:16:07 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yejb7456/article/details/130730065
版权

最近客户反馈他们delete了包含LOB列的表，通过shrink space 无法降低LOB列的高水位，无法回收空间，进行了测试。

环境说明：
1、数据库版本11.2.0.4.0
2、DB_SECUREFILE=PERMITTED(允许创建SecureFiles（默认值）)

测试目的：

通过对表进行delete操作后，想通过move和shrink降低表和LOB列的高水位。

结论：
1、当LOB类型为SecureFile时，通过对表进行shrink 操作，表高水位可以降低，但是LOB列高水位无法降低。
2、当LOB类型为BasicFiles时，通过对表进行shrink 操作，表高水位可以降低，LOB列高水位也可以降低。
3、当LOB类型为SecureFile时，通过对表进行move 操作，表高水位可以降低，但是LOB列高水位无法降低,需要通过MOVE LOB列进行操作降低。
4、当LOB类型为BasicFiles时，通过对表进行move 操作，表高水位可以降低，但是LOB列高水位无法降低,需要通过MOVE LOB列进行操作降低。
5、不管怎么样，LOB列的高水位无法降低，可以通过单独通过move lob列来进行降低高水位。

6、在对LOB列单独MOVE的时候，LOB列的索引不会失效，但是非LOB列的索引会失效！生产上一定要慎重，同时如果做的话，别忘记观察索引状态，及时rebuild索引。

以下是详细的测试过程：
=====================    先进行shrink操作测试       ================
1、创建SECUREFILES 和 BASICFILES类型LOB表

--创建SECUREFILE类型LOB表
CREATE TABLE SECUREFILES_TAB (
     id         NUMBER,
     clob_data  CLOB
   )
   LOB(clob_data) STORE AS SECUREFILE;
   
--创建BASICFILE类型LOB表（不加LOB(clob_data) STORE AS BASICFILE 这段默认就是BASICFILE类型）
CREATE TABLE BASICFILES_TAB(
     id         NUMBER,
     clob_data  CLOB
   );


2、创建索引（观察索引失效情况）,生产上，可以先考虑插入数据，再创建索引，提升插入效率
create index ind_SECUREFILES_TAB_id on SECUREFILES_TAB(id);
create index ind_BASICFILES_TAB_id on BASICFILES_TAB(id);

SQL> create index ind_SECUREFILES_TAB_id on SECUREFILES_TAB(id);
create index ind_BASICFILES_TAB_id on BASICFILES_TAB(id);

Index created.

SQL> 
Index created.

SQL> 


3、插入数据

SQL> DECLARE
  2      l_clob CLOB := RPAD('X',10000, 'X');
  3    BEGIN
  4  for i IN 1..1000 LOOP
  5        INSERT INTO SECUREFILES_TAB VALUES (i, l_clob);
  6      END LOOP;
  7      COMMIT;
  8  for i IN 1..1000 LOOP
  9        INSERT INTO BASICFILES_TAB VALUES (i, l_clob);
 10      END LOOP;
 11      COMMIT;
 12     END;
 13     /  

PL/SQL procedure successfully completed.

4、检查数据
SQL> SQL> 
SQL> select count(*) from SECUREFILES_TAB;

  COUNT(*)
----------
      1000

SQL> select count(*) from BASICFILES_TAB;

  COUNT(*)
----------
      1000  

5、检查表对应的LOB列

SQL> set linesize 300
col COLUMN_NAME for a20

6、检查索引状态，都是有效的
select owner,index_name,table_name,status from dba_indexes where table_name in ('SECUREFILES_TAB','BASICFILES_TAB');

7、查看表和LOB列大小
SQL> SELECT segment_name, bytes
  2  FROM   dba_segments
  3  WHERE  segment_name IN ('SECUREFILES_TAB','BASICFILES_TAB','SYS_LOB0000028976C00002$$','SYS_LOB0000028973C00002$$');

 
8、对表进行delete 操作
SQL>

 delete from SECUREFILES_TAB where id>10;
delete from BASICFILES_TAB where id>10;

990 rows deleted.

SQL> 
990 rows deleted.

9、delete 后再次查看表和LOB列大小，没有变化
SQL> SELECT segment_name, bytes
  2  FROM   user_segments
  3  WHERE  segment_name IN ('SECUREFILES_TAB','BASICFILES_TAB','SYS_LOB0000028976C00002$$','SYS_LOB0000028973C00002$$');

 
10、对表进行shrink操作

SQL>

ALTER TABLE YJB.SECUREFILES_TAB ENABLE ROW MOVEMENT;
ALTER TABLE YJB.SECUREFILES_TAB SHRINK SPACE CASCADE;

ALTER TABLE YJB.BASICFILES_TAB ENABLE ROW MOVEMENT;
ALTER TABLE YJB.BASICFILES_TAB SHRINK SPACE CASCADE;

Table altered.

SQL> 
Table altered.

SQL> SQL> 
Table altered.

SQL> 

Table altered.


11、查看对表做shrink后，表和LOB列大小

发现
   1、表的大小都变小了，即高水位降低了
   2、BASICFILE的LOB列的大小也变小了，但是LOB列类型为SECUREFILE的LOB，大小保持不变。
   
SQL> SELECT segment_name, bytes
  2  FROM   dba_segments
  3  WHERE  segment_name IN ('SECUREFILES_TAB','BASICFILES_TAB','SYS_LOB0000028976C00002$$','SYS_LOB0000028973C00002$$');

 
12、查看索引状态，通过shrink操作后，都是有效的，主要是由于shrink会自动维护索引。
SQL> select owner,index_name,table_name,status from dba_indexes where table_name in ('SECUREFILES_TAB','BASICFILES_TAB');


13、对无法降低高水位的LOB列进行单独MOVE操作
SQL> alter table SECUREFILES_TAB move lob(clob_data) store as  (tablespace users);

Table altered.

=======

14、再次查看LOB列的高水位情况，发现已经下降，同时回收了空间
SQL> SELECT segment_name, bytes
  2  FROM   user_segments
  3  WHERE  segment_name IN ('SECUREFILES_TAB','BASICFILES_TAB','SYS_LOB0000028890C00002$$','SYS_LOB0000028893C00002$$');

15、再次检查索引，LOB列的索引状态正常，但是发现非LOB列的索引反而变成：失效状态，这个坑，大家生产上，还是要特别注意。

 16、通过rebuild 进行重建后，id列索引恢复正常。

SQL> alter index IND_SECUREFILES_TAB_ID rebuild;

=====================    然后进行MOVE操作测试       ================

1、创建SECUREFILES 和 BASICFILES类型LOB表

创建SECUREFILE类型LOB表
SQL> CREATE TABLE SECUREFILES_TAB (
  2       id         NUMBER,
  3       clob_data  CLOB
  4     )
  5     LOB(clob_data) STORE AS SECUREFILE;
   
--创建BASICFILE类型LOB表
CREATE TABLE BASICFILES_TAB(
     id         NUMBER,
     clob_data  CLOB
   )
   LOB(clob_data) STORE AS BASICFILE;

Table created.

SQL> SQL> SQL>   2    3    4    5  
Table created.


 

2、创建索引
create index ind_SECUREFILES_TAB_id on SECUREFILES_TAB(id);
create index ind_BASICFILES_TAB_id on BASICFILES_TAB(id);

3、插入数据。

SQL> SQL> SQL> SQL> DECLARE
  2        l_clob CLOB := RPAD('X',10000, 'X');
  3      BEGIN
  4    for i IN 1..1000 LOOP
  5          INSERT INTO SECUREFILES_TAB VALUES (i, l_clob);
  6        END LOOP;
  7        COMMIT;
  8    for i IN 1..1000 LOOP
  9          INSERT INTO BASICFILES_TAB VALUES (i, l_clob);
 10        END LOOP;
 11        COMMIT;
 12       END;
 13       /  

PL/SQL procedure successfully completed.

4、查看数据量。

SQL> select count(*) from SECUREFILES_TAB;
select count(*) from BASICFILES_TAB;

  COUNT(*)
----------
      1000

SQL> 
  COUNT(*)
----------
      1000

SQL> 

5、查看表的LOB列

SQL> set linesize 300
col COLUMN_NAME for a20
select owner,table_name,column_name,segment_name,INDEX_NAME,tablespace_name,COMPRESSION,DEDUPLICATION,SECUREFILE from dba_lobs where table_name in ('SECUREFILES_TAB','BASICFILES_TAB');

6、检查索引状态，都是有效的
SQL> SQL> select owner,index_name,table_name,status from dba_indexes where table_name in ('SECUREFILES_TAB','BASICFILES_TAB');

7、检查表、索引大小

SELECT segment_name, bytes
FROM   dba_segments
WHERE  segment_name IN ('SECUREFILES_TAB','BASICFILES_TAB','SYS_LOB0000029137C00002$$','SYS_LOB0000029134C00002$$');

8、对表进行delete 操作,表和LOB高水位都没有下降。

delete from SECUREFILES_TAB where id>10;
delete from BASICFILES_TAB where id>10;

SELECT segment_name, bytes
FROM   dba_segments
WHERE  segment_name IN ('SECUREFILES_TAB','BASICFILES_TAB','SYS_LOB0000029137C00002$$','SYS_LOB0000029134C00002$$');


9、MOVE表后，表和索引高水位下降，但是两种类型的LOB对象高水位没有降低
SQL> alter table SECUREFILES_TAB move;
alter table BASICFILES_TAB move;


SQL> SELECT segment_name, bytes
FROM   dba_segments
WHERE  segment_name IN ('SECUREFILES_TAB','BASICFILES_TAB','SYS_LOB0000029137C00002$$','SYS_LOB0000029134C00002$$');

 10、LOB列索引未失效，但是非LOB列的索引失效了(shrink这点就比move要强)。
SQL> select owner,index_name,table_name,status from dba_indexes where table_name in ('SECUREFILES_TAB','BASICFILES_TAB');


11、单独对LOB列进行move操作，可以看到LOB列的高水位已经降低
SQL> 
SQL> alter table SECUREFILES_TAB move lob(clob_data) store as  (tablespace users);
alter table BASICFILES_TAB move lob(clob_data) store as  (tablespace users);

SQL> SELECT segment_name, bytes
  2     FROM   dba_segments
  3     WHERE  segment_name IN ('SECUREFILES_TAB','BASICFILES_TAB','SYS_LOB0000028908C00002$$','SYS_LOB0000028911C00002$$','IND_BASICFILES_TAB_ID','IND_SECUREFILES_TAB_ID');

12、最后别忘记把失效的索引rebuild.

jianbo ye
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
Oracle的LOB（CLOB）大字段以及（SYS_LOB$$）清理.txt
04-28
Oracle的LOB（CLOB）大字段以及（SYS_LOB$$）清理.txt
oracle lob 字段清理,Oracle数据库高水位释放——LOB字段空间释放
weixin_39755824的博客
04-05 2313
在Oracle数据库中表随着数据增长，所占磁盘容量也会增长，当表中数据不再需要时，对表进行delete操作，表中代表所占空间的标志-高水位，不会随着数据删除而下降，高水位没有变化，即使删除了数据库，表所占空间大小仍然不变，因此，如果要释放空间，就需要对表进行如下处理：一、如果表所在的表空间是段空间自动管理(即ASSM)，可对表进行shink操作，来释放空闲数据块，降低高水位，shink操作可不用暂...
Oracle数据库高水位释放——LOB字段空间释放
qq_32838955的博客
03-03 2374
在Oracle数据库中表随着数据增长，所占磁盘容量也会增长，当表中数据不再需要时，对表进行delete操作，表中代表所占空间的标志-高水位，不会随着数据删除而下降，高水位没有变化，即使删除了数据库，表所占空间大小仍然不变，因此，如果要释放空间，就需要对表进行如下处理： 一、如果表所在的表空间是段空间自动管理（即ASSM），可对表进行shink操作，来释放空闲数据块，降低高水位，shink操作可不用...
Oracle的LOB（CLOB）大字段以及（SYS_LOB***$$）清理
hcq_lxq的博客
12-06 6881
oracle应用过程中出现表空间不足的时候，为了知道具体哪些表占用空间较大我们可以用如下sql查询： Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name order by Sum(bytes) desc; 查询结过出现SYS_LOB***$$这种Segment_Name，记录着大字段占用空间。我们可用dba_objects表查询映射的是哪一个表的大字段；object_id为LOB后到C之间的那
oracle如何清理sys_lob,清理 oracle lobsegment | 学步园
weixin_30216901的博客
04-12 2238
查询到表空间有个对象占用空间非常大，达200GSQL> select * from (2 select owner,segment_name,tablespace_name,segment_type,bytes/1024/1024 from dba_segments order by 5 desc) where TABLESPACE_NAME='USERS' AND rownum<2...
lob-rest-api-documentation
05-12
LOBOS和eNVenta REST-API 这是LOBOS REST API的文档。 LOBOS Informatik AG可为eNVenta-ERP提供本文档中描述的所有端点 以下LOBOS应用程序在生产中使用此API： LOBOS企业商店 LOBOS Enterprise PWA扫描仪 ...
lob-coding-exercise:用于 Lob 编码挑战的 Node.js 项目
06-24
Lob 编码练习 这是我为 Lob 编码挑战工作的 Node.js 项目的存储库。 依赖关系 运行服务器 node index.js ##Challenge 构建一个 API，将文件转换为缩略图数组（每个页面一个），然后使用每个生成的缩略图的 URL 进行...
lob-stripe-app:使用 Lob 和 Stripe 的 Node.js 应用程序
06-14
lob-stripe-app 使用 Lob 和 Stripe 的 Node.js 应用程序 发展 全局安装包 npm i jscs jshint nodemon - g 启动服务器 nodemon 检查样式 npm run style
lob-ios:适用于Lob API的iOS包装器
05-16
建议用户在其应用程序的服务器端与Lob API集成，因为这比在客户端集成更安全。 Lob.com iOS客户端 Lob.com iOS客户端是 API（ ）的简单包装。 设置项目 要包含iOS客户端，只需将iOS客户端项目放入要使用的项目中...
LoB Analytics-开源
04-27
该软件是用于测量驼背，脊柱前凸和脊柱侧弯的工具。 LoB Analytics是使用Python开发的。
检验lob数据插入是否会插入到高水位线(HWM)以下的空余空间
blockheading的博客
11-21 587
EODA@PROD1> create table lob(a clob)tablespace registration; Table created. EODA@PROD1> insert into lob select rownum from all_objects; 72927 rows created. EODA@PROD1> exec dbms_stats.gather_table
mysql事务(原理)
最新发布
m0_74347016的博客
05-19 519
mysql事务原理
数据库SQL编写规范-SQL书写规范整理（SQL语句书写规范全解-Word原件）
xx_123321456的博客
05-14 304
​​​​​​工作安排任务书，可行性分析报告，立项申请审批表，产品需求规格说明书，需求调研计划，用户需求调查单，用户需求说明书，概要设计说明书，技术解决方案，数据库设计说明书，详细设计说明书，单元测试报告，总体测试计划，单元测试计划，产品集成计划，集成测试报告，集成测试计划，系统测试报告，产品交接验收单，验收报告，验收测试报告，压力测试报告，项目总结报告，立项结项审批表，成本估算表，项目计划，项目周报月报，风险管理计划，质量保证措施，项目甘特图，项目管理工具，操作手册，接口设计文档，软件实施方案，运维方案，安
php centos选择sqlserver的驱动和扩展选择版本的说明
Z.X的博客
05-14 440
2023年2月23日13:41:48首先是php php扩展 驱动 数据库的关系 官方文档说明： https://learn.microsoft.com/zh-cn/sql/connect/php/step-1-configure-development-environment-for-php-development?view=sql-server-2017https://learn.microsoft.com/zh-cn/sql/connect/php/system-requirements-for-th
生产报表工具PI DataLInk 与 行列视之间的区别
2401_83701843的博客
05-17 247
行列视，全名“行列视生产数据应用系统”，行列视品牌所面对的业务范围远远超越了PIDatalink报表工具，他首先是一款本地部署的在线Excel文档管理系统，解决个人报表在线管理问题，其次，她和PIDataLInk一样，也是面向实时数据库报表需求的一套工具，其区别在于行列视品牌兼容国内外大多数实时数据库，也可以同时连接多个数据源，也就是她完全可以替代PI-DataLINk，同时也适用于其他品牌下的实时数据库系统；
MySQL存储过程添加二十万条数据日常练习
mi_mx的博客
05-17 304
【代码】MySQL日常练习。
linux中查找某个文件或文件夹
DN金猿的博客
05-18 238
locate命令将会在整个系统中搜索文件和文件夹，并通过正则表达式匹配以"/folder_name"结尾的路径。这将会在指定的路径（/path/to/search）中递归地列出所有文件和文件夹，并使用grep命令匹配以"/folder_name"结尾的行。该命令将会在指定的路径（/path/to/search）中查找名称为"folder_name"的文件夹。此方法与第2种方法类似，但使用了find命令来递归搜索文件夹，并使用grep命令来匹配以"/folder_name"结尾的行。
MongoDB聚合运算符：$zip
superatom01的博客
05-15 356
摘要：`$zip`用于将输入数组的元素重新组合转换为新的数组，重组转换的的规则是使用输入数组的第一个元素重组为第一个数组，使用第二个元素重组为第二个数组，以此类推。
oracle 怎样将创建或管理LOB列 赋于一般用户
04-02
要将创建或管理LOB列的权限授予一般用户，必须使用以下步骤： 1. 确保具有创建或管理LOB列的权限的用户（通常是SYS或SYSTEM用户）已经创建了LOB列。 2. 使用GRANT语句授予一般用户执行CREATE TABLE或ALTER TABLE语句的权限。 3. 在CREATE TABLE或ALTER TABLE语句中，指定LOB列的属性和大小。 4. 使用GRANT语句授予一般用户对LOB列的SELECT，INSERT，UPDATE和DELETE权限。 5. 如果需要，使用GRANT语句授予一般用户使用DBMS_LOB包的权限，以便他们可以使用LOB列的其他功能，如截取和连接LOB数据等。 需要注意的是，授予LOB列权限的过程可能因数据库版本和具体情况而有所不同。最好参考官方文档或咨询相关专业人士。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值