반응형
테스트 디비를 백업을 할려고 보니 redo logfile 그룹이 3개라서 불필요한 것 같아 한개 그룹을 삭제하기로 하였습니다.
테스트 디비라고 하여 좀 안일하게 써온터라 데이터파일 용량도 커지고 로그 파일등 정리가 안되어있네요.
테스트 디비라고 무시말고 메인디비 쓰듯이 써야 할 것 같습니다. 소중하게..ㅎㅎ
날 잡아서 정리 좀 해야할 것 같네요.(메인디비 쓰듯이 써야할텐데..ㅡㅡ;;)

SQL>  select a.group#, a.member, b.members, b.status
  2  from v$logfile a, v$log b
  3* where a.group#=b.group#
SQL> /

    GROUP# MEMBER                                              MEMBERS STATUS
---------- ---------------------------------------- ---------- --------
             3  /oracle/oradata/PROD/Disk5/redo03a.log              2  INACTIVE
             2  /oracle/oradata/PROD/Disk4/redo02a.log              2  INACTIVE
             1  /oracle/oradata/PROD/Disk3/redo01a.log              2  CURRENT
             1  /oracle/oradata/PROD/Disk5/redo01b.log              2  CURRENT
             2  /oracle/oradata/PROD/Disk3/redo02b.log              2  INACTIVE
             3  /oracle/oradata/PROD/Disk4/redo03b.log              2  INACTIVE

6 rows selected.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select a.group#, a.member, b.members, b.status
  2  from v$logfile a, v$log b
  3  where a.group#=b.group#;

    GROUP# MEMBER                                              MEMBERS STATUS
---------- ---------------------------------------- ---------- --------
             2  /oracle/oradata/PROD/Disk4/redo02a.log              2  INACTIVE
             1  /oracle/oradata/PROD/Disk3/redo01a.log              2  CURRENT
             1  /oracle/oradata/PROD/Disk5/redo01b.log              2  CURRENT
             2  /oracle/oradata/PROD/Disk3/redo02b.log              2  INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.members, b.status
  2  from v$logfile a, v$log b
  3  where a.group#=b.group#;

    GROUP# MEMBER                                              MEMBERS STATUS
---------- ---------------------------------------- ---------- --------
             2  /oracle/oradata/PROD/Disk4/redo02a.log              2  CURRENT
             1  /oracle/oradata/PROD/Disk3/redo01a.log              2  ACTIVE
             1  /oracle/oradata/PROD/Disk5/redo01b.log              2  ACTIVE
             2  /oracle/oradata/PROD/Disk3/redo02b.log              2  CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select a.group#, a.member, b.members, b.status
  2  from v$logfile a, v$log b
  3  where a.group#=b.group#;

    GROUP# MEMBER                                              MEMBERS STATUS
---------- ---------------------------------------- ---------- --------
             2  /oracle/oradata/PROD/Disk4/redo02a.log              2  INACTIVE
             1  /oracle/oradata/PROD/Disk3/redo01a.log              2  CURRENT
             1  /oracle/oradata/PROD/Disk5/redo01b.log              2  CURRENT
             2  /oracle/oradata/PROD/Disk3/redo02b.log              2  INACTIVE

SQL>
반응형

'Oracle > Oracle Study' 카테고리의 다른 글

오라클 10.2.0.1.0 설치  (0) 2010.09.08
Oracle Analytic Function(분석함수)  (0) 2010.05.24
리두 로그 삭제  (0) 2010.04.07
serveroutput 단순 예  (0) 2010.02.23
SGA 자동관리 전환  (0) 2010.02.17
set echo on & set feedback off  (0) 2010.02.16
반응형
아침에 출근하여 보니 아래와 같은 에러가 났습니다.
ORA-14400: inserted partition key does not map to any partition

엥..이건 분명 데이터가 들어갈 만한 파티션이 없다는 얘기...

즉, 그 데이터에 맞는 파티션이 생성이 안되었다는 건데...음..

기간(Range Partition) 단위로 되어있어서 분명히 월이 넘어간 데이터 때문에 insert가 안된것으로 판단됩니다.
확인해 보았습니다.
select * from dba_tab_partitions where table_name = 'temp_table';

해당 파티션을 생성한 후 이상없이 잘 돌아갔습니다.
반응형

'Oracle > Oracle Error' 카테고리의 다른 글

