[ORACLE] LOB 생성, 조회

-- BLOB, CLOB 생성

create tablespace test datafile '/oracle/base/oradata/ORCL/test.dbf' size 100M autoextend on; create user test identified by test default tablespace test temporary tablespace temp;   grant dba to test;   create table clob ( name varchar2(20) primary key not null, age number(10), character CLOB);   create table blob ( name varchar2(20) primary key not null, age number(10), character BLOB);   insert into clob values ('AAA', 26, 'ABCDEFGHIG1ABCDEFGHIG2ABCDEFGHIG3');   insert into clob values ('BBB', 27, EMPTY_CLOB());   insert into blob values ('AAA', 26, null);

 

-- table 구조 조회

 

desc <테이블명>

 

-- lob table 조회

select owner, table_name from dba_lobs;

 

-- LOB table count(*)

set line 300 col OWNER for a20 col OBJECT_TYPE for a5 select owner, object_type, count(*) from dba_objects where object_type = 'LOB' group by owner,object_type order by owner,object_type ;