SQL中如何釋放大數(shù)據(jù)量的lob字段空間，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

創(chuàng)新互聯(lián)主營(yíng)惠安網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,重慶App定制開(kāi)發(fā),惠安h5重慶小程序開(kāi)發(fā)搭建,惠安網(wǎng)站營(yíng)銷(xiāo)推廣歡迎惠安等地區(qū)企業(yè)咨詢(xún)

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

Tablespace created.

--scott用戶(hù)創(chuàng)建測(cè)試表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.


--查詢(xún)表大小(包含表和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
         

--查詢(xún)表大小(不包含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


--查詢(xún)表大小(只包含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
 
 
--查詢(xún)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

--查詢(xún)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

發(fā)現(xiàn)刪除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

二、再次插入數(shù)據(jù)：
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字段數(shù)據(jù)；

三、再次插入數(shù)據(jù)：
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方式刪除數(shù)據(jù)，實(shí)際上物理塊還是被占用，高水位沒(méi)有下降。
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

結(jié)論：在刪除lob字段的大數(shù)據(jù)量時(shí)，可以采用重建表（CTAS)、刪除lob字段再重建alter table table_name drop (column)、導(dǎo)出導(dǎo)入（只導(dǎo)出元數(shù)據(jù)）、或者直接truncate全表刪除全表（包括lob)降低高水位。 

看完上述內(nèi)容，你們掌握SQL中如何釋放大數(shù)據(jù)量的lob字段空間的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！

標(biāo)題名稱(chēng)：SQL中如何釋放大數(shù)據(jù)量的lob字段空間
分享地址：http://chinadenli.net/article42/jgceec.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站建設(shè)、電子商務(wù)、網(wǎng)站策劃、企業(yè)建站、App設(shè)計(jì)、做網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)