'SQL'에 해당되는 글 79건

  1. 2017.07.13 MS SQL2008R2 Cluster IP 변경 절차 1
  2. 2009.03.31 SQL Injectin 공격 방어를 위한 DB 설정 변경
  3. 2008.11.25 windows 2000을 포맷하지 않고 초기화는 방법
  4. 2008.11.25 SQL 트랜잭션 로그가 가득 차는 원인
  5. 2008.11.25 윈도우2000에서 137기가 바이트 이상의 고용량 하드 사용하기
  6. 2008.08.08 MS-SQL table 복사
  7. 2008.08.06 MS-SQL 테이블 개체소유자 변경
  8. 2008.08.06 MS-SQL 데이터정렬 변경하기
  9. 2008.08.06 MS-SQL 테이블별 용량체크
  10. 2008.07.30 Windows Server DNS백업을 위한 레지스트리의 DNS Zones 위치정보
  11. 2008.07.30 Windows 2003 메모리 3G이상 사용시
  12. 2008.07.30 IIS재설치후 백업된 메타데이터로 복원시 IUSR 계정 패스워드가 변경되어 발생하는 에러 해결방안 1
  13. 2008.07.23 중소기업을 위한 SQL Server 기본 모니터링 - 3
  14. 2008.07.23 중소기업을 위한 SQL Server 기본 모니터링 - 2
  15. 2008.07.23 중소기업을 위한 SQL Server 기본 모니터링 - 1
  16. 2008.07.16 MS-SQL 데이터정렬 변경하기
  17. 2008.02.27 SQL서버 진단을 위한 주요 성능카운터
  18. 2007.11.12 MS-SQL 성능향상을 위한 시스템상태 점검사항 및 설정사항
  19. 2007.10.25 SA계정 패스워드 변경하기
  20. 2007.09.26 도메인의 웹(Web) 서버 종류 알아내기
  21. 2007.09.23 Oracle 기본 SQL 사용법
  22. 2007.09.23 SQL Injection을 활용한 웹 공격
  23. 2007.09.22 터미널 서비스의 포트를 변경하는 방법
  24. 2007.09.22 Windows2000 설치시 생성되는 IUSER_computername과 IWAM_computername 계정에 대한 암호 알아내기
  25. 2007.09.22 Windows 자동종료 예약하기
  26. 2007.09.22 Access DB 및 엑셀 파일을 SQL DB로 변환하기
  27. 2007.09.22 터미날서비스가 정상적으로 돌아가는데 접속이 안되는 경우
  28. 2007.09.22 Windows 2000에서의 "NTLDR Is Missing" 에러 메세지 복구 콘솔 사용해서 해결
  29. 2007.09.22 중복되는 MAC 주소의 충돌 문제를 해결하는 방법
  30. 2007.09.22 오류 메시지: 내부 DNS 오류 때문에 원격 서버를 찾을 수 없습니다(SMTPSVC)

MS SQL2008R2 Cluster IP 변경 절차
SQL Server 2008R2 Cluster 환경에서 각 노드의 Public Network IP 및 클러스터 리소스인 MSDTC, Quorum, SQL 리소스 IP를 변경하는 절차에대하여 정리합니다.  Windows Server 2008 Hyper-V 에 SQL Server 2008R2 Cluster 를 구성하였으며 이
테스트 환경에서 변경 절차를 진행합니다.

[시나리오]
기존 네트워크 구성 정보
AD
192.168.0.1
SNODE1
192.168.0.2
SNODE2
192.168.0.3
Cluster
192.168.0.4
MSDTC
192.168.0.5
SQL
192.168.0.6
Heartbeat
10.10.0.2 / 10.10.0.3
변경 네트워크 구성 정보
AD
192.168.1.1
SNODE1
192.168.1.2
SNODE2
192.168.1.3
Cluster
192.168.1.4
MSDTC
192.168.1.5
SQL
192.168.1.6
Heartbeat
10.10.0.2 / 10.10.0.3
진행 내용
제목
SQL2008R2 MSCS IP 변경 절차
총 소요시간(분)

작업 지원 인원

설치 정보
구분
AD
NODE1
NODE22
서버 환경
Windows server 2008R2 /  MS SQL Server 2008R2
Host Name
AD.test.com
Snode1.test.com
Snode2.test.com
Real IP
192.168.0.1
192.168.0.2
192.168.0.3
도메인 가입 여부
Domain controller in the Node
설치 도메인 계정
Test\administrator
Cluster name / IP

cluster / 192.168.0.4
MSDTC name /IP

MSDTC / 192.168.0.5
가상 SQL 서버

Sqlsluter
인스턴스 명

MSSQL$sqlsluter
가상 SQL 서버 IP

192.168.0.6
사전 점검
순번
항목
비 고                   
소요시간(분)
1
NIC 점검
Public/Private NIC 설정 확인

2
스토리지 점검
디스크 관리자에서 공유 디스크에 대한 확인

3
이벤트 로그 점검
특이 사항 유무 확인

4
보안 업데이트 점검
최신 보안 업데이트까지 적용 여부 확인

5
드라이버/펌웨어 확인
NIC 등 최신 드라이버/펌웨어 적용 여부 확인

총 소요 시간

작업절차(순번)
설 명
1
Snode2 서버 Cluster Services 중지
2
Snode2 서버 Iscsi Target 제거
3
Snode2 Public IP변경
4
Snode2 서버 종료
5
Snode1 서버 Cluster Services 중지
6
Snode1 서버 Iscsi Target 제거
7
Snode1 Public IP변경
8
Snode1 서버 종료
9
AD서버 IP변경
10
AD서버 StarWind IP변경
11
AD서버 종료                                   
12
서버 이전후(가정) AD서버 ON
13
Snode1 서버 ON
14
Snode1 서버 iSCSI 에서 디스크 연결
15
Snode1 서버 Cluster Service 시작
16
클러스터 관리자에서 Cluster 리소스의 IP변경
17
Cluster 리소스 온라인으로 전환                      
18
클러스터 관리자에서 MSDTC 리소스의 IP변경
19
MSDTC 리소스 온라인으로 전환
20
클러스터 관리자에서 SQL Server 리소스의 IP변경
21
SQL Server 리소스 온라인으로 전환
22
Snode2 서버 ON
23
Snode2 서버 iSCSI 에서 디스크 연결
24
Snode2 서버 Cluster Service 시작
25
클러스터 관리자에서 node이동하여 fail over 시 SQL 및 정상작동 Test진행
[작업절차]
1. Snode1, Snode2 각 노드가 온라인 상태에서 아래 작업을 진행합니다.
  1. Snode2 노드 Cluster Services 중지(사용안함으로 설정)

2.Iscsi Target 제거(MSDTC, Quorum, SQL 디스크 모두 Logoff) 스토리지시 건너뜀

3.Snode2 Public IP변경(AD의 DNS주소 포함)

4.Snode2 서버종료
5.Snode1 노드 Cluster Services 중지(사용안함으로 설정)

6.Iscsi Target 제거(MSDTC, Quorum, SQL 디스크 모두 Logoff) 스토리지시 건너뜀

7.Snode1 Public IP변경

8.Snode1 서버종료
9.AD서버 IP변경

10.StarWind의 IP변경

11.AD서버 종료
12.서버 이전후 AD서버 ON
13.Snode1 서버 ON
14.iSCSI 에서 디스크 연결

15.Cluster Service 시작
16.장애조치 클러스터 관리자 시작
17.해당 클러스터 è 네트워크 è 클러스터 네트워크 3번 마우스 우클릭후 속성 선택하여 클라이언트가 이 네트웍을 통해연결할 수 있음 선택

18.해당 클러스터 선택후 중앙 하위 클러스터 코어 리소스창에 클러스터 IP주소 마우스 우클릭

19.Cluster 리소스의 IP변경(변경확인 적용안될시 다시 변경)

20.IP변경 확인후 해당 IP리소스 온라인으로 전환

21.Cluster 리소스 온라인으로 전환

22.서비스 및 응용프로그램으로 이동하여 MSDTC IP변경작업 진행(작업 동일)

23.IP 변경 작업 및 리소스 온라인 전환 작업 진행

24.SQL Server 동일 IP변경 및 리소스 온라인 전환 작업 진행

25.Snode2 서버 ON
26.iSCSI 에서 디스크 연결

27.Cluster Service 시작
28.node이동 fail over 시 SQL 및 정상작동 Test진행

Posted by 시스템매니아
,

SQL Injection 공격이 여전히 활발하게 들어오고 있습니다. 최근에는 MSSQL이건 Oracle이건 가리지 않는 속성을 보여주더군요. 요즘의 SQL Injection공격은 DB를 변조하는 방식으로 진행되었지만, 아직도 일부 SQL Injection 공격은 여전히 시스템 장악을 목적으로 사용됩니다. 가장 대표적인 공격이 바로 xp_cmdshell 이겠지요.

xp_cmdshell와 같은 명령어는 잘쓰면 좋지만, 아무나 사용할 수 있다면 최악의 상황으로 이어질 수 있습니다.

type c:\boot.ini 대신에 del c:\boot.ini를 했다면?

 이러한 공격을 막는 방법으로 서버에 도달하기 이전에 웹방화벽에서 필터링을 하고, 웹소스 레벨에서 필터링을 하고, DB에서 적절한 권한 부여라는 모든 단계를 거치는 것이 좋습니다. 이 방법을 모두 동원할 수 없다 할지라도, 적어도 두개 이상의 방법을 선택하여 진행하여야 안전하다고 할 수 있습니다.

  • 웹방화벽이 완벽을 말할 수 없는 이유는 학습하는데 걸리는 시간이 있기 때문이며(WebKnight와 같이 지능형 장비가 아닌 경우는 패턴 추가하기 전까지는 답이 없습니다),
  • 웹소스 레벨의 필터링은 개발자의 실수가 있을 수 있기 때문이며,
  • DB에서의 권한 부여는 기본값 그대로 쓰기에는 충분히 위험하기 때문입니다.

 위에 나열한 3가지 필터링할 수 있는 부분 중에서 어느 한 곳만이라도 확실히 막기만 한다면, 웹 취약점을 이용한 공격은 생각보다 많이 막을 수 있습니다, 그 중에서 DB의 권한 제어를 위해 금지 또는 조심해야 할 확장 프로시저는 다음과 같은 형식으로 제어할 수 있습니다.

USE master
DENY  EXECUTE  ON [master].[dbo].[xp_availablemedia] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_dirtree] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_fileexist] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_fixeddrives] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_getfiledetails] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regaddmultistring] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regdeletekey] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regdeletevalue] TO [guest] CASCADE 
DENY  EXECUTE  ON [master].[dbo].[xp_regread] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regremovemultistring] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regwrite] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_subdirs] TO [guest] CASCADE

DENY  EXECUTE  ON [master].[dbo].[xp_availablemedia] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_dirtree] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_fileexist] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_fixeddrives] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_getfiledetails] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regaddmultistring] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regdeletekey] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regdeletevalue] TO [public] CASCADE 
DENY  EXECUTE  ON [master].[dbo].[xp_regread] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regremovemultistring] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regwrite] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_subdirs] TO [public] CASCADE

USE msdb
REVOKE ALL ON dbo.mswebtasks FROM public
REVOKE EXECUTE ON sp_add_job FROM public
REVOKE EXECUTE ON sp_add_jobserver FROM public
REVOKE EXECUTE ON sp_add_jobstep FROM public
REVOKE EXECUTE ON sp_enum_dtspackages FROM public
REVOKE EXECUTE ON sp_get_dtspackage FROM public
REVOKE EXECUTE ON sp_get_sqlagent_properties FROM public
REVOKE EXECUTE ON sp_start_job FROM public

 위에 있는 쿼리는 SQL 서버에 쿼리 분석기로 연결한 후 한번 실행해주면 간단하게 할 수 있는 부분입니다만, 실제 서버에 바로 적용하면 돌이킬 없는 상황이 되버릴 수도 있습니다. 특히 스케줄 부분에 대한 확장 프로시저를 건드리기 때문에 복잡한 구성을 가진 SQL 서버라면 적용시 장애로 이어질 수도 있습니다.

 더구나, 위에 있는 쿼리를 실행한다고 해서 서버가 완벽하게 보호되는건 아닙니다. SELECT 나 UPDATE 같은 쿼리를 통해서 DB의 내용 자체를 변조하는 공격을 막을 수는 없기 때문입니다. 

 

출처 : http://www.ntfaq.co.kr/4307

Posted by 시스템매니아
,
윈2000 재설치하지 않고 초기화시키는 방법!!!

재설치를 하지 않고 윈2000의 셋팅을 초기화시키는 방법은 winnt - system32 - config 폴더안의 파일을 winnt - repair 폴더안의 파일들로 대치를 시키는것입니다

물론 윈2000부팅 된 상태에서는 접근이 안됩니다.. 즉,,도스부팅디스켓으로 또는 윈98로 부팅후 winnt - repair 폴더안의 파일들을 winnt - system32 - config 폴더안으로 복사 해넣으시면 되지요 .
이 파일들은 숨김.시스템속성이 있으므로 당연히 속성을 확인하셔야 겠지요..

멀티부팅 해서 쓰시는 분은 다른 OS로 부팅하셔서 작업하시면 편하실겁니다..

그런후에 재부팅하 시면 윈2000이 초기화 즉,,첨 설치시의 환경으로 돌아갑니다. 이 경우레지스트리 시스템 장치 보안정보등이 초기화가 되지요..

물론 응용프로그램도 설치가 안된 상태로 됩니다..

윈2000을 재설치 안하고 파일들을 복사해 넣음으로 초기상태로 돌아가서 사용하게 됩니다.

보조프로그램의 백업 응급복구디스켓을 시스템이 가장 안정화 되어있을때 만드시면 winnt - repair - regback 라는 폴더가 생기고 그 아래에 파일들이 생깁 니다.. 윈2000에 이상이 있을 시에 위에서 처럼 winnt - repair 안의 파일들 대신 winnt - repair - regback

파일들을 이용해서 작업하시면 됩니다

Posted by 시스템매니아
,
출처 : http://support.microsoft.com

요약
SQL Server 트랜잭션 로그가 가득 차면 데이터베이스에서 CHECKPOINT를 비롯하여 UPDATE, DELETE 또는 INSERT 작업을 더 이상 할 수 없습니다. 이러한 상황은 아래와 같은 오류 1105로 나타납니다.

Can't allocate space for object syslogs in database dbname because the logsegment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
master 또는 tempdb를 비롯한 어떤 데이터베이스에서나 이러한 상황이 발생할 수 있습니다. 본 문서에서는 오류 1105를 일으키는 문제점에 대한 가능한 원인과 해결 방법을 설명합니다. 트랜잭션 로그가 가득 차서 오류 1105가 발생한 경우에는 DUMP TRANSACTION 문을 사용하여 로그를 비워야 합니다. DUMP TRANSACTION의 사용 방법에 대한 자세한 내용은 SQL Server 설명서를 참조하십시오.
추가 정보
Microsoft SQL Server 같은 전형적인 관계형 데이터베이스의 기본 특성은 트랜잭션 무결성을 보장하는 것입니다. 모든 트랜잭션은 시스템 오류가 발생한 경우에도 변경 내용이 모두 적용되거나 전혀 적용되지 않는다는 점에서 완전한 원자성(즉, 개별적으로 기능을 수행)을 가져야 합니다. 사용자 정의 트랜잭션에서 BEGIN TRANSACTION 문과 COMMIT TRANSACTION 문 사이에 있는 모든 문은 모두 적용되거나 또는 전혀 적용되지 않습니다. 암시적 트랜잭션에서는 각각의 개별 SQL 문을 원자 단위로 간주합니다.

이러한 기능 때문에 SQL Server는 프로덕션 단계에서 전원 공급이 차단되거나 운영 체제가 충돌하는 등의 문제가 발생한 다음 다시 시작되고 나면 사용자의 개입 없이 자동으로 데이터베이스를 일관된 상태로 복구합니다. 이와는 대조적으로 비 관계형 시스템에서는 시스템 오류가 발생하면 데이터베이스의 일관성 문제를 검사하기 위해 오랜 시간에 걸친 수동 작업이 필요한 경우가 많습니다.

이러한 기능은 바로 트랜잭션 로그 메커니즘에 의해 제공됩니다. 트랜잭션 무결성은 SQL Server의 기본 기능, 즉 본질적인 특성으로 간주되기 때문에 로깅 설정을 해제할 수 없습니다. 특정 유틸리티를 사용하거나 빠른 BCP 또는 SELECT INTO 같은 유지 관리 작업을 통해 최소 로깅을 수행할 수 있지만 이러한 최소 로깅에서도 나중에 롤백할 수 있도록 익스텐트 할당을 기록합니다.

로깅에는 상당히 많은 공간이 필요할 수 있습니다. 예를 들어, 대개의 경우 업데이트되는 각 데이터 행의 업데이트 이전 및 이후 이미지뿐만 아니라 영향을 받는 모든 인덱스 행의 이미지도 기록해야 합니다. 로그에 기록되는 각 행에 대해 일정 양의 트랜잭션 레코드 오버헤드를 기록해야 하기 때문에 업데이트되는 데이터와 소모되는 로그 공간의 비율은 행 너비에 따라 상당히 다를 수 있습니다. 좁은 행인 경우에는 특정 UPDATE, DELETE 또는 INSERT 작업에 소모되는 로그 공간이 소모되는 데이터 공간의 10배가 될 수 있습니다. 행이 넓으면 소모되는 로그 공간이 상대적으로 작아집니다. 트랜잭션 무결성을 제공하기 위해서는 로그 공간 소모를 피할 수 없습니다. 데이터베이스 관리자는 설치 시 충분한 로그 공간을 제공해야 합니다.

필요한 로그 공간은 여러 가지 요인에 따라 달라질 수 있기 때문에 정확하게 예측하기는 어렵습니다. 처음에 로그 크기를 데이터베이스 크기의 15-30% 크기로 할당하는 것이 적절하다는 일반적인 규칙이 있지만, 실제 상황에서는 이 규칙도 매우 다를 수 있습니다. 성공적인 SQL Server 설치에서는 사용할 특정 데이터 및 응용 프로그램에 필요한 로그 공간을 대략적으로 예측하기 위해 간단한 실증적 테스트를 수행한 다음 그 예측을 바탕으로 로그 크기를 정합니다. 테스트 없이 계산만을 바탕으로 로그 크기를 정하기는 어려우며 정확하지 않을 수 있습니다.

예측하기 어려운 여러 가지 요인이 로그 공간 소모량의 변화에 영향을 미칠 수 있습니다. 그러한 요인 중 하나는 쿼리 최적화 프로그램(Query Optimizer)입니다. 특정 SQL 데이터 수정 문의 경우 액세스 계획은 데이터의 통계적 분포에 따라 시간이 가면서 달라질 수 있습니다. 액세스 계획마다 서로 다른 크기의 로그 공간을 소모할 수 있습니다. 또 따른 요인은 피할 수 없는 내부 데이터베이스 조각화로서 이는 수행되는 페이지 분할의 수에 영향을 미칠 수 있습니다. SQL Server는 사용자를 대신하여 데이터를 자동으로 관리하기 때문에 이 프로세스를 조사하거나 이 프로세스에 영향을 주기 위해 사용자가 할 수 있는 일이나 해야 할 일은 없습니다.

간단한 테스트 예제로 DBCC CHECKTABLE(syslogs)을 실행할 수 있습니다. 그러면 데이터 수정 쿼리에 대한 대표 예제를 실행하기 전과 실행한 후에 로그에 있는 2048바이트 데이터 페이지 수가 반환됩니다. 이 방법으로 이 유형의 쿼리에 필요한 로그 공간 크기를 대략적으로 알 수 있습니다. SQL Server 같은 관계형 데이터베이스에 로그 공간이나 데이터 디스크 공간을 제공할 때는 일반적으로 공간을 과다하게 할당하는 것이 최선의 방법입니다.

