-- 실전 오라클 백업과 복구(생능출판사, 서진수 지음), 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