function 부분 클릭 시 다운현상  (0) 2010.07.02
[kkocxj : pjpCtx]  (0) 2010.04.12
ORA-14400  (0) 2010.04.01
ora-27476, ora-06512  (0) 2010.01.26
Linux Oracle9i Ora-01031:insufficient privileges  (0) 2008.11.19
Ora-32004, Ora-19905:10g Archive mode 설정 후 Error  (0) 2008.10.02
반응형
   http://iotn.co.kr/power/zboard.php?id=9_1_bbs&no=939
최길호 2008-07-04 14:58:56, 조회 : 2,514

출처: http://forums.oracle.com/forums/thread.jspa?messageID=1612957&tstart=0

"WARNING: inbound connection timed out (ORA-3136)"은 client에서 Oracle에 접속시 
SQLNET.INBOUND_CONNECT_TIMEOUT에 기술된 시간에 인증을 받을수 없는 경우에 발생 합니다.

SQLNET.INBOUND_CONNECT_TIMEOUT은 기본적인 값이 60 SEC 이며 정의된 시간에 인증이 않되면
alert log file에 "WARNING: inbound connection timed out (ORA-3136)"를 발생 합니다.

AIX Oracle 10.2.0.2 

alert.log
=================================================
WARNING: inbound connection timed out (ORA-3136)

조치
=================================================
$ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=0

or

lsnrctl
===================================================================================
LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux4U2_DB)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 60        <<<< 60 sec
The command completed successfully

LSNRCTL> set inbound_connect_timeout 0
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux4U2_DB)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 0        <<<< Change Value
The command completed successfully

test
===================================================================================
Linux env not exist sqlnet.ora file
[Linux4U2_DB :/home/gh]$ ls $ORACLE_HOME/network/admin/sqlnet.ora
ls: /oracle/product/10.2/network/admin/sqlnet.ora: No such file or directory

LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux4U2_DB)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully

LSNRCTL> set inbound_connect_timeout 60
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux4U2_DB)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully

LSNRCTL> stop
LSNRCTL> start
LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux4U2_DB)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully

inbound_connect_timeout = 0 [ default ]


최길호
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>
반응형

'Oracle > Oracle Study' 카테고리의 다른 글

Oracle Analytic Function(분석함수)  (0) 2010.05.24
리두 로그 삭제  (0) 2010.04.07
serveroutput 단순 예  (0) 2010.02.23
SGA 자동관리 전환  (0) 2010.02.17
set echo on & set feedback off  (0) 2010.02.16
Data Dictionary  (0) 2010.02.02
반응형
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를 수정하고 좀 고생을 했습니다.
구글에 찾다가가 SGA에 관한 글을 보고 많은 도움이 되었습니다.

사고경위(?)는 이렇습니다. ㅋㅋ
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을 너무 많이 한 것 같습니다.ㅜㅜ
반응형

'Oracle > Oracle Study' 카테고리의 다른 글

리두 로그 삭제  (0) 2010.04.07
serveroutput 단순 예  (0) 2010.02.23
SGA 자동관리 전환  (0) 2010.02.17
set echo on & set feedback off  (0) 2010.02.16
Data Dictionary  (0) 2010.02.02
전역 임시 테이블(temporary table)  (0) 2010.01.29
반응형

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>

반응형

'Oracle > Oracle Study' 카테고리의 다른 글

serveroutput 단순 예  (0) 2010.02.23
SGA 자동관리 전환  (0) 2010.02.17
set echo on & set feedback off  (0) 2010.02.16
Data Dictionary  (0) 2010.02.02
전역 임시 테이블(temporary table)  (0) 2010.01.29
alert file 에서 error 개수 확인 방법  (0) 2010.01.14
반응형
구글링을 하다보면
같은 내용으로 Data Dictionary object 에 대한 설명을 올려놓은 글들이 많이 있었습니다.
개인적으로 정렬도 잘 안되고 보기가 많이 불편하여 엑셀파일로 정리를 하였습니다.
(단순히 긁어다가 넣었을 뿐입니다.ㅋㅋㅋ)
보기에는 충분한 자료와 내용들이지만, 
오래전에 올린 것들이라 좀 더 업그레이드 된 글들을 찾아 볼수가 없어서 아쉽네요!!
제가 만들기에는 턱없이 부족한 스킬이라...^^;;; 글재주도 없고요.ㅎㅎ

그냥 가지고 있다가 필요할때마다 찾아보면 많은 도움이 될 것입니다.
DBA가 기본적(?)으로 알아야하는 것이기 때문에 필히 숙지를 하시는 것도 좋은 방법이 아닐까요?^^

반응형

'Oracle > Oracle Study' 카테고리의 다른 글

