[아이티윌 오라클 DBA 과정 91기] 260119 TIL
2026. 1. 20. 21:20ㆍCourses/아이티윌 오라클 DBA 과정
Table Reorganization(REORG)(Cont.)
2. Table Move
테이블 생성 및 데이터 입력
SYS@ora19c> drop table hr.reorg_test purge;
Table dropped.
# 테이블 생성
SYS@ora19c> create table hr.reorg_test(id number, name varchar2(100)) tablespace users;
Table created.
# 데이터 만건 입력
SYS@ora19c> begin
for i in 1..10000 loop
insert into hr.reorg_test(id, name) values(i, 'table/index reorganization example');
end loop;
end;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SYS@ora19c> select count(*) from hr.reorg_test;
COUNT(*)
----------
10000
# 테이블 세그먼트가 사용 중인 익스텐트 및 블록 수
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
9 72 589824
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_TEST';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
USERS 0 65536
USERS 1 65536
USERS 2 65536
USERS 3 65536
USERS 4 65536
USERS 5 65536
USERS 6 65536
USERS 7 65536
USERS 8 65536
9 rows selected.
제약 조건 추가
alter table hr.reorg_test add constraint reort_id_pk primary key(id);
select constraint_name, status, validated from dba_constraints where table_name = 'REORG_TEST';
select index_name, uniqueness from dba_indexes where table_name = 'REORG_TEST';
# 인덱스 세그먼트가 사용 중인 익스텐트 및 블록 수
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORT_ID_PK';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
4 32 262144
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORT_ID_PK';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYSAUX 0 65536
SYSAUX 1 65536
SYSAUX 2 65536
SYSAUX 3 65536
테이블 정보 조회
SYS@ora19c> select num_rows, blocks, avg_row_len from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
-- 통계 정보 수집
SYS@ora19c> exec dbms_stats.gather_table_stats('hr', 'reorg_test')
PL/SQL procedure successfully completed.
SYS@ora19c> select num_rows, blocks, avg_row_len, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ----------- -------------------
10000 65 39 2026-01-19 10:35:39
-- 실행 계획 확인
select * from hr.reorg_test where id = 100;데이터 삭제
SYS@ora19c> delete from hr.reorg_test where id > 100;
9900 rows deleted.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select count(*) from hr.reorg_test;
COUNT(*)
----------
100
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
9 72 589824
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_TEST';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
USERS 0 65536
USERS 1 65536
USERS 2 65536
USERS 3 65536
USERS 4 65536
USERS 5 65536
USERS 6 65536
USERS 7 65536
USERS 8 65536
9 rows selected.
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORT_ID_PK';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
4 32 262144
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORT_ID_PK';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYSAUX 0 65536
SYSAUX 1 65536
SYSAUX 2 65536
SYSAUX 3 65536
SYS@ora19c> select num_rows, blocks, avg_row_len from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
10000 65 39
SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'REORG_TEST'
and owner = 'HR'; 2 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
9 rows selected.
SYS@ora19c> select
a.tablespace_name,
b.file_name,
a.status,
b.bytes/1024/1024 as "total size(mb)",
(b.bytes - c.free_bytes)/1024/1024 as "used size(mb)",
c.free_bytes/1024/1024 as "free size(mb)",
b.autoextensible
2 3 4 5 6 7 8 9 from
dba_tablespaces a,
dba_data_files b,
( select tablespace_name, file_id, sum(bytes) as free_bytes
from dba_free_space
group by tablespace_name, file_id
) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name
and b.file_id = c.file_id
order by b.file_id; 10 11 12 13 14 15 16 17 18 19
TABLESPACE_NAME FILE_NAME STATUS total size(mb) used size(mb) free size(mb) AUT
------------------------------ -------------------------------------------------- --------- -------------- ------------- ------------- ---
SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf ONLINE 910 908.9375 1.0625 YES
SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 720 673.3125 46.6875 YES
UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 340 18.25 321.75 YES
FDA_TBS /u01/app/oracle/oradata/ORA19C/fda_tbs01.dbf ONLINE 10 1 9 YES
USERS /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 88.75 4.25 84.5 YES- free 공간이 충분한 경우 move 가능
테이블 MOVE 작업
SYS@ora19c> alter table hr.reorg_test move tablespace users;
Table altered.
SYS@ora19c> select object_id, data_object_id from dba_objects where object_name = 'REORG_TEST';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
75043 75057- move를 하기 전에는 object_id와 data_object_id가 동일
- rowid : data object id(6) + data file num(3) + data block id(6) + row slot num(3)
- rowid가 바뀜 → 인덱스가 깨짐
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
1 8 65536
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_TEST';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
USERS 0 65536
# 인덱스가 unusable 상태로 바뀌었기 때문
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORT_ID_PK';
no rows selected
SYS@ora19c> select num_rows, blocks, avg_row_len from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
10000 65 39
SYS@ora19c> select index_name, status from dba_indexes where table_name = 'REORG_TEST';
INDEX_NAME STATUS
------------------------------ --------
REORT_ID_PK UNUSABLE
인덱스 rebuild
SYS@ora19c> alter index hr.reort_id_pk rebuild online;
Index altered.
SYS@ora19c> select index_name, status from dba_indexes where table_name = 'REORG_TEST';
INDEX_NAME STATUS
------------------------------ --------
REORT_ID_PK VALID
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORT_ID_PK';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
1 8 65536- 테이블에 lock 걸리기 때문에 운영 중에 수행 불가
3. 복제 테이블
테이블 생성
SYS@ora19c> drop table hr.reorg_test purge;
Table dropped.
SYS@ora19c> create table hr.reorg_test(id number, name varchar2(100)) tablespace users;
Table created.
SYS@ora19c> begin
for i in 1..10000 loop
insert into hr.reorg_test(id, name) values(i, 'table/index reorganization example');
end loop;
end;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SYS@ora19c> select count(*) from hr.reorg_test;
COUNT(*)
----------
10000
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
9 72 589824
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_TEST';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
USERS 0 65536
USERS 1 65536
USERS 2 65536
USERS 3 65536
USERS 4 65536
USERS 5 65536
USERS 6 65536
USERS 7 65536
USERS 8 65536
9 rows selected.
제약 조건 생성
SYS@ora19c> alter table hr.reorg_test add constraint reorg_id_pk primary key(id);
Table altered.
SYS@ora19c> select constraint_name, status, validated from dba_constraints where table_name = 'REORG_TEST';
CONSTRAINT_NAME STATUS VALIDATED
------------------------------ -------- -------------
REORG_ID_PK ENABLED VALIDATED
SYS@ora19c> select index_name, uniqueness, status from dba_indexes where table_name = 'REORG_TEST';
INDEX_NAME UNIQUENES STATUS
------------------------------ --------- --------
REORG_ID_PK UNIQUE VALID
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_ID_PK';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
4 32 262144
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_ID_PK';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYSAUX 0 65536
SYSAUX 1 65536
SYSAUX 2 65536
SYSAUX 3 65536테이블 정보 조회
SYS@ora19c> select num_rows, blocks, avg_row_len from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
SYS@ora19c> exec dbms_stats.gather_table_stats('hr', 'reorg_test')
PL/SQL procedure successfully completed.
SYS@ora19c> select num_rows, blocks, avg_row_len, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ----------- -------------------
10000 65 39 2026-01-19 11:20:47데이터 삭제
SYS@ora19c> delete from hr.reorg_test where id > 100;
9900 rows deleted.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select count(*) from hr.reorg_test;
COUNT(*)
----------
100
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
9 72 589824
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_TEST';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
USERS 0 65536
USERS 1 65536
USERS 2 65536
USERS 3 65536
USERS 4 65536
USERS 5 65536
USERS 6 65536
USERS 7 65536
USERS 8 65536
9 rows selected.
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_ID_PK';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
4 32 262144
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_ID_PK';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYSAUX 0 65536
SYSAUX 1 65536
SYSAUX 2 65536
SYSAUX 3 65536
SYS@ora19c> select num_rows, blocks, avg_row_len from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
10000 65 39
SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'REORG_TEST'
and owner = 'HR'; 2 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
9 rows selected.
SYS@ora19c> select
a.tablespace_name,
b.file_name,
a.status,
b.bytes/1024/1024 as "total size(mb)",
(b.bytes - c.free_bytes)/1024/1024 as "used size(mb)",
c.free_bytes/1024/1024 as "free size(mb)",
b.autoextensible
from
dba_tablespaces a,
dba_data_files b,
( select tablespace_name, file_id, sum(bytes) as free_bytes
from dba_free_space
group by tablespace_name, file_id
) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name
and b.file_id = c.file_id
order by b.file_id; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
TABLESPACE_NAME FILE_NAME STATUS total size(mb) used size(mb) free size(mb) AUT
------------------------------ -------------------------------------------------- --------- -------------- ------------- ------------- ---
SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf ONLINE 910 908.9375 1.0625 YES
SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 720 674.125 45.875 YES
UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 340 20.25 319.75 YES
FDA_TBS /u01/app/oracle/oradata/ORA19C/fda_tbs01.dbf ONLINE 10 1 9 YES
USERS /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 88.75 4.25 84.5 YES
SYS@ora19c> select object_id, data_object_id from dba_objects where object_name = 'REORG_TEST';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
75061 75061
복제 테이블 생성
SYS@ora19c> create table hr.reorg_test_temp tablespace users as select * from hr.reorg_test;
Table created.
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST_TEMP';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
1 8 65536
SYS@ora19c> drop table hr.reorg_test purge;
Table dropped.
rename
- rename은 소유자만 가능
SYS@ora19c> conn hr/hr
Connected.
HR@ora19c> rename reorg_test_temp to reorg_test;
Table renamed.
HR@ora19c> select count(*) from reorg_test;
COUNT(*)
----------
100제약 조건 추가
HR@ora19c> conn / as sysdba
Connected.
SYS@ora19c> alter table hr.reorg_test add constraint reorg_id_pk primary key(id);
Table altered.SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
1 8 65536
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_TEST';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
USERS 0 65536
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_ID_PK';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
1 8 65536
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_ID_PK';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYSAUX 0 65536
SYS@ora19c> select num_rows, blocks, avg_row_len from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
100 4 38
SYS@ora19c> select f.file_name
2 from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'REORG_TEST'
and owner = 'HR'; 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf
SYS@ora19c> select
a.tablespace_name,
b.file_name,
a.status,
b.bytes/1024/1024 as "total size(mb)",
(b.bytes - c.free_bytes)/1024/1024 as "used size(mb)",
c.free_bytes/1024/1024 as "free size(mb)",
b.autoextensible
from
dba_tablespaces a,
dba_data_files b,
( select tablespace_name, file_id, sum(bytes) as free_bytes
from dba_free_space
group by tablespace_name, file_id
) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name
and b.file_id = c.file_id
order by b.file_id; 10 11 12 13 14 15 16 17 18 19
TABLESPACE_NAME FILE_NAME STATUS total size(mb) used size(mb) free size(mb) AUT
------------------------------ -------------------------------------------------- --------- -------------- ------------- ------------- ---
SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf ONLINE 910 908.9375 1.0625 YES
SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 720 673.9375 46.0625 YES
UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 340 20.25 319.75 YES
FDA_TBS /u01/app/oracle/oradata/ORA19C/fda_tbs01.dbf ONLINE 10 1 9 YES
USERS /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 88.75 3.75 85 YES
SYS@ora19c> select object_id, data_object_id from dba_objects where object_name = 'REORG_TEST';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
75063 750634. TABLE SHRINK SPACE
SYS@ora19c> drop table hr.reorg_test purge;
Table dropped.
SYS@ora19c> create table hr.reorg_test(id number, name varchar2(100)) tablespace users;
Table created.
SYS@ora19c> begin
for i in 1..10000 loop
insert into hr.reorg_test(id, name) values(i, 'table/index reorganization example');
end loop;
end;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SYS@ora19c> select count(*) from hr.reorg_test;
COUNT(*)
----------
10000
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
9 72 589824
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_TEST';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
USERS 0 65536
USERS 1 65536
USERS 2 65536
USERS 3 65536
USERS 4 65536
USERS 5 65536
USERS 6 65536
USERS 7 65536
USERS 8 65536
9 rows selected.
SYS@ora19c> alter table hr.reorg_test add constraint reorg_id_pk primary key(id);
Table altered.
SYS@ora19c> select constraint_name, status, validated from dba_constraints where table_name = 'REORG_TEST';
CONSTRAINT_NAME STATUS VALIDATED
------------------------------ -------- -------------
REORG_ID_PK ENABLED VALIDATED
SYS@ora19c> select index_name, uniqueness, status from dba_indexes where table_name = 'REORG_TEST';
INDEX_NAME UNIQUENES STATUS
------------------------------ --------- --------
REORG_ID_PK UNIQUE VALID
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_ID_PK';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
4 32 262144
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_ID_PK';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYSAUX 0 65536
SYSAUX 1 65536
SYSAUX 2 65536
SYSAUX 3 65536
SYS@ora19c> select num_rows, blocks, avg_row_len from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
SYS@ora19c> exec dbms_stats.gather_table_stats('hr', 'reorg_test')
PL/SQL procedure successfully completed.
SYS@ora19c> select num_rows, blocks, avg_row_len, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ----------- -------------------
10000 65 39 2026-01-19 11:43:02
SYS@ora19c> delete from hr.reorg_test where id > 100;
9900 rows deleted.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select count(*) from hr.reorg_test;
COUNT(*)
----------
100
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
9 72 589824
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_TEST';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
USERS 0 65536
USERS 1 65536
USERS 2 65536
USERS 3 65536
USERS 4 65536
USERS 5 65536
USERS 6 65536
USERS 7 65536
USERS 8 65536
9 rows selected.
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_ID_PK';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
4 32 262144
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_ID_PK';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYSAUX 0 65536
SYSAUX 1 65536
SYSAUX 2 65536
SYSAUX 3 65536
SYS@ora19c> select num_rows, blocks, avg_row_len from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
10000 65 39
SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'REORG_TEST'
and owner = 'HR'; 2 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
9 rows selected.
SYS@ora19c> select
a.tablespace_name,
b.file_name,
a.status,
b.bytes/1024/1024 as "total size(mb)",
(b.bytes - c.free_bytes)/1024/1024 as "used size(mb)",
c.free_bytes/1024/1024 as "free size(mb)",
b.autoextensible
from
dba_tablespaces a,
dba_data_files b,
( select tablespace_name, file_id, sum(bytes) as free_bytes
from dba_free_space
group by tablespace_name, file_id
) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name
and b.file_id = c.file_id
order by b.file_id; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
TABLESPACE_NAME FILE_NAME STATUS total size(mb) used size(mb) free size(mb) AUT
------------------------------ -------------------------------------------------- --------- -------------- ------------- ------------- ---
SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf ONLINE 910 908.9375 1.0625 YES
SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 720 674.125 45.875 YES
UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 340 22.25 317.75 YES
FDA_TBS /u01/app/oracle/oradata/ORA19C/fda_tbs01.dbf ONLINE 10 1 9 YES
USERS /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 88.75 4.25 84.5 YES
SYS@ora19c> select object_id, data_object_id from dba_objects where object_name = 'REORG_TEST';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
75065 75065
행 이동 활성화
SYS@ora19c> select row_movement from dba_tables where owner='HR' and table_name = 'REORG_TEST';
ROW_MOVE
--------
DISABLED
SYS@ora19c> alter table hr.reorg_test enable row movement;
Table altered.
SYS@ora19c> select row_movement from dba_tables where owner='HR' and table_name = 'REORG_TEST';
ROW_MOVE
--------
ENABLEDshrink 작업
SYS@ora19c> alter table hr.reorg_test shrink space compact;
Table altered.
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
9 72 589824- HWM는 이동하지 않음
- shrink 작업하는 동안 이동 되는 대상 row에 대해서만 lock이 발생
- 운영 중에 수행 가능
HWM(high Water Mark) 조정
SYS@ora19c> alter table hr.reorg_test shrink space;
Table altered.
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
1 8 65536- DML 작업 불허
- 테이블에 Lock이 발생
- compact 방식으로 한 후에 트랜잭션이 더이상 수행되지 않을 때 이 방식으로 다시 수행
행 이동 비활성화
SYS@ora19c> alter table hr.reorg_test disable row movement;
Table altered.인덱스 축소
- 인덱스 블록은 그대로 남아있음
SYS@ora19c> select index_name, status from dba_indexes where table_name = 'REORG_TEST';
INDEX_NAME STATUS
------------------------------ --------
REORG_ID_PK VALID
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_ID_PK';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
4 32 262144cascade: 테이블 shrink 작업 시에 종속 세그먼트도 함께 축소 가능
alter table hr.reorg_test shrink space cascade compact;
alter table hr.reorg_test shrink space cascade;- 수동으로 인덱스 축소 : index rebuild
SYS@ora19c> alter index hr.reorg_id_pk rebuild online;
Index altered.
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_ID_PK';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
1 8 655365. Redefinition
https://www.oracle.com/kr/database/technologies/high-availability/online-ops.html
- 운영 중 reorg 가능
- but 장애 났던 전적이 있음… 고객사에서는 운영 중에 하는 건 안 좋아함
- 운영 중에 하지 않을거면 4번 시나리오(shrink)가 임시 테이블도 생성할 필요 없기 때문에 더 나음
SYS@ora19c> drop table hr.reorg_test purge;
Table dropped.
SYS@ora19c> create table hr.reorg_test(id number, name varchar2(100)) tablespace users;
Table created.
SYS@ora19c> begin
for i in 1..10000 loop
insert into hr.reorg_test(id, name) values(i, 'table/index reorganization example');
end loop;
end;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SYS@ora19c> select count(*) from hr.reorg_test;
COUNT(*)
----------
10000
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
9 72 589824
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_TEST';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
USERS 0 65536
USERS 1 65536
USERS 2 65536
USERS 3 65536
USERS 4 65536
USERS 5 65536
USERS 6 65536
USERS 7 65536
USERS 8 65536
9 rows selected.
SYS@ora19c> alter table hr.reorg_test add constraint reorg_id_pk primary key(id);
Table altered.
SYS@ora19c> select constraint_name, status, validated from dba_constraints where table_name = 'REORG_TEST';
CONSTRAINT_NAME STATUS VALIDATED
------------------------------ -------- -------------
REORG_ID_PK ENABLED VALIDATED
SYS@ora19c> select index_name, uniqueness, status from dba_indexes where table_name = 'REORG_TEST';
INDEX_NAME UNIQUENES STATUS
------------------------------ --------- --------
REORG_ID_PK UNIQUE VALID
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_ID_PK';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
4 32 262144
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_ID_PK';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYSAUX 0 65536
SYSAUX 1 65536
SYSAUX 2 65536
SYSAUX 3 65536
SYS@ora19c> select num_rows, blocks, avg_row_len from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
SYS@ora19c> exec dbms_stats.gather_table_stats('hr', 'reorg_test')
PL/SQL procedure successfully completed.
SYS@ora19c> select num_rows, blocks, avg_row_len, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ----------- -------------------
10000 65 39 2026-01-19 13:47:26
SYS@ora19c> delete from hr.reorg_test where id > 100;
9900 rows deleted.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select count(*) from hr.reorg_test;
COUNT(*)
----------
100
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
9 72 589824
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_TEST';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
USERS 0 65536
USERS 1 65536
USERS 2 65536
USERS 3 65536
USERS 4 65536
USERS 5 65536
USERS 6 65536
USERS 7 65536
USERS 8 65536
9 rows selected.
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_ID_PK';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
4 32 262144
SYS@ora19c> select tablespace_name, extent_id, bytes from dba_extents where segment_name = 'REORG_ID_PK';
TABLESPACE_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
SYSAUX 0 65536
SYSAUX 1 65536
SYSAUX 2 65536
SYSAUX 3 65536
SYS@ora19c> select num_rows, blocks, avg_row_len from dba_tables where table_name = 'REORG_TEST';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
10000 65 39
SYS@ora19c> select f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'REORG_TEST'
and owner = 'HR'; 2 3 4 5
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
/u01/app/oracle/oradata/ORA19C/users01.dbf
9 rows selected.
SYS@ora19c> select
a.tablespace_name,
b.file_name,
a.status,
b.bytes/1024/1024 as "total size(mb)",
(b.bytes - c.free_bytes)/1024/1024 as "used size(mb)",
c.free_bytes/1024/1024 as "free size(mb)",
b.autoextensible
from
dba_tablespaces a,
dba_data_files b,
( select tablespace_name, file_id, sum(bytes) as free_bytes
from dba_free_space
group by tablespace_name, file_id
) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name
and b.file_id = c.file_id
order by b.file_id; 11 12 13 14 15 16 17 18 19
TABLESPACE_NAME FILE_NAME STATUS total size(mb) used size(mb) free size(mb) AUT
------------------------------ -------------------------------------------------- --------- -------------- ------------- ------------- ---
SYSTEM /u01/app/oracle/oradata/ORA19C/system01.dbf ONLINE 910 908.9375 1.0625 YES
SYSAUX /u01/app/oracle/oradata/ORA19C/sysaux01.dbf ONLINE 720 676.375 43.625 YES
UNDOTBS1 /u01/app/oracle/oradata/ORA19C/undotbs01.dbf ONLINE 340 25.25 314.75 YES
FDA_TBS /u01/app/oracle/oradata/ORA19C/fda_tbs01.dbf ONLINE 10 1 9 YES
USERS /u01/app/oracle/oradata/ORA19C/users01.dbf ONLINE 88.75 4.25 84.5 YES
SYS@ora19c> select object_name, object_id, data_object_id, to_char(created, 'yyyy-mm-dd hh24:mi:ss') create_date, to_char(last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') ddl_date, timestamp, status
from dba_objects
where object_name = 'REORG_TEST'; 2 3
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID CREATE_DATE DDL_DATE TIMESTAMP STATUS
------------------------------ ---------- -------------- ------------------- ------------------- ------------------- -------
REORG_TEST 75070 75070 2026-01-19 13:46:18 2026-01-19 13:46:53 2026-01-19:13:46:18 VALID
- create_date : 생성 시각
- ddl_date : 마지막 ddl 수행 시각
- timestamp : recompile 시각
- temp 공간이 필요
- pk 제약 조건 필요
임시테이블 생성(reorg 대상 테이블과 구조가 동일한 테이블 생성)
SYS@ora19c> create table hr.reorg_test_temp tablespace users as select * from hr.reorg_test where 1 = 2;
Table created.
SYS@ora19c> alter table hr.reorg_test_temp add constraint reorg_temp_id_pk primary key(id);
Table altered.
SYS@ora19c> select constraint_name, constraint_type, index_name from dba_constraints where table_name = 'REORG_TEST_TEMP';
CONSTRAINT_NAME C INDEX_NAME
------------------------------ - ------------------------------
REORG_TEMP_ID_PK P REORG_TEMP_ID_PK
SYS@ora19c> select object_name, object_id, data_object_id, to_char(created, 'yyyy-mm-dd hh24:mi:ss') create_date, to_char(last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') ddl_date, timestamp, status
from dba_objects
where object_name in ('REORG_TEST', 'REORG_TEST_TEMP'); 2 3
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID CREATE_DATE DDL_DATE TIMESTAMP STATUS
------------------------------ ---------- -------------- ------------------- ------------------- ------------------- -------
REORG_TEST_TEMP 75072 75072 2026-01-19 14:01:02 2026-01-19 14:02:24 2026-01-19:14:01:02 VALID
REORG_TEST 75070 75070 2026-01-19 13:46:18 2026-01-19 13:46:53 2026-01-19:13:46:18 VALID온라인 재정의 할 수 있는지 체크
exec dbms_redefinition.can_redef_table('hr', 'reorg_test', dbms_redefinition.cons_use_pk)redefinition
SYS@ora19c> exec dbms_redefinition.start_redef_table('hr', 'reorg_test', 'reorg_test_temp')
PL/SQL procedure successfully completed.
SYS@ora19c> select count(*) from hr.reorg_test_temp;
COUNT(*)
----------
100
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
9 72 589824
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST_TEMP';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
1 8 65536 원본 테이블과 임시 테이블 간의 차이 발생
SYS@ora19c> select * from hr.reorg_test where id = 50;
ID NAME
---------- --------------------------------------------------
50 table/index reorganization example
SYS@ora19c> select * from hr.reorg_test_temp where id = 50;
ID NAME
---------- --------------------------------------------------
50 table/index reorganization example
SYS@ora19c> update hr.reorg_test set name = 'ITWILL' where id = 50;
1 row updated.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select * from hr.reorg_test where id = 50;
ID NAME
---------- --------------------------------------------------
50 ITWILL
SYS@ora19c> select * from hr.reorg_test_temp where id = 50;
ID NAME
---------- --------------------------------------------------
50 table/index reorganization example
SYS@ora19c> select * from hr.reorg_test where id = 1;
ID NAME
---------- --------------------------------------------------
1 table/index reorganization example
SYS@ora19c> select * from hr.reorg_test_temp where id = 1;
ID NAME
---------- --------------------------------------------------
1 table/index reorganization example
SYS@ora19c> delete from hr.reorg_test where id = 1;
1 row deleted.
SYS@ora19c> commit;
Commit complete.
SYS@ora19c> select * from hr.reorg_test where id = 1;
no rows selected
SYS@ora19c> select * from hr.reorg_test_temp where id = 1;
ID NAME
---------- --------------------------------------------------
1 table/index reorganization example- reorg 하는 동안 트랜잭션이 수행되어도 상관없음
- 원본 테이블과 임시 테이블의 상태가 동기화되지는 않음
임시 테이블을 원본 테이블과 동기화 유지
SYS@ora19c> exec dbms_redefinition.sync_interim_table('hr', 'reorg_test', 'reorg_test_temp')
PL/SQL procedure successfully completed.
SYS@ora19c> select * from hr.reorg_test where id = 50;
ID NAME
---------- --------------------------------------------------
50 ITWILL
SYS@ora19c> select * from hr.reorg_test_temp where id = 50;
ID NAME
---------- --------------------------------------------------
50 ITWILL
SYS@ora19c> select * from hr.reorg_test where id = 1;
no rows selected
SYS@ora19c> select * from hr.reorg_test_temp where id = 1;
no rows selected재정의 프로세스 완료
SYS@ora19c> select object_name, object_id, data_object_id, to_char(created, 'yyyy-mm-dd hh24:mi:ss') create_date, to_char(last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') ddl_date, timestamp, status
from dba_objects
where object_name in ('REORG_TEST', 'REORG_TEST_TEMP'); 2 3
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID CREATE_DATE DDL_DATE TIMESTAMP STATUS
------------------------------ ---------- -------------- ------------------- ------------------- ------------------- -------
REORG_TEST_TEMP 75072 75072 2026-01-19 14:01:02 2026-01-19 14:10:47 2026-01-19:14:01:02 VALID
REORG_TEST 75070 75070 2026-01-19 13:46:18 2026-01-19 14:10:45 2026-01-19:13:46:18 VALID
SYS@ora19c> exec dbms_redefinition.finish_redef_table('hr','reorg_test','reorg_test_temp');
PL/SQL procedure successfully completed.
SYS@ora19c> select object_name, object_id, data_object_id, to_char(created, 'yyyy-mm-dd hh24:mi:ss') create_date, to_char(last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') ddl_date, timestamp, status
from dba_objects
where object_name in ('REORG_TEST', 'REORG_TEST_TEMP'); 2 3
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID CREATE_DATE DDL_DATE TIMESTAMP STATUS
------------------------------ ---------- -------------- ------------------- ------------------- ------------------- -------
REORG_TEST 75072 75072 2026-01-19 14:01:02 2026-01-19 13:46:18 2026-01-19:13:46:18 VALID
REORG_TEST_TEMP 75070 75070 2026-01-19 13:46:18 2026-01-19 14:19:55 2026-01-19:14:19:55 VALID- 원본 테이블과 임시 테이블의 object_id가 뒤바뀜
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
1 8 65536
SYS@ora19c> select extents, blocks, bytes from dba_segments where segment_name = 'REORG_TEST_TEMP';
EXTENTS BLOCKS BYTES
---------- ---------- ----------
9 72 589824
- 테이블 크기도 서로 뒤바뀜
SYS@ora19c> select constraint_name, constraint_type, index_name from dba_constraints where table_name = 'REORG_TEST';
CONSTRAINT_NAME C INDEX_NAME
------------------------------ - ------------------------------
REORG_TEMP_ID_PK P REORG_TEMP_ID_PK
SYS@ora19c> select constraint_name, constraint_type, index_name from dba_constraints where table_name = 'REORG_TEST_TEMP';
CONSTRAINT_NAME C INDEX_NAME
------------------------------ - ------------------------------
REORG_ID_PK P REORG_ID_PK- 제약 조건은 이름이 안 바뀜
reorg 시 컬럼 변경
SYS@ora19c> create table hr.reorg_test_temp(content varchar2(60), emp_id number constraint reorg_temp_id_pk primary key) tablespace users;
Table created.
exec dbms_redefinition.can_redef_table('hr', 'reorg_test', dbms_redefinition.cons_use_pk)
exec dbms_redefinition.start_redef_table('hr', 'reorg_test', 'reorg_test_temp', 'id emp_id, name content')
exec dbms_redefinition.sync_interim_table('hr', 'reorg_test', 'reorg_test_temp')
exec dbms_redefinition.finish_redef_table('hr','reorg_test','reorg_test_temp')SYS@ora19c> select count(*) from hr.reorg_test;
COUNT(*)
----------
100
SYS@ora19c> select * from hr.reorg_test where emp_id <= 10;
CONTENT EMP_ID
------------------------------------------------------------ ----------
table/index reorganization example 1
table/index reorganization example 2
table/index reorganization example 3
table/index reorganization example 4
table/index reorganization example 5
table/index reorganization example 6
table/index reorganization example 7
table/index reorganization example 8
table/index reorganization example 9
table/index reorganization example 10
10 rows selected.reorg 대상 테이블 찾기
SYS@ora19c> set serveroutput on;
SYS@ora19c> BEGIN
FOR sel_c_r IN
( select owner, segment_name, bytes, partition_name,
decode(partition_name,null,null,'.'||partition_name) is_part,
tablespace_name
from dba_segments
where segment_type= 'TABLE'
and owner ='HR'
)
LOOP
if (dbms_space.verify_shrink_candidate(sel_c_r.owner,
sel_c_r.segment_name,
'TABLE',
sel_c_r.bytes,
sel_c_r.partition_name ))
then
dbms_output.put_line(' ==> '||sel_c_r.owner||'.'||sel_c_r.segment_name
|| sel_c_r.is_part||', '||sel_c_r.bytes||'byte , TS NAME :'||sel_c_r.tablespace_name);
end if;
END LOOP;
END;
/
==> HR.INC_EMP, 655360byte , TS NAME :USERS
==> HR.JAN, 131072byte , TS NAME :USERS
==> HR.REORG_TEST, 589824byte , TS NAME :USERS
PL/SQL procedure successfully completed.'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글
| [아이티윌 오라클 DBA 과정 91기] 260121 TIL (1) | 2026.01.21 |
|---|---|
| [아이티윌 오라클 DBA 과정 91기] 260120 TIL (1) | 2026.01.20 |
| [아이티윌 오라클 DBA 과정 91기] 260116 TIL (0) | 2026.01.20 |
| [아이티윌 오라클 DBA 과정 91기] 260115 TIL (0) | 2026.01.15 |
| [아이티윌 오라클 DBA 과정 91기] 260114 TIL (1) | 2026.01.14 |