SQL Server 7.0 및 2000 클래스 서버는 필요에 따라 트랜잭션 로그를 확장하는 기능이 있습니다. 확장되는 크기를 사용자가 조정할 수도 있고 사용 가능한 디스크 공간을 모두 이용하도록 지정할 수도 있습니다. 로그 파일은 여러 개의 가상 로그 파일로 구성됩니다. 이 가상 로그 파일의 개수와 크기는 SQL Server에서 자동으로 결정하며 사용자가 구성할 수 없습니다. 데이터베이스를 처음 생성할 때 각 실제(Physical) 로그 파일은 최소한 두 개의 가상 로그 파일을 가집니다. 데이터베이스 관리자는 로그 공간 부족이 발생하지 않도록 하기 위해 데이터베이스의 "검사점에서 로그 자름(Truncate log on checkpoint)" 옵션을 사용 가능하게 설정하기도 합니다. 이 옵션의 목적은 주로 백업을 위해 로그 덤프에 의존하지 않는 개발용 또는 테스트용 데이터베이스를 위해 자동으로 로그를 자르는 방법을 제공하는 것입니다. 이 옵션을 설정해도 로깅 또는 트랜잭션 무결성의 설정이 해제되지는 않습니다. 이 옵션을 설정하면 단지 검사점(Checkpoint) 처리기가 약 60초 간격으로 로그를 자를 뿐입니다. "검사점에서 로그 자름(Truncate log on checkpoint)" 옵션이 설정된 데이터베이스에 수동 검사점(Checkpoint) 명령을 실행하면 로그가 잘리지 않습니다. tempdb 데이터베이스에 대해서는 이 옵션이 항상 설정되어 있지만 sp_help 저장 프로시저(stored procedure) 출력의 상태 열에는 나타나지 않습니다.

"검사점에서 로그 자름(Truncate log on checkpoint)" 옵션을 설정하더라도 여러 가지 요인에 의해 로그 공간이 부족할 수 있습니다. 이러한 각각의 요인을 아래에서 설명합니다.
대량 원자 트랜잭션, 특히 일괄적인 UPDATE, INSERT 또는 DELETE 작업 트랜잭션: 각각의 단일 SQL 문은 완전히 적용되거나 전혀 적용되지 않아야 하는 원자 단위로 간주됩니다. 따라서 모든 행 교체가 로그에 기록되어야 하며 수행되는 동안에는 트랜잭션이 잘리지 않습니다. 예를 들어, 실행하는 데 5분이 걸리는 대량 일괄 INSERT가 실행될 경우 이 시간 동안은 해당 트랜잭션에 소모되는 로그가 잘리지 않습니다. 데이터베이스 관리자는 예상되는 가장 큰 일괄 작업을 수행하는 데 충분한 로그 공간을 제공하거나 일괄 작업을 좀더 작은 그룹으로 나누어 수행해야 합니다.
커밋되지 않은 트랜잭션: 로그는 가장 오래된 커밋되지 않은 트랜잭션 이전까지만 잘립니다. 커밋되지 않은 트랜잭션의 원인은 몇 가지가 있을 수 있지만 대개는 응용 프로그램 오류가 그 원인입니다. 원인은 아래와 같습니다.
일괄 트랜잭션: 위에서 설명한 것처럼 대량 일괄 트랜잭션이 수행되는 동안에는 트랜잭션에 의해 생성되는 로그 레코드가 잘리지 않습니다. 그러나 그러한 트랜잭션 때문에 같은 기간에 커밋을 수행하는 좀더 짧은 다른 트랜잭션의 로그도 잘리지 않습니다.

예를 들어, 데이터베이스 관리자가 가능한 가장 큰 일괄 트랜잭션을 수행하는 데 충분한 크기의 로그를 할당했다고 가정합니다. 그러나 이 트랜잭션이 실행되는 동안 좀더 짧은 다른 데이터 수정 문도 역시 로그 공간을 소모할 수 있습니다. 대량 일괄 트랜잭션이 먼저 시작되었고 그것이 가장 오래된 커밋되지 않은 트랜잭션이기 때문에 로그 공간은 잘리지 않습니다. 관리자는 대량 일괄 트랜잭션의 동시성과 로그에 미치는 영향을 인식하고 적절하게 로그 크기를 조정해야 합니다.
사용자 정의 트랜잭션 내에서 사용자 입력 또는 시간이 오래 걸리는 다른 작업을 허용하는 잘못 설계된 응용 프로그램: 응용 프로그램이 BEGIN TRANSACTION을 실행한 후 사용자의 동작에 따라 시간이 오래 걸릴 수도 있는 입력을 사용자에게 요구하는 경우를 예로 들 수 있습니다. 이 경우, 사용자가 응답하고 응용 프로그램이 COMMIT을 실행할 때까지는 로그를 자를 수 없습니다.
트랜잭션이 커밋되지 않는 응용 프로그램 오류: 이 오류의 일반적인 원인은 사용자 정의 트랜잭션 내에서 DB-Library 호출 dbcancel()을 잘못 처리하는 것입니다. dbcancel()을 사용하여 쿼리를 취소할 경우 현재 실행 중인 SQL 문은 중단되고 롤백되지만 다른 트랜잭션은 계속 실행됩니다. 응용 프로그램에서 이를 인식하고 필요한 ROLLBACK TRANSACTION 또는 COMMIT TRANSACTION 문을 실행하여 트랜잭션을 닫아야 합니다. 그렇지 않으면 아래와 같은 오류 3902가 발생합니다.

The commit transaction has no corresponding BEGIN TRANSACTION.
응용 프로그램에서 SELECT @@TRANCOUNT를 보내어 어떤 트랜잭션 중첩 수준이 있는지 확인하는 것이 유용할 수도 있습니다. 그러나 응용 프로그램에서 무조건 SELECT @@TRANCOUNT를 보낸 다음 COMMIT/ROLLBACK을 실행하여 @@TRANCOUNT=0을 얻을 수는 없습니다. @@TRANCOUNT가 응용 프로그램이 예상하는 것과 다르면 응용 프로그램이 트랜잭션 중첩 수준을 추적하는 데 실패했음을 의미하므로 응용 프로그램 설계에 오류가 있는 것입니다. 이 시점에서 COMMIT/ROLLBACK을 실행하면 응용 프로그램은 어떤 트랜잭션이 의도하지 않은 트랜잭션 수준이 되었는지 알지 못하기 때문에 의도하지 않은 트랜잭션이 적용되거나 중단될 수도 있습니다. 대신에 프로그래머는 응용 프로그램 및 관련된 저장 프로시저(stored procedure)를 디버깅하여 의도하지 않은 트랜잭션 수준의 원인을 알아내야 합니다.
네트워크 연결 끊김을 SQL Server에 알리지 않는 네트워크 오류: 사용자 정의 트랜잭션이 실행되는 동안 클라이언트 워크스테이션이 응답하지 않거나 다시 부팅되거나 종료되면 네트워크 계층에서 SQL Server에 이 사실을 알려야 합니다. 네트워크가 적절하게 이 사실을 알려주지 않으면 SQL Server에게는 클라이언트가 여전히 존재하는 것처럼 보이기 때문에 클라이언트가 열어 놓은 트랜잭션이 계속 유지됩니다. 이는 네트워크 문제이므로 네트워크를 조사해야 합니다. 관리자가 sp_who, sp_lock 또는 네트워크 유틸리티를 사용하여 여전히 존재하는 클라이언트 세션을 확인한 다음 수동으로 제거함으로써 이 문제를 해결할 수 있습니다.
차단으로 인해 커밋되지 않은 트랜잭션: 다중 사용자 환경에서는 열려 있는 트랜잭션이 다른 프로세스가 설정한 잠금(Lock)에 의해 차단될 수 있습니다. 이 경우 트랜잭션이 계속 열려 있어 로그를 자르지 못하게 합니다. 이 문제를 확인하려면 프로그래머 또는 데이터베이스 관리자가 sp_who, sp_lock 또는 기타 도구를 사용하여 동시성 환경을 분석해야 합니다. 대부분의 경우 적절한 쿼리, 인덱스 및 데이터베이스 설계를 통해 차단 문제를 줄이거나 없앨 수 있습니다.
데이터 수정 쿼리를 취소하는 데 실패: 응용 프로그램이 dbcancel()을 실행했으나 네트워크 또는 SQL 문제로 인해 쿼리가 취소되지 않으면 쿼리는 계속 실행되고 트랜잭션은 열린 상태를 유지합니다. 이 문제가 의심스러우면 sp_who를 사용하여 쿼리가 취소되었는지 확인합니다. TCP/IP 소켓 클라이언트에서 취소를 시도하는 경우에는 명명된 파이프(Named Pipe) 클라이언트에서 테스트를 수행하거나 로컬 파이프를 사용하여 서버 컴퓨터에서 클라이언트 응용 프로그램을 실행합니다. 이 방법은 쿼리가 취소되지 않은 것이 네트워크 문제 때문인지 SQL 문제 때문인지 구분하는 데 도움이 됩니다.
검사점(Checkpoint) 처리기 잘라내기 대역폭 초과: 로그는 60초마다 잘리지만 이 잘라내기 속도는 제한적입니다. 이 시나리오는 일반적이지 않으므로, 먼저 기타 가능한 로그 오버플로의 원인을 고려하여 배제한 후 이 시나리오에 해당하는지 검사해야 합니다. 그러나, 많은 클라이언트가 동시에 대량 업데이트를 수행하면 최대 잘라내기 속도보다 로그가 차는 속도가 더 빠를 수 있습니다. 이는 액체를 일정한 속도로만 배출할 수 있는 깔대기를 사용할 때 배출하는 동안에도 액체가 넘칠 수 있는 것과 유사합니다. 이 시나리오에 해당하면 업데이트되는 행의 수를 줄이도록 응용 프로그램을 다시 구성할 수 있습니다. 업데이트되는 행의 수를 줄이는 것은 항상 모든 관계형 데이터베이스의 기본 설계 목표가 되어야 합니다.

이 방법으로 해결할 수 없으면 스트라이핑, 추가 컨트롤러 등을 통해 디스크 I/O 대역폭을 늘리도록 시스템을 재구성할 수 있습니다. 이 경우에는 일반적으로 검사점(Checkpoint) 처리기 프로세스가 로그 잘라내기를 계속 시도하면서 DUMP TRANSACTION 상태에서 점점 더 많은 시간을 소모하는지 확인합니다. 잘라내기 임계값을 초과하고 나면(아래 참조) 로그를 지울 때까지 검사점(Checkpoint) 처리기가 해당 데이터베이스에서 잘라내기를 시도하는 것을 보지 못할 수도 있습니다.
잘라내기 임계값 초과: 검사점(Checkpoint) 처리기는 기본적으로 DUMP TRANSACTION WITH TRUNCATE_ONLY를 수행합니다. 로그가 이미 특정 지점까지 가득 찼으면 수동으로 실행했을 때처럼 덤프가 항상 성공하지는 않습니다. 예를 들어, 검사점(Checkpoint) 처리기가 로그를 잘라낸 후 다음에 다시 잘라내기 전에 많은 업데이트 작업이 몰려 로그가 95%까지 채워질 수 있습니다. 검사점(Checkpoint) 처리기가 잘라내기를 시도할 때는 로그가 완전히 채워지지는 않았지만 거의 가득 차서 잘라내기가 불가능할 수도 있습니다. 이 때 잘라내기를 수행할 수 없는 이유는 로그 잘라내기 자체를 로그에 기록해야 하기 때문입니다. 이 경우 유일한 해결 방법은 DUMP TRANSACTION WITH NO_LOG를 사용하여 수동으로 로그를 잘라내는 것입니다. NO_LOG 옵션을 사용하는 것은 로그에 기록되지 않는 작업이기 때문에 이 작업을 하는 동안 시스템에 오류가 발생하면 데이터베이스 오류로 이어질 수 있으므로, 반드시 필요한 경우가 아니면 NO_LOG 옵션을 사용하지 않는 것이 좋습니다.
위 요인들 간 상호 작용: 예를 들어, 업데이트가 많은 환경인 경우 정상 조건에서는 검사점(Checkpoint) 처리기 잘라내기 속도에서 로그가 가득 채워지지 않습니다. 예를 들어, 잠금(Lock) 경쟁 같은 위 조건 중 하나에 의해 일시적으로 열린 트랜잭션이 로그를 50%까지 채운다고 가정하면 다른 업데이트 상황을 처리하기 위한 공간이 줄어들어 자동 잘라내기가 불가능한 지점인 잘라내기 임계값까지 도달할 가능성이 더욱 커집니다. tempdb 데이터베이스에서의 트랜잭션은 다른 데이터베이스의 경우와 마찬가지로 로그에 기록됩니다. TRUNCATE LOG ON CHECKPOINT는 tempdb에 있기 때문에 대부분의 경우 로그가 잘리고 오버플로가 발생하지 않습니다. 그러나 위에 언급한 상황으로 인해 tempdb 로그가 가득 채워질 수 있습니다. Tempdb는 일반적으로 혼합된 로그 및 데이터(sysusages.segmap=7)를 위해 구성되므로 데이터 작업과 로그 작업이 사용 가능한 동일 공간을 차지하기 위해 경쟁합니다. GROUP BY, ORDER BY DESC 등과 같은 특정 Transact-SQL 구성 요소는 작업 공간을 확보하기 위해 자동으로 tempdb를 요구합니다. 이 트랜잭션은 또한 작업 공간을 위해 tempdb에서 암시적 BEGIN TRANSACTION 레코드를 발생합니다. 이 tempdb 트랜잭션은 사용자 데이터베이스에서 트랜잭션 기간 동안 계속되며 이로 인해 이 기간 동안 tempdb 로그 잘라내기가 지연될 수 있습니다. 잠금(Lock)에 의한 차단 또는 응용 프로그램이 dbnextrow()를 완전히 처리하지 않는 등의 문제로 인해 사용자 데이터베이스에서 트랜잭션이 중단되면 tempdb에서의 트랜잭션이 열린 상태로 남게 되어 tempdb 로그 잘라내기를 방해할 수 있습니다. 프로그래머는 응용 프로그램을 디버깅하고 이 문제를 유발한 동시성 문제를 해결해야 합니다.
SQL Server 7.0 및 2000 클래스 서버에서 트랜잭션 로그 잘라내기는 가상 로그 파일(VLF)을 잘라냄으로써 수행됩니다. 활성 로그의 일부분이 특정 VLF에 있으면 해당 VLF를 잘라낼 수 없습니다. 활성 로그가 모든 VLF에 있으면 해당 로그를 잘라낼 수 없습니다. autogrowth가 사용 가능하게 설정되어 있고, 트랜잭션 로그가 있는 볼륨에 공간이 있으며 최대 파일 크기에 도달하지 않았으면 트랜잭션 로그는 로그 파일 등록 정보에 지정된 크기까지 커집니다.
아래에서는 TRUNCATE LOG ON CHECKPOINT의 설정 여부에 따라 SQL을 시작할 때의 로그 잘라내기 동작을 설명합니다.
TRUNCATE LOG ON CHECKPOINT를 설정했고 시작할 때 로그가 가득 차있으면 no_log를 사용하여 로그가 자동으로 덤프됩니다.
마스터 데이터베이스의 로그를 별도의 장치에 넣으면 로그가 로드될 수 없기 때문에 이제 마스터 데이터베이스에서는 TRUNCATE LOG ON CHECKPOINT가 기본값입니다. 가능한 옵션은 로그가 가득 찼을 때 삭제하는 것 뿐입니다.
TRUNCATE LOG ON CHECKPOINT를 설정하지 않았고 시작할 때 로그가 가득 차지 않았으면 복구는 완료되지만 최종 검사점(Checkpoint)이 작성되지 않습니다. 관리자는 데이터베이스에 액세스하여 no_truncate로 로그를 덤프하여 데이터를 저장한 다음 no_log로 덤프하여 로그를 제거하거나 덤프 작업 없이 바로 제거할 수 있습니다.

Posted by 시스템매니아
,
1.윈도가 반드시 137기가 보다 적은 파티션에 먼저 설치되어야 합니다.
2.메인보드 바이오스에서 48-bit LBA 모드를 지원해야 합니다.
3.당연하지만 137기가 이상의 하드 디스크가 있어야 합니다.
4.레지스트리에서 다음 값을 설정하여야 합니다.
-시작-실행-regedit 엔터
-HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Atapi\Parameters
를 찾아갑니다.
-Parameters 에서 마우스 오른쪽 클릭하여 `새로만들기-DWORD`로
`EnableBigLba` 를 만들고 값은 십진수로 `1`을 설정하고
윈도우를 재부팅합니다.
5.48-bit LBA 모드는 서비스 팩 2 이상에서 지원되며,
서비스 팩 3 혹은 그 이후버전의 서비스팩을 설치하였다 하더라도
여전이 위의 레지스트리 값을 수정해 주어야 합니다.

-주의사항-
1.만약 137기가 이상이 정상적으로 지원되는 상태에서 위의 레지스트리
값이 삭제되면 하드 디스크의 데이터가 깨어질 수 있습니다.
2.137기가 이상을 지원하는 시스템에서 미리 포맷되어 데이터가
저장된 하드디스크를 새로운 시스템에서 정상 사용하기 위해서도
위의 레지스트리 수정이 필요합니다.

Posted by 시스템매니아
,

MS-SQL table 복사

MS-SQL 2008. 8. 8. 11:01
전체 table복사

insert into aaa_new
select * from aaa

조건 이용 부분 table복사
insert into aaa_new
select top 34500 * from aaa where date_rec >= '2003-08-01' order by date_rec
Posted by 시스템매니아
,

개체소유자변경
-해당 DB에 테이블의 소유자는 전부 dbo로 변경됨
SP_MSFOREACHTABLE 'sp_changeobjectowner ''?'' , ''dbo'''

Posted by 시스템매니아
,

Collaction 정보역시 마찬가지 인데요 이것은, SQL Server의 기본 데이터 정렬을 설정하는 옵션 입니다. MSSQL 을 설치할 때 대부분 Korean_Wansung_CI_AS 으로 설정을 하게 됩니다. 이것의 의미는 "한국어_완성형 문자" 라는 의미 입니다.

테이블에 데이타를 입력할경우, 특정칼럼 기준으로 정렬할경우 완성형으로 비교 판단 하게 되겠지요.

SELECT * FROM ::fn_helpcollations()
GO

쿼리분석기에서 위의 명령을 내리면, MSSQL에서 지원하는 Collaction 정보들이 나오게 됩니다.

Collaction 정보를 변경하고자 한다면,

ALTER DATABASE CollateDb
COLLATE Korean_Wansung_CI_AI
GO

이렇게 하시면 됩니다.

확인은 아래처럼 하시구요...

EXEC sp_helpdb 'CollateDb'
GO

이렇게 했을때 오류가 발생할 수도 있습니다.

"칼럼 'xxx'는 데이터베이스 데이타 정렬에 종속되어 있습니다."

위의 오류가 나타나면, 데이터베이스에 있는 개체들이 데이터베이스의 정렬에 종속되어 있는 경우에 발생되는 오류입니다. 따라서 해당 개체들을 다른 Collation으로 변경을 한 다음에 데이터베이스 Collation으로 변경을 하셔야 합니다.

아래는 Collaction 의 변경에 대한 구문입니다.

Korean_Wansung_CI_AI  으로의 변경

ALTER TABLE 테이블명
ALTER COLUMN 칼럼명 VARCHAR(50) COLLATE Korean_Wansung_CI_AI
GO

ALTER TABLE 테이블명
ALTER COLUMN 칼럼명 VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS 
GO

sp_help 테이블명 을 통해서 결과를 확인해 보시면 되겠습니다.

만약  아래와 같은 메시지가 나왔다면,

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

서버: 메시지 5074, 수준 16, 상태 8, 줄 1
개체 'pk_테이블명은'은(는) 열 '칼럼명'에 종속되어 있습니다.
서버: 메시지 4922, 수준 16, 상태 1, 줄 1
하나 이상의 개체가 이 열을 액세스하기 때문에 ALTER TABLE ALTER COLUMN '칼럼명'이(가) 실패했습니다.

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

온라인 설명서에서 "데이터 정렬 변경"이라는 제목의 문서를 보시기 바랍니다.

애러의 원인은 다음과 같습니다.

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

현재 다음 항목에서 참조하는 열의 데이터 정렬은 바꿀 수 없습니다.

계산된 열
인덱스
자동으로 또는 CREATE STATISTICS 문에 의해 생성된 배포 통계
CHECK 제약 조건
FOREIGN KEY 제약 조건
------------------------

 기본키 제거 -> Collation 변경 -> 기본키 재생성 의 순서에 따라서 Collation의 정보를 변경 하시면 됩니다.

Posted by 시스템매니아
,

select table_name = convert(varchar(30), min(o.name)), table_size = ltrim(str(sum(reserved) * 8192 / 1024.,15,0) + 'KB')
from sysindexes i inner join sysobjects o on (o.id = i.id)
where i.indid in (0, 1, 255) and o.xtype = 'U'
group by i.id
go

Posted by 시스템매니아
,
DNS백업시 %SystemRoot%\system32\dns폴더와 레지스트리의 DNS Zones을 백업하게 되는데

Windows2000과 Windows2003의 레지스트리의 DNS Zones 위치가 다릅니다.

Windows2000 :

[\HERY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\DNS\Zones]

Windows2003 :