SGA 자동관리 전환  (0) 2010.02.17
set echo on & set feedback off  (0) 2010.02.16
Data Dictionary  (0) 2010.02.02
전역 임시 테이블(temporary table)  (0) 2010.01.29
alert file 에서 error 개수 확인 방법  (0) 2010.01.14
number(p,s) : precision, scale ora-01438  (0) 2009.12.10
반응형
[참고문헌 : 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하는데 유용한 정보입니다.

위 내용과 무관하지만 테이블스페이스를 삭제하면 그 안에 데이터도 사라진다는 것 또한 잊지마세요!!
반응형

'Oracle > Oracle Tip' 카테고리의 다른 글

long형 replace  (0) 2011.01.24
LONG과 CLOB 에 대한 데이터 이전.  (0) 2011.01.04
tablespace 생성 스크립트  (0) 2010.04.30
테이블 read only 설정  (0) 2010.01.27
1년치 날짜 생성.  (0) 2010.01.14
유용 update문  (0) 2009.12.09
반응형
어제 회의가 끝나고 회식자리를 가기 위해서 제자리로 돌아와 컴퓨터를 보니 메신저로 아는 분께서 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


이 말을 꼭 전해드리고 싶지만, 이미 지나간 시간이고..너무 간단한 문제였던 터라 
벌써 해결했으리라 생각을 해봅니다. 그리고 정말 죄송합니다.ㅠㅠ
반응형

'Oracle > Oracle Error' 카테고리의 다른 글

[kkocxj : pjpCtx]  (0) 2010.04.12
ORA-14400  (0) 2010.04.01
ora-27476, ora-06512  (0) 2010.01.26
Linux Oracle9i Ora-01031:insufficient privileges  (0) 2008.11.19
Ora-32004, Ora-19905:10g Archive mode 설정 후 Error  (0) 2008.10.02
ORA-01653  (0) 2006.10.20
반응형
[출처] : 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년...
반응형

'Oracle > Oracle Tip' 카테고리의 다른 글

long형 replace  (0) 2011.01.24
LONG과 CLOB 에 대한 데이터 이전.  (0) 2011.01.04
tablespace 생성 스크립트  (0) 2010.04.30
테이블 read only 설정  (0) 2010.01.27
1년치 날짜 생성.  (0) 2010.01.14
유용 update문  (0) 2009.12.09
반응형
처 : [http://ukja.tistory.com/240 욱짜님 블로그]
         평소 자주 찾아보는 욱짜님 블로그에 있는 설명 그대로를 테스트 해본 결과 입니다.

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에 대해서...
  (음.. 이건 오라클에서 정규화를 쓸수있는 함수라고는 알고 있었고, 예전부터 이에 대해 공부를
   해야겠다고 마음만 갖고 있었는데 이 함수가 이곳에 또 나와있어서 하루 빨리 공부를 
   해야하지 않을까라는 생각이 들었습니다.)

추후 위 사항들에 대해서 글을 남기도록 하겠습니다.
반응형
반응형
출처 카페 > ProDBA | jams
원문 http://cafe.naver.com/prodba/15608

일일 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 문은 신중을 기해야 할 것입니다. 검증은 중요한 것이니깐요^^
위 쿼리문 중 마지막 쿼리문이 가장 낫다고는 하였지만 어느 것 하나 정답은 없다고 생각합니다.

반응형

'Oracle > Oracle Tip' 카테고리의 다른 글

long형 replace  (0) 2011.01.24
LONG과 CLOB 에 대한 데이터 이전.  (0) 2011.01.04
tablespace 생성 스크립트  (0) 2010.04.30
테이블 read only 설정  (0) 2010.01.27
1년치 날짜 생성.  (0) 2010.01.14
유용 update문  (0) 2009.12.09
반응형
오라클을 설치할 때 기본적으로 깔리는 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

반응형

'Oracle > Oracle Study' 카테고리의 다른 글

alert file 에서 error 개수 확인 방법  (0) 2010.01.14
number(p,s) : precision, scale ora-01438  (0) 2009.12.10
Oracle Default DB 내 맘대로 바꾸기.  (0) 2009.11.19
Partition Table(2) - Management  (0) 2009.06.17
Partition Table(1) - Create  (0) 2009.06.17
Row Migration  (0) 2008.11.30
반응형
출처 천상태자 | 천상태자
원문

오라클 클라이언트를 다시 설치하다보니

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;

반응형

'Oracle > Oracle Study' 카테고리의 다른 글

number(p,s) : precision, scale ora-01438  (0) 2009.12.10
Oracle Default DB 내 맘대로 바꾸기.  (0) 2009.11.19
Partition Table(2) - Management  (0) 2009.06.17
Partition Table(1) - Create  (0) 2009.06.17
Row Migration  (0) 2008.11.30
10g Archive Mode로 변경  (0) 2008.10.01
반응형
파티션 테이블에는 여러가지가 있습니다.
그 중 가장 대표적으로 쓰이는 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

데이터가 불규칙하게 각 테이블스페이스에 저장되어있는것을 확인해 볼 수 있습니다.
다음에는 파티션 테이블의 추가,삭제등 기본적인 관리 방법에 대해서 요약 하도록 하겠습니다.
반응형

'Oracle > Oracle Study' 카테고리의 다른 글

Oracle Default DB 내 맘대로 바꾸기.  (0) 2009.11.19
Partition Table(2) - Management  (0) 2009.06.17
Partition Table(1) - Create  (0) 2009.06.17
Row Migration  (0) 2008.11.30
10g Archive Mode로 변경  (0) 2008.10.01
Rollup & Cube & Grouping 차이  (0) 2008.05.16
반응형
출처 카페 > ProDBA | 오라킬
원문 http://cafe.naver.com/prodba/9276

<?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 블록

(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만 자동관리가 된다.

반응형

'Oracle > Oracle Study' 카테고리의 다른 글

Partition Table(2) - Management  (0) 2009.06.17
Partition Table(1) - Create  (0) 2009.06.17
Row Migration  (0) 2008.11.30
10g Archive Mode로 변경  (0) 2008.10.01
Rollup & Cube & Grouping 차이  (0) 2008.05.16
with grant option & with admin option 차이  (0) 2008.05.14
반응형

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>

반응형

'Oracle > Oracle Error' 카테고리의 다른 글

ORA-14400  (0) 2010.04.01
ora-27476, ora-06512  (0) 2010.01.26
Linux Oracle9i Ora-01031:insufficient privileges  (0) 2008.11.19
Ora-32004, Ora-19905:10g Archive mode 설정 후 Error  (0) 2008.10.02
ORA-01653  (0) 2006.10.20
ORA-01653  (0) 2006.10.02
반응형

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로 복사하시면 되겠죠.

반응형

'Oracle > Oracle Error' 카테고리의 다른 글

ORA-14400  (0) 2010.04.01
ora-27476, ora-06512  (0) 2010.01.26
Linux Oracle9i Ora-01031:insufficient privileges  (0) 2008.11.19
Ora-32004, Ora-19905:10g Archive mode 설정 후 Error  (0) 2008.10.02
ORA-01653  (0) 2006.10.20
ORA-01653  (0) 2006.10.02
반응형

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모드를 해제한후 다시 설정을 해야한다.

(지금까지 내가 알기로는 그렇다.)

반응형

'Oracle > Oracle Study' 카테고리의 다른 글

Partition Table(1) - Create  (0) 2009.06.17
Row Migration  (0) 2008.11.30
10g Archive Mode로 변경  (0) 2008.10.01
Rollup & Cube & Grouping 차이  (0) 2008.05.16
with grant option & with admin option 차이  (0) 2008.05.14
view에 사용되는 with check option  (0) 2006.10.20
반응형

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 -                           - 결과자료 -

소관회계계정금액소관회계계정금액
1A1100합계  1200
1A1100
  1소관합계  600
1B1200 ->      1소관A1200
1B1200 B1400
2A1300   2소관합계  600
2A1300      2소관A1600

 

-- 원시 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);

반응형

'Oracle > Oracle Study' 카테고리의 다른 글

Row Migration  (0) 2008.11.30
10g Archive Mode로 변경  (0) 2008.10.01
Rollup & Cube & Grouping 차이  (0) 2008.05.16
with grant option & with admin option 차이  (0) 2008.05.14
view에 사용되는 with check option  (0) 2006.10.20
오라클 아스키로 특수문자 업데이트  (0) 2006.10.16
반응형

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

 

내용은 길지만 차근차근 따라하신다면 그렇게 긴 내용은 아닙니다.

업무 중에 할려니 힘드네요..ㅋㅋㅋ

감사합니다.

반응형

'Oracle > Oracle Study' 카테고리의 다른 글

Row Migration  (0) 2008.11.30
10g Archive Mode로 변경  (0) 2008.10.01
Rollup & Cube & Grouping 차이  (0) 2008.05.16
with grant option & with admin option 차이  (0) 2008.05.14
view에 사용되는 with check option  (0) 2006.10.20
오라클 아스키로 특수문자 업데이트  (0) 2006.10.16

+ Recent posts