. Export, Import Utility는 이기종 플래폼에서 운영되는 Database 간의 데이터를 쉽게 이동 시킬수 있는 간단한 방법을 제공
. Export Utility는 Object Definition, table Data 등 추출하여 Binary 형태의 Dump 파일을 생성
. Import Utitity 는 Export 에 의해 생성된 Dump 파일을 Database로 Import 시킨다. Export 에 의해 생성된 파일 이외의 다른 파일은 사용할수 없다.
. Export, Import Utitity는 Database Backup/Recovery에 사용되어 될 수 있다.


- 언제 Export , Import 를 사용하는가?
. 테이블 재 구성 - Row Migration 이 많이 발생한 경우, 빈 Block, Fragmentation이 많을 경우 경합을 최소화 같은 경우 사용
. 사용자간 데이터 이동
. 데이터 베이스간 데이터 이동
. 다른 플래폼 또는 다른 버젼으로 데이터 이동 - upgrade 등 ....
. Logical BackUp시

- Export , Import 사용
. Exprot , Import를 사용하기 생성해야 하는 view가 있는데 catexp.sql을 실행하면 된다. 일반적으로 Database를 생성할때 catalog.sql이 실행 catexp.sql은 catalog.sql에 포함됨 (/oracle/홈/RDBMS/ADMIN 에 존재)
. Export, Import는 command interactive, graphic
. 공통적으로 사용되는 Parameter는 Paramete 파일에 지정
  - > parameter 파일은 name=values의 형태를 가진다.




■ Export Paramger
- userid : EXPORT를 실행시키고 있는 username/password명.
- buffer : 데이터 행들을 가져오는데 사용되는 버퍼의 크기.
- file : 생성되는 EXPORT덤프 파일명.
- filesize : EXPORT덤프 파일의 최대 크기
- grants : 데이터베이스 객체에 대한 권한 정보의 EXPORT여부 (Y/N 플래그)
- indexes : 테이블에 대한 INDEXES의 EXPORT여부 (Y/N 플래그)
- rows : 행들에 대한 EXPORT여부. (Y/N 플래그)
만약 “no”이면 데이터는 EXPORT되지않고 테이블의 정의만 EXPORT 됩니다.
- constraints : 테이블에 대한 제약조건 정보의 EXPORT여부 (Y/N 플래그)
- compress : IMPORT에 대비하여 테이블의 데이터를 한 extent로 압축 할것인가의 여부
(Y/N 플래그)
- full : 전체 데이터베이스를 EXPORT할것인가의 여부 (Full Level Export) (Y/N 플래그)
- owner : EXPORT될 데이터베이스의 소유자명 (User Level Export)[owner=user]
- tables : export될 테이블의 리스트(Table Level Export) [tables=(table1, table2, ...)]


- Table Level Export 
. 명시된 테이블만 엑스포트 됩니다. 
. 테이블의 구조, 인덱스, 권한등이 테이블과 함께 엑스포트 됩니다.

- User Level Export
. 사용자 객체들이 엑스포트 되고 객체들 안에 있는 데이터도 엑스포트 됩니다.
. 사용자 객체에 대한 모든 권한들과 인덱스들도 엑스포트 됩니다.
. 다른 사용자들의 객체와 권한, 인덱스들은 엑스포트 되지 않습니다.

- Full Level Export 
. 전체 데이터베이스가 엑스포트 됩니다. 
. 모든 테이블스페이스, 모든 사용자, 또한 모든 객체, 데이터들이 포함됩니다





- Table Mode (지정 테이블만 Export)
. parameter 파일 내용 (EXP1.PAR 만들어서 밑의 내용을 써라.)
FILE = expdat.dmp
TABLES = (scott.emp, scott.dept)
GRANTS = Y
INDEXES = Y

. 실행 
 - > exp system/manager parfile=exp1.par  (프롬프트 창에서 실행, 파라미터 파일을 미리 만들고 exp 명령 사용시)
 - > exp system/manager tables=(scott.emp,scott.dept) grants=y indexes=y (파라미터 파일 미사용시에는 옵션을 일일이 지정해야함)



- USER Mode (특정 사용자의 모든 테이블 백업)
. parameter 파일 내용 (EXP2.PAR 만들어서 밑의 내용을 써라.)
FILE=scott_all.dmp
OWNER=scott
GRANTS=Y
ROWS=Y
COMPRESS=Y

. 실행
 - > exp scott/tiger parfile=exp2.par



- FULL Mode (DBA 사용자가 모든걸 백업)
. parameter 파일 내용(EXP3.PAR 만들어서 밑의 내용을 써라.)
FILE=dba.dmp
GRANT=Y
FULL=Y
ROWS=Y

.실행
 - >exp system/manager parfile=exp3.par





 Import Parameter

- userid : IMPORT를 실생시키는 계정의 username/password명
- buffer : 데이터를 행들을 가져오는데 사용되는 buffer의 bytes수
- file : IMPORT될 EXPORT 덤프 파일명
- show : 파일 내용이 화면에 표시되어야 할 것인가를 나타냄(Y/N 플래그)
- ignore : IMPORT중 CREATE명령을 실행할 때 만나게 되는 에러들을 무시할 것인지 결정
(Y/N 플래그)
- indexes : 테이블 INDEX의 IMPORT여부(Y/N 플래그)
- rows : 테이블 데이터를 IMPORT할 것인가(Y/N 플래그)
만약 "N"로 설정하면 데이터베이스 객체들에 대한 DDL만이 실행됩니다.
- full : FULL엑스포트 덤프 파일이 IMPORT 할때 사용합니다.
- tables : IMPORT될 테이블 리스트
- commit : 배열(배열의 크기는 BUFFER에 의해 설정됩니다) 단위로 COMMIT을 할것인가 결정
기본적으로는 테이블 단위로 COMMIT을 합니다.
- fromuser : EXPORT덤프 파일로 부터 읽혀져야 하는 객체들을 갖고 있는 테이터베이스 계정
- touser : EXPORT덤프 안에 있는 객체들이 IMPORT될 데이터베이스 계정



- Import 예제
. parameter 파일 내용(IMP1.PAR 만들어서 밑의 내용을 써라.)
FILE=EXPDAT.DMP
SHOW=N
IGNORE=N
GRANT=Y
FROMUSER=SCOTT

. 실행
 - > imp system/manager parfile=imp1.par (파라미터 파일 있을시 파라미터 파일 지정)
 - > imp system/manager file=expdat.dmp fromuser=scott tables(dept.emp)


- 다른 계정으로 IMPORT하기
==> scott유저의 데이터를 EXPORT받아서 test 유저에게 IMPORT하는 예제 입니다.
C:\>exp userid=system/manager file=’C:\scott.dmp’ owner=scott
C:\>imp userid=system/manager file=’C:\scott.dmp’ fromuser=scott touser=test



- 전체 데이터베이스가 IMPORT됩니다. (Full Level)
C:\>imp userid=system/manager file=’C:\full.dmp’ full=y


- scott의 유저 IMPORT를 실행 합니다.(User Level)
C:\>
imp userid=scott/tiger file=’C:\scott.dmp’


. Network 으로 연결된 Database와의 통신을 위해 SQL * NET 또는 NET 8 이라는 Network 통신 모듈이 제공된다.
. Network 으로 연결된 Database를 Access하기 위해 Client에는 tnsnames.ora, sqllnet.ora 파일이 있어야 하며 Server 에는 Listener.ora 파일이 있어야 한다.
. Server 가 Client의 요청을 받기 위하여 Listener가 구동되어 있어야 한다.
. Database 간의 통신을 위해 Database Link 를 사용할 수 있다.
. Client / Server 개념은 상대적이며 Server가 때로는 Client 역활을 수행 할 수도 있다.


- tnsnames.ora 작성법
 - > 홈/Network/ADMIN 에 있다.
 - > GUI 환경에서 편히 작업하고 싶으면 Net Manager 유틸을 이용하라. (오라클 설치시 설치되어있다.)

- listener.ora 작성법
- > 홈/Network/ADMIN 에 있다.
- > GUI 환경에서 편히 작업하고 싶으면 Net Configuration Assistant 유틸을 이용하라. (오라클 설치시 설치되어있다.)



- listener 실행
 명령 프롬프트창
 - > lsnrctl start // 서비스 시작
 - > lsnrctl status // 상태보기
 - > lsnrctl stop // 중지




- 원격 데이터베이스 액세스 - 원격 데이터 베이스 접속
 - > id/password@서비스이름
    - > 서비스이름은 내 컴퓨터에 등록한 원격지 데이터베이스에 별명이다. 곧 나만 알고 있는 이름이다.
 - > 예) tnsnames.ora 파일에 ORATEST 라는 서비스이름(별명)으로 원격지 데이터베이스 orcl 이 등록되 있다.
     -> scott/tiger@ORATEST 라 실행시키면 된다.





- Net Manager 를 이용한 GUI로 tnsnames.ora 편집


1. "로컬 -> 서비스이름 지정 " 을 클릭하고 왼쪽 상단쪽의 플러스(+) 추가 버튼을 클릭한다.



 


2. 네트 서비스 이름 에 자신이 붙이고 싶은 이름을 입력한다. (아무거나 괜찮다. 자신만아는 별명이다.)




3. 프로토콜을 지정하는 부분이다. 인터넷 원격지이기 때문에 TCP/IP(인터넷 프로토콜)을 선택한다.(특수한 경우 다른걸 선택해서 사용해도 된다.)



4. 호스트 이름에는 원격지 데이터베이스의 IP주소를 입력하고 포토 번호에도 원격지 데이터베이스의 포트를 입력한다. 포트의 경우 1521이 기본이다.



5. 서비스 이름을 등록하는 곳이다. 이건 원격지 데이터베이스의 서비스 이름을 입력하는 부분이다.(이건 원격지 데이터베이스 관리자에게 물어봐야한다. 이 서비스 이름 혹은 SID 가 다르다면 접속이 불가능하다.)  네트 서비스 이름이 별명이라면 서비스이름 혹은 SID 는 실제 이름이다.




6. oratest 라는 자신이 만든 네트 서비스 이름이 등록되있는 것을 알 수 있다.



7. 원격지 데이터 베이스에 접속 가능한 id, passwd 와 네트 서비스 이름을 ' id/passwd@네트서비스이름 ' 형식으로 입력하여 접속한다.





- 데이터 베이스 링크 생성
 CREATE DATABASE LINK linkname  CONNECT TO id IDENTIFIED BY passwd USING 'net service name';
 - >  linkname은  편의상 호스트 네임(db.shop.co.kr 이런식)으로 쓰는것이 좋다.
 - > 'net service name' 은 tnsnames.ora 에 등록한 별명이다. 바로 위 예제에서 만든 oratest 같은 것을 말한다.

- 데이터 베이스 링크 사용
 예) SELECT * FROM emp@db.shop.co.kr;

