文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

SQL中如何释放大数据量的lob字段空间

2024-04-02 19:55

关注

SQL中如何释放大数据量的lob字段空间,相信很多没有经验的人对此束手无策,为此本文总结了问题出现的原因和解决方法,通过这篇文章希望你能解决这个问题。

SQL> create tablespace ts_lob datafile '/u01/app/oracle/oradata/DBdb/ts_lob.dbf' size 500m autoextend off;

Tablespace created.

--scott用户创建测试表lob1:
SQL> grant dba to scott;

Grant succeeded.

SQL> conn scott/tiger;
Connected.
SQL> create table lob1(line number,text clob) tablespace ts_lob;

Table created.

SQL> insert into lob1  select line,text from  dba_source;

637502 rows created.

SQL> insert into lob1 select * from lob1;

637502 rows created.

SQL> select count(*) from lob1;

  COUNT(*)
----------
   1275004

SQL> commit;

Commit complete.


--查询表大小(包含表和lob字段)
select (select nvl(sum(s.bytes/1024/1204), 0)                              -- the table segment size  
          from dba_segments s
         where s.owner = upper('SCOTT')
           and (s.segment_name = upper('LOB1'))) +
       (select nvl(sum(s.bytes/1024/1024), 0)                              -- the lob segment size  
          from dba_segments s, dba_lobs l
         where s.owner = upper('SCOTT')
           and (l.segment_name = s.segment_name and
               l.table_name = upper('LOB1') and
               l.owner = upper('SCOTT'))) +
       (select nvl(sum(s.bytes/1024/1024), 0)                              -- the lob index size  
          from dba_segments s, dba_indexes i
         where s.owner = upper('SCOTT')
           and (i.index_name = s.segment_name and
               i.table_name = upper('LOB1') and index_type = 'LOB' and
               i.owner = upper('SCOTT'))) "total_table_size_M"
        FROM DUAL;
        
total_table_size_M
------------------
        239.966154
           

--查询表大小(不包含lob字段)               
col SEGMENT_NAME for a30
col PARTITION_NAME for a30
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024 M from dba_segments where segment_name='LOB1' and owner='SCOTT';

OWNER                          SEGMENT_NAME                   PARTITION_NAME                          M
------------------------------ ------------------------------ ------------------------------ ----------
SCOTT                          LOB1                                                                 208


--查询表大小(只包含lob字段)       
set lines 200 pages 999
col owner  for a15
col TABLE_NAME for a20
col COLUMN_NAME for a30
col SEGMENT_NAME for a30
select a.owner,  
       a.table_name,  
       a.column_name,  
       b.segment_name,
       b.segment_type,  
       ROUND(b.BYTES / 1024 / 1024)  
  from dba_lobs a, dba_segments b  
 where a.segment_name = b.segment_name  
   and a.owner = 'SCOTT'  
   and a.table_name = 'LOB1'  
union all  
select a.owner,  
       a.table_name,  
       a.column_name,  
       b.segment_name,
       b.segment_type,  
       ROUND(b.BYTES / 1024 / 1024)  
  from dba_lobs a, dba_segments b  
 where a.index_name = b.segment_name  
   and a.owner = 'SCOTT'  
   and a.table_name = 'LOB1';

OWNER           TABLE_NAME           COLUMN_NAME                    SEGMENT_NAME                   SEGMENT_TYPE       ROUND(B.BYTES/1024/1024)
--------------- -------------------- ------------------------------ ------------------------------ ------------------ ------------------------
SCOTT           LOB1                 TEXT                           SYS_LOB0000089969C00002$$      LOBSEGMENT                               63
SCOTT           LOB1                 TEXT                           SYS_IL0000089969C00002$$       LOBINDEX                                  0
   
   
--查询ts_lob表空间的表大小排行
SQL> select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments
               where tablespace_name='TS_LOB' group by segment_name )
               order by sx desc;
 
SEGMENT_NAME                           SX
------------------------------ ----------
LOB1                                  208
SYS_LOB0000089969C00002$$              63
SYS_IL0000089969C00002$$            .0625

--查询lob字段SCOTT_LOB0000089963C00002$$ 、SCOTT_IL0000089963C00002$$:
SQL> col object_name for a30
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME in('SYS_LOB0000089969C00002$$','SYS_IL0000089969C00002$$');

OWNER           OBJECT_NAME                    OBJECT_TYPE
--------------- ------------------------------ -------------------
SCOTT           SYS_IL0000089969C00002$$       INDEX
SCOTT           SYS_LOB0000089969C00002$$      LOB

