|  | oracle 10.2
LISTENER parameter "inbound_connect_timeout" set to 10
telnet loclhost 1521
[10초 경과]
Connection closed by foreign host.
listener.log
=======================================================================
TNS-12525: TNS:listener has not received client's request in time allowed
TNS-12535: TNS:operation timed out
TNS-12606: TNS: Application timeout occurred |
PRODBA 카페의 [용돌이]님이 작성하신 글입니다. http://cafe.naver.com/prodba/11505
처음 ORA-3136을 접했을때 생각해봤던 건데 도움이 될까 해서 정리해서 올립니다. 10g가 되면서 inbound_connect_timeout 이 60초로 바뀌게 되었고 그로 인해서 가끔씩 alert에 제목과 같은 메지시가 나오곤 합니다. 우선 ORA-3136이 발생되는 경우를 살펴보면 CASE 1 WIPPY@/oracle> sqlplus /@rac1
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 7 13:33:27 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: 이상태에서 타이핑 없이 대기를 하게되면 60초 후에 alert log에 ORA-3136 이 떨어지게 됩니다. 그렇다면 그 60초 동안 어떤일이 발생하는가? 1. ps -ef|grep LOCAL=NO 로 확인해 보면 process가 하나 늘어나 있는 것을 볼 수 있습니다. 2. v$process 나 v$resource_limit를 보면 process가 하나 늘어나 있는 것을 볼 수 있습니다. 위를 통해서 60초를 강제로 설정해 놓은 이유를 알 수 있습니다. 누군가 악의적인 목적으로 저런식의 접속을 한다음 끊지 않으면 설정해 놓은 process개수를 다 채워버리게 되고 정상적인 connection이 처리되지 못하는 문제가 있겠죠. 따라서 60초를 주기로 oracle에서는 저런 process를 강제로 죽이는 것입니다. ORA-3136을 발생시키지는 않지만 유사한 경우가 있습니다. CASE 2 WIPPY@/oracle> telnet 192.168.0.11 1521
Trying 192.168.0.11...
Connected to 192.168.0.11 (192.168.0.11).
Escape character is '^]'. 위와 같이 했을 경우에는 다음과 같은 현상이 나타나게 됩니다. 1. ps -ef|grep LOCAL=NO 로 확인해 보면 process하나 늘어나 있는 것을 볼 수 있습니다. 2. v$process 나 v$resource_limit를 보면 변화가 없습니다. 즉 OS상에서는 process가 뜨지만 oracle의 view에는 반영이 되지 않는 상황입니다. 이러한 상황도 system resource를 사용하는 것이기 때문에 그다지 좋은 상황은 아니겠죠. 위에 유사한 두가지 CASE에 대해서 설명을 했는데 ORA-3136 에 대해서 좀 찾아보신 분들은 알겠지만 이 메세지를 안나오게 하기위해서는 sqlnet.ora 에 SQLNET.INBOUND_CONNECT_TIMEOUT = 0 설정을 하거나 listener.ora에 INBOUND_CONNECT_TIMEOUT_<listener_name> = 0 을 설정하라고 합니다. 제가 테스트 해본 결과에 의하면 sqlnet.ora - SQLNET.INBOUND_CONNECT_TIMEOUT = 0 : sqlplus /@RAC1 에만 영향을 줍니다. listener.ora - INBOUND_CONNECT_TIMEOUT_<listener_name> = 0 : telnet 192.168.0.11 1521 에만 영향을 줍니다. 즉 APP의 접속 형태에 따라 sqlner.ora와 listener.ora를 취사선택하거나 둘다 설정을 해야할 필요성이 있는 것입니다. INBOUND_CONNECT_TIMEOUT을 설정할때는 위와 같은 내용을 인지하고 설정을 하셔야 할것으로 생각됩니다. 무작정 ERROR를 보이지 않게 하기 위해서 설정을 한다면 보안상 심각한 문제를 야기할 수도 있으니까요. 즉 ORA-3136이 발생하게 되면 connection이 늦게 처리되는 APP에 대해서 먼저 확인하고 60초 안에 접속이 이루어 지도록 APP단의 수정을 해야 하는 것이 우선이라고 생각합니다. ## 10.2.0.4 linux에서 테스트한 결과 입니다. 버전에 따라 차이가 있을 수 있음을 .. 이해하시길..
--------------------------------------------------------------------------------- 나의 의견 : alert file에 ora-3136이 떴을때 웬지 모를 불안감이 생겼었지만.. 이 에러에 대해서 조금 알고나니.. 유용한 에러라 생각되어집니다. inbound_connect_timeout을 유용하게 사용할 수 있을 것 같습니다.
SQL> show serveroutput serveroutput OFF SQL> begin 2 dbms_output.put_line('serveroutput이 실행 중입니다.'); 3 end; 4 /
PL/SQL procedure successfully completed.
SQL> set serveroutput on SQL> show serveroutput serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED SQL> r 1 begin 2 dbms_output.put_line('serveroutput이 실행 중입니다.'); 3* end; serveroutput이 실행 중입니다. --결과물이 출력되었습니다.
PL/SQL procedure successfully completed.
SQL>
1. GRID의 개념 1.1 GRID의 개념 1) GRID 컴퓨팅의 정의
그리드 컴퓨팅(Grid Computing)의 핵심 개념은 전화나 전기, 수도 같은 유틸리티로써의 컴퓨팅이다. 사용자는 원하는 때 원하는 만큼 정보나 컴퓨팅 작업을 요청하고 받을 수 있게 된다.
그리드 컴퓨팅은 프로세싱, 네트워크 대역폭 및 스토리지지 용량과 같은 분산된 컴퓨팅 자원을 가상화하여 하나의 시스템 이미지를 만들어 사용자 및 응용 프로그램이 다양한 IT 기능에 완벽하게 접근 할 수 있도록 지원한다. 쉽게 기업내에 산재해 있는 소형 서버들을 연결해 하나의 커다란 컴퓨터처럼 사용하는 개념으로 생각할 수 있다. 유틸리티 컴퓨팅은 "클라이언트 측면에서" 본 것이다. "서버 측면에서" 보자면(그리드의 이면을 파고들면), "그리드"는 자원 할당, 정보 공유 그리고 고가용성과 관련된 개념이다.
① 자원할당 - 자원을 요청하고 필요로 하는 누구든지 원하는 것을 얻을 수 있도록 하는 것 - 요청이 없는 동안에 자원의 낭비를 막는 것.
② 정보공유 - 사용자와 어플리케이션이 필요로 하는 정보는 언제 어디서나 필요에 따라 이용할 수 있도록 해주는 것.
③ 고가용성 - 고가용성이란 하나의 노드에 문제가 생긴 경우 다른 노드에서 서비스나 기능을 대신 제공하는 것을 말한다. - 긴 시간동안 지속적으로 운영이 가능한 시스템이나 컴포넌트로 가용성이란 흔히 "100% 가용" 등과 같이 상대적으로 측정되거나 또는 "절대 고장나지 않음" 등과 같이 표현 될 수 있다.
2) GRID 컴퓨팅의 필요성 ① GRID 컴퓨팅은 소형 서버들을 연결, 고성능을 유지하면서 더욱 많은 자원을 활용 가능 하므로 효율적인 IT인프라 사용을 위한 좋은 방안이다. ② 기존에 사용하던 시스템을 전환할 필요 없이, 기존 인프라스트럭처로부터 GRID 컴퓨팅으로 전환 가능하다. ③ GRID 컴퓨팅은 저렴한 가격으로도 기업의 인프라를 효율적으로 활용할 수 있는 최적의 솔루션이다.
3) 오라클 GRID 컴퓨팅 - 저렴한 다수의 컴퓨팅 자원으로 고가의 컴퓨팅 자원보다 더 높은 성능을 구현할 수 있다. - 그리드는 어떤 컴퓨터든, 자원을 항상 최대 용량 만큼 쓰는 것이 아니기 때문에 쉬고 있는 순간에 다른 업무에 활용할 수 있도록 빌려준다는 개념으로도 이해할 수 있다. - Oracle Database 10g는 Enterprise Grid Computing을 위해 디자인된 최초의 데이터베이스이다.
2 RAC의 개념 2.1 RAC의 정의 - Oracle Real Application clusters(RAC)는 Oracle Parallel Server(OPS)의 후속 제품으로 개발되어 Oracle9i 버전부터 기능을 제공 한다. - RAC는 동일 데이타베이스(스토리지)를 여러 인스턴스에서 동시에 액세스할 수 있다 - RAC는 시스템 확장이 가능하기 때문에 결함 허용, 로드 밸런싱 및 향상된 성능을 제공한다. - 모든 노드가 동일한 데이타베이스를 액세스하기 때문에 한 인스턴스에서 장애가 발생해도 데이타베이스에 대한 액세스가 손실되지 않는다. - Oracle RAC의 핵심은 공유 디스크 하위 시스템이다. - 클러스터의 모든 노드는 클러스터 내의 모든 노드에 대한 데이타, 리두 로그 파일, 제어 파일 및 매개변수 파일을 액세스할 수 있어야 한다. - 데이터 디스크는 모든 노드가 데이타베이스를 액세스할 수 있도록 허용하기 위해 전역으로 사용할 수 있어야 한다. 2.2 RAC의 장점
① 확장성(자원(CPU/메모리/디스크등)이 부족했을 경우에 대처 할 수 있는 구조)
- 새로운 업무가 지속적으로 추가되어 서버의 용량이 부족해지는 경우가 발생된다면 클러스터상에 새로운 서버를 유연하게 확장 할 수있고, 서버를 확장 하더라도 문제가 발생하지 않는다. ② 고가용성 (장애가 발생해도 시스템 전체가 운용 될 수 있는 구조)
- 하나의 서버로 구성된 데이터베이스일 경우 데이터베이스 장애가 발생할 경우 복구될때까지 서비스 이용이 불가능 했으나, RAC의 경우에는 하나의 서버에 장애가 발생하더라도, 나머지 서버에서 지속적인 서비스를 제공할 수 있어 서비스의 중지가 발생하지 않는다. 3. RAC 구조
3.1 RAC의 구조
- 물리적인 하나의 데이터베이스를 여러 대의 서버가 공유하여 사용하는 것. - 모든 서버들은 같은 데이터를 사용하게 되어 논리적으로는 하나의 시스템을 이용하는 것임. - Cache Fusion 기능을 위해 NODE간 High-Speed Interconnect Network는 필수
reference - Linux 및 FireWire에 RAC 클러스터 구축 (주)한국오라클 - RAC & Enterprise Manageability Best Practices (주)한국오라클 ================================================ * 데이터베이스 정보공유 커뮤니티 oracleclub.com * 강좌 작성자 : 김정식 (oramaster _at_ naver.com) ================================================ ※ oracleclub 강좌를 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^ ※ oracleclub 강좌는 개인의 학습용으로만 사용 할 수 있습니다. 학원 홍보용이나 수익을 얻기 위한 용도로 사용을 하시면 안됩니다. ^^
library cache miss 비율을 보고 shared_pool_size를 수정하고 좀 고생을 했습니다.
사고경위(?)는 이렇습니다. ㅋㅋshared_pool은 자동관리가 되고 있다는 것을 잠시 잊고 있었습니다.library cache miss 비율이 4%가 넘어서 shared_pool_size를 늘려주어서 0.2%로 떨어트렸습니다. 문뜩 shared_pool_size를 직접적으로 올려주는 것보다 sga_max_size 이나 sga_target 값을 올려서 자동으로 shared_pool_size를 올려주는게 나을 것 같아서 수정하기로 했습니다.
SQL> select name, value, isdefault from v$parameter --이 값은 최초 sga 영역 메모리에 대한 값들입니다. 2 where name like '%size%'; 즉, shared_pool_size를 변경해 주기 전
NAME VALUE ISDEFAULT ------------------------ ------------ ------------- sga_max_size 205520896 TRUE shared_pool_size 0 TRUE large_pool_size 0 TRUE java_pool_size 0 TRUE streams_pool_size 0 TRUE shared_pool_reserved_size 3355443 TRUE java_max_sessionspace_size 0 TRUE db_block_size 8192 TRUE db_cache_size 0 TRUE ...
29 rows selected.
[수정작업]
SQL> alter system set shared_pool_size = 0 scope=spfile; --다시 원복을 시켰습니다.
System altered.
SQL> show parameter sga_target
NAME TYPE VALUE ------------- ------------ ------- sga_target big integer 196M
SQL> show parameter sga_max_size;
NAME TYPE VALUE --------------- ------------ ------- sga_max_size big integer 196M
sga_target 값이 max size가 되어 sga_max_size값을 올려야겠네요.
SQL> select component, current_size/1024/1024 size 2* from v$sga_dynamic_components
COMPONENT size --------------------------- --------- shared pool 72 large pool 4 java pool 4 streams pool 0 DEFAULT buffer cache 112
13 rows selected.
SQL> alter system set sga_max_size =228m scope=both; System altered.
SQL> select component, current_size/1024/1024 size 2* from v$sga_dynamic_components
COMPONENT size --------------------------- --------- shared pool 72 large pool 4 java pool 4 streams pool 0 DEFAULT buffer cache 144 --한번 증가시킨 sga_max_size는 다시 줄일수가 없습니다. 이미 자동으로 메모리에 할당을 했기때문이죠. 줄이고자 한다면 기존에 있던 pfile로 startup을 해야겠지요! ...
13 rows selected.
SQL> select name, value, isdefault from v$parameter 2 where name like '%size%';
NAME VALUE ISDEFAULT ---------------------------- ----------- ------------------ sga_max_size 239075328 FALSE shared_pool_size 0 FALSE ...
29 rows selected.
SQL>
sga_max_size 와 shared_pool_size는 값을 한번씩 변경해주었습니다. 그 이후로는 isdefault가 false로 되었습니다. true로 설정을 어떻게 해야하는지 몰라서 한참을 헤멨습니다. sga_target 값을 0으로 설정 후 다시 sga_max_size 값을 설정하였습니다. 즉, sga 관리를 수동으로 전환 후 다시 자동으로 전환을 시켰습니다.
SQL> select name, value, isdefault from v$parameter 2 where name like '%size%';
NAME VALUE ISDEFAULT ------------------------ ------------ ------------------ sga_max_size 239075328 FALSE shared_pool_size 75497472 FALSE large_pool_size 4194304 FALSE java_pool_size 4194304 FALSE streams_pool_size 0 FALSE shared_pool_reserved_size 3774873 TRUE java_max_sessionspace_size 0 TRUE db_block_size 8192 FALSE db_cache_size 150994944 FALSE ...
29 rows selected.
컥! 자동 관리 되었던 모든 메모리가 false로 변했네요! value도 알아서 지정이 되버렸군요. 같은 쿼리문을 실행시킨 처음 결과 값들과 비교를 해보면 많이 달라졌지요! 어떻게 수정을 해야하는지 몰라서 별짓을 다해봤습니다.ㅋㅋㅋ 그냥 pfile로 올려서 다시 원상복귀 시킬까도 생각해보았습니다. 나중에 비슷한 일이 생길 것을 대비해서 다시 열심히 찾아보았습니다.
[해결방안]
일단 쓸데없이 메모리를 차지하지 않도록 해당 자동관리 되는 메모리들을 0으로 수정하였습니다.
SQL> alter system set shared_pool_size = 0 scope=spfile;
System altered.
SQL> alter system set large_pool_size = 0 scope=spfile;
System altered.
SQL> alter system set java_pool_size = 0 scope=spfile;
System altered.
SQL> alter system set streams_pool_size = 0 scope=spfile;
System altered.
SQL> alter system set db_cache_size = 0 scope=spfile;
System altered.
* 아래와 같은 reset 명령어가 있었습니다.
SQL> alter system reset sga_max_size scope=spfile sid='*';
System altered.
SQL> alter system reset shared_pool_size scope=spfile sid='*';
System altered.
SQL> alter system reset large_pool_size scope=spfile sid='*';
System altered.
SQL> alter system reset java_pool_size scope=spfile sid='*';
System altered.
SQL> alter system reset streams_pool_size scope=spfile sid='*';
System altered.
SQL> alter system reset db_cache_size scope=spfile sid='*';
System altered.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started.
Total System Global Area 239075328 bytes Fixed Size 1218724 bytes Variable Size 83887964 bytes Database Buffers 150994944 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SQL> select name, value, isdefault from v$parameter 2 where name like '%size%';
NAME VALUE ISDEFAULT ------------------------ ------------ ------------- sga_max_size 239075328 TRUE shared_pool_size 0 TRUE large_pool_size 0 TRUE java_pool_size 0 TRUE streams_pool_size 0 TRUE shared_pool_reserved_size 3774873 TRUE java_max_sessionspace_size 0 TRUE db_block_size 8192 TRUE db_cache_size 0 TRUE ...
29 rows selected.
정상으로 되었습니다.ㅋㅋ sga_max_size을 늘리려다가 무슨 난리를 친건지..ㅎㅎㅎㅎ library cache miss 비율은 1%로 되었네요!
나의 의견 : 참고로 위 설명에는 나와있지 않았지만 shutdown을 너무 많이 한 것 같습니다.ㅜㅜ
http://kr.forums.oracle.com/forums/thread.jspa?messageID=1699078
(Oracle 10g) MMAN 백그라운드 프로세스를 통한 자동 공유 메모리 관리
========================================
PURPOSE
이 문서에서는 Oracle database 10g의 Self Managing 기능 중의 하나인 자동 공유 메모리 관리 기능에 대하여 알아보고 SGA_TARGET 이라는 새로운 파라미터와 MMAN이라는 새로운 백그라운드 프로세스에 대하여 소개하기로 한다.
Explanation
1. 개요
SGA_TARGET 파라미터를 이용한 자동 SGA 튜닝이 어떻게 이루어지는지 그 원리를 알아보도록 한다.
자동 SGA 튜닝은 Oracle database 10g의 ADDM을 가능하게 하는 요소인 메모리 advisor가 그 기능을 수행한다.
자동 공유 메모리 관리 기능이 갖는 장점은 다음과 같은 몇 가지가 있다.
첫째, workload가 변함에 따라 자동으로 공유 메모리가 적용이 된다.
둘째, 메모리의 활용률을 극대화한다.
세째, out-of-memory라는 메모리 부족 발생으로 인한 에러를 예방할 수 있다.
즉, 오라클의 공유 메모리 영역 중
Shared pool size, Buffer cache size, Large pool size, Java pool size를 매뉴얼하게 셋팅할 필요가 없다. 가용한 메모리의 사용을 보다 효과적으로 해주는 것 뿐만 아니라, 메모리 자원을 얻는 데 필요한 비용을 줄여줄 수 있다.
무엇보다 dynamic하고 flexible한 메모리 관리 구조를 통하여 오라클 데이타베이스 관리를 단순화시켜 준다.
2. MMAN 백그라운드 프로세스
공유 메모리의 자동 튜닝을 위하여 MMAN이라는 백그라운드 프로세스가 새로이 등장하였다.
MMAN이라는 백그라운드 프로세스가 5분 마다 주기적으로 수집한 작업 부하(Workload) 정보를 바탕으로 동적으로 구성이 된다. 메모리는 가장 필요한 곳으로 동적으로 할당이 된다.
SPFILE을 사용하면 MMAN이 변경한 파라미터들의 정보가 자동으로 SPFILE에 저장이 된다.
그러므로, 가능한 Oracle 9i 이상부터는 SPFILE 의 사용을 권장한다.
왜냐 하면 다음과 같은 세 가지 장점이 있기 때문이다.
첫째, 각 부분 크기의 권장안을 인스턴스 종료 후에도 보관할 수 있다.
둘째, 저장되어진 각 파라미터들의 사이즈는 데이타베이스 기동 시 할당이 된다.
세째, 각 파라미터들의 최적의 값을 찾는 데 드는 비용을 줄일 수 있다.
이렇게 MMAN 이라는 프로세스에 의해서 자동 공유 메모리 관리 기능이 구현이 되는 것이다.
3. SGA_TARGET 파라미터를 통한 자동 공유 메모리 관리
자동 공유 메모리 관리 기능을 사용하게 되면 오라클 SGA 관련 네 가지 파라미터들을 DBA가 일일이 셋팅할 필요가 없다.
오라클의 공유 메모리 크기는 SGA_TARGET 이라는 파라미터 하나로 다 조절이 된다.
SHARED_POOL_SIZE, DB_CACHE_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE 라는 파라미터들을 구성하는 데 관여하지 않아도 된다는 뜻이다.
과거에는 이러한 파라미터들을 너무 낮게 잡게 되면 성능도 저하될 뿐만 아니라 out-of-memory error인 ORA-4031 ERROR를 자주 만나게 되었고 메모리 낭비도 있었다.
그러나, Oracle database 10g의 New Feature는 SGA_TARGET이라는 새로운 파라미터만 셋팅하여도 되게 설계되었다.
SGA_TARGET이라는 파라미터는 해당 인스턴스에 필요한 SGA의 최대 크기를 나타낸다.
이 파라미터는 SGA 내의 모든 메모리들을 포함한다.
즉, Automatic하게 사이즈가 결정되는 파라미터, 매뉴얼하게 결정되는 파라미터, startup 시에 할당되는 internal metadata 할당을 다 포함한다.
10g 이전 버젼처럼 SGA의 TOTAL 크기를 정확히 컨트롤하는 것이 어렵지가 않다는 뜻이다.
SGA_TARGET 파라미터를 셋팅할 때에는 다음을 염두에 두어야 한다.
1) SGA 영역 중 자동 구성으로 조절되지 않는 영역이 있는데
Redo Log buffer 와 Fixed SGA 영역, BUFFER KEEP, RECYCLE과 관련된 파라미터, STREAMS POOL 관련 파라미터들이다.
2) SGA_TARGET 파라미터가 0으로 셋팅되면 자동 공유 메모리 기능은 DISABLE된다.
SGA_TARGET 파라미터의 DEFAULT 값은 0이다.
SGA_TARGET 파라미터가 0이 아닌 어떤 값으로 셋팅이 되어 있어도
SHARED_POOL_SIZE 또는 DB_BLOCK_BUFFERS 와 같은 파라미터들은 여전히 셋팅할 수는 있다.
3) SGA_TARGET 파라미터가 설정되어 있지 않거나 0 으로 셋팅되어 있을 경우,
자동 튜닝 파라미터들은 10g 이전 버젼에서와 같이 관리된다.
단, 예외는 있다. SHARED_POOL_SIZE가 그 경우인데, 내부적인 STARTUP 시 소모되는 오버헤드는 포함이 된다. 이 부분이 SHARED_POOL_SIZE에 포함이 된다.
Oracle 10g 이전 버젼에서는 이 부분이 SHARED_POOL_SIZE에 포함되지 않았으나
10g부터는 포함되게 되어 SHARED_POOL_SIZE를 좀 더 크게 잡아야 한다.
구체적으로 10g에서는 SHARED_POOL_SIZE를 32m 정도 더 크게 잡아야 한다.
예를 들어, Oracle 9i에서 SHARED_POOL_SIZE를 256M를 사용했었다면,
Oracle 10g에서는 같은 효과를 얻으려면 288M 정도로 잡아야 한다.
4) SGA_TARGET 파라미터가 0이 아닌 값으로 셋팅이 되어 있는 경우
자동으로 튜닝되는 SGA 파라미터들은 모두 기본적으로 0으로 셋팅이 된다.
이러한 파라미터들은 자동 공유 메모리 관리 알고리즘에 의하여 자동으로 사이즈가 결정이 된다.
그러나, 만약 이러한 자동 튜닝 파라미터들이 0이 아닌 어떤 값으로
셋팅이 되어 있다면 명시한 값은 자동 튜닝 알고리즘에 의해 최저값을 나타낸다.
예를 들어, SGA_TARGET 파라미터가 8G로 셋팅되어 있고, SHARED_POOL_SIZE가
1G로 셋팅되어 있다면 SHARED_POOL_SIZE는 절대 1G 아래로 떨어지지 않음을 뜻한다.
V$SGA_Dynamic_components 뷰를 조회하면 자동 튜닝 component들의 실제 사이즈를 확인할 수 있다.
SELECT component, current_size/1024/1024
FROM v$sga_dynamic_components;
4. 수동으로 튜닝되는 SGA 파라미터 설정
SGA의 몇몇 구성 파라미터들은 자동으로 튜닝되지 않아서 매뉴얼하게 튜닝을 해야 한다.
다음은 수동으로 튜닝되는 SGA 파라미터들이다.
- KEEP 및 RECYCLE 버퍼 캐시
- 멀티 블록 사이즈 캐시(DB_nK_cache_size)
- 로그 버퍼
- 스트림즈 POOL
수동으로 튜닝되는 파라미터들은 반드시 사용자에 의해 명시되어져야 한다.
이런 파라미터들은 10g 이전 버젼에서와 같이 정밀하게 크기가 제어되어야 한다.
매뉴얼하게 튜닝되는 이러한 파라미터들은 SGA_TARGET에는 포함되지만, 자동으로 튜닝되지는 않는다.
예를 들어, SGA_TARGET 이 8G이면 MANUAL하게 수동으로 조절되는 파라미터들의 사이즈의 합을 1G로 잡으면 7G는 자동으로 오라클이 알아서 설정한다.
5. SGA_TARGET 파라미터의 설정 변경
SGA_TARGET 파라미터의 RESIZING이란 SGA_TARGET 파라미터의 설정 변경을 의미한다.
SGA_TARGET 초기화 파라미터의 특징은 다음과 같다.
첫째, 운영 중에 동적으로 변경이 가능하다.
둘째, SGA_MAX_SIZE 내에서 크기를 증가시킬 수 있다.
세째, 모든 구성 요소의 최저값의 합까지 크기를 감소시키는 것이 가능하다.
또한, SGA_TARGET 파라미터의 설정은 오직 자동으로 튜닝할 수 있는
파라미터들에만 영향을 준다.
예를 들어, SGA_MAX_SIZE가 10G이고, SGA_TARGET이 8G로 가정을 한다.
만약, DB_KEEP_CACHE_SIZE가 1G로 셋팅이 된다고 하면 SGA_TARGET 을 최대 9G까지 늘릴 수 있다.
추가적인 1G는 SGA_TARGET에 의해 조절되는 자동 튜닝 파라미터들에게 분배가 된다.
DB_KEEP_CACHE_SIZE 는 영향을 받지 않는다는 뜻이다.
이것은 SGA_TARGET 파라미터를 줄일 때에도 해당된다.
6. 자동 공유 메모리 관리 비활성화
이 기능을 비활성화하려면 SGA_TARGET 파라미터를 0으로 설정하면 된다.
이렇게 SGA_TARGET 파라미터를 0으로 설정하게 되면 자동 튜닝 파라미터들은 그 당시의 값으로 설정된다.
그리고, 전체 SGA 크기에는 영향을 미치지 않는다.
예) 변경 전 값
SGA_TARGET=8G
SHARED_POOL_SIZE=1G
변경 후 값
SGA_TARGET=0
SHARED_POOL_SIZE=2G
DB_CACHE_SIZE=4G
LARGE_POOL_SIZE=512M
JAVA_POOL_SIZE=512M
SGA_TARGET이 8G로 셋팅되어 있고, SHARED_POOL_SIZE 파라미터가 1G로
설정되어 있다가 SGA_TARGET을 0으로 변경하면 자동 튜닝 파라미터들은
그 당시의 값으로 셋팅되고 전체 SGA 크기는 이전 SGA 크기를 초과하지 않는다.
SHARED_POOL_SIZE가 2G로 셋팅이 된 것은 내부적으로 측정하여 결정된 수치이다.
7. 동적 SGA 파라미터의 수동 변경
자동 튜닝되는 파라미터를 수동으로 변경할 경우 다음과 같은 영향을 가진다.
1) 만약 파라미터의 새로이 반영되는 값이 현재의 값보다 클 경우에는 즉시 반영이 된다.
그러나, 새로이 반영되는 값이 현재의 값보다 작을 경우에는 현재의 값에는 즉시 변화가 없고 최저값으로 셋팅이 된다.
2) 자동으로 튜닝되는 파라미터들을 수동으로 변경하였을 경우에는 SGA의 자동 튜닝 부분에 영향을 준다.
RESIZE 수행 시에 사용되는 메모리는 자동 튜닝 파라미터들로부터
더해지거나 감해질 뿐 매뉴얼하게 조절되는 파라미터들에는 영향을 주지 않는다.
Example
다음은 자동 튜닝 파라미터 설정 시 V$PARAMETER 조회 결과 예이다.
SGA_TARGET=8G
DB_CACHE_SIZE=0
JAVA_POOL_SIZE=0
LARGE_POOL_SIZE=0
SHARED_POOL_SIZE=0
SELECT name, value, isdefault
FROM V$PARAMETER
WHERE name LIKE '%size%';
만약 SGA_TARGET 파라미터가 0이 아닌 값으로 셋팅이 되어 있는 경우 자동 튜닝되는 파라미터들은 값을 명시하지 말라는 뜻이다.
V$PARAMETER 뷰를 조회하였을 때 자동으로 튜닝되는 이러한 SGA 파라미터들의 값은 모두 0이다.
이것이 정상이고, isdefault 컬럼 값은 TRUE 이다.
즉, 자동 공유 메모리 관리 기능을 구현할 때에는 이 파라미터들은 매뉴얼하게 설정을 하지 않으면 된다.
ubuntu:~/oracle/Script$ vi test.sql
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_language = 'AMERICAN';
select * from dept;
ubuntu:~/oracle/Script$ ls test.sql ubuntu:~/oracle/Script$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on 화 2월 16 17:26:22 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> show echo echo OFF --default 로 지정됨. SQL> @test.sql
Session altered.
Session altered.
DEPTNO DNAME LOC ----------- ----------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL> set echo on SQL> @test.sql SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'; --명령어가 자동으로 출력됨.
Session altered.
SQL> SQL> alter session set nls_language = 'AMERICAN';
Session altered.
SQL> SQL> select * from dept;
DEPTNO DNAME LOC ----------- ----------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL> SQL> SQL> exit --셋팅 된 값들을 초기 상태로 돌려놓기 위해서 저는 그냥 exit하고 다시 접속합니다. (다른 이것저것 셋팅 된 것들이 많아지면 원복하기 힘들어서요.reset하는 방법을 몰라서..흐흐) --참고로 컬럼설정에 대한 초기화는 clear column; 입니다. Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr oduction With the Partitioning, OLAP and Data Mining options
ubuntu:~/oracle/Script$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on 화 2월 16 17:32:47 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> show feedback FEEDBACK ON for 6 or more rows --default 값은 선택된 행이 6행 이상일 때 row수를 알려준다.
SQL> @test.sql
Session altered.
Session altered.
DEPTNO DNAME LOC ----------- ----------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL> set feedback 3 --선택된 행을 3으로 해보겠습니다. (선택된 행이라는 말이 이해 안가서 한번 해봤습니다.) SQL> @test.sql
Session altered.
Session altered.
DEPTNO DNAME LOC ----------- ----------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
4 rows selected. --아하..요놈이 출력 되는군요! (요놈의 숫자가 feedback의 수에 따라서 출려되느냐 안되느냐 차이였습니다.)
SQL> set feedback off SQL> @test.sql
DEPTNO DNAME LOC ----------- ----------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL>
구글링을 하다보면
같은 내용으로 Data Dictionary object 에 대한 설명을 올려놓은 글들이 많이 있었습니다.
개인적으로 정렬도 잘 안되고 보기가 많이 불편하여 엑셀파일로 정리를 하였습니다.
(단순히 긁어다가 넣었을 뿐입니다.ㅋㅋㅋ)
보기에는 충분한 자료와 내용들이지만,
오래전에 올린 것들이라 좀 더 업그레이드 된 글들을 찾아 볼수가 없어서 아쉽네요!!
제가 만들기에는 턱없이 부족한 스킬이라...^^;;; 글재주도 없고요.ㅎㅎ
그냥 가지고 있다가 필요할때마다 찾아보면 많은 도움이 될 것입니다.
DBA가 기본적(?)으로 알아야하는 것이기 때문에 필히 숙지를 하시는 것도 좋은 방법이 아닐까요?^^
[참고문헌 : export one-on-one ORACLE]
* 전역 임시 테이블 임시테이블을 생성하는 동작은 공간 할당까지 포함하지 않으며, 다른 종류의 테이블들과는 다르게 initial 익스텐트가 할당되지 않는다. 런타임에 세션이 처음으로 임시 테이블에 데이터를 삽입하면 그 세션을 위한 임시 세그먼트가 생설될 것이다. 각 세션은 자신만의 임시 세그먼트(존재하는 세그먼트의 익스텐트가 아니다)를 가지기 때문에 모든 사용자들은 임시 테이블을 위한 공간을 서로 다른 테이블 영역에서 할당할 수 있을 것이다.
*임시 테이블은 트리거, 체크 제약조건, 인덱스 등과 같은 영구 테이블의 많은 속성들을 갈질 수 있다. 임시 테이블에서 지원되지 않는 영구 테이블의 특성은 다음과 같다.
1. 참조 무결성 제약조건을 가질 수 없다. 임시 테이블들은 외래 키의 대상이 될 수 없고, 임시 테이블에 대해 선언된 외래 키를 가질 수도 없다. 2 .VARRAY 혹은 NESTED TABLE 유형의 열을 가질 수 없다. 3. 인덱스 구조 테이블이 될 수 없다. 4. 인덱스나 해시 클러스터 안에 있을 수 없다. 5. 분할될 수 없다. 6. ANAYLYZE 명령어를 통해 생성된 통계 정보를 가질 수 없다.
SQL> create global temporary table temp_table_session 2 on commit preserve rows 3 as select * from dept where 1=2;
Table created.
SQL> create global temporary table temp_table_transaction 2 on commit delete rows 3 as 4 select * from dept where 1=2;
Table created.
SQL> create global temporary table temp_table_default -- 아무것도 지정하지 않음. 2 as select * from dept where 1=2;
Table created.
SQL> insert into temp_table_session values(70,'SCOTT','TIGER');
1 row created.
SQL> insert into temp_table_transaction values(70,'SCOTT','TIGER');
1 row created.
SQL> insert into temp_table_default values(70,'SCOTT','TIGER');
1 row created.
SQL> select session_cnt, transaction, default_cnt 2 from (select count(*) session_cnt from temp_table_session) a, 3 (select count(*) transaction from temp_table_transaction) b, 4 (select count(*) default_cnt from temp_table_default) c 5 /
SESSION_CNT TRANSACTION DEFAULT_CNT -------------- -------------- ------------- 1 1 1
SQL> commit;
Commit complete.
SQL> select session_cnt, transaction, default_cnt 2 from (select count(*) session_cnt from temp_table_session) a, 3 (select count(*) transaction from temp_table_transaction) b, 4 (select count(*) default_cnt from temp_table_default) c 5 /
SESSION_CNT TRANSACTION DEFAULT_CNT -------------- -------------- -------------- 1 0 0
SQL>
나의 의견 : 아무것도 지정해 주지 않은 것은 transaction 설정을 default로 가지고 있는가가 저는 궁금합니다. 위 테스트의 결과를 보면 default 값으로 가지고 있지 않나 하는 생각이 듭니다.
[출처 : PRODBA 의 가을바람님의 글을 보고 테스트를 해보았습니다.]
1. 트리거의 이용
SQL> create or replace trigger emp_sal_read_only --트리거 이름 2 before insert or update or delete 3 on emp --원하는 테이블명 4 begin 5 raise_application_error 6 (-20001,'Table EMP is read only, You cannot make changes to the data.'); 7 end; 8 /
Trigger created.
SQL> delete from emp; delete from emp * ERROR at line 1: ORA-20001: Table EMP is read only, You cannot make changes to the data. ORA-06512: "SCOTT.EMP_SAL_READ_ONLY", 줄 2에서 ORA-04088: 트리거 'SCOTT.EMP_SAL_READ_ONLY'의 수행시 오류
SQL> drop trigger emp_sal_read_only;
Trigger dropped.
SQL> delete from emp;
14 rows deleted.
SQL> rollback;
Rollback complete.
SQL>
2. read only tablespace 를 이용
SQL> select * from tab
TNAME TABTYPE CLUSTERID ---------- -------------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE AA TABLE SALES TABLE DEPT_TEST TABLE EMP_TEST TABLE NUM_TEST TABLE
9 rows selected.
SQL> create tablespace temp_readonly 2 datafile '/oracle/oradata/PROD/Disk5/temp_readonly.dbf' size 5M reuse 3 autoextend on;
Tablespace created.
SQL> alter table emp_test move tablespace temp_readonly;
Table altered.
SQL> alter tablespace temp_readonly read only;
Tablespace altered.
SQL> delete from emp_test; delete from emp_test * ERROR at line 1: ORA-00372: 파일 7는 지금 수정될 수 없습니다 ORA-01110: 7 데이터 파일: '/oracle/oradata/PROD/Disk5/temp_readonly.dbf'
SQL> drop tablespace temp_readonly including contents and datafiles cascade constraints;
Tablespace dropped.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID ---------- -------------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE AA TABLE SALES TABLE DEPT_TEST TABLE NUM_TEST TABLE
8 rows selected. --> emp_test가 사라졌네요..잉ㅜㅜ 조심하세요!!
나의 의견 : 1. 트리거를 이용한 방법은 새롭게 알게된 것이며, 테스트 해 본 결과 유용하게 쓰일 것 같습니다. 2. 테이블을 옮기고 테이블스페이스를 다시 수정하고 번거로운 작업있긴하지만, 이것 또한 테이블을 read only하는데 유용한 정보입니다.
위 내용과 무관하지만 테이블스페이스를 삭제하면 그 안에 데이터도 사라진다는 것 또한 잊지마세요!!
어제 회의가 끝나고 회식자리를 가기 위해서 제자리로 돌아와 컴퓨터를 보니 메신저로 아는 분께서 GATHER_STATS_JOB을 disable시킬려고 하는데 에러가 났다고 좀 봐달라고 하였습니다. 회식자리를 가야하는 급한마음에 얼렁뚱땅 에러만 보고 잘모르겠다고 하였습니다. 그러나 회식자리에서 계속 그 에러 생각이 떠나질 않았습니다. 많이 궁금하더라구요. 무슨 에러일까.. 모지모지~ㅎㅎㅎ(모..중간에는 잊어버렸지만요..ㅋㅋ)
회식 자리 가기 전에 메모장에 에러메시지를 저장을 해놓고 나간터라 오늘 출근하여 그 메세지를 다시 한번 보았습니다. 깜짝 놀랐습니다.
에러메세지는 아래와 같았습니다. SQL> exec dbms_scheduler.disable('GATHER_STATS_JOB'); BEGIN dbms_scheduler.disable('GATHER_STATS_JOB'); END;
* 1행에 오류: ORA-27476: "SYSTEM.GATHER_STATS_JOB"이(가) 존재하지 않음 ORA-06512: "SYS.DBMS_ISCHED", 줄 2763에서 ORA-06512: "SYS.DBMS_SCHEDULER", 줄 1753에서 ORA-06512: 줄 1에서
존재 하지 않음(?)...gather_stats_job이 존재하지 않는다는거지요? 컥 이런 간단한 문제를...ㅠㅠ
그러나 보내준 내용을 보면 gather_stats_job이 있다고 하네요. SQL> select state from dba_scheduler_jobs 2 where job_name = 'GATHER_STATS_JOB';
STATE ------------------------------ SCHEDULED
응? 이건 또 무슨 문제일까라고 생각을 해보니~ 혹시...설마...라는 의구심으로 system 계정에 들어가서 테스트를 해본 결과.. 역시나 같은 에러가 떨어지더라구요..
원인은 계정문제였습니다. sys계정이 아닌 system 계정으로 접속을 하여 disable을 시킬려고 한것입니다. 제 테스트 디비에 있는 걸로 보니 그렇더군요..ㅋㅋ
SQL> select owner, job_name, state from dba_scheduler_jobs 2 where job_name = 'GATHER_STATS_JOB';
OWNER JOB_NAME STATE ------------------------ ------------------------- --------------- SYS GATHER_STATS_JOB SCHEDULED
이 말을 꼭 전해드리고 싶지만, 이미 지나간 시간이고..너무 간단한 문제였던 터라 벌써 해결했으리라 생각을 해봅니다. 그리고 정말 죄송합니다.ㅠㅠ
[출처] : http://cafe.naver.com/prodba/16202 PRODBA 카페에서 지구인 님께서 올려주신 것.
SELECT TARGET_DATE T_DATE, TO_CHAR(TARGET_DATE, 'DAY') T_DAY FROM ( SELECT ADD_MONTHS(TO_DATE(LEVEL, 'DDD'), 0) TARGET_DATE FROM DUAL CONNECT BY LEVEL<=365 ) ;
0 을 조절해주면서 년을 수정해 줄수 있습니다. Ex. 12 이면 2011년, 24이면 2012년...
평소 자주 찾아보는 욱짜님 블로그에 있는 설명 그대로를 테스트 해본 결과 입니다.
1. bdump의 경로를 알아보고 지정.
SQL> col value new_value back_dump SQL> select value from v$parameter where name = 'background_dump_dest';
VALUE -------------------------------------------------------------------------------- /oracle/oradata/PROD/admin/bdump
SQL> create or replace directory back_dump_dir as '&back_dump'; old 1: create or replace directory back_dump_dir as '&back_dump' new 1: create or replace directory back_dump_dir as '/oracle/oradata/PROD/admin/bdump'
Directory created.
SQL>
2. get_trace_file3생성. -> 불러올 파일을 읽는 함수 같군요.
http://sites.google.com/site/ukja/sql-scripts-1/c/get_trace_file 참조.
SQL> create or replace type varchar2_array as table of varchar2(4000); 2 /
Type created.
SQL> create or replace function get_trace_file3(dir_name in varchar2, file_name in varchar2) 2 return varchar2_array 3 pipelined 4 as 5 v_handle utl_file.file_type; 6 v_line varchar2(2000); 7 begin 8 v_handle := utl_file.fopen(dir_name, file_name, 'R', 32767); 9 loop 10 begin 11 utl_file.get_line(v_handle, v_line); 12 exception when no_data_found then 13 exit; 14 end; 15 pipe row(v_line); 16 end loop; 17 19 utl_file.fclose(v_handle); 18 20 return; 21 end; 22 /
Function created.
SQL>
3. global temporary table 생성 -> commit 또는 섹션종료시에 데이터가 삭제되는 전역임시테이블생성
SQL> create global temporary table t_alert_analyze 2 ( 3 reg_date date, 4 error_code varchar2(10), 5 message varchar2(4000) 6 );
Table created.
SQL>
4. alert_analyze.sql script 실행
SQL>@alert_analyze.sql (or @alert_analyze.sql alert_PROD.log % % %)
* alert_analyze.sql 의 파일 내용.
define __ALERT_LOG = "&1" -> alert 파일 명(ex. alert_PROD.log) define __START_DT = "&2" -> 검색 시작 날짜(2009/07/01 or %(default)) : sysdate - 100000 define __END_DT = "&3" -> 검색 끝 날짜(2009/07/01 or %(default)) : sysdate + 1 define __PATTERN = "&4" -> alert 파일에 대한 특정 패턴 (default 설정 '%')
set serveroutput on
delete from t_alert_analyze;
declare v_date date; v_start_dt date; v_end_dt date; v_err_code varchar2(10); v_message varchar2(4000); b_include boolean := false; begin select decode('&__START_DT', '%', sysdate - 100000, to_date('&__START_DT', 'yyyy/mm/dd')) into v_start_dt from dual; select decode('&__END_DT', '%', sysdate + 1, to_date('&__END_DT','yyyy/mm/dd')) into v_end_dt from dual;
for r in (select column_value as txt from table(get_trace_file3('BACK_DUMP_DIR', '&__ALERT_LOG'))) loop -- dbms_output.put_line(r.txt); if regexp_like(r.txt, '[0-9][0-9]:[0-9][0-9]:[0-9][0-9] [0-9][0-9][0-9][0-9]') then v_date := to_date(r.txt, 'Dy Mon dd hh24:mi:ss yyyy'); if b_include = false then if v_date between v_start_dt and v_end_dt then b_include := true; end if; end if; end if; if b_include then if r.txt like '%&__PATTERN%' then if r.txt like 'ORA-%' then -- error code exists v_err_code := substr(r.txt, 1, 9); v_message := substr(r.txt, 12); else v_err_code := '-'; v_message := r.txt; end if; insert into t_alert_analyze(reg_date, error_code, message) values(v_date, v_err_code, v_message); end if; end if; end loop; end; /
select to_char(reg_date,'yyyy/mm/dd') as "when", error_code, count(*) from t_alert_analyze where error_code like '%ORA%' group by to_char(reg_date,'yyyy/mm/dd'), error_code order by 1 desc, 3 desc ;
나의 의견 : 설명이 잘 나와있었음에도 불구하고 내공이 부족하여, 많은 시행착오로 드디어 성공하였습니다. 위 쿼리문들을 보고 공부를 해두어야 할 것이 몇 가지가 생겨나서 적어봅니다. 1. 전역 임시 테이블에 관하여... (기본적인 사항은 알고 있었지만, 좀 더 세밀하게 파악을 해보도록 하겠습니다.)
2. 중첩테이블에 대해서... (이런 것이 있다는 것만 알았지 명령어가 무엇인지, 어떤 기능을 가지고 있는지, 등등 현재로써는 아는 것이 하나도 없습니다.)
3. regexp_like에 대해서... (음.. 이건 오라클에서 정규화를 쓸수있는 함수라고는 알고 있었고, 예전부터 이에 대해 공부를 해야겠다고 마음만 갖고 있었는데 이 함수가 이곳에 또 나와있어서 하루 빨리 공부를 해야하지 않을까라는 생각이 들었습니다.)
추후 위 사항들에 대해서 글을 남기도록 하겠습니다.
< 일일 CECKLIST > <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><?xml:namespace prefix = o /> l DATABASE 1.1 매일 alertSID.log 화일의 내용과 trace file의 내용을 check - 이 화일에서 internal error나 다른 oracle error들을 알수 있다. 이 화일의 내용은 무한히 늘어나므로 이 화일의 directory space도 조절할 필요가 있다. 1.2 alerSID.log화일이나 trace 화일 일정 크기 이상이 되면 backup - alertSID.ora는 무한히 커지므로 적당한 양만큼 bacup을 받아라. 이 화일로 장애 발생의 유추가 가능하므로 필요하다. 1.3 *_dump_dest의 free space여부를 항상 확인 - InitSID.ora이나 configSID.ora에 *_dump_dest가 설정되어 있다. 1.4 각 tablespace에 free space의 fragmentation이 일어났는지 확인 - fragmentation이 많이 일어나고 free space는 많이 존재하지 않는다면 하나의 data file을 첨가하라.fraementation도 높고 free space와 disk space가 거의 존재하지 않는다면 table들과 free space를 각각 연속적으로 연결이 되도록 backup/export를 받은 후 다시 drop/import를 하고 재구성한다. 1.5 각 tablespace에 free space를 생성되는 속도를 확인 - 즉 database의 성장 속도를 확인하여 space 부족으로 생길 수 있는 DB가 hang이 걸리는 문제를 미리 대비할 수 있도록 하라. 2 CONTROL FILE 2.1 매일 밤 hot backup을 받아라. (ARCHIVE경우) 3 Online Redo Log File 3.1 V$LOG를 사용해서 invalid하거나 stale 상태를 check하라. - INVALID log file error는 I/O error로서 alert.log에 기록되지 않으며 alert.log file을 분석함으로써탐지가 가능하다. STALE은 shutdown abort전에 쓰여지고 있는 log가 완전하지 않거나 그log에 대해 걸려있는 write 상태가 알 수 없는 것일 때에 생성된다. 이것도 역시 alert.log 화일에 기록되지 않는다. 이런 현상이 자주 일어나면 hardware의 문제를 가리키는 것일 수도 있다. 3.2 log switch interval을 자주 check하라. - log switch interval은 위의 time의 차이를 계산하면 알 수 있다. Log Switch가 너무 자주 발생하면 혹시 hot backup 상태로 두고 있는 화일이 있는지 확인 하라. 3.3 checkpoint 간격을 자주 확인 - 권할 만한 checkpoint의 간격은 매10에서 15분 정도이다.이 checkpoint의 간격은 Background process가 죽어서 instance가 abort되는 극한 상황에서 database를 살리고 잠깐의 시간 동안crash recovery를 할 때 반영된다.위의 간격을 조절하려면 database에서 checkpoint interval setting 또는 checkpoint_timeout을 조절함에 의해 가능하다.checkpoint_timeout을 0으로 그리고checkpoint_interval을 online redo log file의 크기보다 크게 두면 checkpoint는 log switch가 일어날때 일어난다. - 잦은 checkpoint는 crash recovery의 기간은 줄여주나 dirty buffers를 자주 쓰는 것과 file headers를 자주 update하는데 드는 overhead가 발생한다. 4 Rollback Segment tablespace 4.1 Rollback Segment가 online이 되어있는지 확인 - 어떤 rollback segment는 의도적으로 offline이 되어있을 수 있다. 예를 들면 rollbacksegment를 가진 datafile에 문제가 발생시 등에서다. 이런 경우의 원인을 조사하라. 4.2 ORA-1555 error가 생성되는지 여부를 확인 - Database는 여전히 사용가능하며 application error가 일어날 수도 있다. 4.3 ORA-1538,1551,1552,1553,1554,1555,1556,1557,1558,1559,1562를 check하라. - 위의 error는 extent를 할당할 수 없거나 tablespace fragmentation이 일어나는 경우에 나타난다.위의 error가 발생해도 database는 여전히 사용이 가능하나 application errors가 일어날 수 있다. - 이 경우datafile을 더함으로써 많은 space를 추가하거나 더욱 큰 rollback segments를 추가하여더 큰 transaction을 다룰 수 있도록 재구성하라. 5 Archived Redo Logs (ARCHVIE LOG MODE ONLY) 5.1 archive file이 생성되는 destination에 여유 공간이 있도록 유지하라. - disk에 여유공간이 없어 archive log를 write할 수 없어서 DB가 hang이 걸림을 방지하기 위해서 필수적이다.archive destination에 freespace가 특정 threshold이상이면 alarm하게 함으로써 수시 점검이 가능하도록 하여야 한다 5.2 archived log file을 특정 threshold에 도달할 때마다 backup을 받아라. - Archived redo log file의 갯수는 log file의 크기와 redo의 양에 의해 달려있다. 그리고 redo의 양은 transaction의 비율과 연관성이 있다. 위의 양에 따라 더 자주backup을 받을 수 있다. backup을 받을 때 archiver가 완전이 다 쓴 archived redo log file만을 받도록 해야 한다. 5.3 Archived redo log file의 sequence number가 순차적인지 확인 - Archive file에 이름이 명명되어질 때 archived log file은 log sequece가 주어지도록 되어 있다.그러므로 log switch가 일어날 때마다 sequence number는 하나씩 증가된다. 그러나,OPS의 경우에는 thread number가 함께 명명되어짐을 잊지 말아야 한다. 5.4 ARCH process가 움직이는지를 자주 확인하라 - OS상에서 ARCH process가 움직이는지 확인함으로써 ARCH process가 움직이지 않아서DB가 hang이 걸리는 문제를 막을 수 있다. 5.5 alert.log에 Archive log들에 관한 error가 있는지 확인 - 위의 화일은 대걔 $ORACLE_BASE/admin/$ORACLE_SID/bdump에 존재하거나 initSID.ora (parameter file)내의 *_dump_dest방향을 참조하라. 6 OS detection 6.1 Disk failure나 controller의 이상이 있는지 항상 확인 6.2 OS mirroring이 되고 있는지 항상 확인
number(p,s) : 전체 p자리 중 소수점 이하 s자리(p:1~38, s:-84~127)
number 형식 컬럼에 대한 length를 지정하지 않고 사용되는 경우가 있습니다.예를 들자면 create table num_test (num1 number); 라고 지정을 해놓는데, 데이터가 일정치 않을 경우 엄청난 값의 길이가 들어갈 수 있는 상황이 발생하겠죠.
그래서 흔히들 number(10) 같이 길이를 지정해 주고 사용되고 있습니다. 소수점을 넣을려고 한다면 number(10,2) 이런 식으로 format을 설정해 줍니다.
제가 하고 싶은 말은 얼마전 우연히 number(3,8) 에 대해서 의문을 가지게 되었습니다. 즉, p가 s보다 작을 경우 어떻게 되는가 입니다. 아직까지도 정확하게 잘 모르겠습니다.
테스트를 해본 결과 p가 s보다 작을경우 값이 0을 제외하고는 들어가지 않는다는 거죠! 제 생각으로는 소수점 8자리까지 표시를 가능하게 했으나 길이가 3으로 설정되어, 너무 짧게 잡아 놓아서 들어가지 않는다고 일단은 결론을 지웠습니다.
그러나 또 다른 의문이 생겼습니다. 그렇다면 insert into num_test values(1.5); 들어가야 하지 않나? 입니다. 저 값의 길이는 2가 되고 소수점도 한자리밖에 차질하지 않으니 들어가야하지 않을까요? 역시나 들어가지 않더군요. 이것 또한 제가 지은 결론은 number(3,8)은 소수점 자리를 제외한 나머지 길이값이 정수값이 된다 입니다. 3-8 = -5가 되니 정수값은 넣어주질 못하는 것이고.. number(6,5) 일 경우는 6-5 = 1 이 되는 한자리의 정수값은 들어가게 됩니다.
여기서 또 다른 의문!! number(3,8) 일때는 정수값은 못들어간다 해도 소수점 자리는 3자리까지는 들어가야지 않나? 입니다. 그러나 들어가질 않습니다.
최종 결론을 내리자면 p값이 s보다 작을 경우는 값이 들어가질 않는다 입니다. 애초에 format을 설정할때 만들어지지 않게 했다면 이해를 했겠건만 설정이 되니, 무엇인가가 되는게 아닌가 의심을 해봅니다.
여기저기 찾아봐도 저의 궁금증을 풀어줄 만한 자료는 찾질 못햇습니다. 좀 더 확인 작업을 하고 확실하게 알아봐야 할 것 같습니다. 뭔가 확실한 이유를 찾아서..ㅎㅎ
참고로 아래 사항은 제가 테스트 해본 결과 입니다.
SQL> create table num_test (a number);
Table created.
SQL> alter table num_test modify a number(3,5);
Table altered.
SQL> insert into num_test values(1.1); insert into num_test values(1.1) * ERROR at line 1: ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.
SQL> alter table num_test modify a number(4,5);
Table altered.
SQL> insert into num_test values(1.1); insert into num_test values(1.1) * ERROR at line 1: ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.
SQL> alter table num_test modify a number(5,5);
Table altered.
SQL> insert into num_test values(1.1); insert into num_test values(1.1) * ERROR at line 1: ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.
SQL> insert into num_test values(.1);
1 row created.
SQL> select * from num_test;
A ---------- 0 0 .1
SQL> alter table num_test modify a number(6,5);
Table altered.
SQL> insert into num_test values(0.1);
1 row created.
SQL> insert into num_test values(1.1);
1 row created.
SQL> insert into num_test values(10.1); insert into num_test values(10.1) * ERROR at line 1: ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.
SQL> insert into num_test values(1.12);
1 row created.
SQL> insert into num_test values(1.12123141);
1 row created.
SQL> drop table num_test;
Table dropped.
테스트 테이블 확인.
SQL> select deptno, max(sal) from emp group by deptno;
DEPTNO MAX(SAL) ---------- ---------- 30 2850 20 3000 10 5000
SQL> select * from dept_test;
DEPTNO DNAME LOC SAL ---------- ------------------- ----------- ---------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 2850 40 OPERATIONS BOSTON -1 DBA Seoul 40 SALES CHICAGO 50 SALES CHICAGO 1111
7 rows selected.
무심코 지나쳐 버릴 것 같은 update문을 재연해 보았습니다. 아래와 같은 쿼리문을 가끔 날려서 실수를 범하는 경우가 있어서 간단하게 정리를 해보았습니다.
SQL> update dept_test a 2 set sal = (select max(sal) from emp b where a.deptno=b.deptno group by deptno);
7 rows updated.
SQL> select * from dept_test;
DEPTNO DNAME LOC SAL ---------- ------------------- ----------- ---------- 10 ACCOUNTING NEW YORK 5000 20 RESEARCH DALLAS 3000 30 SALES CHICAGO 2850 40 OPERATIONS BOSTON -1 DBA Seoul 40 SALES CHICAGO 50 SALES CHICAGO
7 rows selected. --> 위 update 조건에 맞지 않는 것은 null값으로 처리가 되네요.
SQL> rollback;
Rollback complete.
<해결방안>
아래 사항들은 가끔 제가 사용하는 것입니다. 방법은 여러가지가 있겠죠^^; (오래전에 구글링으로 찾아서 update시 유용하게 사용하고 있어서 정리를 합니다.)
SQL> update dept_test a 2 set sal = (select max(sal) from emp b where a.deptno=b.deptno group by deptno) 3 where deptno in (select deptno from emp group by deptno);
3 rows updated.
SQL> select * from dept_test;
DEPTNO DNAME LOC SAL ---------- ------------------- ----------- ---------- 10 ACCOUNTING NEW YORK 5000 20 RESEARCH DALLAS 3000 30 SALES CHICAGO 2850 40 OPERATIONS BOSTON -1 DBA Seoul 40 SALES CHICAGO 50 SALES CHICAGO 1111
7 rows selected. --> 테이블을 2번 읽어야 하고 데이터가 많을 경우 시간이 많이 걸리는 단점이 있어요. 예전에 한도끝도 없이 기다렸다가 결국에는 취소를 시켰다는..ㅠㅠ
SQL> rollback;
Rollback complete.
SQL> update dept_test a 2 set sal = nvl((select max(sal) 3 from emp b where a.deptno=b.deptno group by deptno), a.sal);
7 rows updated. --> 업데이트 할 값이 null 이라면 원래 값을 넣어줌.
SQL> select * from dept_test;
DEPTNO DNAME LOC SAL ---------- ------------------- ----------- ---------- 10 ACCOUNTING NEW YORK 5000 20 RESEARCH DALLAS 3000 30 SALES CHICAGO 2850 40 OPERATIONS BOSTON -1 DBA Seoul 40 SALES CHICAGO 50 SALES CHICAGO 1111
7 rows selected. --> 불필요한 로우까지 업데이트를 하는 단점이 있습니다.
SQL> rollback;
Rollback complete.
SQL> update (select a.sal a_sal, b.sal b_sal 2 from dept_test a, (select deptno, max(sal) sal from emp group by deptno) b 3 where a.deptno=b.deptno) 4 set a_sal=b_sal; set a_sal=b_sal * ERROR at line 4: ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다 --> 음..에러가 나네요
SQL> update /*+bypass_ujvc*/(select a.sal a_sal, b.sal b_sal --> 힌트절 사용 2 from dept_test a, (select deptno, max(sal) sal from emp group by deptno) b 3 where a.deptno=b.deptno) 4* set a_sal=b_sal SQL> /
3 rows updated.
SQL> select * from dept_test;
DEPTNO DNAME LOC SAL ---------- ------------------- ----------- ---------- 10 ACCOUNTING NEW YORK 5000 20 RESEARCH DALLAS 3000 30 SALES CHICAGO 2850 40 OPERATIONS BOSTON -1 DBA Seoul 40 SALES CHICAGO 50 SALES CHICAGO 1111
7 rows selected. --> 위 3개 쿼리중 실용성있는 쿼리문이라고 합니다.(그냥 그렇다네요ㅋㅋ)
SQL>
<결론> 가끔 나의 짧은 지식으로 검증도 해보지 않고, 무턱대고 commit을 실행 시키는 실수로 인해서 감당할 수 없는 큰 일이 발생할 수가 있습니다. 다른 것도 꼼꼼히 살펴봐야하겠지만 특히나 update 문은 신중을 기해야 할 것입니다. 검증은 중요한 것이니깐요^^ 위 쿼리문 중 마지막 쿼리문이 가장 낫다고는 하였지만 어느 것 하나 정답은 없다고 생각합니다.
오라클을 설치할 때 기본적으로 깔리는 DB를 수정해 보았습니다.
데이터 파일 이동, 리두로그파일 추가, 아카이브 모드 변경, 풀백업 등등
예전에 배웠던 것들을 조금씩 조금씩 기억해 나가면서..변환을 하였습니다.
좀 길긴하지만 차근차근히 보시면 금방 눈에 쏘옥 들어올겁니다.
참고로 울트라에디트에서 편집을 하였습니다.(그냥 단순히 긁어다가 복사한 것 뿐이지만요~)
파일을 참조 해 주세요.
※ 작업한 내역
1. 데이터 파일 이동
2. temporary temp tablespace 생성 및 삭제.
3. redo logfile 이동 및 member 추가
4. control file 경로 수정 및 폴더 생성, admin 경로에 대한 pfile 수정
- 예전 alert file, trace file 등 전부 이동.
5. pfile 구동후 spfile로 복사
6. 기존 데이터 파일 삭제
7. archive mode 변경
8. index tablespace 생성
9. full backup
오라클 클라이언트를 다시 설치하다보니 Javaw.exe 에서 에러가 발생하는 군요. @@;; 이전 포스팅에 R2 버전은 잘 된다라고 했더니, 여기저기서 안된다는 분들이 있었는데, 아마도 이 문제였나봅니다. 아무리 해봐도 안되어서 @@; Java를 통한 GUI 설치를 포기하고 콘솔에서 무인 자동설치로 설치해 버렸습니다. 일단, 하드디스크 특정 폴더로 오라클 클라이언트 설치 파일들을 모두 옮깁니다. response 폴더의 clientadmin.rsp 파일을 열어 내용을 수정합니다. (clientadmin.rsp는 관리자 모드로 설치하기 위한 스크립트 파일입니다.) #-----------------------------------------------------------------------------
#Name : ORACLE_HOME
#Datatype : String
#Description: Complete path of the Oracle Home.
#Example : ORACLE_HOME = "/product/10.2.0/client"
#-----------------------------------------------------------------------------
ORACLE_HOME="c:\oracle\product\10.2.0\client" #-----------------------------------------------------------------------------
#Name : ORACLE_HOME_NAME
#Datatype : String
#Description: Oracle Home Name. Used in creating folders and services.
#Example : ORACLE_HOME_NAME = "OraClient10ghome1"
#-----------------------------------------------------------------------------
ORACLE_HOME_NAME="OraClient10ghome1" 값은 꼭 위와 같지 않아도 됩니다. ^^ 그런후 명령 프롬프트를 실행한 후 아래의 명령을 입력합니다. >> setup.exe -silent -responsefile c:\임시파일\response\clientadmin.rsp 이쁜 설치 화면은 뜨지 않지만, 잘 ~ 설치 됩니다. ^^
이번에는 Partition Table에 대한 기본적인 관리에 대해서 정리하고자 합니다.
dba_tab_partitions, all_tab_partitions, user_tab_partitions (partition table에 대한 정보가 나와요)
* 파티션 테이블 스페이스 추가. SQL> alter table range_emp add partition range_p4 values less than(5000) tablespace tbs4; SQL> alter table range_emp add partition range_p5 values less than(maxvalue) tablespace tbs5;
위와 같이 추가해 줄수 있으며 maxvalue가 설정이 되었을 경우는 더이상 추가를 해줄수가 없습니다. 그러나 maxvalue 파티션 부분을 분할 해 줄수 있습니다.
* 파티션 테이블 스페이스 분할. SQL> alter table range_emp split partition range_p5 at(6000) 2 into (partition range_p6 tablespace p6, partition range_p5 tablespace p5); -- range_p5 파티션을 range_p6으로 split(분할) 한다.
* 파티션 테이블 스페이스 삭제. SQL> alter table range_emp drop partition range_p5;
* 파티션 테이블 데이터 삭제. SQL> delete from range_emp partition (range_p1); SQL> alter table range_emp truncate partition range_p1;
* 파티션 테이블 스페이스 이름 변경 SQL> alter table range_emp rename partition range_p6 to range_p7;
* 파티션 테이블 스페이스 옮기기 SQL> alter table range_emp move partition range_p7 tablespace tbs7;
위 명령어들을 하나씩 실행시키면서 object를 확인해보면 어떻게 변경이 되는지 이해하실겁니다.
※참고 url
---------------------------------------------------------------------------------- 아래 사항은 너무 오래전에 했던거라 다시한번 해봐야 될것같아 확실히 말을 못하겠습니다. 예전 제가 요약한건데..이해를 못한다는..ㅋㅋㅋㅋ
*partition table의 물리적인 속성 변경하기. alter table part_tbl storage(next 10M); : part_tbl의 모든 partition의 next 값이 변경된다. alter table part_tbl modify partition part_tbl_200805 storage (maxextents 1000); : part_tbl_200805 partition의 maxextents 값만 변경한다.
*파티션 상태 확인 select index_name,partiton_name,high_value,status,tablespace_name from user_ind_partitions;
* partition index rebuild alter index 인덱스이름 rebuild partition 파티션 이름;
*global index rebuild 하기 alter index 인데스이름 rebuild;
파티션 테이블에는 여러가지가 있습니다.그 중 가장 대표적으로 쓰이는 range partition 과 hash partition에 대해서 요약 정리를 하려고 합니다.
<Tablespace 생성>
일단 Partition Table을 생성하기 위해서 Tablespace를 3개만 생성하겠습니다. SQL> create tablespace tbs1 2 datafile 'D:\oracle\product\PROD\Disk5\tbs1.dbf' size 10m 3* autoextend on maxsize 20m; 테이블스페이스가 생성되었습니다. SQL> create tablespace tbs2 2 datafile 'D:\oracle\product\PROD\Disk5\tbs2.dbf' size 10m 3* autoextend on maxsize 20m; 테이블스페이스가 생성되었습니다. SQL> create tablespace tbs3 2 datafile 'D:\oracle\product\PROD\Disk5\tbs3.dbf' size 10m 3* autoextend on maxsize 20m; 테이블스페이스가 생성되었습니다.
Range와 Hash partition 동일에 동일한 데이터를 insert를 하겠습니다.
--partition_insert.sql (hash partition에 insert시 테이블명을 수정해주세요.) insert into range_emp(empno,ename,job,hiredate,sal) values(999,'Ultra','SALESMAN',sysdate,3000); insert into range_emp(empno,ename,job,hiredate,sal) values(1000,'Ultra','SALESMAN',sysdate,3000); insert into range_emp(empno,ename,job,hiredate,sal) values(1500,'Ultra','SALESMAN',sysdate,3000); insert into range_emp(empno,ename,job,hiredate,sal) values(1999,'Ultra','SALESMAN',sysdate,3000); insert into range_emp(empno,ename,job,hiredate,sal) values(2000,'Ultra','SALESMAN',sysdate,3000); insert into range_emp(empno,ename,job,hiredate,sal) values(2001,'Ultra','SALESMAN',sysdate,3000); insert into range_emp(empno,ename,job,hiredate,sal) values(2999,'Ultra','SALESMAN',sysdate,3000); insert into range_emp(empno,ename,job,hiredate,sal) values(3000,'Ultra','SALESMAN',sysdate,3000); insert into range_emp(empno,ename,job,hiredate,sal) values(1999,'Ultra','SALESMAN',sysdate,3000); insert into range_emp(empno,ename,job,hiredate,sal) values(3999,'Ultra','SALESMAN',sysdate,3000);
1. Range Partition 특정 컬럼을 기준(ex. 날짜기준)으로 삼아 데이터를 해당 테이블 스페이스로 분산을 시켜주고, 이로인해 데이터를 관리가 용이해지지만, 기준 컬럼에 의해 데이터들이 불균형 분포되어있거나, 특정 기준값을 삭제한다면 테이블스페이스의 공간관리가 불균형해지고 관리가 힘들어집니다.
<Range Partition Table 만들기>
SQL> create table range_emp 2 (empno number(4) 3 ,ename varchar2(30) 4 ,job varchar2(9) 5 ,mgr number(4) 6 ,hiredate date 7 ,sal number(7,2) 8 ,comm number(7,2) 9 ,deptno number(2)) 10 partition by range(empno) 11 (partition range_p1 values less than(2000) tablespace tbs1 --> 2000미만인 값들 12 ,partition range_p2 values less than(3000) tablespace tbs2 --> 3000미만인 값들 13* ,partition range_p3 values less than(4000) tablespace tbs3); --> 4000미만인 값들 테이블이 생성되었습니다. SQL>@partition_insert.sql
다른 값들은 볼 필요 없고 empno 를 기준으로 partition table을 만들었으니 이 값만 확인해도 되겠죠!
SQL> select empno from range_emp partition(range_p1); EMPNO ---------- 999 1000 1500 1999 1999 SQL> select empno from range_emp partition(range_p2); EMPNO ---------- 2000 2001 2999 SQL> select empno from range_emp partition(range_p3); EMPNO ---------- 3000 3999
위와 같이 데이터가 들어간 것을 확인 할 수 있습니다. 데이터가 불균형하게 들어간 것을 볼 수 있습니다. 테이블 스페이스의 space 관리가 잘못되고 있다는 것입니다.
2. Hash Partition
데이터 관리보다는 성능향상의 목적이 있습니다. 각각 다른 파티션에 데이터를 고르게 분산시킬수가 있습니다. 파티션 테이블 생성시에 반드시 파티션의 개수를 명시해야합니다.(데이터 분산을 위해서) 파티션의 수는 2의 거듭 제곱 수 (즉, 2,4,6,8,16,....)로 설정하는 것이 가장 이상적입니다. 저는 테이블 스페이스를 3개만 만들었으니 3개로만 하겠습니다.
<Hash Partition Table 만들기>
SQL> create table hash_emp 2 (empno number(4) 3 ,ename varchar2(30) 4 ,job varchar2(9) 5 ,mgr number(4) 6 ,hiredate date 7 ,sal number(7,2) 8 ,comm number(7,2) 9 ,deptno number(2)) 10 partition by hash(empno) --자동으로 partition 이름을 만들어준다. 11 partitions 3 12* store in (tbs1, tbs2, tbs3);
or
SQL> create table hash_emp2 2 (empno number(4) 3 ,ename varchar2(30) 4 ,job varchar2(9) 5 ,mgr number(4) 6 ,hiredate date 7 ,sal number(7,2) 8 ,comm number(7,2) 9 ,deptno number(2)) 10 partition by hash(empno) --파티션 이름을 수동으로 지정해 주고 싶을때. 11 (partition hash_p1 tablespace tbs1 12 ,partition hash_p2 tablespace tbs2 13* ,partition hash_p3 tablespace tbs3) 테이블이 생성되었습니다. SQL> @partition_insert.sql
SQL> select empno from hash_emp2 partition (hash_p1); EMPNO ---------- 1000 1999 1999
SQL> select empno from hash_emp2 partition (hash_p2); EMPNO ---------- 999 2000 2999 3000 3999
SQL> select empno from hash_emp2 partition (hash_p3); EMPNO ---------- 1500 2001
데이터가 불규칙하게 각 테이블스페이스에 저장되어있는것을 확인해 볼 수 있습니다. 다음에는 파티션 테이블의 추가,삭제등 기본적인 관리 방법에 대해서 요약 하도록 하겠습니다.
<?xml:namespace prefix = o /><?xml:namespace prefix = o /><?xml:namespace prefix = o /> 오라클의 각종 Limit 값 기준환경은 Linux(32bit 64bit파일)를 사용했을 때를 기준으로 설명되어 있다. AIX, Windows, Mac 에 대해서는 플랫폼 별 매뉴얼을 참조 구분 | 최대값 | 비고 | 스키마 오브젝트 | Column 수, | 1000 개 | 테이블,컬럼명 길이제한 - 30 바이트(30 캐릭터) | Sequence | 38 자리 | | 결합 인덱스 컬럼 수 | l 32 개 컬럼 l 한 BLOCK 80% 이상 차지하면 에러 | BLOCK 사이즈 제한은 일반 컬럼과는 다르게 INDEX 는 row chaining 의 구조를 적용 할 수 없음 | PK 컬럼 수 | l 32개 컬럼 l 한 BLOCK 80% 이상 차지하면 에러 | | 데이터 블록 | 데이터베이스 블록 사이즈 | 2K/4K/8K/16K/( 32K 64bit OS) | | 하나의 데이터파일의 최대 데이터 블록 수 | 2^22 – 1 블록 (4,194,303 개) | 예제) 블록사이즈 4K 이면 약 16 GB 데이터파일 사이즈 8K이면 32 GB, 16K 이면 64GB 128K 이면 128GB | BIGFILE 최대 데이터 블록 수 | 2^32 –1 블록 (4,294,967,295 개) | 예제) 블록사이즈 4K 이면 약 16 TB | EXTENT 최대크기 | 딕셔너리 관리방식 | 4GB * 블록사이즈 | | 로컬 관리 방(uniform으로extent 지정) | 2GB * 블록사이즈 | | MAXEXTENT | 무제한 | | 파일 | Control 파일사이즈 | 블록사이즈 * 20,000 (리커버리 카탈로그를 사용하는 Rman 이 아닌 경우) | | 데이터 파일수 | l 전체- 65,533 개 l 하나의 테이블스페이스- 1,022 개 | 참조 파라미터 DB_FILES | 데이터파일 사이즈 | 4,194,303 * 블록사이즈(UNIX) | Imp/exp –32bit의 경우 2 GB 64bit의 경우–무제한 SQL*LOADER파일 – 32bit의 경우 – 2 GB 64bit의 경우 – 무제한 | SQL문의 제약사항 | SQL문의 크기 | 65,535 Byte (Oracle 9i) 제한없음 (10g) | 미들웨어에 따라서는 32K 제약이 있는 경우도 있다. DBMS_SQL 패키지를 사용해서 최대값 이상 사용가능 | Dynamic SQL (Execute immediate) | 32 KB (Varchar2 의 최대값과 같다) | 이를 초과하면 Ora-06502 에러 발생 | FROM절 | 제한없음 | | WHERE절 | 제한없음 | | IN 리스트 | 1000 개 | | GROUP BY 개수 | 기술하는 그룹핑 컬럼의 사이즈가 1BLOCK 사이즈를 초과하지 못한다. | 매뉴얼상에는 이렇게 기술되어 있지만 실제로는 제한이 없는 것으로 알려져 있다.(확실치않음 ^^;; ) | ORDER BY | 제한없음 | | 표준함수의 최대값 | COALESCE 함수의 인수의 최대값 – 256 개 DECODE 함수의 인수의 최대값 – 255 개 | | PL/SQL 제약사항 | User defined Function의 인수의 개수 | PL/SQL 프로시저의 인수의 최대값은 65,536 | | PL/SQL 의 원시코드 사이즈 | 스토어드프로시저, function, trigger 등의 상한값은 64 KB(일부의 windows에서는32KB) | 트리거 소스코드의 사이즈가 지나치게 길게 되면DBMS_METADATA 실행 시에 에러발생 |
원문 http://www.shift-the-oracle.com/oracle/limits.html
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
---------------------------------- ---------- ------------
BIN$vZBAmE1rSaiCK+8QN9p98g==$0 TABLE : drop 된 table
BIN$JkwChjqZRbex7BzBEETFpQ==$0 TABLE : drop 된 table
HHH TABLE
BBB TABLE
4 개의 행이 선택되었습니다.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------------ -------------- -------------------
AAA BIN$JkwChjqZRbex7BzBEETFpQ==$0 TABLE 2008-11-30:12:13:12
GGG BIN$vZBAmE1rSaiCK+8QN9p98g==$0 TABLE 2008-11-29:21:02:52
SQL> conn /as sysdba
연결되었습니다.
SYSDBA 권한을 가진 계정은 show recyclebin 명령어가 되지 않는다.
SQL> desc dba_recyclebin
(sysdba권한으로 계정에 상관없이 휴지통에 있는 objects를 전부 확인할 수 있음.)
SQL> col owner for a6
SQL> col type for a6
SQL> col original_name for a15
SQL> select owner,original_name,object_name,type,droptime
2 from dba_recyclebin;
OWNER ORIGINAL_NAME OBJECT_NAME TYPE DROPTIME
-------- --------------- ---------------------------------- ------ -------------------
SYSTEM AAA BIN$8+y2KcS+QpCUoZ6Rn9sj6Q==$0 TABLE 2008-11-29:21:02:21
SCOTT GGG BIN$vZBAmE1rSaiCK+8QN9p98g==$0 TABLE 2008-11-29:21:02:52
SCOTT AAA BIN$JkwChjqZRbex7BzBEETFpQ==$0 TABLE 2008-11-30:12:13:12
SQL>
- 복구
SQL> flashback table system.aaa to before drop;
플래시백이 완료되었습니다.
SQL>
- 휴지통 삭제.
SQL> purge recyclebin; - 일반 계정에서 휴지통 비우기
SQL> purge dba_recyclebin; - sys권한으로 모든 휴지통 다 비우기
SQL> purge tablespace example; (해당 tablespace 내의 모든 object를 삭제)
SQL> purge tablespace example user scott;
(scott user에 속하는 example tablespace 내의 object를 삭제)
- 테이블 삭제시 휴지통에 넣지않고 바로 삭제.
SQL> drop table aaa purge;
일반계정은 show recyclebin 으로 지워진 object를 확인하여 복구를 하고,
sys 는 desc dba_recyclebin 으로 확인 후 복구를 할 수 있습니다.
- 휴지통 기능 off
SQL> show parameter recyclebin
NAME TYPE VALUE
-------------------- ------ ---------
recyclebin string ON
SQL> alter system set recyclebin=off;
시스템이 변경되었습니다.
SQL>
Row Migration(행마이그레이션)이란 하나의 레코드는 하나의 블록안에 들어가야하는데
update가 발생하여 레코드값이 커지면서 블록안에 남은 여유공간이 없어 그 레코드가 다른 블록으로 옮겨지게 되는 현상입니다. 행 마이그레이션을 방지하기 위해서 블록에 여유공간을 남겨 놓는데 이것을 PCTFREE 라 합니다. PCTFREE의 필요성을 한번 테스트 해봅시다. 먼저 2개의 테이블 생성. create table aaa
(a number
,b varchar2(20)
,c date default sysdate)
tablespace users
pctfree 0
pctused 30
storage (freelists 2); ======================================= aaa 테이블에 대해 2만건 데이터 insert. begin
for z in 1..20000 loop
insert into aaa values (z,'abcdef',default);
end loop;
end;/ 후 commit; ======================================= aaa 테이블의 통계정보를 수집. anaylze table aaa compute statistics; | create table bbb
(a number
,b varchar2(20)
,c date default sysdate)
tablespace users
pctfree 20
pctused 30
storage (freelists 2); ==================================== bbb 테이블에 대해 2만건 데이터 insert. begin
for z in 1..20000 loop
insert into bbb values (z,'abcdef',default);
end loop;
end;/ 후 commit; ==================================== aaa 테이블의 통계정보를 수집. anaylze table aaa compute statistics; |
cf. PCTUSED : 블록에 있던 데이터가 삭제가 되었을 때 어느 정도의 공간이 남게 된다면 그 블록은 다시 재사용이 가능한데 이것을 PCTUSED라고 하고 기준치를 퍼센트 단위로 설정하게 된다. 기준치를 높게 잡는다면 블록의 재사용률이 낮아질 것이고, 낮게 잡으면 행마이그레이션 일어날 확률이 높아질 것입니다. SQL> select table_name, num_rows, chain_cnt, blocks from user_tables
2 where table_name in ('AAA','BBB'); TABLE_NAME NUM_ROWS CHAIN_CNT BLOCKS
-------------- -------------- ----------- -----------
AAA 20000 0 65
BBB 20000 0 80 현재 사용되어진 블록은 각각 65,80개가 사용되어지고 CHAIN_CNT를 확인하여보니 행마이그레이션은 일어나지 않았습니다. 여기서 각 테이블의 B 컬럼을 업데이트를 해보겠습니다. SQL> update aaa set b='abcdefghijk'; 20000 행이 갱신되었습니다. SQL> update bbb set b='abcdefghijk'; 20000 행이 갱신되었습니다. SQL> commit; 커밋이 완료되었습니다. SQL> analyze table aaa compute statistics; 테이블이 분석되었습니다. SQL> analyze table bbb compute statistics; 테이블이 분석되었습니다. SQL> select table_name, num_rows, chain_cnt, blocks from user_tables
2 where table_name in ('AAA','BBB'); TABLE_NAME NUM_ROWS CHAIN_CNT BLOCKS
-------------- -------------- ----------- -----------
AAA 20000 5539 103
BBB 20000 0 80 SQL> BBB 테이블은 사용되어진 블록의 개수가 변하지 않고 그대로 80개의 블록이 사용되었지만, AAA 테이블은 사용되는 블록이 103개로 늘어나면서 행마이그레이션이 발생했다는 것을 알수있습니. 결과적으로 PCTFREE 를 설정해줌으로써 큰 데이터 값으로 업데이트시에도 행마이그레이션이 발생하지 않았습니다.
행마이그레이션이 발생하지 않기 위해서는 PCTFREE, PCTUSED 에 대한 비율도 잘 맞춰주어서 블록의 빈 공간에 대해서도 효율적으로 관리를 해주어야 할 것 입니다. 이러한 번거로움 때문에 9i부터는 PCTUSED만 자동관리가 된다.
oralce@admin] sqlplus "/as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on 목 11월 20 14:30:34 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. ERROR: ORA-01031: insufficient privileges 사용자명 입력: ERROR: ORA-01017: 사용자명/암호가 부적합, 로그온할 수 없습니다 사용자명 입력: ERROR: ORA-01017: 사용자명/암호가 부적합, 로그온할 수 없습니다 SP2-0157: 3회 시도후 ORACLE에 CONNECT 하지못하고 SQL*Plus을 종료했습니다. oracle@admin] db에 접속을 할 수가 없네요ㅠㅠ oracle@admin]vi %ORACLE_HOME%/network/admin/sqlnet.ora 확인해서 다음과 같이 설정합니다. # SQLNET.AUTHENTICATION_SERVICES = (NTS) /* 윈도우에서는 괜찮지만 리눅스에서는 주석처리를 해줘야합니다. */ NAMES.DIRECTORY PATH=(TNSNAMES, ONAMES, HOSTNAME) 설정 후 다시 접속해보세요!! oracle@admin] sqlplus "/as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Web Nov 19 17:20:26 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance. SQL>
10g 에서 archive mode 설정을 9i와 똑같이 하였다가 다음과 같은 에러를 발생시켰습니다. 제가 archive mode 설정에서 수정한 부분들..
SQL> alter system set log_archive_format='arc%T%TS%S.ARC' scope=spfile;
SQL> alter system set log_archive_start=true scope=spfile;
SQL> shutdown immediate; 데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-19905: log_archive_format must contain %s, %t and %r SQL> 10g에서는 log_archive_start는 사용되지 않고, 파일 format(log_archive_format) 지정은 %t,%r,%s 이 세개가 반드시 들어가야 합니다. 해결 방안..(알고 보면 굉장히 간단한거였는데..너무 헤매고 돌아댕겼습니다.ㅋㅋ) pfile로 startup을 합시다.(spfile만 수정했기 때문에 기존 pfile은 똑같겠죠.) SQL> startup pfile = 'C:\oracle\app\oracle\product\10.2.0\server\database\initXE.ora' ORACLE 인스턴스가 시작되었습니다. Total System Global Area 285212672 bytes
Fixed Size 1287016 bytes
Variable Size 92277912 bytes
Database Buffers 188743680 bytes
Redo Buffers 2904064 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다. SQL> create spfile from pfile; 위와 같이 하신다면 정상적으로 spfile로 구동이 가능할 것입니다. shutdown 후 startup 하시면 확인 가능하겠죠^^ cf. 혹시 pfile도 같이 수정이 되었다면 pfile을 수정 해 보지요. pfile을 text 파일 편집기로 열어봅니다. pfile의 내용 중 #log_archive_start -- 이 부분을 주석처리 합니다.
log_archive_format='arc%T%S%R.ARC' -- format을 %t,%s,%r이 들어가도록 수정합니다. pfile로 startup 한 후 pfile을 spfile로 복사하시면 되겠죠.
C:\> sqlplus "/as sysdba" archive mode로 변경하기 전에 확인 해 봐야 할 것!! SQL> archive log list 데이터베이스 로그 모드 아카이브 모드가 아님
자동 아카이브 사용 안함
아카이브 대상 USE_DB_RECOVERY_FILE_DEST
가장 오래된 온라인 로그 순서 32
현재 로그 순서 33 SQL>show parameter log_archive_max_processes; (활성화 되고 있는 archive process 확인) NAME TYPE VALUE
--------------------------- -------------- -------
log_archive_max_processes integer 2 process를 늘려주고 싶을 때 : alter system set log_archive_max_processes=3; 저는 default 값으로 활성화 시키도록 하겠습니다. 이제 본격적으로 간단한 설정을 해보도록 하겠습니다. archive file 저장 위치를 지정해준다. SQL> alter system set log_archive_dest_1='location=c:\PROD\DISK1\Archive\arch01 reopen'; SQL> alter system set log_archive_dest_2='location=c:\PROD\DISK2\Archive\arch02 reopen'; cf. mandatory : 아카이브하는 작업이 성공적으로 완료되어야 온라인 리두 로그파일을 겹쳐 쓸 수있음 optional : 아카이브하는 작업이 성공적으로 완료되지 않은 경우에도 온라인 리두 로그 파일을 재사용할 수 있음. reopen : 키워드와 함께 숫자를 지정하면, 실패 후 지정된 시간(초)이 경과한 다음 다시 쓰기를 시도함. default = 300, ex) reopen=500 REOPEN을 지정하지 않은 경우 선택 대상에서 발생한 오류는 기록된 다음 무시되고 더 이상 이 대상으로 리두 로그가 보내지지 않는다. 필수 대상에서 오류가 발생하면 아카이브가 성공적으로 수행될 때까지 온라인 리두 로그를 재사용할 수 없다. 저는 process를 2개를 활성화 하였기 때문에 경로를 2개를 지정해주었습니다. 서로 다른 디스크에 저장을 시켜주어야 하지만 여건상 같은 디스크에 넣을수 밖에 없었네요.^^ show parameter log_archive_dest 를 보면 위치가 설정된 것을 확인해 보실수 있습니다. * process를 여러개 지정해주는 이유 1. 대량의 트랙잭션 로드 작업 과 동시에 다른 작업들이 맞물려 wait가 발생되는 archive 이목현상을 방지하기 위해서 2. archive log 파일을 다중화 하여 복구를 대비하기 위하여(archive file도 깨질수있기때문임.) SQL> alter system set log_archive_format='ARC%T%S%R.ARC' scope=spfile; ※ 주의할 점은 9i까지는 %s만 넣어도 상관없었지만 10g부터는 %t%s%r이 반드시 들어가야한다. 참고로 저는 %s%t 만 넣었다가 DB가 startup이 안되서 복구시키느냐구 고생을 좀 하였습니다.^^;; 제가 경험한 에러 및 복구 방안은 Oracle Error Catalog에 추후 등록시키도록 하겠습니다. SQL> shutdown immediate; 데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
SQL> startup mount; ORACLE 인스턴스가 시작되었습니다. Total System Global Area 285212672 bytes
Fixed Size 1287016 bytes
Variable Size 92277912 bytes
Database Buffers 188743680 bytes
Redo Buffers 2904064 bytes
데이터베이스가 마운트되었습니다.
SQL> alter database archivelog; 데이타베이스가 변경되었습니다. SQL> alter database open; 데이타베이스가 변경되었습니다. 이렇게 하면 archive로 설정이 되었습니다. 잘 되었는지 확인 해 봅시다. SQL> archive log list --처음에 확인했던 명령어이죠. 데이터베이스 로그 모드 아카이브 모드
자동 아카이브 사용
아카이브 대상 C:\oraclexe\oradata\Archive\Arch2
가장 오래된 온라인 로그 순서 36
아카이브할 다음 로그 37
현재 로그 순서 37 SQL> alter system archive log current; 시스템이 변경되었습니다. SQL> Archive file이 저장되는 경로에 가서 파일이 생성되었는지 확인해 봅시다. 잘 생성되었다면 archive mode로 이상없이 변경 된 것입니다. 9i보다는 많이 간단해 진 것 같습니다.
또한 log_archive_start는 9i에서는 true로 설정을 해주어야했지만, 10g에서 obsolete parameter 입니다. 설정에 상관없이 true로 인식을 합니다.
--추가글(20100429) 만약 archive process를 2개로 가동시키다가 1개로 줄이고 싶다면 archive모드를 해제한후 다시 설정을 해야한다. (지금까지 내가 알기로는 그렇다.)
Rollup 과 Cube는 9i 부터 생긴 함수 입니다.
Rollup 과 Cube는 8i 부터 생긴 함수 입니다. 그러므로 예전에 8i 때까지 사용하던 union과 비교를 해보시면 쉽게 이해 하실 수 있습니다. 8i와 9i의 쿼리문을 비교해보시면 쉽게 이해하실수 있을 거라 생각되어져 별다른 설명은 첨부하지 않았습니다. HR 계정에서 다음 쿼리문을 차근차근히 살펴보시고 실행을 한번 해보세요. SQL> conn hr/hr (1) Rollup -Ver. 9i- select department_id,job_id,avg(salary),count(*)
from employees
group by rollup(department_id,job_id)
----여기서 하나더 grouping 에 대해서도 함께 알아 보도록 하겠습니다.---- (저도 확실하게 설명해 드릴수는 없지만 아는데까지만요^^;) select department_id,job_id,count(*) cnt,avg(salary) avgsal,grouping(department_id) g1,grouping(job_id) g2
from employees
group by rollup(department_id,job_id) 다음과 같은 결과가 나올 것입니다. DEPARTMENT_ID JOB_ID CNT AVGSAL G1 G2
---------------- ---------- ---------- ---------- ------- -------
10 AD_ASST 1 4400 0 0
10 1 4400 0 1
2 7000 1 1
G1 ,G2 컬럼의 0과 1의 숫자값은 그룹을 묶어 주었는지 여부를 판단할때 쓰입니다. 0은 그룹으로 묶어주었다 1은 그룹으로 묶어주지 않았다 를 의미합니다. G1, G2의 값이 각각 0값인 데이터들을 보시면, 하나는 DEPARTMENT_ID 가 NULL이고, JOB_ID가 SA_REP 인것들을 그룹으로 묶어서 계산되어진거고, 아래를 또 보시면 DEPARTMET_ID가 10이고 JOB_ID가 AD_ASST인 것들을 그룹으로 묶어서 계산되어진거라 보시면 됩니다. 나머지 G1 이 0 이고 G2가 1인 것들은 DEPARTMENT_ID 값이 각각 NULL OR 10인 것들만 묶어서 계산되어졌다고 보시면 됩니다. 이해하셨을지 모르겠네요.ㅋㅋ^^; -Ver. 8i - 8i 때는 어떻게 위 함수를 사용했는지 보시죠~ select department_id,job_id,avg(salary) avgsal,count(*) cnt from employees
group by department_id,job_id
union
select department_id,to_char(null),avg(salary) avgsal,count(*) cnt
from employees
group by department_id
union
select to_number(null),to_char(null),avg(salary) avgsal,count(*) cnt
from employees Tip. 참고로 실행 계획도 겸하여 보신다면 어떤 쿼리문이 효율적인지 금방 알수 있을 겁니다. SQL> set autotrace on exp 아래와 같은 오류가 난다면 utlxplan.sql을 실행 시켜주세요!! SP2-0613: PLAN_TABLE 포맷 또는 존재를 입증할 수 없습니다
SP2-0611: EXPLAIN 레포트를 사용 가능시 오류가 생겼습니다
SQL> @%oracle_home%\rdbms\admin\utlxplan.sql; 테이블이 생성되었습니다. SQL> set autotrace on exp (2) Cube
-Ver. 9i- select department_id,job_id,count(*) cnt,avg(salary) avgsal from employees
group by cube(department_id,job_id) -Ver. 8i- select department_id,job_id,count(*) cnt,avg(salary) avgsal
from employees
group by department_id,job_id
union
select department_id,to_char(null),count(*) cnt,avg(salary) avgsal
from employees
group by department_id
union
select to_number(null),job_id,count(*) cnt,avg(salary) avgsal
from employees
group by job_id
union
select to_number(null),to_char(null),count(*) cnt,avg(salary) avgsal
from employees (3) Grouping set -Ver. 9i- select deptno, job, count(*) cnt, avg(sal) avgsal from emp group by grouping sets(deptno,job) -Ver. 8i- select deptno, null job, count(*) cnt, avg(sal) avgsal from emp group by deptno union select null, job, count(*) cnt, avg(sal) avgsal from emp group by job (4) Quiz 마지막으로 문제 하나 내도록 하겠습니다. 제 친구가 회사에서 풀어보라고 했던 문제인데 풀어보라고 저에게 주더군요. 저도 나름대로 풀어보았습니다. 어거지로 풀긴 했지만요..ㅋㅋㅋ 문제] 아래 표를 보시면 원시 Table을 결과자료 처럼 나오게 하면 되는 겁니다. (어떤 쿼리문을 사용하시던 간에 나오기만 하면 되는거 아닌가요?^^ㅋㅋㅋㅋ) - 제가 한 쿼리문은 쪽지를 보내주시면 보내드리도록 하겠습니다.^^ 참고로 저는 rollup을 사용하였습니다. - 원시 Table - - 결과자료 - | 소관 | 회계 | 계정 | 금액 | | 소관 | 회계 | 계정 | 금액 | | 1 | A | 1 | 100 | | 합계 | | | 1200 | | 1 | A | 1 | 100 | | 1소관합계 | | | 600 | | 1 | B | 1 | 200 | -> | 1소관 | A | 1 | 200 | | 1 | B | 1 | 200 | | | B | 1 | 400 | | 2 | A | 1 | 300 | | 2소관합계 | | | 600 | | 2 | A | 1 | 300 | | 2소관 | A | 1 | 600 |
-- 원시 Table 만들기. 컬럼 이름은 아무거나 해도 될 듯 싶습니다. create table test1
(no number(2)
,a varchar2(2)
,b number(2)
,c number(10))
tablespace users; insert into test1 values(1,'A',1,100);
insert into test1 values(1,'A',1,100);
insert into test1 values(1,'B',1,200);
insert into test1 values(1,'B',1,200);
insert into test1 values(2,'A',1,300);
insert into test1 values(2,'A',1,300);
with grant option 과 with admin option에 대해 테스트를 하기 전에 쉽게 이해하실수 있도록 기본적인 셋팅을 하도록 하겠습니다. C:\> sqlplus "/as sysdba" 1. 사용자 생성(USER_1, USER_2) SQL> create user user_1 identified by user_1 default tablespace users; 사용자가 생성되었습니다. SQL> create user user_2 identified by user_2 default tablespace users; 사용자가 생성되었습니다. 2. 접속 권한 및 간단한 권한 주기 SQL> grant create session, create table, create view to user_1; 권한이 부여되었습니다. SQL> grant create session, create table to user_2; 권한이 부여되었습니다. 이제 with grant option 과 with admin option 을 알아보도록 하겠습니다. (1) with grant option 기본적으로 scott 계정은 다들 가지고 계시니 scott 계정을 들어가보도록 하겠습니다. SQL> conn scott/tiger 연결되었습니다. scott이 user_1에게 emp 테이블을 select 할수 있는 권한을 with grant option을 써서 부여 하도록 합니다. SQL> grant select on emp to user_1 with grant option; 권한이 부여되었습니다. SQL> select * from user_tab_privs
2 where table_name='EMP'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ---------- --------------- --------------- --------------- ---- ---
USER_1 SCOTT EMP SCOTT SELECT YES NO scott에게 받은 권한을 똑같이 user_2에게 부여합니다. SQL> conn user_1/user_1
연결되었습니다.
SQL> grant select on scott.emp to user_2 with grant option; 권한이 부여되었습니다. SQL> select * from user_tab_privs
2 where table_name='EMP'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ---------- --------------- --------------- --------------- ---- ---
USER_1 SCOTT EMP SCOTT SELECT YES NO
USER_2 SCOTT EMP USER_1 SELECT YES NO USER_TAB_PRIVS 테이블은 누구의 소유이며 권한자 및 권한을 부여 받은 자를 확인 할 수 있습니다. 이제 부여한 권한을 다시 회수 할 것입니다. 어떻게 회수가 되는지 확인해 보시길 바랍니다. SQL> conn scott/tiger
연결되었습니다.
SQL> revoke select on emp from user_1; 권한이 취소되었습니다. SQL> conn user_1/user_1;
연결되었습니다.
SQL> select * from user_tab_privs
2 where table_name='EMP'; 선택된 레코드가 없습니다. SQL> conn user_2/user_2;
연결되었습니다.
SQL> select * from user_tab_privs
2 where table_name='EMP'; 선택된 레코드가 없습니다. ※ scott이 user_1의 권한 revoke 하였지만, user_1이 user_2에게 부여한 권한도 같이 회수가 되는 것을 볼 수 있습니다. (2) with admin option SQL> conn /as sysdba 연결되었습니다. SQL> grant alter session to user_1 with admin option; 권한이 부여되었습니다. SQL> select * from dba_sys_privs where grantee like 'USER%'; GRANTEE PRIVILEGE ADM
---------- ------------------- ---
USER_1 CREATE VIEW NO
USER_1 CREATE TABLE NO
USER_1 ALTER SESSION YES
USER_1 CREATE SESSION NO
USER_2 CREATE TABLE NO
USER_2 CREATE SESSION NO 6 개의 행이 선택되었습니다. SQL> conn user_1/user_1
연결되었습니다.
SQL> grant alter session to user_2 with admin option; 권한이 부여되었습니다. SQL> select * from user_sys_privs where username like 'USER%'; USERNAME PRIVILEGE ADM
----------------------- ------------------ ---
USER_1 CREATE VIEW NO
USER_1 CREATE TABLE NO
USER_1 ALTER SESSION YES
USER_1 CREATE SESSION NO SQL> conn user_2/user_2
연결되었습니다.
SQL> select * from user_sys_privs where username like 'USER%'; USERNAME PRIVILEGE ADM
----------------------- ------------------ ---
USER_2 CREATE TABLE NO
USER_2 ALTER SESSION YES
USER_2 CREATE SESSION NO SQL> 위 테이블을 보시면 ADM 컬럼에 YES로 나와있는 것을 볼수 있습니다. 이제 GRANT 권한을 회수한 것처럼 똑같이 해보겠습니다. SQL> conn /as sysdba SQL> revoke alter session from user_1; 권한이 취소되었습니다. SQL> select * from dba_sys_privs where grantee like 'USER%'; GRANTEE PRIVILEGE ADM
---------- --------------- ---
USER_1 CREATE VIEW NO
USER_1 CREATE TABLE NO
USER_1 CREATE SESSION NO
USER_2 CREATE TABLE NO
USER_2 ALTER SESSION YES
USER_2 CREATE SESSION NO ※ dba_sys_privs를 보시면 user_2의 권한은 그대로 남아있고 user_1의 권한 회수 된것을 볼수 있습니다. 결론을 내리자면 with grant option 은 revoke 시 다른사용자에게도 부여한 권한을 같이 회수하지만 with admin option은 특정 사용자의 권한만 회수가 되고 나머지 다른사용자에게 부여된 권한은 회수가 되지 않습니다. 사용된 테이블 : user_tab_privs : dba_sys_privs : user_sys_privs 내용은 길지만 차근차근 따라하신다면 그렇게 긴 내용은 아닙니다. 업무 중에 할려니 힘드네요..ㅋㅋㅋ 감사합니다.
|
DataDictionary_Description.xlsx
20091119_-_PROD_Tuning.sql