Oracle - Tablespace & Datafile 관리

Notice

Recent Posts

Recent Comments

Link

Github

Today

Total

« 2025/05 »
일	월	화	수	목	금	토
				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

Tags more

Archives

관리 메뉴

mingming

Oracle - Tablespace & Datafile 관리 본문

Database

Oracle - Tablespace & Datafile 관리

mingming_96 2023. 10. 27. 17:43

Tablespace 관리

Tablespace 생성

SQL> create tablespace test datafile '/data01/ORCL/test01.dbf' size 100M;

Tablespace에 datafile 추가

SQL> alter tablespace test add datafile '/data01/ORCL/test02.dbf' size 100M;

Datafile resize

alter database datafile '/data01/ORCL/test02.dbf' resize 120M;

Datafile 자동 증가 옵션 설정

alter database datafile '/data01/ORCL/test02.dbf' autoextend on;

테이블스페이스 상태 확인

SQL> col file_name format a50
SQL> set linesize 200
SQL> select tablespace_name, file_name, bytes/1024/1024, autoextensible from dba_data_files;

TABLESPACE_NAME                FILE_NAME                                          BYTES/1024/1024 AUT
------------------------------ -------------------------------------------------- --------------- ---
SYSTEM                         /data01/ORCL/system01.dbf                                      700 YES
SYSAUX                         /data01/ORCL/sysaux01.dbf                                      550 YES
UNDOTBS1                       /data01/ORCL/undotbs01.dbf                                     355 YES
USERS                          /data01/ORCL/users01.dbf                                         5 YES
HR_USER                        /data01/ORCL/hr.dbf                                           1024 YES
TEST                           /data01/ORCL/test01.dbf                                        100 NO
TEST                           /data01/ORCL/test02.dbf                                        120 YES

SQL> select a.file#, a.ts#, b.name, a.status, a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts# ;

     FILE#        TS# NAME                           STATUS  CHECKPOINT_CHANGE#
---------- ---------- ------------------------------ ------- ------------------
         1          0 SYSTEM                         SYSTEM             1468477
         2          1 SYSAUX                         ONLINE             1468477
         3          2 UNDOTBS1                       ONLINE             1468477
         4          4 USERS                          ONLINE             1468477
         5          7 TEST                           ONLINE             1468477
         7          6 HR_USER                        ONLINE             1468477

Tablespace offline

SQL> alter tablespace test offline;

Tablespace altered.

SQL> @table

     FILE#        TS# NAME                           STATUS  CHECKPOINT_CHANGE#
---------- ---------- ------------------------------ ------- ------------------
         1          0 SYSTEM                         SYSTEM             1468477
         2          1 SYSAUX                         ONLINE             1468477
         3          2 UNDOTBS1                       ONLINE             1468477
         4          4 USERS                          ONLINE             1468477
         5          7 TEST                           OFFLINE            1470602
         7          6 HR_USER                        ONLINE             1468477

Tablespace online

SQL> alter tablespace test online;

Tablespace altered.

SQL> @table

     FILE#        TS# NAME                           STATUS  CHECKPOINT_CHANGE#
---------- ---------- ------------------------------ ------- ------------------
         1          0 SYSTEM                         SYSTEM             1468477
         2          1 SYSAUX                         ONLINE             1468477
         3          2 UNDOTBS1                       ONLINE             1468477
         4          4 USERS                          ONLINE             1468477
         5          7 TEST                           ONLINE             1470632
         7          6 HR_USER                        ONLINE             1468477

Datafile offline

데이터 파일을 오프라인시키면 테이블스페이스의 상태는 Recover 가 됩니다.

SQL> alter database datafile '/data01/ORCL/test02.dbf' offline;

SQL> @table

     FILE#        TS# NAME                           STATUS  CHECKPOINT_CHANGE#
---------- ---------- ------------------------------ ------- ------------------
         1          0 SYSTEM                         SYSTEM             1468477
         2          1 SYSAUX                         ONLINE             1468477
         3          2 UNDOTBS1                       ONLINE             1468477
         4          4 USERS                          ONLINE             1468477
         5          7 TEST                           ONLINE             1470836
         6          7 TEST                           RECOVER            1470871
         7          6 HR_USER                        ONLINE             1468477

Datafile online

데이터 파일을 온라인 하기 전 recover 단계를 거쳐야 합니다.