SQL>  select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME,INDEX_NAME from dba_lobs where segment_name in('SYS_LOB0000089969C00002$$','SYS_IL0000089969C00002$$');

OWNER           TABLE_NAME           COLUMN_NAME                    SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME
--------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
SCOTT           LOB1                 TEXT                           SYS_LOB0000089969C00002$$      TS_LOB                         SYS_IL0000089969C00002$$

SQL>
SQL> select SEGMENT_NAME,bytes /1024/1024 sx from dba_segments where tablespace_name='TS_LOB' and SEGMENT_NAME in('SYS_LOB0000089969C00002$$','SYS_IL0000089969C00002$$');

SEGMENT_NAME                           SX
------------------------------ ----------
SYS_LOB0000089969C00002$$              63
SYS_IL0000089969C00002$$            .0625


一、先试着删除lob字段:
SQL>  alter table scott.lob1 drop (text);

Table altered.

SQL> select SEGMENT_NAME,bytes /1024/1024 sx from dba_segments where tablespace_name='TS_LOB' and SEGMENT_NAME in('SYS_LOB0000089969C00002$$','SYS_IL0000089969C00002$$');

no rows selected

SQL> select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments where tablespace_name='TS_LOB' group by segment_name ) order by sx desc;

SEGMENT_NAME                           SX
------------------------------ ----------
LOB1                                  208

发现删除lob字段可以释放表空间。


--再次添加LOB字段:
SQL> alter  table scott.lob1 add (text clob);

Table altered.

SQL>  select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments where tablespace_name='TS_LOB' group by segment_name ) order by sx desc;

SEGMENT_NAME                           SX
------------------------------ ----------
LOB1                                  208
SYS_LOB0000089969C00002$$           .0625
SYS_IL0000089969C00002$$            .0625

二、再次插入数据:
SQL> insert into scott.lob1 select LINE,text from dba_source;

637502 rows created.

SQL> insert into scott.lob1 select LINE,text from dba_source;

637502 rows created.

SQL> commit;

Commit complete.

SQL>  select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments where tablespace_name='TS_LOB' group by segment_name ) order by sx desc;

SEGMENT_NAME                           SX
------------------------------ ----------
LOB1                                  208
SYS_LOB0000089969C00002$$              63
SYS_IL0000089969C00002$$            .0625


--接着试着truncate表LOB1
SQL> truncate table scott.lob1;

Table truncated.

SQL>  select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments where tablespace_name='TS_LOB' group by segment_name ) order by sx desc;

SEGMENT_NAME                           SX
------------------------------ ----------
LOB1                                .0625
SYS_LOB0000089969C00002$$           .0625
SYS_IL0000089969C00002$$            .0625

truncate表也可以释放lob字段数据;

三、再次插入数据:
SQL> insert into scott.lob1 select LINE,text from dba_source;

637502 rows created.

SQL> insert into scott.lob1 select LINE,text from dba_source;

637502 rows created.

SQL> commit;

Commit complete.

SQL>
SQL>  select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments where tablespace_name='TS_LOB' group by segment_name ) order by sx desc;

SEGMENT_NAME                           SX
------------------------------ ----------
LOB1                                  184
SYS_LOB0000089969C00002$$              63
SYS_IL0000089969C00002$$            .0625

使用delete方式删除数据,实际上物理块还是被占用,高水位没有下降。
SQL> delete scott.lob1;

1275004 rows deleted.

SQL>
SQL> select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments where tablespace_name='TS_LOB' group by segment_name ) order by sx desc;

SEGMENT_NAME                           SX
------------------------------ ----------
LOB1                                  184
SYS_LOB0000089969C00002$$              63
SYS_IL0000089969C00002$$              .75

SQL> select count(*) from scott.lob1;

  COUNT(*)
----------
         0
         
SQL> truncate table scott.lob1;

Table truncated.

SQL> select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments where tablespace_name='TS_LOB' group by segment_name ) order by sx desc;

SEGMENT_NAME                           SX
------------------------------ ----------
LOB1                                .0625
SYS_LOB0000089969C00002$$           .0625
SYS_IL0000089969C00002$$            .0625

结论:在删除lob字段的大数据量时,可以采用重建表(CTAS)、删除lob字段再重建alter table table_name drop (column)、导出导入(只导出元数据)、或者直接truncate全表删除全表(包括lob)降低高水位。 

看完上述内容,你们掌握SQL中如何释放大数据量的lob字段空间的方法了吗?如果还想学到更多技能或想了解更多相关内容,欢迎关注亿速云行业资讯频道,感谢各位的阅读!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