[MSSQL] *=과 LEFT OUTER JOIN의 차이점..


  LEFT OUTER JOIN 을 사용 했을때 JOIN 절에 조건식을 넣는것과 JOIN 이후 WHERE을 이용해서 조건식을 넣는것의 차이점  및 *=는 전자를 이야기 한다
-------------------------------------------------------------------------------------------------------
출처 : http://blog.naver.com/crabz/140023357168


 MSSQL의 left outer join과 *= 는 같은 표현이 아닙니다. (전혀 다름)

 예를 들어 다음과 같은 테이블이 있습니다.

 select * from A;

YM         EMP       DEPT

200212  1   40

200212  2   40

200212  3   40

200212  4   40

200212  5   40

200212  6   40

200212  7   40

200212  8   40

 

select * from B;

YM         EMP       DEPT

200212  1   40

200212  2   40

200212  3   40

200212  4   40

 

 

 

(1)

select count(*)

from A left outer join  B

on  A.emp = B.emp

where A.ym = '200212'

and B.ym='200212'

and A.dept='40';

 

result : 4

 

(2)

select  count(*)

from A , B

where A.emp *= B.emp

and A.ym = '200212'

and B.ym='200212'

and A.dept='40';

 

result : 8

 

 

직접 돌려보시면 확인 가능하듯이

위의 적은 바와 같이 결과가 다르게 나옵니다.

 

 

많은 사람들이

MSSQL에서

LEFT Outer Join 과 *=는 동의어라고 생각하지만 사실은 그렇지 않습니다.

LEFT Outer Join과 *= 는 의미적으로 다른 사용입니다.

 

MSSQL의 *= 을 사용하지 말라는 권고사항을

단지 과거의 사용형식이기 때문이라고 생각하기 때문에 흘려듣는데..(일부 책에도 그렇게 소개되

기도 했지만..)

 

*=이 소개되었을때 ANSI에서는 Outer 조인에 대하여 어떠한 표준이나 사양이 존재하지 않았습니

다. 이런 문제로 ANSI위원회에서는 Outer 조인의 올바른 의미와 구현원칙을 제정하였고 이후

MSSQL 6.5에서 ANSI OUTER JOIN이 체택되었습니다.

 

즉 *= 는 이전의 MSSQL의 하위버전을 위해 지원하는 이전의 'MSSQL식'의 Outer JOIN 형식입니다.

 

둘의 근본적인 차이는 이렇습니다.

 

1구문의

and B.ym='200212'

는 아웃터 조인이 일어난후에 조건 체크가 되고

 

2구문의

and B.ym='200212'

는 아웃터 조인이 일어나기전에 조건 체크가 됩니다.

 

즉 1구문에서 4개의 행밖에 나오지 않은것은

OUTER JOIN을 한 후에  B.ym='200212' 조건에 의해

B.ym이 Null 인것을 제외하였기 때문입니다.

 

반면에 2구문에서 8개의 행이 나온것은

조인이 일어나기 전에 B테이블의 4건을 선택하는 조건으로

and B.ym='200212'이 이용되고

그 이후에 Outer Join이 실행되었습니다.

 

상황에 따라 다른 조건을 추가로 기입해서 같은 결과가 나오도록 할수 있으나

이를테면

1의 구문을

and (BB.ym = '200212' or BB.ym is null ) 식으로 수정하여야 합니다.

(그러나 이것도 B의 ym 컬럼이 Not Null이 아니라면 같은 결과를 보장할수 없습니다.. )

 

이런 미묘한 차이는 찾기 어려운 버그를 양산시키므로

말 그대로 *= 식의 사용법은 "권장하지 않습니다."

[펌] MSSQL 잠금에 관한 고찰(3) - 교착상태(데드락,DeadLock)에 대하여


원문출처 : http://kuaaan.tistory.com/100
------------------------------------------

DeadLock이란 둘 이상의 세션이 서로 맞물려 차단된 상태를 말한다. DeadLock이 발생하면 영원히 지속되기  때문에 SQL 서버가 자동으로 찾아내어 해제시켜 준다. DeadLock에는 두가지 종류가 있다.

1. 순환 교착 (Cycle Deadlock)
   교착상태를 설명할 때 보통 예로 드는 것이 이 "Cycle DeadLock"이다. 두 세션이 필요한 리소스를 얻기 위해 서로 상대방이 Lock을 풀기를 기다리는 상태라고 설명할 수 있다. 

예를 들면 다음과 같다.
  1. -- 세 션 1  
  2. BEGIN TRAN  
  3.   
  4. UPDATE TAB_A SET COL1 = COL1+2 WHERE PK = 10  
  5.   
  6. WAITFOR DELAY '0:0:5'  
  7.   
  8. UPDATE TAB_B SET COL1 = COL1+2 WHERE PK = 10  
  1. -- 세 션 2 : 세션 1과 연달아서 실행한다.  
  2. BEGIN TRAN  
  3.   
  4. UPDATE TAB_B SET COL1 = COL1+2 WHERE PK = 10  
  5.   
  6. WAITFOR DELAY '0:0:5'  
  7.   
  8. UPDATE TAB_A SET COL1 = COL1+2 WHERE PK = 10  

위와 같이 실행시키면 한쪽 세션에서는 다음과 같은 결과를 볼 수 있다.
(1개 행 적용됨)

서버: 메시지 1205, 수준 13, 상태 50, 줄 1
트랜잭션(프로세스 ID 54)이 lock 리소스에서 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다. 트랜잭션을 다시 실행하십시오.

교착상태란 무엇인가를 직관적으로 알려주는 예이다.

2. 변환 교착 (Conversion DeadLock)
Conversion DeadLock 은 잠금모드가 SharedLock에서 UPD-Lock 혹은 X-Lock으로 전환될 때 발생하는 문제로서, 채번(일련번호 매기는 일)과 관련해서 발생하는 경우가 많다.

1) 세션 A가 트랜젝션을 건 후 어떤 Row에 공유잠금(S-Lock)을 걸었다고 가정해보자
2) 세션 B도 트랜젝션을 건 후 그 Row에 공유잠금을 걸었다. 공유잠금끼리는 서로 호환되니까 당근 가능하다.
3) 이 상태에서 세션 A는 그 Row에 Update를 시도한다. 이 Row에는 세션 B에서 공유잠금을 걸었으므로 세션 A는 배타적잠금을 을 걸기 위해 세션 B가 공유잠금을 풀어주기를 기다린다.
4) 이때, 세션 B도 그 Row에 Update를 시도한다

과연 어떻게 될까? A는 B의 공유잠금때문에 Update를 진행하지 못하고, B는 A의 공유잠금때문에 Update를 진행하지 못하게 된다. 이것이 Conversion DeadLock이다.

실제 테스트를 해보면 다음과 같다.
  1. -- 이 SQL 을 세션 1, 2에서 연달아 실행한다.  
  2. DECLARE @NUM INT  
  3.   
  4. BEGIN TRAN  
  5.   
  6. SELECT @NUM = VAL + 1 FROM TAB1   
  7.   WITH (REPEATABLEREAD)   
  8.   WHERE NUMTYPE = 'TestApp'  
  9.   
  10. WAITFOR DELAY '0:0:5'  
  11.   
  12. UPDATE TAB1 SET VAL = @NUM WHERE NUMTYPE = 'TestApp'  

테스트해보면 데드락이 발생하는 것을 확인할 수 있다.

