OS 환경 : Oracle Linux 8.8 (64bit)
DB 환경 : Oracle Database 23.7.0.25.01 ai for Oracle Cloud and Engineered Systems
방법 : 오라클 23ai 신기능 smallfile 테이블스페이스 shrink
오라클 23ai부터 bigfile 테이블스페이스에 대해 shrink 가 가능해짐
참고 : 오라클 23ai 신기능 bigfile 테이블스페이스 shrink ( https://positivemh.tistory.com/1169 )
그리고 23.7 버전부터 smallfile 테이블스페이스도 shrink가 가능해짐
이 기능은 테이블스페이스 단위로 reorg를 진행하는 기능임
기존에는 데이터파일의 크기를 줄이고 싶은 경우 바로 resize 명령을 실행하면 아래 에러가 발생했음
ORA-03297: file contains used data beyond requested RESIZE value
그렇기 때문에 데이터파일 resize 전 테이블이나 인덱스 단위로 move 또는 rebuild 를 통해 공간을 회수 해주었어야 했음
23ai 부터 사용가능한 dbms_space.shrink_tablespace 프로시저를 사용하면 이 기능들을 한번에 수행해줌
참고로 sysaux 테이블스페이스도 이 기능으로 shrink가 가능함
유의사항 : 이 기능을 사용해 shrink를 하게 되면 여유공간이 없어지기 때문에 필요한 만큼 수동으로 크기를 늘려주거나 autoextend를 on으로 해놓아야함
본문에서는 10gb 크기의 테이블스페이스를 만든 뒤 데이터 삽입 및 삭제 후 이 기능을 테스트해봄
사용법
|
1
2
3
4
5
|
DBMS_SPACE.SHRINK_TABLESPACE (
ts_name IN VARCHAR2,
shrink_mode IN NUMBER,
target_size IN NUMBER,
shrink_result OUT CLOB --선택);
|
설명 :
ts_name : 테이블스페이스 이름
shrink_mode :
- ts_mode_analyze : shrink 대상을 분석하는 모드(공간 관련 추정만 수행하지만 실제 축소의 성공 또는 실패를 예측할 수는 없음)
- ts_mode_shrink : shrink 을 실행(기본값)
- ts_mode_shrink_force : move 를 오프라인으로 실행하고 싶은경우 사용하는 강제 shrink 모드
이 모드는 오브젝트를 온라인으로 move 할 수 없지만 오프라인으로라도 move 하고 싶은 경우 사용할 수 있음
오프라인 move 시 lock 이 발생하기 때문에 해당 테이블에 대해 dml 및 쿼리가 불가함
그리고 일부 오브젝트는 온라인이나 오프라인으로 move 할 수 없기 때문에 항상 성공하지는 않는다고함
target_size : target_size 를 정해서 해당 크기 만큼만 줄일수도 있음(bytes 단위), target_size 의 기본값은 ts_target_max_shrink 임(최대로 shrink 실행)
shrink_result : 프로시저 출력 결과를 clob 로 반환하고 싶은경우 사용하는 인자, 미사용시 그냥 출력됨
이 기능은 23.7 이전버전에서는 bigfile 테이블스페이스에서만 실행가능했고 smallfile 테이블스페이스에 이 프로시저를 사용하면 에러가 발생했었음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> create smallfile tablespace smallts datafile '/app/oracle/oradata/ORACLE23/oracle23pdb1/smallts01.dbf' size 1g;
SQL> set serveroutput on
SQL> execute dbms_space.shrink_tablespace('SMALLTS', shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE);
Procedure exited because it encountered error:
ORA-20000: Tablespace name is not valid or it isn't bigfile tablespace
ORA-06512: at "SYS.DBMS_SPACE", line 6012
BEGIN dbms_space.shrink_tablespace('SMALLTS', shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE); END;
*
ERROR at line 1:
ORA-20000: Tablespace shrink exited with error
ORA-06512: at "SYS.DBMS_SPACE", line 9997
ORA-06512: at "SYS.DBMS_SPACE", line 10013
ORA-06512: at line 1
|
테스트
(pdb에서 테스트함)
샘플 유저 생성
참고 : 오라클 19c 테스트 시 사용하는 imsi 유저 생성 쿼리 ( https://positivemh.tistory.com/1169 )
신규 테이블스페이스 생성
|
1
2
3
|
SQL> create smallfile tablespace imsits datafile '/app/oracle/oradata/ORA23EDBFS/ORA23EDBFSpdb1/imsits01.dbf' size 10g autoextend off;
Tablespace created.
|
*23ai부터 기본 시스템 테이블스페이스 옵션이 bigfile 로 되어있기때문에 별도로 smallfile 옵션을 붙여줘야 smallfile 테이블스페이스로 생성됨
참고 : 오라클 23ai bigfile 테이블스페이스 기본값 ( https://positivemh.tistory.com/1168 )
샘플 유저의 default tablespace 를 imsits 로 변경
|
1
2
3
|
SQL> alter user imsi default tablespace imsits quota unlimited on imsits;
User altered.
|
샘플 유저의 default tablespace 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col file_name for a70
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible
from dba_data_files
where tablespace_name = (select default_tablespace from dba_users where username = 'IMSI');
TABLESPACE_NAME FILE_ID FILE_NAME GB MAX_GB AUT
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- ---
IMSITS 12 /app/oracle/oradata/ORA23EDBFS/ORA23EDBFSpdb1/imsits01.dbf 10 0 NO
|
샘플 테이블 생성 및 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
SQL>
conn imsi/imsi@localhost:1521/ORA23EDBFSpdb1
drop table sample_t1 purge;
create table sample_t1
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
) nologging;
DECLARE
TYPE tbl_ins IS TABLE OF sample_t1%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..100 LOOP
FOR i IN 1..100000 LOOP
w_ins(i).id1 := (d - 1) * 100000 + i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2019,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO sample_t1 VALUES w_ins(i);
COMMIT;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
|
샘플 테이블 추가 생성
|
1
2
3
4
5
6
|
SQL>
create table sample_t2 nologging as select * from sample_t1;
create table sample_t3 nologging as select * from sample_t1;
create table sample_t4 nologging as select id1, id2 from sample_t1;
create table sample_t5 nologging as select id1, id2 from sample_t1;
create table sample_t6 nologging as select id1, id2 from sample_t1;
|
샘플 인덱스 생성
|
1
2
3
4
|
SQL>
create index sample_t4_ix1 on sample_t4(id1, id2) nologging;
create index sample_t5_ix1 on sample_t5(id1, id2) nologging;
create index sample_t6_ix1 on sample_t6(id1, id2) nologging;
|
샘플 테이블 크기와 실제 사용 블록 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
SQL>
set lines 200 pages 1000
col owner for a15
col segment_name for a20
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t1) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T1'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t2) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T2'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t3) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T3'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t4) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T4'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t5) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T5'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t6) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T6'
order by 1,2,3
;
OWNER SEGMENT_NAME SEGMENT_TYPE MB BLOCKS USED
--------------- -------------------- ------------------ ---------- ---------- ----------
IMSI SAMPLE_T1 TABLE 928 118784 117496
IMSI SAMPLE_T2 TABLE 928 118784 117498
IMSI SAMPLE_T3 TABLE 928 118784 117498
IMSI SAMPLE_T4 TABLE 208 26624 26084
IMSI SAMPLE_T5 TABLE 208 26624 26084
IMSI SAMPLE_T6 TABLE 208 26624 26084
6 rows selected.
|
샘플 인덱스 크기 확인(테이블처럼 dbms_rowid 를 이용한 실제 블록은 확인 불가)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
set lines 200 pages 1000
col owner for a15
col segment_name for a20
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks
from dba_segments
where owner = 'IMSI'
and segment_name like 'SAMPLE_T%'
and segment_type = 'INDEX'
order by 1,2,3;
OWNER SEGMENT_NAME SEGMENT_TYPE MB BLOCKS
--------------- -------------------- ------------------ ---------- ----------
IMSI SAMPLE_T4_IX1 INDEX 280 35840
IMSI SAMPLE_T5_IX1 INDEX 280 35840
IMSI SAMPLE_T6_IX1 INDEX 280 35840
|
샘플 테이블 데이터 일부(80%) 삭제
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
delete /*+ parallel(sample_t1 2) */ from sample_t1 where rownum <= (select count(*) * 0.8 from sample_t1);
commit;
delete /*+ parallel(sample_t2 2) */ from sample_t2 where rownum <= (select count(*) * 0.8 from sample_t2);
commit;
delete /*+ parallel(sample_t3 2) */ from sample_t3 where rownum <= (select count(*) * 0.8 from sample_t3);
commit;
delete from sample_t4 where rownum <= (select count(*) * 0.8 from sample_t4);
commit;
delete from sample_t5 where rownum <= (select count(*) * 0.8 from sample_t5);
commit;
delete from sample_t6 where rownum <= (select count(*) * 0.8 from sample_t6);
commit;
|
샘플 테이블 크기와 실제 사용 블록 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
SQL>
set lines 200 pages 1000
col owner for a15
col segment_name for a20
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t1) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T1'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t2) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T2'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t3) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T3'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t4) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T4'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t5) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T5'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t6) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T6'
order by 1,2,3
;
OWNER SEGMENT_NAME SEGMENT_TYPE MB BLOCKS USED
--------------- -------------------- ------------------ ---------- ---------- ----------
IMSI SAMPLE_T1 TABLE 928 118784 23528
IMSI SAMPLE_T2 TABLE 928 118784 23444
IMSI SAMPLE_T3 TABLE 928 118784 23444
IMSI SAMPLE_T4 TABLE 208 26624 5245
IMSI SAMPLE_T5 TABLE 208 26624 5245
IMSI SAMPLE_T6 TABLE 208 26624 5245
6 rows selected.
|
실제 사용블록 USED가 줄어듬 하지만 dba_segments의 mb는 줄지 않음
샘플 인덱스 크기 확인(테이블처럼 dbms_rowid 를 이용한 실제 블록은 확인 불가)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
set lines 200 pages 1000
col owner for a15
col segment_name for a20
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks
from dba_segments
where owner = 'IMSI'
and segment_name like 'SAMPLE_T%'
and segment_type = 'INDEX'
order by 1,2,3;
OWNER SEGMENT_NAME SEGMENT_TYPE MB BLOCKS
--------------- -------------------- ------------------ ---------- ----------
IMSI SAMPLE_T4_IX1 INDEX 280 35840
IMSI SAMPLE_T5_IX1 INDEX 280 35840
IMSI SAMPLE_T6_IX1 INDEX 280 35840
|
테이블, 인덱스 전체 크기 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
select round(sum(bytes)/1024/1024, 2) mb, round(sum(bytes)/1024/1024/1024, 2) gb
from dba_segments
where owner = 'IMSI'
and segment_name like 'SAMPLE_T%';
MB GB
---------- ----------
4248 4.15
|
4.15gb 사용중임
이렇게 데이터를 delete 로 지운 경우 공간이 회수되지 않고 남아있어
reorg(move, shrink, rebuild) 등의 작업을 해줘야 공간이 회수됨
하지만 23ai 버전에서는 테이블스페이스 자체(데이터파일)를 shrink 할수 있어 공간 회수가 됨
USERS 테이블스페이스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col file_name for a70
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible
from dba_data_files
where tablespace_name = 'IMSITS';
TABLESPACE_NAME FILE_ID FILE_NAME GB MAX_GB AUT
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- ---
IMSITS 12 /app/oracle/oradata/ORA23EDBFS/ORA23EDBFSpdb1/imsits01.dbf 10 0 NO
|
현재 10gb로 할당되어 있지만 실제로는 4gb 정도밖에 사용하지 않고 있음
통계정보 수집(선택)
|
1
2
3
4
5
6
7
8
|
SQL>
exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'SAMPLE_T1', cascade => TRUE);
exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'SAMPLE_T2', cascade => TRUE);
exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'SAMPLE_T3', cascade => TRUE);
exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'SAMPLE_T4', cascade => TRUE);
exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'SAMPLE_T5', cascade => TRUE);
exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'SAMPLE_T6', cascade => TRUE);
exec dbms_stats.gather_table_stats(ownname => 'IMSI', tabname => 'SAMPLE_T6', cascade => TRUE);
|
먼저 DBMS_SPACE.TS_SHRINK_MODE_ANALYZE로 얼마나 많은 공간을 회수할수 있는지 분석을 해볼수 있음
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set serveroutput on;
execute dbms_space.shrink_tablespace('IMSITS', shrink_mode => DBMS_SPACE.TS_SHRINK_MODE_ANALYZE);
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 0
Total Movable Size(GB): 0
Original Datafile Size(GB): 10
Suggested Target Size(GB): 5.11
Process Time: +00 00:00:00.497539
PL/SQL procedure successfully completed.
|
이전 테스트에서는 이동가능한 오브젝트가 표시되었었는데 현재는 이동가능한 오브젝트와 크기가 0으로 나옴
상세한 내용을 보고싶은 경우 아래 10613 trace 를 켜고 dbms_space.shrink_tablespace 를 실행하면됨
|
1
2
3
4
5
|
SQL>
set serveroutput on;
alter session set events '10613 trace name context forever, level 1';
execute dbms_space.shrink_tablespace('IMSITS', shrink_mode => DBMS_SPACE.TS_SHRINK_MODE_ANALYZE);
alter session set events '10613 trace name context off';
|
트레이스 설명
10613 : Prints debug information for auto-space managed segments
https://blog.naver.com/i2noo/50153574291
실제 shrink_tablespace 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set timing on
set serveroutput on
execute dbms_space.shrink_tablespace('IMSITS');
-------------------SHRINK RESULT-------------------
Total Moved Objects: 0
Total Moved Size(GB): 0
Original Datafile Size(GB): 10
New Datafile Size(GB): 4.15
Process Time: +00 00:00:03.422075
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.08
|
약 4초만에 완료됨
옮겨진 오브젝트는 없고 데이터파일 크기가 4.15gb로 줄어듬
alert log 확인
|
1
2
3
4
5
6
7
8
|
$ tail -300f /app/oracle/diag/rdbms/ora23edbfs/ORA23EDBFS/trace/alert_ORA23EDBFS.log
2025-06-08T20:17:56.594685+09:00
ORA23EDBFSPDB1(3):alter database datafile '/app/oracle/oradata/ORA23EDBFS/ORA23EDBFSpdb1/imsits01.dbf' resize 4455399424
ORA23EDBFSPDB1(3):Resize operation completed for file# 12, fname /app/oracle/oradata/ORA23EDBFS/ORA23EDBFSpdb1/imsits01.dbf, old size 10485760K, new size 4350976K
ORA23EDBFSPDB1(3):Completed: alter database datafile '/app/oracle/oradata/ORA23EDBFS/ORA23EDBFSpdb1/imsits01.dbf' resize 4455399424
2025-06-08T20:17:58.046440+09:00
ORA23EDBFSPDB1(3):alter database datafile '/app/oracle/oradata/ORA23EDBFS/ORA23EDBFSpdb1/imsits01.dbf' resize 4455399424
ORA23EDBFSPDB1(3):Completed: alter database datafile '/app/oracle/oradata/ORA23EDBFS/ORA23EDBFSpdb1/imsits01.dbf' resize 4455399424
|
IMSITS 테이블스페이스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col file_name for a70
select tablespace_name, file_id, file_name, round(bytes/1024/1024/1024, 2) gb,
round(maxbytes/1024/1024/1024, 2) max_gb, autoextensible
from dba_data_files
where tablespace_name = 'IMSITS';
TABLESPACE_NAME FILE_ID FILE_NAME GB MAX_GB AUT
-------------------- ---------- ---------------------------------------------------------------------- ---------- ---------- ---
IMSITS 12 /app/oracle/oradata/ORA23EDBFS/ORA23EDBFSpdb1/imsits01.dbf 4.15 0 NO
|
데이터파일 크기가 2.91gb로 줄어듬
샘플 테이블 크기와 실제 사용 블록 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
SQL>
set lines 200 pages 1000
col owner for a15
col segment_name for a20
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t1) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T1'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t2) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T2'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t3) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T3'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t4) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T4'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t5) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T5'
union all
select owner, segment_name, segment_type, round(bytes/1024/1024, 2) mb, blocks,
(select count(distinct dbms_rowid.rowid_block_number(rowid)||dbms_rowid.rowid_relative_fno(rowid)) "used" from imsi.sample_t6) "USED"
from dba_segments where owner = 'IMSI' and segment_name = 'SAMPLE_T6'
order by 1,2,3
;
OWNER SEGMENT_NAME SEGMENT_TYPE MB BLOCKS USED
--------------- -------------------- ------------------ ---------- ---------- ----------
IMSI SAMPLE_T1 TABLE 928 118784 23528
IMSI SAMPLE_T2 TABLE 928 118784 23444
IMSI SAMPLE_T3 TABLE 928 118784 23444
IMSI SAMPLE_T4 TABLE 208 26624 5245
IMSI SAMPLE_T5 TABLE 208 26624 5245
IMSI SAMPLE_T6 TABLE 208 26624 5245
6 rows selected.
|
무슨 이유에서인지 각 테이블별 shrink 가 되지 않음
재기동을 하거나 통계정보 재수집, analyze 및 shrink 를 해도 동일하게 크기가 줄지 않았음
append insert 방식으로도 테스트 해보았지만 결과는 동일했음(Total Moved Objects가 0으로 표시되었음)
참고1. dbms_space.shrink_tablespace 의 출력이 느리거나 부하가 심한 경우 clob 변수를 선언하여 출력도 가능함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
variable result clob
execute dbms_space.shrink_tablespace('IMSITS', shrink_result => :result);
set long 30000
print result
RESULT
-------------------------------------
Total Moved Objects: 0
Total Moved Size(GB): 0
Original Datafile Size(GB): 2.21
New Datafile Size(GB): 2.21
Process Time: +00 00:00:00.234140
|
참고2. target_size 를 정해서 datafile 을 해당 크기까지만 줄일수도 있음(byte 단위)
target_size 의 기본값은 ts_target_max_shrink 임(최대한)
|
1
2
3
|
SQL>
set serveroutput on
execute dbms_space.shrink_tablespace('IMSITS', target_size => 2147483648);
|
결론 :
23ai 부터 나온 dbms_space.shrink_tablespace 기능 덕분에 간편하게 다중 테이블, 인덱스에 대한 reorg 를 할수 있음
보통 용량을 줄이기 위해 사용량이 적은 시간대에 move 나 rebuild 작업들을 개별 테이블 단위로 진행 하는데
이 기능을 통해 작업을 테이블스페이스 단위로 커맨드 한줄로 할수도 있게됨
물론 대상이 너무 많은경우 프로시저 실행후 끝날때까지 시간이 오래 소요될수는 있을듯함
이 경우 내부적으로는 move 나 rebuild 명령이 실행되는 것이기 때문에 Ctrl + C 로 끊어도 크게 문제는 없지 않을까 싶지만(move 나 rebuild 시 작업을 끊게 되면 그냥 원상태로 롤백됨)
실제 업무 환경에서 사용할 경우 충분한 테스트를 거친 후 사용해야할듯함
공식문서에도 경고가 나와있듯이 shrink_tablespace를 실행하면 가능한 최대로 데이터파일을 축소시켜버리기때문에 autoextend가 설정되어있지 않다면 꼭 수동으로 데이터파일 크기를 여유롭게 증가시켜줘야함
그렇지 않으면 사용량 99%에서 데이터가 더 못들어오고 장애로 이어질 수 있음
이전테스트에서는 Total Moved Objects가 3개정도 표시되었었는데 이번 테스트에선 제대로 동작하지 않았음
버그일수도 있고, 테스트 방식에 문제일수도 있음. 추후 버전이 더 업데이트 되었을때 테스트해봐야겠음
참조 :
https://positivemh.tistory.com/1163
https://positivemh.tistory.com/1169
https://blogs.oracle.com/coretec/post/bigfile-tablespace-shrink
https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1954
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tablespaces.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-TABLESPACE.html
https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_SPACE.html#GUID-891B506D-DD4F-4881-B30C-56E4C39523ED
https://oracle-base.com/articles/23/bigfile-tablespace-shrink-23
https://oracle-base.com/articles/21c/space-management-enhancements-21c
https://www.killiansbytes.com/post/reclaiming-unused-space-in-oracle-database-23c-with-tablespace_shrink
https://connor-mcdonald.com/2023/11/09/express-edition-needs-bigfile/
https://positivemh.tistory.com/350
https://oracle-base.com/articles/23/smallfile-tablespace-shrink-23
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c(19.28) 신기능 (0) | 2025.07.22 |
|---|---|
| 오라클 11gR2 to 19c 업그레이드 시 preupgrade.jar 스크립트 사용법 (0) | 2025.06.22 |
| 오라클 19c tde 설정 방법 및 데이터파일 암호화 테스트 (0) | 2025.06.15 |
| 오라클 19c 로컬 파티션 인덱스의 기본 테이블스페이스 (0) | 2025.06.10 |
| 오라클 19c alter database default tablespace 명령어 (0) | 2025.06.07 |