망할고양이의 도피처

* SQL Server 2008 이후버전에서 테스트 되었습니다.

기본적으로 사용법은 아래와 같다.

CONVERT( [포맷(길이)], [날짜 값], [변환형식] )

select convert(varchar, getdate(), 120)       -->  2021-01-12 21:23:16
select convert(varchar(10), getdate(), 120)  -->  2021-01-12
select convert(varchar(16), getdate(), 120)  -->  2021-01-12 21:25

그외 변환형식

매번 느끼는 것 이지만.. MS쓰는 쓸때마다 불편하다..

사용법

1. windows Key + R   "CMD" 입력

2. cmd 창에서 아래와 같이 입력

※ 오라클 서버 또는 오라클 클라이던트가 설치 되어 있는 경우를 가정하여 작성하였습니다.

Clustered Index & Non-Clustered Index 

테이블을 생성 후 Primary Key를 생성하면 기본적으로 Clutered Index로 지정이 됩니다.(클러스터형 인덱스가 없는 경우)

하지만, 데이터를 파악하고 인덱스를 걸면 가장 효율적인 컬럼을 클러스터 인덱스로 지정하는것을 권장합니다.

Clustered Index

• 해당 컬럼을 기준으로 정렬, 테이블당 1개씩만 허용
• Clustered Index가 적용된 컬롬은 CUD 작업이 진행될 경우 데이터의 정렬에 관여합니다. 
• 되도록이면 UNIQUE 해야합니다.  UNIQUE하지 않으면 안되는것이 아니라 UNIQUE하지 않는 경우 오버헤드가 발생하는 것을 인지해야합니다.
• SP_COLUMNS 명령을 이용해서 사용빈도(높음), 데이터길이(짧음), UNIQUE여부(되도록이면) 인 컬럼을 지정합니다.
• 목차를 보고 해당 페이지를 찾아 가는것이 아니라 바로 해당 페이지를 찾아가는 것입니다.

Non-Clustered Index

• 한 테이블에 여러개를 생성 할 수 있습니다. (최대 240개)
• 목차를 보고 해당 페이지를 찾아가는 형태의 검색 방식입니다
• 테이블에 대한 인덱스를 많이 만들면 테이블의 데이터가 변경될 경우 인덱스도 모두 적절하게 조정되어야 하므로 INSERT, UPDATE, DELETE 및 MERGE 문의 성능이 저하될 수 있습니다.
• 쿼리의 조건자 및 조인 조건에서 자주 사용되는 열에 대해 비클러스터형 인덱스를 만듭니다. 

ORA-01502: 인덱스 분할영역은 사용할 수 없은 상태입니다 를 만났다.

이러한 현상은 보통 테이블 파티션이 이동하거나 , 테이블 스페이스 이동 , 병합등의 사유로 인덱스 참조가 깨진 현상이라고 볼 수 있다

해결방법은 가장 간단한 방법으로 Index Rebuild를 수행한다.

alter index your_table_name rebuild

위 구문으로 해결할 수 있다. 그러나..

파티션 테이블로 구성되어 있다면 다른 구문을 사용해야한다.

아래와 같은 구문을 수행해야하며 파티션 단위로 재 생성 해주어야 한다.

alter index your_table_name rebuild partition your_partition_name ;

global partition index의 경우 non-partition index, partition index 등 파티션 테이블 관련 작업인 경우 재 생성이 필수 이다.

따라서 파티션 테이블별로 global index의 rebuild 작업용의 스크립트를 작성해 놓아야 빠른 시간안에 관련 인덱스를 재 생성 할 수 있다

Rebuild 일괄생성 구문

SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD ;' FROM USER_INDEXES;

Query 작성 중 다 외우기는 힘들어 (외우긴 했으나 사용법이...) 검색해야 할 때가 있어 정리합니다.

LOWER( column|expression ) 

괄호 안의 텍스트를 소문자로 전환합니다.

LOWER('ABCDEFG') --> abcdefg : 

UPPER( column|expression ) 

괄호 안의 텍스트를 대문자로 전환합니다.

UPPER('aabbCC') --> AABBCC

INITCAP( column|expression ) 

문자열의 첫 글자를 대문자로 전환 후 이후 텍스트는 소문자로 전환합니다. 
INITCAP('hello') --> Hello

CONCAT( column1|expression1 ,column2|expression2 ) 

문자열을 연결(붙여)준다. || 와 같은 효과를 함수로 사용할 때 사용한다.
CONCAT('AA','BBccc')  --> AABBcc

SUBSTR(column|expression, m [,n])   

substring 이랑 비슷한 기능을 수행한다. 차이첨은  m 번째부터 n 번째까지 인덱스 값으로 n 값이 m에 상대적이라는 것이 차이점이다.

시작 인덱스가 음수인경우 뒤에서부터 인덱스를 계산한다.
SUBSTR('A1B2C3',1,3) --> A1B

LENGTH( column|expression ) 

문자열의 길이를 리턴한다. 

LENGTH('ABCDEF') --> 6 : 

INSTR( column|expression, str) 

indexof와 동일한 기능을 제공한다. 즉, 문자열에 해당하는 문자가 있는지 확인하여 해당 문자의 인덱스를 리턴한다.

INSTR('AARCCC','R') --> 3 : 문자열에 'R'의 인덱스(몇 번째 위치)를 리턴한다. 

LPAD( column|expression,n,str ) : n 은 전체 길이  , str 대체 문자

주어진 문자열에 대해서 n 에대한 자릿수만큼 str의 문자(or 숫자)를 왼쪽에 체워 넣습니다. 
LPAD('ABC',10,'0') --> 0000000ABC

RPAD( column|expression,n,str ) : n 은 전체 길이  , str 대체 문자

