본문 바로가기

프로그래밍/DBMS40

오라클, 중복 데이터 삭제하기 아주 간단한 테이블을 만들더라도 잘 생각해야 된다. 귀찮다고 실천하지 않으면 꼭 이 작업을 하게 된다.반드시 데이터 무결성을 위한 수고를 아끼지 말자.어쨌든, 중복데이터를 삭제해야 할 일이 생겼다. 물론 테스트 중인 프로세스여서 크게 문제는 없지만..운영중에 발생했다면 아주 짜증 났을듯. 아래처럼 하자. delete from table_awhere rowid in (select rowidfrom(select * from(select ROW_NUMBER() OVER (PARTITION BY key_col_1 ORDER BY key_col_1) AS num, key_col_1from table_a)where num >= 2)) 2014. 4. 1.
MSSQL 테이블 변경 시, "변경 내용을 저장할 수 없습니다.." 나올 때 테이블 디자인을 변경하려고 하니 "변경 내용을 저장할 수 없습니다" 메시지가 나왔다. 아래와 같이 옵션을 조절한다.단, 해당 옵션을 해제할 경우 문제가 발생할 수 있다고 하니 MSDN을 반드시 확인하고 적용할 것. 테이블 다시 생성 해야 하는 변경 내용을 저장 안 함 옵션을 변경 하려면 다음과이 같이 하십시오.SQL Server Management Studio (SSMS)를 엽니다.도구 메뉴에서 옵션을 클릭 합니다.옵션 창의 탐색 창에서 디자이너를 클릭 합니다.선택 또는 테이블 다시 생성 해야 하는 변경 내용을 저장 안 함 확인란의 선택을 취소 하 고 확인을 클릭 합니다. http://support.microsoft.com/kb/956176/ko위 링크 확인할 것. 2014. 3. 12.
MSSQL, 테이블 사용중인 프로시져 알아내기 Select object_name(id) From syscomments Where text like '%테이블명%' Group by object_name(id) Order by object_name(id) 위 처럼 간단하게 조회할 수 있다. 끗 2013. 12. 18.
MSSQL 플랜캐시 확인 쿼리 얼마 전 서버 이전 작업을 했다. 그런데 정상으로 작동하던 시스템이 조금 꼬이는 현상이 발견됬다.그중 특이한 증상 중 하나... 쿼리 분석기와 클라이언트(웹단)의 프로시저 실행 속도의 차이가 발생하는 것이었다. 웹단은 팝업창이 실행되면서 콤보 박스에 데이터를 세팅하는 부분이 있었는데, 그 프로시저에 문제가 발생하게 된 것.프로파일러로 추적하니 15초가 소요되는 것을 발견, 쿼리분석기로 직접 실행하니 1초. 하참~~이런 저런 방법을 써봤으나, 무용지물. 해결 방법 찾던 중 아래의 프로시저의 플랜캐시 확인에 대한 글이 있어서 참조해 봤다. 해결은 이 글과는 크게 상관 없었지만 힌트는 얻을 수 있었다. 아래는 플랜캐시를 확인했던 쿼리와 펌링크이다.출처 : http://www.sqler.com/269485쿼리S.. 2013. 9. 25.
64bit MSSQL 2008 R2 에서 32bit Oracle DB 링크 걸기 후아~ 진짜 고생했다. 오라클 클라이언트만 10번도 넘게 깔았다 지웠다 했네... 찾다찾다 글 하나를 발견하는데....유레카~~ 아래 포스팅을 따라서 했더니 되었다.http://blog.naver.com/PostView.nhn?blogId=metalbear&logNo=40170264823&redirect=Dlog&widgetTypeCall=true위 포스트 주인장께 감사드린다.아래는 주인장이 참고했다는 원글 주소http://thiruna.blog.com/2010/10/28/making-linked-server-connection-between-sql-server-64-bit-oracle-32-bit/ 요약1. 오라클 클라이언트 32비트를 설치한다.2. 오라클 클라이언트 64비트를 설치한다. 단, 32 비.. 2013. 8. 29.
MSSQL 문자열 잘라서 테이블 반환 함수 ALTER FUNCTION [dbo].[FN_SPLIT]( @StrValue VARCHAR(MAX), -- 분리할 문자열 @SplitChar VARCHAR(1) -- 구분할 문자) RETURNS @SPLIT_TEMP TABLE ( VALUE VARCHAR(50) )AS BEGIN DECLARE @oPos INT, @nPos INT DECLARE @TmpVar VARCHAR(1000) -- 분리된 문자열 임시 저장변수 SET @oPos = 1 -- 구분문자 검색을 시작할 위치 SET @nPos = 1 -- 구분문자 위치 WHILE (@nPos > 0) BEGIN SET @nPos = CHARINDEX(@SplitChar, @StrValue, @oPos ) IF @nPos = 0 SET @TmpVar = R.. 2013. 4. 24.
오라클 DECODE, GREATEST 로 음수값 처리하기 음수면 0으로 반환하고 싶을 때, 처리하는 방법을 궁리했다. 구글의 힘으로 조합하니 아래와 같이 2가지 정도가 좋을 듯. 음수면 무조건 0을 반환하고 싶으면 GREATEST 함수를 사용하자.GREATEST(변수, 0) 하면 끝이다. 이 함수는 인자 중에 가장 큰 값을 반환해 주는 함수인데, 음수 보다 0이 크니 변수가 음수일 경우 0이 반환된다. 두번째는 부호에 따라 처리해야 하면DECODE(SIGN(변수), 1, 0, 변수)SIGN함수는 부호를 반환해 주는데, 양수면 1, 음수면 -1, 0이면 0이 반환된다. 그에 따라 알아서 사용하면 될 듯. 끝~ 2013. 4. 10.
MSSQL 0으로 나누기 오류 처리하기 ISNULL로 처리해도 되지만 부득이하게 0으로 나누는 오류를 봐야 할 경우 아래 옵션을 추가하면 NULL로 반환해 준다. SET ANSI_WARNINGS OFF; SET ARITHIGNORE ON; SET ARITHABORT OFF; 가끔 써먹을 일이 있어서 적어둠 2013. 4. 9.
MSSQL, 줄리안데이트 <-> DATETIME 바꾸는 함수 DATETIME -> 줄리안데이트 CREATE function [dbo].[DATETIME_TO_JULIAN](@date as datetime) returns int as begin -- DataTime 값을 줄리안데이트 형식으로 변환 declare @return as int, @tempdate as char(8), @basedate as datetime, @tempyyyy as int, @interval as int set @tempdate = convert(char(8), @date, 112) set @tempyyyy = year(@date) -- cast(left(@tempdate, 4) as int) set @basedate = convert(datetime, left(@tempdate, 4) +.. 2012. 1. 2.
MSSQL, 줄리안 데이트로 바꾸기 이달말일 SELECT ( DatePART ( yy, ( DATEADD(day, -1, DATEADD ( mm, 1, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)) ) ) ) -1900 ) * 1000 + DatePART ( dy, ( DATEADD ( day, -1, DATEADD(mm, 1, DATEADD (mm, DATEDIFF(mm, 0, getdate()), 0) ) ) ) ) 전달말일 SELECT ( DatePART ( yy, ( DATEADD(day, -1, DATEADD ( mm, 0, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)) ) ) ) - 1900 ) * 1000 + DatePART ( dy, ( DATEADD ( day.. 2012. 1. 2.
오라클 스케줄러 로그 확인 쿼리 10분 단위로 돌아가는 스케줄러인 경우 로그가 많이 쌓인다. 때로는 실패한 스케줄이 있는지 확인해야 하는데 그때 사용할 수 있는 쿼리이다. select * from user_scheduler_job_log where job_name='스케줄명' and status='FAILED' 지속적으로 체크를 할 필요성이 있으니 꼭 알아둘 것. 2011. 7. 5.
오라클 스케줄러, Toad 사용하기 오라클 인터페이스 작업을 위해 Job을 사용하려다 Scheduler를 사용하라는 얘기에 적용해 보았다. 오라클 : 10.0.2 Toad : 9.0.1.8 OS : Windows 7 Enterprise 큰 흐름은 스케줄러의 스케줄(일정)을 만들고 스케줄러 잡에 그 스케줄을 적용하는 것이다. 스케줄을 만들지 않고 스케줄러 잡에서 직접 스케줄을 생성할 수도 있으니 참고하자. 스케줄을 따로 만드는 것은 하나의 스케줄로 여러개의 스케줄러 잡에 적용할 수 있다는 장점이 있다. 특정한 시간에 인터페이스가 되는 정보가 많다면 스케줄(일정)을 먼저 만든 후 적용하면 추후에 일괄 변경하기도 간편해 진다. 아래 Schema에 들어간 데이터는 각자의 세팅에 따라 달라진다. ※ 이미지 중 뭉게진 부분은 토드에서 자동으로 잡거나.. 2011. 6. 22.
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다 일반 쿼리로는 문제없이 사용했던 코드를 프로시져에 집어넣었더니 딱 하고 저 에러가 떴다. 분명히 테이블은 존재하고 일반 SELECT 문으로도 조회가 잘 된다. 그런데도 프로시져에 위치시키면 제목과 같은 에러를 뱉어낸다. 여러 테이블이 존재하는데 유독 한 테이블만 이런 현상이 발생했다. 킁.. 구글링~~ 존재하는 테이블인데도 읽지 못하는 것은 권한 문제라고 한다. 그럼 그 한 테이블만 권한 설정을 잘못해서 만들었다는 거냐. 만든넘 누구냐~. 시스템 유저로 들어가서 권한을 할당해주는 작업을 진행했다. 인터페이스 용 테이블이여서 다른 권한은 없어도 되고 SELECT 권한만 부여했다. grant select on [유저명.권한을 할당할 테이블명] to [테이블에 권한을 행사 할 유저명]; 요렇게 처리하고 프로시.. 2011. 6. 14.
오라클 MERGE INTO - 한번에 INSERT, UPDATE 하기 가끔 두 테이블을 비교해서 INSERT를 하거나 UPDATE 를 해야하는 경우가 발생한다. MSSQL 때는 IF EXISTS ... THEN 구문을 사용했었다. 사실 더 좋은 쿼리가 있었을지도 모르지만 내가 모르니까 모르는 것이겠지. 오라클로 인터페이스 작업을 하다보니 좀 파워있게 사용해야할 일이 많이 생겼다. 사실 인터페이스 작업이야 INSERT, UPDATE가 전부이니까. 오라클은 MSSQL같이 사용하지 못하길래 찾아봤더니 아주 멋진 녀석이 있었다. 단 제약조건은 좀 있지만서도. 일단은 오라클 9 버젼 이상부터 지원하는 것 같다.(아니면 강력하게 태클 요청 드림) 기본 구문은 아래에 있음. MERGE INTO 타겟테이블 TT USING 소스테이블 ST ON (TT.필드1=ST.필드1 AND TT.필드.. 2011. 6. 3.
오라클 Job을 테스트하자. 이번에 오라클로 인터페이스 작업을 하게 됬다. Job으로 프로시져를 실행시키는 작업을 해야 할 것 같아서 테스트를 진행해 봤다. win 7 x86 + 오라클 10g + 토드 9.0.1 으로 작업했다. 하단 스크립트들은 모두 토드의 마법사로 만든 것이다. 엑셀에 정리한 내용을 옮겨왔더니 무지막지하게 보기 싫어져 버렸다. 테스트 시나리오 테스트를 진행하기 전 가상의 시나리오를 생성한다. 1. 테이블을 생성한다. 테이블명은 test_schedule 필드구성은 일련번호, 비고, 입력일시로 간단하게 구성 일련번호는 시퀀스이고 시퀀스명은 SQ_TEST_SCHEDULE 임 sql 스크립트를 백업한다. 2. 프로시져 생성 프로시져명은 sp_test_schedule_ins test_schedule 테이블에 insert .. 2011. 4. 14.
toad로 오라클 연결하기 toad for oracle xpert v9.0 버젼을 설치한다. 아 고민스러운데 이 토드 버젼을 설치하면 c에 oracle 폴더가 생겼던 건지 아님 그 뒤에 설치가 실패했던 10g 버젼의 오라클 클라이언트 설치 중에 c에 oracle 폴더가 설치됬던건지 확인이 안된다. 업데이트 : 오라클 클라이언트를 설치해야 아래 폴더가 나타납니다. 어쨌든 C:\oracle\product\10.2.0\client_1 라는 폴더가 토드설치 후에 만들어져 있었다는 기준으로 작성한다. 1. client_1 폴더에 network\admin 폴더를 만든다. 2. admin 폴더안에 tnsnames.ora 파일을 만든다. 3. 토드를 실행시킨다. 4. session 메뉴의 new connect 메뉴를 선택한다. 5. toad 설.. 2011. 3. 16.
[MSSQL] DB에 속한 테이블명 알아내기 MSSQL에서 DB안에 속한 테이블명 알아내는 쿼리.use pubs select * from information_schema.tables 그러면 테이블의 스키마(필드구조)를 보고 싶다면?select * from information_schema.columns where table_name='titleauthor' 2011. 1. 3.
[MSSQL] Create Function  mssql 에 함수를 만들어보자 create function 함수명(인자1 변수타입, 인자2 변수타입....) returns 반환 변수타입 as begin declare @변수 데이터타입 . . 함수 내용 작성 return 반환값[변수] end # 반환 변수타입 : table 이나 스칼라 변수타입(char(50), varchar(10), int 같은) 등이 올 수있다. 왠만하면 함수 만들지 않는 로직으로 가자.. 괜히 복잡하기만 하다 2010. 12. 8.
[MSSQL] 문자열 Substring substring(문자열, 자르기시작할인덱스, 자를길이) 문자열의 시작 인덱스는 1임 좀 외우자 앙~~ 에혀~  2010. 12. 8.
DB간 테이블 복사 쿼리 select * into db명.소유자명.타겟테이블명 from db명.소유자명.원본테이블명 간단히 될줄 알았으나 소유자명을 안넣어서 헤맴.. 소유자명 꼭 넣을것  2010. 12. 8.