|
[Oracle Server 개념]논리적인 DataBase 구조
-----------------------------------
논리적 DataBase구조
-----------------------------------
A. TableSpace
- 각 DataBase는 논리적으로 하나이상의 TableSpace로 구성된다.
- 각 TableSpace에서는 하나이상의 DataFile이 명시적으로 생성되어, TableSpace에 있는 모든 논리적 구조의 Data를 물리적으로 저장한다.
- TableSpace의 DataFile을 합친 크기는 TableSapce의 저장영역이다.
- 생성예제
create tablespace users datafile '/usr2/oradata/users01.율' size 100m
default storage (initial 100k next 100k);
[TableSpace의 이름은 USERS이며 DataFile은 users01.dbf를 사용하고,TableSpace의 초기크기는 100M,TableSpace에 Table이나 인덱스를 만들 때 Storage구를 기술안하는 경우에는 초기100k,확장100k안 table이나 Index가 생성된다.즉 default storage는 TableSpace에 생성되는 Object에대해 Storage구의 시술이 없는 경우에 적용되는 사항이다.]
- TableSpace의 저장영역을 늘이는예
Alter tablespace users add datafile '/usr2/oradata/users02.율' size 100m;
위에서 만든 users TableSpace의 size를 100m더 늘이는 경우이다.
B. Schema/Schema Object
- 스키마는 개체모음이고 스키마개체는 DataBase의 데이터를 직접 참조하는 논리적구조, 스키마개체 로는 Table,View,Sequence,Stored Procedure,Synonym,Index, Cluster,DataBase Link등이 있다.
(동일한 스키마개체가 서로 다른 TableSpace에 있을수 있고, 한 TableSpace가 여러스키마 게체를 보유할 수 있다.)
C. 데이터블럭/확장영역/Segment
- Oracle Data는 가장작은 단위인 Data Block에 저장된다.
- 확장영역은 연속적인 몇 개의 데이터블록으로 한번에 할당되며 특정정보의 정보 저장에 사용된다.
- Segmet
Data Segmet : Table의 모든 Data는 Data Segment확장 영역에 저장된다.
Index Segment : 각 Index는 모든 데이터를 저장하는 인덱스 Segment를 가진다.
RollBack Segment : '실행취소(RollBack)'정보를 저장
Temp Segment : SQL명령문의 실행을위해 임시작업영역이 필요할 때 사용
[Oralce Server 개념]물리적인 DataBase 구조
--------------------
물리적 DataBase구조
--------------------
A. DataFile
- 모든 Oracle DataBAse는 하나이상의 DataFile을 가지며, DB의 영역이 부족할 때 자동으로 확장할 수 있는 기능이 있다.
- 하나이상의 DataFile이 TableSpace를 형성한다.
- 수정된 Data나 새로운 Data는 파일에 즉시 Write할 필요가 없다.즉 디스크 Access량을 줄이고 성능을 향상시키려면 Data를 메모리에 저장했다가 DBWR BackGround Process가 한번에 디스크에 저장한다.
B. Redo Log File
- Oracle DB는 2개 이상의 Redo Log File을 가진다.
- Redo Log의 주기능은 변경사항을 저장,이미 수정된 Data가 장애 때문에 DataFile에 기록되지 못했다면 수정된 부분이 Redo Log에 있으므로 수행한 작업을 손실하지는 않는다.
C. Control File
- Control File에는 DB이름, DataFile과 Redo Log File의 위치,DB생성시간등이 기록되어 있다.
- Oracle은 Instance가 시작될때마다 DataBase와 Redo Log File을 지정한다. 새 DataFile이나 Redo Log File이 생성되는 경우에는 Oracle은 Control File을 자동으로 수정한다.
[DataBase]DataBase의 시작과 종료
---------------
DataBase 시작
---------------
Server Manager를 기동한후 작업실시 svrmgrl(unix), svrmgr30(NT용 Oracle 8.x)
1. DataBase를 마운트 하지않고 인스턴스 시작
startup nomount;
- DataBase 생성중에만 이러한 경우가 발생
2. DataBase를 마운트한후 인스턴트 시작
startup mount;
- 데이터 파일의 이름변경,리두로그 파일추가,삭제,변경
Redo Log Archive Option 활성화또는 비활성화
전체 DataBase 복구작업 등의 경우에 사용
3. 인스턴스 시작후 DB를 Mount하여 Open
startup open;
- 사용자들이 일반적인 데이타 Access 작업을 할 수 있다.
4. DataBase 시작단계에서 Access제한
startup restrict;
- 인덱스 재구축이나, DB Export/Import 수행
SQL*Loader등의 작업 수행
create session권한이 있는 사용자는 DB에 접속 가능하며, Create session/restriced sesison권한이 있는 사용자는 DB에 Access 할 수 있슴. 즉 DBA만이 restricted session권한이 있어야 함
5. Instance 강제시작
startup force;
- Instance 시작시 문제가 발생한 경우나, shutdown normal이나 shutdown immediate로 현재의 Instance를 종료할수 없는 경우에 사용
6. 인스턴스를 시작하고, DB를 Mount한다음 자동으로 복구
startup recover;
---------------
DataBase 종료
---------------
1. 정상종료
shutdown normal;
- 사용자들이 모든 Session을 끊을때까지 기다린후 shutdown
다시 시작할때 인스턴스 복구가 필요없슴.
2. 즉시종료
shutdown immediate;
- 현재 Client의 모든 SQL명령이 즉시종료
Commit안된 Session은 RollBack됨
현재 session을 RollBack한후 즉시 Connection을 끊어버림
3. 인스턴스중지
shutdown abort;
- DB를 즉시 종료할 경우에 사용
인스턴스를 시작할때 문제가 발생한 경우 Client의 SQL명령이 즉시 종료되며 Commit안된 Transaction은 RollBack안되며, 즉시 종료됨
[Oracle Server 개념]논리적 저장영역(Data Block,Extents,Seg
논리적 저장영역
1. Data Block
- Oracle에서 Data를 저장하는 가장 작은 단위, 블록단위로 DataBase의 DataFile에 있는 저장영역을 관리한다.
- Data Block의 크기는 최대한계내에는 운영체제 블록크기의 배수여야 한다.
- 구성
Head : 블록주소와 Segment유형(Data,Index,RollBack…)등의 일반적인 블록정보를 포함
Table Directory : 해당블록에 행을 가지는 Table에대한 정보를 가진다.
Row Directory : 블록내의 실제 행에 대한 정보를 가진다.
Row Data : Table Data Or Index Data를 포함한다.
빈영역 트랜잭션 입력항목에 사용되는 영역 : 블록내의 하나이상의 행을 Access하는 Insert/Update/Delete/Select등의 명령문은 블록에 하나의 트랜잭션 입력항목을 필요로 한다. 대부분의 운영체제에서는 약23 Byte정도를 필요로함
2. Extents
- 저장영역할당을 위한 논리적인 단위로 연속적인 몇 개의 Data Block으로 구성된다.
- 각 Segment는 적어도 하나이상의 Extents(RollBack Segment는 2개이상)를 자지고 생성됩니다.
이러한 확장을 initial extents라고 한다.
- 일반적으로 한 Segment의 확장영역은 drop table또는 drop cluster명령으로 개체를 삭제하여 TableSpace에 반환한다.
(예외)truncate … drop storage를 이용하여 Table이나 Cluster를 Truncate시킬수 있슴. Oracle은 주기적으로 RollBack Segment에 있는 하나이상의 Extents를 해제할수 있다. DBA는 alter table table_name deallocate unused명령을 이용하여 확장영역을 해제할 수 있다.
3. Segment
- Extents(확장영역)의 집합
A. Data Segment
- DataBase의 SnapShot과SnapShot Log를 포함한 모든 Cluster화 되지 않은 Table은 자신의 Data를 저장하기위해 단일데이터세그먼트(Single Data Segment)를 가진다. Create Table/SnapShot/SnapShot Log명령을 사용하여 Object를 생성할때 Data Segment를 생성하며 Create Cluster명령이 실행될때 Cluster 에 대한 Data Segment를 생성한다.
B. Index Segment
- Index생성시 Segment할당된다.(Create Index…)
C. RollBack Segment
- Transaction이 RollBack되어야할 경우 트랜잭션 작동을 기록
- 각 DataBase에는 하나이상의 RollBack Segment가 있슴
- Transaction별로 변경되기전의 Data를 기록
- Transaction을 Commit할때 RollBack Segment의 정보를 해체하지만 바로 없애지는 않는다.
Transaction을 Commit 하기전에 시작된 Query에 관련된 Data의 read-consistent view(읽기 일관성 뷰)를 생성하지위해 data가 RollBack Segment에 남아 있다.
그러한 View를 생성할 가능성이 있는한 RollBack Data를 사용할 수 있도록 Oracle은 RollBack Segment의 확장영역을 순차적으로 사용한다.
RollBack Segment의 마지막 영역이 꽉차면 첫번째 Extents로 돌아가서 RollBack Data를 기록함.
또한 장기적으로 실행되는 Transaction은 RollBack Segment에 새로운 Extents(확장영역)를 요구할수도 있슴.
D. Temp Segment
- Create Index, Select Order By, Select Distinct, Select Group By, Select Union,
Select,Minus,Select ,Intersect등의 명령은 Temp Sefment를 필요로 한다.
예를들어 Query에 Distint,Group By,Order by등이 있으면 Oralce은 두개의 Temp Segment를 요구할수도 있다. 명령이 완료되면 Oracle은 Temp Segment를 삭제한다.
- Oracle은 사용자 Session기간 동안 필요한만큼의 Temp Segment를 생성한다.
Create User나 Alter User등의 명령에 Temporary TableSpace다음의 TableSpace에 Temp Segment를 생성한다. 사용자가 Temp Segment를 생성하지 않으면 Default System TableSpace임
예)create user scott identified by tiger default tablespace users temporary tablespace temp;
[SQL]단일행함수중 문자열에 관련된 함수
단일행 Function( 단일행에 대해서만 적용됨)
1. 문자 Function
Lower(Column\Expression)
- 알파벳 문자를 소분자로 전환
Upper(Column\Expression)
- 알파벳 문자를 대분자로 전환
Initcap(Column\Expression)
- 각 단어의 첫문자는 대문자로, 다른 문자는 소문자로 전환)
Concat(Column1\Expression1, Column2\Expression2)
- 첫번째 문자값과 두번째 문자값을 연결, ‘||’와 동일
Substr(Column1\Expression,m,[n])
- 문자값중 m위치에서 n문자길이에 해당되는 문자를 돌려줌
Length(Column\Expression)
- 문자갯수를 값으로 돌려줌
Nvl(column\Expression1,column\Expression2)
- 첫번째 값이 Null이면 두번째 값으로 바꾸어 줌
예]
select lower(first_name||''||last_name) VP,
initcap(userid) USERID
upper(title) TITLE
from s_emp
where title like ‘VP%’;
-------------------------------------------------------------------------------
VP USERID TITLE
-----------------------------------------------------------------------------
ladoris ngao Lnago VP,OPERATIONS
midori nagayama Mnagayana VP,SALES
LOWER(‘SQL COURCE’) ‘sql cource’
UPPER(‘SQL Cource’) ‘SQL COURCE’
INITCAP(‘SQL COURCE’) ’Sql Cource’
CONCAT(‘Good’,’Morning’) ‘GoodMorning’
SUBSTR(‘String’,1,3) ‘Str’
LENGTH(‘string’) 6
제품명 3글자가 Ace인 제품의 이름과 제품명의 길이나열
select name, length(name)
from s_product
where substr(name,1,3) = ‘Ace’
[SQL]단일함수중 숫자에 관련된 함수
----------------------------
단일함수중 숫자에 관련된 함수
----------------------------
단일함수란 하나의 Row에 영향을 주는 함수를 말한다.
ROUND(Column/Expression,n)
Column, Expression값을 소수점 n자리까지 반올림한다.
만약 n이 없다면 소수점은 없어진다. 또한 n값이 음수라면 소수점의 왼쪽 자리수 만큼 반올림된다.
TRUNC(Column/Expression,n)
Column, Expression값을 소수점 n자리까지 절삭한다.
만약 n이 없다면 소수점은 나타나지 않는다. 또한 n값이 음수라면 소수점의 왼쪽 자리만큼 절삭한다.
MOD(m,n)
m값을 n으로 나누어 남은 나머지를 Return한다.
예)
round(45.923,2) a 45.92
round(45.923,0) a 46
round(45.923,-1) a 50
trunc(45.923,2) a45.92
trunc(45.92) a 45
trunc(45.923,-1) a 40
월급여가 1500만원보다 큰 사원에 대해 급여를 상여로 나누고난 나머지 계산
select last_name, mod(salary,commission_pct)
from s_emp
where salary > 1500;
[SQL]단일행함수중 날자관련 함수및 날자연산
----------------------------------
단일행함수중 날자관련 함수및 날자연산
----------------------------------
MONTHS_BETWEEN(date1,date2)
Date1과 date2사이의 달수를 찾아준다. 양수또는 음수가 Return
ADD_MONTHS(date,n)
날자에 n달을 추가함, n은 정수이며 음수도 가능
NEXT_DAY(date,'char')
date다음의 해당주('char')의 다음 날자를 찾아준다. char은 날또는 문자스트링을 나타내는 숫자임
LAST_DAY(date)
date를 포함하고 있는 달의 마지막일을 찾아준다.
ROUND(date[,'fmt'])
어떤 형식 모델(fmt)이 명시되지 않았을 때 자정까지 시간설정과 함께 날자를 돌려줌, fmt가 표시되어 있다면 날자를 그기준으로 round시킴
TRUNC(date,[,'fmt'])
어떤 형식모델(fmt)가 명시되지 않았을때는 date에 포함되어 있는 달의 첫번째달을 찾아준다.
만약 fmt='YEAR'이면 date포함한 해의 첫번째 날을 찾아준다.
예)
MONTHS_BETWEEN('01-SEP-95','11-JAN-94') a 16.6774194
ADD_MONTHS('11-JAN-95',6) a '11-JUL-95'
NEXT_DAY('01-SEP-96','FRIDAY') a '08-SEP-95'
LAST_DAY('02-SEP-96') a '30-SEP-97'
ROUND('25-MAY-95','MONTH') a '01-JUN-95'
ROUND('25-MAY-95','YEAR') a '01-JAN-95'
ROUND('25-MAY-95') a Rounded to the nearest day '25-MAY-95'
TRUNC('25-MAY-95','MONTH') a '01-MAY-95'
TRUNC('25-MAY-95','YEAR') a '01-JAN-95'
입사한지 48개월 미만인 모든 사원들의 사번, 입사일, 근무월수 및 입사6개월되는 날자를 Display
select id,start_date, months_between(sysdate,start_date) '금무월수'
add_months(start_date,6) REVIEW
from s_emp where months_between(sysdate, start_date) < 48;
|