주어진 문자열에 대해서 n 에 대한 자릿수만큼 str의 문자(or 숫자)를 오른쪽에 채워 넣습니다. 

RPAD('ABC',10,'0') --> ABC0000000

LTRIM( column|expression, str) 

문자열의 왼쪽 공백(여백) 문자를 제거한다. 또한 LTRIM은 왼쪽 반복적인 문자(str)나, 특정 문자열을 제거할 수 있다 제거할 문자가 동일하다면 옵션의 문자 개수는 무관하다

LTRIM('      test_Text') --> 'test_Text' 

RTRIM(column|expression)

문자열의 오른쪽 공백(여백)문자를 제거한다. 또한  오른쪽의 반복적인 문자를 제거할 수 있다. 제거할 문자가 동일하다면 옵션의 문자 개수는 무관하다

LTRIM('test_Text          ') --> 'test_Text' 

TRIM(leading/tailing/both, trim_character FROM target_source ) 

기본적으로 TRIM 함수는 문자열의 공백(스페이스바)을 제거합니다. (양쪽 모두). 문자와 문자 사이의 공백은 제거하지 못합니다. 

TRIM('     NEW ITEM     ') --> 'NEW ITEM'

특정 문자를 제거하는 목적으로 사용할 경우에는 아래와 같이 사용할 수 있다.
TRIM( 'S' FROM 'SSMITH') --> MITH 

REPLACE(source_text, target_str, conv_str)

source text에서 target_str에 해당하는 문자(or문자열)를 conv_str로 대체합니다.

REPLACE('last King', 'last', 'NEW') --> NEW King

Oracle 함수는 크게 단일행 함수와 복수행 함수로 구분할 수 있다.

Oracle에는 많은 함수가 존재한다. 함수는 Query가 쉬워지고 응용프로그램의 코딩을 줄여줄 수 있다

따라서 함수는 많이 알고 있으면 좋다.

가끔 쓰이는 몇가지 함수를 알아보도록 한다.

NVL2 (Column, Express1, Express2)

해당 컬럼이 NULL이면 express-1의 값을, NULL이 아니면 express-2의 값을 리턴해 줍니다. 

NULLIF (Express1, Express2)
Express-1과 express-2의 값을 비교하여 그 값이 같으면 NULL을 리턴 하고 다르면 express-1의 값을 리턴해 줍니다.

COALESCE(Express1, , , , ExpressN)
Express-1이 NULL이 아니면 express-1을 리턴하고, NULL이면 express-2 를, express-1과 express-2 모두 NULL이면 express-n을 리턴해 줍니다.

TRIM ( LEADING [문자열] FROM col1 ) ;

LEADING은 왼쪽 문자열을 절삭 시켜 줍니다.

TRIM ( TRAILING [문자열] FROM col1 ) ;

 TRAILING은 오른쪽 문자열을 절삭 시켜 줍니다. 

TRIM ( BOTH [문자열] FROM col1 ) ;

BOTH는 양쪽 문자열을 동시에 절삭 시켜 줍니다.

SELECT 그룹함수([DISTINCT]/all]),
{column [Alias],...}
FROM 테이블명
[WHERE Query 조건(들)]
[GROUP BY 컬럼1, 컬럼2, …n]
[HAVING Group-Conditiona]
[ORDER BY 컬럼1, 컬럼2, …..[ASC/DESC]];

GROUP BY : 결과값을 지정한 컬럼을 기준으로 그룹화
HAVING : GROUP BY에 의한 결과에 대한 조건 절
ORDER BY : 결과값을 분류(Sorting)할 때

관리자로 로그인 되어있는 상태를 기준으로 작성되었습니다.

1. 사용할 데이터베이스를 선택하기 위해서 현재 생성되어 있는 데이터베이스 목록을 확인합니다.

2. 사용할 데이터 베이스를 지정합니다. 

3. 사용자 생성 및 해당 데이터베이스르 사용할 수 있는 권한을 부여합니다.

4. 권한 및 계정정보를 서버에 재적용합니다.

End. 생성된 계정으로 로그인하여 지정한 데이터베이스에 접속이 되는지 확인합니다.

환경 : windows 7 / MySQL 5.6 에서 테스트 되었습니다. 

1. MySQL 서비스를 종료합니다.

2. 관리자 권한으로 Cmd를 실행합니다.

3. 다음의 명령어 입력 ( --skip-grant-tables 는 주석이 아닙니다.)

mysqld -P3306 --skip-grant-tables

4. 새로운 CMD 창을 관리자로 실행합니다. (관리자 패스워드 없이 루트권한으로 로그인 됩니다.)

mysql -P3306 mysql

5. 아래의 쿼리로 패스워드를 변경합니다.

update mysql.user set authentication_string=password('new_password') where user='root';

Oracle 설치 후 계정을 생성한 뒤 시스템을 사용하는데 갑자기 시스템 로그인이 안될 때가 있다

시스템 로그에 ORA-28001: the password has expired 가 있다면 쉽게 해결 할 수 있다.

아래 Query를 이용하여 프로필 정보를 확인하면 

아래와 같이 붉은 글자 항목이 180으로 설정되어있을 것이다. 

이 값을 UNLIMITED로 변경해주면 해결이 가능하다.

ORA-12631 : 이름 검색에 실패

TNS-12631 Username retrieval failed

어느 날 갑자기 혹은 PC를 교체하여 똑같은 환경을 구성했음에도... 위와 같은 오류가 발생하는 경우에는 아래와 같이 조치하면 해결할 수 있습니다. 

sqlnet.ora 파일을 찾아서 내용을 보면 아래와 같습니다. 

해당 파일의 내용중 

윗부분을 주석처리 하고 아래와 같이 변경합니다.