- SYNONYM 생성 (원격지 데이터 베이스의 테이블을 간편한 별명으로 만들때 유용하다.)
 예) CREATE SYSNONYM emp_shop FOR emp@db.shop.co.kr ;

- SYNONYM 사용
 예) SELECT * FROM emp_shop;


- 데이터 베이스 링크를 사용하는 이유
데이터 베이스 링크를 사용하는 경우는 특정 원격 데이터 베이스를 A,B,C 등 여려 사용자가 링크로 원격 데이터 베이스를 사용하면
원격 데이터베이스의 호스트이름(IP)가 변했을때 링크의 정보만 바꾸면 되어 편하다.
링크를 사용하지 않으면 각 사용자가 각각 원격 데이터베이스의 호스트이름(IP)를 바꾸어 등록해야 하는 불편함이있다.
(.. 내가 쓴 글인데도.. 말이 참.. 애매하네요.. 알아서들 이해하세요...)



- 대충 정리
. tnsnames.ora 와 listener.ora 가 있어야한다.
. 서버는 listener 가 실행되고 있는 상태여야한다.
. 데이터 베이스 링크를 만들어 사용한다.
. 데이터 베이스 링크의 특정 테이블은 SYNONYM 이나 VIEW 를 이용한다.
 

  1. 알 수 없는 사용자 2012.10.15 14:12

    좋은 정보 감사합니다. 제 블로그에도 퍼가고자 이렇게 댓글 남깁니다.

    • 단세포소년 2012.10.15 20:46 신고

      동영상 강의 대충 정리해 본 것인데 도움이 된다니 정말 다행입니다.



EMPNO로 검색을 많이 한다면 INDEX를 EMPNO로 오름차순한다.
검색 속도를 빠르게 해준다. INSERT나 DELETE에는 속도가 떨어진다.


인덱스 생성
CREATE INDEX indexname ON table(column) 
STORAGE (INITIAL 500K NEXT 500K PCTINCREASE 0) TABLESPACE tablespacename;
 - > indexname 이 table의 column에서 사용될 것이다. 기본은 오름차순, TABLESPACE가 필요(물리공간 필요)

. ORACLE은 기본적으로 B*-Tree Index를 제공
. 검색(SELECT) 속도를 향상시키기 위해 INDEX를 생성한다.
. 갱신, 삭제, 삽입 등의 작업에서는 INDEX 처리를 위해 OverHead가 발생한다.
. INDEX를 저장하기 위한 물리적인 공간이 필요하다.
. 최적화를 위해 분리된 TABLESPACE에 생성한다.(가능하다면 별도의 Disk)
 - > 입,출력을 따로하여 성능을 향상시키기 위해
. 가능한 수정이 빈번하지 않은 Column을 대상으로 INDEX를 생성한다.
 - > 읽기 전용 테이블..
. Column 분포도가 10~15% 이내인 경우에 Index를 생성한다.(때로는 Full Scan이 효율적인 경우도 있다.)

인덱스 변경
ALTER INDEX indexname STORAGE(PCTINCREASE 0);

인덱스 제거
DROP INDEX indexnamel;
 - > 테이블 삭제시 INDEX도 삭제 된다.





BITMAP INDEX


. 각 값에 대한 하나의 Bitmap, 각 행에 대한 하나의 Entry를 갖는다.
. 논리 연산(AND, OR등)을 통한 효율적인 Bitmap의 조합
. 다른 Indexing 기법과 비교하여 실질적인 공간 사용 감소
. 질의(SELECT) 이외의 작업에는 유지 비용이 많이 발생

비트맵 인덱스 생성
CREATE BITMAP INDEX indexname ON table(column)
STORAGE(INITIAL 500K NEXT 500K PCTINCREASE 0)
TABLESPACE tablespacename;

b*-Tree Index 보다 나은 성능을 발휘하는 경우
. Very Large Table
. 낮은 Cardinality를 갖는 Column(성별, 결혼 유무, 연령, 지역등 적은 유한개의 값을 가질때)
. 질의가 WHERE절에 있는 모든 Column에 대한 Bitmap Index를 가지는 경우
. 질의의 결과가 많은 수의 행을 만족하는 각각의 WHERE절 조건을 가지는 경우

※ 높은 Cardinality 를 갖는 Column에 Bitmap Index를 사용하면 심각한 성능 저하 발생(주민등록번호, 주소 등)




 
UNIQUE INDEX
Unique Index는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있다. Primary key 와 Unique 제약 조건시 생성되는 인덱스는 Unique 인덱스 이다.

UNIQUE INDEX 생성
CREATE UNIQUE INDEX indexname ON table(column);





NON-UNIQUE INDEX
Non-Unique Index는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있다.

NON-UNIQUE INDEX 생성
CREATE  INDEX indexname ON table(column);




CONCATENATED INDEX
결합인덱스는 두 컬럼을 비교하여 검색 할때 많이 쓰인다.

index ON table(A, B ,C) 의 경우 앞에서 부터 정렬된다. 즉 A정렬후 B정렬, B정렬후 C정렬
즉 검색시 중요 조건(컬럼)부터 index 생성시 앞에 두어야 한다.

SELECT * FROM table WHERE A=20 AND B=30 AND C=10; 일 경우 빠르다.
SELECT * FROM table WHERE A=20 일 경우 빠르다.
SELECT * FROM table WHERE C=20 AND B=30 AND A=10; 일 경우 INDEX의 효과가 없다.






REVERSE KEY INDEX
생성
CREATE INDEX indexname ON table(column) REVERSE;

. Reverse Key Index는 Index Column의 Data를 Byte 단위로 역순으로 저장한다. 예) 123 - > 321 로 저장
. Data 123, 124, 125, 126 ... 와 같이 만들어지고, 삭제도 그와 같은 순서로 발생한다고 했을때 일반적인 B*-Tree Index를 사용하면 Tree가 한쪽으로 쏠리는 현상이 발생하게 된다. 이경우 Reverse Key Index를 사용하면 321, 421, 521, 621... 등과 같이 Index가 저장되므로 B*-Tree가 한쪽으로 쏠리는 현상을 방지할 수 있다.





DESCENDING INDEX
생성
CREATE INDEX indexname ON table(column DESC);
. 내림차순으로 index 생성
. ORDER BY 작업을 줄임으로써 검색 속도를 향상시킬 수 있다.





INDEX가 존재 해도 사용할 수 없는 경우
. INDEX COLUMN의 변형
- > SELECT * FROM table WHERE UPPER(ename) ='SCOTT' ;
. NOT 연산자 사용
- > SELECT * FROM table WHERE job <> 'SALESMAN';
. NULL 비교
- > SELECT * FROM table WHERE comm IS NOT NULL;
. Optimizer에 의한 선택
- > 인덱스가 여러가지일때 최적화에 따라 사용이 안될수 있다.

  1. 알 수 없는 사용자 2013.01.17 11:33

    퍼갈게요!! ^^

    많은 도움이 됩니다.

. ROLE 은 권한들의 묶음이다.
. ROLE 은 필요에 따라 ENABLE/DISABLE 될 수 있다.
. ROLE 은 사용자뿐만 아니라 ROLE에게도 부여 될수 있으나 자신에게는 부여될 수 없다.
. 두개의 ROLE이 서로에게 부여 될 수 없다.
. ROLE 은 PASSWORD를 가질수 있다.
. ROLE 을 사용하면 DATABASE 사용자의 권한관리를 단순화 시킨다.

ROLE 사용시 혜택
 - > 권한 부여 작업의 감소, 동적이 권한 관리(Role 변경시 Role 부여 받은 모든 사용자에게 갱신) , 권한의 활성화/ 비 활성화(enable, disable 가능)


ROLE - ROLE 생성
CREATE ROLE name NOT IDENTIFIED;
CREATE ROLE name IDENTIFIED BY password;
DROP ROLE name; // 삭제

ROLE - ROLE 부여 절차
CREATE ROLE name NOT IDENTIFIED;
GRANT CREATE SESSION, CREATE TABLE TO rolename;
GRANT rlename TO user;

. ROLE 생성후 권한을 ROLE에 주고 ROLE을 사용자 또는 다른 ROLE 에게 부여
. ROLE 생성을 위하여 CREATE ROLE 시스템 권한 필요


관련 DICTIONARY
ROLE_SYS_PRIVS : ROLE에 부여된 시스템 권한에 대한 정보
ROLE_TAB_PRIVS : ROLE에 부여된 테이블 권한에 대한 정보
ROLE_ROLE_PRIVS : 다른 ROLE에 부여된 ROLE에 대한 정보
SESSION_ROLE : 현재 사용자에게 활성화된 ROLE에 대한 정보
USER_ROLE_PRIVS : 사용자에게 부여된 ROLE에 대한 정보
DBA_SYS_PRIVS : 사용자 및 ROLE에 부여된 시스템 권한에 대한 정보
DBA_ROLES : 데이터 베이스에 존재하는 모든 ROLE에 대한 정보
. 사용자는 자신의 schema에 object를 생성후 해당 object를 다른 사용자에게 사용 할 수 있도록 권한을 부여
. 해당 object를 생성한 사용자 즉 object의 owner로 부터 해당 object를 사용 할 수 있는 권한
. 사용자에게 object권한을 부여함으로써 특정 Table, view, sequence 또는 stored procedure에 대해 작업을 할 수 있게 해준다.
. object권한의 우형은 object에 따라 다르다. 예로 table이나 view는 SELECT 권한이 있지만 Triger는 SELECT 권한이 없다.


권한부여
 GRANT SCLECT ON tablename TO user;
- > 자신의 table object를 SELECT 할수 있는 권한을 uesr 에게 준다.

GRANT INSERT ON tablename TO user;
- > 삽입 권한을 준다.

GRANT UPDATE(column) ON tablename TO user;
- > 특정 컬럼에만 권한을 준다.

권한을 해제
REVOKE SELECT ON tablename FROM user;






WITH GRANT OPTION

// DBA가 STORM의 권한을 취소하면 STORM이 SCOTT에게 준 권한 또한 사라진다.

. 권한을 받은자가 다른 사용자에게 권한을 줄 수 있다.
. WITH GRANT OPTION으로 주어진 권한은 계층적이다.
. WITH GRANT OPTION은 ROLE에게 부여되지 않는다.
. WITH GRANT OPTION으로 GRANT 한 권한은 REVOKE시 CASCADE 된다. 즉 부여자의 권한이 회수 될때 같이 회수 된다.
. WITH GRANT OPTION은 계층적이다.
 - > a -> b -> c 로 주었을때 c가 a의 권한을 빼앗지 못함


관련 DICTIONARY
. USER_TAB_PRIVS : 사용자가 소유자, 부여자 또는 권한을 받은자인 경우의 오브젝트에 대한 권한
. USER_TAB_PRIVS_MADE : 사용자가 소유하고 있는 오브젝트에 대한 모든 권한
. USER_TAB_PRIVS_RECD : 사용자가 권한을 받은자인 경우의 오브젝트에 대한 권한
. USER_COL_PRIVS : 사용자가 소유자, 부여자 또는 권한을 받은자인 경우 열에 대한 권한
. USER_COL_PRIVS_MADE : 사용자가 소유하고 있는 오브젝트의 열에 대한 모든 권한
. USER_COL_PRIVS_RECD : 사용자가 권한을 받은자인 경우의 열에 대한 권한