만약 위의 SELECT문에서 WITH (REPEATABLEREAD) 잠금힌트가 없다면
어떻게 될까?
머릿속으로 생각할 때는 데드락이 발생하는 것이 불가능할 것 같다. 
만약 DB가 엄청나게 바쁜 상황이라면?? 잘 모르겠다. ^^;


위와 같은 SQL이 데드락이 발생하지 않도록 하려면 어떻게 해야 할까?
위의 교착상태는 SELECT시 공유잠금이 동시에 걸렸기 때문에 발생한 것이므로,
SELECT시에 다음과 같이 잠금 힌트를 주어 처음부터
명시적으로 UPDLOCK을 걸어주면 
교착상태를 방지할 수 있다.
SELECT @NUM = VAL + 1 FROM TAB1 WITH (UPDLOCK) WHERE NUMTYPE = 'TestApp'


내가 전에 링크해왔던 글 중에
UPDATE Character SET login_count = login_count + 1 , login_time = GETDATE() WHERE name = @IN_NAME;
위와 같은 SQL이 Conversion DeadLock이 발생하기 쉬우므로
UPDATE Character WITH (UPDLOCK) SET login_count = login_count + 1 , login_time = GETDATE() WHERE name = @IN_NAME;
이렇게 써야 한다는 글이 있었는데, 이것은 잘못된 내용이었다.

UPDATE문 실행시 WHERE 필터가 진행될 때는 해당 Row에 Update Lock이 걸리며, 
실제로 WRITE가 진행될 때 Exclusive Lock으로 전환된 다. 

위의 업데이트문에서는 Shared Lock이 걸리지 않으며 따라서 위의 잠금 힌트는 주나마나 한 것이 된다.

※ 여러 테이블을 Join 하는 경우
WITH (UPDLOCK) 과 같은 힌트는 각 테이블 별로 지정해야 한다.


3. Transaction을 걸지 않았는데도 DeadLock이 발생하는 경우
 가끔은... 아예 BEGIN TRAN이란 문장 자체가 없는데도 데드락이 발생하는 경우가 있다. 이것은 모든 INSERT, UPDATE, DELETE 문장이 실행될 때 명시적으로 TRANSACTION이 지정되지 않을 경우 자동으로 "암시적 트랜잭션"이 생성되기 때문이다. 
즉, 
  1. UPDATE TEST_TAB SET VAL = 1 WHERE PK = 10  
이라는 문장은 실제로는
  1. BEGIN TRAN  
  2. UPDATE TEST_TAB SET VAL = 1 WHERE PK = 10  
  3. COMMIT  
과 같은 식으로 실행되는 것이다.

그렇다면 만약 한 UPDATE 문장 내에서 Shared Lock과 Exclusive Lock이 함께 걸리는 SQL이 있을까?

물론 있다.

  1. -- Select 와 Update가 한 SQL 내에 섞여 있어 Conversion Deadlock에 취약한 SQL  
  2. UPDATE TEST_TAB   
  3.    SET VAL = 1  
  4.    WHERE PK IN (SELECT PK FROM TEST WHERE VAL = 10)  

위와 같은 문장은 SELECT가 실행될 때 S-Lock이 걸린 후 X-Lock으로 전환된다.
실제로 세션1 에서 먼저 TEST_TAB 에 적당한 UPDATE문을 실행하여 테이블 전체에 X-Lock을 건 후 
세션2 에서 위의 Update 문을 실행하고 SP_LOCK으로 세션 2의 잠금상태를 점검해보면 다음과 같이 S-Lock을 먼저 시도하고 있는 것을 확인할 수 있다.

따라서 DB가 바쁠때 위와 같은 문장이 동시다발적으로 실행되면 DeadLock이 발생하게 된다.
(실제로 WHILE 루프로 묶어서 여러 세션에서 돌려보면 테스트 환경에서도 교착상태가 재현된다.)

위와 같이 UPDATE 문 내에 명시적으로 SELECT 서브쿼리가 들어있지 않으면서도 SharedLock을 유발하는  UPDATE 문장들이 있다.
  1. UPDATE UPDATE_TAB  
  2.    SET UPDATE_TAB.MANAGE = 1  
  3.    FROM UPDATE_TAB, UPDATE_TAB A  
  4.    WHERE A.SEQ = UPDATE_TAB.SEQ AND A.MANAGE = 1  

위와 같은 JOIN UPDATE문 역시 데드락에 취약하다.

교착상태를 방지하기 위해 가급적이면 한 SQL 내에 UPDATE문장과 SELECT 문장을 섞어 쓰는 것을 피해야 한다.



4. 교착상태 탐지방법 : 추적플래그 1204 
DeadLock이 발생했을 때 그것을 추적하여 원인을 확인하는 여러가지 방법이 있지만 가장 좋은 방법은 "추적플래그 1204"를 설정하는 것이다. 자세한 내용은 여기를 참조한다.



5. DeadLock을 줄이는 5가지 방법
DeadLock을 줄이기 위해서는 다음과 같은 방법들이 있다.

1) 인덱스를 설정한다. 인덱스가 없으면 Lock이 걸리는 범위가 훨씬 넓어지기 때문에 교착상태가 발생하기 쉬워진다.
2) 자원들을 한쪽으로 사용한다. A와 B라는 테이블이 있다면 모든 세션에서 A->B 순서로 사용하도록 한다.
3) 트랜잭션은 가급적이면 짧게 만든다.
4) 테이블의 크기를 작게 쪼갠다. (정규화)
5) Transaction Isolation Level을 "Read Uncommitted"로 설정한다.

[펌] MSSQL 잠금에 관한 고찰(2) - 격리 수준(Transaction Isolation Level)에 대하여


원문출처 : http://kuaaan.tistory.com/98
-----------------------------------------

격리 수준(Transaction Isolation Level)이란 SQL Server에서 잠금(Lock)을 어떤 식으로 적용할 것인가에 관한 얘기이다. 주로 SELECT시의 Lock을 어떻게 걸 것이냐에 따라 격리 수준이 구분지어진다.

1. READ COMMITTED
  글자 그대로 해석하면 "커밋된 데이터만 읽을 수 있는" 격리수준이다. 
  SQL Server의 기본 격리수준으로서 SELECT 실행시 공유잠금을 건다. 이 격리수준에서는 SELECT를 시도하려는 DATA에 다른 트랜잭션에서 업데이트를 진행한 경우, 배타적 잠금(X-Lock)이 걸린 데이터에 공유잠금(S-Lock)을 걸려고 시도하므로 업데이트의 트랜잭션이 종료될 때까지 SELECT는 Block된다. Block된 SELECT는 트랜잭션이 종료되면 자동으로 실행된다.
  예를 들어 다음과 같은 SQL을 순서대로 실행시킬 경우, 세션 2는 블럭된다.
  1. -- 세 션 1  
  2. BEGIN TRAN  
  3. UPDATE TABLENAME SET VAL=1 WHERE PK = 10  
  1. -- 세 션 2  : 위의 SQL 실행 후 실행  
  2. SELECT * FROM TABLENAME WHERE  PK = 10 -- 블럭된다!!!  



2. READ UNCOMMITTED
  말 그대로 "커밋되지 않은 데이터도 읽을 수 있는" 격리수준이다.
  업데이트가 커밋되지 않은 데이터에는 배타적 잠금(X-Lock)이 걸려있다. 배타적 잠금이 걸린 데이터를 SELECT할 수 있다는 것은 SELECT시 어떠한 잠금도 걸지 않는다는 것을 의미한다.
