유저 세션: Dedicated Server 프로세스
💡 유저 프로 세서 하나당 서버 프로세스가 하나씩 있는 것
유저 세션: Shared Server 프로세스
💡 유저 세션과 서버프로세스 사이에 디스패치가 있어서 응답큐에 있는 결과를 유저세션에게 전달
Dedicated Server 프로세스 보다 훨씬 더 자원을 아낄 수 있다.
- 쿠팡이나 이 커머션 같은 회사
SGA 및 PGA
✅ shared server 는 PGA가 저거밖에 없어서 라지풀을 늘려야한다.
💡 dba를 위한 tip
shared server procecss 구성에 대한 팁!
: shared server procecss 구성을 하게 되면 PGA 영역의 거의 모든 영역이 SGA내로 들어가기 때문에 (SGA내)large pool 사이즈를 늘려주어야 한다. 그래야 shared server process 구조로 구성했을 때의 여러 문제들을 예방할 수 있다. (예: 인터파크)
Shared Server를 사용하지 않는 경우
다음은 Shared Server를 사용하여 수행하지 않아야 하는 특정 데이터베이스 작업 유형입니다.
-> Dedicated Server 를 써라 !
✅ 데이터베이스 관리
✅ 백업 및 recovery 작업
✅ 일괄 처리 및 대량 로드 작업
✅ 데이터 웨어하우스 작업
실습1. (저번시간 마지막 문제) shared server process 구조를 구성
■ shared server process 구조를 구성하는 방법
1. dispatcher 를 띄울수 있도록 파라미터 설정
SQL> show parameter dispatcher
2. shared server process 의 갯수를 결정하는 파라미터를 설정
SQL> show paramete shared_servers
SQL> alter system set shared_servers=2;
db 를 startup 하면 최초로 띄울 shared server process 의 갯수
SQL> show parameter max_shared_servers
SQL> alter system set max_shared_servers= 10;
workload 가 busy 한 상태가 되었을때 최대 shared server
process 를 몇개까지 띄울수 있는지를 결정하는 파라미터
3. 오라클에 접속하고자 하는 전체 세션의 갯수 파라미터
와 그중에서 shared server process 를 사용할 세션
의 갯수를 결정하는 파라미터를 설정
SQL> show parameter sessions
100개
SQL> alter system set sessions=100 scope=spfile;
오라클에 접속하는 세션의 갯수를 100개로 하겠다.
SQL> show parameter shared_server_sessions
95개
SQL> alter system set shared_server_sessions=95;
100개중에서 shared server process 를 이용할 세션의
갯수를 95개로 지정하겠다.
SQL> alter system set max_dispatchers=3;
* database 의 특별한 작업을 하는 세션들을 위해서
dedicated server process 를 예비해둬야한다.
SQL> shutdown immediate
SQL> startup
* 현재 떠있는 디스패쳐의 갯수
select name ,status from v$dispatcher;
* 현재 떠있는 shared server 프로세서의 갯수
select name ,status from v$shared_server;
문제. shared server process 를 이용할수있도록
tnsnames.ora 를 구성하고 접속해보시오 !
리눅스의 tnsnames.ora 파일을 연다.
[orcl:diag]$ cd $ORACLE_HOME
[orcl:dbhome_1]$ cd network
[orcl:network]$ cd admin
[orcl:admin]$ vi tnsnames.ora
문제. 특별한 작업을 위해 dedicated sever process 를
5개가 뜰수있도록 남겨둔 이유가 무엇인가 ?
그 특별한 작업이 무엇인가 ?
$ sqlplus sys/oracle@orcl2_shared as sysdba
SQL> shutdown immediate
ORA-00106: cannot startup/shutdown database when connected to a dispatcher
shared server 구조에서 db 를 올렸다 내렸다하는
특별한 작업은 반드시 dedicated server process 를
통해서 작업해야한다. 
✅ 아직 작업이 없어서 이렇게만 떠있음
Shared Server 프로세서를 할당받아서 할 수 없는일
💡 아래의 경우는 권장이 아니라, 그냥 에러가 난다.
- db startup, shutdown과 같은 db 관리 작업
- 백업과 복구 작업
- 대량의 데이터 insert 작업
- 대량의 정렬 작업
실습1. (오늘의 마지막 문제) shared server process 구조를 구성
■ shared server process 구조를 구성하는 방법
1. dispatcher 를 띄울수 있도록 파라미터 설정
SQL> show parameter dispatcher
SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=1)';
2. shared server process 의 갯수를 결정하는 파라미터를 설정
SQL> show paramete shared_servers
SQL> alter system set shared_servers=2;
db 를 startup 하면 최초로 띄울 shared server process 의 갯수
SQL> show parameter max_shared_servers
SQL> alter system set max_shared_servers= 10;
workload 가 busy 한 상태가 되었을때 최대 shared server
process 를 몇개까지 띄울수 있는지를 결정하는 파라미터
3. 오라클에 접속하고자 하는 전체 세션의 갯수 파라미터
와 그중에서 shared server process 를 사용할 세션
의 갯수를 결정하는 파라미터를 설정
SQL> show parameter sessions
100개
SQL> alter system set sessions=100 scope=spfile;
오라클에 접속하는 세션의 갯수를 100개로 하겠다.
SQL> show parameter shared_server_sessions
95개
SQL> alter system set shared_server_sessions=95;
100개중에서 shared server process 를 이용할 세션의
갯수를 95개로 지정하겠다.
SQL> alter system set max_dispatchers=3;
* database 의 특별한 작업을 하는 세션들을 위해서
dedicated server process 를 예비해둬야한다.
SQL> shutdown immediate
SQL> startup
* 현재 떠있는 디스패쳐의 갯수
select name ,status from v$dispatcher;
* 현재 떠있는 shared server 프로세서의 갯수
select name ,status from v$shared_server;
문제. shared server process 를 이용할수있도록
tnsnames.ora 를 구성하고 접속해보시오 !
리눅스의 tnsnames.ora 파일을 연다.
[orcl:diag]$ cd $ORACLE_HOME
[orcl:dbhome_1]$ cd network
[orcl:network]$ cd admin
[orcl:admin]$ vi tnsnames.ora
문제. 특별한 작업을 위해 dedicated sever process 를
5개가 뜰수있도록 남겨둔 이유가 무엇인가 ?
그 특별한 작업이 무엇인가 ?
$ sqlplus sys/oracle@orcl2_shared as sysdba
SQL> shutdown immediate
ORA-00106: cannot startup/shutdown database when connected to a dispatcher
shared server 구조에서 db 를 올렸다 내렸다하는
특별한 작업은 반드시 dedicated server process 를
통해서 작업해야한다.
우리는 지금 세어드허버로 구성을 했다.
이렇게 구성하기 위해 파라미터파일 3개를 바꾸었다.
- -- tns alias 로 접속 가능한지 확인하기.
[orcl:admin]$ sqlplus scott/tiger@orcl tnsvi tnsnames.sh 파일 열어보기.
ORCL 안에 server = DEDICATED 로 되어있을 것
근데 쉐어 서비스로 들어가고싶다면-> 실습
리스너에 서비스를 등록하는 방법 2가지 (p6-18)
- 정적 서비스 등록(static service registration)
listner.ora에 4가지 정보를 다 등록해주는 방법 * 호스트 이름 * 포트 * 프로토콜 * 서비스 이름
- 동적 서비스 등록(dynamic service registration)
★ listner.ora 파일에 아래 3가지만 있고, 서비스 이름은 pmon이 spfile.ora 파라미터 파일 안에 service_name 파라미터를 보고 서비스 이름을 찾아서 직접 리스너에게 서비스 이름을 알려준다. * 호스트 이름 * 포트 * 프로토콜⭐ 정적과 동적 서비스 등록의 차이점 잘 알고있기 !
⭐⭐⭐12c 부터는 이 역할을 pmon이 안하고 별도의 프로세서가 담당합니다. PMON은 그냥 2가지 역할만 한다. (p.1-29)
- 비 정상적으로 종료된 세션의 resource를 정리
- IDLE 세션에 대한 세션 모니터 !
리스너2 정적이라서 내리기. 리스너 하나만 열어보기
$ ps -ef | grep LISTEN* | grep -v grep
oracle 23456 1 0 06:56 ? 00:00:00 /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER2 -inher it
oracle 29953 1 0 09:37 ? 00:00:00 /u01/app/oracle/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit
lsnrctl stop LISTENER2orcl_shared= (host= localhost) 아니고 (host= )
orcl에 있는 호스트 그거로 설정하기
실습
문제1) shared server process 를 이용할 수 있도록 네트웍 구성을 하고 접속을 하시오 !
1. dedicated 방식으로 접속하기
-- 기존 수행중인 리스너(lsnr2) 를 종료하고, orcl 리스너를 올린다.
$ lsnrctl stop lnsr2 $ lsnrctl start-- tns alias 로 접속 가능한지 확인하기.
[orcl:admin]$ sqlplus scott/tiger@orcl✅ tnsnames.ora 파일을 보면 orcl 이 dedicated 인 것을 알 수 있으므로, 위의 접속은 dedicated 방식으로 접속한 것이다.
2. shared 방식으로 접속하기
✔️ shared server process 접속이 가능하게 하려면
- local_listener 를 null 로 변경한다.
SYS> alter system set local_listener='';
- DB 를 내렸다가 올린다.
SYS> shutdown immediate SYS> startup
- 리스너를 내린다.
$ lsnrctl stop
- tnsnames.ora 파일에 아래의 내용을 추가한다.-> 실습파일이랑 호스트명 다르다. LOCALHOST에서
edydr1p0.us.oracle.com로$ vi tnsnames.ora ORCL_shared = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = edydr1p0.us.oracle.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = orcl.us.oracle.com) ) )
- 기본 listener 를 동적서비스 등록 방법으로 사용하도록 한다.
(서버의 IP 를 localhost로 변경, 정적 리스너로 구성한 내용 제거)$ vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) ) #SID_LIST_LISTENER= # (SID_LIST = # (SID_DESC = # (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) # (SID_NAME=orcl) # ) # )
- 리스너를 시작한다.
$ vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) ) #SID_LIST_LISTENER= # (SID_LIST = # (SID_DESC = # (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) # (SID_NAME=orcl) # ) # )
- 리스너에 서비스가 올라왔는지 확인한다.
[orcl:admin]$ lsnrctl status ... Services Summary... Service "orcl.oracle.com" has 1 instance(s). ~> tnsnames.ora 의 SERVICE_NAME 을 위의 서비스이름과 똑같이 써줘야 한다. Instance "orcl", status READY, has 2 handler(s) for this service... The command completed successfully
- orcl_shared 로 접속을 시도한다.
[orcl:~]$ sqlplus scott/tiger@orcl_shared SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 30 12:31:05 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SCOTT>
- 현재 떠있는 서버 프로세스 확인 -> 원래 두개였는데 세개가 되었다.
SYS> select name, status from v$shared_server;
✅ shared server에서 디비작업 할 수 없다. 에러난다.
large_pool 사이즈 늘리기
SQL> show parameter large_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 0
SQL>alter system set large_pool_size = 1000000;
데이터 베이스 간의 통신 구성
내가 짝꿍의 emp테이블을 select 하고 싶다면 dbLink를 생성해야 한다.
CREATE DATABASE LINK <remote_global_name>
CONNECT TO <user> IDENTIFIED BY <pwd>
USING '<connect_string_for_remote_db>';
내꺼 tnsnames.ora 안에 짝꿍의 정보 쓰고 내꺼로 로그인 한다음에 링크 만들때 using 옆에 짝꿍꺼 쓰기
실습 -> 리눅스 아니고 윈도우에서 하기
- king유저 아니고 c##scott으로 하기
- 포트 1521로 변경
- db링크 이름은 짝꿍 이니셜
- 아이피주소 확인은 명령프롬프트 창 lsnrctl status
내꺼
(HOST=WIN-0SV31PI4F1Q), "orcl"서비스, (PORT=1522)
- 윈도우에서 tnsnames.ora 파일 찾기
C:\Users\ITWILL\Desktop\WINDOWS.X64_193000_db_home\network\admin방화벽풀기
제어판\시스템 및 보안\Windows Defender 방화벽
실습 짝꿍 db_link 만들기
1. 19c 내리고 18로 진행
2. lsnrctl stop 하고 18c 리스너로 올려야한다 .
3. 서비스 가서 19c 리스너 중지하기
나중에 깔린게 우선순위가 노ㅠ다.
시작 > 시스템 > 환경 변수 편집 > path 누르고 편집 누르기 > 19c 내리고 18c 올리기
✔️ C:\app\ITWILL\product\18.0.0\dbhomeXE\network\admin 로 가서 tnsnames.ora 열고 아래 코드 붙여넣기. xe_donghe는 알리아스
xe_donghe =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.16)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xe)
)
) ✔️ scott에서 db링크 생성!
drop database link 링크명
create database link dh_link
connect to c##scott
identified by tiger
using 'xe_donghe';
select * from emp@dh_link; 
drop database link 링크명
create database link wg_link
connect to c##scott
identified by tiger
using 'orcl_syg';
select * from emp@wg_link; 
A: only 쿼리? 아니다.
B: 업뎃했던거 중단이랑 롤백 안됨
D: 리스너가 시작될때까지 어떠한 작업도 할 수 없는거 아니다.
답은 B!
✅ 답은 c, e PMON아니고 Listener Registration Process (LREG)
정답 D!
A: 안끊긴다.
B: 현존하는 커넥션들은 안끊기는게 맞는데 계속 작업할 수 있다.
C: 맞다. 정상적으로 일한다.
D: 모든 세션은 중단되지 않는다.
정답 C!
: lsnrctl reload라고 하는 때는 listener.ora 파일의 내용을 수정하고 수정된 내용으로 리스너에 반영하고 싶을 때 사용합니다.
원래는 listener.ora 파일의 내용을 수정했으면 반드시 리스너를 내렸다가 다시 올려야 수정된 내용이 반영되는데, 그렇게 하지 않고 바로 반영하고 싶다면 reload 하면 됩니다.
• 간단한 연결(Easy Connect) 이름 지정: TCP/IP 연결
문자열 사용
• 로컬 이름 지정: 로컬 구성 파일 사용 -> tnsnames.ora 파일 가지고 접속하는 방식sqlplus scott/tiger@orcl• 디렉토리 이름 지정: 중앙화된 LDAP 호환
디렉토리 서버 사용
• 외부 이름 지정: 지원되는 비오라클 이름 지정 서비스 사용
✅ 문제에 one-to-one : DEDICATED 관련
📖 7장 데이터베이스 저장 영역 구조 관리
테이블 데이터가 저장되는 방법
데이터베이스 블록: 내용
✅ 사용가능영역이 있는 이유는 만약 업데이트해서 용량 늘어나면 (scott-> scottttttttt) 여기 쓰기 위해.
근데 이 영역이 없다면 다른블락으로 이사시키고 그 이사간 주소를 이 영역에다가 넣는다. 이 현상을 row migration 이다. 이게 많이 일어나면 성능이 느려진다. 주소읽고 그 이사간 블록 가야하는 두번 블록을 읽어야 하니까. 결국, 마이그레이션이 나타나지 않게 하는 영역인데 pct free 영역이라고도 한다. 테이블 생성할 때 pct free를 얼마나 주겠다고 지정한다.
create table emp39
(empno number,
ename varchar2(10))
pctfree 40; -> 40% 라는 뜻✅ 업데이트가 크게 일어날 것 같은 테이블에는 넉넉히 주고 만드는데 사실 이거 예상이 불가능하기 때문에 우리는 pctfree 써서 지정하지 않는다. 자동조절이 되기 때문 ! 뭔지만 알고있자
💡 정리!!
: 8kb 블럭에 데이터를 끝까지 모두 채우지는 않고 여유공간을 10% 남겨 놓습니다. 이 여유공간에 대한 테이블 파라미터를 pctfree라고 합니다. 이 여유공간은 row migration 현상을 최소화 하기 위한 공간입니다. 기존 데이터에 변경을 크게 했을 때 여기에 update를 해서 row migration이 최대한 안생기게 방지합니다. 로우 마이그레이션은 성능을 느리게 합니다.
새 테이블스페이스 생성
💡 em 으로 테이블 스페이스 생성하는 화면
테이블 스페이스 생성시 옵션 설명
1. Locally managed Tablespace
- 성능이 우수 (INSERT 시 LMT 가 유용함)
왜 성능이 우수하냐면 테이블에 데이터를 입력할때 오라클이 데이터를 입력할 때 비어있는 공간이 있는지 확인을 한다. dba_free_space 라는 데이터 딕셔너리 인데, 이 딕셔너리는 어디있냐면 system tablespace에 있습니다. system, ts01, ts02, ts03... 이렇게 있을 때 system tablespace에 부하가 일어날 수 있다. 이게 dictionary managed (위 이미지에 이거 체크x)
- 해당 tablespace 내에서 Bitmap 을 이용.
ts01, ts02 각각에 둬서 system 가서 확인하지 않도록
비트맵의 비트 : 하나의 블록 / 블록 그룹의 정보
Data Dictionary Table 을 참조하기 위한 recursive SQL 을 사용하지 않아도 된다.
- recursive SQL : data dictionary 를 조회하는 SQL
insert 시 system tablepace 에 부하를 주지 않음⭐ 장점
1. Data Dictionary 경합, 즉 System tablespace 의 경합을 줄일 수 있음.
2. 공간 절약
3. Rollback 정보의 비생성
4. Recursive Space Management Operation 감소
5. Fragmentation 감소
⭐ LMT Extent 관리의 원리
LMT 로 할당 된 Datafile 에 Free Space Bitmap Block 이 할당되며, Extent 의 Allocation 및 Free 시 마다 Bitmap value 를 바꿔 현재의 상태를 반영하며, rollback 정보는 생성하지 않는다. 각 bit 는 extent 하나를 대표한다.
✅ 비트맵 정보가 하나의 tablespace에 두는것이Locally managed Tablespace
1. Dictionary managed Tablespace
- 비어있는 데이터 insert시 비어있는 Extent를 할당 받을 때 항상 Data Dictionary 를 참고함.
- 장점 : extent 의 크기를 유연하게 할 수 있음. insert가 자주 발생하는 system인 경우 extent의 사이즈를 크게 해놓으면 insert 속도가 좀 빨라질 수 있다.
- 단점 : insert 시 system tablepace 에 엄청난 부하를 줌
⭐ locally managed 가 더 장점이 많기때문에 locally managed 를 쓰는게 바람직하다.
테이블 스페이스의 type 3가지
permanent~> data 를 영구히 저장하는 테이블 스페이스예) emp, dept같은 테이블이 저장되는 곳 (=users tablespace)
temporary~> 임시로 사용하는 data 를 저장하는 테이블 스페이스예) SQL의 temporary table이 여기에 저장된다. with절의 temporary table도 여기에 저장된다.
undo~> rollback 을 하기 위한 data 를 저장하는 테이블 스페이스예) data를 delete로 지우면, 삭제된 데이터가 이곳에 저장된다. 만약 rollback 수행하면 가능해진다.
테이블 스페이스의 상태(status) 3가지
read only~> 읽기만 가능한 tablespaceread write~> 읽기 쓰기가 가능한 tablespaceoffline~> data 액세스가 아예 불가능한 tablespace
관련실습
테이블스페이스는 SYS, 테이블은 scott에서 생성!
✔️ ts02, ts03 을 SYS에서 만들어준다. -> ts02는 원래 있었어서 드롭하고 다시 만들었음
drop tablespace ts02 including contents and datafiles;
SYS> create tablespace ts02
datafile '/home/oracle/ts02.dbf' size 50m;
SYS> create tablespace ts03
datafile '/home/oracle/ts03.dbf' size 100m; ✔️ 위에서 생성한 ts03 테이블 스페이스에 emp03 이라는 테이블을 emp 테이블과 똑같은 구조로 생성하시오 !
SCOTT> create table emp03
tablespace ts03 -- tablespace 는 여기에 작성한다.
as
select * from emp where 1=2;✔️ 위에서 생성한 ts03 테이블에 데이터를 오류가 발생할때까지 입력해보자 !
SYS> grant dba to scott;SCOTT> insert into emp03
2 select *
3 from emp;
14 rows created.
SCOTT> insert into emp03
2 select *
3 from emp03;
14 rows created.
...
SCOTT> /
458752 rows created.
SCOTT> /
insert into emp03
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.EMP03 by 128 in tablespace TS03
✅ ts03을 보면 공간이 꽉 차있는 것을 볼 수 있다.
테이블 스페이스의 공간을 늘리는 방법
- 테이블 스페이스에 data file 추가( 현업에서 주로 사용함 )
장애 시 복구를 빨리 하기 위해 추천하는 방법이다.SYS> alter tablespace ts03 add datafile '/home/oracle/ts03b.dbf' size 50m;
- 테이블 스페이스에 기존에 존재하는 파일의 사이즈를 resize 명령어로 늘리는 방법
( 추천하지 않음 )하나의 파일이 점점 커지면, 나중에 backup 할 때 시간이 오래 걸리고 파일이 깨질 경우, 복구할 때 시간이 오래 걸린다.
SYS> alter database datafile '/home/oracle/ts03.dbf' resize 200m;
✔️ 다른 테이블들도 70% 이상 공간 확보하기
alter tablespace example
add datafile '/home/oracle/example02.dbf' size 100m;
alter tablespace sysaux
add datafile '/home/oracle/sysaux02.dbf' size 100m;
alter tablespace system
add datafile '/home/oracle/system02.dbf' size 100m;
-- 아래는 ASM 쪽에 추가하는 방법
alter tablespace example add datafile size 100m; 
✔️ 테이블 스페이스를 생성할 때, 초기에 설정한 크기 값이 꽉 차면 자동으로 10m 씩 증가하여 1GB까지 증가하도록 늘리는 방법
SYS> create tablespace ts04 datafile '/home/oracle/ts04.dbf' size 100m autoextend on next 10m maxsize 1024m; --이걸 써주어야 자동으로 10m씩 증가되는게 만들어진다. ls -rlt
✅ 테이블 스페이스 이름, 스페이스별 토탈 사이즈 출력
SYS> select tablespace_name, sum(bytes)/1024/1024||'MB' total_size from dba_data_files group by tablespace_name;
✅ 테이블 스페이스 이름, 테이블 스페이스 별 free space 가 얼마나 되는지 확인SYS> select tablespace_name, sum(bytes)/1024/1024||'MB' free_size from dba_free_space group by tablespace_name;
✅ 위의 문제 1, 2 번에서 작성한 SQL 을 활용하여 아래의 내용을 출력하시오tablespace_name, total_size, free_sizeselect t.tablespace_name, t.total_size, f.free_size from (select tablespace_name, sum(bytes)/1024/1024||'MB' total_size from dba_data_files group by tablespace_name) t, (select tablespace_name, sum(bytes) /1024/1024||'MB' free_size from dba_free_space group by tablespace_name) f where t.tablespace_name = f.tablespace_name;✅ 테이블스페이스이름, 사용율을 출력하시오
select t.tablespace_name, ((t.total_size - f.free_size) / t.total_size) * 100 "used space(%)" from (select tablespace_name, sum(bytes)/1024/1024 total_size from dba_data_files group by tablespace_name) t, (select tablespace_name, sum(bytes)/1024/1024 free_size from dba_free_space group by tablespace_name) f where t.tablespace_name = f.tablespace_name;
✅ 테이블스페이스 중 사용율이 80% 이상 되는 테이블 스페이스 이름을 출력select tablespace_name from ( select t.tablespace_name, ((t.total_size - f.free_size) / t.total_size) * 100 AS USED from (select tablespace_name, sum(bytes)/1024/1024 total_size from dba_data_files group by tablespace_name) t, (select tablespace_name, sum(bytes)/1024/1024 free_size from dba_free_space group by tablespace_name) f where t.tablespace_name = f.tablespace_name ) where USED > 60;✅ 테이블스페이스 중 사용율이 80% 이상 되는 테이블 스페이스 이름, 테이블스페이스에 해당하는 data file 이름, 현재 크기를 출력하시오 ~
select a.tablespace_name, b.file_name, b.bytes/1024/1024 "bytes(MB)" from ( select t.tablespace_name, ((t.total_size - f.free_size) / t.total_size) * 100 AS USED from (select tablespace_name, sum(bytes)/1024/1024 total_size from dba_data_files group by tablespace_name) t, (select tablespace_name, sum(bytes)/1024/1024 free_size from dba_free_space group by tablespace_name) f where t.tablespace_name = f.tablespace_name ) a, dba_data_files b where USED > 60 and a.tablespace_name = b.tablespace_name;
60%인 테이블스페이스는 2배로 resize 되도록 하는 스크립트 만들기!
col script for a100
select 'alter database datafile ''' || b.file_name || ''' resize ' || (b.bytes/1024/1024)*2 || 'm;' AS script
from (
select t.tablespace_name,
((t.total_size - f.free_size) / t.total_size) * 100 AS USED
from (select tablespace_name, sum(bytes)/1024/1024 total_size
from dba_data_files
group by tablespace_name) t,
(select tablespace_name, sum(bytes)/1024/1024 free_size
from dba_free_space
group by tablespace_name) f
where t.tablespace_name = f.tablespace_name
) a,
dba_data_files b
where USED > 60
and a.tablespace_name = b.tablespace_name;
SCRIPT
------------------------------------------------------------------------------------------
alter database datafile '+DATA/orcl/datafile/sysaux.257.796857623' resize 1060m; -- 얘는 안하기
alter database datafile '+DATA/orcl/datafile/sysaux.267.1145464529' resize 200m;
alter database datafile '/home/oracle/sysaux02.dbf' resize 200m;
alter database datafile '+DATA/orcl/datafile/system.256.796857621' resize 1360m;
alter database datafile '+DATA/orcl/datafile/system.269.1145464533' resize 200m;
alter database datafile '+DATA/orcl/datafile/system.270.1145464597' resize 200m;
alter database datafile '/home/oracle/system02.dbf' resize 200m;ts로 시작하는 테이블 스페이스를 drop하기
SQL> drop tablespace ts01 including contents and datafiles;
SQL> drop tablespace ts02 including contents and datafiles;
SQL> drop tablespace ts03 including contents and datafiles;alter database datafile '+DATA/orcl/datafile/sysaux.257.796857623' resize 140m;
alter database datafile '+DATA/orcl/datafile/sysaux.267.1145464529' resize 20m;
alter database datafile '/home/oracle/sysaux02.dbf' resize 20m;
alter database datafile '+DATA/orcl/datafile/system.256.796857621' resize 136m;
alter database datafile '+DATA/orcl/datafile/system.269.1145464533' resize 20m;
alter database datafile '+DATA/orcl/datafile/system.270.1145464597' resize 20m;
alter database datafile '/home/oracle/system02.dbf' resize 20m;Tablespace 생성 시 옵션
테이블 스페이스의 종류
- permanent ~> data 를 영구히 저장하는 테이블 스페이스
- temporary ~> 임시로 사용하는 data 를 저장하는 테이블 스페이스
- undo ~> rollback 을 하기 위한 data 를 저장하는 테이블 스페이스
SYS> select tablespace_name, contents from dba_tablespaces;
temporary tablespace 생성
💡임시로 사용하는 data 를 저장하는 tablespace
SYS> create temporary tablespace temp02
tempfile '/home/oracle/temp02.dbf' size 20m;
-- 생성 확인
SYS> select tablespace_name, contents
from dba_tablespaces; 
undo tablespace 생성
💡 rollback 을 수행하기 위한 data 를 저장하는 tablespace
SYS> create undo tablespace undotbs02
datafile '/home/oracle/undotbs02.dbf' size 10m;
-- 생성 확인
SYS> select tablespace_name, contents
from dba_tablespaces; 
ASM 영역에 undo tablespace 생성하기
SYS> create undo tablespace undotbs3
datafile '+data' size 100m;
또는
SYS> create undo tablespace undotbs3
datafile size 100m;✅ OMF (Oracle Managed File) 기능이 켜져 있는 경우 '+data' 를 생략해도 된다. (create undo tablespace undotbs3 datafile size 100m;)
SYS#1> show parameter db_create_file_dest
❓ 우리가 테이블 만들 때 아래처럼 하면 어떤 테이블스페이스에 만들어질까?
-> default tablespace에 만들어진다 !
create table emp900
(empno number(10),
ename varchar2(10) );
select table_name, tablespace_name
from user_tables; create table emp901
(empno number(10),
ename varchar2(10) )
tablespace example;오늘의 마지막 문제 default permanent tablespace를 users 에서 example로 변경하고 테이블 스페이스 옵션 안쓰면 만들어진 테이블이 example테이블 스페이스에 만들어지는지 확인하세요.다시 돌려놓기
SYS> alter database default tablespace example;
-- 생성 확인
SYS> select property_name, property_value
from database_properties;
create table emp900
(empno number(10),
ename varchar2(10) );
select table_name, tablespace_name
from user_tables; 
alter database default tablespace users;
create table emp901
(empno number(10),
ename varchar2(10) );
select table_name, tablespace_name
from user_tables; 
