테이블스페이스 datafile의 경로 및 이름 변경이 필요한 경우 move datafile 명령을 통해 online으로 작업 가능합니다.
작업 진행 시 해당 datafile을 사용 중인 테이블에 DML, DDL 시 lock wait 발생
테스트 입니다
테스트 전 확인
-- 세그먼트 확인
SELECT owner, segment_name, round(bytes/1024/1024) size_mb, tablespace_name
FROM dba_segments
WHERE owner = 'SK'
OWNER|SEGMENT_NAME|SIZE_MB|TABLESPACE_NAME|
-----+------------+-------+---------------+
SK |TAB1 | 720|TS01 |
SK |TAB1_IX01 | 88|TS01 |
2 row(s) fetched.
-- move 전 datafile 확인
SELECT B.FILE_ID, B.FILE_NAME,
B.TABLESPACE_NAME,
B.BYTES / 1024/1024 "TOTAL SIZE(MB)",
((B.BYTES - SUM(NVL(A.BYTES,0)))) /1024/1024 "USED(MB)",
(SUM(NVL(A.BYTES,0))) /1024/1024 "FREE SIZE(MB)",
ROUND((1 - (SUM(NVL(A.BYTES,0)) / (B.BYTES))) * 100,2) "USED_%"
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID(+) = B.FILE_ID AND B.TABLESPACE_NAME = 'TS01'
GROUP BY B.TABLESPACE_NAME, B.FILE_ID, B.FILE_NAME, B.BYTES
ORDER BY B.TABLESPACE_NAME, B.FILE_ID
FILE_ID|FILE_NAME |TABLESPACE_NAME|TOTAL SIZE(MB)|USED(MB)|FREE SIZE(MB)|USED_%|
-------+-------------------------+---------------+--------------+--------+-------------+------+
2|/data01/ora19ent/ts01.dbf|TS01 | 5120| 809| 4311| 15.8|
1 row(s) fetched.
move datafile 실행
file_id는 변경없이 file_name만 변경됨 - 실제 파일은 복사 후 기존 파일 삭제로 진행됨(추가 용량 확보 필요)
-- move datafile 실행
alter database move datafile '/data01/ora19ent/ts01.dbf' TO '/data01/ora19ent/ts01_11.dbf'
--# move 후 datafile 확인
SELECT B.FILE_ID, B.FILE_NAME,
B.TABLESPACE_NAME,
B.BYTES / 1024/1024 "TOTAL SIZE(MB)",
((B.BYTES - SUM(NVL(A.BYTES,0)))) /1024/1024 "USED(MB)",
(SUM(NVL(A.BYTES,0))) /1024/1024 "FREE SIZE(MB)",
ROUND((1 - (SUM(NVL(A.BYTES,0)) / (B.BYTES))) * 100,2) "USED_%"
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID(+) = B.FILE_ID AND B.TABLESPACE_NAME = 'TS01'
GROUP BY B.TABLESPACE_NAME, B.FILE_ID, B.FILE_NAME, B.BYTES
ORDER BY B.TABLESPACE_NAME, B.FILE_ID
FILE_ID|FILE_NAME |TABLESPACE_NAME|TOTAL SIZE(MB)|USED(MB)|FREE SIZE(MB)|USED_%|
-------+----------------------------+---------------+--------------+--------+-------------+------+
2|/data01/ora19ent/ts01_11.dbf|TS01 | 5120| 809| 4311| 15.8|
1 row(s) fetched.
--# du 명령으로 OS에서 파일 사이즈 체크
[ora19ent@svr ora19ent]$ while true; do echo ''; du -sh ts*; sleep 1; done
5.1G ts01.dbf
5.1G ts01.dbf
105M ts01_11.dbf
5.1G ts01.dbf
476M ts01_11.dbf
5.1G ts01.dbf
827M ts01_11.dbf
...
5.1G ts01.dbf
5.1G ts01_11.dbf
5.1G ts01_11.dbf
--# move datafile 실행 시 alert.log 메세지
2024-09-16T06:01:12.026824+09:00
alter database move datafile '/data01/ora19ent/ts01.dbf' TO '/data01/ora19ent/ts01_11.dbf'
2024-09-16T06:01:12.028944+09:00
Moving datafile /data01/ora19ent/ts01.dbf (2) to /data01/ora19ent/ts01_11.dbf
...
...
2024-09-16T06:02:23.283233+09:00
Move operation committed for file /data01/ora19ent/ts01_11.dbf
2024-09-16T06:02:26.582918+09:00
Completed: alter database move datafile '/data01/ora19ent/ts01.dbf' TO '/data01/ora19ent/ts01_11.dbf'
테이블 DML 테스트
DML 수행 시 작업 관련 lock wait은 확인되지 않았음
테이블 DDL 수행 시 작업 관련 lock wait은 확인되지 않았음(여러번 반복
테스트시에도 동일함)
--# 1초 주기로 DML수행
update sk.tab1
set object_id = '9999'
where owner = 'SYSTEM'
;
rollback
;
--# lock wait 모니터링
####################################
Mon Sep 16 06:02:24 KST 2024
SESS_INFO OBJ_NM OBJ_TYPE CMD STATE SQL_ID EVENT LMODE BLOCKING_SESSION
--------------- --------------- --------------- ---------- ------------------- ------------- ------------------------------ --------------- ----------------
184,58430,@1 SK.TAB1 TABLE UPDATE WAITED SHORT TIME avzrkt3j3rq19 db file sequential read Row-X (SX)
1 row selected.
Elapsed: 00:00:00.03
####################################
Mon Sep 16 06:02:25 KST 2024
SESS_INFO OBJ_NM OBJ_TYPE CMD STATE SQL_ID EVENT LMODE BLOCKING_SESSION
--------------- --------------- --------------- ---------- ------------------- ------------- ------------------------------ --------------- ----------------
184,58430,@1 SK.TAB1 TABLE UPDATE WAITING avzrkt3j3rq19 read by other session Row-X (SX) 41
1 row selected.
Elapsed: 00:00:00.05
####################################
Mon Sep 16 06:02:26 KST 2024
no rows selected
Elapsed: 00:00:00.01
####################################
Mon Sep 16 06:02:27 KST 2024
SESS_INFO OBJ_NM OBJ_TYPE CMD STATE SQL_ID EVENT LMODE BLOCKING_SESSION
--------------- --------------- --------------- ---------- ------------------- ------------- ------------------------------ --------------- ----------------
184,39087,@1 SK.TAB1 TABLE UNknown WAITED SHORT TIME SQL*Net message from client Row-X (SX)
1 row selected.
Elapsed: 00:00:00.04
테이블 DDL 테스트
-- ADD 컬럼
ALTER TABLE sk.tab1 ADD (col1 varchar(10)) ;
Table altered.
Elapsed: 00:00:00.01
-- DROP 컬럼
ALTER TABLE sk.tab1 drop (col1) ;
Table altered.
Elapsed: 00:00:04.99
--# lock wait 모니터링
####################################
Mon Sep 16 06:25:28 KST 2024
no rows selected
Elapsed: 00:00:00.00
####################################
Mon Sep 16 06:25:29 KST 2024
SESS_INFO OBJ_NM OBJ_TYPE CMD STATE SQL_ID EVENT LMODE BLOCKING_SESSION
--------------- --------------- --------------- --------------- ------------------- ------------- ------------------------------ --------------- ----------------
184,10867,@1 SYS.WRI$_OPTSTA TABLE ALTER TABLE WAITED SHORT TIME 1va4hbazm6fms db file scattered read Row-X (SX)
T_HISTHEAD_HIST
ORY
184,10867,@1 SYS.WRI$_OPTSTA TABLE ALTER TABLE WAITED SHORT TIME 1va4hbazm6fms db file scattered read Row-X (SX)
T_HISTGRM_HISTO
RY
184,10867,@1 SK.TAB1 TABLE ALTER TABLE WAITED SHORT TIME 1va4hbazm6fms db file scattered read Exclusive
3 rows selected.
Elapsed: 00:00:00.05
####################################
Mon Sep 16 06:25:31 KST 2024
SESS_INFO OBJ_NM OBJ_TYPE CMD STATE SQL_ID EVENT LMODE BLOCKING_SESSION
--------------- --------------- --------------- --------------- ------------------- ------------- ------------------------------ --------------- ----------------
184,10867,@1 SYS.WRI$_OPTSTA TABLE ALTER TABLE WAITED SHORT TIME 1va4hbazm6fms db file scattered read Row-X (SX)
T_HISTHEAD_HIST
ORY
184,10867,@1 SYS.WRI$_OPTSTA TABLE ALTER TABLE WAITED SHORT TIME 1va4hbazm6fms db file scattered read Row-X (SX)
T_HISTGRM_HISTO
RY
184,10867,@1 SK.TAB1 TABLE ALTER TABLE WAITED SHORT TIME 1va4hbazm6fms db file scattered read Exclusive
3 rows selected.
Elapsed: 00:00:00.05
####################################
Mon Sep 16 06:25:33 KST 2024
SESS_INFO OBJ_NM OBJ_TYPE CMD STATE SQL_ID EVENT LMODE BLOCKING_SESSION
--------------- --------------- --------------- --------------- ------------------- ------------- ------------------------------ --------------- ----------------
184,10867,@1 SYS.WRI$_OPTSTA TABLE ALTER TABLE WAITED SHORT TIME 1va4hbazm6fms db file scattered read Row-X (SX)
T_HISTHEAD_HIST
ORY
184,10867,@1 SYS.WRI$_OPTSTA TABLE ALTER TABLE WAITED SHORT TIME 1va4hbazm6fms db file scattered read Row-X (SX)
T_HISTGRM_HISTO
RY
184,10867,@1 SK.TAB1 TABLE ALTER TABLE WAITED SHORT TIME 1va4hbazm6fms db file scattered read Exclusive
3 rows selected.
Elapsed: 00:00:00.06
####################################
Mon Sep 16 06:25:34 KST 2024
no rows selected
Elapsed: 00:00:00.00
KEEP 옵션 사용 테스트
KEEP 옵션 사용 시 기존 datafile이 삭제되지 않고 남아있음
--# keep 옵션
> SELECT B.FILE_ID, B.FILE_NAME,
B.TABLESPACE_NAME,
B.BYTES / 1024/1024 "TOTAL SIZE(MB)",
((B.BYTES - SUM(NVL(A.BYTES,0)))) /1024/1024 "USED(MB)",
(SUM(NVL(A.BYTES,0))) /1024/1024 "FREE SIZE(MB)",
ROUND((1 - (SUM(NVL(A.BYTES,0)) / (B.BYTES))) * 100,2) "USED_%"
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID(+) = B.FILE_ID AND B.TABLESPACE_NAME = 'TS01'
GROUP BY B.TABLESPACE_NAME, B.FILE_ID, B.FILE_NAME, B.BYTES
ORDER BY B.TABLESPACE_NAME, B.FILE_ID
FILE_ID|FILE_NAME |TABLESPACE_NAME|TOTAL SIZE(MB)|USED(MB)|FREE SIZE(MB)|USED_%|
-------+----------------------------+---------------+--------------+--------+-------------+------+
2|/data01/ora19ent/ts01_11.dbf|TS01 | 5120| 809| 4311| 15.8|
1 row(s) fetched.
> alter database move datafile '/data01/ora19ent/ts01_11.dbf' TO '/data01/ora19ent/ts01.dbf' keep
0 row(s) modified.
> SELECT B.FILE_ID, B.FILE_NAME,
B.TABLESPACE_NAME,
B.BYTES / 1024/1024 "TOTAL SIZE(MB)",
((B.BYTES - SUM(NVL(A.BYTES,0)))) /1024/1024 "USED(MB)",
(SUM(NVL(A.BYTES,0))) /1024/1024 "FREE SIZE(MB)",
ROUND((1 - (SUM(NVL(A.BYTES,0)) / (B.BYTES))) * 100,2) "USED_%"
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID(+) = B.FILE_ID AND B.TABLESPACE_NAME = 'TS01'
GROUP BY B.TABLESPACE_NAME, B.FILE_ID, B.FILE_NAME, B.BYTES
ORDER BY B.TABLESPACE_NAME, B.FILE_ID
FILE_ID|FILE_NAME |TABLESPACE_NAME|TOTAL SIZE(MB)|USED(MB)|FREE SIZE(MB)|USED_%|
-------+-------------------------+---------------+--------------+--------+-------------+------+
2|/data01/ora19ent/ts01.dbf|TS01 | 5120| 809| 4311| 15.8|
1 row(s) fetched.
[ora19ent@svr ora19ent]$ ls -al
total 10506272
drwxrwxr-x. 2 ora19ent ora19ent 79 Sep 16 06:43 .
drwxrwxrwx. 3 root root 22 Sep 14 10:12 ..
-rw-rw----. 1 ora19ent ora19ent 5368717312 Sep 16 06:43 ts01.dbf
-rw-rw----. 1 ora19ent ora19ent 5368717312 Sep 16 06:43 ts01_11.dbf
댓글 쓰기