업데이트되지 않은 데이터를 읽을 수 있다는 것은 무결성을 깨트릴 위험성이 있다는 의미이다. 예를 들어 SELECT한 결과가 실제로 DB에 INSERT되지 않고 ROLLBACK될 경우, 존재하지 않는 DATA를 SELECT한 결과가 된다. 하지만 그런 경우가 매우 적고, READ UNCOMMITTED 수준에서는 동시성이 향상되는 효과가 있기 때문에 자주 사용되는 수준이다.

  예를 들어 다음과 같은 SQL을 순서대로 실행시킬 경우, 세션 2에서는 1 값이 SELECT된다..
  1. -- 세 션 1  
  2. UPDATE TABLENAME SET VAL=2 WHERE PK = 10  
  3.   
  4. BEGIN TRAN  
  5. UPDATE TABLENAME SET VAL=1 WHERE PK = 10  
  1. -- 세 션 2  : 위의 SQL 실행 후 실행  
  2. SELECT * FROM TABLENAME WITH (READUNCOMMITTED) WHERE  PK = 10 -- 1 값 이 SELECT된다.  

READ UNCOMMITTED 격리수준은 다음과 같이 설정할 수 있다.
  1. SELECT * FROM TABLE1 WITH (READUNCOMMITTED) WHERE ... -- 해 당 SELECT문장에만 적용  
  2. SELECT * FROM TABLE1 WITH (NOLOCK) WHERE ... -- 해당 SELECT문장에만 적용  
  3.   
  4. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- 해당 세션에 적용  



3. REPEATABLE READ
  직역하면 "반복가능한 읽기" 정도?
  REPEATABLE READ 수준에서는 동일 트랜잭션 내에서 한번 SELECT했던 ROW에 대해서는 UPDATE나 DELETE가 불가능하며 트랜잭션 종료시까지 다시 SELECT했을 때 같은 값이 SELECT된다는 것이 보장된다.
  내부적으로는 SELECT했을 때 해당 ROW에 걸리는 S-LOCK이 즉시 해제되지 않고 트랜잭션 종료시까지 유지된다는 것을 의미한다.

다음의 예를 보자
  1. -- 세 션 1  
  2. BEGIN TRAN  
  3. SELECT VAL FROM TABLENAME WITH (REPEATABLEREAD) WHERE  PK = 10 -- VAL 의 값이 1인 RECORD 1개가 출력되었다고 치자.  
  1. -- 세 션 2  : 위의 SQL 실행 후 실행  
  2. UPDATE TABLENAME SET VAL = 2 WHERE PK = 10 -- 세션1 트랜잭션 종료시까지 BLOCK된다.  

이 상태에서 세션 1에서 다시 SELECT해본다
  1. -- 세 션 1  
  2. SELECT VAL FROM TABLENAME WITH (REPEATABLEREAD) WHERE  PK = 10 -- 조 금 전과 동일하게 1 값이 1개 출력된다.  

그러나 이 격리수준은 SELECT된 ROW의 데이터에 UPDATE할 수 없다는 의미이지, 이 테이블에 INSERT 까지 차단된다는 의미는 아니어서 SELECT 했던 행의 값들은 동일하게 유지되지만, SELECT의 결과가 항상 동일하다는 의미는 아니다. (즉 SELECT 결과 RecordSet의 변경은 되지 않지만 추가는 발생할 수 있다.)
다음과 같이 해보자.
  1. -- 세 션 1  
  2. BEGIN TRAN  
  3. SELECT VAL FROM TABLENAME WITH (REPEATABLEREAD) WHERE  VAL = 1 -- VAL 의 값이 1인 RECORD 1개가 출력  
  1. -- 세 션 2  : 위의 SQL 실행 후 실행  
  2. INSERT INTO TABLENAME (PK, VAL) VALUES (11, 1) -- 잘 실행된다.  

이 상태에서 세션 1에서 다시 SELECT해본다.  
  1. -- 세 션 1  
  2. SELECT VAL FROM TABLENAME WITH (REPEATABLEREAD) WHERE  VAL = 1 -- VAL 값 이 1인 레코드가 2개 출력된다.  
SELECT 결과가 동일하게 보장되지는 않음을 확인할 수 있다.

REPEATABLE READ 격리수준은 다음과 같이 설정할 수 있다.
  1. SELECT * FROM TABLE1 WITH (REPEATABLEREAD) WHERE ... -- 해 당 SELECT문장에만 적용  
  2.   
  3. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- 해당 세션 에 적용  



4. SERIALIZABLE
  우리말로는 "직렬화"이다. 직렬화란 한줄로 서서 순서대로 처리되는 상황을 의미하며 동시성은 최악이 된다.
  이 경우에는 SELECT 했을 때 SELECT된 ROW에 RANGED S-LOCK이 걸리고 트랜잭션 종료시까지 유지되기 때문에, 트랜잭션 종료시까지 해당 범위에 대해서 일체의 UPDATE, INSERT, DELETE가 차단되고, 따라서 동일한 SELECT 결과 재현이 보장된 다. 이것은 SQL Server에서 지원하는 가장 높은 격리수준이며 가장 높은 데이터 무결성이 제공되지만, 성능은 최악이다. COM+의 기본 수준이라고 한다.
다음의 예를 보자
  1. -- 세 션 1  
  2. BEGIN TRAN  
  3.   
  4. SELECT * FROM TABLENAME   
  5. WITH (SERIALIZABLE)  
  6. WHERE VAL < 5 -- VAL값이 5이하인 RECORD에 RANGED S-LOCK이 걸린다.  
  1. -- 세 션 2  : 위의 SQL 실행 후 실행  
  2. INSERT INTO TABLENAME (PK, VAL) VALUES (11, 1) -- 이 SQL은 BLOCK된다!!  
  3. INSERT INTO TABLENAME (PK, VAL) VALUES (12, 7) -- 이 SQL 은 잘 실행된다.  
첫번째 INSERT문이 BLOCK되는 것은 INSERT하려는 VAL값이 세션1에서 RANGE LOCK을 건 범위에 포함되기 때문이다. 그러나 두번째 INSERT문은 RANGE LOCK의 범위 밖의 값이므로 INSERT가 가능하다.

여기서 RANGE-LOCK이 걸리는 것은 해당 컬럼에 인덱스가 있을 경우에 한한다. 위의 예제에서 VAL 컬럼에 인덱스가 없다면 테이블 전체에 LOCK이 걸리기 때문에 첫번째 INSERT문은 물론이고 두번째 INSERT문도 BLOCK된다.

VAL 컬럼에 인덱스가 있을 경우 위의 SELECT를 실행한 후 SP_LOCK 프로시져를 실행해 LOCK 상태를 살펴보면 아래와 같다.

위의 이미지를 보면 Range Lock (RangeS-S)이 걸려있는 것을 볼 수 있다.

이 상태에서 인덱스만 삭제한 후 동일한 SELECT문을 실행시키면 다음과 같이 된다.

전체 테이블에 S-Lock이 걸린 것을 볼 수 있다. (인덱스의 유무는 잠금의 크기에 이정도로 큰 영향을 준다!!!)

