반응형
일반적으로 컬럼을 삭제할 때는 ALTER TABLE ... DROP COLUMN을 사용합니다.
하지만 테이블이 현재 트랜잭션이 발생해서 사용중(Online) 상태라면? drop 컬럼은 사용할 수 없을 것입니다.
그때 사용할 수 있는 방법이 UNUSED 인데요. 컬럼을 사용하지 않는다고 mark하는 것입니다. mark만 하기 때문에 용량이나 블록은 그대로이지만 딕셔너리뷰나 테이블에서 조회시에는 drop된것과 동일하게 보입니다.
그리고 트랜잭션이 없을때 UNUSED된 컬럼을 삭제하면 된다고 하는데, 24시간 사용중인 테이블이라면 별 효과가 없을 것같다는 생각을 했습니다.
이번 글은 unused 컬럼에 대해서 여러가지 테스트를 해보면서 비교해보려고 합니다.
1) drop column과 unused 후 drop시 실제로 exclusive lock이 걸리는지?
테이블 확인
SQL> select count(*) from emp;
COUNT(*)
---------
14,680,064
1 rows selected.
SQL> select bytes/1024/1024 as MB from dba_segments where segment_name='EMP' and owner='DEV';
MB
---------
596
1 rows selected.drop column
alter table DEV.EMP drop column ENAME;
확인명령어
EMP에 대해 exclusive lock 이 발생하였습니다. 작업 중에 들어오는 트랜잭션은 대기하게 됩니다.
SQL> select lo.XIDUSN,lo.XIDSLOT,lo.XIDSQN,do.object_name,lo.session_id,lo.process
,DECODE(lo.locked_mode, 0, 'none', 1, 'null', 2, 'row-share(RS)', 3, 'row-exclusive(SX)'
, 4, 'share(S)', 5, 'share row exclusive(SRX)'
, 6, 'exclusive(X)') as lock_mode
from v$locked_object lo,dba_objects do
where lo.object_id=do.object_id;
XIDUSN XIDSLOT XIDSQN OBJECT_NAME SESSION_ID PROCESS LOCK_MODE
--------- --------- --------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------------------------------- ------------------------
130 26 8328 WRI$_OPTSTAT_HISTHEAD_HISTORY 1901 56033588 row-exclusive(SX)
130 26 8328 WRI$_OPTSTAT_HISTGRM_HISTORY 1901 56033588 row-exclusive(SX)
130 26 8328 EMP 1901 56033588 exclusive(X)
3 rows selected.
unused
alter table DEV.EMP set unused column HIREDATE;
SQL> select * from dba_unused_col_tabs
where owner='DEV';
OWNER TABLE_NAME COUNT
----- -------------------------------------------------------------------------------------------------------------------------------- ---------
DEV EMP 1
1 rows selected.
drop unused columns
alter table DEV.EMP drop unused columns;확인명령어
마찬가지로 exclusive lock이 발생합니다. 해당 방법도 online으로 사용하기엔 무리가 있습니다.
SQL> select lo.XIDUSN,lo.XIDSLOT,lo.XIDSQN,do.object_name,lo.session_id,lo.process
,DECODE(lo.locked_mode, 0, 'none', 1, 'null', 2, 'row-share(RS)', 3, 'row-exclusive(SX)'
, 4, 'share(S)', 5, 'share row exclusive(SRX)'
, 6, 'exclusive(X)') as lock_mode
from v$locked_object lo,dba_objects do
where lo.object_id=do.object_id;
XIDUSN XIDSLOT XIDSQN OBJECT_NAME SESSION_ID PROCESS LOCK_MODE
--------- --------- --------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------------------------------- ------------------------
65 7 17643 WRI$_OPTSTAT_HISTHEAD_HISTORY 1901 56033588 row-exclusive(SX)
65 7 17643 WRI$_OPTSTAT_HISTGRM_HISTORY 1901 56033588 row-exclusive(SX)
65 7 17643 EMP 1901 56033588 exclusive(X)
3 rows selected.
2) unused와 invisible 을 수행한 상태에서 expdp/impdp하고 상태 확인하기.
테이블에 unused 와 invisible 수행
작업 전
SQL> select table_name,column_name,column_id From dba_tab_columns where table_name='EMP' and owner='DEV';
TABLE_NAME COLUMN_NAME COLUMN_ID
----------------- --------------------------------
EMP EMPNO 1
EMP ENAME 2
EMP JOB 3
EMP MGR 4
EMP HIREDATE 5
EMP SAL 6
EMP COMM 7
EMP DEPTNO 8
8 rows selected.alter table DEV.EMP set unused column sal;
alter table DEV.EMP modify mgr invisible;확인
unused 된 컬럼은 dba_tab_columns에 나타나지 않고, invisible 컬럼은 column_id가 null로 변경되어서 보입니다.
SQL> select * from dba_unused_col_tabs
where owner='DEV';
OWNER TABLE_NAME COUNT
----- -------------------------------------------------------------------------------------------------------------------------------- ---------
DEV EMP 1
1 rows selected.
SQL> select table_name,column_name,column_id From dba_tab_columns where table_name='EMP' and owner='DEV';
TABLE_NAME COLUMN_NAME COLUMN_ID
------------------------------ --------------------------- ---------
EMP EMPNO 1
EMP ENAME 2
EMP JOB 3
EMP MGR
EMP HIREDATE 4
EMP COMM 5
EMP DEPTNO 6
7 rows selected.
expdp
expdp '"/ as sysdba"' directory=PUBLIC_EXEC_DIR dumpfile=EMP_UNUSED_INVISIBLE.dmp logfile=EMP_UNUSED_INVISIBLE.log tables=DEV.EMP
impdp
remap table을 이용하여 새 테이블로 이관
impdp '"/ as sysdba"' directory=PUBLIC_EXEC_DIR dumpfile=EMP_UNUSED_INVISIBLE.dmp logfile=EMP_UNUSED_INVISIBLE_imp.log tables=DEV.EMP remap_table=DEV.EMP:EMP_UNUSED_INVISIBLE확인
impdp 된 테이블은 unused 상태가 없습니다. 즉 컬럼이 drop 된것으로 판단하고 이관되었습니다.
invisible상태는 그대로 가지고 이관되었습니다.
SQL> select table_name,column_name,column_id From dba_tab_columns where table_name='EMP_UNUSED_INVISIBLE' and owner='DEV';
TABLE_NAME COLUMN_NAME COLUMN_ID
----------------------------------- -------------------- ---------
EMP_UNUSED_INVISIBLE MGR
EMP_UNUSED_INVISIBLE EMPNO 1
EMP_UNUSED_INVISIBLE ENAME 2
EMP_UNUSED_INVISIBLE JOB 3
EMP_UNUSED_INVISIBLE HIREDATE 4
EMP_UNUSED_INVISIBLE COMM 5
EMP_UNUSED_INVISIBLE DEPTNO 6
7 rows selected.
확인
SQL> select * from dba_unused_col_tabs
where owner='DEV';
OWNER TABLE_NAME COUNT
----- -------------------------------------------------------------------------------------------------------------------------------- ---------
DEV EMP 1
1 rows selected.
visible 명령어도 잘 수행됩니다.
alter table DEV.EMP_UNUSED_INVISIBLE modify mgr visible;
SQL> select table_name,column_name,column_id From dba_tab_columns where table_name='EMP_UNUSED_INVISIBLE' and owner='DEV';
TABLE_NAME COLUMN_NAME COLUMN_ID
---------------------------- ------------------- ---------
EMP_UNUSED_INVISIBLE MGR 7
EMP_UNUSED_INVISIBLE EMPNO 1
EMP_UNUSED_INVISIBLE ENAME 2
EMP_UNUSED_INVISIBLE JOB 3
EMP_UNUSED_INVISIBLE HIREDATE 4
EMP_UNUSED_INVISIBLE COMM 5
EMP_UNUSED_INVISIBLE DEPTNO 6
용량의 이슈가 없다면 , 계속 online 상태의 테이블이라면 drop column 대신 unused로 사용해서 mark만 해놓으면 drop column과 동일한 상태로 사용할 수 있습니다.
그리고 ununsed 된 컬럼은 동일한 컬럼명으로 다시 add도 가능합니다.
SQL> alter table DEV.EMP set unused column sal;
SQL> select table_name,column_name,column_id From dba_tab_columns where table_name='EMP' and owner='DEV';
TABLE_NAME COLUMN_NAME COLUMN_ID
--------------------------- ---------------------- ---------
EMP EMPNO 1
EMP ENAME 2
EMP JOB 3
EMP MGR 4
EMP HIREDATE 5
EMP COMM 6
EMP DEPTNO 7
SQL> alter table DEV.EMP add (SAL NUMBER(7,2));
SQL> select table_name,column_name,column_id From dba_tab_columns where table_name='EMP' and owner='DEV';
TABLE_NAME COLUMN_NAME COLUMN_ID
------------------- ------------------ ---------
EMP EMPNO 1
EMP ENAME 2
EMP JOB 3
EMP MGR 4
EMP HIREDATE 5
EMP COMM 6
EMP DEPTNO 7
EMP SAL 8
반응형
'Oracle > 운영' 카테고리의 다른 글
| 통계정보 restore 하기 (0) | 2023.10.05 |
|---|---|
| ddl_lock_timeout 과 ORA-00054 : resource busy (0) | 2023.07.24 |
| [ACL,UTL_SMTP] 10g,11g 메일링 서비스 관련 이슈 (0) | 2023.06.16 |
| 큰 트랜잭션이 취소됐을때 확인 법과 대처법 (0) | 2023.05.19 |
| [ASM] diskgroup에 disk 추가하기 (0) | 2023.05.16 |
댓글