釋放大資料量的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)降低高水位。
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)降低高水位。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2148205/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle資料庫高水位釋放——LOB欄位空間釋放Oracle資料庫
- [20181020]lob欄位的索引段.txt索引
- ORACLE LOB大欄位維護Oracle
- [20181022]lob欄位的lobid來之那裡.txt
- [20210208]lob欄位與查詢的問題.txt
- [20181031]lob欄位與布隆過濾.txt
- [重慶思莊每日技術分享]-重建LOB欄位上的IndexIndex
- 【ORA-01555】Oracle LOB欄位匯出 報錯 ORA-01555Oracle
- MySQL欄位新增註釋，但不改變欄位的型別MySql型別
- ORACLE 資料匯出LOB欄位報錯ORA-31693,ORA-02354,ORA-22924Oracle
- [轉帖]達夢資料庫-統計資料表資料量及空間表大小資料庫
- HTTP header 欄位解釋HTTPHeader
- 【MybatisPlus】資料庫的datetime型別欄位為空的時候，報錯空指標？MyBatis資料庫型別指標
- 支援 enum 型別的欄位允許為空插入資料庫型別資料庫
- Java判斷欄位是否為空，為空賦值 ？Java賦值
- dbms_lob儲存過程導致臨時表空間100%儲存過程
- 如何釋放Mac空間？釋放Mac系統空間小技巧Mac
- LOB欄位相關概念(自動建立LOB索引段和重建索引方法)索引
- SQL Server中獲取資料庫名、表名、欄位名和欄位註釋的SQL語句SQLServer資料庫
- 資料庫欄位設定非空， phalcon建立資料驗證不透過資料庫
- 【掃盲篇】衛生資訊資料集欄位解釋
- 帝國CMS欄目管理增加自定義欄位值的為空判斷
- mybatis自動填充時間欄位MyBatis
- 更新欄位時更新時間不自動更新（不更新 updated_at 欄位）
- ORACLE ASM磁碟組空間溢位OracleASM
- 關於日期及時間欄位的查詢
- Mysql InnoDB刪除資料後釋放磁碟空間的方法MySql
- MySQL 5.7的表刪除資料後的磁碟空間釋放MySql
- /etc/shadow檔案相關欄位的解釋
- MySQL 資料庫技巧：批次更新隨機生成的時間戳欄位MySql資料庫隨機時間戳
- pandas dataframe 時間欄位 diff 函式函式
- MySQL為欄位新增預設時間（插入時間）MySql
- http請求頭個欄位解釋HTTP
- Dynamics CRM使用計算欄位自動計算兩個時間欄位的天數差
- 實現註解校驗Dto欄位是否為空
- 編輯功能-載荷裡空欄位沒有傳
- lsof |grep deleted 釋放磁碟空間delete
- pydantic 欄位欄位校驗
- 64位win10多大硬碟空間合適_64位win10支援多大硬碟空間Win10硬碟