. CREATE SESSION - 권한을 받은자가 데이터베이스에 접속가능
. CREATE TABLE - 테이블 생성 권한
. UNLINITED TABLESPACE - 테이블 스페이스에서 블록을 할당가능
. SELECT ANY TABLE - 권한을 받은자가 어느 테이블, 뷰라도 검색가능하며 어떤 schema로 된 snapshot이라도 검색가능
. 이외에도 100여가지 이상의 시스템 권한이 있다.


권한 부여
CREATE USER acc_user identified by password
CONNECT acc_user/password // 실패
GRANT CREATE SESSION TO acc_user, user1, ... [PUBLIC]
// PUBLIC 은 모든 사용자를 뜻함
GRANT CREATE TABLE, UNLIMITED TABLESPACE TO user
// CREATE TABLE 과 UNLIMITED TABLESPACE 가 동시에 존재해야 테이블 생성가능

권한 취소
REVOKE 권한 FROM 유저


WITH ADMIN OPTION



// REVOKE 를 사용하여 STORM의 권한을 없애도 STORM이 SCOTT 에게 준 권한은 사라지지 않는다.



-> 권한을 받은자가 시스템 권한 또는 ROLE 을 다른 사용자 또는 ROLE 에게 부여가능
. WITH ADMIN OPTION은 계층적이지 않다.
 - > a -> b -> c 이면 c가 b의 권한을 없앨수 있다.
. REVOKE시에는 WITH ADMIN OPTION 명시 필요 없음
. WITH ADMIN OPTION 으로 GRANT한 권한은 REVOKE시 CASCADE 되지 않음

사용예)
GRANT CREATE SEESION TO user WITH ADMIN OPTION


DICTIONARY
 - > DBA_SYS_PRIVS

User Management
생성
1. CREATE USER id IDENTIFIED BY password ;
2. CREATE USER id IDENTIFIED BY password ;
   DEFAULT TABLESPACE tablespacename
   TEMPORARY TABLESPACE tempspacename ;
3. CREATE USER id IDENTIFIED BY password
    QUOTA 15M ON tablespacename QUOTA 10M ON system;

* QUOTA (한도량): 사용자에게 tablespace의 크기를 제한 
* 권한이 있는 사용자가 사용자를 만들어야 한다.

DICTIONRAY
- > dba_users(시스템전체), user_users(자기자신)


변경
1. ALTER USER id IDENTIFIED password ;
2. ALTER USER id DEFAULT ROLE connect, resource;
   - > 룰은 사용자 생성시 지정 불가


■ 미리 정의된 롤
롤   명                                  설      명
-------------------------------------------------------------------
CONNECT                     |  이전 버전과의 호환을 위해서 제공됩니다
RESOURCE                    |  이전 버전과의 호환을 위해서 제공됩니다
DBA                           |  모든 시스템 권한과 WITH ADMIN OPTION
EXP_FULL_DATABASE          |  DB를 익스포트할 권한
IMP_FULL_DATABASE          |  DB를 임포트할 권한
DELETE_CATALOG_ROLE       |  DB 테이블에 대한 DELETE 권한
EXECUTE_CATALOG_ROLE     |  DD 패키지에 대한 EXECUTE권한
SELECT_CATALOG_ROLE       |  DD 테이블에 대한 SELECT 권한



제거
1. DROP USER id;
2. DROP USER id CASCADE;
   - > 관련 내용 모두 삭제

* user_ts_quotas : 현 사용자 공간 모니터링
* dba_ts_quotas : 모든 사용자 공간 모니터링




사용자 세션 모니터링
- > v$session

세션 중간 : ALTER SYSTEM KILL SESSION 'sid,serial_number';







PASSWORD MANAGEMENT
. 사용자에 시스템 자원할당의 한계를 정하는 방법으로 PROFILE을 이용
. DATABASE 사용자의 PASSWORD 관리를 위해 PROFILE을 사용
. CREATE USER, ALTER USER 시 부여
. 암호관리 유형
. ACCOUNT LOCKING - 지정 횟수 로그인 실패시 해당 사용자 잠금
. PASSWORD AGING AND EXPIRCTION - 유효기간 설정
. PASSWORD HISTORY - 이전 사용 password 다시 사용 못함
. PASSWORD COMPLEXITY VERIFICATION - 복잡도 지정


예1)
CREATE PROFILE profilename LIMIT
             FAILED_LOGIN_ATTEMPTS 4
             PASSWORD_LOCK_TIME 30  //30일

예2)
ALTER USER id PROFILE profilename ;


DICTIONARY 
- > dba_users
 

명시적 해제 : ALTER USER id ACCOUNT UNLOCK;


* Password Aging and Expiration
 - PASSWORD_LIFE_TIME 90;
   - > 동일 암호 90일 동안 사용가능

 - PASSWORD_GRACE_TIME 3;
   - > 만료기간후 첫 로그인 후 3일 동안 경고 뜸 

명시적 만료 : ALTER USER id PASSWORD EXPIRE;


* Passwrod History
 - PASSWORD_REUSE_TIME 60;  
  - > 같은 암호를 60일동안 재사용 불가      
 - PASSWORD_REUSE_MAX 3;
  - > 암호를 다시 사용까지 횟수 3번임
 - > 위 둘은 베타적 관계 하나가 정수면 나머지는 UNLIMITED 설정 필요

* 복잡성
 - > UTLPWDMG.SQL 스크립트에 예제 존재
 - > PROFILE 에 이 스크립트를 등록한다.
 예) ALTER PROFILE profilename LIMIT PASSWORD_VERIFY_FUNCTION verify_function;


최종예제)
CREATE PROFILE profilename LIMIT
FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60 PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION very_function
PASSWORD_LOCK_TIME 1/24 PASSWORD_GRACE_TIME 10;


PROFILE 삭제
 - > PROFILE DROP profilename;
 - > 모든 속성 UNLIMITED, 함수는 NULL, DROP FUNCTION functionname;


DICTIONARY
 - > dba_profiles


Java Application 은 Oracle Database Server 에서 실행

. Oracle8i 부터  Java Stored Procedure를 사용할 수 있다.
. Java Application 은 Oracle Database Server 에서 실행 된다.
. Java Stored Procedure는 향상된 Application Performance를 제공한다.
. Java Stored Procedure는 비즈니스 룰의 집중된 시행을 제공한다.
. Oracle의 주요 Java Component는 JVM, JDBC, SQLJ이다.
. Oracle의 Server, OCI, Thin 타입의 JDBC Driver를 제공한다.
. Oracle JVM은 AWT를 지원하지 않는다.



 오라클 8 이전 구조



오라클 8 이후 구조


작성 순서



예1)
1. java code 작성
class SayHello {
 public static void main(String[] args){
       System.out.println(say(args[0]));
 }
public static String say(String name){
   return "Hello " + name + "!!!";
 }
}

2. 컴파일
 javac SayHello.java

3. 데이터베이스에 자바 클래스 로드
loadjava -user scott/tiger SayHello.class

4.콜 스펙 작성
CREATE OR REPLACE FUNCTION say_hello(name VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA
NAME 'SayHello.say(java.lang.String) return java.lang.String';

5. 실행
SELECT say_hello('안정환') FROM dual;

6.Java Stored Procedure 삭제
DROP FUNCTION say_hello; //오라클
dropjava -user scott/tiger SayHello.class  //명령행



예2)
1. JDBC 로 작성된 자바 코드 (OS에서 데이터베이스 접속)
import java.sql.*;
class EmpInfoOS
{
      static String dbUrl ="jdbc:oracle:thin:@localhost:1521:orcl";
      static String userId = "scott";
      static String password = "tiger";

       public static void main(String[] args) throws SQLException
       {
            if(args.length !=1){
               System.exit(0);
            }
            System.out.println(getEmpName(Integer.parseInt(args[0])));
        }
       public static String getEmpName(int empNo) throws SQLException
       {
             try{
                          Class.forName("oracle.jdbc.driver.OracleDriver");
              }catch(ClassNotFoundException cnfe) {
                          return cnfe.toString();
              }
       
              Connection connection = DriverManager.getConnection(dbUrl,userId, password);
              Statement statement = connection.createStatement();

              String sqlSystax = "SELECT ename FROM emp WHERE empno =" + empNo;
              ResultSet rs = statement.executeQuery(sqlSyntax);

              String empName = "No data found";
              if(rs.next()) empName = rs.getString("ename");

              statement.close();
              return empName;
       }
}

2. 컴파일
javac EmpInfoOS.java



예3)
 1. JDBC를 이용한 자바코드 (데이터베이스에서 작동되는 메소드)
import java.sql.*;
class EmpInfoDB
{
     public static String getEmpName(int empNo) throws SQLException
     {
         Connection connection = new oracle.jdbc.driver.OracleDriver().defaultConnection();
         Statement statement = connection.createStatement();

         String sqlSyntax = "SELECT ename FROM emp Where empno = " + empNo;
         ResultSet rs = statement.executeQuery(sqlSyntax);

         String empName = "no data found";
         if(rs.next()) empName = rs.getString("ename");

         statement.close();
         return empName;
     }
}

2. 컴파일
javac EmpInfoDB.java

3.데이터베이스에 자바 클래스 로드
loadjava -user scott/tiger EmpInfoDB.class

4.콜 스펙 작성
CREATE OR REPLACE FUNCTION getEmpName(v_empno NUMBER) RETURN VARCHAR2 AS LANGUAGE JAVA
NAME 'EmpInfoDB.getEmpName(int) return java.lang.String';

5. 실행
SELECT getEmpName(7369) FROM dual;




관련 Dictionary
user_objects

PROCEDURE : 실행결과를 반환하지 않는다.
FUNCTION : 실행결과를 반환한다.

. PL/SQL은 DATABASE 내에서 절차적인 처리를 할 수 있도록 지원하는 3GL 언어이다.
. PROCEDURE 와 FUNCTION은 PL/SQL, JAVA, C 등을 이용하여 작성된다.
. PACKAGE는 관련된 PROCEDURE와 FUNCTION들의 묶음이다.
. PACKAGE는 HEADER 부분과 BODY 부분으로 구성되어진다.
. BUSINESS LOGIC 을 STORED PROCEDURE 내에 두면 APPLICATION 수정 없이 SYSTEM을 변경 할 수 있고(유연성) PERFORMANCE를 향상 시킬 수 있다.


예)  사원 급여 조정 Procedure
CREATE OR REPLACE PROCEDURE scott.adjust_sal(v_flag VARCHAR2 , v_empno NUMBER, v_pct NUMBER ) AS
BEGIN
         IF v_flag = 'INCREASE' THEN
             UPDATE emp SET sal=sal + (sal * (v_pct / 100)) WHERE empno=v_empno;
         ELSE
             UPDATE emp SET sal = sal - (sal * (v_pct / 100)) WHERE empno = v_empno;
        END IF;