[\HERY_LOCAL_MACHINE\SOFTWARE\Microsoft\WindowsNT\CurrentVersion\DNSServer\Zones]
Posted by 시스템매니아
,

C:\boot.ini파일 수정

/fastdetect /3GB /Userva=3030

Posted by 시스템매니아
,
Windows2000의 IIS5.0에서 TEST되었음.

브라우저의 오류 메시지:
HTTP 500 - 내부 서버 오류
또는
Server Application Error
The server has encountered an error while loading an application during the processing of your request. Please refer to the event log for more detail information. Please contact the server administrator for assistance.

시스템 이벤트 로그의 오류 메시지:
원본: DCOM
오류: DCOM에 "로그온 실패: 알 수 없는 사용자 이름이거나 암호가 틀립니다." 오류가 발생했으며 서버를 실행하기 위해 .\IWAM_SERVERNAME에 로그온하지 못했습니다.

원본: W3SVC
오류: 서버가 '/LM/W3SVC/1/Root/op' 응용 프로그램을 로드하지 못했습니다. 오류는 '구성된 식별자가 올바르지 않아 서버 프로세스를 시작하지 못했습니다. 사용자 이름과 암호를 점검하십시오.'입니다.
또는
원본: W3SVC 오류: "서버가 '/LM/W3SVC/4/Root/' 응용 프로그램을 로드하지 못했습니다. 오류는 'c000003b'입니다."

원본: W3SVC 오류: "COM 응용 프로그램 '{3D14228D-FBE1-11d0-995D-00C04FD919C1}'('/LM/W3SVC/4/Root')이(가) Out of Process를 활성화하지 못했습니다."

1. C:\Inetpub\AdminScripts\Adsutil.vbs파일을 Notepad로 엽니다.
2. 편집 메뉴에서 찾기를 누르고 IsSecureProperty = True를 입력한 후 다음 찾기를 누릅니다.
3. "IsSecureProperty = True"를 "IsSecureProperty = False"로 변경합니다. 저장후 닫음
4. 도스창에서 IUSR 계정 암호 알아내기 cscript.exe adsutil.vbs get w3svc/anonymoususerpass
5. 도스창에서 IWAM 계정 암호 알아내기 cscript.exe adsutil.vbs get w3svc/wamuserpass
6. 내컴퓨터-관리-시스템도구-로컬사용자 및 그룹 - 사용자로 이동
7. IUSR계정과 IWAM계정의 패스워드를 위에서 확인한 패스워드로 수정하여줍니다.
8. 서비스에서 IIS Admin Service 재시작합니다
Posted by 시스템매니아
,

출처 : dbguide.net 에 올려진 SSA 성대중 책임 컨설턴트 님의 글

해결! DB D&A>> 중소기업을 위한 SQL Server 기본 모니터링 - 3

악성 쿼리 진단

 

연재 순서

1 시스템 진단(백업 시스템 현황, 디스크 현황)  /  2 서버 점검 운영 진단  /  3 쿼리 진단(악성 쿼리) 기타 논의 사항

 

4. 쿼리 진단

<전문가> 애플리케이션 성능의 경우 애플리케이션에서 데이터베이스로 호출할 문제가 있는데, 부분은 파워빌더로 개발하면서 오류가 발생한 같다. 이것은 직접 수정하기는 어려운 상황이니까, 쿼리 상에서 오류점이 있는지 살펴보고 수정을 하도록 하겠다.

<전문가> attention 되는 부분을 중심으로 살펴보면, 테이블 디자인이 잘못 되어 있는 것이 있다. tb_szd20 테이블의 경우, 모든 컬럼이 varchar(510)으로 설정되어 있다. 물론 실제 저장된 데이터는 이보다 작은 값으로 저장되어 있지만, 만약 전체 칼럼의 합이 8000Bytes 이상이 되면, 저장시 오류가 발생한다. 부분은 개발과정에서 담당자의 실수로 인한 것으로 판단되기 때문에, 적절하게 수정되어야 한다. 또한 인덱스를 생성하기 위해서도 컬럼의 길이를 조정해야 필요가 있다. PGM_ID 칼럼을 varchar(10) 변경하고, 인덱스를 추가하면 성능에 도움이 것이다.

해결 쿼리 1>>

select PGM_ID, PGM_NM FROM comm.dbo.tb_szd20 WHERE pgm_id ='saa02'

 

증상)

테이블 스캔 발생

 

문제원인)

PK 없음, 데이터모델링 오류(모든 칼럼이 VARCHAR(510)으로 되어 있음)

 

문제해결)

PK 추가

alter table tb_szd20 add constraint PK_tb_szd20 PRIMARY KEY (PGM_ID)

 

칼럼 데이터형식 변경

alter table tb_szd20 alter column PGM_ID varchar(10) not null

alter table tb_szd20 alter column PGM_NM varchar(50) not null

 

<전문가> 출하 업무 부분에서 출하 부분(수주 번호) 인덱스를 가지고 있는데, 검색시 날짜 조건을 요구하다 보니 프로세싱이 원활하지 않다. 검색 조건을 지원하기 위한 인덱스를 추가해야 한다.

<신청자> 프로그램에서 검색 목록을 선택할 , 어떤 목록을 선택하느냐에 따라 속도가 많이 차이 난다.

해결 쿼리 2>>

SELECT  v11.SAL_DT             AS            SAL_DT,

                                                     v11.SAL_NO            AS            SAL_NO,

                                                     v11.CUST_CD         AS            CUST_CD,

                                                     … (중간생략)

                                                     v11.REMARK           AS            REMARK

FROM      TB_SAA20               v11,

                                                     TB_SZA10                v12

                   WHERE v11.SAL_DT             >=                               '20070501'

                     AND   v11.SAL_DT             <=                               '20070521'

                     AND   v11.CUST_CD         LIKE         RTRIM(LTRIM('')) + '%'

                     AND   v11.CUST_CD         *=                               v12.CUST_CD

ORDER BY               v11.SAL_NO DESC

 

증상)

테이블 스캔 발생

 

문제원인)

검색 조건에 지원하기 위한 인덱스 누락(SAL_NO 에만 인덱스 존재)

 

문제해결)

인덱스 추가

create index IX_SAL_DT ON TB_SAA20(SAL_DT,CUST_CD)

 

<전문가> 현재 시스템에서 가장 많은 I/O 발생시키는 쿼리를 찾기 위해, 프로필러에서 Read 컬럼이 1000보다 쿼리를 추적하였다. 가장 문제가 되는 쿼리를 보면, ITEM_NAME() 이라는 사용자정의 함수를 SELECT 절에서 반복 호출하고 있다. 또한 ITEM_NAME() 사용자정의함수의 내부 코드를 분석한 결과, 쿼리를 지원하기 위한 인덱스가 전혀 없는 상태이고, 테이블의 칼럼의 데이터형도 잘못 설정되어 있는 상태이다.

<신청자> 거래처 공통 코드 불러오는 부분이다.

<전문가> 잘못된 칼럼 길이를 수정해서라도, 인덱스를 만들어 주면 성능을 개선할 있다. TB_SZC10 테이블의 GRP_CD 칼럼과 KND_CD 칼럼에 인덱스를 생성해 주면 된다.

해결 쿼리 3>>

SELECT  v11.SAL_NO                              AS            SAL_NO,

                  v11.SAL_SQ                              AS            SAL_SQ,

                  v11.SAL_DT                              AS            SAL_DT,

                  … (중간생략)

                  SALE.DBO.ITEM_NAME(V11.ITEM_CD, '1')          AS            STY_NM,

                  RTRIM(LTRIM(SALE.DBO.ITEM_NAME(V11.ITEM_CD, '2'))) + ' * ' +

                  RTRIM(LTRIM(SALE.DBO.ITEM_NAME(V11.ITEM_CD, '3')))                 AS            ITEM_SIZE,

                  SALE.DBO.ITEM_NAME(V11.ITEM_CD, '4')                            AS            COLO_NM,

                  SALE.DBO.ITEM_NAME(V11.ITEM_CD, '5')                            AS            GRAD_NM,

                  … (중간생략)

                  v11.REMARK                             AS            REMARK

  FROM  TB_SAA21               v11,

                  TB_SZA10                v12

                   WHERE v11.SAL_NO            =                                 '200705020003'

                     AND   v11.CUST_CD         *=                               v12.CUST_CD

  ORDER BY           v11.SAL_NO,

                                                     SUBSTRING(v11.ITEM_CD, 1, 1) +

                                                     SUBSTRING(v11.ITEM_CD, 12, 2) +

                                                     SUBSTRING(v11.ITEM_CD, 2, 10)

 

증상)

과도한 IO 발생

 

문제원인)

SELECT 절에 반복적으로 사용되는 dbo.ItemName() 사용자정의함수의 문제

 

문제원인)

PK 없음, 데이터모델링 오류(모든 칼럼이 NVARCHAR(510)으로 되어 있음)

 

문제해결)

칼럼 데이터형식 변경

alter table TB_SZC10 alter column GRP_CD nvarchar(4) not null

alter table TB_SZC10 alter column KND_CD nvarchar(20) not null

PK 추가

alter table TB_SZC10 add constraint PK_TB_SZC10 PRIMARY KEY (grp_cd,knd_cd)

 

성능이 20 이상 개선됨

개선사항>> 현재 데이터베이스에 있는 데이터 모델에 칼럼이 시스템 설계서와 다르게 구성되어 있는 부분도 많고, 인덱스 전략도 누락된 부분이 있는 것으로 판단된다. 또한 불필요한 사용자정의 함수의 반복호출로 인해 과도한 IO 발생하고 있다. 이에 대해서는 시간을 가지고 접근해야 하는 상황으로 보인다. 우선, 월말 결산 시점의 쿼리를 수집하여 추가적인 점검을 해야 하는 상황이라고 판단된다. 이를 위해 템플릿으로 5월말 결산시점의 쿼리를 수집하여 결과를 전달해 주기를 요청했다.

 

5. 기타 논의 사항

<전문가> 문제 상황을 진단하는 방법에 대해서는 일단 설명을 했지만, 문제 상황을 파악한 이를 직접 해결하는 것은 좀더 교육이 필요할 같다. 일단 비전문가이지만, 업무를 맡은 이상 기본적인 교육을 받고, 유지 관리를 하는 것이 바람직할 같다.

<신청자> 그래도 점이 고민이 된다. DBA 없는 상황에서, 교육을 받는 것이 좋을지, 아웃소싱을 하는 것이 좋을지.

<전문가> 아웃소싱을 하더라도 회사 내에 DB 대한 관리를 있는 주체는 필요하다. 아웃소싱으로 들어오는 인력들이 내부 담당자만큼 업무를 파악하거나 설계상의 문제까지 고려해서 관리해주긴 힘들다. 적어도 어떤 문제가 있고, 이것이 어떤 방법들로 해결될 있는지를 아웃소싱 담당자와 협의해서 결정할 있는 내부의 주체는 필요하다. 그러려면 아웃소싱을 하더라도 기본적인 교육을 통한 지식 획득이 필요하다고 본다.

<신청자> 다른 회사들은 주로 어떻게 관리하나. DBA 있나?

<전문가> 몇몇 대기업을 제외하곤, DBA 있는 경우가 드물다. DBA 있더라도 해당 업무 DB 전담하는 경우도 별로 없다. 결국 전산 관리자가 DB 관리를 책임지는 경우가 대부분이라고 있다.

<신청자> 어떻게 관리를 하면 좋을지. 평소에 관리할 시간은 부족하고, 그러다 문제가 발생하면 어디부터 손을 대야 할지 막막하다.

<전문가> 얘기한 사항이 사실 상충되는 부분이다. 평소에 관리를 하면, 문제가 생길 것이고, 문제가 생기더라도 어떤 부분이 문제인지 빨리 찾아 해결할 있다. 하지만 평상시에 방치하면, 당연히 문제 지점을 찾기가 어렵다. 지금까지 설명한 순서대로 적어도 2(마감 /) 사전 모니터링을 하면, 원인을 찾기 힘든 문제는 발생하지 않을 것이다.

Posted by 시스템매니아
,

출처 : dbguide.net 에 올려진 SSA 성대중 책임 컨설턴트 님의 글  

해결! DB D&A>> 중소기업을 위한 SQL Server 기본 모니터링 - 2

SQL 서버 설정과 테이블 컬럼 점검

 

연재 순서

1 시스템 진단(백업 시스템 현황, 디스크 현황)  /  2 서버 점검 운영 진단  /  3 쿼리 진단(악성 쿼리) 기타 논의 사항, 전문가 총평

 

2. SQL 서버 설정 점검

2-1. 데이터베이스 관리 기능 점검

<전문가> 애플리케이션 성능 이슈를 제기했는데, 스펙으로 봐선 시스템 상의 문제는 아닌 같다. DB D&A 신청서에는 미처 적지 못한, 성능상의 구체적인 문제점이 무엇이었나.

<신청자> 입출고 관리 프로그램에서 수행되는 테이블 목록 중에서 수불과 마감 프로세스가 느리다. 수불에서 데이터를 가져와서 마감을 하는 프로세스인데, 이때 속도가 많이 느려졌다. 테이블에서 데이터 수집할 때도 느렸었는데 부분은 하드디스크 증설과 윈도우 재설치 후에 좋아졌다.   

<전문가> 그럼 실제 SQL 서버에 접속해서 설정이 제대로 되어 있는지, 쿼리의 문제가 있는 부분이 있는지 같이 하나씩 살펴보자.

<신청자> 데이터베이스는 comm sale 있으며, comm 시스템의 기준정보를 저장하기 위한 목적으로 사용되고, 실제 트랜잭션 정보는 sale 데이터베이스에 저장되어 있다. Sale 데이터베이스는 500MB 크기이며, 전체 복구 모델을 사용하고 있다.

<전문가> 보니까 전체 DB 사이즈가 400MB(데이터베이스 파일 375MB, 로그파일 40MB)이고, 디스크 C 저장하고 있다. 자동축소 기능을 쓰고 있는데, 기능은 해제해야 한다. 자동 축소 기능은, 인터벌 타임마다 용량을 체크해서 여유 공간이 나오면 자동으로 줄여 버린다. 상태에서 다시 데이터가 늘어나면 자동증가 하게 되고 다시 자동증가한 크기가 자동축소 임계값 기준으로 여유공간이 남게 되면 자동축소 되는 과정이 반복되는 문제 발생의 원인이 있다. 또한 자동축소나 자동증가가 일어나는 동안에는 해당 영역에 대해서는 잠금이 설정되기 때문에 I/O 병목현상의 원인이 있다. 실행 DB 환경에는 기능은 해제하고 사용하는 것이 좋다. 기능은 바로 해제하도록 하겠다.

<전문가> 그리고 계정은 sa 계정을 사용하고 있는데, 보안 때문에 별도 관리 계정을 만드는 것이 좋다. 파일 증가를 400MB 제한하도록 설정했는데, 특별한 이유가 있는지.

<신청자> 특별한 이유가 있는 것은 아니고, 인터넷에서 기본 설정 가이드라인이 그렇게 되어 있는 것을 봤다.

<전문가> 데이터는 늘어나기 마련이기 때문에 파일 증가는무제한으로 두는 것이 바람직하다. 현재 시스템환경으로 봤을 , 데이터 파일의 경우 500MB, 로그 파일은 200MB 정도로 늘려주는 것이 좋다. , 백업할 파일 크기를 기억하고 있다가 복원할 , 디스크상에 백업할 당시 공간이 남아 있어야 복원이 가능하다는 것에는 유념해야 한다. 그리고 백업의 경우엔 데이터와 로그 백업을 F 받아서, 데이터 파일이나 로그 원본 파일과는 분리해서 저장하는 좋을 같다.

<신청자> 인터넷으로 보기로는 로그 파일은 트랜잭션 백업을 하면 비워진다고 해서, 작게 잡았다.

<전문가> 작게 잡은 것이 문제가 되는 것은 아니다. 하지만 저장 공간이 많이 여유로운 상황이 아니기 때문에 좀더 탄력적으로 운영할 필요가 있다. 성능 개선을 위해 인덱스 최적화 등을 권고할 예정이어서 여유 공간이 좀더 필요하기도 하다. 실제 월말 마감 작업을 경우에는 전날 정도 로그 파일 백업 용량을 줄여줬다가 마감 후에 늘리는 식으로 탄력적으로 활용하면 도움이 것이다.  

개선사항 1>> sale 데이터베이스에 자동축소(IsAutoShrink) 옵션이 설정되어 있다. 자동축소가 설정되어 있으면, 주기적으로 데이터베이스의 여유공간을 체크하여 설정한 임계값보다 많은 여유공간이 남은 경우, 데이터베이스를 자동으로 축소하게 된다. 이는 데이터가 계속 증가하는 운영 데이터베이스에서는 권장되지 않는 옵션이기 때문에 설정 해제할 것을 권고한다.  

개선사항 2>> 계속 증가하는 데이터베이스이기 때문에 자동 증가가 업무시간에 발생하지 않도록 하기 위해 데이터베이스 파일 크기를 500MB, 로그파일의 크기를 200MB 정도로 충분히 늘려주는 것을 고려해야 한다.

개선사항 3>> 최대 파일 크기를 제한하는 것보다는 무제한으로 설정하고, 파일 크기를 주기적으로 모니터링하여 디스크 공간부족이 발생하는지 여부를 체크하는 것이 바람직하다.

--확인방법

exec sp_helpdb sale

--실행결과

sale              404.88 MB        sa             7 04 16 2007 Status=ONLINE, Updateability=READ_WRITE,

UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=Korean_Wansung_CI_AS,

SQLSortOrder=0,

IsAutoShrink, IsTornPageDetectionEnabled,

IsAutoCreateStatistics, IsAutoUpdateStatistics       80

 

1  C:\Data\MSSQL\Data\sale_Data.MDF    PRIMARY    375168KB     716800KB    102400KB     data only

2  C:\Data\MSSQL\Data\sale_Log.LDF    NULL     39424KB     410624KB     102400KB     log only

 

2-2. 파일 사이즈 점검

<전문가> 현재 파일 사이즈를 보면, 366MB 할당된 데이터 파일 사이즈이고, 306MB 실제 사용되고 있는 것을 확인할 있다.

<신청자> 그럼 지금 너무 많이 사용되고 있는 건가?

<전문가> 할당된 영역 90% 정도 사용되고 있는 상태니까, 여유 공간이 부족하다고 있다. 이런 경우엔 100MB 자동 증가하게 되어 있으니까, 데이터 파일 사이즈를 500MB 정도까지 미리 늘려놓으면 충분할 것이다.

--확인방법

USE SALE

GO

DBCC SHOWFILESTATS

GO

--실행결과

Fileid        FileGroup                     TotalExtents         UsedExtents          Name           

1               1                   5862                  4909                  sale           

 

--계산방법(1 Extent = 8 Pages = 64 KB)

select 5862*8*8/1024. as Reserved

     , 4909*8*8/1024. as Used

, (4909*8*8/1024.)/(5862*8*8/1024.)*100.

 

Reserved                  Used         Usage(%)

366.375000              306.812500     83.7(%)

 

2-3. 테이블 컬럼 점검

<전문가> 사이즈에 대해선 정도 체크를 하고, 테이블을 살펴보도록 하자. 100MB 넘는 테이블이 있는지를 검색해볼 텐데, 별로 없을 같긴 하다. , tb_sfa10(제품 수불) 테이블이 검색되는데, 보면 58 6400건의 데이터가 있고 사이즈가 500MB 된다. 기본적으로 테이블의 전체 데이터 20% 변경되어야 자동통계업데이트가 되기 때문에, 테이블에 저장된 행수가 많아지면 성능에 문제가 있다. DBCC UPDATEUSAGE 명령을 사용하면, sysindexes 저장되어 있는 정보를 최신상태로 업데이트해 준다. 업데이트 결과를 보면, 270MB 낮춰진 것을 확인할 있다. 전에 전체 데이터 사이즈가 400MB인데, tb_sfa10 테이블만 500MB 조회되어서 깜짝 놀랐는데, 이제 정상화가 됐다.

 Tip>> 사용자 지정 메뉴에서 자주 쓰는 명령어를 11개까지 지정할 있다.

<전문가> 여기서 전체 컬럼을 보면 49개나 되고, 행의 크기(전체 컬럼 길이의 ) 524byte 된다.

<신청자> 그럼 데이터페이지 하나에 저장하지 할텐데?

