select sum(dbms_lob.getlength(b)/1024/1024)
from lob_test
;
select dbms_lob.getlength(b)
from lob_test
;
truncate table lob_test;
insert into lob_test values(1, empty_clob());
declare
v1 clob;
begin
select b into v1 from lob_test;
for i in 1..6 loop
dbms_lob.append(v1, rpad('x',1000,'x'));
end loop;
end;
/
commit;
select dbms_lob.getlength(b)
from lob_test
;
insert into lob_test select a, b from lob_test connect by level <= 10000;
commit;
select sum(dbms_lob.getlength(b)/1024/1024)
from lob_test
;
SQL>
SQL>
SQL>
SQL> truncate table lob_test;
insert into lob_test values(1, empty_clob());
declare
v1 clob;
begin
select b into v1 from lob_test;
for i in 1..6 loop
dbms_lob.append(v1, rpad('x',1000,'x'));
Table truncated.
SQL>
1 row created.
SQL> 2 3 4 5 6 7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL>
SQL> select dbms_lob.getlength(b)
2 from lob_test
3 ;
DBMS_LOB.GETLENGTH(B)
---------------------
6000
SQL>
SQL>
SQL>
SQL>
SQL> insert into lob_test select a, b from lob_test connect by level <= 10000;
commit;
select sum(dbms_lob.getlength(b)/1024/1024)
from lob_test
;
10000 rows created.
SQL>
Commit complete.
SQL> SQL> 2 3
SUM(DBMS_LOB.GETLENGTH(B)/1024/1024)
------------------------------------
57.226181
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select segment_name,segment_type,sum(bytes/1024/1024)
2 from dba_segments
3 where owner='SCOTT'
4 and segment_name = 'SYS_LOB0000100237C00002$$'
5 group by segment_name,segment_type
6 order by 3 desc
7 ;
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE SUM(BYTES/1024/1024)
------------------------------------------------------ --------------------
SYS_LOB0000100237C00002$$
LOBSEGMENT 80
SQL>