END;
 - > scott.adjust_sal // scott 사용자의 스키마 내에 생성.
 - > adjust_sal // 사용자 명이 빠진다면 현재 로그인 계정 스키마 내에 생성


예)  사원의 연봉을 구하는 Function
CREATE OR REPLACE FUNCTION scott.get_annual_sal(v_empno NUMBER)
        RETURN NUMBER IS v_sal NUMBER;
BEGIN
         SELECT (sal + NVL(comm,0)) * 12 INTO v_sal FROM emp WHERE empno = v_empno;
         RETURN v_sal;
END;


예) 사원의 퇴직급여를 구하는 Function
CREATE OR REPLACE FUNCTION scott.get_retire_money (v_empno NUMBER)
          RETURN NUMBER IS v_sal NUMBER;
BEGIN
        SELECT ROUND((sal + NVL(comm, 0)) * ROUND(MONTHS_BETWEEN(sysdate,hiredate), 0 ) /12, 0) INTO v_sal FROM emp WHERE empno = v_empno;
        RETURN v_sal;
END;


예) 사원 정보를 삭제 하는 Procedure
CREATE OR REPLACE PROCEDURE scott.remove_emp (v_empno NUMBER) AS
BEGIN
        DELETE FROM emp WHERE empno = v_empno;
END;


예) 사원의 입사일자를 구하는 Function
CREATE OR REPLACE FUNCTION scott.get_hiredate(v_empno NUMBER, v_fmt VARCHAR2)
         RETURN VARCHAR2 IS v_hiredate VARCHAR2(20);
BEGIN
         SELECT TO_CHAR(hiredate, v_fmt) INTO v_hiredate FROM emp WHERE empno = v_empno;
         RETURN v_hiredate;
END;


PROCEDURE 실행
SQL > EXEC procedure_name(매개변수,.....);

FUNCTION 실행
예) SELECT empno "사번", ename "성명", get_annual_sal(empno) "연봉", get_retire_money(empno) "퇴직금" FROM emp WHERE deptno = 30;
 - > get_annual_sal, get_retire_money 가 function 이다. 


개발자를 위한 Help Function 생성
CREATE OR REPLACE FUNCTION scott.help(v_module VARCHAR2) 
            RETURN VARCHAR2 IS v_usage VARCHAR2(100);
BEGIN
         v_usage := v_module || '는(은) 등록되지 않은 모듈 입니다.' ;
         IF UPPER(v_module) = 'ADJUST_SAL' THEN
            v_usage := '종류 : PROC, 사용법 : ADJUST_SAL(INCREASE | DECREASE, 사번, 증감율)';
         ELSIF UPPER(v_module) = 'GET_ANNUAL_SAL' THEN
            v_usage := '종류 : FUNC, 사용법 : GET_ANNUAL_SAL(사번)';
         .....
         ....
         END IF;
         RETURN v_usage;
END;
 
사용 : SELECT HELP('ADJUST_SAL') FROM dual;




PACKAGE 생성
1) package header 생성
CREATE OR REPLACE PACEAGE emp_mgmt AS
    PROCEDURE adjust_sal(v_flag VARCHAR2, v_empno NUMBER, v_pct NUMBER);
    FUNCTION get_annual_sal(v_empno NUMBER) RETURN NUMBER;
END emp_mgmt;

2) package body 생성
CREATE OR REPLACE PACEAGE BODY emp_mgmt AS
-- 급여 조정    
PROCEDURE adjust_sal(v_flag VARCHAR2, v_empno NUMBER, v_pct NUMBER) IS
     BEGIN
              IF v_flag = 'INCREASE' THEN
                  UPDATE emp SET sal = sal + (sal * (v_pct / 100)) WHERE empno = v_empno;
              ELSE
                  UPDATE emp SET sal = sal - (sal * (v_pct / 100)) WHERE empno = v_empno;
              END IF;
   END;

-- 연봉 계산
FUNCTION get_annual_sal (v_empno NUMBER) RETURN NUMBER IS v_sal NUMBER;
     BEGIN 
             SELECT (sal + NVL(comm,0)) * 12 INTO v_sal FROM emp WHERE empno = v_empno;
             RETURN v_sal;
    END;
.....
....
END emp_mgmt;


package 실행
EXEC emp_mgmt.adjust_sal('INCREASE',7369, 10);
 - > procedure 은 EXEC 이용하여 패키지명.함수

function은 SELECT 문장으로 이용하여 실행


PROCEDURE, FUNCTION , PACKAGE  - 관련 DICTIONARY
 - > user_source , user_objects
 - > user_souce 에는 소스가  user_objects에는 오브젝트들의 이름을 확인할수 있다. 즉 user_objects 로 이름 확인하고 user_source 로 소스 확인한다.

. 특정 Event 가 발생 할때 자동으로 임의의 처리를 하고자 할때 사용한다.
. Trigger를 감사에 이용할 수 있다. => 디버그나 log로써 사용할 수 있다.
. Application 개발 시 관련 테이블의 Transaction 처리를 간소화 할수 있다.
. 데이터 무결성을 유지 하기 위해 사용 될 수 있다.
. 분산처리 옵션이 없더라도 원격 데이터의 로컬 사본을 만들 수 있다.


사용 예)
.어떤 자료가 ROLLBACK 될 때 어디의 자료로 ROLLBACK 해야 된다는 TRIGGER 작성가능
.인사 정보가 사라지면 인사과로 삭제되어야 한다.
.테이블을 원격지에 똑같이 만들때


TRIGGER 종류
. DML TRIGGER : DML(INSERT, UPDATE , DELETE) Event 가 발생 할때
. DDL TRIGGER : DDL(CREATE, ALTER, DROP) Event 가 발생 할때 작동
. DATABASE EVENT TRIGGER : DATABASE 를 STARTUP, SHUTDOWN 할때, DATABASE에 LOGON, LOGOFF 할 때 , DATABASE에서 오류가 발생 했을 때 작동
. BEFORE TRIGGER : Event 가 발생한 직전에 실행
. AFTER TRIGGER : Event 가 발생한 직후에 실행
. INSTEAD OF TRIGGER : DML 문장에 의해 직접 변경할 수 없는 VIEW를 변경하기 위해 사용 (매우 유용하면서 중요하다.)


실습을 위한 준비
. 실습개요
 - DML TRIGGER를 사용하여 신규 사원추가에 따라 성적테이블 초기화
 - DML TRIGGER를 사용하여 성적변화에 따른 급여의 가감을 처리
 - DDL TRIGGER를 사용하여 Object에 대한 감사 기능을 수행
 - DATABASE EVENT TRIGGER 를 사용하여 사용자 LOGON/LOGOGG에 대한 감사 기능 수행

예1)
CREATE OR REPLACE TRIGGER emp AFTER INSERT ON emp FOR EACH ROW
BEGIN
         INSERT INTO emp_rating(empno) VALUES(:new.empno);
END;
 - > :new.empno : 트리거에서 제공하는 것 바꾼것의 새로운 것
 - > FOR EACH ROW : 여러 ROW가 영향을 받았을 경우 각각을 실행한다는 의미


예2)
CREATE OR REPLACE TRIGGER emp
AFTER INSERT ON emp FOR EACH ROW
BEGIN
        IF :new.job <> 'PRESIDENT' THEN
            INSERT INTO emp_rating(empno) VALUES(:new.empno)
END;
 - >  IF :new.job <> 'PRESIDENT' THEN  - 새로운 job 이 PRESIDENT 가 아니면 이라는 의미


예3)
CREATE OR REPLACE TRIGGER emp_rating.aft
AFTER UPDATE OF computing ON emp_rating FOR EACH ROW
BEGIN
         IF :old.computing < :new.computing THEN
             UPDATE emp SET sal=sal+sal*0.01 WHERE empno = :new.empno
END;
 - >  AFTER UPDATE OF computing ON emp_rating FOR EACH ROW  : 각각의 emp_rating 테이블의 computing 컬럼이 바뀌었을때  란 뜻이다.


삭제 예)
DROP TRIGGER emp_aft_ins_row ;


TRIGGER - INSTEAD OF TRIGGER
예) UNION 혹은 함수를 사용한 VIEW에서는 INSERT가 불가능하다. 이를 가능하게 하는 TRIGGER를 작성한다.
CREATE OR REPLACE VIEW animal(animal_kind,animal_name,animal_weight) AS 
SELECT 'BIRD', bird_name, weight FROM bird UNION ALL
SELECT 'FISH', fish_name, weight FROM fish UNION ALL
SELECT 'MAMAL',mamal_name,weight FROM mammal ;
- > UNION 은 합집합을 만들어 주는 명령어로 컬럼만 일치하면 합집합을 만들어 준다. 이 경우 VIEW 자체에 INSERT가 불가능하다

위 VIEW 에 INSERT 가 가능케 하려면 INSTEAD OF TRIGGER 가 필요
CREATE OR REPLACE TRIGGER animal_ifno_insert
INSTEAD OF INSERT ON animal
REFERENCING NEW AS n FOR EACH ROW
BEGIN
          IF :n.animal_name IS NOT NULL THEN
             IF :n.animal_kind = 'FISH' THEN
                  INSERT INTO FISH(fish_name,weight) VALUES(:n.animal_name, :n.animal_weight);
            ELSIF :n.animal_kind = 'BIRD' THEN
                  INSERT INTO bird(brid_name, weight) VALUES(:n.animal_name, :n.animal_weight);
           ELSIF  :n.animal_kind = 'MAMMAL' THEN
                      INSERT INTO mammal(mammal_name, weight) VALUES(:n.animal_name, :n.animal_weight);
           END IF;
        END IF;
END;



TRIGGER - DDL TRIGGER (LOG 라고 생각해라)
CREATE OP REPLACE TRIGGER andit_object AFTER CREATE ON SCHEMA[DATABASE]  
BEGIN
        INSERT INTO trigger_log VALUES('CREATE' || sys.dictionary_obj_type || ' ' || sys.dictionary_obj_name || 'by' || sys.login_user, sysdate);
END;
 - > SCHEMA 는 현재 사용자 영역이라고 생각해라. 데이터베이스 전체에 활용하고 싶다면 DATABASE 라고 적으면 된다.
 - > 문자열과 값을 연결할때는 || 을 사용한다.

 

< - 사용자가 CREATE 를 실행시키면 위 andit_object 트리거가 실행되어 trigger_log 에 값을 저장하게 된다.
CREATE 뿐만 아니라 ALTER. DROP등 DDL 에 대해 동일하게 트리거가 작동되며 즉 로그처럼 트리거를 사용할수 있다.











TRIGGER - BATABASE EVENT TRIGGER
CREATE OR REPLACE TRIGGER log_logon AFTER LOGON ON DATABASE
WHEN ( USER = 'scott' OR USER LIKE 'system%' )
BEGIN
        INSERT INTO trigger_log VALUES('LOGON' || USER, sysdate);
        COMMIT;