<전문가> 물론 행의 크기가 커지면 데이터베이스 하나에 저장하지 못하기 때문에, 성능에 문제를 있다. 특히 문자열 칼럼의 길이가 실제 필요한 데이터보다 과도하게 설정되어 있는 경우가 많은 것으로 보여진다. 이에 대해서는 성능의 문제가 된다면 수정할 필요가 있다. 현재 3 정도의 신규 데이터가 존재하고 있고. 쿼리를 보면서 다시 살펴보겠지만, 쿼리를 지원하는데 필요한 인덱스가 부족하지 않나 싶다. 부분은 조금 후에 실제 쿼리들을 살펴보면서 인덱스 추가기 필요한 부분은 수정하는 것이 좋을 같다.  

<전문가> 지금 사용되고 있는 현황을 살펴보면, , 근데 아무도 접속이 되어 있네.

<신청자> 오전에 1, 2시간만 사용하고, 오후엔 거의 사용 한다.

<전문가> , 그럼 프로필러를 돌려보자. 프로필러는 익숙하게 사용하나?

<신청자> 지난번에 문제가 있을 프로필러를 알게 됐고, 요즘엔 한번씩 돌려본다. 아직 기능을 많이는 모른다.

<전문가> 기본적인 작업의 50%, 문제를 찾아내는 것의 대부분이 프로필러를 이용해서 이뤄지기 때문에, 툴은 좀더 익숙해지도록 노력할 필요가 있을 같다. 한가지 Tip으로 얘기하면, 금번 작업을 위해, 제공된 템플릿을 사용하면 기본적인 모니터링을 수행할 있다. 앞으로 성능 문제가 있을 때도 이를 실행해서 템플릿을 잡으면, 어떤 부분이 문제가 있는지를 있다. 그리고 데이터베이스ID 대해서 필터를 설정하는 것이 좋다. 1-4까지는 시스템 데이터베이스이기 때문에 5 이상으로 필터조건을 설정하는 것이 효율적일 것이다.

--확인방법

USE SALE

GO

-- 사전에 sysindexes 있는 정보를 최신상태로 업데이트

DBCC UPDATEUSAGE(0)

GO

select object_name(id),name, rows,  rowcnt, rowmodctr, reserved, used, dpages,*

from sysindexes

where id > 100000 and name not like '_WA_Sys%'

order by reserved desc

 

-- tb_sfa10 테이블 정보

테이블명   행수()      할당량(KB)   사용량(KB)   인덱스(KB)   잔량(KB)

tb_sfa10     586,489      270,408        268,704       1,672           32

 

인덱스

tb_sfa10_pk     clustered, unique, primary key located on PRIMARY     CLS_DT,  ITEM_CD,   UNT_QTY

 

칼럼길이합

select sum(length) from syscolumns where id = object_id('tb_sfa10')

524 byte

 

데이터확인

select CLS_DT, count(*) from tb_sfa10 group by CLS_DT

월별로 평균 3 건의 데이터 저장됨

 

3. 운영 진단

<전문가> 이제 유지관리와 관련해서 살펴보자. 백업이 어떻게 되고 있는지 로그를 잠깐 살펴보면, 전체 백업을 2시간마다 수행하고, 1주일간의 백업 데이터를 유지하고 있고, 로그 백업은 10 단위로 하고 있고, 2주일간의 백업 데이터를 유지하고 있다.

<신청자> 인터넷에 보니까, 전체 백업을 1시간에 한번, 10분에 한번씩 한다는 사람들이 많았다. 그래서 이렇게 자주해야 할까 하면서도, 간격을 줄였다. 개발자가 요청해서, 테이블의 데이터를 텍스트 파일 형태로 내보내기 하고 있다.

<전문가> 지금 설정된 것은, 너무 빈번하게 백업을 하고 있는 같다 또한, 백업할 때마다 DBCC CHECKDB 명령이 자동 실행되도록 설정되어 있는 상태이다. 이는 사용자 쿼리에 비해, 유지보수 작업을 위한 DBCC CHECKDB 명령을 위한 작업 부하가 오히려 많은, 배보다 배꼽이 상황이 같다.

<신청자> 처음엔 1시간에 1번씩 백업을 받았었다. 그러다 보니까 백업 용량이 디스크를 채워서, 그나마 2시간으로 늘린 것이다. 

<전문가> 불필요하게 자주 백업을 받고 있다. 전체백업을 1/1, 로그백업을 1/1시간으로 조정해도 충분할 것이다. 백업과 인덱스 재생성 작업을 점심시간에 하는 것은 이유가 있는 것인가. 보통은 야간에 작업을 하는데.

<신청자> 야간엔 사용자가 없기 때문에, 서버를 중단(Shutdown)한다. 오전에만 잠깐 사용자가 몰리기 때문에, 보통 점심 시간에 사용자가 없어서 1시간 정도씩 진행을 한다.  

개선사항 1>> 불필요하게 자주 백업을 진행하고 있으며, 백업을 수행할 때마다 DBCC CHECKDB 통해 시스템의 일관성 검사를 반복 수행하고 있어서, 현재 데이터베이스에 수행되는 쿼리 가장 많은 부하를 발생시키는 쿼리가 되고 있다. 이를 개선하기 위해 전체 백업은 1 1, 로그 백업은 1시간 간격으로 조정할 것을 권고한다.

개선사항 2>> 또한 DBCC CHECKDB 백업시마다 수행할 필요는 없다고 판단돼, 데이터베이스 유지관리 마법사의 해당 옵션은 해제할 것을 권고한다. 또한, 현재 백업본의 관리기간이 서로 다르기 때문에 1주로 통일하면 백업본을 관리하기 위한 디스크 공간을 절약할 있다.

개선사항 3>> 인덱스 재생성 작업은 사용자의 접근을 제한하기 때문에, 향후 데이터베이스의 크기가 커지거나 사용자가 늘어나면 별도의 유지관리 시간을 야간 시간으로 조정하여 수행할 것을 권고한다. 또한 tb_sfa10 테이블과 같은 대용량 테이블에 대해서는 수동 통계 업데이트 정책도 적용할 필요가 있다.

Posted by 시스템매니아
,

출처 : dbguide.net 에 올려진 SSA 성대중 책임 컨설턴트 님의 글

해결! DB D&A>> 중소기업을 위한 SQL Server 기본 모니터링 -1  

백업과 디스크 현황 점검부터 시작

도스코 영업부의 송구현 주임은 비전산 담당자지만, 약 1년 전부터 사내 입출과 관리 프로그램의 관리를 맡고 있다. 최근 성능 저하 현상이 나타나는 애플리케이션에 어떤 문제가 있는지, 기본적으로 SQL Server를 유지 관리하는 방법이 무엇인지를 몰라 답답하던 상황이다. SSA(SQL Server Academy)의 성대중 책임 컨설턴트가 직접 안산 반월공단의 도스코를 방문해 기본적인 SQL Server 설정부터 쿼리 분석, 인덱스 추가에 이르는 방법론을 제시했다.

  • 일시: 2007 5 25
  • 신청자: 도스코 영업부 송구현(song92) 주임
  • 전문가: SSA 성대중 책임 컨설턴트

 




요청사항

1. 애플리케이션 성능 점검 - 프로그램 오류인지 DB 부하 문제인지 검토(DB 문제일 경우 권고안 제시)

2. 데이터베이스 유지관리 - DB 점검 관리 항목과 방법 제안

 

연재 순서

1 시스템 진단(백업 시스템 현황, 디스크 현황)  

2 서버 점검 운영 진단  

3 쿼리 진단(악성 쿼리) 기타 논의 사항, 전문가 총평

 

                 

<전문가> 영업부 주임이신데, 어떻게 SQL 서버 관리를 맡고 있나? 

<신청자> 원래 개발자가 있었는데, 담당자가 2006년에 사내 입출고 관리 프로그램을 개발한 후에 장기간 해외 근무를 하고 있다. 가지 가이드라인이나 조언을 주긴 하는데, DBA 전산 담당자 없이 영업부 소속의 비전문가가 관리하다 보니 어려움이 크다.

<전문가> 구체적으로는 주로 어떤 문제점이 발생했나?

<신청자> 사실 가장 문제점은 지금 제대로 운영되고 있는지 조차 불안하다는 것이다. 업무 시스템이 월말 마감 외에는 오전에 1, 2시간만 주로 사용되는, 사실 그렇게 사용량이 많은 편이 아니다. 그런데도 HDD 용량을 미처 체크하지 못해서 다운된 적이 있었고, 백업을 해놓은 상태여서 복구하는데 어려움이 있었다.

<전문가> 문제가 발생했을 이제까지는 주로 어디를 통해 정보를 얻었었나?

<신청자> 인터넷이다. 네이버카페를 주로 이용했고, 최근에 DBGuide.net 알게 됐다. 하지만 정보들이 지금 우리 시스템 환경과 맞지 않고, 비전문가이다 보니 적용 또는 응용하는 것도 쉽지 않다.

 

1. 시스템 진단

1-1. 서비스팩/패치 점검

<전문가> 우선 SQL Server 운영 상황에 대한 진단을 하고, 요청했던 애플리케이션 성능 점검과 데이터베이스 유지 관리 방안을 전달하는 것이 좋을 같다. 사전에 모니터링 툴을 전달해서, 그에 대한 결과 값을 받았으나 서비스팩과 누적핫픽스가 적용되지 않은 이전 버전(2000.80.194RTM)이어서 모니터링 툴이 제대로 값을 산출하지 못했다. 어차피 DB 관리의 기본 작업이진단 있기 때문에, 우선 진단 과정부터 함께 천천히 살펴보는 것이 좋겠다.

<신청자> 성능에 문제가 있어서 얼마 전에 윈도우 환경을 포맷한 , 패치와 서비스팩 업데이트를 잊고 있었다. 그래도 어제 패치를 업데이트하고, 서비스팩도 SP4(2039) 버전으로 업그레이드했다.

개선 사항>> 최근 Rollup 패키지인 2187 버전의 HotFix 추가로 설치해야 하는 상태이며, 아래의 주소를 참조해 다운로드 받아서 설치할 있다.

http://www.microsoft.com/downloads/details.aspx?displaylang=ko&FamilyID=9c9ab140-bdee-44df-b7a3-e6849297754a

--확인방법

select @@version

--실행결과

Microsoft SQL Server 2000 - 8.00.2039(Intel X86)  May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack )

*<주의> 서비스팩의 적용 HotFix 적용은 기존 애플리케이션의 동작에 영향을 미칠 있기 때문에 운영환경에 적용하기 전에 반드시 테스트 환경에서 테스트되어야 한다.

 

1-2. 백업 시스템 현황 점검

<전문가> 그럼 같이 상황 점검을 해보자. 백업은 어떻게 하고 있는지. 유지관리 메뉴에 보면, 전체 백업과 트랜잭션 로그 백업이라는 2가지 옵션이 있는데 어떻게 사용하고 있나.

<신청자> 테이블 백업을 텍스트로 하고 있다. 개발자가 원하는 사항이다. 그리고 정확한 주기는 설정한지 오래 되어서, 기억이 나지만 전체 백업과 트랜잭션 로그 백업 모두 시행하고 있다.

<전문가> 그럼 복구 모델은 전체 복구 모델을 사용하고 있는 상태이고, 사용 환경은 하이퍼쓰레딩이 가능한 CPU 2개로, 논리적으로는 4개로 있고, 메모리는 1GB. 여유공간이 몇백MB 있는 것으로 봐서 메모리가 부족한 상태는 아니다.

개선사항>> 향후 시스템의 리소스가 부족하게 되면 메모리는 추가할 필요가 있는 상태라고 판단된다. 참고로, 현재 SQL Server 2000 Standard Edition 사용하고 있기 때문에 SQL Server 최대 2GB까지 메모리를 할당할 있다.

--확인방법

Exec master..xp_msver

--실행결과

1               ProductName          NULL       Microsoft SQL Server

2               ProductVersion        524288    8.00.2039

3               Language                 1042        한국어

4               Platform  NULL       NT INTEL X86

5               Comments               NULL       NT INTEL X86

6               CompanyName      NULL       Microsoft Corporation

7               FileDescription        NULL       SQL Server Windows NT

8               FileVersion               NULL       2000.080.2039.00

9               InternalName          NULL       SQLSERVR

10             LegalCopyright       NULL       1988-2004 Microsoft Corp. All rights reserved.

11             LegalTrademarks   NULL       Microsoft?? is a registered trademark of Microsoft Corporation.

12             OriginalFilename    NULL       SQLSERVR.EXE

13             PrivateBuild             NULL       NULL

14             SpecialBuild             133627904              NULL

15             WindowsVersion     248381957              5.2 (3790)

16             ProcessorCount      4               4

17             ProcessorActiveMask              15             0000000f

18             ProcessorType        586           PROCESSOR_INTEL_PENTIUM

19             PhysicalMemory     1023        1023 (1073164288)

20             Product ID                NULL       NULL

 

1-3. 디스크 운영 환경 점검

<전문가> 그러면 하드디스크는 어떻게 사용하고 있는지.

<신청자> 디스크 서브시스템은 현재 RAID5 구성된 C,D 드라이브와, 최근에 공간 부족 문제를 해결하기 위해 추가한 E,F 드라이브로 구성되어 있다. 현재 C 드라이브에 데이터베이스 파일 로그 파일이 저장되어 있으며, D 드라이브에는 스크립트 기반으로 테이블을 텍스트 파일로 내보내기 파일을 저장하고, E 드라이브에는 백업 파일을, F 드라이브에는 기타 관련 파일을 저장하고 있다

<전문가> 기본적으로 디스크는 OS 위한 공간, 데이터베이스 파일을 위한 공간, 로그 파일을 위한 공간, 백업 파일을 위한 공간으로 분리해서 사용하는 것이 일반적인 권고사항이다. 가능하다면, 물리적으로 분리된 디스크 상에 위치하는 것이 바람직하며, 특히 데이터베이스 파일 로그 파일과 백업파일은 별도의 디스크에 위치시키는 것이 좋다. 왜냐하면, 디스크 장애발생시에 데이터베이스 관련 파일과 백업 파일이 같은 디스크상에 존재하면, 복구할 없기 때문이다. 현재로서는 사용량이 많지 않아서 괜찮을 있지만, 이후 점검 후에 디스크 용량 조정이 필요하다면 조치를 취해야 같다.

개선사항>> 현재로서는 사용량이 많지 않기 때문에, 특별한 조치가 필요한 상태는 아니다. 하지만 현재 시스템 볼륨(운영체제가 설치된 볼륨) C 드라이브에 데이터베이스 파일과 로그 파일이 함께 저장되어 있다. 이를 별도의 드라이브로 분산시키면 디스크의 병목현상을 해결할 있다. 또한 가능하다면, 데이터베이스 파일과 로그 파일도 분리하는 것이 바람직하다.

--확인방법

exec master..xp_fixeddrives

--실행결과

C               20266 - Data/Log

D               26685

E               17623 - Backup

F               4570


Posted by 시스템매니아
,
데이터베이스의 Collation 정보를 말합니다.

MSSQL DBMS 는 WINDOWS OS 와 상호작용을 하여 처리하는 경우가 많이 있습니다.

Collaction 정보역시 마찬가지 인데요 이것은, SQL Server의 기본 데이터 정렬을 설정하는 옵션 입니다. MSSQL 을 설치할 때 대부분 Korean_Wansung_CI_AS 으로 설정을 하게 됩니다. 이것의 의미는 "한국어_완성형 문자" 라는 의미 입니다.

테이블에 데이타를 입력할경우, 특정칼럼 기준으로 정렬할경우 완성형으로 비교 판단 하게 되겠지요.

SELECT * FROM ::fn_helpcollations()
GO

쿼리분석기에서 위의 명령을 내리면, MSSQL에서 지원하는 Collaction 정보들이 나오게 됩니다.

Collaction 정보를 변경하고자 한다면,

ALTER DATABASE CollateDb
COLLATE Korean_Wansung_CI_AI
GO

이렇게 하시면 됩니다.
Posted by 시스템매니아
,
MS-SQL서버의 성능을 모니터링할수 있는 각 성능카운터에대한 설명입니다.

Processor\%Processor Time : % Processor Time은 프로세서가 비유휴 스레드를 실행하는 데 소비하는 시간의 백분율입니다. 이것은 프로세서가 각 샘플 간격 동안 유휴 스레드를 실행하는 데 소비한 시간을 측정하여 간격 기간에서 그 값을 뺀 것입니다. 각 프로세서에는 유휴 스레드가 있는데 이것은 다른 어떤 스레드도 실행되지 않을 때 사이클을 소비하는 스레드입니다. 이 카운터는 프로세서 동작의 주요 표시기이며 샘플 간격 동안 관찰되는 사용 시간의 평균 백분율을 표시합니다. 이것은 서비스가 비활성인 시간을 모니터링하여 100%에서 그 값을 뺀 것입니다.
즉 쉽게 말해서, 1시간 동안 CPU상태를 관찰하였고, 1시간 중에서 30분간 CPU가 일을 하였다면, %Processor Time은 50% 입니다. 일반적으로 %Processor Time이 80%이상 사용되고 있으면, 관심을 가지고 사용량의 변화를 모니터 하셔야 합니다.

Processor\%Privileged Time : % Privildged Time은 프로세스 스레드가 특권 모드에서 명령을 실행하면서 경과된 시간을 백분율로 표시한 것입니다. Windows 시스템 서비스가 호출되면, 서비스는 시스템 전용 데이터를 액세스하기 위해 흔히 특권 모드에서 실행됩니다. 그러한 데이터는 사용자 모드에서 실행되는 스레드가 액세스하지 못하도록 보호됩니다. 시스템 호출은 페이지 오류 또는 인터럽트가 발생할 때처럼 명시적이거나 암시적입니다. 일부 초기 운영 체제와는 달리 Windows는 사용자 및 특권 모드의 일반적인 보호뿐만 아니라 하위 시스템을 보호하기 위해 프로세스 경계를 사용합니다. 응용 프로그램을 대신하여 Windows에서 수행한 일부 작업은 프로세스의 특권 시간 및 다른 하위시스템 프로세스에서도 나타납니다. 쉽게 말해서, 운영체제가 사용한 시간의 백분율 값입니다.

Memory\Available Bytes : Available Bytes는 컴퓨터에서 실행되는 프로세스에 사용할 수 있는 실제 메모리의 양(바이트)입니다. 이것은 0으로 채워 있거나 비어 있거나 대기 중인 메모리 목록에 있는 공간을 합해 계산합니다. 빈 메모리는 사용할 준비가 된 메모리이고, 0으로 채워진 메모리는 다음 프로세스가 이전 프로세스에서 사용된 데이터를 보지 못하도록 0으로 채운 메모리 페이지로 구성되며, 대기 메모리는 프로세스의 작업 집합(실제 메모리)에서 제거되어 디스크로 라우트되었지만 다시 호출되어 사용될 수 있는 메모리를 말합니다. 이 카운터는 최근에 관찰된 값만 표시하며 평균값은 아닙니다. 사용 가능한 여유 메모리는 많이 있으면 좋겠지만, 그렇지 않다면, 최소한 5Mbyte이상은 남아 있어야 합니다.

Memory\Page Faults/sec : Page Faults/sec는 프로세스가 사용하는 메모리 공간(Working Set)에 존재하지 않는 코드나, 데이터를 요청할 경우에 발생합니다. 이 때 요청된 코드나 데이터를 다른 메모리 공간에서 찾으면 Soft Page Fault라고 하며, 디스크에서 찾게되면 Hard Page Fault라고 합니다. Page Faults/sec은 초당 발생한 Soft Page Fault와 Hard Page Fault의 합입니다.
Soft Page Fault값은 Page Faults/sec값에서 Pages/sec값을 빼면 됩니다.
따라서,  Page Faults/sec의 값은 작을수록 좋다고 말 할 수 있겠으며, 상황에 따라서 그 임계치는 다르므로, 평상시에 모니터하여 적정 값을 파악해 두셔야 합니다.

Memory\Pages/sec : Pages/sec는 하드 페이지 부재를 해결하기 위해 디스크에서 읽거나 디스크로 쓴 페이지의 비율입니다. 이것은 Memory\\Pages Input/sec과 Memory\\Pages Output/sec의 합입니다. 메모리 공간이 부족하게 되면, 디스크의 페이징 파일로 메모리의 내용을 옮겨서 메모리의 여유공간을 확보하여 사용하게 되며, 또 필요시 페이징 파일에서 데이터를 메모리로 로드하여 처리하는 과정을 반복하게 되므로 성능이 저하되게 됩니다.
Pages/sec 카운터 값이 20이상 지속되면 메모리 이상을 의심해야 합니다.

PhysicalDisk\%Disk Read Time : 디스크가 읽기 작업을 수행한 시간의 백분율입니다.

PhysicalDisk\%Disk Write Time : 디스크가 쓰기 작업을 수행한 시간의 백분율입니다.

