예제51. 테이블 스페이스 생성과 관리에 대한 A4지 한장이 머리속에 있어야 한다
오라클 저장 영역 구조
위의 구조에서 tablespace 의 종류를 배웁니다
*테이블 스페이스 생성 시 고려사항
-
extent 관리 방법
(1) dictionary 관리 방식
(2) local 관리 방식 -
저장되는 data 의 성격
(1) permanent
(2) temporary
(3) undo
*테이블 스페이스 공간을 추가
(1) data file 추가
(2) 기존의 data file을 resize
예제 52 데이터를 저장하기 위한 첫걸음 : 테이블 스페이스 생성
■ 실습1.
#1. 현재 인스턴스가 뭐가 떠있는지 확인하시오 !
$ ps -ef |grep pmon
#2. . oraenv 를 이용해서 PROD 를 사용할 수 있는 환경이 되게 합니다.
PROD
/u01/app/oracle/product/11.2.0/dbhome_1
#3. sys 유져로 접속합니다.
#4. 인스턴스가 open 인지 확인합니다.
#5. datafile 이 어디에 있는지 조회합니다.
#6. ts01 테이블 스페이스를 생성합니다.
prod> create tablespace ts01
datafile '/u01/app/oracle/oradata/PROD/disk1/ts01.dbf' size 10m;
home 밑에 oracle 밑에 파일명 데이터파일 사이즈는 10m emp011 을 테이블스페이스 ts01 안에 만들겠다.
#7. 잘생성되었는지 확인합니다.
PROD> @tablespace
문제1. 사이즈를 50m 로 해서 ts02 라는 테이블 스페이스를 생성하시오 !
prod> create tablespace ts02
datafile '/u01/app/oracle/oradata/PROD/disk1/ts02.dbf' size 50m;
문제2. scott 유져로 접속해서 ts02 테이블 스페이스에 emp 테이블과 똑같은
테이블을 생성하는데 emp_backup 이라는 이름으로 생성하시오 !
PROD>
create table emp_backup
tablespace ts02
as
select * from emp;
PROD> select count(*) from emp_backup;
문제3. 데이터 이행용 오라클 관리 이수자평가 준비 문제
테이블 스페이스를 생성할 때 테이블을 저장하는 테이블 스페이스와 인덱스를 저장하는 테이블 스페이스를
서로 분리해야 성능이 좋아집니다.
인덱스를 저장하기 위한 테이블 스페이스를 indx_ts01 라는 테이블 스페이스 이름으로 사이즈는 10m로 생성하는데
sys유져에서 생성하시오 !
create tablespace indx_ts01
datafile '/u01/app/oracle/oradata/PROD/disk1/indx_ts01.dbf' size 10m;
@datafile 해서 주소 비슷하게 넣어라
문제4. scott 유져에서 emp_backup 테이블에 sal 인덱스를 거는데
이 인덱스의 데이터가 indx_ts01 테이블 스페이스에 생성되게하시오
PROD > connect scott/tiger
PROD > show user
create index emp_backup_sal
on emp_backup(sal)
tablespace indx_ts01;
PROD > select index_name, tablespace_name
2 from user_indexes;
※ 중요내용 정리 : 테이블과 인덱스는 서로 다른 테이블 스페이스에 저장해야 합니다
- 성능이 좋아집니다
- datafile 이 깨졌을 때 복구가 빨라집니다.
문제5. 실습
sys
#1. ts03 이라는 테이블 스페이스를 사이즈 10 m 로 만든다
create tablespace ts03
datafile '/u01/app/oracle/oradata/PROD/disk1/ts03.dbf' size 10m;
scott
#2. ts03 테이블 스페이스에 scott 계정의 dept 테이블의 백업본인 dept_backup 을 생성
create table dept_backup
tablespace ts03
as
select * from dept;
#3. indx_ts03 테이블 스페이스를 사이즈 10m 로 만든다
create tablespace indx_ts03
datafile '/u01/app/oracle/oradata/PROD/disk1/indx_ts03.dbf' size 10m;
#4. dept_backup 테이블에 deptno 인덱스
indx_ts03 테이블 스페이스에 생성하시오
create index dept_backup_deptno
on dept_backup(deptno)
tablespace indx_ts03;
예제 53 테이블스페이스를 삭제할 때는 이 옵션을 알아야 해요 책 7-16
문법 drop tablespace ts01 including contents and datafiles;
설명 including contents 를 써야 테이블 스페이스 안에 데이터도 같이 삭제됨.
테이블 스페이스 안에 테이블이 하나라도 있으면 반드시 including contents 를 사용해야 합니다
including datafiles 를 써야 os 의 물리적 datafile 까지 같이 삭제 됩니다
이 옵션을 안쓰고 그냥 drop 하면 os 에 물리적 파일이 남아있습니다
괜히 공간만 차지하고 있고 dba 를 불안하게 합니다
TS01 AVAILABLE /u01/app/oracle/oradata/PROD/disk1/ts01.dbf
#2. ts01 테이블 스페이스를 drop 하는데 물리적인 datafile 까지 같이 삭제되게 하시오
PROD> drop tablespace ts01 including contents and datafiles;
#3. ts02 테이블 스페이스를 삭제하는데 including contents and datafiles 없이 그냥 삭제해봐라
왜 이런 에러가 발생하냐면 : ts02에 테이블이 있어서 입니다
PROD > drop tablespace ts02 including contents;
and datafiles 없이 해본 os 에 남았는지 보려고
rm02.dbf 로 물리적으로는 남아있는데
지우기 부담스러움... 죽은 파일이긴 한데...
※ rm 으로 지우지 말고 반드시 including contents and datafiles 를 쓰자 !!
문제1. 아래의 테이블 스페이스만 남겨두고 나머지 다 드랍 !
PROD > @tablespace
INDX_TS01 TS03 INDX_TS03 드랍
drop tablespace indx_ts01 including contents and datafiles;
drop tablespace ts03 including contents and datafiles;
drop tablespace indx_ts03 including contents and datafiles;
▩ 예제54. default tablespace 가 뭔지 알고 있어야합니다.
다음과 같이 테이블을 생성했을 때 테이블이 어느 테이블 스페이스에 생성될까요?
PROD > connect scott/tiger
Connected.
PROD > create table emp10
2 (empno number(10),
3 ename varchar2(10) );
Table created.
Enter value for table_name: emp10
system tablespace 에 사용자 데이터를 담는 테이블을 저장하면 안된다
system tablespace 는 system 데이터가 저장되어야 합니다.
실습
#1. default tablespace 로 지정하기 위해 test01 이라는 테이블 스페이스를 생성합니다.
#2. test01 이라는 테이블 스페이스를 디폴트 테이블스페이스로 지정해줌
PROD > alter database default tablespace test01;
database property : 데이터베이스의 설정 상태를 볼 수 있음.
#3. scott 유져에서 emp500 이라는 테이블을 아래와 같이 생성해보고 어느 테이블 스페이스에 만들어졌는지 확인하시오
connect scott/tiger
문제1. example 이라는 테이블 스페이스는 100m 로 생성하고 이 테이블 스페이스를 default tablespace 로 지정하고
잘 지정되었는지 확인하시오 ~~
사용한 쿼리
create tablespace example
datafile '/u01/app/oracle/oradata/PROD/disk1/example.dbf' size 100
alter database default tablespace example;
drop tablespace test01 including contents and datafiles;
▩ 예제55. 테이블 스페이스에 공간이 꽉차면 공간을 추가할 줄 알아야해요. p7-12
공간을 추가하는 방법 2가지
- 데이터 파일 추가
기존의 데이터파일과 다른 이름으로 하기
실습
#1. test100이라는 테이블 스페이스를 사이즈 10m 로 만드시오
create tablespace test100
datafile '/u01/app/oracle/oradata/PROD/disk1/test100.dbf' size 10m;
#2. test100 테이블 스페이스 의 공간을 확인 하시오
#3. scott 유져에서 test100 테이블 스페이스에 emp 테이블과 똑같은 emp100 테이블을 생성
PROD > connect scott/tiger
Connected.
PROD > create table emp100
tablespace test100
as
select * from emp;
Table created.
#4. emp100 테이블에 emp100 테이블을 반복해서 입력해서 테이블스페이스의 공간을 꽉 차게 하시오
insert into emp100
select * from emp100;
@space.sql
꽉 찼음
#5. test100 테이블 스페이스의 공간을 10메가 더 늘려라
@tablespace
TEST100 AVAILABLE /u01/app/oracle/oradata/PROD/disk1/test100.dbf
alter tablespace test100
add datafile '/u01/app/oracle/oradata/PROD/disk1/test100b.dbf' size 10m;
점심시간문제 다시 test100 테이블 스페이스의 공간을 데이터로 꽉 차게 하고 다시 10m 더 늘리시오 !
alter tablespace test100
add datafile '/u01/app/oracle/oradata/PROD/disk1/test100c.dbf' size 10m;
점심시간문제 다시 test100 테이블 스페이스의 공간을 데이터로 꽉 차게 하고 다시 10m 더 늘리시오 !
alter tablespace test100
add datafile '/u01/app/oracle/oradata/PROD/disk1/test100c.dbf' size 10m;
예제 56 과도한 정렬 작업을 위해서는 Temporary tablespace 를 잘 이해하고 있어야 합니다
pga에서 정렬을 해야되는데 1억건처럼 너무 많으면 못해
temp 라는 테이블 스페이스를 데이터 임시 공간으로 써야한다.
그림설명
평상시와는 다르게 과도한 정렬 작업을 해야한다면 temp tablespace 가 필요합니다
※ 정렬을 일으키는 sql
1. order by
2. create index 생성
3. sort merge join
4. minus
5. intersect
6. union
※ temporary tablespace 를 사용하는 sql
- 정렬을 일으키는 sql
- with
- 임시테이블(temporary table)
과도한 정렬을 일으키면 temporary tablespace 가 full 이 나고 더이상 정렬 sql 이 수행 되지 않고
에러가 나면서 실패합니다.
실습
#1. 테이블 스페이스를 조회하는데 테이블 스페이스의 속성을 확인해서 조회하시오 !
SELECT TABLESPACE_name, contents
FROM dba_tablespaces;
#2. temporary tablespace 의 전체 사이즈와 사용량을 조회하는 쿼리를 수행하시오 !
-- 임시 테이블스페이스의 전체 사이즈와 사용량 함께 조회
select d.tablespace_name,
d.total_mb,
(d.total_mb - t.free_mb) as used_mb,
t.free_mb
from (select tablespace_name,
sum(bytes)/1024/1024 as total_mb
from dba_temp_files
group by tablespace_name) d,
(select tablespace_name,
sum(bytes_free)/1024/1024 as free_mb
from v$temp_space_header
group by tablespace_name) t
where d.tablespace_name = t.tablespace_name;
#3. 과도한 정렬 작업을 일으키기 위한 테이블을 생성합니다
#4. 위의 테이블에 인덱스를 생성해서 정렬작업을 일으키면 temporary tablespace 의
사용량이 늘어나는지 확인하시오 !
alter session set workarea_size_policy=manual; : 정렬작업과 관련한 메모리 관리를 수동으로 하겠다.
alter session set sort_area_size = 10;
select /+ leading(a b) use_merge(b) /a., b.
from orders a, orders b
where a.customer_id = b.customer_id
order by a.customer_id desc;
@temp_space.sql
코드를 입력DROP TABLE ORDERS;
CREATE TABLE ORDERS
AS
WITH TEMP_ORDERS AS(
SELECT /*+ MATERIALIZE */
TRUNC(DBMS_RANDOM.VALUE(1, 2193)) ORDER_DATE_CD
, DECODE(TRUNC(DBMS_RANDOM.VALUE(0, 2)), 1, 'direct', 'online') ORDER_MODE
, 'C'||LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 50001)), 5, '0') CUSTOMER_ID
, 'E'||LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 643)), 3, '0') EMPLOYEE_ID
, TRUNC(DBMS_RANDOM.VALUE(1, 11)) ORDER_STATUS
, 0 ORDER_TOTAL
FROM DUAL
CONNECT BY LEVEL <= 100000)
SELECT 'O'||LPAD(ROW_NUMBER() OVER(ORDER BY A.ORDER_DATE_CD), 8, '0') ORDER_ID
, TO_DATE(B.DATE_ID||LPAD(TRUNC(DBMS_RANDOM.VALUE(0, 24)), 2, '0')||
LPAD(TRUNC(DBMS_RANDOM.VALUE(0, 60)), 2, '0')||
LPAD(TRUNC(DBMS_RANDOM.VALUE(0, 60)), 2, '0'), 'YYYYMMDDHH24MISS') ORDER_DATE
, ORDER_MODE, CUSTOMER_ID, EMPLOYEE_ID, ORDER_STATUS, ORDER_TOTAL
FROM TEMP_ORDERS A
, (SELECT /*+ NO_MERGE */ ROWNUM DATE_CD
, YYYY||MM||DD DATE_ID
FROM (SELECT LPAD(ROWNUM,2,'0') MM FROM DUAL CONNECT BY LEVEL <= 12)
, (SELECT LPAD(ROWNUM,2,'0') DD FROM DUAL CONNECT BY LEVEL <= 31)
, (SELECT ROWNUM + 2006 YYYY FROM DUAL CONNECT BY LEVEL <= 6)
WHERE DD<=DECODE(MM,'01','31','03','31','04','30','05','31','06','30',
'07','31','08','31','09','30','10','31','11','30','12','31',
'02',DECODE(MOD(TO_NUMBER(YYYY),4),0,'29','28')) ) B
WHERE A.ORDER_DATE_CD = B.DATE_CD;
Table created.
#4. 위의 테이블에 인덱스를 생성해서 정렬작업을 일으키면 temporary tablespace 의
사용량이 늘어나는지 확인하시오 !
alter session set workarea_size_policy=manual; : 정렬작업과 관련한 메모리 관리를 수동으로 하겠다.
alter session set sort_area_size = 10;
select /+ leading(a b) use_merge(b) /a., b.
from orders a, orders b
where a.customer_id = b.customer_id
order by a.customer_id desc;
@temp_space.sql
#5. 또다른 과도한 정렬작업을 일으키게 되면 수행한 sql 이 실패하는지 확인하시오 !
select /+ leading(a b) use_merge(b) / a., c.
from orders a, orders b, orders c
where a.customer_id = b.customer_id and b.customer_id = c.customer_id
order by a.customer_id desc;
에러가 발생했다고 가정하고 해결해보자
문제1. temp 테이블 스페이스의 공간을 늘리시오 !
select file_name
from dba_temp_files;
alter tablespace temp
add tempfile '/u01/app/oracle/oradata/PROD/disk1/temp02.dbf' size 10m;
@temp_sapce.sql
과도한 정렬을 일으키는 sql 을 수행하기 전에 반드시 temporary tablespace 의 여유공간이 있는지
확인하고 정렬작업을 해야 두번 일하지 않는다.
여유가 있는지 확인 후 없다면 alter tablespace temp add tempfile 로 늘려놓고 해야함
add tempfile 인것이 중요 ~!!!!!
문제2. temporary tablespace 의 공간을 더 늘리시오 !
temp data file 을 두개 더 추가해서 여유공간을 충분히 확보 하시오
alter tablespace temp
add tempfile '/u01/app/oracle/oradata/PROD/disk1/temp03.dbf' size 10m;
alter tablespace temp
add tempfile '/u01/app/oracle/oradata/PROD/disk1/temp04.dbf' size 10m;
위와같이 이미 추가한 데이터 파일은 추가 된 상태로 그냥 남아있는 것입니다
※ dba를 위한 팁!
아주 큰 정렬 작업을 지금 딱 1번만 하면 되고 평상시에는 크게 할 일이 없다면
지금 바로 할 정렬 작업을 위한 또 다른 temporary tablespace 를 생성하고 사용하면 된다
그러고 나서 나중에 원래 temporary tablespace 를 원상복구 시키면 된다.
무분별하고 크게 만든 temporary tablespace 를 정리하고 싶다면 새로운 temporary tablespace를
만들고 만든 그 테이블 스페이스를 default temporary tablespace 로 사용하면 됩니다.
실습
#1. 현재 사용중인 default temporary teblespace 가 뭔지 확인합니다
#2. temp2 라는 temporary tablespace 를 10m 로 생성합니다
create temporary tablespace temp2
tempfile '/u01/app/oracle/oradata/PROD/disk3/temp2_01.dbf' size 10
#4. 기존 temporary tablespace 를 drop
drop tablespace temp including contents and datafiles;
문제3. temp3 이라는 temporary tablespace 를 사이즈 10m 로 만들고
temp3 을 defalut temporary tablespace 로 지정 후 기존 temp2 테이블 스페이스를 drop
create temporary tablespace temp3
tempfile '/u01/app/oracle/oradata/PROD/disk3/temp3_01.dbf' size 10m;
alter database default temporary tablespace temp3;
drop tablespace temp2 including contents and datafiles;
예제57 과도한 DML 작업이 성공적으로 수행되려면 UNDO tablespace 를 잘 이해하고 있어야 해요
select tablespace_name, contents
from dba_tablespaces;
undo 취소하다
undo data 는 취소를 하기 위해 필요한 데이터, 롤 백을 위해 필요한 데이터.
delete from emp;
emp 테이블을 싹 지우고 undo tablespace 에 데이터가 저장된다.
※ undo data 를 발생시키는 sql ?
취소가 가능한 sql ?
- delete
- update
- insert
- merge
실습
#1. database 에서 사용하는 기본 undo tablespace 가 무엇인지 확인합니다
show parameter undo_tablespace
#2. undotbs2 라는 이름으로 undo tablespace 를 생성하시오 !
create undo tablespace undotbs2
datafile '/home/oracle/undotbs2.dbf' size 2m;
#3. undotbs2 를 기본 undo tablespace 로 지정하시오
alter system set undo_tablespace='UNDOTBS2' scope=both;
#4. 과도한 dml 작업을 일으켜서 실패하는지 확인
과도한 delete 작업을 일으켰기 때문에
undo tablespace 보다 지워낸 양이 많아서 에러가 난다
#5. undotbs2 undo tablespace 에 공간을 추가해서 위의 상황을 해결하시오 !
UNDOTBS2 AVAILABLE /home/oracle/undotbs2.dbf
alter database datafile '/home/oracle/undotbs2.dbf' resize 100m;
문제1. 기본 undo tablespace 를 다시 undotbs 로 돌려놓고 undotbs2 는 drop
alter system set undo_tablespace='UNDOTBS' scope=both;
drop tablespace undotbs2 including contents and datafiles;
예제 58 만약 datafile 이름을 변경하거나 다른 위치로 이동하고 싶다면
테이블 스페이스를 offine 해야한다
※ 테이블 스페이스를 언제 offline 시켜야 하는가 ?
- datafile 이름을 변경할 때
- datafile 을 다른 위치로 변경할 때
- 실수로 datafile 을 rm 으로 삭제해서 db가 안올라올 때
- datafile 이 손상되어서 복구가 필요할 때
실습
#1. ts100 이라는 테이블 스페이스를 /home/oracle 밑에 datafile 을 두고 생성합니다
#2. ts100 테이블 스페이스에 emp 와 같은 테이블은 emp1000 테이블을 생성합니다
#3. ts100 테이블 스페이스의 datafile 인 /home/oracle/ts100.dbf 를
/u01/app/oracle/oradata/PROD/disk1 밑으로 이동합니다
#4. emp1000 테이블이 잘 조회되는지 확인합니다
select count(*) from emp1000;
#1. ts100 이라는 테이블 스페이스를 /home/oracle 밑에 datafile 을 두고 생성합니다
create tablespace ts100
datafile '/home/oracle/ts100.dbf' size 10m;
#2. ts100 테이블 스페이스에 emp 와 같은 테이블은 emp1000 테이블을 생성합니다
PROD > create table emp1000
2 tablespace ts100
3 as select * from emp;
Table created.
#3. ts100 테이블 스페이스의 datafile 인 /home/oracle/ts100.dbf 를
/u01/app/oracle/oradata/PROD/disk1 밑으로 이동합니다
이 때 오프라인 시켜줘야 함.
TS100 AVAILABLE /home/oracle/ts100.dbf
/u01/app/oracle/oradata/PROD/disk1 밑으로 이동합니다
alter tablespace ts100 offline;
os 로 나가서 datafile 을 옮긴다
$ mv /home/oracle/ts100.dbf /u01/app/oracle/oradata/PROD/disk1/ts100.dbf
PROD > alter database rename file '/home/oracle/ts100.dbf'
2 to '/u01/app/oracle/oradata/PROD/disk1/ts100.dbf';
alter tablespace ts100 online;
@tablespace
TABLESPACE STATUS FILE_NAME
TS100 AVAILABLE /u01/app/oracle/oradata/PROD/disk1/ts100.dbf
#4. emp1000 테이블이 잘 조회되는지 확인합니다
select count(*) from emp1000;
문제14 다음과 같은 현업의 시나리오를 만들고 해결하시오!
테이블 스페이스를 생성하는데 데이터 파일명에 확장자를 안쓰고 생성하는 시나리오를 일으키세요
그리고 해겨라세요
PROD > create tablespace yys
2 datafile '/home/oracle/yys01' size 10m;
Tablespace created.
PROD > create table emp_yys
2 tablespace yys
3 as select * from emp;
Table created.
해결은 다시 /home/oracle/yys01 ------ > /home/oracle/yys01.dbf 로 이름 변경
alter tablespace yys offline;
!mv /home/oracle/yys01 /home/oracle/yys01.dbf
alter database rename file '/home/oracle/yys01'
to '/home/oracle/yys01.dbf';
alter tablespace yys online;
os 에서 실제로 datafile 을 옮긴다 - 물리적으로 옮김
오라클 몰래한거나 다름없음
mv /home/oracle/ts100.dbf /u01/app/oracle/oradata/PROD/disk1/ts100.dbf
alter database rename file '/home/oracle/yys01' : 오라클이 인식할 수 있도록 control file 내용 변경
to '/home/oracle/yys01.dbf';
alter database datafile 7 offline drop;
alter databse open;