END;

CREATE OR REPLACE TRIGGER log_logoff BEFORE LOGOFF ON DATABASE
WHEN (USER = 'SCOTT' OR USER LIKE 'SYSTEM%')
BEGIN
        INSERT INTO trigger_log VALUES('LOGON' || USER , sysdate);
        COMMIT;
END;
  1. 블록그 2011.12.17 15:57

    너무 좋은 내용이라 스크랩 욕심이 나네요..
    퍼가겠습니다^^;

    • 단세포소년 2011.12.20 20:38 신고

      막 퍼가셔도 됩니다. 동영상 강의보면서 정리한건데.. 이제는 저도 가물가물하네요.. 공부해야겠다

  2. Toko Speaker JBL 2012.11.30 01:15

    순전히 웹 사이트에서이 문제의 최대 - 날짜에 후속하고, 당신이 내가 당신이 유용한 게시물을 게시하는 데 걸린 시간을 소중히 얼마나 많이 알려하고자하는 것입니다. 게시물 내에서 정말 심각하게 모든 쉽게이 문제를 처리하는 방법에 대한이 말했다. 당신의 블로그에서 좀 더 아이디어를 함께하고 내가 너에게서 배운 무슨 일이라도 다른 사람을 제공하기 위해 마련 내 개인 기쁨이 될 것입니다. 평소 뛰어난 노력에 감사드립니다.

  3. 감사합니다 모든 사람에게이 웹 사이트에서 자세히 읽을 수있는 매우 즐거운 possiblity 주셔서 정말. 그것은 일반적으로 너무 커요뿐만 아니라, 당신이 가지고 새로운 일을 읽어 매주 최소 3 회에서 블로그를 방문 개인적으로 저에게 많은 즐거움과 박제 그리고 내 사무실 친구. 그리고 실제로, 우리는 실제로 귀하가 제공하는 뛰어난 지식을 놀라됩니다. 이 게시물에서 선택한이 도움말은 궁극적으로 지금까지했던 가장 효율적이다.

  4. Paleo Zone Meal Plan 2013.01.30 20:23

    내가 그 계속을 기대하면 소리가 매우 성실하고 좋은 주제를 작성한 것들. 우리 많은 사람들이이 행사에 대해 알고하지 않습니다. 게시물이 도움이됩니다.

  5. ckj 2014.09.15 14:02

    trigger는 따로 커밋명령어 내리지 않아도 커밋되지 않나요?
    맨 마지막 trigger에 commit 명령어는 입력안해도될꺼같아요



Sequence : 자동적으로 순차적인 번호를 생성하고자 하는 때 사용, 기본 키를 생성할 때 주로 사용, Table 에 종속적이지 않으며 여러사용자에 의해 공유, 메모리에 캐시형태로 존재하면 Access 효율성을 높일 수 있다.


SEQUENCE 생선, 변경, 제거
생성 : CREATE SEQUENCE empno_seq INCREMENT BY 1 START WITH 1000 MAXVALUE 9999 NOCYCLE CACHE 10;
변경 : ALTER SEQUENCE empno_seq CYCLE CACHE 20;
삭제 : DROP SEQUENCE empno_seq;

사용 : SEQUENCE_NAME.NEXTVAL  // 다음 숫자
         SEQUENCE_NAME.CURRVAL  // 현재 숫자 
예) empno_seq.NEXTVAL 

** SEQUENCE의 값 사이에는 GAP이 발생가능
   - ROLLBACK, SYSTEM CRASH, 동일한 SEQUENCE가 다른 테이블에서 사용


SEQUENCE의 장점
. 과거 SELECT MAX(empno)+1 FROM emp;  사용
. 전통적 방식보다 오버헤드가 적다.
. Application 코드를 단순화 시킴


SEQUENCE 제약사항
. Sub Query가 아닌 SELECT LIST 에서 사용가능
. INSERT 문장에서 SELECT LIST에서 사용가능
. INSERT 문장의 Values Clause 에서 사용가능
. UPDATE 문장의 Set Clause 에서 사용가능
. DISTINCT 혹은 UNIQUE 를 사용한 SELECT 에서 사용불가
. GROUP BY, HAVING, ORDER BY를 사용한 SELECT에서 사용불가
. VIEW의 SELECT LIST 에서 사용불가
. TABLE에서의 DEFAULT 표현식에서 사용불가


SEQUENCE 관련 DICTIONARY
 - > user_sequences





SYNONYM = 동의어
. 객체 이름을 단순화 시켜주는 객체이다.
. TABLE, VIEW , PRECEDURE, TRIGGER 등 객체에 대한 다른 이름을 제공
. 사용자에게 위치 투명성 제공
. 원격지 테이블을 짧게 할수 있다.

생성 : CREATE SYNONYM e FOR [tablename, viewname, objectname];
삭제 : DROP SYNONYM e ;


SYNONYM 관련 DICTIONARY
 - > user_synonyms
       

. view 는 table 과 유사하고 table 처럼 사용하지만 table과 달리 data를 저장하기 위한 물리적 공간을 필요로 하지 않는다.
. data 를 물리적이 아닌 논리적 집합을 갖는다.
. table 과 마찬가지로 select , insert, update, delete 가 가능
. view 를 생상하면 select 문장이 dictionnary에 저장된다.
. view 를 조회하면 dictionary에 저장되어 있는 해당 view의 sql 문장을 이용하여 근간이 되는 table을 access한다.


VIEW 생성
CREATE VIEW v_emp ( empno, ename , job, mar, hinate, deptno ) AS SELECT empno, ename , job, mar, hinate, deptno FROM emp WHERE job = 'MANAGR' ;

VIEW 삭제
DROP VIEW v_ep ; (VIEW를 없앤다고 해도 물리적이지 않으므로 data가 삭제 되지 않는다.)

VIEW 변경
CREATE OR REPLACE VIEW ~~


VIEW를 사용하는 이유
- 보안 관리을 위한 VIEW
   . 보안 등급에 맞추어 컬럼 및 범위를 정하고 privilege 부여
   . 연산 결과만 제공하고 Algorithm 을 숨기기 위해 사용
   . SELECT List 를 Function으로 가공하여 UPDATE, INSERT를 원천적으로 봉쇄
   . Table 의 명칭이나 Column의 명칭을 숨기기 위한 View

- 사용 편의를 위한 View
   . 검색조건의 단순화
   . End User를 위한 Table 명, Column의 한글화
   . join 문장의 단순화를 위한 View

- 수행속도 향상을 위한 View
   . 미리 Tuning 된 SQL문으로 생성한 View
   . 특정한 절차로 수행시키기 위해 View 의 SELECT List에 HINT등을 사용한 View
 
- 융통성 향상을 위한 View
   . 업무 규칙의 변경이 빈번하여 응용프로그램의 수정이 자주 발생 경우의 해결


- 보안관리를 위한 View
CREATE VIEW v_emp(empno, ename) AS SELECT NVL(empno,NULL), ename FROM emp;
 - > NVL(empno,NULL) 같이 view에 함수를 이용한 가공된 Column은 INSERT, UPDATA 불가

CREATE VIEW v_emp2(empno,ename,annual_sal) AS SELECT empno, ename, (sal + NVL(comm,0))*12 annual_sal FROM emp;
 - > 연산을 숨길수 있다.



읽기전용으로 만들기
CREATE VIEW v_emp_read_only(empno, ename) AS SELECT empno, ename FROM emp WITH READ ONLY;



수행속도 향상을 위한 VIEW

SELECT /*+ INDEX_DESC(dept pk_dept) */ deptno FROM detp WHERE deptno=>0 AND rownum=1;

/*+ INDEX_DESC(dept pk_dept) */ ====>힌트를 주는 것임
(dept테이블의 pk_dept라는 인덱스를 desc거꾸로 정렬해라 하는 뜻)
where절에 deptno>0이라는 당연한 조건을 준이유는 옵티마이저가 검색조건을 비교할때
인덱스(deptno에 생성되어있는 인덱스)를 사용하기 때문이다.
rownum=1은 한건만. 그럼 결국 가장 큰값이 구해지는 것이다.



VIEW 관련 Dictionary
 - > user_views



. NOT NULL : NULL을 허용안함
. CHECK : 조건에 맞는 값만을 허용
. UNIQUE : 중복된 값 허용안함
. PRIMARY KEY : 각행을 유일하게 식별하는 값
. FOREIGN KEY : 값이 참조하고 있는 테이블의 PRIMARY KEY 내에 존재한다고 보장


예)
CREATE TABLE student(
                                    stuid CHAR(5) CONSTRAINT student_stu_id_pk  PRIMARY KEY
                                    CONSTRAINT student_stu_id_ck CHECK (LENGTH(TRIM(' ' FROM stu_id ))=5),
                                    stu_name VARCHAR(10) CONSTRAINT student_stu_name_nn NOT NULL ;

예)
CREATE TABLE score(
                                 stu_id CHAR(5) CONSTRAINT score_stu_id_nn NOT NULL,
                                 std_id NUMBER(5) CONSTRAINT score_std_id_nn NOT NULL,
                                 score NUMBER(3) CONSTRAINT score_nn NOT NULL,
                                 grade CHAR(1) CONSTRAINT score_grade_ck CHECK(grade IN('A','B','C'));
                                 CONSTRAINT score_pk PRIMARY KEY(stu_id, stb_id),
                                 CONSTRAINT score_fk FOREIGN KEY(stu_id) REFERENCES student(stu_id) ON DELETE CASCADE);
// ON DELETE CASCADE 는 student 의 stu_id 가 삭제되면 같이 삭제되어라. (무결성)


제약조건 비활성화
ALTER TABLE table DISABLE CONSTRAINT constraint_name;
예)
ALTER TABLE score DISABLE CONSTRAINT score_pk;
ALTER TABLE score DISABLE CONSTRAINT PRIMARY KEY;


제약조건 활성화
DISABLE -> ENABLE 로 바꾸면 된다.


EXCEPTION
**ENABLE 시 무결성 조약이 안되어 ENABLE이 안될 때 EXCEPTION 이용
EXCEPTION 테이블 필요
CREATE TABLE exceptions (
                                       row_id ROWID,
                                       owner VARCHAR2(30),
                                       table_name VARCHAR2(30);
                                       constraint VARCHAR(30));
**ALTER TABLE student ENABLE PRIMARY KEY EXCEPTION INTO exceptions;
**SELECT * FROM WHERE rowid IN(SELECT row_id FROM exptions);





제약조건 추가
ALTER TABLE table ADD CONSTRAINT ~~
예) ALTER TABLE score ADD ( CONSTRAINT score_score_ck CHECK (score > -1), CONSTRAINT score_score_ck2 CHECK(score < 10) );

제약조건 삭제
ALTER TABLE table DROP CONSTRAINT constraint_name ;
예) ALTER TABLE score DROP PRIMARY KEY;
     ALTER TABLE socre DROP UNIQUE(std_id) CASCADE


