'Database'에 해당되는 글 38건
- 2014.09.25 공백 구분자로 텍스트 자르기
- 2014.09.24 특수문자 기준 문자열 자르기 문의 드립니다~
- 2014.08.27 윈도우7에 오라클 11g 클라이언트 설치
- 2014.05.29 프로시저 검색하기
- 2014.02.20 오라클 힌트 정리표
- 2014.02.20 [오라클] TABLE ANALYZE 하기
- 2013.09.12 숫자/문자 체크
- 2012.02.07 오라클 wait
- 2012.02.03 오라클 OBJECT 조회(USER_SOURCE 활용)
- 2012.02.03 오라클 TIMESTAMP
- 2011.03.02 오라클 테이블 복사 쿼리
- 2011.03.01 crow의 오라클 백업/복구 제2회
- 2011.03.01 crow의 오라클 백업/복구 제1회
- 2011.02.28 SYS_CONNECT_BY_PATH , CONNECT BY
- 2010.10.07 SQL) 세로를 가로의 데이터로 만들기
with a as (
select '제주특별자치도 서귀포시 광평로 105 ( 안덕면, 통나무힐스 )' bigo from dual
union all
select '부산광역시 강서구 대저1동 4302-22' from dual
)
select bigo
, regexp_substr(bigo, '[^ ]+',1,1) a
, regexp_substr(bigo, '[^ ]+',1,2) b
, regexp_substr(bigo, '[^ ]+',1,3) c
, regexp_substr(bigo, '[^ ]+',1,4) d
, regexp_substr(bigo, '[^ ]+',1,5) e
from a
문자열 ';' 기준으로 아래 값들을
HT1816112121400121;로스엔젤레스;부산광역시 강서구 대저1동 4302-22
HT7014021412091123;대한민국;부산광역시 강서구 대저1동 4302-22
HT1611221911240214;오스트레일리아;부산광역시 강서구 대저1동 4302-22
A컬럼에 HT1816112121400121
B컬럼에 로스엔젤레스
C컬럼에 부산광역시 강서구 대저1동 4302-22
A,B,C 컬럼에 나누어 넣으려고 합니다...
SELECT BIGO, SUBSTR(BIGO,1,INSTR(컬럼,';')-1) A, REVERSE(SUBSTR(REVERSE(BIGO), 19, INSTR(REVERSE(BIGO), ';') -1)) B, REVERSE(SUBSTR(REVERSE(BIGO), 1, INSTR(REVERSE(BIGO), ';') - 1)) C
FROM 테이블명 WHERE BIGO LIKE '%;%'; 이렇게 했더니...
B에 들어가는 값들이 '로스엔젤레스;부산광역시' 또는 '대한민국;부산광역시 강서구' 이런식으로 들어가는데요...
B컬럼 구문 어느곳이 틀린건지 문의 드립니다...
그리고 가운데 '로스엔젤레스, 대한민국, 오스트레일리아' 만 가져오려고 구문을 짜봤는데
'로스엔젤레스;부산광역시' 이렇게 나옵니다...
SELECT BIGO
, REGEXP_REPLACE(SUBSTR(BIGO, INSTR(BIGO, ';', 1) +1), '\(;*\)') T
FROM (SELECT BIGO FROM 컬럼명);
두개의 쿼리에 대해 조언 부탁드리겠습니다.....
| 글 등 록 | 목록보기 |
윈도우7에 오라클 11g 클라이언트를 설치해보겠습니다.
오라클은 서버와 클라이언트로 나뉘어지는건 다들 아시죠?
서버는 누군가 다른 사람들이 접속할 수 있는 장소를 만드는 곳이고
클라이언트는 다른 서버로 접속하기 위한 것입니다.
따라서 클라이언트를 설치한다면
데이터베이스에 접속하는 것은 가능하나
데이터베이스를 생성하는 것은 안됩니다.
데이터베이스를 생성할 목적이라면 서버를 설치하시면 되겠습니다.
설치 방법은 간단합니다.
http://www.oracle.com/technetwork/topics/winsoft-085727.html
홈페이지로 이동하여 밑에 두 파일을 다운로드 받습니다.
|
|
두 파일을 같은 폴더에 압축을 풀어 놓습니다.
tnsname 파일을 위치하기 위해 다운받은 폴더 밑에 network - admin 폴더를 생성하고
tnsname 파일을 위치시킵니다.
여기까지 하면 설치는 끝났습니다.
마지막으로 패스만 설정하면 설치가 완료됩니다.
<내컴퓨터> <속성> <고급 시스템 설정> <환경변수> <시스템 변수> 로 들어가
환경변수를 설정합니다.
1. ORACLE_HOME -> 다운받은 오라클 폴더(ex. C:\instantclient_11_2)
2. TNS_ADMIN -> tnsname파일이 위치한 폴더
3. path -> 다운받은 오라클 폴더(ex. C:\instantclient_11_2)
원격 접속지로 접속이 되는지 확인합니다.
시작프로그램 - 실행 - cmd 입력하여 cmd창으로 이동합니다.
sqlplus 아이디/패스워드@원격지tnsname
정상적으로 접속이 되면 설치가 완료되었습니다.
접속이 되지 않으면 tnsname 파일을 확인하여 정보가 맞는지 확인합니다.
윈도우 환경변수 등록 (시스템 환경변수)
변수명 - NLS_LANG
변수값 - KOREAN_KOREA.KO16MSWIN949
[출처] 윈도우7 오라클 11g 클라이언트 설치하기, 다운받기|작성자 여행하면 성공한다
http://blog.naver.com/itough2323?Redirect=Log&logNo=80125972268
http://blog.naver.com/explojh?Redirect=Log&logNo=60036603826
◆ 개요
힌트는 SQL 튜닝의 핵심부분으로 일종의 지시구문이다.
SQL에 포함되어 쓰여져 Optimizer의 실행 계획을 원하는 대로 바꿀 수 있게 해준다.
오라클 Optimizer라고 해서 항상 최선의 실행 계획을 수립할 수는 없으므로
테이블이나 인덱스의 잘못된 실행 계획을 개발자가 직접 바꿀 수 있도록 도와주는 것이다.
사용자는 특정 SQL 문장에서 어떤 인덱스가 선택도가 높은지에 대해 알고 있는데
이 경우 오라클 서버의 Optimizer에 의존하여 나온 실행 계획보다 훨씬 효율적인 실행 계획을 사용자가 구사할 수 있다.
◆ 사용
힌트를 사용하여 아래와 같은 것들을 할 수 있다.
액세스 경로, 조인 순서, 병렬 및 직렬 처리, Optimizer의 목표(Goal)를 변경 가능하다.
◆ 오라클 힌트 사용예
|
SELECT /*+ INDEX(idx_col1) */ |
|
SELECT /*+ ORDERED INDEX(b idx_col1) */ |
※ 주의! 주석 표시 뒤에 '+' 기호가 있다.
◆ INDEX Access Operation 관련 HINT
|
HINT |
내용 |
사용법 |
|
INDEX |
INDEX를 순차적으로 스캔 |
INDEX(TABLE_name, INDEX_name) |
|
INDEX_ASC |
INDEX를 내림차순으로 스캔. |
|
|
INDEX_DESC |
INDEX를 오름차순으로 스캔. |
INDEX_DESC(TABLE_name, INDEX_name) |
|
INDEX_FFS |
INDEX FAST FULL SCAN |
INDEX_FFS(TABLE_name, INDEX_name) |
|
PARALLEL_INDEX |
INDEX PARALLEL SCAN |
PARALLEL_INDEX(TABLE_name, INDEX_name) |
|
NOPARALLEL_INDEX |
INDEX PARALLEL SCAN 제한 |
NOPARALLEL_INDEX(TABLE_name, INDEX_name) |
|
AND_EQUALS |
여러개의 INDEX MARGE 수행 |
AND_EQUALS(INDEX_name, INDEX_name) |
|
FULL |
FULL SCAN 지정된 테이블에 대한 전체 스캔. |
FULL(TABLE_name) |
◆ JOIN Access Operator 관련 HINT
|
HINT |
내용 |
사용법 |
|
USE_NL |
NESTED LOOP JOIN 옵티마이저가 NESTED LOOP JOIN을 사용하도록 한다. 먼저 특정 TABLE의 ROW에 액세스하고 그 값에 해당하는 다른 TABLE의 ROW를 찾는 작업을 해당범위까지 실행하는 조인. |
USE_NL(TABLE1, TABLE2) |
|
USE_NL_WITH_INDEX |
INDEX를 사용해서 NESTED LOOP JOIN을 사용하도록 한다. |
USE_NL_WITH_INDEX(TABLE INDEX) |
|
USE_MERGE |
SORT MERGE JOIN 옵티마이저가 SORT MERGE JOIN을 사용하도록 한다. 먼저 각각의 TABLE의 처리 범위를 스캔하여 SORT한 후, 서로 MERGE하면서 JOIN하는 방식. |
USE_MERGE(TABLE1, TABLE2) |
|
USE_HASH |
HASH JOIN 옵티마이저가 HASH JOIN을 사용하도록 한다. |
USE_HASH(TABLE1, TABLE2) |
|
HASH_AJ |
HASH ANTIJOIN |
HASH_AJ(TABLE1, TABLE2) |
|
HASH_SJ |
HASH SEMIJOIN |
HASH_SJ(TABLE1, TABLE2) |
|
NL_AJ |
NESTED LOOP ANTIJOIN |
NL_AJ(TABLE1, TABLE2) |
|
NL_SJ |
NESTED LOOP SEMIJOIN |
NL_SJ(TABLE1, TABLE2) |
|
MERGE_AJ |
SORT MERGE ANTIJOIN |
MERGE_AJ(TABLE1, TABLE2) |
|
MERGE_SJ |
SORT MERGE SEMIJOIN |
MERGE_SJ(TABLE1, TABLE2) |
◆ JOIN시 DRIVING 순서 결정 HINT
|
HINT |
내용 |
사용법 |
|
ORDERED |
FROM절에 명시된 테이블의 순서대로 DRIVING |
|
|
LEADING |
파라미터에 명시된 테이블의 순서대로 JOIN |
LEAING(TABLE_name1, TABLE_name2, ...) |
|
DRIVING |
해당 테이블을 먼저 DRIVING |
DRIVING(TABLE) |
◆ 기타 HINT
|
HINT |
내용 |
사용법 |
|
APPEND |
INSERT시 DIRECT LOADING |
|
|
PARALLEL |
SELECT, INSERT시 여러개의 프로세스로 수행 |
PARALLEL(TABLE, 개수) |
|
CACHE |
데이터를 메모리에 CACHING |
|
|
NOCACHE |
데이터를 메모리에 CACHING하지 않음 |
|
|
PUSH_SUBQ |
SUBQUERY를 먼저 수행 |
|
|
REWRITE |
QUERY REWRITE 수행 |
|
|
NOREWIRTE |
QUERY REWRITE를 수행 못함 |
|
|
USE_CONCAT |
IN절을 CONCATENATION ACCESS OPERATION으로 수행 |
|
|
USE_EXPAND |
IN절을 CONCATENATION ACCESS OPERATION으로 수행못하게 함 |
|
|
MERGE |
VIEW MERGING 수행 |
|
|
NO_MERGE |
VIEW MERGING 수행못하게 함 |
|
※ 추가
ALL_ROWS : 가장 좋은 단위 처리량의 목표로 문 블록을 최적화하기 위해 cost-based 접근 방법을 선택합니다. (즉, 전체적인 최소의 자원 소비, 모든 레코드의 처리하는 시간의 최소화를 목적으로 최적화)
FIRST_ROWS : 가장 좋은 응답 시간의 목표로 문 블록을 최적화하기 위해 cost-based 접근 방법을 선택합니다. (첫번째 레코드의 추출 시간을 최소화할 목적으로 최적화)
CHOOSE : 최적자(optimizer)가 그 문에 의해 접근된 테이블을 위해 통계의 존재에 근거를 두는 SQL문을 위해 rule-based 접근 방법과 cost-based 접근 방법 사이에 선택하게 됩니다.
CLUSTER : 지정된 테이블에 대한 클러스터 스캔.
HASH : 지정된 테이블에 대한 해쉬 스캔.
ROWID : 지정된 테이블에 대한 ROWID에 의한 테이블 스캔.
RULE : explicitlly chooses rule-based optimization for a statement block. rule-base Optimizer를 사용.
◆ 주의
|
SELECT /*+ ORDERED USE_NL(A B C) INDEX(B ITOREDRETL1) */ FROM TORDERDTL B, TORDER A, TITEM C WHERE ... |
1. ORDERED : FROM 절에 기술한 테이블 순(B → A → C 순)으로 조인함. USE_NL에 사용한 순서가 아님.
※ 참고) LEADING, 예) LEADING(B) : FROM 절의 기술 순서와 상관없이 B테이블이 가장 먼저 선행됨.
2. USE_NL : 조인방법을 Nested Loops방식으로 선택.
예) USE_NL(A B), 테이블명이 아닌 테이블에 대한 Alias명
참고) USE_HASH, USE_MERGE
3. INDEX : 특정 인덱스를 오름차순으로 읽음.
예) INDEX(B ITORDERDTL1) : B는 TORDERDTL 테이블의 Alias명, 사용할 인덱스명 기술
참고) USE_DESC(B ITORDERDTL1) : ITORDERDTL1 인덱스를 내림차순으로 읽음
◆ 실행계획 살펴보기 1
|
SELECT * FROM ( ==> 인라인뷰 3 ( ==> 인라인뷰 2 ( ==> 인라인뷰 1 ) ) ) |
과 같이 되어 있다고 하자.
이럴 경우 실행계획을 보면, 우리가 예상했던대로 인라인뷰1 ==> 인라인뷰2 ==> 인라인뷰3 순서로 드라이빙 되지 않는 경우가 있다.
이런 경우 강제로 위 순서대로 실행계획을 수립하도록 하는 힌트
SELECT /*+ ORDERED */
*
FROM ( .....
위 힌트를 사용하면 맨 깊숙한 인라인뷰부터 실행된다.
◆ 힌트 사용하기
1. /*+ USE_CONCAT */
USE_CONCAT : 조건절에 있는 OR 연산자조건 (또는 IN 연산자 조건)을 별도의 실행단위로 분리하여 각각의 최적의 액세스 경로를 수립하여 이를 연결(Concatenation)하는 실행계획을 수립하도록 유도하는 힌트.
반드시 처리주관 조건이 OR로 나누어졌을 때 적용해야 하며, 잘못 사용하면 비효율이 발생할 수 있으므로 주의해야 함.
예>
SELECT /*+ USE_CONCAT */
FROM employees
WHERE job = &job
OR dept_no = &deptno;
풀어쓰자면
SELECT *
FROM employees
WHERE job = &job
UNION ALL
SELECT *
FROM employees
WHERER dept_no = &deptno;
WHERE 절 이후에 나오는 컬럼에 맞게 인덱스를 탄다.
2. /*+ NO_EXPAND */
조건절에 있는 OR 연산자 조건 또는 IN 연산자 조건을 연결 실행계획으로 처리되지 않도록 할 때 사용하는 힌트.
USE_CONCAT의 반대 개념.
예>
SELECT /*+ NO_EXPAND */
FROM customer
WHERE cust_type in ('A','B');
참고 : http://blog.naver.com/hkjhc107?Redirect=Log&logNo=130035827974
◆ 참고
1. Nested Loop
- 테이블의 인덱스끼리 inner-outer 루프를 형성하여 결과를 쿼리하는 방식입니다.
- 제일 많은 유형의 실행계획입니다.
2. Sort Merge
- 쿼리의 결과가 많은 양의 데이터를 읽는 경우, 테이블들을 각각 full-scan하여 같은 키값을 갖는 데이터끼리 조인하여 실행합니다.
- Sort-Merge 방식은 많은 메모리와 디스크 I/O를 필요로 하기 때문에, sqlplus를 실행하는 주체의 메모리/CPU/디스크 스펙에 많은 영향을 받습니다.
3. Hash Join
- 한 테이블은 매우 많은 Row를 갖고, 다른 한 테이블은 매우 적은 Row를 가질 때, 해쉬 알고리즘에 의해 큰 테이블을 여러개의 버켓으로 나누어 쿼리를 수행하는 방식입니다. 작은 테이블은 인덱스를 태우는 것보다 full-scan을 하는 것이 유리할 때 사용됩니다.
[출처] [본문스크랩] 오라클 힌트 정리표|작성자 귀차니즘극복
얼마전에 있었던 경험을 바탕으로 내용을 적어 본다.
갑자기 DBA의 실수로 운영DB의 테이블이 DROP 되었다.
신속한 시스템 복구를 위해 운 좋게 직전에 백업해두었던 테이블이 있어서 임시로 복구를 했다.
그냥 저냥 업무시간대를 잘 넘기고 있어서 별 문제 없이 지나가나 했었다.
갑자기 퇴근시간이 다되어 DB의 CPU 사용률이 높아지더니 떨어질 기미가 보이지 않는다.
여러 쿼리들의 수행시간이 엄청 길어졌고 계속 부하가 걸려온다.
느려진 쿼리의 PLAN을 떠봤다. 갑자기 타지 않던 FULL SCAN 플랜이 자꾸 뜬다.
분명히 DROP된 테이블과 관련이 있는듯 했으나, 새로 생성된 INDEX를 봐도 기존과 동일하게 문제가 없었다.
암만 봐도 인덱스의 문제가 없다.. 이거 왜이러지 하는 순간... 번뜩 생각이 났다.
보통 DB의 옵티마이져는 COST 베이스 방식으로 운영이 되고, 옵티마이저에 따라 DB가 플랜을 만들어서 돌린다.
이때 통계정보를 활용한다.... 바로 이거인듯 했다.
그래서 ANALYZE 된 정보를 확인해 보았다.
SELECT TABLE_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
SAMPLE_SIZE,
LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME = 'SAMPLE_TABLE'
;
역시나 TABLE이 DROP되면서 통계정보도 날아갔던 것이다.
즉, 아무리 인덱스가 생성되어 있어도 데이터의 형태에 따라 만들어진 통계정보가 없으면 FULL_SCAN이 발생한다.
DROP된 테이블이 시스템 사용시 무조건 사용되는 테이블이라 그 영향력은 크다.
그럼 통계정보를 생성해 본다.
ANALYZE TABLE SAMPLE_TABLE
COMPUTE STATISTICS
;
[문법 설명]
ANALYZE object_name operation STATISTICS
[VALIDATE STRUCTURE[CASCADE]]
[LIST CHAINED ROWS[INTO tables]]
* object는 TABLE, INDEX, CLUSTER 중에 해당하는 오브젝트 기술
* operation
- COMPUTE : 각각의 값등을 정확하게 계산한다. 가장 정확한 통계를 얻을 수 있지만 처리속도가 가장 느리다.
- ESTIMATE : 자료사전의 값과 데이터 견본을 가지고 통계를 예상한다. 덜정확하지만 처리속도가 훨씬 빠르다.
- DELETE : 테이블의 모든 통계정보를 삭제한다.
수행하고 나서 상단의 조회 쿼리를 다시 조회하면 결과가 나온다.
자 이제 느린 쿼리를 다시 수행해 본다... 음 역시 예전의 상태를 되찾았다!
[출처] [오라클] TABLE ANALYZE 하기|작성자 귀차니즘극복
숫자만 -> trim(translate(cmp_user_id,'1234567890',' ')) is null
문자만 -> trim(translate(cmp_user_id,'1234567890',' ')) is not null
ss.username, se.time_waited,
so.physical_reads
from v$session_event se, v$session ss, v$sess_io so
where 1=1
and event like '%db file %'
and se.sid = ss.sid
and ss.sid = so.sid
order by time_waited desc;
v$session_wait 를 조회하세요.
|
V$EVENT_NAME
|
인스턴스에서 정의된 대기이벤트의 정보를 가지는 참조성 View
|
이벤트 명, Parameter1, Parameter2, Parameter3
|
|
V$SYSTEM_EVENT
|
인스턴스의 기동 후 , 전 세션에서 발생한 대기 이벤트의 누계 통계치(인스턴스 단위)
|
인스턴스의 전반적인 안정도를 판단할 수 있으며, 특정 시간대의 Delta 정보를 산출하여 구간 분석 가능
|
|
V$SESSION_EVENT
|
현재 접속되어 있는, 각 세션 별 대기이벤트의 누계 통계치
|
접속한 세션에 대해, 각 이벤트 별 통계정보를 파악
|
|
V$SESSION_WAIT
|
각 세션이 현재 대기하고 있는 대기이벤트에 대한 상세 정보(P1,P2,P3,대기시간 등)를 제공
|
누적데이터가 아니고, 실시간 데이터이므로, 반복적으로, Query를 수행하여 대기이벤트의 상황을 파악해야 함
|
|
V$SYSTEM_WAIT_CLASS
|
10g NF.인스턴스 기동 후 발생된 대기이벤트를 관련된 Class별로 Grouping 한 결과 제공
|
대기이벤트의 클래스 단위로, 인스턴스의 대기 상황 파악
|
|
V$SESSION_WAIT_CLASS
|
10g NF. 현재 접속되어 있는 각 세션 별 대기이벤트를 Class별로 Grouping한 결과 제공
|
대기이벤트의 클래스 단위로, 세션의 대기 상황 파악
|
|
V$SESSION_WAIT_HISTORY
|
10g NF. 최근 10개의 V$SESSION_WAIT 데이터 제공
|
Session Wait에 대한 아주 짧은 이력 정보 제공
|
|
V$EVENT_HISTOGRAM
|
10g NF. 인스턴스 기동후의 대기이벤트에 대한 히스토그램 제공
|
각 버킷(대기시간의 구간)별 대기이벤트의 발생 이력 파악에 도움
|
|
V$ACTIVE_SESSION_HISTORY
|
10g NF. Active Session의 이력정보 제공
|
1초 단위로 각 Active 세션 기본 정보,SQL수행정보, PQ 관련정보, 대기Event정보, 대기 Event관련 OBJ#, FiLE#, BLOCK# 등의 정보 제공 |
SELECT * FROM USER_SOURCE
WHERE TEXT LIKE '%테이블명%'
SELECT * FROM 테이블명 AS OF TIMESTAMP(SYSTIMESTAMP -INTERVAL '20' MINUTE)
WHERE SEQ ='1'
20분전 데이터 조회
[출처] 오라클 테이블 복사 쿼리 |작성자 수유산장
테스트용 테이블 만들때 유용하게 쓸수 있죠..
CREATE TABLE TEST_MEMBER AS SELECT * FROM MEMBER
검색을 걸어서 필요한 데이타만 복사할수도 있죠.
CREATE TABLE TEST_MEMBER AS SELECT * FROM MEMBER WHERE IDX<10000
테이블 구조만 복사하고 싶다면..
CREATE TABLE TEST_MEMBER AS SELECT * FROM MEMBER WHERE 1=2
검색조건을 이렇게 한다면 테이타는 복사가 안되겠죠..
테이블이 이미 만들어진 경우는
INSERT INTO TEST_MEMBER SELECT * FROM MEMBER
주의할점은 테이블구조만 복사된다는 것이다.
프라이머리키나 인덱스등은 복사가 안돼니 따로 설정해 주어야 한다.
안녕하세요 영원한 초보 crow입니다. 두번째 강좌입니다..^^
김호님 감사합니다 허접하기 짝이 없는 강좌가 올라갈 줄이야 꿈에도 몰랐습니다.
앞으로 더욱 알차게 실무에 바로 쓸 수 있게 올리겠습니다.
그럼 첫번째 강좌에 이어 오늘은 테이블스페이스를 만들어 보고, 크기 조정하고, 지우고...
그리고 테이블 스페이스가 지웠졌을 때 복구하는 이런 것들을 해보죠.
아무래도 데이타가 쌓이는 곳이 이곳이다 보니 쪼까 중요하자나요..
일단 오라클을 실행하구요..
[oracle@crow oracle]$ svrmgrl SVRMGR> connect internal SVRMGR> startup |
전 시간에 배운걸 이용해서 테이블 스페이스가 어디있는지 확인하겠습니다.
SVRMGR> select tablespace_name, bytes, file_name from dba_data_files; TABLESPACE_NAME BYTES FILE_NAME ------------------------------------------------------------------------------- SYSTEM 183500800 /home/oracle/oradata/ORCL/system01.dbf OEM_REPOSITORY 5242880 /home/oracle/oradata/ORCL/oemrep01.dbf RBS 26540032 /home/oracle/oradata/ORCL/rbs01.dbf TEMP 10485760 /home/oracle/oradata/ORCL/temp01.dbf USERS 2087409664 /home/oracle/oradata/ORCL/users01.dbf INDX 10485760 /home/oracle/oradata/ORCL/indx01.dbf DRSYS 83886080 /home/oracle/oradata/ORCL/drsys01.dbf |
보시는대로 /home/oracle/oradata/ORCL 에 다 있군요.. 그러면 test라는 테이블 스페이스를 하나 만들어 보겠습니다.
SVRMGR> create tablespace test
2> datafile '/home/oracle/oradata/ORCL/test_01.dbf'
3> size 10K;
Statement processed.
SVRMGR> select tablespace_name, bytes, file_name from dba_data_files;
TABLESPACE_NAME BYTES FILE_NAME
------------------------------ ---------- ------------------------------------------
SYSTEM 183500800 /home/oracle/oradata/ORCL/system01.dbf
OEM_REPOSITORY 5242880 /home/oracle/oradata/ORCL/oemrep01.dbf
RBS 26540032 /home/oracle/oradata/ORCL/rbs01.dbf
TEMP 10485760 /home/oracle/oradata/ORCL/temp01.dbf
USERS 2087409664 /home/oracle/oradata/ORCL/users01.dbf
INDX 10485760 /home/oracle/oradata/ORCL/indx01.dbf
DRSYS 83886080 /home/oracle/oradata/ORCL/drsys01.dbf
TEST 10240 /home/oracle/oradata/ORCL/test_01.dbf <==여기보이시죠
8 rows selected. |
보시는대로 test라는 테이블 스페이스를 만들고요, /home/oracle/oradata/ORCL/ 에 test_01.dbf라는 이름의 파일을 만들었습니다.. 아.. 그리고 크기는 10kbyte 로 만들어놨습니다.. 여기서 꼭 /home/oracle/oradata/ORCL/ 에 만들 필요는 없습니다. 전 그저 쉽게 하기 위해 이렇게 하는겁니다..^^ 그럼 테이블 스페이스의 크기를 늘려볼까요?
SVRMGR> alter tablespace test add datafile '/home//oracle/oradata/ORCL/test_02.dbf' size 30K; Statement processed. |
흠 그럼 데이타 파일 크기를 늘리죠.
SVRMGR> !ls -al /home/oracle/oradata/ORCL/test_02.dbf -rw-r----- 1 oracle dba 53248 Apr 13 06:05 /home//oracle/oradata/ORCL/test_02.dbf |
크기가 늘어났군요.. 그럼 지워볼까요..
SVRMGR> drop tablespace test; Statement processed. |
지금은 테이블 스페이스 안에 데이타가 없어서 그냥 이렇게 지워졌지만.. 데이타가 들어있다면
drop tablespace test including contents; |
이렇게 하세요.. 그럼 어디 데이타 파일도 지워졌나 보죠.
SVRMGR> !ls -al /home//oracle/oradata/ORCL/ total 2363032 drwxr-x--x 2 oracle dba 4096 Apr 13 06:02 . drwxr-x--x 3 oracle dba 4096 Nov 27 05:54 .. -rw-r----- 1 oracle dba 4315136 Apr 13 06:11 control01.ctl -rw-r----- 1 oracle dba 4315136 Apr 13 06:11 control02.ctl -rw-r----- 1 oracle dba 83888128 Apr 13 05:48 drsys01.dbf -rw-r----- 1 oracle dba 10487808 Apr 13 05:48 indx01.dbf -rw-r----- 1 oracle dba 5244928 Apr 13 05:48 oemrep01.dbf -rw-r----- 1 oracle dba 26542080 Apr 13 05:48 rbs01.dbf -rw-r----- 1 oracle dba 512512 Apr 13 05:48 redo01.log -rw-r----- 1 oracle dba 512512 Apr 13 06:08 redo02.log -rw-r----- 1 oracle dba 183502848 Apr 13 05:48 system01.dbf -rw-r----- 1 oracle dba 10487808 Apr 13 05:48 temp01.dbf -rw-r----- 1 oracle dba 12288 Apr 13 06:08 test_01.dbf <== -rw-r----- 1 oracle dba 53248 Apr 13 06:08 test_02.dbf <==데이타파일은 이렇게 2개 살아있죠 -rw-r----- 1 oracle dba 2087411712 Apr 13 05:48 users01.dbf |
이 데이타 파일을 지워보겠습니다..
SVRMGR> !rm -f /home//oracle/oradata/ORCL/test_* |
자 이렇게 해서 지워졌습니다..
SVRMGR> shutdown immediate; SVRMGR> exit |
데이타 베이스를 종료하고 나왔습니다.. 지금까지 테이블스페이스를 만들어보고, 조정하고, 지워봤습니다. 자, 이제 temp 라는 테이블 스페이스를 유실시킨 후 복구 실습을 한번 해보겠습니다. 역시 오라클을 시동하시구요,
[oracle@crow oracle]$ svrmgrl SVRMGR> connect internal SVRMGR> startup |
그럼 먼저 temp 테이블 스페이스의 크기와 경로를 알아보구요
SVRMGR> select tablespace_name, bytes, file_name from dba_data_files; TABLESPACE_NAME BYTES FILE_NAME ------------------------------ ----------ㅡ------------------------------------------------ SYSTEM 183500800 /home/oracle/oradata/ORCL/system01.dbf OEM_REPOSITORY 5242880 /home/oracle/oradata/ORCL/oemrep01.dbf RBS 26540032 /home/oracle/oradata/ORCL/rbs01.dbf -------------------------------------------------------------------------------- TEMP 10485760 /home/oracle/oradata/ORCL/temp01.dbf<==여기 보이시죠 -------------------------------------------------------------------------------- USERS 2087409664 /home/oracle/oradata/ORCL/users01.dbf INDX 10485760 /home/oracle/oradata/ORCL/indx01.dbf DRSYS 83886080 /home/oracle/oradata/ORCL/drsys01.dbf 7 rows selected. |
경로는 아까 본 거기이고 크기는 1M 네요.
SVRMGR> shutdown abort <== failure가 되었다고 가정하는 것입니다. ORACLE instance shut down. |
[oracle@crow ORCL]$ svrmgrl SVRMGR> connect internal Connected. |
위처럼 파일을 지우고요. 아래처럼
SVRMGR> alter database open; Statement processed. |
이렇게 데이타베이스를 open 합니다 그리고 나서
SVRMGR> drop tablespace temp including contents; Statement processed. |
이렇게 temp 스페이스를 지웁니다.. 그런다음 아래와 같이
SVRMGR> create tablespace temp datafile '/home/oracle/oradata/ORCL/temp01.dbf' size 1M; Statement processed. |
아까 경로 와 크기를 알아둔 거 있자나요. 그걸 그대로 이용해서 하나 다시 만들어줍니다..그럼 끝.. 이제 오라클을 종료하고요..
SVRMGR> shutdown immediate |
스타트업을 합니다..
SVRMGR> startup ORACLE instance started. Total System Global Area 71998864 bytes Fixed Size 64912 bytes Variable Size 54984704 bytes Database Buffers 16777216 bytes Redo Buffers 172032 bytes Database mounted. Database opened. |
잘 실행되네요.. 다시 종료시키죠..
SVRMGR> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SVRMGR> exit Server Manager complete. |
파일이 복구되어있는지 ls로 확인해 보면..
[oracle@crow ORCL]$ ls control01.ctl drsys01.dbf oemrep01.dbf redo01.log system01.dbf temp01.org control02.ctl indx01.dbf rbs01.dbf redo02.log temp01.dbf users01.dbf [oracle@crow ORCL]$ rm -f temp01.org <== 테스트가 끝났으므로 이젠 필요없는 파일을 지웁니다.. |
이렇게 테스트가 끝났습니다. 테이블스페이스 유실이었을때 이렇게 하면 됩니다.. 그런데 여기서 중요한 건 경로와 크기를 잘 알아야 한다는 것이겠죠.. 이걸 어떻게 기억하낭^^ 오늘은 이만 합니다..^^ 좀 난해하면 crow@dreamsta.com으로 연락주세요... 변변치 않는 실력에 답변까지 하기가 좀 뭐하지만 최선을 다해 답변해드리겠습니다.. 감사합니다.. 다음 강좌에서는 아카이브 모드와 노아카이브모드에대해서 알아보져.. 그럼 안녕히계세요..
출처 : http://cafe.naver.com/javalove.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=294&
안녕하세요? 백업/복구 과정 강좌를 맡게 된 영원한 초보 crow입니다.. 정식 명칭은 10의 redcrow 이구요 저도 초보라 강좌가 강좌답게 갈런지 의심스럽네요..^^ .. 현재 (주)DWS에서 허접한 개발자로 있구요 ^^ 처음 게임프로그래머로 시작을 했고 IT쪽은 이제 1년이 쪼금 안됐네요.. 나이요 절라 많아요 73년12월10일생이에요. 음력으로여^^ 아무쪼록 귀엽게 봐주시구요.. 백업은 저도 몇 달 전에 학원에서 듣고 써 먹지를 못했습니다.. 학원에서 배운 그대로 여러분에게 전달은 안되겠지만 한번 최선을 다해 보도록 하겠습니다.. 저도 테스트를 해봐야 하기에 컴터는 펜Ⅲ650 / 128M (리눅스 6.2/오라클 8i) 에 설치를 해놓았습니다.. 그리고 일단, 여러분.. 디비는 만들어보셨는지 ^^ 오라클을 설치하면 기본적으로 디비가 하나 만들어지지만, 그거말고 여러분이 디비를 하나 만들줄 알아야 합니다.. 전 여러분이 디비를 만들줄 안다는 가정하에서 들어가겠습니다.. 왜냐하면요.. 오라클 데이타베이스에 전반적인 메모리 구조와 프로세서 구조를 설명을 해야 하는데.. 저도 사실 가물가물하거든요 오라클을 안만진지가 어언~~~~~~~몇 개월돼서..^^ 그럼 일단 오라클 데이타 베이스에 데이타파일, 컨트롤파일, 리두로그파일 들이 어디에 있는지 알아보죠 이 파일들이 꼭 있어야 백업이 가능하고 복구가 가능합니다. 오늘은 이 파일들을 가지고 놀아보겠습니다. 그리고 이 파일들을 가지고 백업을 하는건 다음시간에 하기로 하구요.. 데이타 베이스에 데이터파일은 v$datafile, v$datafile_header, dba_data_files 이 값들을 추출해서 볼 수 있습니다.. 데이터파일이란 테이블스페이스를 구성하는 파일로 실제로 데이타가 저장되는 곳입니다. 한번 실습해보죠..
일단 오라클이 시동이 안되어있다면 이렇게 시동하세요.
[oracle@crow oracle]$ svrmgrl Oracle Server Manager Release 3.1.5.0.0 - Production (c) Copyright 1997, Oracle Corporation. All Rights Reserved. Oracle8i Release 8.1.5.0.2 - Production With the Java option PL/SQL Release 8.1.5.0.0 - Production SVRMGR> connect internal Connected. SVRMGR> startup ORACLE instance started. Total System Global Area 71998864 bytes Fixed Size 64912 bytes Variable Size 54984704 bytes Database Buffers 16777216 bytes Redo Buffers 172032 bytes Database mounted. Database opened. |
참고로 오라클 종료 명령어는 shutdown입니다. 이제 값을 추출해 보죠.
SVRMGR> select name from v$datafile; NAME -------------------------------------------------------------------------------- /home/oracle/oradata/ORCL/system01.dbf /home/oracle/oradata/ORCL/oemrep01.dbf /home/oracle/oradata/ORCL/rbs01.dbf /home/oracle/oradata/ORCL/temp01.dbf /home/oracle/oradata/ORCL/users01.dbf /home/oracle/oradata/ORCL/indx01.dbf /home/oracle/oradata/ORCL/drsys01.dbf |
데이타 파일 위치와 이름들이 보이시죠.. 디비를 한번 만들어보신 분들은 그 사용 목적이 뭔지 아실껍니다.. 모르시는 분들도 그냥 이런게 있구나만 알고 계십시요. 이 강좌는 오라클 백업과 복구이기 때문에 자세한 설명은 다음에 제가 오라클 디비를 만들어 볼 때 해드리거나 아니면 다른 참고 자료를 읽어보세요.. 제가 잘가는 database.sarang.net에 가보시면 오라클뿐만아니라 여러가지 많은 디비들의 자료가 있습니다. 그럼 계속 나가보겠습니다.. 데이터파일에 관한 정보는 v$datafile과 v$datafile_header, dba_files 에 있습니다. 또 있던거 같은데 생각이 나질 않네요...^^; 우리는 지금 데이타파일의 위치와 이름만 알면 되니까 이거면 충분합니다. 그리고 컨드롤파일의 위치와 파일 이름은 v$controlfile, 리두로그는 v$logfile, v$log를 통해서 볼 수 있습니다..
SVRMGR> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /home/oracle/oradata/ORCL/control01.ctl /home/oracle/oradata/ORCL/control02.ctl |
컨트롤 파일 위치 정보 입니다.. 컨트롤 파일은 데이타 화일들의 위치나 Online/Offline 상태, 온라인 로그화일들의 위치와 가장 최근의 system change number 값과 가장최근에 발행한 CheckPoint 시간과 각종 오라클 관련 정보를 저장하는곳입니다.
SVRMGR> select * from v$logfile; GROUP# STATUS MEMBER ======================================================= 1 STALE /home/oracle/oradata/ORCL/redo01.log <--현재 이것만 사용하고 있습니다.. 2 /home/oracle/oradata/ORCL/redo02.log <--이건 아직 사용대기중입니다.. |
로그파일 위치와 파일이름이 출력되었습니다. 리두로그파일은 변화된 모든 데이타를 저장하며 데이타베이스 복구 작업의 경우에만 사용됩니다. 참고로 데이타베이스 대한 정보는 v$database 로 보실 수 있습니다. 그리고 각 v$view 에 대한 필드명을 알고 싶으면, 알고 있겠지만 desc v$database, v$datafile(테이블명); 아시겠져.. 오늘 강좌를 이걸로 마치겠습니다 다음 강좌에서는 테이블스페이스를 하나 만들고 테이블을 만들어서 데이타를 넣어서 그걸 가지고 백업해보겠습니다.. 그럼 이만..^^
출처 : http://blog.naver.com/cjj721004?Redirect=Log&logNo=120116296911
1. SYS_CONNECT_BY_PATH(column,char)은 데이터의 셀프조인에 의한 트리구조 데이테에 대해서 한row에서 표현할때 사용하는 것입니다.
간단히 말씀드리면, FIle시스템에서 파일이 위치하는 경로에 대해서 표현하는것과 같다고 생각하시면 됩니다.
2. "START WITH rnum = 1" 은 경로중에서 root에 해당하는 부분을 어디서 부터 할것인지 설정한는 조건을 말합니다.
3. "CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR a = a"은 계층을 만들기 위한 조인부분으로 경로의 다음 연결 경로를 지정하는 역할을 합니다.
즉, A -> B -> C 로 데이터의 연결을 만들기 위함입니다.
4. 이함수는 Oracle에서 사용하는 것으로, 특별한 환경 설정은 없으며, 단지 Data에 대해서 계층구조를 가지고 있다면 언제든지 사용을 할수 있습니다.
가장 대표적인 예제로는 회의의 조직도 Data가 되겠네요.. ^^
출처 : Tong - redyoon님의 DB통
간단한 샘플
SELECT A
, SUBSTR(MAX(SYS_CONNECT_BY_PATH(B, ',')), 2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
GROUP BY A
다음의 실행하면 결과값은 다음과 같이 나올 것이다.
A | B
1 | 엄마,아빠,이모
2 | 삼촌,오빠
---------------
어떻게 만들어지는지 분석모드
SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL
A B
1 엄마
1 아빠
1 이모
2 삼촌
2 오빠
일단 기본 데이터를 정의한다.
SELECT A,B,
ROW_NUMBER() OVER( ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
A B RNUM
1 엄마 1
1 아빠 2
1 이모 3
2 삼촌 4
2 오빠 5
기본데이터를 ROW_NUMBER() OVER( ORDER BY A) 를 사용해서 A 컬럼을 기준으로 정렬을해서 RNUM 을 정의해준다.
SELECT A
,SUBSTR(SYS_CONNECT_BY_PATH(B, ','),2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
A B
1 엄마
1 엄마,아빠
1 엄마,아빠,이모
2 삼촌
2 삼촌,오빠
위에 나온데이터를 기준으로 SYS_CONNECT_BY_PATH 를 써줬다.
START WITH RNUM = 1 시작 기준점을 RNUM 1을 시작점으로 잡고
CONNECT BY PRIOR RNUM = RNUM - 1 RNUM과 하나작은 RNUM과 결합하면서,
AND PRIOR A = A A값을 기준으로 같은거 끼리 묶어줬다.
CONNECT BY PRIOR RNUM = RNUM - 1 를 이해할때는 하나의 BOM을 생각하면 될거다.
시작은 RNUM = 1 로 시작하고
RNUM 이 2이면 RNUM 1과 2를 합치면서 A와 A가 같은걸 결합
RNUM 이 3이면 RNUM 3과 2를 합치고 A와 A가 같은걸 결합
SELECT A
, SUBSTR(MAX(SYS_CONNECT_BY_PATH(B, ',')), 2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
GROUP BY A
A B
1 엄마,아빠,이모
2 삼촌,오빠
그룹함수를 써서 A로 그룹지어서 가장큰 값을 뽑아오면 끝
[출처] SYS_CONNECT_BY_PATH , CONNECT BY|작성자 아기대장
/* Formatted on 2010/10/07 14:11 (Formatter Plus v4.8.8) */
WITH tmp AS
(SELECT '1' AS col1, 'A' AS col2
FROM DUAL
UNION ALL
SELECT '2' AS col1, '가' AS col2
FROM DUAL
UNION ALL
SELECT '1' AS col1, 'B' AS col2
FROM DUAL
UNION ALL
SELECT '1' AS col1, 'C' AS col2
FROM DUAL)
SELECT col1, LTRIM (SYS_CONNECT_BY_PATH (col2, ','), ',') AS col1
FROM (SELECT col1, col2,
ROW_NUMBER () OVER (PARTITION BY col1 ORDER BY col1) rn,
COUNT (*) OVER (PARTITION BY col1) cnt
FROM tmp)
WHERE LEVEL = cnt
START WITH rn = 1
CONNECT BY PRIOR col1 = col1 AND PRIOR rn = rn - 1;
2) 세로를 가로의 컬럼 데이터로 만들기
/* Formatted on 2010/10/07 16:07 (Formatter Plus v4.8.8) */
WITH tmp AS
(SELECT '1' AS col1, 'a' AS col2
FROM DUAL
UNION ALL
SELECT '2' AS col1, '가' AS col2
FROM DUAL
UNION ALL
SELECT '1' AS col1, 'b' AS col2
FROM DUAL
UNION ALL
SELECT '1' AS col1, 'c' AS col2
FROM DUAL)
SELECT col1, MIN (DECODE (r, 1, col2)), MIN (DECODE (r, 2, col2)),
MIN (DECODE (r, 2, col2))
FROM (SELECT col1, col2,
ROW_NUMBER () OVER (PARTITION BY col1 ORDER BY col2) r
FROM tmp)
GROUP BY col1
-- 1. 어차피 a 는 고정 자리수일텐데 Instr 은 뭐하러 하나요?-- 2. Why Reverse?-- 3. 정규식 패턴 사용이 적절하지 않고요...WITHtAS(SELECT'HT1816112121400121;로스엔젤레스;부산광역시 강서구 대저1동 4302-22'bigoFROMdualUNIONALLSELECT'HT7014021412091123;대한민국;부산광역시 강서구 대저1동 4302-22'FROMdualUNIONALLSELECT'HT1611221911240214;오스트레일리아;부산광역시 강서구 대저1동 4302-22'FROMdual)SELECTbigo-- 1. a 가 자리수 고정인 경우, SUBSTR(bigo, 1, 18) a1, SUBSTR(bigo, 20, INSTR(bigo,';', 1, 2)-20) b1, SUBSTR(bigo, INSTR(bigo,';', 1, 2)+1) c1-- 2. 자리수와 상관 없이, SUBSTR(bigo, 1, INSTR(bigo,';')-1) a2, SUBSTR(bigo, INSTR(bigo,';')+1, INSTR(bigo,';', -1)-INSTR(bigo,';')-1) b2, SUBSTR(bigo, INSTR(bigo,';', -1)+1) c2-- 3. 정규식 이용, REGEXP_SUBSTR(bigo,'[^;]+', 1, 1) a3, REGEXP_SUBSTR(bigo,'[^;]+', 1, 2) b3, REGEXP_SUBSTR(bigo,'[^;]+', 1, 3) c3FROMt;