행위

LOB 테이블 관리

DB CAFE

(LOB에서 넘어옴)
thumb_up 추천메뉴 바로가기

1 테이블에 있는 LOB 컬럼 조회[편집]

set pagesize 200
set lines 200
set long 999
col owner for a15
col table_name for a20
col column_name for a21
-- ---------------------
select a.owner,a.table_name,a.column_name, data_type
  from dba_lobs a
     , dba_tab_columns b
 where a.column_name=b.column_name
   and a.table_name = b.table_name
   and a.owner = b.owner
   and b.owner not in ('SYS','SYSTEM','DBSNMP','WMSYS');

2 LOB 사이즈 조회[편집]

SELECT TABLE_NAME
      , sum(bytes)
   FROM (SELECT B.table_name AS table_name
              , A.bytes
           FROM DBA_SEGMENTS A
              , DBA_LOBS B
          WHERE A.segment_name = B.segment_name
            AND EXISTS (SELECT 1
                 FROM MIG_TABLES C
                WHERE C.TABLE_NAME = B.TABLE_NAME
                  AND C.OWNER      = B.OWNER
                  AND C.SYSTEM_CODE = 'LC1'
                  AND C.USE_YN = 'Y'
              )     
                          
          )
group by table_name;

2.1 실제 사용중인 LOB 사이즈 조회[편집]

SELECT nvl((sum(dbms_lob.getlength( &lob_column ))),0) AS bytes 
  FROM &table_name;

3 LOB 테이블 컬럼 변경[편집]

3.1 NOLOGGIN 모드로 변경[편집]

alter table EMP modify LOB(EMP_TEXT컬럼) (nocache nologging); -- nocache , nologgin 으로 변경

4 LOB 입력 프로시져[편집]

 attach_file clob type 은 4G 까지 가능한 data형이고, 9i 부터 지원한다.

  • 디비링크 사용시 CTAS로 LOB 테이블 복사 가능

CREATE OR REPLACE PROCEDURE SP_CLOB_TEST (
 v_MSG_ID   IN VARCHAR2,
 v_XML_DATA   IN CLOB
)
/*
 처리내용 : CLOB 을 활용한 데이터 입력을 위한 procedure
 처리내용 : BPCIM에서 입력된 XML 데이터를 TB_BPCIM_IF 테이블에 insert
    CLOB 컬럼에 데이터를 넣기 위해서는
    1. EMPTY_CLOB을 활용하여 insert
    2. FOR UPDATE 로 해당 컬럼을 lock
    3. DBMS_LOB.WRITE / DBMS_LOB.WRITEAPPEND 활용하여 update 처리.
 made by joajoa. 2012.10.31
*/
IS
 v_clob_selected CLOB;
 v_write_amt     NUMBER;
 v_write_offset  INTEGER := 1;
 v_buf           LONG;
 -- CLOB 컬럼에 한번에 입력시킬 크기(오라클 Buffer 설정과 연관된 것 같음. Buffer 사이즈에 따라 크기 조정)
 v_buf_value     INTEGER := 1000;
 v_clob_length   NUMBER;
 loop_index      INTEGER;
BEGIN
/*
CREATE TABLE TB_CLOB_TEST (
 MSG_ID VARCHAR2(10),
 XML_DATA CLOB 
);
*/
 DBMS_OUTPUT.ENABLE ( 1000000);
 DBMS_OUTPUT.PUT_LINE( '***** [ SP_CLOB_TEST Procedure START ' || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') || ' ] *****');
 -- 새로운 ROW일 경우 offset 초기화
 v_write_offset := 1;
 -- CLOB 제외한 값 insert
 INSERT INTO TB_CLOB_TEST
  ( MSG_ID, XML_DATA
  )
  VALUES
  ( v_MSG_ID,
   EMPTY_CLOB()
  );
 -- CLOB 초기화
 SELECT XML_DATA INTO v_clob_selected FROM TB_CLOB_TEST
 WHERE MSG_ID = v_MSG_ID
 FOR UPDATE;
 -- 컬럼 DATA가 존재할 경우만
 IF v_XML_DATA is not null THEN
  v_clob_length := DBMS_LOB.GETLENGTH (v_XML_DATA);
  -- CLOB 컬럼에 한번에 입력시킬 수 있는 크기보다 큰 경우 LOOP로 처리
  IF v_clob_length > v_buf_value THEN
   -- DATA 전체 크기를 v_buf_value 크기로 나눈 값만큼 LOOP
   FOR loop_index IN 1 .. TRUNC(v_clob_length / v_buf_value)
   LOOP
    DBMS_OUTPUT.PUT_LINE( cast(loop_index as varchar2) || '. loop_index --------' || cast((v_buf_value * (loop_index - 1)) + 1 as varchar2));
    v_buf := DBMS_LOB.SUBSTR(v_XML_DATA, v_buf_value, (v_buf_value * (loop_index - 1) ) + 1);
    v_write_amt := DBMS_LOB.GETLENGTH (v_buf);
    DBMS_LOB.WRITEAPPEND(v_clob_selected, v_write_amt, v_buf);
   END LOOP;
   -- 나머지 DATA 입력
   IF MOD(v_clob_length, v_buf_value) <> 0 THEN
    v_buf := DBMS_LOB.SUBSTR(v_XML_DATA, MOD(v_clob_length, v_buf_value), (v_clob_length - MOD(v_clob_length, v_buf_value)) + 1);
    v_write_amt := DBMS_LOB.GETLENGTH (v_buf);
    DBMS_LOB.WRITEAPPEND(v_clob_selected, v_write_amt, v_buf);
   END IF;
  -- CLOB 컬럼에 한번에 입력시킬 수 있는 크기보다 작은 경우의 처리
  ELSE
   v_buf := v_XML_DATA;
   v_write_amt := DBMS_LOB.GETLENGTH (v_buf);
   DBMS_LOB.WRITE(v_clob_selected, v_write_amt, v_write_offset, v_buf);
  END IF;
 END IF;
    
 COMMIT;
EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('***** [ SP_CLOB_TEST Procedure ERROR ' || TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') || ' ] *****');
  DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM) ;
  ROLLBACK;
END;

5 LOB 세그먼트 다른 TABLESPACE로 이동[편집]

-- Find the lob segment details

select table_name,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where OWNER='DBACLASS'

-- Move to new tablespace

alter table DBACLASS.LOB_SEG1 move lob (PAYLOAD) store as SYS_LOB0000100201C00011$$ ( tablespace USERS);
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=LOB_테이블_관리&oldid=12198"