HighLight in your life~

-- 실전 오라클 백업과 복구(생능출판사, 서진수 지음), p.144~p.154
-- 일반 테이블 스페이스 관리

목록
1. 일반 테이블 스페이스 생성 및 조회하기
2. 각 데이터 파일의 실제 사용량 확인하는 방법
3. 테이블 스페이스 용량 관리하기
 1) 수동으로 테이블 스페이스에 데이터 파일 추가하기
 2) 데이터 파일 크기 수동 증가시키기
 3) 데이터 파일 크기 자동 증가시키기
 4) 각 데이터 파일들의 autoextend 유무 확인하기

4. Tablespace Offline
 1) normal mode
 2) temporary mode
 3) immediate mode

-- 1. 일반 테이블 스페이스 생성 및 조회하기

SQL> create tablespace haksa
datafile '/u01/app/oracle/oradata/orcl/haksa01.dbf' size 10M
segment space management auto;

SQL> select tablespace_name, status, contents, extent_management,
segment_space_management
from dba_tablespaces;

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

-- 2. 각 데이터 파일의 실제 사용량 확인하는 방법
SQL> set line 200
SQL> col file# for 999
SQL> col ts_name for a10
SQL> col total_blocks for 9999999
SQL> col used_blocks for 9999999
SQL> col pct_used for a10

-- 아래 쿼리 오래 걸림
-- Production에서 운영 중에 하지 마시기 바랍니다.

SQL> select distinct d.file_id file#,
d.tablespace_name ts_name,
d.bytes/1024/1024 MB,
d.bytes/8192 total_blocks,
sum(e.blocks) used_blocks,
to_char(nvl(round(sum(e.blocks)/(d.bytes/8192),4),0)*100, '09.99')||' %' pct_used
from dba_extents e, dba_data_files d
where d.file_id = e.file_id(+)
group by d.file_id, d.tablespace_name, d.bytes
order by 1, 2;

FILE# TS_NAME            MB TOTAL_BLOCKS USED_BLOCKS PCT_USED
----- ---------- ---------- ------------ ----------- ----------
    1 SYSTEM            480        61440       60992  99.27 %
    2 UNDOTBS1           30         3840        3136  81.67 %
    3 SYSAUX            240        30720       30304  98.65 %
    4 USERS               5          640         376  58.75 %
    5 EXAMPLE           100        12800        8728  68.19 %
    6 UNDO01             10         1280         168  13.13 %
    7 HAKSA              10         1280              00.00 %

-- 3. 테이블 스페이스 용량 관리하기
SQL> col tablespace_name for a10
SQL> col file_name for a50
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
SQL> create table scott.iphak(studno number) tablespace haksa;

SQL> begin
 for i in 1..50000 loop
  insert into scott.iphak values(i);
 end loop;
 commit;
end;
/

-- 1) 수동으로 테이블 스페이스에 데이터 파일 추가하기
SQL> alter tablespace haksa
add datafile '/u01/app/oracle/oradata/orcl/haksa02.dbf' size 20M;

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

-- 2) 데이터 파일 크기 수동 증가시키기
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/haksa01.dbf' resize 20M;

-- 3) 데이터 파일 크기 자동 증가시키기
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/haksa01.dbf' autoextend on;

cf.'autoextend on' 옵션을 사용할 경우 데이터 파일은 자동으로 증가하게 되며 그 크기는 만약 오라클이 32bit일 경우에는 최대 파일 1개의 크기는 16GB까지 가능하며 오라클이 64bit용일 경우에는 최대 크기가 32GB까지 가능하게 된다.

-- 4) 각 데이터 파일들의 autoextend 유무 확인하기
SQL> set line 200
SQL> col tablespace_name for a10
SQL> col file_name for a50

SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "auto", online_status from dba_data_files;

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/haksa02.dbf' autoextend on;

SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "auto", online_status from dba_data_files;

-- 4. Tablespace Offline
-- 1) normal mode
SQL> alter tablespace haksa offline;

cf. Tablespace를 offline하게 되면 그 파일들에는 새로운 정보가 저장되지 않는다. 그래서 offline한 후에 online을 한다면 반드시 checkpoint를 발생시켜야 한다.

SQL> select file#, name, status from v$datafile;

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#;

SQL> alter tablespace haksa online;

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#;

SQL> alter system checkpoint;

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#;

-- 2) temporary mode

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/haksa02.dbf' offline; -- immediate mode

SQL> alter tablespace haksa offline; -- 에러 발생, 이미 offline되어 있음

SQL> alter tablespace haksa offline temporary;

SQL> recover tablespace haksa;

SQL> alter tablespace haksa online;

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#;

SQL> alter system checkpoint;

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#;

-- 3) immediate mode