PhysicalDisk\%Disk Time : 디스크가 읽기 및 쓰기 작업을 수행한 시간의 백분율이며, 이 값은 Disk Read Time과 Disk Write Time의 합입니다.

PhysicalDisk\Avg. Disk Queue Length : 디스크의 읽기 및 쓰기 작업을 위해 대기중인 실제 디스크 큐 길이 입니다. 이 값은 디스크당 2를 초과하게 되면, 디스크 쪽 부하를 점검해야 합니다.

PhysicalDisk\Current Disk Queue Length : 현재 시점의 디스크 읽기 및 쓰기 작업을 위해 대기중인 디스크 큐 길이 입니다. 이 값 역시 2를 초과하면, 디스크 쪽 부하를 점검해야 합니다.

Network Interface\Current Bandwidth : 네트워크 인터페이스의 현재 대역폭입니다. 사용하는 네트워크 어댑터 카드의 지원 가능 대역이 100Mbps인 경우에 Current Bandwidth값이 10Mbps라면 네트워크 어댑터 카드의 속성 세팅이 잘 못 되었을 가능성이 큽니다.

Network Interface\Packets Outbound Errors : 이 항목은 오류로 인해서 외부로 반출할 수 없는 패킷의 수를 나타냅니다.

Network Interface\Packets Received Errors : 이 항목은 상위 계층의 프로토콜로 전달되지 못하도록 하는 오류를 포함하고 있는 외부로부터 유입되는 패킷의 수를 나타냅니다.

Server\Bytes Total/sec : 이 항목은 초당 서버가 네트워크에서 주고 받은 바이트 수 입니다. 동일 네트워크에 존재 하는 서버들의 각각의 Bytes Total/sec 합이 네트워크 대역폭보다 크다면, 네트워크 분리를 고려하셔야 합니다.

SQLServer:Buffer Manager\Buffer cache hit ratio : SQL서버가 데이터를 디스크에서 읽지 않고 버퍼 풀에서 찾은 페이지 비율입니다. 이 값은 90% 이상 유지되어야 하며, 대부분의 시스템에서  98% 정도 유지 됩니다. 이 값이 90보다 낮다면 버퍼 공간이 부족하다고 판단할 수 있습니다.

SQLServer:Buffer Manager\Page life expectancy : 이 항목은 페이지가 참조되지 않고 얼마나 오랫동안 버퍼에 존재할 수 있는가를 초 단위로 나타냅니다. 이 값이 300초 이하이면, 성능향상을 위해  SQL서버에 추가적인 버퍼 공간이 필요하다고 볼 수 있습니다.

SQLServer:Buffer Manager\Page reads/sec : 모든 데이터베이스에 대해서 발생한 물리적 데이터 페이지의 초당 읽기 수를 나타냅니다. 물리적 I/O는 상대적으로 비용이 많이 발생하므로, 더 큰 데이터 캐시를 사용하거나, 인덱스 및 쿼리를 효율적으로 작성하거나, 데이터베이스 모델링을 다시하여 물리적 I/O비용을 줄여야 합니다.

SQLServer:Buffer Manager\Stolen Page : 윈도우 시스템이 SQL서버가 아닌 다른 응용 프로그램의 요구를 충족시키기 위하여 얼마나 많은 페이지들이 SQL Server 데이터 캐시로부터 제거되었는지를 나타낸다. min Server memory를 지정하여 SQL서버가 지정한 만큼의 SQL서버 전용으로 메모리를 할당하게 할 수 있지만, 그 만큼 다른 프로그램이 적은 메모리로 운영되게 되므로 페이징이 발생하게 됩니다. 따라서 메모리 증설을 고려해야 합니다.

SQLServer:Databases\Log Flushes/sec : 초당 발생한 로그 플러시 수를 나타냅니다. 하나의 로그플러시는 하나의 트랜잭션이 커밋되어 로그 파일에 기록되는 것을 의미 합니다.

SQLServer:Databases\Transactions/sec : 선택한 데이터 베이스에서 발생한 초당 발생하는 트랜잭션 수를 나타냅니다.

SQLServer:General Statistics\User Connections : 시스템에 연결된 사용자 수를 나타냅니다.

SQL Server:Latches\Average Latch Wait Time(ms) : 요청된 래치에 대한 평균 래치 대기 시간(ms)

SQL Server:Latches\Latch Waits/sec : 즉시 서비스 될 수 없고 자원 해제를 위해 대기해야 하는 래치 요청 수

SQL Server: Locks\Average Wait Time(ms) : 리소스에 잠금을 걸기 위해  대기하였던 평균 시간(ms)을 나타냅니다.

SQL Server: Locks\Lock Timeouts/sec : 리소스에 잠금을 얻기 위해 대기하면서 타임 아웃된 잠금 요청 횟수를 나타냅니다.

SQL Server: Locks\Lock Waits/sec : 즉시 충족시킬 수 없고 잠금을 허가하기 위해서 호출자가 기다려야 하는 잠금 요청 수

SQL Server: Locks\Number of Deadlocks/sec : 교착 상태를 일으킨 잠금 요청 수

SQL Server: Memory Manager\Memory Grants Pending : 작업 영역 메모리 허가를 위해 대기하고 있는 현재의 프로세스 수

SQL Server: Memory Manager\Target Server Memory(KB) : SQL Server가 사용할 수 있는 전체 메모리 양

SQL Server: Memory Manager\Total Server Memory(KB) : SQL Server가 사용하고 있는 전체 메모리 양

SQL Server: SQL Statistics\Batch Requests/sec : 초당 요청 받은 SQL 배치 요청 수

SQL Server: SQL Statistics\SQL Compilations/sec : 초당 SQL Server가 SQL문을 컴파일 한 수

SQL Server: SQL Statistics\Re-Compilations/sec : 초당 SQL Server가 SQL문을 재 컴파일 한 수
Posted by 시스템매니아
,

1. 네트워크
네트워크 인터페이스의 추가
서브네트워킹 작업
네트워크 업그레이드
클러스터링방안 및 기가모듈의 사용
 
2. CPU
성능모니터링을 이용한 CPU 모니터링
% Processor Time  - 80%  이하가 정상
% Privileged Time  - 80%  이하가 정상
% User Time   - 80%  이하가 정상
Processor Queue Length - 2 이하가 정상
대책 : 정상수치가 아닐 때 해결방법
     프로세서  추가, 더빠른  프로세서로  교체, 보다많은  캐쉬 확보

3. 메모리
성능모니터링을 이용한 메모리 모니터링
Available Bytes   - 4 메가  이상이 정상
Pages/sec (paging in/out)         - 0~20일 때 정상
Page Reads/sec   - 5  이하가 정상
Page Writes/sec   - 5  이하가 정상
Buffer Cache Hit Ratio  - 90%  이상이 정상
대책 : 정상수치가 아닐 때 해결방법
메모리  추가

4. 디스크
성능모니터링을 이용한 디스크 모니터링
검사항목
% Disk Read Time  - 40%  이하가 정상
% Disk Write Time  - 40%  이하가 정상
% Disk Time   - 40%  이하가 정상
Avg. Disk Queue Length - 2 이하가 정상
대책 : 정상수치가 아닐 때 해결방법
디스크  추가 , RAID  고려

Posted by 시스템매니아
,
MS-SQL2000에서 TEST됨

쿼리분석기 에서 아래 명령줄 실행

sp_password NULL, '바꿀암호', 'sa'
Posted by 시스템매니아
,
Netcraft - Web Server Query Site
: 해당 도메인 혹은 IP 주소가 어떤 O/S 혹은 어떤 웹서버 종류와 버전을 사용 중인지 체크해주는 사이트이다.

메인페이지 상단의 Whats that site running? 뒤에 도메인을 적고 찾기버튼을 누르면됩니다.

http://uptime.netcraft.com/up/graph/

Posted by 시스템매니아
,

Oracle 기본 SQL 사용법

MS-SQL 2007. 9. 23. 21:01
제 1 장 데이터의 검색
SQL 명령어는 다음과 같이 기술한다.
■ SQL 명령어는 한 줄 혹은 여러 줄에 기술한다.
■ 일반적으로 절들은 수정하기 쉽게 다른 줄에 기술한다.
■ TAB 을 사용할 수 있다.
■ SQL 명령어 단어는 생략하거나 분리할 수 없다.
■ SQL 명령어는 대소문자를 구분하지 않는다.
■ SQL 명령어는 ; 으로 종료한다.
■ SQL 명령어는 SQL BUFFER 에 저장된다.
■ SQL BUFFER 에 저장된 SQL 명령어는 / 혹은 RUN 으로 실행할 수 있다.
SQL*PLUS 명령어는 다음과 같이 기술한다.
■ SQL*PLUS 명령어는 기본적으로 한 줄에 기술한다.
■ SQL*PLUS 명령어는 대소문자를 구별하지 않는다.
■ SQL*PLUS 명령어는 SQL BUFFER 에 저장되지 않는다.
■ SQL*PLUS 명령어는 다음과 같다.
• DESCRIBE table명 : TABLE 의 구조를 보여준다.
• SAVE file명 : SQL BUFFER 를 file 로 저장한다.
• START file명 : file 을 수행한다.
• @ file명 : file 을 수행한다.
• EDIT file명 : EDITOR 를 사용하여 file 을 편집한다.
• SPOOL file명 : QUERY 결과를 file 에 저장한다.
• SPOOL OFF : SPOOL FILE 을 닫는다.
• HOST : SQL*PLUS 를 떠나지 않고 HOST 상태로 간다.
• HELP 명령어 : SQL, SQL*PLUS, PL/SQL 에 대한 HELP 를 보내준다.
• EXIT : SQL*PLUS 를 종료한다.

전체 데이타의 검색
가장 간단한 SELECT 문장의 형식은 다음과 같다.
. SELECT 절에는 검색하고 싶은 COLUMN 명들을 기술한다.
. FROM 절에는 SELECT 절에서 기술된 COLUMN 명들이 포함된 TABLE 명을 기술한다.
TABLE 의 모든 ROW 와 모든 COLUMN 을 검색한다.
SELECT *
FROM table명 ;
[ 예제 ]
S_DEPT TABLE 로부터 모든 ROW 와 COLUMN 을 검색하시오.
SELECT *
FROM S_DEPT ;

특정 column의 검색
SELECT 절에서 검색하고자 하는 COLUMN 명을 COMMA 를 사용하여 나열한다.
COLUMN 순서는 검색하고 싶은 순서대로 나열한다.
COLUMN HEADING 은 COLUMN 명이 대문자로 출력된다.
SELECT column명, column명, column명,..
FROM table명 ;
[ 예제 ]
S_EMP TABLE 로부터 ID, LAST_NAME, START_DATE 를 검색하시오.

SELECT ID, LAST_NAME, START_DATE
FROM S_EMP ;

산술식을 사용한 검색
산술 연산자를 사용하여 검색되는 데이타 값을 변경할 수 있다.
산술 연산식은 COLUMN 명, 상수 값, 산술 연산자로 구성된다.
SELECT 산술연산식
FROM table명 ;
[ 예제 ]
S_EMP TABLE 로부터 ID, LAST_NAME, 연봉을 검색하시오.
연봉은 SALARY * 12 로 계산한다. (+,-,*,/,())
SELECT ID, LAST_NAME, SALARY * 12
FROM S_EMP ;

Column alias
기본적으로 COLUMN HEADING 은 COLUMN 명이 대문자로 출력된다.
그러나 많은 경우 COLUMN 명이 이해하기 어렵거나 무의미하기 때문에 COLUMN ALIAS 를 사용하여
COLUMN HEADING 을 변경할 수 있다.
ANSI SQL 92 와 호환을 위해 ALIAS 앞에 AS 를 붙일 수 있다.
ALIAS 에 공백이나 특수문자가 포함되거나 대소문자를 구별하고 싶으면 " " 로 막아준다.
COLUMN ALIAS 를 사용하여 COLUMN HEADING 을 변경할 수 있다.
SELECT column명 alias, column명 "alias", column명 as alias
FROM table명 ;
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME, (SALARY + 100) * 12, DEPT_ID 를 검색하시오.
단, COLUMN ALIAS 는 Employee, ANNUAL_SALARY, DEPARTMENT NO 로 정의하시오.
SELECT LAST_NAME "Employee", (SALARY + 100) * 12 AS ANNUAL_SALARY,
DEPT_ID "DEPARTMENT NO"
FROM S_EMP ;

Column의 결합
COLUMN 과 다른 COLUMN, 산술연산식, 상수 값과 결합하여 하나의 COLUMN 으로 결합할 수 있다.
SELECT column명|| column명
FROM table명;
[ 예제 ]
S_EMP TABLE 에서 FIRST_NAME 과 LAST_NAME 을 결합하여 ALIAS EMPLOYEE 로 검색하시오.
SELECT FIRST_NAME || LAST_NAME EMPLOYEE
FROM S_EMP ;

Null값 처리
특정 COLUMN 에 값이 입력되어 있지 않을 때, 그 값을 NULL 이라 부른다.
NULL 값은 0 이나 공백과 같지 않다.
NULL 값이 산술 연산식에 포함되면 그 결과도 NULL 이다.
그러므로 NVL FUNCTION 을 사용하여 NULL 값을 다른 값으로 대체하여야 한다.
NULL 값을 다른 값으로 대체한다.
NVL (number_column, 9)
NVL (date_column, '01-JAN-95')
NVL (character_column, 'ABCDE')
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME, COMMISSION 값을 검색하시오.
COMMISSION 은 SALARY * COMMISSION_PCT /100 으로 계산하시오.
SELECT LAST_NAME, SALARY * NVL(COMMISSION_PCT,0) /100 COMMISSION
FROM S_EMP ;

중복 row의 제거
SELECT 결과 값에 중복된 값이 있을 때 중복을 피하고 UNIQUE 하게 검색한다.
중복된 ROW 를 제거한다.
SELECT DISTINCT column명, column명
FROM table명;
[ 예제 ]
S_DEPT TABLE 에서 NAME 이 중복되지 않게 검색하시오.
SELECT DISTINCT NAME
FROM S_DEPT ;

데이타의 정렬
SELECT 되는 ROW 의 순서는 알 수 없다.
그러므로 ROW 를 SORT 하고 싶으면 ORDER BY 절을 사용하여야 한다.
DATA 의 DEFAULT SORT 순서는 ASCENDING 이며 다음과 같다.
• 숫자 : 1 에서 999 순으로 SORT 한다.
• 날짜 : 01-JAN-92 에서 01-JAN-95 순으로 SORT 한다.
• 문자 : A 에서 Z 순서로 SORT 한다.
• NULL : ASC 순에서는 뒤에, DESC 순에서는 앞에 나온다.
역순으로 SORT 하고 싶으면 COLUMN 명 뒤에 DESC 를 붙인다.
COLUMN 명 대신에 ALIAS 혹은 SELECT 한 COLUMN 의 순서로 지정할 수도 있다.
SELECT expr
FROM table명
ORDER BY {column명, expr} [ASC|DESC] ;
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME, DEPT_ID, START_DATE 를 LAST_NAME 순으로 검색하시오.
SELECT LAST_NAME, DEPT_ID, START_DATE
FROM S_EMP
ORDER BY LAST_NAME ;

특정 row의 검색
WHERE 절에서 조건식을 기술하여 조건을 만족하는 ROW 만 검색할 수 있다.
조건식은 COLUMN 명, COMPARISON OPERATOR, VALUE 로 구성되어 있다.
문자 값은 ' ' 으로 묶어주고 값의 대소문자를 구별하여 적어준다.
날짜 값은 ' ' 으로 묶어주고 지정된 날짜 형태로 적어준다. '01-MAR-97'
숫자값은 값만 적어준다.
특정 ROW 만 검색한다.
SELECT expr
FROM table명
WHERE expr operator value
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME 이 Magee 인 사원의 FIRST_NAME, LAST_NAME,
TITLE 을 검색하시오. (=,>,<,>=,<=,<>)
SELECT FIRST_NAME, LAST_NAME, TITLE
FROM S_EMP
WHERE LAST_NAME = 'Magee' ;

Between...and
BETWEEN OPERATOR 를 사용하여 범위를 지정할 수 있다.
범위를 지정할 때는 작은 값을 먼저 큰 값을 나중에 지정한다.
두 범위의 한계 값을 포함한다.
BETWEEN...AND...
NOT BETWEEN...AND...
[ 예제 ]
S_EMP TABLE 에서 START_DATE 가 09-MAY-91 에서 17-JUN-91 사이에 입사한
사원의 FIRST_NAME, LAST_NAME, START_DATE 를 검색하시오.
SELECT FIRST_NAME, LAST_NAME, START_DATE
FROM S_EMP
WHERE START_DATE BETWEEN '09-MAY-91' AND '17-JUN-91' ;

In[list]
IN OPERATOR 를 사용하여 나열된 값들 중에서 값을 검사한다.
IN(LIST), NOT IN(LIST)
[ 예제 ]
S_EMP TABLE에서 DEPT_ID 가 10 , 31, 41 혹은 50 인 사원의 FIRST_NAME,
LAST_NAME, DEPT_ID 를 검색하시오.
SELECT FIRST_NAME, LAST_NAME, DEPT_ID
FROM S_EMP
WHERE DEPT_ID IN (10, 31, 41, 50) ;

like
찾고자 하는 값을 정확히 모를 때, LIKE OPERATOR 를 사용하여 문자형태가 같은 ROW 를 검색한다.
WILDCARD 를 사용하여 문자의 형태를 지정한다.
% : 여러 문자, _ : 한문자
LIKE '형태', NOT LIKE '형태'
[ 예제 ]
S_EMP TABLE에서 LAST_NAME 이 M 으로 시작하는 사원의 LAST_NAME 을 검색하시오.
SELECT LAST_NAME
FROM S_EMP
WHERE LAST_NAME LIKE 'M%' ;
SELECT LAST_NAME
FROM S_EMP
WHERE LAST_NAME LIKE '__M____' ;

is null
IS NULL OPERATOR을 사용하여 값이 NULL 인 것을 찾을 수 있다.
NULL 값은 값이 정의되지 않은 것을 의미하기 때문에 = OPERATOR를 사용하여
어떤 값과 비교할 수 없기 때문에 사용한다.
IS NULL, IS NOT NULL
[ 예제 ]
S_EMP TABLE에서 COMMISSION_PCT 가 NULL 인 사원의 LAST_NAME, SALARY,
COMMISSION_PCT 를 검색하시오.
SELECT last_name, salary,commission_pct,last_name, salary
FROM s_emp
WHERE commission_pct is null;

조건식의 결합
조건식을 기술할 때 AND 와 OR 를 사용하여 여러가지 조건을 결합할 수 있다.
AND 와 OR 가 같이 사용됐을 때 AND 가 먼저 수행되고 OR 가 나중에 수행된다.
그러므로 우선순위를 바꾸고자 하면 ( ) 를 사용한다.
WHERE 조건식 AND | OR 조건식
[ 예제 ]
S_EMP TABLE에서 DEPT_ID 가 41 이고 TITLE 이 Stock Clerk 인 사원의
LAST_NAME, SALARY, DEPT_ID, TITLE 을 검색하시오.
SELECT LAST_NAME, SALARY, DEPT_ID, TITLE
FROM S_EMP
WHERE DEPT_ID = 41
AND TITLE = 'Stock Clerk' ;

제 2 장 Single Row Functions
소문자로 변환
모든 문자를 소문자로 변환시킨다.
LOWER(COLUMN명)
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME 이 Smith 인 사원의 FIRST_NAME, LAST_NAME 을 소문자로 출력시키시오.
SELECT LOWER(FIRST_NAME), LOWER(LAST_NAME)
FROM S_EMP
WHERE LOWER(LAST_NAME) = 'smith' ;

대문자로 변환
모든 문자를 대문자로 변환시킨다.
UPPER(COLUMN명)
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME 이 Smith 인 사원의 TITLE 을 대문자로 출력하시오.
SELECT UPPER(TITLE)
FROM S_EMP
WHERE UPPER(LAST_NAME) = 'SMITH';

첫글자만 대문자로 변환
단어의 첫글자는 대문자로, 나머지는 소문자로 변환시킨다.
INITCAP(COLUMN명)
[ 예제 ]
S_EMP TABLE 에서 TITLE 을 단어의 첫글자만 대문자로 출력시키시오.
SELECT INITCAP(TITLE)
FROM S_EMP ;

문자의 부분을 자름
문자를 시작위치(M)에서 자리수(N) 만큼 잘라준다.
자리수(N)이 생략되면 시작위치(M)에서 끝까지 잘라준다.
SUBSTR(COLUMN명, M, N)
[ 예제 ]
S_PRODUCT TABLE 에서 NAME COLUMN 의 앞에서 부터 3글자가 Ace 인 제품의
NAME 을 출력하시오.
SELECT NAME
FROM S_PRODUCT
WHERE SUBSTR(NAME, 1, 3) = 'Ace' ;