SERIALIZABLE 격리수준은 다음과 같이 설정할 수 있다.
  1. SELECT * FROM TABLE1 WITH (SERIALIZABLEWHERE ... -- 해당 SELECT문장에만 적용  
  2.   
  3. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- 해 당 세션에 적용 

[펌] MSSQL 잠금에 관한 고찰(1) - 잠금(Lock) 매커니즘에 대하여


원문 출처 : http://kuaaan.tistory.com/97
-----------------------------------------------

  데이터의 무결성을 보장하기 위해 SQL Server에서는 데이터에 Lock을 건다. 사흘에 걸쳐 책을 읽고 테스트를 하면서 공부한 끝에 이 Lock의 매커니즘을 어느정도 이해할 수 있게 되었다. 이에 이해한 내용을 정리해보고자 한다.

1. 잠금(Lock)의 개념

  데이터에 잠금(Lock)을 건다고 하면 언뜻 생각하기엔 데이터가 들어있는 방에 들어가지 못하게 방문을 걸어 잠근다는 느낌이 들지만, 사실은 방문에 "이 방에는 U-Lock이 걸려있음" 이라고 써 붙이는 개념에 가깝다.
  그 방에 누군가 SELECT를 시도한다면, 시도하는 사람은 그 방에 또 "S-Lock이 걸려있음"이라고 써붙이게 되는데 이때 이전에 걸려있던 "U-Lock"과 지금 걸려고 시도하는 "S-Lock"의 호환성을 비교하게 된다. 다행히도 "U-Lock"은 "S-Lock"과 호환되므로 "S-Lock"을 걸 수 있게 되고, SELECT에 성공하게 된다.
  만약 누군가가 그 방에 UPDATE를 시도한다면 그 사람은 그 방에 U-Lock을 걸려고 시도하겠지만 U-Lock은 U-Lock과 호환되지 않으므로 Lock을 걸지 못하고 기다리게 된다. 말하자면 이 Update시도는 기존 트랜잭션이 끝날때까지 "Block"되게 되는 것이다.

  걸어잠그는 것과 걸어잠갔다고 써붙이는 것이 무슨 차이가 있냐고?

  이 방에 들어있는 Data에 접근할 때는 서로간의 약속이 되어 있다. 예를 들면 "Select를 시도할 때는 S-Lock을 걸어야 한다"라던지... "X-Lock이 걸려있을 때는 S-Lock을 걸 수 없다"라던지... 뭐 이런 약속들이다. 그런데 만약 이 약속을 어기는 사람이 있다면 어떻게 될까? 위에서 언급했듯이 실제로 방 문을 걸어잠그는 것이 아니기 때문에 약속을 무시하는 사람은 방에 마음대로 들어갈 수 있게 된다. 예를 들어서... 이미 X-Lock이 걸려있는 Row에

  1. SELECT * FROM TEST_TAB WHERE ID=3  
을 시도하면 BLOCK되겠지만
  1. SELECT * FROM TEST_TAB WITH (READUNCOMMITTED) WHERE ID=3  
를 시도하면 Data를 읽을 수 있게 된다. (WITH (READUNCOMMITTED) 라는 잠금 힌트는 Select할 때 S-Lock을 걸지 말라는 의미이다.)

현재 걸려있는 Lock을 확인하려면


을 실행하면 된다. 만약 특정 세션의 Lock 상태를 확인하려면 세션 ID를 인자로 주면 된다.
  1. EXEC SP_LOCK @@spid  

2. 잠금의 세기
  잠금의 "세기" 라는 것은 여러가지 종류의 Lock들 간의 "호환 관계"라고 정리할 수 있다. S-Lock과 U-Lock이 "호환된다"라 함은 "S-Lock"이 걸려있는 상태에서 제 3의 세션에서 그 데이터에 "U-Lock"을 걸 수 있다는 의미이다.

2.1 공유 잠금 (Shared-Lock, S-Lock)
  공유잠금은 가장 낮은 강도의 잠금으로서, 일반적으로 Select를 할 때 공유잠금이 발생하며, Select가 완료되는 즉시 공유잠금은 해제된다. (트랜잭션이 완료되기 전이더라도 Select 완료 시점에서 잠금이 해제된다는 것이 중요하다.) 공유잠금은 서로 다른 공유잠금과 호환된다. 이 말은 바로 동일한 데이터를 서로 다른 세션에서 동시에 Select할 수 있다는 의미이다. 반면에 공유 잠금은 배타적잠금과는 호환되지 않는데 이 의미는 다른 트랜잭션에서 Update를 수행한 레코드(Uncommitted Data)에 대해 Select를 할 수 없다는 의미로 해석하면 된다.

2.2 배타적 잠금 (Exclusive-Lock, X-Lock)
  배타적잠금은 가장 높은 강도의 잠금으로서, Update가 행해진 시점부터 그 트랜잭션이 Commit될 때까지 배타적 잠금이 걸린다. 배타적 잠금은 다른 모든 종류의 잠금과 호환되지 않는다. 이 의미는 어떠한 약한 잠금이라도 걸려있는 레코드에 대해서는 Update가 불가능하며, 반대로 Update가 진행중인 레코드에 대해서는 Select를 포함한 어떠한 작업도 불가능하다는 의미가 된다.

2.3 업데이트 잠금 (Update-Lock, U-Lock)
  업데이트잠금은 공유잠금과 배타적잠금의 중간 강도의 잠금이다. 공유잠금과는 호환되지만 다른 업데이트잠금이나 배타적 잠금과는 호환되지 않는다. 일반적으로는 Update의 Filter(Where절)가 수행되는 단계에서 업데이트 잠금이 걸리며, Filter된 결과에 대해 실제로 Update를 시도할 때 업데이트잠금은 배타적 잠금으로 전환된다. (만약 테이블에 인덱스가 없거나 Where절이 인덱스를 탈 수 없게 되어 있다면, 테이블을 풀스캔하면서 모든 레코드에 업데이트잠금을 걸 것이다.)
  업데이트잠금은 잠금힌트를 통해 업데이트문이 아닌 Select문에도 걸 수 있다. 보통 컨버젼 데드락을 방지하기 위해 Select문에 업데이트 잠금을 거는 경우가 많다.
  1. SELECT ColA, ColB FROM TAB_NAME WITH (UPDLOCK) WHERE ColA = 'AA'  


3. 잠금의 크기
3.1 잠금의 크기
  잠금의 크기라 함은 어느 정도의 범위를 잠글 것인가에 관한 이야기이다. 대체로 Row Lock (Key Lock), Page Lock, Table Lock 정도가 있다.
  "잠금 비용"이란 잠금을 거는 과정에서 발생하는 성능 손실을 말한다. 만약 Lock을 걸어야 할 페이지가 너무 많다면, 차라리 Table 전체에 Lock을 거는 것이 "잠금 비용"이 훨씬 낮을 것이다.
  "동시성 비용"이란 잠금을 걸면서 동시성이 낮아져서 발생하는 성능 손실을 의미한다. Page Lock 여러개를 Table 하나로 대체하였다면 "잠금 비용"은 낮아지겠지만 대신 "동시성 비용"은 높아질 것이다.
  SQL Server는 "잠금 비용"과 "동시성 비용" 간에 균형을 적절히 고려하여 잠금의 범위를 결정하게 된다. 일반적으로 약 40%의 페이지에 Lock을 걸어야 한다면 테이블 Lock으로 대체된다고 한다
  잠금의 크기를 줄이기 위해서는 적절한 인덱스를 사용하는 것이 중요한데, 인덱스를 타지 못하여 Table Full Scan이 발생한다면 업데이트할 데이터를 찾는 과정에서 테이블 전체에 Lock을 걸게 되기 때문이다.

3.2 내재된 잠금 (Intent-Lock)
  내재된 잠금은 앞의 세가지 잠금과는 약간 다른 차원의 이야기이다. 내재된 잠금을 이해하기 위해서는 "잠금의 크기"를 먼저 이해해야 한다. 
  만약 세션1에서 어떤 Row에 잠금을 걸었다고 가정하자. 그 상태에서 세션2에서 그 Row가 속한 테이블 전체에 테이블잠금을 걸려고 시도하면 어떻게 될까? 당연히 테이블잠금이 걸리면 안된다. (만약 테이블락을 걸 수 있다면 세션1은 레코드잠금을 건 상태에서 다음 작업을 못한 채 꼼짝도 못하게 될 것이다. )
  그렇다면, 세션2는 어떻게 이 테이블의 Row 중 하나에 락이 걸려있다는 것을 알 수 있을까? 테이블에 락을 걸기 전에 모든 페이지와 모든 Row를 다 조사해야 할까? 실제로 SQL Server는 그 반대로 구현되어 있다. 즉, 세션 1이 그 Row에 업데이트 잠금을 걸 때, 해당 레코드가 속한 상위 페이지와 상위 테이블에 내재된 잠금을 함께 걸게 된다. 이렇게 함으로서 세션 2는 테이블에 락을 걸기 전에 해당 테이블만 확인해보면 락을 걸어도 될지를 결정할 수 있게 된다.
내재된 잠금은 IX, IU 와 같이 표기한다. (Intent Exclusive Lock, Intent Update Lock)

다음과 같이 테스트해보자.
  1. BEGIN TRAN  
  2.   
  3. UPDATE test  
  4. SET val = 2  
  5. WHERE pk = 100  
  6.   
  7. EXEC SP_LOCK @@spid  
SP_LOCK 프로시져로 살펴보았을 때 내재된 잠금은 다음과 같이 확인된다.

위의 결과는 52번 세션에서 인덱스(KEY)에 배타적잠금(Mode = X)를, 그것이 속한 페이지와 테이블에는 내재된 배타적잠금을 (Mode = IX)를 걸었다는 의미가 되겠다.


4. 잠금의 호환성 (Lock Compatibility)
  위의 잠금들 간의 호환 관계는 다음과 같은 간단한 테이블로 정리된다.
  위에서 언급한 바와 같이 Update Lock과 Shared Lock이 호환된다 함은 Update Lock이 걸려있는 페이지에 Shared Lock을 또 걸수 있다는 의미가 된다.

Requested mode

Existing granted mode

IS

S

U

IX

SIX

X

Intent shared (IS)

Yes

Yes

Yes

Yes

Yes

No

Shared (S)

Yes

Yes

Yes

No

No

No

Update (U)

Yes

Yes

No

No

No

No

Intent exclusive (IX)

Yes

No

No

Yes

No

No

Shared with intent exclusive (SIX)

Yes

No

No

No

No

No

Exclusive (X)

No

No

No

No

No

No




5. 잠금의 길이
  잠금의 길이란 잠금이 지속되는 시간을 의미한다. 
  일반적인 공유잠금(S-Lock)은 SELECT 문이 끝나면 자동으로 풀린다. 반면에 단독잠금(X-Lock)과 업데이트잠금(UPD-Lock)은 트랜잭션 종료시까지 지속된다.
  공유잠금(S-Lock)의 길이는 경우에 따라 달라질 수 있어, 격리 수준이 Serializable이나 Repeatable Read인 경우 S-Lock도 트랜잭션 종료시까지 지속된다.

MSSQL2005 외부접속, 외부연결 방법


SQL2005를 설치하면 SQL SERVER 노출영역 구성이 보일겁니다.( 시작 - 프로그램 - SQL SERVER 2005 - 구성도구 )


실행하면 위의 화면이 나옵니다. 빨간네모를 클릭해주세요.


그럼 위와 같은 화면이 나옵니다. 이 화면에서 TCP/IP만 사용으로 합니다. 그런 후 확인 누르고 창을
닫습니다.



시작 - 프로그램 - SQL SERVER 2005 - 구성 에 보면 구성관리자라고 있을 겁니다. 그걸 실행해주면 위의 화면이 나옵니다. TCP/IP를 더블클릭해주세요 아래의 화면이 나옵니다.



IP주소로 탭을 변경 해 주시고 아래와 같이 원하는 포트(기본은 1433)로 변경해줍니다. 저같은 경우는 사용을 꼭 "예"로 안해도 외부 접속이 되더군요. 혹시 안되시는분은 사용을 "예"로 해보시길 바랍니다.

미지막으로 SQLSERVER를 다시 시작해줍니다. netstat -an으로 34567번 포트가 리스닝 상태인지 확인 해 봅니다.

[MSSQL] BAK 파일을 이용한 DB 복원


-- BAK 파일에서 mdf, ldf와 정보를 볼수 있다.
RESTORE FILELISTONLY
FROM DISK = 'd:\201004270200.BAK' --백업파일위치

RESTORE DATABASE up
FROM DISK = '201004270200.BAK'
WITH MOVE 'backup_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\backup.mdf', --데이터파일
-- LogicalName(데이터) --저장할 경로
MOVE 'backup_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\backup.ldf' --로그파일
-- LogicalName(로그) --저장할 경로

위의 자료 참고~

[오라클] 에러 메세지와 에러의 이유


http://mis3nt.gnu.ac.kr/PublicData/Oracle11gDoc/server.111/b28278/e0.htm

[MSSQL] 2000, 2005 등 에서 BAK로 복원 하기


출처 : http://mcpicdtl.blogspot.com/2009/12/mssql-restore-bak-mssql-2000-2005-2008.html


실행 전 주의 사항 -3. 복원의 DB명은 미리 생성하지 말것(Unique DB Name)

1. 확인1
RESTORE HEADERONLY FROM DISK = '(A)backup file path and file name'
2. 확인2
RESTORE FILELISTONLY FROM DISK = '(A)backup file path and file name'

3. 복원
RESTORE DATABASE DB명(확인1에 DatabaseName컬럼)
FROM DISK = '(A)backup file path and file name'
WITH MOVE '(C)논리명(확인2에 logicalname)' TO '(E)D:\DATA파일경로\파일명.MDF',
MOVE '(D)논리명(확인2에 logicalname)' TO '(F)D:\LOG파일경로\파일명.LDF'

(A)backup file path and file name
--> 백업 파일의 원본 위치
(B)DBName
--> 유일한 이름의 디피명 (먼저 복원할 디피명을 생성하지 말것)
(C)logical DATA name
--> 확인2의 logicalname컬럼(DATA)을 입력하면 됨(복수일시 Move절 수만큼 반복해서 기입)
(D)logical LOG name
--> 확인2의 logicalname컬럼(LOG)을 입력하면 됨(복수일시 Move절 수만큼 반복해서 기입)
Statement')
(E)D:\DATA Path\DATA File.MDF
--> 저장하길 원하는 경로및 파일명(자동생성됨)
(F)D:\DATA Path\DATA File.LDF'
--> 저장하길 원하는 경로및 파일명(자동생성됨)

결론 : 걍 엔터프라이즈 관리자에서 하면 된다 --;;

[MSSQL] 테이블 데이터 인서트문으로 백업 받기(펌)


출처 : http://sqler.pe.kr/web_board/view_list.asp?id=664&read=3618&pagec=&found=is&part=myboard7&ser=yes

=====================================================================================================



if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[sp_generate_insert_script]')
and objectproperty(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_generate_insert_script]
go

create procedure sp_generate_insert_script
@tablename_mask sysname = NULL
as
begin

--------------------------------------------------------------------------------

-- Stored Procedure: sp_generate_insert_script
-- Language: Microsoft Transact SQL (7.0)
-- Author: Inez Boone (inez.boone@xs4al.nl)
-- working on the Sybase version of & thanks to:
-- Reinoud van Leeuwen (reinoud@xs4all.nl)
-- Version: 1.4
-- Date: December 6th, 2000
-- Description: This stored procedure generates an SQL script to fill the
-- tables in the database with their current content.
-- Parameters: IN: @tablename_mask : mask for tablenames
-- History: 1.0 October 3rd 1998 Reinoud van Leeuwen
-- first version for Sybase
-- 1.1 October 7th 1998 Reinoud van Leeuwen
-- added limited support for text fields; the first 252
-- characters are selected.
-- 1.2 October 13th 1998 Reinoud van Leeuwen
-- added support for user-defined datatypes
-- 1.3 August 4 2000 Inez Boone
-- version for Microsoft SQL Server 7.0
-- use dynamic SQL, no intermediate script
-- 1.4 December 12 2000 Inez Boone
-- handles quotes in strings, handles identity columns
-- 1.5 December 21 2000 Inez Boone
-- Output sorted alphabetically to assist db compares,
-- skips timestamps
-- 1.6 June 10 2005 Beatchoi@yahoo.co.kr
-- added support for reserver keyword
--------------------------------------------------------------------------------

-- NOTE: If, when executing in the Query Analyzer, the result is truncated, you can remedy
-- this by choosing Query / Current Connection Options, choosing the Advanced tab and
-- adjusting the value of 'Maximum characters per column'.
-- Unchecking 'Print headers' will get rid of the line of dashes.


declare @tablename varchar (128)
declare @tablename_max varchar (128)
declare @tableid int
declare @columncount numeric (7,0)
declare @columncount_max numeric (7,0)
declare @columnname varchar (30)
declare @columntype int
declare @string varchar (30)
declare @leftpart varchar (8000) /* 8000 is the longest string SQLSrv7 can EXECUTE */
declare @rightpart varchar (8000) /* without having to resort to concatenation */
declare @hasident int


set nocount on

-- take ALL tables when no mask is given (!)

if (@tablename_mask is NULL)
begin
select @tablename_mask = '%'
end


-- create table columninfo now, because it will be used several times

create table #columninfo (num numeric (7,0) identity,name varchar(30),usertype smallint)

select name,id into #tablenames
from sysobjects
where type in ('U' ,'S')
and name like @tablename_mask

-- loop through the table #tablenames

select @tablename_max = MAX (name),@tablename = MIN (name)
from #tablenames

while @tablename <= @tablename_max
begin
select @tableid = id
from #tablenames
where name = @tablename

if (@@rowcount <> 0)
begin
-- Find out whether the table contains an identity column
select @hasident = max( status & 0x80 )
from syscolumns
where id = @tableid

truncate table #columninfo

insert into #columninfo (name,usertype)
select name, type
from syscolumns C
where id = @tableid
and type <> 37 -- do not include timestamps

-- Fill @leftpart with the first part of the desired insert-statement, with the fieldnames

select @leftpart = 'select ''insert into '+@tablename
select @leftpart = @leftpart + '('
select @columncount = MIN (num),@columncount_max = MAX (num)
from #columninfo


while @columncount <= @columncount_max
begin
select @columnname = quotename([name]),@columntype = usertype
from #columninfo
where num = @columncount

if (@@rowcount <> 0)

begin
if (@columncount < @columncount_max)
begin
select @leftpart = @leftpart + @columnname + ','
end
else
begin
select @leftpart = @leftpart + @columnname + ')'
end
end
select @columncount = @columncount + 1
end



select @leftpart = @leftpart + ' values('''

-- Now fill @rightpart with the statement to retrieve the values of the fields, correctly formatted

select @columncount = MIN (num),
@columncount_max = MAX (num)
from #columninfo


select @rightpart = ''

while @columncount <= @columncount_max
begin
select @columnname = quotename(name),@columntype = usertype
from #columninfo
where num = @columncount

if (@@rowcount <> 0)
begin

if @columntype in (39,47) /* char fields need quotes (except when entering NULL);
* use char(39) == ', easier readable than escaping
*/
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace(' + @columnname + ','
+ replicate( char(39), 4 ) + ',' + replicate( char(39), 6) + ')+' + replicate ( char(39), 4 ) + ',''NULL'')'
end

else if @columntype = 35 /* TEXT fields cannot be RTRIM-ed and need quotes */
/* convert to VC 1000 to leave space for other fields */
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace (convert(varchar(1000),'
+ @columnname + ')' + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6 ) + ')+'
+ replicate( char(39), 4 ) + ',''NULL'')'
end

else if @columntype in (58,61,111) /* datetime fields */
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+convert (varchar(20),'
+ @columnname + ')+'+ replicate( char(39), 4 ) + ',''NULL'')'
end

else /* numeric types */
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(convert(varchar(99),' + @columnname + '),''NULL'')'
end

if ( @columncount < @columncount_max)
begin
select @rightpart = @rightpart + '+'','''
end

end
select @columncount = @columncount + 1
end
end

select @rightpart = @rightpart + '+'')''' + ' from ' + @tablename

-- Order the select-statements by the first column so you have the same order for
-- different database (easy for comparisons between databases with different creation orders)

select @rightpart = @rightpart + ' order by 1'

-- For tables which contain an identity column we turn identity_insert on
-- so we get exactly the same content

if @hasident > 0
select 'SET IDENTITY_INSERT ' + @tablename + ' ON'

exec ( @leftpart + @rightpart )

if @hasident > 0
select 'SET IDENTITY_INSERT ' + @tablename + ' OFF'

select @tablename = MIN (name)
from #tablenames
where name > @tablename

end

end
return

[MSSQL2005] 사용자와 스키마의 관계


SQL Server 2005에서는 데이터베이스 사용자와 스키마 간의 암시적 연결을 끊습니다.

스키마 정의

스키마는 하나의 네임스페이스를 구성하는 데이터베이스 엔터티 모음입니다. 네임스페이스는 모든 요소가 고유한 이름을 갖는 집합입니다.

예를 들어 이름 충돌을 피하려면 같은 스키마 안에 이름이 같은 테이블이 두 개 있으면 안 됩니다. 두 개의 테이블에 동일한 이름을 사용하려면 테이블이 각각 서로 다른 스키마에 속해 있어야 합니다.

참고:
데이터베이스 도구에 대해 설명할 때는 "스키마"가 스키마 또는 데이터베이스의 개체에 대해 설명하는 카탈로그 정보를 나타내기도 합니다. Analysis Services에 대해 설명할 때는 "스키마"가 큐브 및 차원 같은 다차원 개체를 나타냅니다.

SQL Server 2000에서 SQL Server 2005로 업그레이드

SQL Server 2000에도 CREATE SCHEMA 문이 있지만 실제로 위에 정의된 방법대로 스키마를 만들지는 않습니다. SQL Server 2000에서는 데이터베이스 사용자와 스키마가 암시적으로 연결되어 있습니다. 모든 데이터베이스 사용자는 사용자와 이름이 같은 스키마 소유자이며 개체 소유자는 자신이 속한 스키마의 소유자와 동일합니다. 따라서 SQL Server 2000에 있는 정규화된 이름의 "스키마"는 데이터베이스의 사용자이기도 합니다. 이런 이유로 SQL Server 2000 데이터베이스에서는 사용자를 제거하기 전에 관리자가 해당 사용자 소유의 모든 개체를 삭제하거나 소유자를 변경해야 했습니다. 이 개체가 포함된 SQL Server 2000 데이터베이스가 있다고 생각해 보십시오.

accounting.ap.george.reconciliation

이 개체는 사용자 "george"의 소유입니다. 관리자가 사용자 "george"를 삭제해야 할 경우 먼저 이 개체를 삭제하거나 개체 소유자를 변경해야 합니다. 후자의 경우 다음과 같이 이름을 바꿀 수 있습니다.

accounting.ap.sandra.reconciliation

개체 소유권을 이전하면 정규화된 이름도 바뀝니다. accounting.ap.george.reconciliation를 참조하는 모든 코드를 업데이트하여 변경된 이름을 반영해야 합니다.

SQL Server 2005에서 스키마는 스키마를 만든 데이터베이스 사용자와 독립적으로 존재합니다. 이름을 변경하지 않고 스키마 소유권을 이전할 수 있습니다. 기능을 명확히 나타내는 쉬운 이름을 사용하여 스키마에 개체를 만들 수도 있습니다. 예를 들어 accounting.ap.sandra.reconciliation 대신 accounting.ap.invoice.reconciliation라는 스키마를 만들 수 있습니다. "invoice"는 사용자가 아니므로 데이터베이스에서 사용자를 삭제해도 이 이름을 변경할 필요가 없습니다. 따라서 데이터베이스 관리자와 개발자의 업무가 단순화됩니다.

사용자와 스키마를 분리할 경우의 이점

스키마에 있는 데이터베이스 사용자를 추상화하면 관리자와 개발자에게 몇 가지 이점이 있습니다.

  • 역할이나 Windows 그룹의 멤버 자격을 통해 여러 사용자가 스키마 하나를 소유할 수 있습니다. 따라서 역할과 그룹이 개체를 소유하도록 해주는 친숙한 기능이 더욱 향상됩니다.

  • 매우 간단하게 데이터베이스 사용자를 삭제할 수 있습니다.

  • 데이터베이스 사용자를 삭제할 때 해당 사용자의 스키마에 포함된 개체의 이름을 바꿀 필요가 없습니다. 따라서 스키마를 만든 사용자를 삭제한 후에 스키마에 포함된 개체를 명시적으로 참조하는 응용 프로그램을 수정하거나 테스트하지 않아도 됩니다.

  • 여러 사용자가 통일된 이름 확인을 위해 하나의 기본 스키마를 공유할 수 있습니다.

  • 공유 기본 스키마를 사용하면 개발자가 DBO 스키마 대신 특정 응용 프로그램에 대해 특별히 만든 스키마에 공유 개체를 저장할 수 있습니다.

  • 스키마 및 스키마에 포함된 개체에 대한 사용 권한을 이전 버전보다 세부적으로 관리할 수 있습니다.

  • 정규화된 개체 이름이 4부분으로 구성됩니다. server.database.schema.object

기본 스키마

SQL Server 2005에는 "기본 스키마"라는 개념도 도입되었습니다. 기본 스키마는 정규화된 이름을 사용하지 않고 참조되는 개체의 이름을 확인하는 데 사용됩니다. SQL Server 2000에서는 데이터베이스 사용자를 호출하여 소유한 스키마를 먼저 확인한 다음 DBO 소유의 스키마를 확인했습니다. SQL Server 2005에서는 개체 이름을 확인할 때 서버가 검색할 첫 번째 스키마를 지정하는 기본 스키마가 사용자마다 하나씩 있습니다. CREATE USER 및 ALTER USER의 DEFAULT_SCHEMA 옵션을 사용하여 기본 스키마를 설정하고 변경할 수 있습니다. DEFAULT_SCHEMA는 정의되어 있지 않으므로 데이터베이스 사용자는 DBO를 기본 스키마로 사용합니다.

권한 부여

여러 엔터티의 소유권을 이전할 수 있습니다.

 


 

오라클 인스턴트 클라이언트 + 파워빌더 가능


 개인적으로 리포팅 해놓는 글 입니다 ^^.. 가끔 까먹는지라.. 파워빌더 11.5를 기준으로 오라클의 클라이언트 인스톨없이 접속이 가능 했음을 오늘의 테스트로 알게 되었습니다. 뭐 예전에도 해봤는데 따로 리포팅 해놓지를 않아서 다시 해보게 되었네요. 오라클 사이트에서 인스턴트 오라클 클라인언트를 다운로드 한 후 프로그램과 같이 배포 하면 됩니다. ( 단 오라클 인스턴트 클라이언트의 용량이 100메가에 육박 하므로 조금 안습.. )
물론 배포할때 tnsnames.ora가 반드시 포함 되어야 합니다. 그럼 미래의 나에게 이 글을 바칩니다. ^^;

오라클 DB 수동생성(펌)


 D:\  dbca

 이렇게 명령어를 치거나 시작 --> 오라클 --> 구성 및 이전 툴 --> Database Configuration Assistant 를 누르시면 같은 기능이 됩니다.

 물론  GUI환경이기 때문에 무지 쉽게 하실 수 있습니다. 그리고 오라클에서도 수동으로 생

 성하는 것보다는 DBCA 를 사용하는 것을 권장 하구요^^

 지금 부터는 수동으로 메뉴얼하게 만드는 법을 가르쳐 드릴께요~

 1. initSID.ora 파일은 데이타베이스 설정을 초기화합니다.

 initSID.ora 파일에 나열된 parameter들은 순서대로 정렬할 필요는 없지만

 

특정parameter가 두번 이상 나열되면 마지막 설정이 사용됩니다.. 그래서 Oracle 9i

 

Reference에서는 이러한 중복을 방지할수 있도록 parameter를 문자순으로 배열할 것을

 

권장합니다.initSID.ora 파일을 $ORACLE_HOME/dbs 디렉토리에 만듭니다.

 

물론 님께서는 D:\ 에 생성하시길 원하시니깐 D:\PROD/dbs 이렇게 만드시면

 

되겠네요. 

 

Windows에서는 이 초기화 파라미터 파일이  $ORACLE_HOME\database 디렉토리에

 

있습니다. 간단히 하기 위하여 사용하고 있던 initSID.ora 파일이나 init.ora을 복사한후

 

수정하여 사용합니당.

 

 test을 위하여 initTEST.ora 라는 초기화 파라미터 파일을 만듭니다.

   (물론 아래의 내용은 필요에 따라 수정하면 되요~)  

 

< initTEST.ora sample >

 

*.background_dump_dest='/home/ora920/admin/ORA920/bdump'

*.compatible='9.2.0.0.0'

*.control_files='/home/ora920/oradata/contest/control_01.ctl','/home/ora920/orada

ta/contest/control_02.ctl','/home/ora920/oradata/contest/control_03.ctl'

*.core_dump_dest='/home/ora920/admin/ORA920/cdump'

*.db_block_size=8192

*.db_cache_size=33554432

*.db_file_multiblock_read_count=16

*.db_name='TEST'

*.instance_name='TEST'

*.large_pool_size=16777216

*.processes=200

*.shared_pool_size=43886080

*.sort_area_size=524288

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS'

*.user_dump_dest='/home/ora920/admin/ORA920/udump'

 

 

디렉토리 같은거는 님께서 맞게 수정해 주시면 될꺼 같아요

 

2. 새로운 인스턴스 ID 설정

    실행하기에 앞서 다른 instance가 떠 있고 같은 OS user을 사용한다면 아래와 같이

    ORACLE_SID을 바꾼다. ORACLE_HOME, LD_LIBRARY_PATH, PATH등은 S/W 인스톨시에이미 설정되어 있을 꺼에요

 

    < Bourne 또는 Korn Shell>

 

    ora920:/home/ora920/product/9.2.0/dbs> export ORACLE_SID=TEST

    ora920:/home/ora920/product/9.2.0/dbs> env |grep SID

    ORACLE_SID=TEST 

 

    < C Shell >

    $setenv ORACLE_SID TEST

 

 

3. NOMOUNT 모드로 데이타베이스을 시작합니다.

 

    sqlplus "/ as sysdba"

 

    SQL> startup nomount

 

4. Create Database 명령 실행

   (물론 파일 이름, 겅로명, 싸이즈등은 필요에 따라 수정합니다.)

 

 

  CREATE DATABASE PROD

  MAXLOGFILES 255

  MAXINSTANCES 1

  MAXDATAFILES 256

  MAXLOGHISTORY 256

  DATAFILE '/home/ora920/admin/PROD/system01.dbf' SIZE 400M

  UNDO TABLESPACE "UNDOTBS" DATAFILE '/home/ora920/admin/TEST/undotbs01.dbf'

  SIZE 200M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

  CHARACTER SET UTF8

  LOGFILE GROUP 1 ('/home/ora920/admin/PROD/redo_PROD01.dbf') SIZE 100M,

  GROUP 2 ('/home/ora920/admin/PROD/redo_PROD02.dbf') SIZE 100M;

 

5. Users Tablespace를 생성합니다

   (물론 파일 이름, 겅로명, 싸이즈등은 필요에 따라 수정하면 되겠죠)

 

  CREATE TABLESPACE "USERS" DATAFILE '/home/ora920/admin/PROD/users01.dbf'

  SIZE 300M AUTOEXTEND ON NEXT 5M MAXSIZE 1500M;

 

6. Temporary Tablespace를 생성합니다.

   (물론 파일 이름, 겅로명, 싸이즈등은 필요에 따라 수정)

 

  CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/home/ora920/admin/PROD/temp01.dbf'

  SIZE 40M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED

  EXTENT MANAGEMENT LOCAL;

  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";

 

7. 필요한 view, synonym등을 생성하기 위해 아래의 두 스크립트를 반드시 실행합니다.

   사용량이 많지 않은 서버에서 두 스크립트가 완료되는데 소요되는 시간은 총 35분에서 60분 사이정도 되구요.

   두 스크립트는 반드시 SYS 사용자로 실행해야 해요.

 

   1) CATALOG.SQL : 데이터 딕셔너리 뷰와 다이나믹 포퍼먼스 뷰를 생성

   2) CATPROC.SQL : PL/SQL을 사용하는데 필요한 패키지 및 프로시져를 생성

 

   SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql

   SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

 

8. pupbld.sql 실행.

   $ORACLE_HOME/sqlplus/admin 디렉토리에 있는 pupbld.sql 스크립트는 Product User Profile    테이블 및 관련 프로시져를 생성합니다. 이 스크립트를 실행하는 주요 용도는 사용자가 SQL*Plus에  접속할때마다 경고 메세지가 표시되는 것을 방지하는 것이구요. 이 스크립트는 SYSTEM 사용자로 실행하면 되요.

 

   $sqlplus system/manager

출처 : http://blog.naver.com/sungeunn (ps 미안요~ ^^)

[출처] 오라클 DB생성|작성자 남프로

오라클 캐릭터셋 변경


오라클을 다른 운영체제에 Import시 캐릭터셋을 꼭 확인 해본다.
아래는 캐릭터셋 변경 방법

캐릭터셋 확인 방법

SELECT NAME, VALUE$
FROM SYS.PROPS$
WHERE NAME = 'NLS_LANGUAGE'
OR  NAME = 'NLS_TERRITORY'
OR  NAME = 'NLS_CHARACTERSET'

1. 9i이상  캐릭터셋 변경
sqlplus as sysdba 로 접속 후

UPDATE SYS.PROPS$
SET  VALUE$   ='KO16MSWIN949'
WHERE NAME   ='NLS_CHARACTERSET'

COMMIT;

오라클 서버를 셧다운 후 리스타트 한다. 

2. 8i 캐릭터셋 변경
 SHUTDOWN IMMEDIATE;
<do a full backup>
 STARTUP MOUNT;
 ALTER SYSTEM ENABLE RESTRICTED SESSION;
 ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
 ALTER SYSTEM SET AQ_TM_PROCESSES=0;
 ALTER DATABASE OPEN;
 ALTER DATABASE CHARACTER SET KO16MSWIN949;
 SHUTDOWN IMMEDIATE;
 STARTUP

 

Vmware + 우분투(리눅스) + 오라클10g 드디어 성공..


아이고 힘들어 죽겠네. 나중을 위해 개인적으로 레포팅을 해놓자.

1. vmware나 가상 머신으로 우분투를 깔때는 적당한 하드용량을 배분할것(특히 스왑용량은 충분히!)

2. 오라클 깔때 10g 인스톨하던중 Linking phase fails for 10gR2 on Ubuntu 6.06: undefined ref to 'nnfyboot'
    버그 발생시 http://forums.oracle.com/forums/thread.jspa?threadID=413032&tstart=0 사이트를 참고하여
    해결

3. 우분투에 오라클10g를 인스톨 할경우 아래의 사이트의 설정을 따라 하여 보자.
    http://blog.naver.com/wjwscv?Redirect=Log&logNo=20052759427

4. 굳이 우분투에 오라클 쓸일 없으면 그냥 윈도우서버 2003에 인스톨하여 하용하자.  시간 많은 사람이나
    한번 해보시길 권한다. 귀찮다 매우!!

6. 리스너 시작은 반드시 oracle 계정으로 한다. !!

7. 그런데 윈도우2003깔고 오라클10g보다 리눅스깔고 오라클10g가 용량이 더 큰건 뭐지..우분투가 좀 무거운
    건가.. 본인이 리눅스는 그닥 잘 몰라서..

8. 아래는 스샷 몇장 ~!

우분투 첫 화면.. 윈XP 가상머신상에서 돌린거라거 좀 느릿 느릿 ^^;; 뭐 제대로 깔면 엄청 빠르겠지..

오라클10.2 리스너 동작 상황 !! 이상무!!

sqlplus로 scott의 테이블 셀렉트!! 이상무!! 이제 다른 응용프로그램에서의 접근만 해보면 테스트 완료

결론 : 내가 왜 이걸 하면서 밤을 샜을까.. 드디어 미친게야.. 다른 할일도 많은데 ㅠ.ㅠ.. 잠자자!!!

오라클 Win 2000,XP에서 오라클 8.1.7이나 8.1.6을 Install 문제


pentium4에서는 오라클 8.1.7이나 8.1.6을 install할려고 하면 처음 autorun에서 화면이뜨고 install을 하면 화면이 죽어버립니다.
 
이유는 기존 8.1.7이나 8.1.6에는 jRE1.1.7이 들어 있는데 이것이 문제인거 같습니다.

SUN사 측에서도 pentium4를 쓸경우는 jre1.1.8이상을 쓰라고 되어 있습니다.
 
해결방안은 우선
 
1. 기존 가지고 있는 CD를 CD-ROM에 삽입해서 깔 수는 없습니다. CD의 전부를 하드에 하드 copy 하십시요.
 
2. http://ofs.gwu.edu/ofs/application/req.html 사이트에 가셔서 symcjit.dll를 다운받으십시오
   <<symcjit.dll 다운로드>>
 
3. 하드에 옮겨놓은 오라클 폴더에 들어가서 stage/Components/oracle.swd.jre/1.1.7.30/1/DataFile/Expanded/jre/win32/bin 디렉토리로 가셔서 다운받으신 파일(symcjit.dll)을 엎어치십시요
 
그리고 처음부터 다시 실행하시면 문제없이 잘 됩니다.

otn.oracle.co.kr/forum 참고