LOB Table Nologging 조회

1. 샘플 테이블 생성

DROP TABLE TB_LOB PURGE;


CREATE TABLE TB_LOB
( col1 NUMBER, col2 CLOB )
LOB(col2) STORE AS tb_lob_col2 (
      TABLESPACE LOB_SEGMENT
      NOCACHE NOLOGGING
      DISABLE STORAGE IN ROW
      INDEX tb_lob_idx01 (
              TABLESPACE LOB_SEGMENT
        )
    ) NOLOGGING;

 

 

2. 테이블 조회

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, STATUS, LOGGING
FROM DBA_TABLES 
WHERE TABLE_NAME = 'TB_LOB';


---------------------------------------------------------------
| OWNER  | TABLE_NAME | TABLESPACE_NAME | STATUS | LOGGING
---------------------------------------------------------------
| SCOTT  | TB_LOB     | USERS           | VALID  | NO
---------------------------------------------------------------

SELECT OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME, TABLESPACE_NAME, LOGGING
FROM DBA_LOBS 
WHERE TABLE_NAME = 'TB_LOB';

--------------------------------------------------------------------------------
| OWNER | TABLE_NAME | COLUMN_NAME  | SEGMENT_NAME  | TABLESPACE_NAME | LOGGING
--------------------------------------------------------------------------------
| SCOTT | TB_LOB     | COL2         | TB_LOB_COL2   | LOB_SEGMENT     | NO
--------------------------------------------------------------------------------

SELECT OWNER, INDEX_NAME, TABLE_NAME, TABLESPACE_NAME, LOGGING, INITIAL_EXTENT, NEXT_EXTENT
FROM DBA_INDEXES 
WHERE INDEX_NAME = 'TB_LOB_IDX01';

-----------------------------------------------------------------------------------------------
| OWNER | INDEX_NAME   | TABLE_NAME | TABLESPACE_NAME | LOGGING | INITIAL_EXTENT | NEXT_EXTENT
-----------------------------------------------------------------------------------------------
| SCOTT | TB_LOB_IDX01 | TB_LOB     | LOB_SEGMENT     | YES     |          65536 |     1048576
-----------------------------------------------------------------------------------------------

 

 

3. INDEX NOLOGGING

ALTER TABLE TB_LOB MODIFY LOB(col2) (NOCACHE NOLOGGING);

SELECT OWNER, INDEX_NAME, TABLE_NAME, TABLESPACE_NAME, LOGGING--,INITIAL_EXTENT, NEXT_EXTENT
FROM DBA_INDEXES 
WHERE INDEX_NAME = 'TB_LOB_IDX01';

------------------------------------------------------------------ 
| OWNER | INDEX_NAME   | TABLE_NAME | TABLESPACE_NAME | LOGGING
------------------------------------------------------------------
| SCOTT | TB_LOB_IDX01 | TB_LOB     | LOB_SEGMENT     | NO     
------------------------------------------------------------------