SQL> alter database datafile '/data01/ORCL/test02.dbf' online;
alter database datafile '/data01/ORCL/test02.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/data01/ORCL/test02.dbf'

SQL> recover datafile '/data01/ORCL/test02.dbf'
Media recovery complete.
SQL> alter database datafile '/data01/ORCL/test02.dbf' online;

Database altered.

SQL> @table

     FILE#        TS# NAME                           STATUS  CHECKPOINT_CHANGE#
---------- ---------- ------------------------------ ------- ------------------
         1          0 SYSTEM                         SYSTEM             1468477
         2          1 SYSAUX                         ONLINE             1468477
         3          2 UNDOTBS1                       ONLINE             1468477
         4          4 USERS                          ONLINE             1468477
         5          7 TEST                           ONLINE             1470836
         6          7 TEST                           ONLINE             1471820
         7          6 HR_USER                        ONLINE             1468477

7 rows selected.

Tablespace 시점 맞추기 - 체크포인트 발생

SQL> alter system checkpoint;

System altered.

SQL> @table

     FILE#        TS# NAME                           STATUS  CHECKPOINT_CHANGE#
---------- ---------- ------------------------------ ------- ------------------
         1          0 SYSTEM                         SYSTEM             1471878
         2          1 SYSAUX                         ONLINE             1471878
         3          2 UNDOTBS1                       ONLINE             1471878
         4          4 USERS                          ONLINE             1471878
         5          7 TEST                           ONLINE             1471878
         6          7 TEST                           ONLINE             1471878
         7          6 HR_USER                        ONLINE             1471878

Datafile 위치 변경 -- offline 가능한 테이블스페이스

1. 해당 테이블 스페이스 offline

alter tablepsace test offline;

2. Datafie 위치 변경

mv /data01/ORCL/test* /data02/ORCL/

3. Control file에 바뀐 위치 기록

alter tablespace test rename datafile '/data01/ORCL/test01.dbf' to '/data02/ORCL/test01.dbf';

alter tablespace test rename datafile '/data01/ORCL/test02.dbf' to '/data02/ORCL/test02.dbf';

4. 테이블 스페이스 온라인

alter tablespace test online;

5. 확인

select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/data01/ORCL/system01.dbf
/data01/ORCL/sysaux01.dbf
/data01/ORCL/undotbs01.dbf
/data01/ORCL/users01.dbf
/data02/ORCL/test01.dbf
/data02/ORCL/test02.dbf
/data01/ORCL/hr.dbf

Datafile 위치 변경 -- offline 불가능한 테이블스페이스 ( system, undo, default, tempory )

1. DB 종료

SQL> shutdown immeidate;

2. Datafile 이동

SQL> !mv /data01/ORCL/system01.dbf /data02/ORCL/

3. 마운트 상태로 시작

SQL> startup mount

4. 컨트롤 파일 내용 변경

alter database rename file '/data01/ORCL/system01.dbf' to '/data02/ORCL/system01.dbf';

5. DB 오픈

alter database open;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/data02/ORCL/system01.dbf
/data01/ORCL/sysaux01.dbf
/data01/ORCL/undotbs01.dbf
/data01/ORCL/users01.dbf
/data02/ORCL/test01.dbf
/data02/ORCL/test02.dbf
/data01/ORCL/hr.dbf

Tablespace 삭제

drop tablespace test including contents and datafiles;

위의 옵션을 사용해서 삭제해야 datafile 까지 삭제됩니다.

Tablespace 사용량 조회 Scripts

column TBS format a20
column Type format a10
column "Size (M)" format a15
select "Status", substr("Name",2) "TBS", "Type", "Size (M)", "Used (M)", "Used %", "Free (M)"
from (
SELECT d.status "Status",
decode(d.tablespace_name,'SYSTEM','1'||'SYSTEM','2'||d.tablespace_name) "Name",
d.contents "Type",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024, '99999999.999') "Used (M)",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0)-NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024, '99999999.999') "Free (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.contents like 'TEMPORARY')
) order by 6 desc;