제약조건 관련 DICTIONARY
USER_CONSTRAINTS 테이블에 제약조건 존재



예 ) CREATE TABLE emp_family(
                                                emp NUMBER(4) NOT NULL,
                                                fam_name VARCHAR2(10) NOT NULL,
                                                ....
                                                CONSTRAINT emp_family_pk PRIMARY KEY(empno,fam_name)
                                               );



테이블 변경
1) ALTER TABLE emp_family ADD (job VARCHAR2(30));
2) ALTER TABLE emp_family MODIFY (job VARCHAR(40) NOT NULL);

테이블 삭제
1) DROP TABLE table;
2) DROP TABLE emp CASCADE CONSTRAINT;    // FOREIGN KEY도 다 함께 삭제 된다. (foreign key 란 다른 테이블의 primary key 를 참조하는 키 입니다.)



컬럼제거
1) ALTER TABLE table DROP COLUMN age;
2) ALTER TABLE table SET UNUSED COLUMN age;
1,2 의 차이점 : DROP COLUMN은 물리적으로 삭제 SET UNUSED COLUMN은 논리적으로 사용안함
3) ALTER TABLE table DROP UNUSED COLUMNS ; 
 - > unused 컬럼들을 물리적으로 삭제





ROW CHAINING(행 연결)


- 행 데이타가 정상적으로 INSERT된 후 삭제작업이 발생하여 하나의 행이 삭제되고 해당블록에는
삭제된 행 크기만큼의 빈 공간이 생김
- 새로운 행 데이타가 입력되면 빈 공간이 존재하는 블록에 데이타가 입력되고 공간부족 현상이 발
생 되면 나머지 데이타가 새로운 블록에 입력된다.

==> SELECT 문에 의한 데이타 검색시 성능 저하현상 발생.





ROW MIGRATION (행 이주)




행 데이타영역에 사용자가 입력한 데이타들이 모두 입력되어 저장공간이 없는 경우
기존 데이타의 변경작업이 일어나면 변경에 의해 저장할 수 있는 공간이 없게된다.
이런 경우 오라클 서버는 변경할 수 없었던 행들을 모구 새로운 블록으로 이동시킨 후 변경작업 수행
==> 행이주(Row-Migration)

- 주로 VARCHAR2 타입을 가진 컬럼에서 발생.
- Row_Migration이 발생한 테이블릐 해당 행을 검색하면 오라클서버는 처음에 데이카가 저장되어
있는 블록을 먼저 검색하고 해당 행이 다른 블록으로 이주되어 있으면 이주된 블록을 다시 읽어
데이타를 검색
- 하나의 행을 검색하기 위해 여러개의 블록을 읽어야 데이타를 가져올 수 있기때문에
검색속도 저하. ( SELECT문 처리시 데이타 검색속도 저하)




PTCTREE 파라메터



ex)
PTRFREE=10 <== 테이블이 생성될 때 할당된 블록의 행 데이터 영역의 크기에서 10% 공간을
INSERT문 실행시 사용하지 말고 블록 내에 데이타 변경(UPDATE)시만 사용하기 위해
미리 확보.
- 행들의 변경이 발생하면 같은 블록내에 변경된 정보를 저장할 수 있기 때문에
한번의 블록 읽기를 통해 원하는 행 참조 가능.
- PTCFREE크기 산정시 평균행의 크기와 초기 행의 크기는 DBA_TABLES 자료사전에서
해당 테이블 정보 참조.

ANALYZE TABLE [table_name] COMPUTE STATISTICS



PCTUSED

PCTUSED


PCTUSED ?


오라클 서버가 테이블의 각 데이터 블록에 대해 유지하려는 사용 공간의 최소 백분율로써
데이터 세그먼트에 대해 지정합니다


ex) PCTUSED 40

-->테이블의 데이터 세그먼트에 사용된 데이터 블록의 사용영역이 39%보다 적어지지 않으면
새로운 행을 삽입할 수 없음을 의미 합니다.


◈ PCTUSED의 기본값은 40% 입니다.


◈ PCTUSED값이 적을 경우

- 블록이 재사용되는 일이 적어질 수 있으므로 처리 비용이 감소 합니다.

- 데이터베이스에서 사용되지 않은 공간이 증가 합니다.


◈ PCTUSED값이 클 경우

- 블록이 재사용되는 일이 많아질 수 있으므로 처리 비용이 증가 합니다.

- 공간 사용도를 향상 시킵니다.


PCTFREE와 PCTUSED값을 선택하는 예

UPDATE시 ROW의 크기가 증가 될 때
- PCTFREE = 20
- PCTUSED = 40

② Insert, Delete가 자주 발생하지만 Update시 ROW의 크기가 증가 되지 않을 때
- PCTFREE = 5
- PCTUSED = 60

③ 테이블이 매우 크지만 대부분 Read Only Transaction 일 때
- PCTFREE = 5
- PCTUSED = 90

 

 




 

 



. Temporary TABLESPACE
 -> 모든 Sort Operation 을 위해 필요한 TABLESPACE, 영구적인 Object를 포함 불가능하다.

CREATE TABLESPACE tablespacee
DEFAULT STORAGE storag_clause
PERMANENT  // 영구적인 object 보유지정(Default)
TEMPORARY // 임시 object 보유지정

. TABLESPACE 생성시 TEMPORARY 키워드를 이용
. v$sort_segment 에서 sort segment 에 대한 공간 할당과 해제 정보 제공


READ ONLY TABLESPACE
- 다음 조건을 갖춰야 Read only 로 만들수 있다.
. tablespace 는 online 이어야 한다.
. 활성 transaction 이 없어야 한다.
. tablespace는 활성 rollback segment 를 포함하고 있지 않아야 한다.
. tablespace는 online backup과 관련되지 않아야 한다.

SYSTEM TABLESPACE는 결코 읽기가 될수 없다.

- TABLESPACE를 읽기 전용으로 만들기
. ALTER TABLESPACE tablespace READ ONLY;

- 읽기 전용 TABLESPACE를 읽기, 쓰기 TABLESPACE로 만들기
. ALTER TABLESPACE tablespace READ WRITE;

- v$datafile view는 data file의 현재 상태를 보여줌


DATA FILE SIZE 조정
1) ALTER TABLESPACE test_data
    ADD DATAFILE 'file path' SIZE 10M
    AUTOEXTEND ON
    NEXT 1M
    MAX SIZE 250M ;

2) ALTER DATABASE DATAFILE 'file path' RESIZE 20M;



TALBESPACE 관련 DICTIONARY
. DBA_TABLESPACE : 모든 TABLESPACE의 STORAGE 정보 및 상태 정보를 갖음
. DBA_DATA_FILES : TABLESPACE를 구성하는 각 DATA FILE 의 정보를 갖음
. DBA_FREE_SPACE : TABLESPACE의 공간 사용에 관한 정보 갖음
. DBA_FREE_SPACEE_COALESCE : TABLESPACE의 수집 가능한 EXTENT 에 대한 통계정보를 갖음


TABLESPACE 공간 수집(디스크 조각 모음)
. ALTER TABLESPACE tablespace COALESCE; 

'학습자료(~2017) > 오라클' 카테고리의 다른 글

10. 오라클 - 제약조건  (0) 2011.03.15
9. 오라클 - 테이블 관리 및 활용  (0) 2011.03.15
8. 오라클 - 테이블 스페이스 관리  (0) 2011.03.14
7. 오라클 - 테이블 스페이스  (0) 2011.03.11
6. 오라클 - Sub Query  (0) 2011.03.11
5. 오라클 - 조인  (0) 2011.03.11

Oracle Database 는 두가지 유형의 테이블 스페이스로 구성되어있다.

● SYSTEM 테이블 스페이스
 - Data Dictionary 정보, Stored Procedure의 정의 Package, Trigger 포함
 - SYSTEM ROLLBACK SEGMENT 포함
 - 사용자 데이터 포함 가능

●NON-SYSTEM 테이블 스페이스
 - 보다 융통성 있게 데이터베이스를 관리
 - Temporary segment , Application Data Segment, Index Segment, User Data Segment
 - Rollback : 특별한 성격의 NON-SYSTEM 테이블스페이스이며 사용중인 Rollback Segment 가 있으면 off-line, 읽기전용으로 전환 할수 없으며, SYSTEM 테이블 스페이스 처럼 복구 되어야 한다. 다시 말하면 복구를 하기위해 전체 데이터 베이스를 off-line하여 복구해야한다.


Storage 구조

Storage 구조를 보면 테이블 스페이스가 직접적으로 말해 data file들의 묶음이다. 테이블 스페이스는 Segment 로 나누어지고 Segment 는 Extent 로 나누고 Extent 는 Oracle block 으로 나뉜다. Oracle block은 o/s Block으로 나뉜다. 
특히 Extent 는 Data file과 매칭 된다. 즉 Extent는 data file 그 자체이다. 또한 순차적 Block이다.
테이블 스페이스는 하나 이상의 파일로 구성 가능 하며 테이블 스페이스의 용량이 부족하면 늘리는 방법이 있다.




TABLE SPACE 생성
CREATE TABLESPACE tablespacename DATAFILE filespec
DEFAULT STORAGE storage_clause

예) CREATE TABLESPACE test_data
     DATAFILE 'f:/oradata/orcl/test_data01.dbf' SIZE 10M
     DEFAULT STORAGE ( INITIAL 2M
                                     NEXT 1M
                                     MINEXTENT 1
                                     MAXEXTENTS 121
                                     PCTINCREASE 0 ) ;

 .  INITIAL 2M : 초기 2M 할당
 .  MAXEXTENTS 121 : 121회 NEXT 가능
 .  NEXT 1M : 용량 부족으로 다음 추가 할당은 1M 이다.
 .  MINEXTENT 1 : 최소 EXTENT 1부터 시작
 .  PCTINCREASE 0 : 추가 할당(NEXT)에 따른 EXTENT 값을 %만큼줄임



TABLESPACE 변경
ALTER TABLESPACE tablespace
           ADD DATAFILE filespec
           RENAME DATAFILE 'filename' to 'filename'
           DEFAULT STORAGE storage_clause ;



TABLESPACE 제거
DROP TABLESPACE tablespace
INCLUDING CONTENTS   //포함된 object, segment를 지움
CASECADE CONSTRAINTS  // 참조 무결성 깨지더라도 다지우겠다.
ADD DATAFILE ;   // 실 파일 삭제

** 기본적으로 object 나 segment 가 존재시 TABLESPACE는 삭제 되지 않는다.
** 기본적으로 파일로 삭제되지 않는다. (ADD DATAFILE 을 추가하면 실 파일 삭제)