문자의 길이를 계산
문자의 길이를 RETURN 한다.
LENGTH(COLUMN명)
[ 예제 ]
S_PRODUCT TABLE 에서 NAME, NAME 의 길이를 출력하시오.
SELECT NAME, LENGTH(NAME)
FROM S_PRODUCT;

숫자의 반올림
지정된 자리수(M) 밑에서 반올림한다.
COLUMN 값이 1 2 3 4. 5 6 7 일 때 자리수(M)는 다음과 같다.
     M : -3-2-1 0 1 2 3
ROUND(COLUMN명, M)
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME, SALARY/22 의 값을 소수 2째 자리까지 나타내고
소수 3째 자리에서 반올림하시오.
SELECT LAST_NAME, ROUND(SALARY/22, 2)
FROM S_EMP ;

숫자의 절사
지정된 자리수(M) 까지 나타내고 그 밑은 잘라버린다.
COLUMN 값이 1 2 3 4. 5 6 7 일 때 자리수(M)은 다음과 같다.
     M : -3-2-1 0 1 2 3
절사 값은 RETURN 한다.
TRUNC(COLUMN명, M)
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME, SALARY / 22 의 값을 십의 자리까지 나타내고
일의 자리는 버림
SELECT LAST_NAME, TRUNC(SALARY/22, -1)
FROM S_EMP ;

나누기의 나머지
M 을 N 으로 나누고 남은 나머지를 RETURN 한다.
MOD(M, N)
[ 예제 ]
10 을 3 으로 나눈 나머지를 구하시오.
SELECT MOD(10, 3)
FROM SYS.DUAL ;

날짜의 연산
DATABASE 안의 DATE 값은 다음과 같은 숫자로 저장되어 있다.
■ CENTURY, YEAR, MONTH, DAY, HOURS, MINUTES, SECONDS
그러므로 산술 연산을 할 수 있다.
● DATE + NUMBER : 숫자만큼 일이 더해진 날짜가 RETURN 된다.
● DATE - NUMBER : 숫자만큼 일이 빼진 날짜가 RETURN 된다.
● DATE1 - DATE2 : 두 날짜 사이의 일수가 계산된다.
날짜 계산을 한다.
DATE + NUMBER
DATE - NUMBER
DATE1 - DATE2
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME, 입사한지 90 일째 되는 날, 입사한지 며칠 됐는 지 검색하시오.
SELECT LAST_NAME, START_DATE + 90, SYSDATE - START_DATE
FROM S_EMP;
( 날짜에는 시간도 포함되어 있으므로 일수 계산의 결과가 소수로 나온다. )

날짜 사이의 개월 수
두 날짜 사이의 개월 수를 RETURN 한다.
MONTHS_BETWEEN(DATE1, DATE2)
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME, 입사한지 몇 달이 됐는지 출력하시오.
SELECT LAST_NAME, MONTHS_BETWEEN(SYSDATE, START_DATE)
FROM S_EMP ;
(일이 포함되어 있어서 소수로 출력된다.)

날짜에 달을 더함
날짜에서 숫자(N) 개월만큼 더해진 날짜를 RETURN 한다.
ADD_MONTHS(DATE, N)
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME, START_DATE, 입사한지 3 개월되는 날짜를 출력하시오.
SELECT LAST_NAME, START_DATE, ADD_MONTHS(START_DATE, 3)
FROM S_EMP ;

지정한 요일 날짜
날짜에서 지정한 요일(CHAR)이 될 날짜를 RETURN 한다.
NEXT_DAY(DATE, 'CHAR')
[ 예제 ]
오늘을 기준으로 돌아오는 금요일이 언제인지 출력하시오.
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'FRIDAY')
FROM SYS.DUAL ;

그 달의 마지막 날
날짜가 포함된 달의 마지막 날을 RETURN 한다.
LAST_DAY(DATE)
[ 예제 ]
이번 달의 마지막 날은 언제인지 출력하시오.
SELECT SYSDATE, LAST_DAY(SYSDATE)
FROM SYS.DUAL ;

날짜의 반올림
형태에 따른 반올림 기준은 다음과 같다.
• YEAR : 6월 이후
• MONTH : 15일 이후
• DAY : 12시 이후
날짜 데이타를 지정된 형태까지 나타내고 그 이하에서 반올림한다.
ROUND(COLUMN명, '형태')
[ 예제 ]
S_EMP TABLE 에서 ID, LAST_NAME, 입사 시작 달을 검색하시오.
단, 15일 이후는 다음달로 올리시오.
SELECT ID, LAST_NAME, ROUND(START_DATE, 'MONTH')
FROM S_EMP ;

날짜의 절사
날짜 데이타를 지정된 형태까지 나타내고 그 밑은 잘라낸다.
TRUNC(COLUMN명, '형태')
[ 예제 ]
S_EMP TABLE 에서 ID, LAST_NAME, 입사 시작 달을 검색하시오.
단, 일자는 잘라버리시오.
SELECT ID, LAST_NAME, TRUNC(START_DATE, 'MONTH')
FROM S_EMP ;

문자를 날짜로 변환
CHARACTER TYPE 을 지정된 형태의 DATE TYPE 으로 변환한다.
TO_DATE(character_column명, '형태')
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME, START_DATE 를 검색하시오.
단, START_DATE 의 값이 92/02/07 인 사원을 검색하시오.
SELECT LAST_NAME, START_DATE
FROM S_EMP
WHERE START_DATE = TO_DATE('92/02/07', 'YY/MM/DD') ;

날짜를 문자로 변환
DATE 값은 기본적으로 DD-MON-YY 형태로 출력된다.
이것을 TO_CHAR FUNCTION 을 사용하면 원하는 다른 형태로 변환할 수 있다.
■ 형태를 지정할 때 사용된 대소문자로 출력된다.
■ DAY 와 MONTH 형태는 공백을 포함한 9 자리로 출력된다.
■ TO_CHAR 의 결과는 80 자리로 출력된다.
DATE TYPE 을 지정된 형태의 CHARACTER TYPE 으로 변환한다.
TO_CHAR(date_column, '형태')
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME, START_DATE 를 검색하시오.
단, START_DATE 의 형태는 1991/06/17 14:20:00 와 같이 출력하시오.
SELECT LAST_NAME, TO_CHAR(START_DATE, 'YYYY/MM/DD HH24:MI:SS'), START_DATE
FROM S_EMP ;

숫자를 문자로 변환
NUMBER TYPE 을 지정된 형태의 CHARACTER TYPE 으로 변환한다.
TO_CHAR(number_column명, '형태')
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME, SALARY 를 검색하시오.
단 SALARY 를 $1,450 와 같은 형태로 출력시키시오.
SELECT LAST_NAME, TO_CHAR(SALARY, '$999,999')
FROM S_EMP ;


제 3 장. 여러Table로부터 Data검색
Equijoin
SIMPLE JOIN (EQUI-JOIN)
여러개의 TABLE 들로부터 정보를 검색하려면, SELECT 문장의 FROM 절에 TABLE명들을 적고
WHERE 절에 각 TABLE의 ROW들을 연결시킬 조건식을 기술한다.
각 TABLE 의 COLUMN명이 중복될 때는 반드시 COLUMN명 앞에 TABLE명을 붙여야 한다.
(중복되지 않을 때는 붙이지 않아도 되지만 명확성을 위해서나 ACCESS 를 위해서 붙이는 것이 좋다.)
N 개의 TABLE 을 JOIN 할 때는 최소한 N-1 개의 조건식이 필요하다.
복합 COLUMN 으로 JOIN 할 때는 더 많은 조건식이 필요하다.
2개 이상의 TABLE 에서 = 조건식을 만족시키는 ROW 들을 연결하여 검색한다.
SELECT table명.column명, table명.column명...
FROM table1명, table2명
WHERE table1명.column1명 = table2명.column명 ;
[ 예제 ]
S_EMP TABLE 과 S_DEPT TABLE 을 사용하여 사원들의 LAST_NAME, DEPT_ID,
NAME 을 검색하시오.
SELECT S_EMP.LAST_NAME, S_EMP.DEPT_ID, S_DEPT.NAME
FROM S_EMP, S_DEPT
WHERE S_EMP.DEPT_ID = S_DEPT.ID ;

특정 row의 join
JOIN 문장을 기술할 때 JOIN 조건식 이외에 다른 조건식을 AND 로 연결할 수 있다.
SELECT table명.column명, table명.column명...
FROM table1명, table2명
WHERE table1명.column1명 = table2명.column2명 AND condition ;
[ 예제 ]
S_EMP TABLE과 S_DEPT TABLE 을 사용하여 LAST_NAME 이 Menchu 인 사원의
LAST_NAME, DEPT_ID, NAME 을 검색하시오.
SELECT S_EMP.LAST_NAME, S_EMP.DEPT_ID, S_DEPT.NAME
FROM S_EMP, S_DEPT
WHERE S_EMP.DEPT_ID = S_DEPT.ID AND S_EMP.LAST_NAME = 'Smith' ;

Table alias
JOIN 문장에서 TABLE명이 긴 경우 TABLE명.COLUMN명 으로 적는 것이 매우 불편하다.
그런데 TABLE명 대신 ALIAS 를 사용하면 편하게 사용할 수 있다.
(SELECT 문장에서 TABLE명 대신 ALIAS 를 지정했다면 그 문장에서는 계속해서
ALIAS 로 사용하여야 한다.)
TABLE ALIAS를 사용하여 JOIN 문장을 간단하게 기술한다.
SELECT alias명.column명, alias명.column명
FROM table1명 alias1명, table2명 alias2명
WHERE alias1명.column1명 = alias2명.column2명 ;
[ 예제 ]
S_CUSTOMER TABLE과 S_REGION TABLE 을 사용하여 고객 명,지역번호,지역 명을 검색하시오.
단, COLUMN ALIAS 와 TABLE ALIAS 를 사용하시오.
SELECT C.NAME "Customer Name", C.REGION_ID "Region Id",
R.NAME "Region Name"
FROM S_CUSTOMER C, S_REGION R
WHERE C.REGION_ID = R.ID ;

Non-Equijoin
NON-EQUIJOIN
JOIN 문장에서 두 TABLE 을 JOIN 하는 조건식에 = OPERATOR 가 사용되지 않고
다른 OPERATOR 가 사용되는 것을 말한다.
SELECT table명.column명, table명.column명...
FROM table1명, table2명
WHERE 조인조건식 ;
[ 예제 ]
EMP TABLE 과 SALGRADE TABLE 을 사용하여 사원의 ENAME, JOB, SAL,GRADE를 검색하시오.
SELECT E.ENAME, E.JOB, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL ;
(BETWEEN OPERATOR 대신에 <= 와 >= 를 사용해도 되지만 BETWEEN 이 간편하다.)

Outer Join
두 TABLE 을 JOIN 할 때 JOIN 조건식을 만족시키지 못하는 ROW 는 검색에서 빠지게 된다.
그런데 이러한 ROW 들이 검색되도록 하는 것이 OUTER JOIN 이다.
(+) OUTER JOIN OPERATOR 를 데이타가 없는 어느 한쪽의 COLUMN 쪽에 붙인다.
JOIN 결과, 데이타가 없는 쪽의 COLUMN 값은 NULL로 검색된다.
조건식을 만족시키지 못하는 데이타도 검색한다.
SELECT table명.column명, table명.column명
FROM table1명, table2명
WHERE table1명.column1명 = table2명.column2명(+)
[ 예제 ]
S_EMP TABLE 과 S_CUSTOMER TABLE 을 사용하여 영업사원의 LAST_NAME,
SALES_REP_ID, NAME 을 검색하시오.
단, 영업사원이 정해져 있지 않은 고객의 이름도 검색하시오.

SELECT E.LAST_NAME, C.SALES_REP_ID,
C.NAME
FROM S_EMP E, S_CUSTOMER C
WHERE E.ID(+) = C.SALES_REP_ID ;

Self Join
TABLE 의 ALIAS 를 사용하여, 마치 2 개의 TABLE 처럼 생각하여 자신의 TABLE 과 자신의 TABLE 을 JOIN 한다.
SELECT alias명.column명, alias명.column명...
FROM table명 alias1명, table명 alias2명
WHERE alias1명.column1명 = alias2명.column2명 ;
[ 예제 ]
S_EMP TABLE 에서 사원들의 LAST_NAME 과 그들의 상사 LAST_NAME 을 검색하시오.
SELECT W.LAST_NAME "Woker",
M.LAST_NAME "Manager"
FROM S_EMP W, S_EMP M
WHERE W.MANAGER_ID = M.ID ;

제 4 장. Group Functions
Group Function
각각의 FUNCTION 은 ARGUMENT 를 받는데 기능은 다음과 같다.
■ DISTINCT : 중복된 값은 제외한다.
■ ALL : DEFAULT 로써 모든 값을 포함한다.
■ COLUMN명 : NULL 값은 제외한다.
■ * : NULL 값도 포함한다.
TABLE 전체를 하나의 GROUP 으로 보고 GROUP FUNCTION 값을 RETURN 한다.
SELECT group_function(column명), group_function(column명)...
FROM table명 ;
[ 예제 ]
S_EMP TABLE 에서 회사 전체의 급여합계, 최고급여, 최소급여, 인원수를 검색하시오.
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), COUNT(SALARY)
FROM S_EMP ;
( COUNT(SALARY) 는 급여를 받는 사원의 총 인원수고 COUNT(*) 는 급여를 받지 않는 사원의 인원수도 포함된다.)

소group으로 분리
기본적인 SELECT 절(그룹화 되지 않은 SELECT절)에는 COLUMN 명과 GROUP FUNCTION 이 같이 기술될 수 없다.
SELECT 절에 COLUMN 명이 기술되려면 GROUP BY 절이 반드시 기술되어야 한다.
SELECT 절에 기술된 COLUMN 명들은 전부 GROUP BY 절에 기술되어야 하며
GROUP BY 절에 기술된 COLUMN 명들은 SELECT 절에 기술되지 않아도 된다.
(하지만 결과를 파악하기 위해서는 SELECT 절에 기술해주는 것이 좋다.)
GROUP BY 절을 기술하면 GROUP BY 절에 기술된 COLUMN 값으로 1 개의 TABLE이 소 GROUP 으로 나눠진다.
결과는 COLUMN 값으로 SORT 되어서 출력된다.
1 개의 TABLE 을 소 GROUP 으로 나누어 GROUP FUNCTION 값을 구한다.
SELECT column1명[, column2명], group_function(column명)
FROM table명
GROUP BY column1명[, column2명] ;
[ 예제 ]
S_EMP TABLE 에서 DEPT_ID, TITLE 별로, 최고급여, 최소급여, 인원수를 검색하시오.
SELECT DEPT_ID, TITLE,
MAX(SALARY), MIN(SALARY),
COUNT(SALARY)
FROM S_EMP
GROUP BY DEPT_ID, TITLE;

특정 group의 선택
HAVING 절이 기술됐을 때 처리되는 순서는 다음과 같다.
① ROW 들이 GROUPing 된다.
② GROUP 에 대해 GROUP FUNCTION 이 적용된다.
③ HAVING 절을 만족하는 GROUP 을 선택한다.
그러므로 GROUP BY 절과 HAVING 절의 순서는 바뀌어도 되지만 의미상 GROUP BY
절 다음에 HAVING 절을 기술하는 것이 좋다.
HAVING 절에서는 GROUP FUNCTION 을 사용하여 GROUP 에 대한 조건식을 기술한다.
SELECT column1명[, column2명], group_function(column명)
FROM table명
GROUP BY column1명[, column2명]
HAVING 그룹조건식 ;
[ 예제 ]
S_EMP TABLE 에서 TITLE 별로 급여합계를 검색하시오.
단, 급여합계가 5000 이상인 GROUP 만 출력하시오.
SELECT TITLE, SUM(SALARY) PAYROLL
FROM S_EMP
GROUP BY TITLE
HAVING SUM(SALARY) >= 5000 ;

Group의 정렬
기본적으로 GROUP BY 절에 기술된 COLUMN 값으로 SORT 된다.
이 순서를 바꾸고자 하면 ORDER BY 절을 기술하면 된다.
DATA 의 SORT 순서를 정한다.
SELECT column1명[, column2명], group_function(column명)
FROM table명
GROUP BY column1명[, column2명]
ORDER BY column명| group_function(column명) ;
[ 예제 ]
S_EMP TABLE에서 DEPT_ID 별로 인원수를 검색하시오.
단, 인원수가 많은 부서부터 출력하시오.
SELECT DEPT_ID, COUNT(*)
FROM S_EMP
GROUP BY DEPT_ID
ORDER BY COUNT(*) DESC ;

제 5 장. Subquery
Single Row Subquery
SUBQUERY 의 결과가 1 개의 ROW 로 나오는 것을 SINGLE ROW SUBQUERY 라 하며
다음과 같은 OPERATOR 를 사용할 수 있다.
=, >, >=, <, <=
VALUE 값을 구하기 위해 SELECT 문장을 사용한다.
SELECT column명, column명...
FROM table명
WHERE column명 operator (SELECT column명
FROM table명
WHERE 조건식 );
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME 이 Smith 인 사원과 같은 업무(TITLE)를 하고 있는
사원의 LAST_NAME, TITLE 을 검색하시오.
SELECT LAST_NAME, TITLE
FROM S_EMP
WHERE TITLE =
( SELECT TITLE
FROM S_EMP
WHERE LAST_NAME = 'Smith') ;

From절의 Subquery
FROM 절에 기술된 SUBQUERY 문은 VIEW 처럼 사용된다.
SELECT alias명.column명, alias명,column명...
FROM table1명 alias1명, (SELECT column2명
FROM table2명
WHERE 조건식) alias2명
WHERE alias1명.column1명 OPERATOR alias2명.column2명 ;
[ 예제 ]
S_EMP TABLE 에서 SALARY 가 회사평균급여 보다 적은 사원의 LAST_NAME,
SALARY, 회사평균급여를 검색하시오.
SELECT E.LAST_NAME, E.SALARY, S.AVGSAL
FROM S_EMP E,
(SELECT AVG(SALARY) AVGSAL
FROM S_EMP) S
WHERE E.SALARY < S.AVGSAL ;

Multi Row Subquery
SUBQUERY 의 결과가 여러 ROW 일 때는 반드시 IN OPERATOR 를 사용하여야 한다.
SELECT column명, column명...
FROM table명
WHERE column명 IN ( SELECT column명
FROM table명
WHERE 조건식);
[ 예제 ]
S_EMP TABLE 과 S_DEPT TABLE 에서 Operations 부서에서 근무하는 사원의
LAST_NAME, TITLE, DEPT_ID 를 검색하시오.
SELECT LAST_NAME, TITLE, DEPT_ID
FROM S_EMP
WHERE DEPT_ID IN (SELECT ID
FROM S_DEPT
WHERE NAME = 'Operations') ;

Multi Column Subquery
SELECT 문장의 WHERE 절에서 여러개의 COLUMN 값을 비교하려면 LOGICAL
OPERATOR 를 사용하여 여러개의 조건식을 기술하여야 한다.
그런데 MULTI COLUMN SUBQUERY 를 사용하면 이를 해결할 수 있다.
SELECT column명, column명,,,
FROM table명
WHERE (column명, column명...) IN (SELECT column명, column명...
FROM table명
WHERE 조건식);
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME Patel 인 사원과 같은 부서, 같은 업무를
맡고 있는 사원의 LAST_NAME, TITLE, DEPT_ID 를 검색하시오.
SELECT LAST_NAME, TITLE, DEPT_ID
FROM S_EMP
WHERE (DEPT_ID, TITLE) IN
(SELECT DEPT_ID, TITLE
FROM S_EMP
WHERE LAST_NAME = 'Patel') ;
SELECT LAST_NAME, TITLE, DEPT_ID
FROM S_EMP
WHERE (DEPT_ID) IN
(SELECT DEPT_ID
FROM S_EMP
WHERE LAST_NAME = 'Patel')
OR (TITLE) IN
(SELECT TITLE
FROM S_EMP
WHERE LAST_NAME = 'Patel') ;

제 6 장. Table 생성
이름 붙이는 법
이름은 다음의 규칙을 따라서 지정한다.
■ TABLE 명이나 COLUMN 명은 문자로 시작하고 30 자 이내로 지정한다.
■ A ~ Z, a ~ z, 0 ~ 9, _ , $ , # 을 사용할 수 있다.
■ 한 USER 내에서는 다른 OBJECT 의 이름과 동일하게 지정할 수 없다.
■ ORACLE7 SERVER 예약어를 사용할 수 없다.
■ 대소문자를 구별하지 않는다.

