내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.5.0.24.07 ai for Oracle Cloud and Engineered Systems
방법 : 오라클 23ai 신기능 bigfile 테이블스페이스 shrink
오라클 23ai 부터 bigfile 테이블스페이스에 대해 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 로 반환하고 싶은경우 사용하는 인자, 미사용시 그냥 출력됨
이 기능은 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
|
테스트
샘플 유저 생성
참고 : 오라클 19c 테스트 시 사용하는 imsi 유저 생성 쿼리 ( https://positivemh.tistory.com/1017 )
신규 테이블스페이스 생성
|
1
2
3
|
SQL> create tablespace imsits datafile '/app/oracle/oradata/ORACLE23/oracle23pdb1/imsits01.dbf' size 10g autoextend off;
Tablespace created.
|
*기본 시스템 테이블스페이스 옵션이 bigfile 로 되어있기때문에 별도로 bigfile 옵션을 붙이지 않아도 bigfile 테이블스페이스로 생성됨
참고 : 오라클 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/ORACLE23/oracle23pdb1/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
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
|
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 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 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 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 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 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 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
|
샘플 인덱스 크기 확인(테이블처럼 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
|
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 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 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 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 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 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 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 23475
IMSI SAMPLE_T2 TABLE 928 118784 23444
IMSI SAMPLE_T3 TABLE 928 118784 23528
IMSI SAMPLE_T4 TABLE 208 26624 5185
IMSI SAMPLE_T5 TABLE 208 26624 5185
IMSI SAMPLE_T6 TABLE 208 26624 5185
|
실제 사용블록 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/ORACLE23/oracle23pdb1/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_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_MODE_ANALYZE);
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 3
Total Movable Size(GB): 2.72
Original Datafile Size(GB): 10
Suggested Target Size(GB): 5.18
Process Time: +00 00:00:02.676151
PL/SQL procedure successfully completed.
|
이동가능한 오브젝트 3개(2.72gb) 로 나오고 datafile 의 제안 크기가 5.18gb로 나옴
상세한 내용을 보고싶은 경우 아래 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_MODE_ANALYZE);
alter session set events '10613 trace name context off';
|
10613 trace on 한뒤 결과 샘플
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL> execute dbms_space.shrink_tablespace('IMSITS', shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE);
[get_ts_number] tablespace number: 6
[get_ts_file_size] tablespace file size: 10737418240
[shrink_tablespace] begin checking concurrency
[check_concurrency] curr_sid: 401 curr_process: 51010 curr_inst: 1
[check_concurrency] skip ora$tbs_shrink creation
[check_concurrency] privileges granted
[check_concurrency] tbs entry successfully inserted
..
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 3
Total Movable Blocks: 356352
Total Movable Size(GB): 2.72
Original Datafile Size(GB): 10
Suggested Target Size(GB): 5.18
Process Time: +00 00:00:02.491078
PL/SQL procedure successfully completed.
|
실제 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: 3
Total Moved Size(GB): 2.72
Original Datafile Size(GB): 10
New Datafile Size(GB): 2.21
Process Time: +00 00:00:37.936524
PL/SQL procedure successfully completed.
Elapsed: 00:00:38.35
|
약 38초만에 완료됨
3개 오브젝트(2.72gb) 가 옮겨졌고 데이터파일 크기가 2.21gb로 줄어듬
alert log 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
$ tail -300f /app/oracle/diag/rdbms/oracle23/oracle23/trace/alert_oracle23.log
2024-09-26T19:41:26.601439+09:00
ORACLE23PDB1(3):alter database datafile '/app/oracle/oradata/ORACLE23/oracle23pdb1/imsits01.dbf' resize 2641362944
ORACLE23PDB1(3):Resize operation completed for file# 12, fname /app/oracle/oradata/ORACLE23/oracle23pdb1/imsits01.dbf, old size 10485760K, new size 2579456K
ORACLE23PDB1(3):Completed: alter database datafile '/app/oracle/oradata/ORACLE23/oracle23pdb1/imsits01.dbf' resize 2641362944
2024-09-26T19:41:35.085625+09:00
ORACLE23PDB1(3):alter database datafile '/app/oracle/oradata/ORACLE23/oracle23pdb1/imsits01.dbf' resize 2378170368
ORACLE23PDB1(3):Resize operation completed for file# 12, fname /app/oracle/oradata/ORACLE23/oracle23pdb1/imsits01.dbf, old size 2579456K, new size 2322432K
ORACLE23PDB1(3):Completed: alter database datafile '/app/oracle/oradata/ORACLE23/oracle23pdb1/imsits01.dbf' resize 2378170368
ORACLE23PDB1(3):alter database datafile '/app/oracle/oradata/ORACLE23/oracle23pdb1/imsits01.dbf' resize 2378170368
ORACLE23PDB1(3):Completed: alter database datafile '/app/oracle/oradata/ORACLE23/oracle23pdb1/imsits01.dbf' resize 2378170368
|
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/ORACLE23/oracle23pdb1/imsits01.dbf 2.21 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 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 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 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 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 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 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 192 24576 23528
IMSI SAMPLE_T2 TABLE 192 24576 23444
IMSI SAMPLE_T3 TABLE 192 24576 23528
IMSI SAMPLE_T4 TABLE 208 26624 5246
IMSI SAMPLE_T5 TABLE 208 26624 5246
IMSI SAMPLE_T6 TABLE 208 26624 5246
6 rows selected.
|
무슨 이유에서인지 sample_t1,t2,t2 는 shrink 가 되었는데 sample_t4,5,6 은 크기가 줄지 않았음
재기동을 하거나 통계정보 재수집, analyze 및 shrink 를 해도 동일하게 크기가 줄지 않았음
참고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 시 작업을 끊게 되면 그냥 원상태로 롤백됨)
실제 업무 환경에서 사용할 경우 충분한 테스트를 거친 후 사용해야할듯함
참조 :
https://positivemh.tistory.com/1163
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
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai 신기능 스키마 레벨 권한 부여 (0) | 2024.11.05 |
|---|---|
| 오라클 23ai 신기능 sql 이력 확인뷰 v$sql_history (1) | 2024.10.31 |
| 오라클 23ai 신기능 SQL 도메인(Domain) (0) | 2024.10.22 |
| 오라클 23ai 신기능 테이블 주석 기능(Annonation) (0) | 2024.10.18 |
| 오라클 23ai 신기능 bigfile 테이블스페이스 기본값 (0) | 2024.10.18 |