TABLESPACE 이해 - 요약
. 테이블 스페이스는 논리적인 storage 개념이다.
. 테이블 스페이스는 물리적으로 하나 이상의 데이터 파일로 구성된다.
. 테이블 스페이스는 크게 SYSTEM 테이블 스페이스와 NON-SYSTEM(USER) 테이블 스페이스로 구분된다.
. segment data , index, rollback, temporary segment등으로 구분된다.
. segment는 하나 이상의 extent들로 구성된다.
. extent 란 연속적으로 할당된 free block 의 모음이다.
. extent 는 기본 storage 할당 단위이다.
. DB Block(Oracle Block) 은 기본 IO단위이다.

'학습자료(~2017) > 오라클' 카테고리의 다른 글

9. 오라클 - 테이블 관리 및 활용  (0) 2011.03.15
8. 오라클 - 테이블 스페이스 관리  (0) 2011.03.14
7. 오라클 - 테이블 스페이스  (0) 2011.03.11
6. 오라클 - Sub Query  (0) 2011.03.11
5. 오라클 - 조인  (0) 2011.03.11
4.오라클 - 그룹, 함수  (0) 2011.03.11

. 단일행 Sub Query : Sub Query 의 결과로 하나의 행 리턴
. 다중행 Sub Query : Sub Query 의 결과로 하나이상의 행 리턴
. 다중열 Sub Query : Sub Query 의 결과로 하나 이상의 열 리턴

사용지침
.WHERE 절, HAVING 절, FROM 절에 Sub Query를 사용 할수 있다.
.Sub Query 는 ()로 둘러싸여 있다.
.Sub Query는 연산자의 오른쪽에 위치하여야 한다.'
.단일행 Sub Query 는 단일행 연산자를 다중행 Sub Query는 다중행 연산자를 사용해야 한다.


예)
질문1) jones 보다 급여를 많이 받는 사원은?
답1) SELECT empno, ename, sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='jones');
 - > 단일열 Sub Query와 단일행 Sub Query 사용



그룹함수를 이용한 Sub Query
예) SELECT empno, ename, sal FROM emp WHERE sal = (SELECT MIN(sal) FROM emp);

HAVING 절에서의 Sub Query
예) SELECT job, MAX(sal) FROM emp GROUP BY job HAVING MAX(sal) > (SELECT MAX(sal) FROM WHERE job='manager');

PAIRWISE Column 비교 :  두개의 컬럼을 묶어서 같은 경우만 참으로 보고 조회한다.
예) SELECT empno, ename, deptno ,sal comm FROM emp WHERE (sal, NVL(com, -1)) IN(SELECT sal, NVL(comm,-1) FROM emp WHERE deptno = 30) ;

FROM 절에서의 Sub Query
 - > FROM 절에서는 Sub Query 가 하나의 VIEW(테이블) 표현
 - > 별칭을 사용해야 한다.
예)SELECT a.empno, a.ename, a.sal, a.deptno, b.avg FROM emp a , (SELECT deptno, ROUND(AVG(sal)) avg FROM emp GROUP BY deptno) b WHERE a.deptno = b.deptno ;



질문2) 급여를 많이 받은 순으로 앞에서 랭킹을 붙여 조회
 - > ROWNUM 이라는 컬럼을 사용하는 것이 관건. ROWNUM은 행 위치를 나타냄
답) SELECT rownum, empno, ename, sal FROM (SELECT * FROM emp ORDER BY sal DESC)
 - > FROM 절에 Sub Query 로 임시 VIEW를 생성한다. 임시 VIEW는 급여로 내림차순 되어 있고 rownum값이 랭킹 값이 된다.



질문3) 급여를 많이 받는 사원 순으로 6위에서 10위까지 랭킹 붙여서 출력
답) SELECT ranking, empno, ename, sal FROM (SELECT rownum ranking, empno, ename, sal FROM (SELECT empno, ename, sal FROM emp ORDER BY sal DESC)) WHERE ranking BETWEEN 6 AND 10;
 - > rownum은 실제 존재 컬럼이 아니다. alias(별칭)를 만들면 복사본이 만들어지고 임시적으로 컬럼이 된다.



'학습자료(~2017) > 오라클' 카테고리의 다른 글

8. 오라클 - 테이블 스페이스 관리  (0) 2011.03.14
7. 오라클 - 테이블 스페이스  (0) 2011.03.11
6. 오라클 - Sub Query  (0) 2011.03.11
5. 오라클 - 조인  (0) 2011.03.11
4.오라클 - 그룹, 함수  (0) 2011.03.11
3. 오라클 - 데이터 제한 및 정렬  (0) 2011.03.10

조인이란 하나 이상의 테이블을 조인해서 보고싶을 때 사용한다.
방법 : 조인 조건은 WHERE 절에 기술, 똑같은 열 이름이 하나 이상의 테이블에 존재 한다면 모호성을 없애기 위해 열 이름 앞에 테이블 이름을 붙여야 한다.
중복되지 않는 열 이름이라 할지라도 열이름 앞에 테이블 이름을 붙이면 성능 향상
2개 이상의 테이블을 조인할 수 있다.


EQUIJOIN
 - > PRIMARY KEY , FOREIGN KEY 와 같이 같은 값이 존재하는 테이블끼리 조인
예 ) emp 테이블과 dept 테이블에 deptno 라는 같은 데이터형의 컬럼이 존재한다면
SELECT emp.empno, emp.ename, emp.deptno, dept.dname FROM emp, dept WHERE emp.deptno = dept.deptno AND sal > 2000 ;


TABLE ALIAS 사용
테이블명이 길거나 테이블명이 같다면 별명을 붙여 짧게 혹은 구별하여 사용할수 있다.
예) SELECT e.empno , e.ename, e.deptno , d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ;
테이블 별칭을 사용하면 SQL문장을 간단하게 작성가능하다. (컬럼 별칭을 사용하는 것과 흡사된다.)



NON-EQUIJOIN
 - > 동일한 data를 가진 column이 없는 table간에 join을 하고자 할 때 사용함
예) SELECT e.empno, e.ename, e.sal , s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;



OUTER JOIN
 - > 두개의 테이블에서 공통된 컬럼이 없거나 또는 컬럼에서 같은 값이 없다면 JOIN문을 사용하여도 원하는 결과값을 얻어오지 못한 다. 때문에 한쪽의 값이 없더라도(null) 결과값이 나올 수 있게 해주는 조건문이다. 어느 한쪽의 컬럼을 기준으로 공통된 값을 얻어 오고, 공통된 값이 없더라도 기준이 되는 컬럼만큼은 결과값으로 찾아오게 된다.

emp
 empno ename deptno 
 7782 CLARK  10 
 7369 SMITM 20
 7499 ALLEN  30

dept
 deptno dname loc 
 10 ACCOUN  NEW YORK 
 20 RESEARCH JP 
 30 SALES KR
 40 OPERATION OR 

예) SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e, dept d WHERE e.deptno(+) = d.deptno;
 - > (+) 는 행 수가 부족한 테이블 쪽에 붙여준다.(의미상 부족한 행을 더 있는것 처럼 보이게 하라는 뜻 같다.)
결과
 e.empno  e.ename   d.deptno   d.dname 
 7782  CLARK  10   ACCOUN 
 7369  SMITM  20  RESEARCH
 7499  ALLEN   30  SALES
     40  OPERATION


예) SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
결과
 e.empno e.ename  d.deptno  d.dname 
 7782  CLARK 10  ACCOUN 
7369  SMITM  20  RESEARCH
7499  ALLEN  30  SALES





SELF JOIN
 - > 자기 자신과 조인하는 구문이다. 자기 자신의 테이블을 구분하기 위해 테이블 별칭(alias)과 컬럼 별칭을 사용해야한다.
예) SELECT w.ename worker, m.ename manager FROM emp w, emp m WHERE w.mgr = m.empno;





Cartesian Product - > 모든 경우수 발생
 - > Query의 From절에 2개 이상의 Table이 있고, 두 Table 사이의 유효한 Join 조건이 기술되어 있지 않은 경우에는 두 Table의 모든 행들이 무조건 결합하여 Table들에 존재하는 행 갯수를 곱한 만큼의 결과값이 반환되는 것을 Cartesian Product(카테시안 곱)라고 한다.
 
. 조인 조건이 생략되거나 잘못된 경우 Cartesian Product가 발생
. 조인 되는 모든 테이블의 모든 행이 조인 되는 경우 Cartesian Product 가 발생한다.
. Cartesian Product 가 발생하면 잘못된 결과를 얻을 수 있으므로 WHERE 절에 정확한 조인 조건을 써야한다
. Cartesian Product 를 외도적으로 사용할 수도 있다.

'학습자료(~2017) > 오라클' 카테고리의 다른 글

7. 오라클 - 테이블 스페이스  (0) 2011.03.11
6. 오라클 - Sub Query  (0) 2011.03.11
5. 오라클 - 조인  (0) 2011.03.11
4.오라클 - 그룹, 함수  (0) 2011.03.11
3. 오라클 - 데이터 제한 및 정렬  (0) 2011.03.10
2. 오라클 - 기본 질의문  (0) 2011.03.10

함수
MAX() : 최대값
AVG([DISTINCT | ALL] n ) : NULL을 무시한 평균
COUNT({* | [DISTINCT | ALL] expr }) : NULL을 무시하지만 * 이라면 NULL과 중복을 포함
MAX([DISTINCT | ALL] expr) : NULL 값 무시 expr의 최대값
MIN([DISTINCT | ALL] expr) :  NULL 값 무시 expr의 최소값
STDDEV([DISTINCT | ALL] n) : NULL값 무시한 n의 표준 편차
SUM([DISTINCT | ALL] n ) : NULL값 무시한 n의 합계
VARIANCE([DISTINCT | ALL] n) : NULL값 무시한 n의 분산

예) SELECT SUM(sal),AVG(sal) FROM 테이블명




. 그룹 함수는 열에 있는 NULL 값을 무시한다.
. NULL 값을 포함하기 위해서는 NVL 함수를 사용한다.
 예) SELECT AVG(NVL(comm, 0)) FROM emp
   NVL 은 어떤 값이 널일때 원하는 값으로 값을 치환한다.




행 그룹화 : GROUP BY
예) SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;
예)오류: SELECT deptno , MAX(sal) FROM emp
   -> deptno 는 비그룹함수 , MAX는 그룹함수 이다. 그래서 위 문장은 오류이다.
예)GROUP BY deptno, job ;
  - > 컬럼 순서에 따라 표현 순서가 다르다. 컬럼 다수를 묶어서 그룹화 가능



☆☆ GROUP 결과 제한 : HAVING 을 이용
 - > GROUP BY 사용시 그룹에 대한 제한은 HAVING 을 사용한다. (평상시는 WHERE 이용)
예) SELECT deptno , MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal) >= 2900 ;
예) SELECT job, SUM(sal) FROM emp WHERE job <> 'SALSMAN' GROUP BY job HAVING SUM(sal) > 5000 ORDER BY SUM(sal);