Status    TBS                  Type       Size (M)        Used (M)      Used %  Free (M)
--------- -------------------- ---------- --------------- ------------- ------- -------------
ONLINE    SYSTEM               PERMANENT          700.000       452.625   64.66       247.375
ONLINE    USERS                PERMANENT            5.000         3.188   63.75         1.813
ONLINE    SYSAUX               PERMANENT          550.000       333.813   60.69       216.188
ONLINE    UNDOTBS1             UNDO               355.000        28.250    7.96       326.750
ONLINE    HR_USER              PERMANENT        1,024.000         1.000    0.10      1023.000
OFFLINE   TEST                 PERMANENT            0.000          .000    0.00          .000

Datafile 사용량 조회 Scripts

column Name format a50
column Tablespace format a20
set line 400
set pagesize 1000
SELECT "Status","Contents", "Name", "Auto", "Tablespace",
".Used %", ". Used / Total (M)" FROM (
SELECT /*+ ordered no_merge(v) */ v.status "Status",
d.file_name "Name",
x.contents "Contents",
d.tablespace_name "Tablespace",
NVL(d.autoextensible,'NO') "Auto",
TO_CHAR(NVL(d.bytes/1024/1024,0),'99999990.000') "Size (M)",
TO_CHAR(NVL((d.bytes-NVL(s.bytes,0))/1024/1024,0),'99999999.999') || ' / ' ||
TO_CHAR(NVL(d.bytes/1024/1024,0),'99999999.999') ". Used / Total (M)",
TO_CHAR(NVL((d.bytes-NVL(s.bytes,0))/d.bytes*100,0),'990.00') ".Used %"
FROM sys.dba_data_files d, v$datafile v,
(SELECT file_id,SUM(bytes) bytes FROM sys.dba_free_space GROUP BY file_id) s,
dba_tablespaces x
WHERE (s.file_id (+)= d.file_id) AND (d.file_name = v.name) and (x.TABLESPACE_NAME = d.TABLESPACE_NAME)
UNION ALL
SELECT /*+ ordered no_merge(v) */ v.status "Status",
d.file_name "Name",
x.contents "Contents",
d.tablespace_name "Tablespace",
NVL(d.autoextensible,'NO') "Auto",
TO_CHAR(NVL(d.bytes/1024/1024,0),'99999990.000') "Size (M)",
TO_CHAR(NVL(t.bytes_cached/1024/1024,0),'99999999.999') || ' / ' ||
TO_CHAR(NVL(d.bytes/1024/1024,0),'99999999.999') ". Used / Total (M)",
TO_CHAR(NVL(t.bytes_cached/d.bytes*100,0),'990.00') ".Used %"
FROM sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v, dba_tablespaces x
WHERE (t.file_id (+)= d.file_id) AND (d.file_id = v.file#) and (x.TABLESPACE_NAME = d.TABLESPACE_NAME)
) order by 5 asc, 6 desc;

Status  Contents              Name                                               Aut Tablespace           .Used % . Used / Total (M)
------- --------------------- -------------------------------------------------- --- -------------------- ------- -----------------------------
ONLINE  PERMANENT             /data01/ORCL/hr.dbf                                YES HR_USER                 0.10         1.000 /      1024.000
ONLINE  PERMANENT             /data01/ORCL/sysaux01.dbf                          YES SYSAUX                 60.69       333.813 /       550.000
SYSTEM  PERMANENT             /data02/ORCL/system01.dbf                          YES SYSTEM                 64.66       452.625 /       700.000
OFFLINE PERMANENT             /data02/ORCL/test01.dbf                            NO  TEST                    0.00          .000 /          .000
OFFLINE PERMANENT             /data02/ORCL/test02.dbf                            NO  TEST                    0.00          .000 /          .000
ONLINE  UNDO                  /data01/ORCL/undotbs01.dbf                         YES UNDOTBS1                7.96        28.250 /       355.000
ONLINE  PERMANENT             /data01/ORCL/users01.dbf                           YES USERS                  63.75         3.188 /         5.000

'Database' 카테고리의 다른 글

Oracle - 데이터파일 장애 복구 (1)	2023.10.27
Oracle - Redo Log File 관리 및 장애 복구 (0)	2023.10.27
Oracle - 컨트롤파일 유실 장애 복구 시나리오 (1)	2023.10.27
Current Redo Log File 유실 복구 (1)	2023.10.24

'Database' Related Articles

mingming

Oracle - Tablespace & Datafile 관리 본문

Oracle - Tablespace & Datafile 관리

Tablespace 관리

'Database' 카테고리의 다른 글

티스토리툴바