Oracle 7 datatype
COLUMN 의 DATATYPE 은 다음과 같다.
■ CHAR(size) : 고정된 size 의 문자 값, 최대 255 자까지 지정할 수 있다.
■ VARCHAR2(size) : size내에서의 가변길이 문자 값,최대 2000자까지 지정할 수 있다.
■ LONG : 가변길이 문자 값, 최대 2 기가 바이트까지 사용할 수 있다. TABLE 당 한 개의 COLUMN 에만 지정 가능하다.
■ NUMBER(p,s) : 정수, 소수 자리수의 합이 P, 소수 자리수가 s 인 숫자값, 최대 38 자리수까지 지정할 수 있다.
■ DATE : 날짜와 시간 값, B.C. 4712년 1월 1일부터 A.D. 4712년 12월 31일까지 입력할 수 있다.
■ RAW(size) : size 내에서의 가변길이 BINARY DATA
■ LONGRAW : 가변길이 BINARY DATA

다른 table로부터 table생성
기존하는 TABLE 로 부터 데이타와 구조를 복사하여 TABLE 을 생성한다.
CREATE TABLE table명 [(column명, column명...)]
AS subquery ;
[ 예제 ]
S_EMP TABLE 에서 DEPT_ID 가 41 인 사원들의 ID, LAST_NAME, USERID,
START_DATE 만을 복사하여 EMP_41 TABLE 을 생성하시오.
CREATE TABLE EMP_41
AS SELECT ID, LAST_NAME, USERID, START_DATE
FROM S_EMP
WHERE DEPT_ID = 41;
(S_EMP TABLE 에서 COLUMN명, TYPE, SIZE, NOT NULL CONSTRAINT 가 복사되어 EMP_41 TABLE 이 생성되며, 데이타는 DEPT_ID = 41 인 ROW 만 복사된다.)

Constraint
CONSTRAINT 의 종류는 다음과 같다.
■ NOT NULL
COLUMN 에 NULL 값이 입력되는 것을 허용하지 않는다.
COLUMN-CONSTRAINT 로만 기술해야 한다.
■ UNIQUE
한 개의 COLUMN 혹은 복합 COLUMN 을 UNIQUE KEY 로 지정한다.
UNIQUE KEY 에는 중복된 값을 허용하지 않는다.
한개의 COLUMN 으로 구성된 UNIQUE KEY 는 NULL 값을 허용한다.
COLUMN 이나 TABLE-CONSTRAINT 로 기술할 수 있다.
복합 COLUMN 으로 구성된 UNIQUE KEY 는 TABLE-CONSTRAINT 로만 기술해야 한다.
UNIQUE KEY COLUMN 의 UNIQUE INDEX FILE 이 자동 생성된다.
■ PRIMARY KEY
ROW 를 UNIQUE 하게 대표할 수 있는 한개의 COLUMN 혹은 복합 COLUMN 으로 지정한다.
PRIMARY KEY 에는 중복된 값과 NULL 값을 허용하지 않는다.
TABLE 당 한 개의 PRIMARY KEY 만 지정할 수 있다.
COLUMN 이나 TABLE-CONSTRAINT 로 기술할 수 있다.
복합 COLUMN 으로 구성된 PRIMARY KEY 는 TABLE-CONSTRAINT 로만 기술해야 한다.
PRIMARY KEY COLUMN 의 UNIQUE INDEX FILE 이 자동 생성된다.
■ FOREIGN KEY
한개의 COLUMN 혹은 복합 COLUMN 으로 지정한다.
같은 TABLE 혹은 다른 TABLE의 PRIMARY KEY 나 UNIQUE KEY 값을 참조한다.
FOREIGN KEY 값은 모 TABLE 에 존재하는 데이타와 같던가 NULL 값을 허용한다.
COLUMN 이나 TABLE-CONSTRAINT 로 기술할 수 있다.
※ CHECK : 각각의 ROW 가 만족해야할 조건을 지정한다.
조건식은 QUERY 조건식과 동일하게 지정한다.
단, 다음과 같은 것은 사용할 수 없다.
CURRVAL, NEXTVAL, LEVEL, ROWNUM, SYSDATE, USER COLUMN 이나
TABLE-CONSTRAINT 로 기술할 수 있다.
CONSTRAINT 명은 다음과 같이 지정한다.
• CONSTRAINT 는 DICTIONARY 에 저장되므로 참조하기 쉽게 의미있게 붙여준다.
• 일반적으로 TABLE명_COLUMN명_CONSTRAINT종류와 같은 형태로 지정한다.
• 사용자가 CONSTRAINT 명을 지정하지 않으면 ORACLE7이 SYS_Cn의 형태로 붙인다.
• 동일한 USER 내에서 CONSTRAINT명은 UNIQUE해야 한다.
CONSTRAINT 는 다음과 같이 기술할 수 있다.
COLUMN-CONSTRAINT : column명 [CONSTRAINT constraint명] constraint종류
TABLE-CONSTRAINT : [CONSTRAINT constraint명] constraint종류
(column명, column명..)

Table 생성
CREATE TABLE table명
(column명 type(size) [DEFAULT VALUE] [column constraint],
column명 type(size) [DEFAULT VALUE] [column constraint],
.... ,
[table constraint] ,
[table constraint] ,
.... ) ;
[ 예제 ]
S_EMP TABLE CHART를 보고 TABLE 을 생성하시오.
단, TABLE CONSTRAINT 로 기술할 수 있는 것은 TABLE CONSTRAINT 로 정의하시오.
CREATE TABLE S_EMP
(ID NUMBER(7),
LAST_NAME VARCHAR2(25) CONSTRAINT S_EMP_LAST_NAME_NN NOT NULL,
FIRST_NAME VARCHAR2(25),
USERID VARCHAR2(8) CONSTRAINT S_EMP_USERID_NN NOT NULL,
START_DATE DATE DEFAULT SYSDATE,
COMMENTS VARCHAR2(25),
MANAGER_ID NUMBER(7),
TITLE VARCHAR2(25),
DEPT_ID NUMBER(7),
SALARY NUMBER(11,2),
COMMISSION_PCT NUMBER(4,2),
CONSTRAINT S_EMP_ID_PK PRIMARY KEY(ID),
CONSTRAINT S_EMP_USERID_UK UNIQUE,
CONSTRAINT S_EMP_DEPT_ID_FK FOREIGN KEY(DEPT_ID)
REFERENCES S_DEPT(ID),
CONSTRAINT S_EMP_COMMISSION_PCT CHECK
(COMMISSION_PCT IN (10, 12.5, 15, 17.5, 20))) ;

제 7 장. Data DICTIONARY
DICTIONARY
• DATABASE 가 만들어 졌을때 DICTIONARY TABLE 도 만들어 진다.
• DATABASE 가 사용중일때 DICTIONARY TABLE 은 ORACLE7 SERVER 에 의해 UPDATE 된다.
• 사용자들은 DICTIONARY TABLE 을 SELECT 할 수 있다.
• DICTIONARY TABLE 은 SYS USER 의 소유다.
• DICTIONARY TABLE 의 값은 대문자로 들어있다.
• DICTIONARY TABLE 의 종류는 다음과 같은 방법으로 알 수 있다.
SELECT   *
FROM    DICTIONARY ;
DICTIONARY TABLE 의 종류는 다음과 같다.
• USER : USER 가 소유하고 있는 OBJECT 의 정보를 보여준다.
• ALL : USER 가 ACCESS 할 수 있는 OBJECT 의 정보를 보여준다.
• DBA : DBA USER 가 ACCESS 할 수 있는 OBJECT 의 정보를 보여준다.

활용예
DICTIONARY TABLE 의 검색예는 다음과 같다.
■ 자신이 갖고 있는 TABLE 의 이름을 검색한다.
SELECT   OBJECT_NAME
FROM    USER_OBJECTS
WHERE   OBJECT_TYPE = 'TABLE';
■ 자신이 갖고 있는 OBJECT 의 종류를 검색한다.
SELECT   DISTINCT OBJECT_TYPE
FROM   USER_OBJECTS;
■ GRANT 와 관련된 DICTIONARY TABLE 의 이름을 검색한다.
SELECT   TABLE_NAME
FROM   DICTIONARY
WHERE   UPPER(COMMENTS) LIKE '%GRANT%';
■ S_EMP TABLE 의 CONSTRAINT 종류를 검색한다.
SELECT   CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION,
R_CONSTRAINT_NAME
FROM   USER_CONSTRAINTS
WHERE   TABLE_NAME = 'S_EMP';
■ S_EMP TABLE 의 COLUMN CONSTRAINT 를 검색한다.
SELECT   CONSTRAINT_NAME, COLUMN_NAME
FROM   USER_CONS_COLUMNS
WHERE   TABLE_NAME = 'S_EMP';

제 8 장. Data 조작
데이타 입력
TABLE 전체 COLUMN 에 값을 입력한다.
INSERT INTO table명
VALUES (value, value...);
[ 예제 ]
S_EMP TABLE 에 다음과 같은 데이타를 입력하시오.
ID : 26, LAST_NAME : Jung Mi, FIRST_NAME : Hong, USERID : Hjungmi,
START_DATE : 05-APR-97, COMMENTS : Teacher, MANAGER_ID : 10,
TITLE : Stock Clerk, DEPT_ID : 45, SALARY : 1200 COMMISSION_PCT : 10
INSERT INTO S_EMP
VALUES (26, 'Jung Mi', 'Hong', 'Hjungmi', '05-APR-97',
'Teacher', 10, 'Stock Clerk', 45, 1200, 10) ;
(값을 지정하는 순서는 TABLE 의 COLUMN 순서에 맞춰서 지정한다.
이 방법보다는 COLUMN명을 기술하여 입력하는 방법이 더 좋다.)

특정 column에 데이타입력
데이타를 입력하고자 하는 COLUMN을 선택하여 입력한다.
INSERT INTO table명(column명, column명....)
VALUES (value, value....);
[ 예제 ]
S_EMP TABLE 에 다음과 같은 데이타를 입력하시오.
ID : 27, LAST_NAME : Smith, FIRST_NAME : Donna, START_DATE : 05-APR-97
INSERT INTO S_EMP(ID, LAST_NAME, FIRST_NAME, START_DATE)
VALUES (27, 'Smith', 'Donna', '05-APR-97') ;

Null, 특수 value 입력
COLUMN 값에 NULL 값을 지정하는 방법은 3 가지가 있다.
• INSERT 문장의 COLUMN LIST 에서 생략한다.
• INSERT 문장의 VALUE 절에서 NULL 로 지정한다.
• INSERT 문장의 VALUE 절에서 '' 로 지정한다.
COLUMN 값에 특수한 값을 입력할 수 있다.
SYSDATE : 현재날짜와 시간
USER : 현재 USERID
[ 예제 ]
S_EMP TABLE 에 다음과 같은 데이타를 입력하시오.
ID : 29, LAST_NAME : Donna, USERID : USER, SALARY : NULL, START_DATE : SYSDATE
INSERT INTO S_EMP(ID, LAST_NAME, USERID, SALARY, START_DATE)
VALUES (29, 'Donna', USER, NULL, SYSDATE);

특수형태의 날짜/시간입력
DATE 값을 입력할 때는 지정된 DATE 형태로 입력하여야 한다.
일반적으로 DD-MON-YY 형태를 사용하며, 이 형태로 데이타를 입력하면 세기는 현재의 세기로, 시간은 자정으로 입력된다.
다른 세기의 날짜나 시간을 입력하고 싶으면 TO_DATE FUNCTION 을 사용한다.
지정된 형태가 아닌 다른 형태의 날짜 값을 입력한다.
TO_DATE('날짜값','날짜형태')
[ 예제 ]
S_EMP TABLE 에 다음과 같은 데이타를 입력하시오.
ID : 30, LAST_NAME : Donna, USERID : SQL01, START_DATE : 199704051400
INSERT INTO S_EMP(ID, LAST_NAME, USERID, START_DATE)
VALUES (30, 'Donna', 'SQL01', TO_DATE('199704051400','YYYYMMDDHH24MI'));

다른table로부터 데이타입력
INSERT 문장을 사용하여 기존하는 TABLE 의 데이타를 다른 TABLE 로 COPY 할 수 있다.
INSERT INTO table명[(column명, column명...)]
SUBQUERY;
[ 예제 ]
S_EMP TABLE 의 ROW들을 HISTORY TABLE 로 COPY 하시오.
단, 01-JAN-94 이전에 입사한 사원의 ID,LAST_NAME,SALARY,START_DATE 를 COPY 하시오
INSERT INTO HISTORY(ID, LAST_NAME, SALARY, START_DATE)
SELECT ID, LAST_NAME, SALARY, START_DATE
FROM S_EMP
WHERE START_DATE < '01-JAN-94' ;
(INSERT 절의 COLUMN 수와 SELECT 절의 COLUMN 수는 같아야 한다.)

데이타 수정
UPDATE 문장을 사용하여 이미 존재하는 COLUMN 값을 수정한다.
UPDATE table명
SET column명 = value, [column명 = value]
[WHERE 조건식] ;
[ 예제 ]
S_EMP TABLE 에서 ID 가 1 인 사원의 데이타를 다음과 같이 수정하시오.
DEPT_ID : 32, SALARY : 2550
UPDATE S_EMP
SET DEPT_ID = 32, SALARY = 2550
WHERE ID = 2 ;

데이타 삭제
DELETE 문장을 사용하여 데이타를 삭제한다.
DELETE FROM table명
[WHERE 조건식] ;
[ 예제 ]
S_EMP TABLE에서 ID 가 20 보다 큰 사원을 삭제하시오.
DELETE FROM S_EMP
WHERE ID > 20 ;

저장
COMMIT 문장(COMMIT;)에 의해 변경된 모든 내용이 DATABASE 에 저장된다.
변경된 모든 데이타는 DATABASE 에 저장된다.
그 전의 데이타는 완전히 없어진다.
모든 사용자가 변경한 내용을 볼 수 있다.
변경된 ROW 에 걸려있던 LOCK 이 해제된다.
그러므로 다른 사용자가 수정할 수 있다.
모든 SAVEPOINT 가 없어진다.
TRANSACTION 을 종료하고 TRANSACTION 안의 모든 변경된 작업을 저장한다.

취소
ROLLBACK 문장(ROLLBACK)을 사용하여 모든 변경된 내용을 취소한다.
모든 변경이 취소되며 수정하기 전의 데이타가 복구된다.
변경된 ROW 에 걸려있던 LOCK 이 해제된다.
다른 사용자들이 그 ROW 에 대해서 변경을 할 수 있다.
TRANSACTION 을 종료하고 TRANSACTION 안의 모든 변경된 작업을 취소한다.

Savepoint지정~취소
TRANSACTION 안에서 ROLLBACK 할 수 있는 POINT 를 지정한다.
지정된 POINT 까지만 ROLLBACK 한다.
SAVEPOINT savepoint명 ;
ROLLBACK TO savepoint명 ;
[ 예제 ]
S_EMP TABLE 에서 TITLE 이 Stock Clerk 인 사원의 SALARY 를 10% 인상하시오.
SAVEPOINT 를 지정하시오.
S_REGION TABLE 에 다음과 같은 데이타를 입력하시오.
ID : 8, NAME : Central
SAVEPOINT 까지 ROLLBACK 하시오.
UPDATE 결과를 저장하시오.
UPDATE S_EMP
SET SALARY = SALARY * 1.1
WHERE TITLE = 'Stock Clerk' ;
SAVEPOINT S1;
INSERT INTO S_REGION(ID, NAME)
VALUES (8, 'Central') ;
ROLLBACK TO S1;
COMMIT;


제 9 장. Table변경/삭제
Column 추가
TABLE 에 새로운 COLUMN 을 추가한다.
ALTER TABLE table명
ADD (column명 type(size) [DEFAULT value] [column_constraint],
...........) ;
[ 예제 ]
S_REGION TABLE 에 다음과 같은 COLUMN 을 추가하시오.
COMMENTS VARCHAR2(25)
ALTER TABLE S_REGION
ADD (COMMENTS VARCHAR2(25))
(추가될 COLUMN 의 위치는 지정할 수 없다. 새로운 COLUMN 은 마지막 위치에 생성된다.)

Column 변경
ALTER TABLE 문장의 MODIFY 절을 사용하여 다음과 같은 변경을 할 수 있다.
COLUMN 의 크기를 확장할 수 있다.
데이타가 들어있지 않으면 COLUMN 의 크기를 줄일 수 있다.
데이타가 들어있지 않다면 COLUMN 의 타입을 수정할 수 있다.
COLUMN 에 NULL 값이 없다면 NOT NULL CONSTRAINT 를 지정할 수 있다.
DEFAULT VALUE 를 변경할 수 있다.
이미 생성되어 있는 COLUMN 을 변경한다.
ALTER TABLE table명
MODIFY (column명 type(size) [DEFAULT value] [NOT NULL],
.............) ;

Constraint 추가
이미 생성되어 있는 TABLE 에 CONSTRAINT 를 추가한다.
ALTER TABLE table명
ADD (table_constraint) ;
[ 예제 ]
S_EMP TABLE 에 다음과 같은 CONSTRAINT 를 추가하시오.
MANAGER_ID COLUMN 이 S_EMP TABLE 의 ID COLUMN 을 REFERENCE 하는
FOREIGN KEY CONSTRAINT 를 추가하시오.
ALTER TABLE S_EMP
ADD (CONSTRAINT S_EMP_MANAGER_ID_FK FOREIGN KEY(MANAGER_ID)
REFERENCES S_EMP(ID)) ;

Constraint 삭제
이미 생성되어 있는 TABLE 의 CONSTRAINT 를 삭제한다.
ALTER TABLE table명
DROP PRIMARY KEY |
UNIQUE(column명) |
CONSTRAINT constraint명 [CASCADE] ;
[ 예제 ]
S_EMP TABLE 의 다음과 같은 CONSTRAINT 를 삭제하시오.
MANAGER_ID COLUMN 의 FOREIGN KEY CONSTRAINT
ALTER TABLE S_EMP
DROP CONSTRAINT S_EMP_MANAGER_ID_FK ;

전체 데이타의 삭제
TRUNCATE 문장은 DDL 이다.
ROLLBACK SEGMENT 를 만들지 않고 모든 데이타를 삭제한다.
데이타가 삭제된 FREE 영역은 환원된다.
TABLE 로부터 모든 데이타를 삭제한다.
TRUNCATE TABLE table명 ;
[ 예제 ]
S_ITEM TABLE 의 모든 데이타를 삭제하시오.
TRUNCATE TABLE S_ITEM ;

Constraint disable/enable
TABLE 에 있는 모든 데이타가 CONSTRAINT 를 만족시켜야 ENABLE 할 수 있다.
PRIMARY KEY, UNIQUE CONSTRAINT 를 ENABLE 하면 그에 따른 INDEX FILE 이 자동적으로 생성된다.
CASCADE OPTION 은 FOREIGN KEY CONSTRAINT 를 DISABLE 할 때 사용한다.
CONSTRAINT 를 삭제하고 새로 만들지 않고 DISABLE, ENABLE 한다.
ALTER TABLE table명
DISABLE | ENABLE PRIMARY KEY |
UNIQUE(column명) |
CONSTRAINT constraint명 [CASCADE] ;
[ 예제 ]
S_DEPT TABLE 의 PRIMARY KEY CONSTRAINT 를 DISABLE 시키시오.
ALTER TABLE S_DEPT
DISABLE CONSTRAINT S_DEPT_ID_PK CASCADE;
S_EMP TABLE 의 S_EMP_DEPT_ID_FK CONSTRAINT 도 자동적으로 DISABLE 된다.

Table 삭제
TABLE 을 삭제하면 그 TABLE 에 딸린 INDEX FILE 도 삭제된다.
VIEW, SYNONYM, STORED PROCEDURE, FUNCTION, TRIGGER 등은 삭제되지 않는다.
CASCADE CONSTRAINTS 는 모 TABLE 을 삭제하고 자 TABLE 의 FOREIGN KEY CONSTRAINT 도 삭제한다.
DROP TABLE table명 [CASCADE CONSTRAINTS] ;
[ 예제 ]
S_DEPT TABLE 을 삭제하시오.
DROP TABLE S_DEPT CASCADE CONSTRAINTS ;

이름의 변경
TABLE, VIEW, SEQUENCE, SYNONYM 의 이름을 변경한다.
RENAME old명 TO new명 ;
[ 예제 ]
S_ORD TABLE 의 이름을 S_ORDER 로 변경하시오.
RENAME S_ORD TO S_ORDER ;

