-- Compression( 압축 테이블) 생성
CREATE TABLE test.t_comp (
id NUMBER PRIMARY KEY,
c1 VARCHAR2(100)
)
COMPRESS FOR OLTP
/ -- (EE 옵션) 또는 BASIC: COMPRESS BASIC
** AS-IS
INFO OGG-06507 MAP (TABLE) resolved (entry ogg.GGS_MARKER): TABLE "OGG"."GGS_MARKER".
WARNING OGG-02180 Table OGG.GGS_MARKER will use legacy trail format to support parameter UPDATEBEFORES.
INFO OGG-06509 Using the following key columns for source table OGG.GGS_MARKER: SEQNO, FRAGMENTNO, OPTIME.
INFO OGG-01487 DDL found, operation [CREATE TABLE test.t_comp (
id NUMBER PRIMARY KEY,
c1 VARCHAR2(100)
)
COMPRESS FOR OLTP (size 91)], start SCN [6161963], commit SCN [6161984] instance [orclasis (1)], DDL seqno [13], marker seqno [13].
INFO OGG-00487 DDL operation included [include all], optype [CREATE], objtype [TABLE], objowner "TEST", objname "T_COMP".
GETUPDATEBEFORES를 켠 탓에 OGG가 내부 테이블(GGS_MARKER)을 옛 트레일 형식으로 기록한다는 경고일 뿐이며 동작에는 문제 없습니다.
** TO_BE
INFO OGG-00482 DDL found, operation [CREATE TABLE test.t_comp (
id NUMBER PRIMARY KEY,
c1 VARCHAR2(100)
)
COMPRESS FOR OLTP (size 91)].
INFO OGG-00489 DDL is of mapped scope, after mapping new operation [CREATE TABLE "TEST"."T_COMP" (
id NUMBER PRIMARY KEY,
c1 VARCHAR2(100)
)
COMPRESS FOR OLTP (size 95)].
INFO OGG-00487 DDL operation included [include all], optype [CREATE], objtype [TABLE], objowner [TEST], objname [T_COMP].
INFO OGG-01407 Setting current schema for DDL operation to [SYS].
INFO OGG-00484 Executing DDL operation.
INFO OGG-00483 DDL operation successful.
확인 명령어
SET LINESIZE 200
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
COLUMN compression FORMAT A10
COLUMN compress_for FORMAT A30
SELECT owner, table_name, compression, compress_for
FROM dba_tables
WHERE compression = 'ENABLED'
And owner = 'TEST';
OWNER TABLE_NAME COMPRESSIO COMPRESS_FOR
-------------------- ------------------------------ ---------- ------------------------------
TEST T_COMP ENABLED OLTP
-- 해제(비압축으로 돌리기)
ALTER TABLE test.t_comp MOVE NOCOMPRESS;
** AS-IS
INFO OGG-01487 DDL found, operation [ALTER TABLE test.t_comp MOVE NOCOMPRESS (size 40)], start SCN [6163146], commit SCN [6163217] instance [orclasis (1)], DDL seqno [16], marker seqno [16].
INFO OGG-00487 DDL operation included [include all], optype [ALTER], objtype [TABLE], objowner "TEST", objname "T_COMP".
INFO OGG-00497 Writing DDL operation to extract trail file.
** TO-BE
INFO OGG-00482 DDL found, operation [ALTER TABLE test.t_comp MOVE NOCOMPRESS (size 40)].
INFO OGG-00489 DDL is of mapped scope, after mapping new operation [ALTER TABLE "TEST"."T_COMP" MOVE NOCOMPRESS (size 44)].
INFO OGG-00487 DDL operation included [include all], optype [ALTER], objtype [TABLE], objowner [TEST], objname [T_COMP].
INFO OGG-01407 Setting current schema for DDL operation to [SYS].
INFO OGG-00484 Executing DDL operation.
INFO OGG-00483 DDL operation successful.
-- INSERT
INSERT INTO test.t_comp (id, c1) VALUES (1, 'data1');
commit;
** AS-IS
INFO OGG-06508 Wildcard MAP (TABLE) resolved (entry test.*): table "TEST"."T_COMP".
WARNING OGG-02180 Table TEST.T_COMP will use legacy trail format to support parameter UPDATEBEFORES.
INFO OGG-06509 Using the following key columns for source table TEST.T_COMP: ID.
** TO-BE
INFO OGG-06506 Wildcard MAP resolved (entry test.*): map "TEST"."T_COMP", target test."T_COMP".
INFO OGG-02756 The definition for table TEST.T_COMP is obtained from the trail file.
INFO OGG-06511 Using following columns in default map by name: ID, C1.
INFO OGG-06510 Using the following key columns for target table TEST.T_COMP: ID.
INFO OGG-03010 Performing implicit conversion of column data from character set UTF-8 to we8mswin1252.
-- UPDATE
UPDATE test.t_comp SET c1 = 'data1modify' WHERE id = 1;
commit;
-- DELETE
DELETE FROM test.t_comp WHERE id = 1;
commit;
** AS-IS
select * from test.t_comp;
no rows selected
** TO-BE
select * from test.t_comp;
no rows selected
압축 테이블의 압축을 풀면 일반 테이블이 되는데.
이때 압축이 풀린 상태에서 데이터를 넣고 압축을 다시한 이후 데이터를 넣은 것과 다른 점이 있는지 확인하기 위해
데이터 3건 넣기
INSERT INTO test.t_comp (id, c1) VALUES (1, 'data1');
INSERT INTO test.t_comp (id, c1) VALUES (2, 'data2');
INSERT INTO test.t_comp (id, c1) VALUES (3, 'data3');
기존 테이블에 압축 적용(이동)
ALTER TABLE test.t_comp MOVE COMPRESS FOR OLTP; -- ONLINE 옵션은 12c+
** AS-IS
INFO OGG-01487 DDL found, operation [ALTER TABLE test.t_comp MOVE COMPRESS FOR OLTP (size 47)], start SCN [6165207], commit SCN [6165233] instance [orclasis (1)], DDL seqno [17], marker seqno [17].
INFO OGG-00487 DDL operation included [include all], optype [ALTER], objtype [TABLE], objowner "TEST", objname "T_COMP".
INFO OGG-00497 Writing DDL operation to extract trail file.
** TO-BE
INFO OGG-00482 DDL found, operation [ALTER TABLE test.t_comp MOVE COMPRESS FOR OLTP (size 47)].
INFO OGG-00489 DDL is of mapped scope, after mapping new operation [ALTER TABLE "TEST"."T_COMP" MOVE COMPRESS FOR OLTP (size 51)].
INFO OGG-00487 DDL operation included [include all], optype [ALTER], objtype [TABLE], objowner [TEST], objname [T_COMP].
INFO OGG-01407 Setting current schema for DDL operation to [SYS].
INFO OGG-00484 Executing DDL operation.
INFO OGG-00483 DDL operation successful.
-- INSERT
INSERT INTO test.t_comp (id, c1) VALUES (4, 'data4');
commit;
EERROR at line 1:
ORA-01502: index 'TEST.SYS_C009167' or partition of such index is in unusable
state
RA-01502 는 대상 테이블의 인덱스가 UNUSABLE 상태라서 DML(INSERT/UPDATE/DELETE)을 막을 때 발생합니다. 메시지에 나온 TEST.SYS_C009131 는 보통 PK/UNIQUE 제약을 받치는 시스템 생성 인덱스입니다.
반대로 해보기
truncate table test.t_comp;
** AS-IS
WARNING OGG-02180 Table TEST.T_COMP will use legacy trail format to support parameter UPDATEBEFORES.
ERROR OGG-01433 Failed to validate table TEST.T_COMP. The table is compressed and extract will not be able to extract data from Oracle logs.
추출 프로세스 ABENDED
삭제
DROP TABLE test.t_comp PURGE;
** AS-IS
INFO OGG-06507 MAP (TABLE) resolved (entry ogg.GGS_MARKER): TABLE "OGG"."GGS_MARKER".
WARNING OGG-02180 Table OGG.GGS_MARKER will use legacy trail format to support parameter UPDATEBEFORES.
INFO OGG-06509 Using the following key columns for source table OGG.GGS_MARKER: SEQNO, FRAGMENTNO, OPTIME.
INFO OGG-01487 DDL found, operation [drop table test.t_comp (size 23)], start SCN [6171798], commit SCN [6171835] instance [orclasis (1)], DDL seqno [32], marker seqno [32].
INFO OGG-00487 DDL operation included [include all], optype [DROP], objtype [TABLE], objowner "TEST", objname "T_COMP".
** TO-BE
INFO OGG-00482 DDL found, operation [drop table test.t_comp (size 23)].
INFO OGG-00489 DDL is of mapped scope, after mapping new operation [drop table "TEST"."T_COMP" (size 27)].
INFO OGG-00487 DDL operation included [include all], optype [DROP], objtype [TABLE], objowner [TEST], objname [T_COMP].
INFO OGG-01407 Setting current schema for DDL operation to [SYS].
INFO OGG-00484 Executing DDL operation.
INFO OGG-00483 DDL operation successful.
-- 그냥 압축 테이블에 dml 넣기
CREATE TABLE test.t_comp (
id NUMBER PRIMARY KEY,
c1 VARCHAR2(100)
)
COMPRESS FOR OLTP
/
-- INSERT
INSERT INTO test.t_comp (id, c1) VALUES (1, 'data1');
commit;
AS-IS
WARNING OGG-02180 Table TEST.T_COMP will use legacy trail format to support parameter UPDATEBEFORES.
ERROR OGG-01433 Failed to validate table TEST.T_COMP. The table is compressed and extract will not be able to extract data from Oracle logs.
추출 프로세스 ABENDED
EXTRACT 파라메터에
TABLEEXCLUDE TEST.T_COMP; 를 추가해주고 기동시키면 기동된다.
'테스트 > 동기화' 카테고리의 다른 글
| 19c -> 11g(12c) - 분산 트랜잭션 (Classic -> Classic) (1) (2) | 2025.08.26 |
|---|---|
| 19c -> 11g(12c) - 파티션 테이블 (Classic -> Classic) (1) (1) | 2025.08.26 |
| 19c -> 11g(12c) - (Classic -> Classic) (1) 파라메터 모음 (0) | 2025.08.26 |
| 19c -> 11g(12c) - (INT -> classic) 파라메터 (0) | 2025.08.26 |
| 19c -> 11g(12c) - 병렬 테이블 (INT -> classic) (1) | 2025.08.26 |