그룹함수 사용 요약
. 단일 함수는 하나의 행에 대해 하나의 결과 리턴
. 그룹 함수는 하나 이상의 행에 대해 하나의 결과 리턴
. 그룹 함수는 GROUP BY 절과 함께 사용되어 행을 그룹화
. 그룹 합수는 연산대상에 NULL을 포함하지 않음
. COUNT 함수의 *, NVL 함수 등을 이용하여 NULL 값을 포함할수 있다.
. 그룹의 결과를 제한하기 위해서 HAVING 절 사용

'학습자료(~2017) > 오라클' 카테고리의 다른 글

6. 오라클 - Sub Query  (0) 2011.03.11
5. 오라클 - 조인  (0) 2011.03.11
4.오라클 - 그룹, 함수  (0) 2011.03.11
3. 오라클 - 데이터 제한 및 정렬  (0) 2011.03.10
2. 오라클 - 기본 질의문  (0) 2011.03.10
1. 오라클  (0) 2011.03.10

데이터 제한
SELECT [DISTINCT] { *, cloumn [alias]} FROM 테이블명 [WHERE condition(s)];
. 문자 스트림과 날짜 값은 단일 인용부호(') 로 묶어서 표현
. 문자값은 대소문자를 구분, 날짜 값은 날짜 형식을 구분


연산자 의미
<>  같지않다
 BETWEEN a AND b a와 b사이 
 IN(list)  list 값 목록중 하나와 일치
 LIKE 문자 패턴 일치 
 IS NULL , IS NOT NULL NULL 값 비교 
 AND 그리고 
 OR  또는
 NOT  참, 거짓 반대로

예) sal BETWEEN 1500 AND 1800 (sal 이 1500이상 1800이하)
예) sal BETWEEN 1800 AND 1500 (찾을수 없다 작은 값을 앞에 큰 값을 뒤에 두어야 한다.)
예) empno IN(7,8,9) (empno 가 7,8,9 중 하나와 일치하면 참)
예) ename LIKE 'J%'  (% : 하나 이상 또는 값이 없는 문자, _ : 하나의 문자)
      그냥 순수 문자 % 를 출력하려면 \% 처럼 패턴인식 문자 앞에 \를 붙여라


SELECT DISTINCT DEPTNO FROM EMP;
  - DISTINCT : 행이 중복시 하나의 행만 출력


데이터 정렬
ORDER BY 컬럼 [ASC, DESC]
 -ASC : 오름차순, DESC : 내림차순


'학습자료(~2017) > 오라클' 카테고리의 다른 글

6. 오라클 - Sub Query  (0) 2011.03.11
5. 오라클 - 조인  (0) 2011.03.11
4.오라클 - 그룹, 함수  (0) 2011.03.11
3. 오라클 - 데이터 제한 및 정렬  (0) 2011.03.10
2. 오라클 - 기본 질의문  (0) 2011.03.10
1. 오라클  (0) 2011.03.10

SQL,SQL * PLUS 기본
  • SQL은 대소문자를 구분하지 않음(쉬운 코드를 위해 적절히 대소문자를 구분)
  • Data type 은 반드시 일치시킨다.
  • WHERE 절에는 상수값 대신 호스트 변수를 사용한다.
    • 예) V_EMPNO = 1010; WHERE empno = V_EMPNO ;
      왜냐하면 WHERE empno=1010 과 WHERE empno = 2020 이 다르다고 생각
  • WHERE 절의 비교대상이 되는 Column을 변형하지 않는다.
  • SQL 작성 규칙을 만들고 모든 개발자가 지키도록 한다.


SELECT 컬럼, ... FROM 테이블 WHERE 조건(AND, OR, =)
INSERT INTO 테이블 (컬럼,.....) VALUES(값,......)
UPDATE 테이블 SET 컬럼 = 값, ....... WHERE 조건
DELETE FROM 테이블 WHERE 조건

오라클 SQL *Plus 기본 아이뒤 SCOTT:TIGER

테이블 확인 :  SELECT * FROM TAB;
응용 : SELECT sal *12 FROM EMP
응용 컬럼이름 다르게 표시 SELECT sal * 12 "~~~~"  FROM emp
응용단위에  ',' 찍기 : SELECT empno , TO_CHAR(sal *12,'999,999,...') FROM emp
                                                        함수↑

날짜 조건으로 WHERE DATE BETWEEN '1987/01/01' AND '1987/12/30' ;
정렬 : ORDER BY 컬럼[ASC,DESC]

테이블 변경시 COMMIT 하면 현재까지 내용 완전 저장
ROLLBACK은 COMMIT 전까지 복구

 테이블 생성
  CREATE TABLE 테이블명(
                                        이름 자료형 [NOT NULL] [PRIMARY KEY]
                                        CONSTRAINT emp_faily PRIMARY KEY(empno,fname)
                                       );

CONSTRAINT 는 제약조건이다.
CONSTRAINT emp_faily PRIMARY KEY(empno,fname) 문 경우 제약조건이름은 emp_faily 이고 PRIMARY KEY 가 empno,fname 혼합 키 라는 것이다.


테이블 삭제
 DROP TABLE 테이블명

SQL * PLUS 명령어
1. DESCRIBE 테이블명   : 테이블 정보 보기
2. DESC 테이블명          : 테이블 정보 보기 축약형
3. RUN                         : 가장 최근 명령실행(축약 R)
4. EDIT                        :  버퍼의 명령을 편집가능(복사, 붙여넣기에 좋다)
5. APPEND                   : 버퍼에 내용추가
6. CLEAR BUFFER         : 버퍼 지우기
7. GET 경로명               : 파일의 문장을 읽어 버퍼에 저장 그후 RUN 하면 좋다
8. START 경로명           : 파일을 읽고 명령을 실행 GET 후 RUN 한것과 같다
9. SPOOL 경로명           : 모든 작업을 경로 파일에 저장
10. EXIT                       : 종료

 

'학습자료(~2017) > 오라클' 카테고리의 다른 글

6. 오라클 - Sub Query  (0) 2011.03.11
5. 오라클 - 조인  (0) 2011.03.11
4.오라클 - 그룹, 함수  (0) 2011.03.11
3. 오라클 - 데이터 제한 및 정렬  (0) 2011.03.10
2. 오라클 - 기본 질의문  (0) 2011.03.10
1. 오라클  (0) 2011.03.10

oracle은 회사이름. Oracle은 관계형 데이터 베이스 시스템.
기능 : Oracle standard edition(SE) < Oracle enterprise edition(EE)

Foreign key는 부모 테이블에 항시 존재하고 부모테이블에 없는 값은 참조 될 수 없다.(무결성) 자식 테이블에서 참조하고 있는 부모의 Primary key는 삭제 될 수 없다.

자료형
 CHAR 고정길이(남는 부분은 공백으로 채워짐) 
 VARCHAR2 가변길이 
 NCHAR  멀티바이트 고정길이
 NVARCHAR  멀티바이트 가변길이
 DATE  날짜와 시간(7byte)
 NUMBER  숫자형, 음수, 양수, 실수
 LONG  가변길이
 RAW  가변길이 바이너리 데이터
 LONG RAW  가변길이 바이너리 데이터
 BLOB  바이너리 객체(4G)
 CLOB  문자객체
 NCLOB  멀티바이트 문자셋이 사용되는 문자 객체
 BFILE  외부 바이너리 파일(OS종속)
 ROWID  Row ID 를 나타내는 바이너리 데이터


오라클 특징
1. 다양한 방법으로 데이터 베이스 Access 가능
  - ODBC, JDBC
2. 거의 모든 언어에서 Oracle 지원
3. PL-SQL은 데이터 베이스를 절차적으로 Access 할 수 있다.
4. 프로그램 코드 내에 SQL 문장을 직접 서술하여 사용가능, 이를 Embedded SQL이라함
5. 최적화
  - rule-based optimizer 룰에 따라, cost-based optimizer 통계에 따라


SGA(System Global Area)
Oracle server 실행시 시스템 전역 영역(SGA)에 메모리 할당
SGA는 Oracle에 대한 데이터와 제어정보를 갖고 있음
SGA는 여러 사용자간 공유

  • Shared Pool은 공유 SQL 영역과 Data Dictinary Cache
  • 공유 SQL 영역은 SQL문장을 실행하기 위한 정보가 저장됨
  • Data Dictionary Cache는 데이터 베이스와 그 구조, 사용자에 대한 참조정보를 가지고 있다.
    • 모든 테이블과 뷰의 이름
    • 테이블의 컬럼명과 데이터 유형
    • 모든 사용자의 권한
  • Buffer cache
    • 디스크에서 읽은 데이터 복사본 존재
    • 사용자 공유
    • 캐시 적중을 노림
    • LRU 알고리즘 운영
  • Redo Log Buffer
    • 변경 정보를 갖고 있는 순환 버펴
    • Recovery시 데이터베이스와 RollBack Segment 엔트리의 변경사항을 재구성
    • Create Table과 Create Index 문장에 UNRECOVERABLE시 생략가능
    • SQL * LOADER의 DIRECT 옵션으로 생략가능


필수 Processes
PMON,SMON,DBWR,LGWR는 Oracle Instance를 실행하기 위한 필수 프로세스
PMON = Processes 모니터, 비정상에 대한 예외처리(Lock 풀기 등 리소스 해제)
SMON = System 모니터, Instance 자동복구 temp segment (order 같은 명령시 임시 테이블) 해제
DBWR = 데이터 버퍼의 내용을 디스크에 read, write 함
LGWR = log 정보를 디스크에 read,write 함
SNPn = Snapshot 자동 Refresh
LCKn = 병렬서버 시스템에서 Instance 간 Lock 수행
RECO = 분산 Transaction 의 실패복구
Pnnn = 병렬 질의, 병렬 인덱스 생성, 병렬 데이터 로드등의 기능 제공
CkPT = Check Point 발생
ARCH = Archive Log Mode 로 데이터 베이스 운용시 Redo Log File의 내용을 Archive Log File로 백업
Server = parse 구문 분석, Execute 실행, Fetch 인출



FILE
Redo Log File <-순환 파일(세개의 파일이 있다면 첫번째가 다 차면 두번째파일로 두번째가 다 차면 세번째 파일로 세번째가 다 차면 다시 첫번째 파일로 스위치함)

Control File 은 데이터 베이스의 구조를 기술하는 작은 크기의 Binary 파일
데이터 베이스 이름이 Control File 에 저장
다른 disk에 최소 두개의 Control File을 만드는 것이 좋다.

Parameter File <- 파라미터 값이 쓰여 있다
  - SHARED_POOL_SIZE , DB_BLOCK_SIZE, DB_BLOCK_BUFFERS.....



 

'학습자료(~2017) > 오라클' 카테고리의 다른 글

6. 오라클 - Sub Query  (0) 2011.03.11
5. 오라클 - 조인  (0) 2011.03.11
4.오라클 - 그룹, 함수  (0) 2011.03.11
3. 오라클 - 데이터 제한 및 정렬  (0) 2011.03.10
2. 오라클 - 기본 질의문  (0) 2011.03.10
1. 오라클  (0) 2011.03.10

+ Recent posts