#테이블 스페이스 및 데이터 파일
oracle 은 데이터를 논리적으로 테이블스페이스에 저장
물리적으로는 데이터 파일에 저장
테이블스페이스 조회
SELECT A.TABLESPACE_NAME,
ROUND(A.BYTES_ALLOC / 1024 / 1024, 2) CURRENT_SIZE,
ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024, 2) FREE_SIZE,
ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024, 2) USED_SIZE,
ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) FREE_RATE,
100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) USED_RATE,
ROUND(MAXBYTES/1048576,2) MAX_SIZE
FROM ( SELECT F.TABLESPACE_NAME,
SUM(F.BYTES) BYTES_ALLOC,
SUM(DECODE(F.AUTOEXTENSIBLE, 'YES',F.MAXBYTES,'NO', F.BYTES)) MAXBYTES
FROM DBA_DATA_FILES F
GROUP BY TABLESPACE_NAME) A,
( SELECT F.TABLESPACE_NAME,
SUM(F.BYTES) BYTES_FREE
FROM DBA_FREE_SPACE F
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
UNION
SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES_USED + BYTES_FREE) / 1048576, 2),
ROUND(SUM(BYTES_FREE) / 1048576,2),
ROUND(SUM(BYTES_USED) / 1048576,2),
ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) FREE_RATE,
100 - ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) USED_RATE,
ROUND(MAX(BYTES_USED + BYTES_FREE) / 1048576, 2)
FROM SYS.V_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME
ORDER BY 1;
테이블 스페이스란 ?
하나 이상의 데이터파일로 구성
여러 논리적 저장 영역 단위로 분할
데이터 파일 ?
스키마 객체 데이터의 저장소
데이터베이스
---------------------------------------------------
테이블 스페이스 테이블스페이스 |
--------------------------| |------------------ ||
[데이터 파일] | | [테이터 파일] ||
[데이터 파일] | | [테이터 파일] ||
--------------------------- --------------------|
---------------------------------------------------
# system 테이블 스페이스 ( system , sysaux:성능튜닝을 위한 데이터 )
- 데이터베이스와 함께 생성
- 모든 데이터베이스에서 필요
- system 실행 취소 세그먼트를 포함
# 비 system 테이블 스페이스 ( 일반, 임시, 언두 )
- 실행취소 세그먼트, 임시 세그먼트, 응용 프로그램 데이터 세그먼트 및 응용 프로그램 인덱스 세그먼트
- 백업 요구사항에 따라 테이블 분리
- 동적 데이터 , 정적데이터
- 사용자 객체에 할당된 공간의 양을 제어
## 지역적 테이블스페이스
- 비트맵 사용
- 데이터딕셔너리 사용x
- 비트맵에 의해 익스텐트 정보의 사용 유무를 확인 할 수 있음
ex)
CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL
## 딕셔너리 관리 테이블스페이스
- 확장 영역이 할당되거나 할당이 해제될 때 해당 테이블 갱신
=> DML 등에 의해 확장영역이 할당, 해제 될때마다 갱신이 된다. - i/o 많이 씀
변경이 이루어진 익스턴트 정보를
-> 시스템테이블스페이스의 데이터딕셔너리의 데이터파일 익스텐트 정보를 변경시킴
ex)
CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf'SIZE 500M
EXTENT MANAGEMENT DICTIONARY
## undo 테이블스페이스
- rollback 수행
- 확장 역역이 지역적으로 관리됨
- transaction 이 끝나지 않은 데이터는 변경전 데이터를 보여줌
ex)
CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/oradata/undo01.dbf' SIZE 40M;
**** transaction 이 끝나지 않은 데이터는 변경전 데이터를 보여줌
테스트
1) 유저 USER_1,USER_2 생성
create user user_1
identified by user1
default tablespace DATA01
create user user_2
identified by user2
default tablespace DATA01
* 사용자 두명 resource ( create 시스템 권한) conncect ( create session 권한) 부여
grant resource, connect to user_1, user_2
2) 두명의 유저 모두 UNDO_TEST 테이블 접근 가능
conn user_1/user1
create table UNDO_TEST(
ID NUMBER(10),
NAME VARCHAR2(10)):
insert into UNDO_TEST values('1','pbj');
conn sys/
grant select on user_1.UNDO_TEST to user_2
SQL> col grantee format a10
SQL> col owner format a10
SQL> col table_name format a15
SQL> col grantor format a10
SQL> col privilege format a10
SQL>
SQL> select * from dba_tab_privs
2 where grantee ='USER_2';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ---------- --------------- ---------- ---------- --- ---
USER_2 USER_1 UNDO_TEST USER_1 SELECT NO NO
4) USER_2 UNDO_TEST 조회
SQL> conn user_2/user2
Connected.
SQL> select * from user_1.UNDO_TEST;
no rows selected
5) USER_1 Commit
SQL> conn user_1/user1
Connected.
SQL> select * from UNDO_TEST;
ID NAME
---------- ----------
1 pbj
6) USER_2 UNDO_TEST 조회
SQL> Conn user_2/user2
Connected.
SQL> select * from user_1.UNDO_TEST;
ID NAME
---------- ----------
1 pbj
** 언두 테이블스페이스는 크기만 증가되고 줄어들지 않는다.
- HWM 에 의해
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' resize 30M;
alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' resize 30M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
## 임시 테이블스페이스
- 정렬 작업에 사용 ( order by, group by )
- 영구 객체를 포함할수 없다
- 지역적으로 관리되는 확장 영역 권장
- 인덱스 생성
-
temp3 의 그룹을 group2로 변경.
-- group2가 없으면 자동으로 생성
SQL> ALTER TABLESPACE temp3 TABLESPACE GROUP group2;
-- temp2 를 그룹에서 제거
SQL> ALTER TABLESPACE temp2 TABLESPACE GROUP '';
-- temp1 의 그룹을 group2로 변경
SQL> ALTER TABLESPACE temp1 TABLESPACE GROUP group2;
-- Temporary Tablespace group2그룹을 디폴트로 설정
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE group2;
-- 사용자 생성지 지정
SQL> CREATE USER hr IDENTIFIED BY hr DEFAULT
TABLESPACE TS1 TEMPORARY TABLESPACE group2;
-- ALTER 명령으로 사용자에게 지정
SQL> ALTER USER hr TEMPORARY TABLESPACE group2;
TEMPORARY GROUP 을 삭제하진 못함.
1) TEMP TABLESPACE 새로 생성.
2) ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP << 기본 템포러리 변경
3) GROUP 에 속한 템포러리 테이블스페이스 제거
ex )
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
* Temporary tablespace group 개념 ( 11g)
여러 사용자가 동시에 order by 같은 정렬작업 수행시 PGA 가 부족할 경우
Schema 의 temporary tablespace 를 사용함
=> temporary tablespace group 개념을 도입
temporary tablespace 여러개 만든 후
group 으로 묶고 특정 schema 에게 이 group 을 지정
ex)
create temporary tablespace temp4
tempfile '/u01/app/oracle/oradata/orcl/temp4.dbf' size 50m
extent management local
tablespace roup temp_group1
create temporary tablespace temp5
tempfile '/u01/app/oracle/oradata/orcl/temp5.dbf' size 50m
extent management local
alter tablespace temp5 tablespace group temp_group1;
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_GROUP1 TEMP2
TEMP_GROUP1 TEMP3
TEMP_GROUP1 TEMP4
TEMP_GROUP1 TEMP5
* 오렌지 space 에서는 그룹형태로 보이지 않음 ( 명령어로만 확인 가능 )
# scott 사용자에게 group 할당
alter user scott temporary tablespace TEMP_GROUP1
# default temporary 로 지정
alter database default temporary tablespace TEMP_GROUP1
select * from dba_users
WHERE username = 'SCOTT'
SQL> select USERNAME ,TEMPORARY_TABLESPACE from dba_users
2 WHERE username = 'SCOTT'
3 ;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SCOTT TEMP_GROUP1
alter tablespace temp2 tablespace group ''; << 그룹에서 제거
# 읽기 전용 테이블스페이스
- 테이블 스페이스에 기록작업 x
#테이스할 haksa 테이블스페이스 생성 ( 지역 테이블스페이스)
CREATE TABLESPACE HAKSA
DATAFILE '/home/oracle/haksa_dat01.dbf' size 1M
extent management local
#테스트에 쓰일 테이블 생성 ( tablespace 는 haksa 로 지정)
CREATE TABLE SCOTT.IPHAK(NO NUMBER)
TABLESPACE HAKSA;
#테스트에 쓰일 sql 문 ( loop )
vi ts_insert.sql=
begin
for i in 1..5000000 loop
insert into scott.iphak values (i);
end loop;
commit;
end;
/
SQL> @ts_insert
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.IPHAK by 8 in tablespace HAKSA
ORA-06512: at line 5
haksa tablespace 모두 사용중
HAKSA 1 128 120 93.75%
## 방법 1 - 수동으로 tablespace 에 data file 추가
alter tablespace haksa add datafile '/home/oracle/haksa_dat02.dbf' size 10M;
18 HAKSA 1 128 120 93.75%
19 HAKSA 10 1280 00.00%
## 방법 2 - Data file 크기 수동 증가시키기
alter database datafile '/home/oracle/haksa_dat01.dbf' resize 10M;
18 HAKSA 10 1280 120 09.38%
19 HAKSA 10 1280 00.00%
## 방법 3 - Data file 크기 자동 증가시키기
alter database datafile '/home/oracle/haksa_dat01.dbf' autoextend on;
select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "Auto", online_status
from dba_data_files
where tablespace_name ='HAKSA';
Aut
HAKSA 10 /home/oracle/haksa_dat01.dbf YES ONLINE
alter database datafile '/home/oracle/haksa_dat02.dbf' autoextend on;
HAKSA 10 /home/oracle/haksa_dat01.dbf YES ONLINE
HAKSA 10 /home/oracle/haksa_dat02.dbf YES ONLINE
SQL> @ts_insert
18 HAKSA 32.0625 4104 3968 96.69%
19 HAKSA 32.0625 4104 3840 93.57%
=> 10M 에서 32M까지 증가함(autoextend on)
# Tablespace Offline Mode
- tablesapce 만 shutdown 의미
*mode
1) Normal Mode : tablespace 에 아무런 문제가 없을 때 하는 정상적인 방법이다.
2) Temporary Mode : 현재 Offline 시키고자 하는 tablespace 의 data file 이 하나라도 이상이 생기게 되면 쓰는 방법이다.
3) Immediate Mode : archive log mode 일 경우에만 사용 가능하다. 이 옵션은 Data file 에 장애가 나서 데이터를 내려 쓰지 못하는 상황에서 쓴다.
test
#tablespace offline
alter tablespace haksa offline;
select file#, name, status from v$datafile where status='OFFLINE';
18 /home/oracle/haksa_dat01.dbf OFFLINE
19 /home/oracle/haksa_dat02.dbf OFFLINE
alter tablespace haksa ONLINE;
select file#, name, status from v$datafile where file# in ('18', '19');
18 /home/oracle/haksa_dat01.dbf ONLINE
19 /home/oracle/haksa_dat02.dbf ONLINE
#특정 datafile만 offline 시키기
alter database datafile '/home/oracle/haksa_dat01.dbf' offline;
select file#, name, status from v$datafile;
18 /home/oracle/haksa_dat01.dbf RECOVER
19 /home/oracle/haksa_dat02.dbf ONLINE
=> 강제로 offline 시킬시 STATUS 상태가 'RECOVER' 상태로 변함
## Tablespace offline 과 checkpoint 관계
13 /home/oracle/pbj_dat01.dbf ONLINE
18 /home/oracle/haksa_dat01.dbf RECOVER
19 /home/oracle/haksa_dat02.dbf ONLINE
SQL> alter tablespace pbj_dat offline
13 /home/oracle/pbj_dat01.dbf OFFLINE
18 /home/oracle/haksa_dat01.dbf RECOVER
19 /home/oracle/haksa_dat02.dbf ONLINE
select a.file#,
a.ts#,
b.name,
a.status,
a.checkpoint_change#
from v$datafile a,
v$tablespace b
where a.ts#=b.ts#
and file# in ('11','12','13','18','19');
checkpoint_change#
11 12 TBS_C ONLINE 2471243
12 13 TBS_D ONLINE 2471243
13 14 PBJ_DAT OFFLINE 2478767
18 24 HAKSA RECOVER 2477976
19 24 HAKSA ONLINE 2471243
=> OFFLINE, RECOVER 만 체크포인트 값이 다름
pbj_dat 을 온라인으로 변경
alter tablespace pbj_dat online;
13 /home/oracle/pbj_dat01.dbf ONLINE
18 /home/oracle/haksa_dat01.dbf RECOVER
19 /home/oracle/haksa_dat02.dbf ONLINE
checkpoint_change#
11 12 TBS_C ONLINE 2471243
12 13 TBS_D ONLINE 2471243
13 14 PBJ_DAT ONLINE 2478876
18 24 HAKSA RECOVER 2477976
19 24 HAKSA ONLINE 2471243
=> pbj_dat tablespace 를 online 시켯지만 여전히 값이 다름
=> 이럴 경우에는 체크포인트를 수동으로 동기화를 시켜야함
#alter system checkpoint;
checkpoint_change#
11 12 TBS_C ONLINE 2479014
12 13 TBS_D ONLINE 2479014
13 14 PBJ_DAT ONLINE 2479014
18 24 HAKSA RECOVER 2477976
19 24 HAKSA ONLINE 2479014
=> 같아진것을 확인
#haksa tablespace offline
SQL> alter tablespace haksa offline;
alter tablespace haksa offline
*
ERROR at line 1:
ORA-01191: file 18 is already offline - cannot do a normal offline
ORA-01110: data file 18: '/home/oracle/haksa_dat01.dbf'
=> 하나의 data file 에 문제가 있는 경우, 정상적인 offline 방법으로는 내려가지 않음 >>> temporary 사용
SQL> alter tablespace haksa offline temporary;
Tablespace altered.
select file#,
name,
status
from v$datafile
where file# in ('13','18','19');
13 /home/oracle/pbj_dat01.dbf ONLINE
18 /home/oracle/haksa_dat01.dbf RECOVER
19 /home/oracle/haksa_dat02.dbf OFFLINE
SQL> recover tablespace haksa;
Media recovery complete.
SQL> alter tablespace haksa online;
Tablespace altered
11 12 TBS_C ONLINE 2479165
12 13 TBS_D ONLINE 2479165
13 14 PBJ_DAT ONLINE 2479165
18 24 HAKSA ONLINE 2479575
19 24 HAKSA ONLINE 2479575
SQL> alter system checkpoint;
11 12 TBS_C ONLINE 2479666
12 13 TBS_D ONLINE 2479666
13 14 PBJ_DAT ONLINE 2479666
18 24 HAKSA ONLINE 2479666
19 24 HAKSA ONLINE 2479666
# tablespace 삭제
SQL> drop tablespace haksa;
drop tablespace haksa
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
=> 테이블이 들어가있기 때문에 삭제가 되지 않음
SQL> drop tablespace example including contents and datafiles;
Tablespace dropped.
#제약조건까지 무시
drop tablespace EXAMPLE including contents cascade constraints;
'Oracle > tablespace' 카테고리의 다른 글
| HWM (0) | 2022.01.25 |
|---|