제 10 장. Sequence
Sequence 생성
SEQUENCE 는 여러 사용자에게 UNIQUE 한 값을 생성해 주는 OBJECT 이다.
SEQUENCE 를 사용하여 PRIMARY KEY 값을 자동적으로 생성한다.
CREATE SEQUENCE sequence명
INCREMENT BY n
START WITH n
MAXVALUE n | NOMAXVALUE
MINVALUE n | NOMINVALUE
CYCLE | NOCYCLE
CACHE n | NOCACHE ;
[ 예제 ]
S_DEPT TABLE 의 ID COLUMN 값에 사용할 SEQUENCE 를 다음과 같이 생성하시오.
START : 51, INCREMENT : 1, MAXVALUE : 9999999, NOCYCLE, NOCACHE
CREATE SEQUENCE S_DEPT_ID
INCREMENY BY 1
START WITH 51
MAXVALUE 9999999
NOCACHE
NOCYCLE ;

Sequence 변경
SEQUENCE 에 정의된 값을 변경한다.
ALTER SEQUENCE sequence명
INCREMENT BY n
MAXVALUE n | NOMAXVALUE
MINVALUE n | NOMINVALUE
CYCLE | NOCYCLE
CACHE n | NOCACHE ;
[ 예제 ]
S_DEPT_ID SEQUENCE 를 다음과 같이 수정하시오.
CACHE : 10
ALTER SEQUENCE S_DEPT_ID
CACHE 10 ;

Sequence 삭제
SEQUENCE 를 삭제한다.
DROP SEQUENCE sequence명 ;
[ 예제 ]
S_DEPT_ID SEQUENCE 를 삭제하시오.
DROP SEQUENCE S_DEPT_ID ;

제 11 장. VIEW

Simple view
SUBQUERY 문장이 간단한 경우 VIEW 를 통해 SELECT,INSERT,UPDATE,DELETE 를 할 수 있다.
■ SELECT : SUBQUERY 의 조건식을 만족하는 데이타만 검색된다.
■ INSERT : NOT NULL COLUMN 을 다 포함하고 있는 경우 INSERT 를 할 수 있다.
SUBQUERY 의 조건식을 만족하지 않는 데이타도 입력이 가능하다.
■ UPDATE : VIEW 를 통해 SELECT 할 수 있는 데이타만 수정할 수 있다.
SUBQUERY 의 조건식을 만족하지 않는 데이타도 수정이 가능하다.
■ DELETE : VIEW 를 통해 SELECT 할 수 있는 데이타만 삭제할 수 있다.
CREATE VIEW view명 [(alias명, alias명....)]
AS SUBQUERY ;
[ 예제 ]
S_EMP TABLE 에서 DEPT_ID 가 45 인 사원의 ID, LAST_NAME, DEPT_ID, TITLE 을
선택해서 VIEW 를 생성하시오.
CREATE VIEW EMP41
AS SELECT ID, LAST_NAME, DEPT_ID, TITLE
FROM S_EMP
WHERE DEPT_ID = 45 ;

With check option
VIEW 를 정의할때 지정한 조건식을 만족하는 데이타만 INSERT, 또는 조건식을 만족하는 데이터로만 UPDATE 가 가능하다.
데이타가 VIEW 의 조건식을 만족하는지 CHECK 한다.
CREATE VIEW view명 [ (alias명, alias명...)]
AS SUBQUERY
WITH CHECK OPTION ;
[ 예제 ]
S_EMP TABLE 에서 DEPT_ID 가 45 인 사원의 ID, LAST_NAME, DEPT_ID, TITLE
을 선택해서 VIEW 를 생성하시오.
단, DEPT_ID 가 45 가 아닌 사원은 입력되지 못하게 만드시오.
CREATE VIEW EMP45
AS SELECT ID, LAST_NAME, DEPT_ID, TITLE
FROM S_EMP
WHERE DEPT_ID = 45
WITH CHECK OPTION ;

With read only
SELECT만 가능한 VIEW 를 생성한다.
CREATE VIEW view명 [(alias명, alias명...)]
AS SUBQUERY
WITH READ ONLY ;
[ 예제 ]
S_EMP TABLE 에서 ID, LAST_NAME, DEPT_ID, SALARY 가 SELECT 만 되도록 VIEW 를 생성
CREATE VIEW R_EMP
AS SELECT ID, LAST_NAME, SALARY
FROM S_EMP
WITH READ ONLY ;

Force
기준 TABLE 이 존재하지 않아도 VIEW 를 생성한다.
CREATE FORCE VIEW view명 [(alias명, alias명...)]
AS SUBQUERY ;
[ 예제 ]
S_EMP TABLE 이 없어도 S_EMP TABLE 에서 ID, LAST_NAME, SALARY 를 선택해서
VIEW 를 생성하시오.
CREATE FORCE VIEW T_EMP
AS SELECT ID, LAST_NAME, SALARY
FROM S_EMP ;

complex view
SUBQUERY 문장에 JOIN, FUNCTION, DISTINCT 또는 연산이 포함된 경우를 말하며 이 경우 VIEW 를 통한 DML 은 수행할 수 없다.
COMPLEX VIEW 를 생성한다.
CREATE VIEW view명 (alias명, alias명...)
AS SUBQUERY ;
[ 예제 ]
S_EMP TABLE 과 S_DEPT TABLE 에서 ID, LAST_NAME, DEPT_ID, NAME 을
선택해서 VIEW 를 생성하시오.
CREATE VIEW EMPDEPT
AS SELECT E.ID, E.LAST_NAME, E.DEPT_ID, D.NAME
FROM S_EMP E, S_DEPT D
WHERE E.DEPT_ID = D.ID ;

View 삭제
VIEW 를 삭제하면 DATABASE 로부터 VIEW 의 정의가 삭제된다.
VIEW 가 기초로 한 TABLE 은 삭제되지 않는다.
DROP VIEW view명 ;
[ 예제 ]
EMPDEPT VIEW 를 삭제하시오.
DROP VIEW EMPDEPT ;

제 12 장. Index

Index 생성
TABLE 생성시 PRIMARY KEY 나 UNIQUE CONSTRAINT 를 지정하면 UNIQUE INDEX 가 자동적으로 만들어 진다.
이 외의 COLUMN 으로 QUERY 를 할 때 속도를 향상시키기 위해서 INDEX 를 생성한다.
INDEX 를 생성하면 QUERY 속도는 빨라질 수 있으나 DML 속도는 늦어질 수 있다.
일반적으로 다음과 같은 경우에 INDEX 를 생성한다.
■ COLUMN 이 WHERE 절이나 JOIN 조건식에 빈번하게 사용될 때
■ COLUMN 값이 넓게 분포되어 있을 때
■ COLUMN 값에 NULL 값이 많이 포함되어 있을 때
■ TABLE 이 크고 QUERY 하는 데이터 양이 10 % 이하일 때
CREATE [UNIQUE] INDEX index명
ON table명(column명[, column명...]) ;
[ 예제 ]
S_EMP TABLE 에서 LAST_NAME 의 QUERY 속도를 향상하기 위하여 INDEX 를 생성하시오.
CREATE INDEX S_EMP_LAST_NAME_IDX
ON S_EMP(LAST_NAME) ;

Index 삭제
INDEX 는 수정할 수 없다. 수정하고 싶은 경우 삭제하고 다시 생성한다.
DROP INDEX index명 ;
[ 예제 ]
S_EMP_LAST_NAME_IDX INDEX 를 삭제하시오.
DROP INDEX S_EMP_LAST_NAME_IDX ;

Posted by 시스템매니아
,
중국에서 제작된 SQL Injection을 활용한 웹 공격의 정석을 보여 주는 동영상입니다.

먼저 공격의 대상을 선정한 다음, Wiswed 공격툴을 활용하여 SQL Injection 취약점을 탐색합니다.

다음으로 모든 링크의 검색을 통해 관리자 페이지를 알아 냅니다.

SQL Injection와 Brute Force Attack을 통해 관리자 ID와 패스워드를 알아 냅니다.

관리자 페이지로 들어간 다음, 파일 업로드 메뉴에서 자바스크립트로 구현된 업로드 속성자 제한을 푼

다음,웹 쉘을 올려서 다음 공격을 준비하는 것까지의 시나리오입니다.

그럼, 관련 공부에 도움 되시기 바랍니다

http://www.metasecurity.org/securityplus/CHack.wmv 클릭하시면 영상을 받아서

보실수 있습니다.
Posted by 시스템매니아
,
Regedit 실행

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TerminalServer\Wds
\Repwd\Tds\Tcp

PortNumber 더블클릭후

10진수를 선택하고 기본포트(3389) 다른 포트번호(1234)를 입력


HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TerminalServer\WinStations\RDP-Tcp

PortNumber 더블클릭후

10진수를 선택하고 기본포트(3389) 다른 포트번호(1234)를 입력
Posted by 시스템매니아
,
이들 계정의 암호는 Windows 2000 사용자 계정 데이타베이스(SAM)에 들어 있습니다.
이들 데이터 저장소에서 암호를 얻는 것은 심각한 보안 위반이 됩니다. 몇 가지 툴을 이용하여 이들 암호를 얻을 수는 있으나,
이 암호는 메타베이스에도 있기 때문에 다음과 같은 스크립트를 Notepad같은 문자 편집기에 입력하고,
스크립트를 getpass.vbs(.vbs로 끝나기만 하면 아무 이름을 써도 됨)로 저장합니다.

------------------------------------------------------------------------
Dim IIsObject
Set IIsObject = GetObject ("IIS://localhost/w3svc")
WScript.Echo "According to the metabase, the anonymous credentials are:"
WScript.Echo " AnonymousUserName = " & _
IIsObject.Get("AnonymousUserName")
WScript.Echo " AnonymousUserPass = " & _
IIsObject.Get("AnonymousUserPass")
WScript.Echo " WAMUserName = " & IIsObject.Get("WAMUserName")
WScript.Echo " WAMUserPass = " & IIsObject.Get("WAMUserPass")
Set IIsObject = Nothing
------------------------------------------------------------------------

스크립트를 실행하면, 디폴트 IUSER_computername과 IWAM_computername 계정에 대한 암호를 볼수 있습니다.

Posted by 시스템매니아
,
바탕화면에 아무 단축 아이콘 하나를 만들고
아래와 같이 입력합니다.

대상 : C:\WINDOWS\system32\tsshutdn.exe 1800 /powerdown

그리고 시작위치에는 다음과 같이 넣으십시요..

시작위치 : C:\WINDOWS\system32

그러시고 적용하시면 됩니다.

위의 글에 보면 1800이라는 숫자가 있는데..
이숫자의 의미는 1800초 후에 꺼진다는 의미입니다.
1800초면 30분이 되죠. 만약 한시간 뒤에 꺼지게 하고 싶다면
1800이란 숫자대신에 3600이라고 쓰시면 됩니다.
Posted by 시스템매니아
,
1. SQL의 DTS WIzzard(데이타 가져오기/내보내기)를 실행합니다.

2. 데이타 원본선택란을 클릭하여 변환을 원하는 소스타입을 선택한다.
ex) Micosoft Access,Microsoft Excel등을 선택

3. 소스 타입 선택후 버튼을 클릭하여 변경을 원하는 파일을 선택한다.

4. 변환할 파일 선택 합니다.

5. 선택후 다음을 클릭합니다. Access DB의 경우 사용자 ID와 암호가 있을 경우 입력합니다 .

6. Transfer할 대상을 선택합니다. (다이렉트 웹 호스팅 DB 서버)
서버와 사용자 이름,암호 입력후 데이타 베이스를 선택합니다.
데이타 베이스선택 완료후 다음을 클릭합니다.

7. Transfer할 방법을 선택합니다.
* DB서버에 테이블로 변환시 첫번째 '원본 데이터 베이스에서 테이블 및 뷰 복사'를 선택합니다.

8. 변환할 테이블을 선택합니다. 특별한 경우 외에는 기본 옵션을 사용십시요.

9. 실행 시기를 결정합니다. 기본값은 즉시 실행입니다.

10. 확인을 눌러 변환을 실행합니다.

11. Transfer중 진행률과 현재 상태가 표시 됩니다.

12. 정상적으로 완료될 경우 이와 같은 메시지를 확인할 수 있습니다.
ex)새창에 1개의 테이블을 Microsoft Access에서 Microsoft SQL Server(으)로 복사했습니다.

13. 변환 완료 후 해당 DB서버에 접속하여 정상적으로 테이블이 생성되었는지 확인합니다.
Posted by 시스템매니아
,
터미날서비스가 정상적으로 작동중인데 접속을 하려고 하면 "접속자가 많아 접속할수 없습니다" 라는
메세지 창이 뜨는 경우가 있습니다. 이런 경우 계속 접속이 안되어 터미날서비스를 언인스톨 한 다음
다시 인스톨해도 접속이 안되는 경우가 있습니다.
"접속자가 많아 접속할수 없습니다"라는 메세지 창과 함께 계속 접속이 안될경우
터미날서비스를 언인스톨하지 말고

시작->프로그램->관리도구->터미날서비스구성에서 연결을 보시면 RDP-Tcp 연결이 있을것입니다.
이 연결을 삭제하시고 새로 생성하면 터미날서비스가 다시 작동 합니다.
Posted by 시스템매니아
,
1. Windows 2000 셋업 디스켓 또는 Windows 2000 CD-ROM으로 컴퓨터를 restart 합니다.

2. 셋업 화면이 나오면 Windows 2000 복구를 위해 <R> 키를 누릅니다.

3. Windows 2000 복구 옵션에서 복구 콘솔을 사용하여 Windows 2000 설치를 복구하기 위해 <C> 키를 누르십시오.

4. 복구를 위한 윈도우 2000 설치 영역의 선택과 관리자 패스워드 요구가 나타납니다.

5. 관리자 패스워드 입력 후 에는 바로 기본 루트인 c:\winnt 폴더로 넘어 갑니다.

6. Windows 2000 CD-ROM에 할당된 드라이브로 들어 갑니다.

7. 다음과 같이 command를 입력하고 enter 키를 누릅니다.
copy drive:\i386\ntldr c:\

copy drive:\i386\ntdetect.com c:\

만약 파일을 overwrite할것인지 물어보면 y를 입력하고 enter를 누릅니다.

8. 다음과 같이 command를 입력하고 enter 키를 누릅니다.
type c:\Boot.ini

다음과 같은 화면이 나타 납니다 :

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINNT

[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Server" /fastdetect

만약 The system cannot find the file or directory specified. 라는 메세지가 나오면 Boot.ini파일이 없거나 손상된겁니다.

9. 만약 Boot.ini파일 없거나 손상됐다면 다음의 순서로 Boot.ini 파일을 새로 만듭니다.

a. Notepad를 이용하여 다음과 같은 입력하여 새로운 boot loader파일을 만듭니다.

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINNT

[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Server" /fastdetect

b. 플로피 디스크에 저장하고 파일명을 Boot.ini로 합니다.

c. 복구 콘솔에서 다음과 같이 command를 입력합니다. 플로피에 저장한 Boot.ini파일을 컴퓨터로 복사 합니다.
copy a:\Boot.ini c:\

10.복구콘솔에서 exit를 입력하고 컴퓨터를 restart 합니다.

Posted by 시스템매니아
,
Windows가 네트워크에서 중복되는 MAC(Media Access Control) 주소를 감지하면 시스템 이벤트 로그에 다음 메시지 중 하나가 나타납니다.

Event ID: 4198
Source : TCP/IP
Description: 시스템에서 네트워크 하드웨어 주소 02:AD:8C:DE:00:FD을(를) 가진 시스템과 IP 주소 129.0.0.1의 주소 충돌이 발견되었습니다. 로컬 인터페이스를 사용할 수 없습니다.


-또는-

Event ID : 4199
Source : TCP/IP
Description: IP 주소 0.0.0.0과(와) 네트워크 하드웨어 주소 xx:xx:xx:xx:xx:xx을(를) 가진 시스템과 주소 충돌이 있습니다. 따라서 이 시스템에서 네트워크 작동이 제대로 되지 않을 수도 있습니다.

충돌 문제를 해결하고 시스템을 다시 시작할 때까지 이 컴퓨터의 TCP/IP를 통한 모든 네트워크 연결이 중단됩니다.


이 문제를 해결하려면 해당 네트워크의 다른 컴퓨터가 같은 MAC(Media Access Control) 주소를 사용하고 있는지 확인해야 합니다. Microsoft TCP/IP 스택과 함께 제공되는 몇 가지 도구를 사용하면 중복된 주소를 가진 컴퓨터를 찾을 수 있습니다.

중복된 MAC(Media Access Control) 주소를 분리하려면 다음과 같이 하십시오.

작업 중인 TPC/IP 기반 클라이언트에서:

1. 명령 프롬프트에서 아래와 같이 입력하여 이벤트 로그 항목에 나와 있는 TCP/IP 주소를 Ping합니다.

PING 129.0.0.1

중복된 주소를 가진 컴퓨터에서 다음과 비슷한 응답을 받습니다.

Pinging 129.0.0.1 with 32 bytes of data:

Reply from 129.0.0.1: bytes=32 time=10ms ttl=128
Reply from 129.0.0.1: bytes=32 time=<10ms ttl=128
Reply from 129.0.0.1: bytes=32 time=<10ms ttl=128
Reply from 129.0.0.1: bytes=32 time=<10ms ttl=128


2. 해당 컴퓨터의 MAC(Media Access Control) 주소가 중복되었는지 확인하려면 명령 프롬프트에서 아래와 같이 입력합니다.

ARP -a 129.0.0.1

다음과 같은 응답을 받습니다.

Internet Address Physical Address
129.0.0.1 02:A0:8C:DE:00:FD <-- matches the event log entry

3. 이제 명령 프롬프트에서 아래와 같이 NBTSTAT 명령을 사용하여 중복 컴퓨터의 익숙한 NetBIOS 이름을 가져올 수 있습니다.
NBTSTAT -A 129.0.0.1

해당 컴퓨터의 NetBIOS 이름과 함께 아래와 같은 응답을 받습니다. 네트워크에서 해당 컴퓨터를 찾을 수 있도록 해당 컴퓨터의 소유자를 확인하려면 이 NetBIOS 이름을 사용합니다.

NAME TYPE STATUS
-----------------------------------------------
NTSERVER1 <00> Unique
DOMAN-NAME <00> GROUP
NTSERVER1 <03> Unique

Media access control address = 02-A0-8C-DE-00-FD

다음 메시지가 나타나면,

HOST NOT FOUND.
이 메시지는 해당 중복 컴퓨터가 Novell 서버, Unix 서버, Router 또는 Jet Direct Printer처럼 NetBIOS를 사용하지 않는 컴퓨터임을 나타냅니다.
중복된 주소를 가진 컴퓨터를 찾은 후에 해당 네트워크 카드를 대체하거나, 로컬로 관리되는 MAC(Media Access Control) 주소로 변경된 경우 해당 네트워크에서 고유한 주소를 갖도록 변경합니다.
Posted by 시스템매니아
,
이벤트 뷰어에 다음과 같은 오류 메세지를 종 종 볼수 있습니다.

Windows 2000 인터넷 프로토콜(IP) 스택이 여러 네트워크 카드(IP)에 바인딩되어 있을 때 다음 이벤트가 발생할 수 있습니다.
이벤트 ID: 4000
원본: SMTPSVC
종류: 경고
설명: Message delivery to the remote domain "your.domain.com" failed. 내부 DNS 오류 때문에 원격 서버를 찾을 수 없습니다.
0000: c00402e7

원인은 SMTP 서비스는 메일을 배달하려고 할 때 DNS(도메인 이름 서비스) 서버를 검색하여 DNS 조회를 수행합니다. TCP/IP가 여러 IP에 바인딩되어 있으면 바인딩 순서에 따라 첫번째 IP 주소에 연결하려고 합니다. 이 IP가 DNS가 반환한 MX 레코드에 전달할 수 있는 IP가 아니면 연결이 실패하고 이벤트 4000이 로깅됩니다. 메시지가 재시도 상태에 들어가고 그 결과 시간이 초과되기 때문에 배달 못 함 보고서(NDR)가 생성됩니다.


이 문제를 해결하려면 다음과 같이 하세요.

1. 바탕 화면에서 네트워크 환경을 마우스 오른쪽 단추로 누릅니다.

2. 등록 정보를 누릅니다.

3. 고급 메뉴에서 고급 설정을 누른 다음 인터넷 IP의 바인딩 순서를 내부 IP보다 앞쪽으로 변경합니다. 이렇게 하면 DNS 쿼리가 조회를 수행하려고 할 때 정확한 네트워크 연결을 찾습니다.

Posted by 시스템매니아
,