Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people are at a loss about how to release the lob field space with a large amount of data in SQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

SQL > create tablespace ts_lob datafile'/ u01 size autoextend off

Tablespace created.

-- scott user creates test table 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.

-- query table size (including table and lob fields)

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

-query table size (does not contain lob field)

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

-- query table size (contains only lob fields)

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

-- query table size ranking of ts_lob tablespaces

SQL > select * from (select SEGMENT_NAME,sum (bytes) / 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

-- query lob fields SCOTT_LOB0000089963C00002 $$and 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 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

First, try to delete the lob field:

SQL > alter table scott.lob1 drop (text)

Table altered.

SQL > select SEGMENT_NAME,bytes / 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 sx from dba_segments where tablespace_name='TS_LOB' group by segment_name) order by sx desc

SEGMENT_NAME SX

LOB1 208

It is found that deleting the lob field frees up the table space.

-- add the LOB field again:

SQL > alter table scott.lob1 add (text clob)

Table altered.

SQL > select * from (select SEGMENT_NAME,sum (bytes) / 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

Second, insert the data again:

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 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

Then try to truncate the table LOB1

SQL > truncate table scott.lob1

Table truncated.

SQL > select * from (select SEGMENT_NAME,sum (bytes) / 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 tables can also release lob field data

3. Insert data again:

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 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

Use delete to delete data, in fact, the physical block is still occupied, and the high water level has not dropped.

SQL > delete scott.lob1

1275004 rows deleted.

SQL >

SQL > select * from (select SEGMENT_NAME,sum (bytes) / 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 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

Conclusion: when deleting a large amount of data in the lob field, the high water level can be reduced by rebuilding the table (CTAS), deleting the lob field and then rebuilding the alter table table_name drop (column), exporting and importing (only exporting metadata), or directly deleting the whole table of the truncate table (including lob).

After reading the above, have you mastered the method of how to free up a large amount of lob field space in SQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.