Search results for 'Oracle/Tests'

Oracle Lob segment 용량 확인.

2014.02.13 17:06
 
 



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>



신고

슈플 Oracle/Tests

티스토리 툴바