테이블의 컬럼을 drop 하는데는 일반적으로 ALTER TABLE DROP COLUMN 명령어를 사용하는데요,
해당 명령어는 DDL로써 exclusive TM lock을 잡고 있기 때문에 실시간으로 테이블에 작업이 있는 경우에는 사용하기가 꺼려지는 작업입니다.
그럴때에 alter table set unused column 명령어를 사용하여 미사용컬럼으로 변경하여 처리하는 방법도 있습니다.
저도 메타데이터만 수정하는 작업이라 금방끝난다정도로만 알고 있었는데, 좀더 상세하게 어떻게 동작을 하는지 정리하면서 배우는 시간을 가지려 합니다.
DROP COLUMN
컬럼을 drop할 때 오라클은 테이블의 모든블록의 모든 row를 읽습니다. 그리고 거기서 drop 될 컬럼을 제거한 후 rewrite하게 됩니다. 여러개의 컬럼을 동시에 drop할 때에는 각 컬럼을 별도로 취급하여 각각 redo와 undo가 생성되게 됩니다.
테이블은 명령어 수행시 exclusive lock이 걸리게 됩니다.
그리고 drop column을 하였다고 해서 공간이 확보되는 것이 아니고, HWM는 그대로이며 그 안에서 데이터만 rewrite되기 때문에 용량측면에서는 용량 반환같은 이점이 없습니다. ( 추후에 reorg를 통해서 반환해야 함)
리소스를 많이 사용하고 오래걸리는 이유.
1) 모든 블록의 모든 row를 읽기 때문
2) drop column 시 undo/redo를 생성
-2bytes 컬럼 drop 시 row당 undo 78 bytes / redo 234 bytes 오버헤드 발생.
3) I/O집약적. 테이블블록수만큼 읽고 다시 써야되기 때문에 그거만큼의 I/O가 발생하게 됨
4) 블록rewrite시 발생하는 CPU비용
- 모든 row 읽기 및 파싱
- column 위치 오프셋 재계산
- 각행의 컬럼 위치 식별 및 제거
- 나머지 컬럼 위치 오프셋 조정
- 재구성된 row 쓰기
UNUSED COLUMN
메타데이터의 변경만으로 컬럼을 drop한것처럼 처리하는 방법입니다.
테이블의 모든 블록을 읽을 필요없이 메타데이터의 변경만 처리하는 부분이라 명령어가 굉장히 빠르게 처리되며, 테이블 exclusive lock도 발생하지 않습니다. 블록 rewrite가 없으므로 undo/redo 오버헤드가 발생하지도 않습니다.
메타데이터만 변경처리하는 부분이라 속도가 굉장히 빨리 처리됩니다.
그리고 drop column과 마찬가지로 동일한 컬럼명으로 다시 생성이 가능해지며 unused된 컬럼은 복구가 불가능합니다.
후에 alter table t drop unused column명령어를 통해 unused 된 컬럼을 drop column과 같은 매커니즘으로 블록 rewrite 작업을 할 수 있게 됩니다.
장점으로는 컬럼의 논리적 drop과 물리적 drop을 나눠서 진행할 수 있다는 점이며, 사용안하는 time에 물리적 drop을 진행할 수 있는 부분이 가장 큰 장점같습니다.
아래는 테스트테이블로 두 명령어 수행 후 메타데이터 및 wait event 의 비교해보겠습니다.
1.테이블 생성
drop table t1 purge;
create table t1
as
with generator as (
select /*+ materialize */
rownum id
from dual
connect by level <= 1e4
)
select
rownum id,
'XX' v2,
'XX' v22,
'XX' v222,
'XX' v2222,
'XX' v22222,
'XX' v222222,
lpad('x',30) v30,
lpad('x',30) v302,
lpad('x',30) v3022,
lpad('x',30) v30222,
lpad('x',30) v302222,
lpad('x',30) v3022222,
rpad('x',100) padding
from
generator v1,
generator v2
where rownum <= 1e6
;
1-1) 작업 전 메타데이터
더보기
SQL> select count(*) From t1;
COUNT(*)
---------
1000000
1 rows selected.
SQL> select * from dba_tab_columns
where table_name='T1';
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_MOD DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH CHAR_USED V80_FMT_IMAGE DATA_UPGRADED HISTOGRAM DEFAULT_ON_NULL IDENTITY_COLUMN SENSITIVE_COLUMN EVALUATION_EDITION UNUSABLE_BEFORE UNUSABLE_BEGINNING COLLATION
-------- ------------ ------------- ----------- ------------- ------------------ ----------- -------------- ---------- -------- --------- -------------- ---------------- ------------ -------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------- --------- --------- ----------- -------------------- ----------- -------------------------------------------- -------------------- ------------ ---------- ----------- ----------- --------- ------------- ------------- --------------- --------------- --------------- ---------------- -------------------- ---------------- ------------------- ---------------
YSBAE T1 ID NUMBER 22 Y 1 1000000 C102 C402 0.000001 0 1 2025/12/12 11:06:11 1000000 YES NO 5 0 NO YES NONE NO NO NO
YSBAE T1 V2 CHAR 2 Y 2 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V22 CHAR 2 Y 3 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V222 CHAR 2 Y 4 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V2222 CHAR 2 Y 5 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V22222 CHAR 2 Y 6 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V222222 CHAR 2 Y 7 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V30 VARCHAR2 30 Y 8 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V302 VARCHAR2 30 Y 9 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V3022 VARCHAR2 30 Y 10 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V30222 VARCHAR2 30 Y 11 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V302222 VARCHAR2 30 Y 12 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V3022222 VARCHAR2 30 Y 13 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 PADDING VARCHAR2 100 Y 14 1 78202020202020202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000 78202020202020202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 100 YES NO 101 100 B NO YES NONE NO NO NO USING_NLS_COMP
14 rows selected.
SQL> select * From sys.col$
where obj# in (select object_id from dba_objects where object_name='T1');
OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM EVALEDITION# UNUSABLEBEFORE# UNUSABLEBEGINNING# SPARE1 SPARE2 SPARE3 SPARE6 SPARE7 SPARE8 COLLID COLLINTCOL# SPARE9 SPARE10 SPARE11 SPARE12 SPARE13 ACDRRESCOL#
--------- --------- --------- ------------ --------- ---------- --------- --------- ------------ ---------- --------- --------- --------- --------- --------- --------- --------- ----------- ------------ --------------- ------------------ --------- --------- ----------- -------------------- --------- --------- --------- ----------- --------- --------- ---------- -------- -------------------- -----------
476956 1 1 22 0 ID 2 22 0 0 1 0 0 0 0 0 0 0
476956 2 2 2 0 V2 96 2 0 0 2 0 873 1 0 0 0 2 16382
476956 3 3 2 0 V22 96 2 0 0 3 0 873 1 0 0 0 2 16382
476956 4 4 2 0 V222 96 2 0 0 4 0 873 1 0 0 0 2 16382
476956 5 5 2 0 V2222 96 2 0 0 5 0 873 1 0 0 0 2 16382
476956 6 6 2 0 V22222 96 2 0 0 6 0 873 1 0 0 0 2 16382
476956 7 7 2 0 V222222 96 2 0 0 7 0 873 1 0 0 0 2 16382
476956 8 8 30 0 V30 1 30 0 0 8 0 873 1 0 0 0 30 16382
476956 9 9 30 0 V302 1 30 0 0 9 0 873 1 0 0 0 30 16382
476956 10 10 30 0 V3022 1 30 0 0 10 0 873 1 0 0 0 30 16382
476956 11 11 30 0 V30222 1 30 0 0 11 0 873 1 0 0 0 30 16382
476956 12 12 30 0 V302222 1 30 0 0 12 0 873 1 0 0 0 30 16382
476956 13 13 30 0 V3022222 1 30 0 0 13 0 873 1 0 0 0 30 16382
476956 14 14 100 0 PADDING 1 100 0 0 14 0 873 1 0 0 0 100 16382
14 rows selected.
-------------------------[Start Time: 2025/12/12 11:06:11]-------------------------
SQL> select segment_name,bytes/1024/1024 as MB from dba_segments
where owner='YSBAE'
and segment_name='T1'
order by bytes desc;
SEGMENT_NAME MB
-------------------------------------------------------------------------------------------------------------------------------- ---------
T1 358
1 rows selected.
2.set unused column
alter table t1 set unused column v2;
--0.41
2-2)메타데이터 확인
용량 변화 없음. dba_tab_columns 컬럼 정보 삭제. sys.col$에는 컬럼명 변경되어 정보가 보임.
더보기
SQL> select count(*) From t1;
COUNT(*)
---------
1000000
1 rows selected.
SQL> select * from dba_tab_columns
where table_name='T1';
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_MOD DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH CHAR_USED V80_FMT_IMAGE DATA_UPGRADED HISTOGRAM DEFAULT_ON_NULL IDENTITY_COLUMN SENSITIVE_COLUMN EVALUATION_EDITION UNUSABLE_BEFORE UNUSABLE_BEGINNING COLLATION
-------- ------------ ------------- ----------- ------------- ------------------ ----------- -------------- ---------- -------- --------- -------------- ---------------- ------------ -------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------- --------- --------- ----------- -------------------- ----------- -------------------------------------------- -------------------- ------------ ---------- ----------- ----------- --------- ------------- ------------- --------------- --------------- --------------- ---------------- -------------------- ---------------- ------------------- ---------------
YSBAE T1 ID NUMBER 22 Y 1 1000000 C102 C402 0.000001 0 1 2025/12/12 11:06:11 1000000 YES NO 5 0 NO YES NONE NO NO NO
YSBAE T1 V22 CHAR 2 Y 3 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V222 CHAR 2 Y 4 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V2222 CHAR 2 Y 5 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V22222 CHAR 2 Y 6 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V222222 CHAR 2 Y 7 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V30 VARCHAR2 30 Y 8 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V302 VARCHAR2 30 Y 9 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V3022 VARCHAR2 30 Y 10 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V30222 VARCHAR2 30 Y 11 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V302222 VARCHAR2 30 Y 12 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V3022222 VARCHAR2 30 Y 13 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 PADDING VARCHAR2 100 Y 14 1 78202020202020202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000 78202020202020202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 100 YES NO 101 100 B NO YES NONE NO NO NO USING_NLS_COMP
13 rows selected.
SQL> select * From sys.col$
where obj# in (select object_id from dba_objects where object_name='T1');
OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM EVALEDITION# UNUSABLEBEFORE# UNUSABLEBEGINNING# SPARE1 SPARE2 SPARE3 SPARE6 SPARE7 SPARE8 COLLID COLLINTCOL# SPARE9 SPARE10 SPARE11 SPARE12 SPARE13 ACDRRESCOL#
--------- --------- --------- ------------ --------- ---------- --------- --------- ------------ ---------- --------- --------- --------- --------- --------- --------- --------- ----------- ------------ --------------- ------------------ --------- --------- ----------- -------------------- --------- --------- --------- ----------- --------- --------- ---------- -------- -------------------- -----------
476956 1 1 22 0 ID 2 22 0 0 1 0 0 0 0 0 0 0
476956 2 2 2 0 SYS_C00002_25121211:10:13$ 96 2 0 0 2 32800 873 1 0 0 0 2 16382
476956 3 3 2 0 V22 96 2 0 0 3 0 873 1 0 0 0 2 16382
476956 4 4 2 0 V222 96 2 0 0 4 0 873 1 0 0 0 2 16382
476956 5 5 2 0 V2222 96 2 0 0 5 0 873 1 0 0 0 2 16382
476956 6 6 2 0 V22222 96 2 0 0 6 0 873 1 0 0 0 2 16382
476956 7 7 2 0 V222222 96 2 0 0 7 0 873 1 0 0 0 2 16382
476956 8 8 30 0 V30 1 30 0 0 8 0 873 1 0 0 0 30 16382
476956 9 9 30 0 V302 1 30 0 0 9 0 873 1 0 0 0 30 16382
476956 10 10 30 0 V3022 1 30 0 0 10 0 873 1 0 0 0 30 16382
476956 11 11 30 0 V30222 1 30 0 0 11 0 873 1 0 0 0 30 16382
476956 12 12 30 0 V302222 1 30 0 0 12 0 873 1 0 0 0 30 16382
476956 13 13 30 0 V3022222 1 30 0 0 13 0 873 1 0 0 0 30 16382
476956 14 14 100 0 PADDING 1 100 0 0 14 0 873 1 0 0 0 100 16382
14 rows selected.
-------------------------[Start Time: 2025/12/12 11:06:11]-------------------------
SQL> select segment_name,bytes/1024/1024 as MB from dba_segments
where owner='YSBAE'
and segment_name='T1'
order by bytes desc;
SEGMENT_NAME MB
-------------------------------------------------------------------------------------------------------------------------------- ---------
T1 358
1 rows selected.
##10046
SQL ID: 0w1jx821q59kg Plan Hash: 0
alter table t1 set unused column v2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.04 0.11 0 0 2 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.11 0 0 2 0
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 106
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 12 0.00 0.00
library cache pin 11 0.00 0.00
enq: IV - contention 61 0.00 0.02
row cache lock 4 0.00 0.00
enq: ZH - compression analysis 8 0.00 0.00
Compression analysis 16 0.00 0.00
KJC: Wait for msg sends to complete 1 0.00 0.00
reliable message 4 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 11.09 12.96
3)drop column
alter table t1 drop column v22;
--20.52
3-2)메타데이터확인
용량 변화 없음. dba_tab_columns 컬럼 정보 삭제. sys.col$에도 컬럼정보 삭제(기존에 unused 되었던 v2도 같이 삭제)
더보기
SQL> select count(*) From t1;
COUNT(*)
---------
1000000
1 rows selected.
SQL> select * from dba_tab_columns
where table_name='T1';
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_MOD DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH CHAR_USED V80_FMT_IMAGE DATA_UPGRADED HISTOGRAM DEFAULT_ON_NULL IDENTITY_COLUMN SENSITIVE_COLUMN EVALUATION_EDITION UNUSABLE_BEFORE UNUSABLE_BEGINNING COLLATION
-------- ------------ ------------- ----------- ------------- ------------------ ----------- -------------- ---------- -------- --------- -------------- ---------------- ------------ -------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------- --------- --------- ----------- -------------------- ----------- -------------------------------------------- -------------------- ------------ ---------- ----------- ----------- --------- ------------- ------------- --------------- --------------- --------------- ---------------- -------------------- ---------------- ------------------- ---------------
YSBAE T1 ID NUMBER 22 Y 1 1000000 C102 C402 0.000001 0 1 2025/12/12 11:06:11 1000000 YES NO 5 0 NO YES NONE NO NO NO
YSBAE T1 V222 CHAR 2 Y 4 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V2222 CHAR 2 Y 5 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V22222 CHAR 2 Y 6 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V222222 CHAR 2 Y 7 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V30 VARCHAR2 30 Y 8 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V302 VARCHAR2 30 Y 9 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V3022 VARCHAR2 30 Y 10 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V30222 VARCHAR2 30 Y 11 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V302222 VARCHAR2 30 Y 12 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V3022222 VARCHAR2 30 Y 13 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 PADDING VARCHAR2 100 Y 14 1 78202020202020202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000 78202020202020202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 100 YES NO 101 100 B NO YES NONE NO NO NO USING_NLS_COMP
12 rows selected.
SQL> select * From sys.col$
where obj# in (select object_id from dba_objects where object_name='T1');
OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM EVALEDITION# UNUSABLEBEFORE# UNUSABLEBEGINNING# SPARE1 SPARE2 SPARE3 SPARE6 SPARE7 SPARE8 COLLID COLLINTCOL# SPARE9 SPARE10 SPARE11 SPARE12 SPARE13 ACDRRESCOL#
--------- --------- --------- ------------ --------- ---------- --------- --------- ------------ ---------- --------- --------- --------- --------- --------- --------- --------- ----------- ------------ --------------- ------------------ --------- --------- ----------- -------------------- --------- --------- --------- ----------- --------- --------- ---------- -------- -------------------- -----------
476956 1 1 22 0 ID 2 22 0 0 1 0 0 0 0 0 0 0
476956 4 4 2 0 V222 96 2 0 0 4 0 873 1 0 0 0 2 16382
476956 5 5 2 0 V2222 96 2 0 0 5 0 873 1 0 0 0 2 16382
476956 6 6 2 0 V22222 96 2 0 0 6 0 873 1 0 0 0 2 16382
476956 7 7 2 0 V222222 96 2 0 0 7 0 873 1 0 0 0 2 16382
476956 8 8 30 0 V30 1 30 0 0 8 0 873 1 0 0 0 30 16382
476956 9 9 30 0 V302 1 30 0 0 9 0 873 1 0 0 0 30 16382
476956 10 10 30 0 V3022 1 30 0 0 10 0 873 1 0 0 0 30 16382
476956 11 11 30 0 V30222 1 30 0 0 11 0 873 1 0 0 0 30 16382
476956 12 12 30 0 V302222 1 30 0 0 12 0 873 1 0 0 0 30 16382
476956 13 13 30 0 V3022222 1 30 0 0 13 0 873 1 0 0 0 30 16382
476956 14 14 100 0 PADDING 1 100 0 0 14 0 873 1 0 0 0 100 16382
12 rows selected.
-------------------------[Start Time: 2025/12/12 11:06:11]-------------------------
SQL> select segment_name,bytes/1024/1024 as MB from dba_segments
where owner='YSBAE'
and segment_name='T1'
order by bytes desc;
SEGMENT_NAME MB
-------------------------------------------------------------------------------------------------------------------------------- ---------
T1 358
1 rows selected.
alter table t1 drop column v22
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 10.49 19.54 10931 45581 2041132 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 10.49 19.55 10931 45581 2041132 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
gc current grant 2-way 17562 0.01 2.14
undo segment extension 4 0.04 0.06
log file switch completion 4 0.01 0.03
latch: gc element 2 0.00 0.00
db file sequential read 556 0.00 0.16
gc cr grant 2-way 267 0.00 0.02
gc buffer busy release 1 0.00 0.00
gc cr multi block grant 174 0.00 0.06
db file scattered read 413 0.02 0.25
library cache lock 7 0.00 0.00
library cache pin 7 0.00 0.00
enq: IV - contention 88 0.00 0.03
row cache lock 16 0.00 0.00
enq: ZH - compression analysis 4 0.00 0.00
Compression analysis 20 0.00 0.00
reliable message 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 9.54 9.54
********************************************************************************
4) set unused + drop unused column
alter table t1 set unused column v222;
alter table t1 drop unused columns;
4-2) 메타데이터 확인
용량 변화 없음. dba_tab_columns 컬럼 정보 삭제. sys.col$에도 컬럼정보 삭제
더보기
SQL> select count(*) From t1;
COUNT(*)
---------
1000000
1 rows selected.
SQL> select * from dba_tab_columns
where table_name='T1';
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_MOD DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH CHAR_USED V80_FMT_IMAGE DATA_UPGRADED HISTOGRAM DEFAULT_ON_NULL IDENTITY_COLUMN SENSITIVE_COLUMN EVALUATION_EDITION UNUSABLE_BEFORE UNUSABLE_BEGINNING COLLATION
-------- ------------ ------------- ----------- ------------- ------------------ ----------- -------------- ---------- -------- --------- -------------- ---------------- ------------ -------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------- --------- --------- ----------- -------------------- ----------- -------------------------------------------- -------------------- ------------ ---------- ----------- ----------- --------- ------------- ------------- --------------- --------------- --------------- ---------------- -------------------- ---------------- ------------------- ---------------
YSBAE T1 ID NUMBER 22 Y 1 1000000 C102 C402 0.000001 0 1 2025/12/12 11:06:11 1000000 YES NO 5 0 NO YES NONE NO NO NO
YSBAE T1 V2222 CHAR 2 Y 5 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V22222 CHAR 2 Y 6 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V222222 CHAR 2 Y 7 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V30 VARCHAR2 30 Y 8 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V302 VARCHAR2 30 Y 9 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V3022 VARCHAR2 30 Y 10 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V30222 VARCHAR2 30 Y 11 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V302222 VARCHAR2 30 Y 12 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V3022222 VARCHAR2 30 Y 13 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 PADDING VARCHAR2 100 Y 14 1 78202020202020202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000 78202020202020202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 100 YES NO 101 100 B NO YES NONE NO NO NO USING_NLS_COMP
11 rows selected.
SQL> select * From sys.col$
where obj# in (select object_id from dba_objects where object_name='T1');
OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM EVALEDITION# UNUSABLEBEFORE# UNUSABLEBEGINNING# SPARE1 SPARE2 SPARE3 SPARE6 SPARE7 SPARE8 COLLID COLLINTCOL# SPARE9 SPARE10 SPARE11 SPARE12 SPARE13 ACDRRESCOL#
--------- --------- --------- ------------ --------- ---------- --------- --------- ------------ ---------- --------- --------- --------- --------- --------- --------- --------- ----------- ------------ --------------- ------------------ --------- --------- ----------- -------------------- --------- --------- --------- ----------- --------- --------- ---------- -------- -------------------- -----------
476956 1 1 22 0 ID 2 22 0 0 1 0 0 0 0 0 0 0
476956 5 5 2 0 V2222 96 2 0 0 5 0 873 1 0 0 0 2 16382
476956 6 6 2 0 V22222 96 2 0 0 6 0 873 1 0 0 0 2 16382
476956 7 7 2 0 V222222 96 2 0 0 7 0 873 1 0 0 0 2 16382
476956 8 8 30 0 V30 1 30 0 0 8 0 873 1 0 0 0 30 16382
476956 9 9 30 0 V302 1 30 0 0 9 0 873 1 0 0 0 30 16382
476956 10 10 30 0 V3022 1 30 0 0 10 0 873 1 0 0 0 30 16382
476956 11 11 30 0 V30222 1 30 0 0 11 0 873 1 0 0 0 30 16382
476956 12 12 30 0 V302222 1 30 0 0 12 0 873 1 0 0 0 30 16382
476956 13 13 30 0 V3022222 1 30 0 0 13 0 873 1 0 0 0 30 16382
476956 14 14 100 0 PADDING 1 100 0 0 14 0 873 1 0 0 0 100 16382
11 rows selected.
-------------------------[Start Time: 2025/12/12 11:06:11]-------------------------
SQL> select segment_name,bytes/1024/1024 as MB from dba_segments
where owner='YSBAE'
and segment_name='T1'
order by bytes desc;
SEGMENT_NAME MB
-------------------------------------------------------------------------------------------------------------------------------- ---------
T1 358
1 rows selected.
SQL ID: 2m6js0hpq6brt Plan Hash: 0
alter table t1 set unused column v222
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.06 0 0 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.06 0 0 1 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 1 0.00 0.00
library cache pin 1 0.00 0.00
enq: IV - contention 26 0.00 0.01
row cache lock 2 0.00 0.00
Compression analysis 8 0.00 0.00
reliable message 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 2.87 2.87
********************************************************************************
alter table t1 drop unused columns
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 5.76 10.50 0 45523 2020330 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 5.76 10.50 0 45523 2020330 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch: cache buffers lru chain 1 0.00 0.00
log file switch (checkpoint incomplete) 2 0.82 0.83
library cache lock 1 0.00 0.00
library cache pin 1 0.00 0.00
enq: IV - contention 72 0.00 0.03
row cache lock 13 0.00 0.00
Compression analysis 20 0.00 0.00
reliable message 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 4.06 4.06
********************************************************************************
5)set unused + drop unused (+checkpoint 10)
checkpoint n 옵션은 n행마다 처리시 commit 을 하게 유도하는 옵션입니다. 기본적으로 미사용시 한번의 commit으로 처리됩니다.
alter table t1 set unused column v2222;
alter table t1 drop unused columns checkpoint 10;
5-2)메타데이터 확인
용량 변화 없음. dba_tab_columns 컬럼 정보 삭제. sys.col$에도 컬럼정보 삭제 + 옵션 사용으로 인한 wait event가 많이 생
더보기
SQL> select count(*) From t1;
COUNT(*)
---------
1000000
1 rows selected.
SQL> select * from dba_tab_columns
where table_name='T1';
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_MOD DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH CHAR_USED V80_FMT_IMAGE DATA_UPGRADED HISTOGRAM DEFAULT_ON_NULL IDENTITY_COLUMN SENSITIVE_COLUMN EVALUATION_EDITION UNUSABLE_BEFORE UNUSABLE_BEGINNING COLLATION
-------- ------------ ------------- ----------- ------------- ------------------ ----------- -------------- ---------- -------- --------- -------------- ---------------- ------------ -------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------- --------- --------- ----------- -------------------- ----------- -------------------------------------------- -------------------- ------------ ---------- ----------- ----------- --------- ------------- ------------- --------------- --------------- --------------- ---------------- -------------------- ---------------- ------------------- ---------------
YSBAE T1 ID NUMBER 22 Y 1 1000000 C102 C402 0.000001 0 1 2025/12/12 11:06:11 1000000 YES NO 5 0 NO YES NONE NO NO NO
YSBAE T1 V22222 CHAR 2 Y 6 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V222222 CHAR 2 Y 7 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V30 VARCHAR2 30 Y 8 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V302 VARCHAR2 30 Y 9 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V3022 VARCHAR2 30 Y 10 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V30222 VARCHAR2 30 Y 11 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V302222 VARCHAR2 30 Y 12 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V3022222 VARCHAR2 30 Y 13 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 PADDING VARCHAR2 100 Y 14 1 78202020202020202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000 78202020202020202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 100 YES NO 101 100 B NO YES NONE NO NO NO USING_NLS_COMP
10 rows selected.
SQL> select * From sys.col$
where obj# in (select object_id from dba_objects where object_name='T1');
OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM EVALEDITION# UNUSABLEBEFORE# UNUSABLEBEGINNING# SPARE1 SPARE2 SPARE3 SPARE6 SPARE7 SPARE8 COLLID COLLINTCOL# SPARE9 SPARE10 SPARE11 SPARE12 SPARE13 ACDRRESCOL#
--------- --------- --------- ------------ --------- ---------- --------- --------- ------------ ---------- --------- --------- --------- --------- --------- --------- --------- ----------- ------------ --------------- ------------------ --------- --------- ----------- -------------------- --------- --------- --------- ----------- --------- --------- ---------- -------- -------------------- -----------
476956 1 1 22 0 ID 2 22 0 0 1 0 0 0 0 0 0 0
476956 6 6 2 0 V22222 96 2 0 0 6 0 873 1 0 0 0 2 16382
476956 7 7 2 0 V222222 96 2 0 0 7 0 873 1 0 0 0 2 16382
476956 8 8 30 0 V30 1 30 0 0 8 0 873 1 0 0 0 30 16382
476956 9 9 30 0 V302 1 30 0 0 9 0 873 1 0 0 0 30 16382
476956 10 10 30 0 V3022 1 30 0 0 10 0 873 1 0 0 0 30 16382
476956 11 11 30 0 V30222 1 30 0 0 11 0 873 1 0 0 0 30 16382
476956 12 12 30 0 V302222 1 30 0 0 12 0 873 1 0 0 0 30 16382
476956 13 13 30 0 V3022222 1 30 0 0 13 0 873 1 0 0 0 30 16382
476956 14 14 100 0 PADDING 1 100 0 0 14 0 873 1 0 0 0 100 16382
10 rows selected.
-------------------------[Start Time: 2025/12/12 11:06:11]-------------------------
SQL> select segment_name,bytes/1024/1024 as MB from dba_segments
where owner='YSBAE'
and segment_name='T1'
order by bytes desc;
SEGMENT_NAME MB
-------------------------------------------------------------------------------------------------------------------------------- ---------
T1 358
1 rows selected.
********************************************************************************
SQL ID: 3yunmjfq7wqsq Plan Hash: 0
alter table t1 set unused column v2222
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.06 0 0 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.06 0 0 1 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 1 0.00 0.00
library cache pin 1 0.00 0.00
enq: IV - contention 26 0.00 0.01
row cache lock 2 0.00 0.00
Compression analysis 8 0.00 0.00
reliable message 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 3.39 3.39
********************************************************************************
SQL ID: 9xftn29y5jyxm Plan Hash: 0
alter table t1 drop unused columns checkpoint 10
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 69.63 349.40 2 7945440 4804649 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 69.63 349.40 2 7945440 4804649 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 8 0.00 0.00
library cache pin 98639 0.10 30.04
enq: IV - contention 398666 0.10 177.97
row cache lock 12 0.00 0.00
latch: ges resource hash list 419 0.00 0.02
db file sequential read 2 0.03 0.03
PGA memory operation 2205 0.00 0.06
KJC: Wait for msg sends to complete 26 0.00 0.00
latch free 13 0.00 0.00
latch: shared pool 1 0.00 0.00
enq: ZH - compression analysis 6 0.00 0.00
Compression analysis 20 0.00 0.00
reliable message 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 6054.15 6054.15
********************************************************************************6)set unused + online reorg
alter table t1 set unused column v22222;
alter table t1 move tablespace users online;
6-2)메타데이터 확인
용량 확보 됨. dba_tab_columns 정보 삭제됨. sys.col$ 정보 남아있음.
더보기
SQL> select count(*) From t1;
COUNT(*)
---------
1000000
1 rows selected.
SQL> select * from dba_tab_columns
where table_name='T1';
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_MOD DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH CHAR_USED V80_FMT_IMAGE DATA_UPGRADED HISTOGRAM DEFAULT_ON_NULL IDENTITY_COLUMN SENSITIVE_COLUMN EVALUATION_EDITION UNUSABLE_BEFORE UNUSABLE_BEGINNING COLLATION
-------- ------------ ------------- ----------- ------------- ------------------ ----------- -------------- ---------- -------- --------- -------------- ---------------- ------------ -------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------- --------- --------- ----------- -------------------- ----------- -------------------------------------------- -------------------- ------------ ---------- ----------- ----------- --------- ------------- ------------- --------------- --------------- --------------- ---------------- -------------------- ---------------- ------------------- ---------------
YSBAE T1 ID NUMBER 22 Y 1 1000000 C102 C402 0.000001 0 1 2025/12/12 11:06:11 1000000 YES NO 5 0 NO YES NONE NO NO NO
YSBAE T1 V222222 CHAR 2 Y 7 1 5858 5858 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 2 YES NO 3 2 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V30 VARCHAR2 30 Y 8 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V302 VARCHAR2 30 Y 9 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V3022 VARCHAR2 30 Y 10 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V30222 VARCHAR2 30 Y 11 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V302222 VARCHAR2 30 Y 12 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 V3022222 VARCHAR2 30 Y 13 1 202020202020202020202020202020202020202020202020202020202078 202020202020202020202020202020202020202020202020202020202078 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 30 YES NO 31 30 B NO YES NONE NO NO NO USING_NLS_COMP
YSBAE T1 PADDING VARCHAR2 100 Y 14 1 78202020202020202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000 78202020202020202020202020202020202020202020202020202020202020200000000000000000000000000000000000000000000000000000000000000000 1 0 1 2025/12/12 11:06:11 1000000 CHAR_CS 100 YES NO 101 100 B NO YES NONE NO NO NO USING_NLS_COMP
10 rows selected.
SQL> select * From sys.col$
where obj# in (select object_id from dba_objects where object_name='T1');
OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM EVALEDITION# UNUSABLEBEFORE# UNUSABLEBEGINNING# SPARE1 SPARE2 SPARE3 SPARE6 SPARE7 SPARE8 COLLID COLLINTCOL# SPARE9 SPARE10 SPARE11 SPARE12 SPARE13 ACDRRESCOL#
--------- --------- --------- ------------ --------- ---------- --------- --------- ------------ ---------- --------- --------- --------- --------- --------- --------- --------- ----------- ------------ --------------- ------------------ --------- --------- ----------- -------------------- --------- --------- --------- ----------- --------- --------- ---------- -------- -------------------- -----------
476956 1 1 22 0 ID 2 22 0 0 1 0 0 0 0 0 0 0
476956 6 6 2 0 SYS_C00002_25121213:05:06$ 96 2 0 0 6 32800 873 1 0 0 0 2 16382
476956 7 7 2 0 V222222 96 2 0 0 7 0 873 1 0 0 0 2 16382
476956 8 8 30 0 V30 1 30 0 0 8 0 873 1 0 0 0 30 16382
476956 9 9 30 0 V302 1 30 0 0 9 0 873 1 0 0 0 30 16382
476956 10 10 30 0 V3022 1 30 0 0 10 0 873 1 0 0 0 30 16382
476956 11 11 30 0 V30222 1 30 0 0 11 0 873 1 0 0 0 30 16382
476956 12 12 30 0 V302222 1 30 0 0 12 0 873 1 0 0 0 30 16382
476956 13 13 30 0 V3022222 1 30 0 0 13 0 873 1 0 0 0 30 16382
476956 14 14 100 0 PADDING 1 100 0 0 14 0 873 1 0 0 0 100 16382
10 rows selected.
SQL> select segment_name,bytes/1024/1024 as MB from dba_segments
where owner='YSBAE'
and segment_name='T1'
order by bytes desc;
SEGMENT_NAME MB
-------------------------------------------------------------------------------------------------------------------------------- ---------
T1 328
1 rows selected.
********************************************************************************
SQL ID: fk9azk33kyt4y Plan Hash: 0
alter table t1 set unused column v22222
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.06 0 0 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.06 0 0 1 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 5 0.00 0.00
library cache pin 5 0.00 0.00
enq: IV - contention 31 0.00 0.01
row cache lock 2 0.00 0.00
enq: ZH - compression analysis 4 0.00 0.00
Compression analysis 8 0.00 0.00
reliable message 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 2.16 2.16
********************************************************************************
SQL ID: 490af6q6y6bsy Plan Hash: 763094079
alter table t1 move tablespace users online
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.04 0 0 9 0
Execute 2 3.32 9.05 41968 88685 107625 2000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.33 9.09 41968 88685 107634 2000000
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 106
Number of plan statistics captured: 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT T1 (cr=44338 pr=20980 pw=45385 time=4314366 us starts=1)
1000000 1000000 1000000 TABLE ACCESS FULL T1 (cr=43580 pr=20972 pw=0 time=888928 us starts=1 cost=12417 si
ze=307000000 card=1000000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 10 0.00 0.00
library cache pin 10 0.00 0.00
enq: IV - contention 177 0.00 0.07
row cache lock 41 0.00 0.01
reliable message 53 0.00 0.03
gc cr multi block grant 27 0.00 0.00
db file scattered read 65 0.00 0.03
gc cr multi block mixed 10 0.00 0.00
PGA memory operation 300 0.00 0.00
db file sequential read 139 0.01 0.10
acknowledge over PGA limit 46 0.02 0.46
gc cr disk read 51 0.00 0.00
gc current multi block request 129 0.00 0.04
local write wait 128 0.00 0.14
gc current grant busy 1 0.00 0.00
gc current grant 2-way 26 0.00 0.00
log file switch completion 6 0.10 0.23
direct path write 9 0.00 0.02
log file switch (checkpoint incomplete) 4 1.11 1.63
enq: TS - contention 8 0.00 0.00
enq: RO - fast object reuse 24 0.03 0.04
enq: CR - block range reuse ckpt 10 0.00 0.01
enq: TM - contention 2 0.00 0.00
log file sync 2 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 4.75 4.75
enq: KO - fast object checkpoint 3 0.00 0.00
direct path read 39 0.04 0.52
log buffer space 2 0.00 0.00
********************************************************************************
저의 생각을 결론으로 마무리하자면..
drop column에 대한 이점은 분명히 있지만, 트랜잭션이 계속 발생하는 테이블에 대해서 작업을 하기에는 부담이 있습니다..
용량에 대한 부담이 없는 DB이고, 트랜잭션이 계속 발생하는 테이블이라면 unused로 처리 후 추후에 drop unused column으로 물리적 삭제까지 하는것이 최선으로 보입니다. 용량확보까지 하려면 reorg까지 같이 해주면 깔끔하게 마무리 될거 같네요.
출처 : https://www.red-gate.com/simple-talk/databases/oracle-databases/dropping-columns/
반응형
'Oracle > 운영' 카테고리의 다른 글
| ORA-01450: Maximum Key Length (6398) Exceeded (3) | 2025.12.19 |
|---|---|
| lob의 용량관리 (deduplicate 와 compress) (0) | 2025.12.17 |
| data type을 확인하는 dump 함수 사용법 (0) | 2025.10.30 |
| lob 컬럼 move tablespace 명령어 (0) | 2025.07.17 |
| SQL loader 사용시 ORA-00054 발생과 예방 (0) | 2025.06.02 |
댓글