펌]oracle 기본메뉴얼

카테고리 없음

펌]oracle 기본메뉴얼

woony5231 2008. 8. 12. 20:59

○ ORACLE SQL 매뉴얼

Ⅰ. RDBMS 개념
- 개체(objects)또는 관계(relation)을 이용하여 데이타를 저장하고 검색,수정등
모든 액세스를 관리(sql이용)

? RDBMS 구성
- TABLE, ROW, COLUMN, FIELD, PRIMARY KEY, FOREIGN KEY

? RDBMS 객체
- TABLE : ROW, COLUMN으로 구성된 기본적인 데이터 저장 단위
- VIEW : 한개이상의 TABLE 데이터의 논리적인 부분집합체
- SEQUENCE ; PRIMARY KEY값을 자동으로 생성하는 객체
- INDEX : 데이터 조회 성능을 향상 시키는 것
.자동생성 : PRIMARY KEY로 생성된
.이용자 생성 : PRIMARY KEY이외의 UNIQUE한 COLUMN이 있을때 이용자가 만듬
> CREATE INDEX ......
- SYNONYM : 객체의 다른이름(동의어)
- PROGRAM UNIT : PROCEDURE,함수,PL/SQL BLOCK...

? SQL
- 오라클7 서버와 통신 하기위한 명령언어(ANSI표준)
- QUERY : SELECT
- DML(DATA MANUPLATION LANGUAGE) : INSERT, DELETE, UPDATE
- DDL(DATA DEFINE LANGUAGE) : CREATE, ALTER, DROP, RENAME, TRUNCATE
- 트랜잭션 제어어 : COMMIT, ROLLBACK, SAVEPOINT
- DCL(DATA CONTROL LANGUAGE) : GRANT, REVOKE

? SQL*PLUS
- SQL 및 PL/SQL 문장을 인식하고 실행시켜 주는 오라클의 TOOL
- 환경 : SQL문장의 작업환경
( SQL>SHOW ALL(전체환경 보기)
SQL>SHOW USER( 현재 ID 보기) )
- 형식 : 검색결과 출력형식 지정 ( FORMAT )
- 파일조작 : 스크립트 파일 저장, 실행, 편집(버퍼 내용)
( SAVE, SET, @, EDIT, START, RUN,...)
- 상호작용 : 사용자변수를 SQL문장에 전달 및 메시지 출력
( HOST, EXIT 등 ; OS로 잠시 나갔다가 옴)
- 기타 ; 데이터베이스 접속, 환경조작, COLUMN정보표시 명령어

? PL/SQL
- 응용프로그램 로직을 추가하여 SQL을 확장한 오라클의 절차적인 언어
- HEADER : 서브프로그램 이름,형태,인수 정의(서브프로그램에서만 사용)
- DECLARATIVE : 변수 선언
- EXECUTABLE : 실행내용(SQL, PL/SQL문장)
- EXCEPTION : 에라 발생시 처리내용 정의
※ PL/SQL 블럭구조
Declare -> 변수 선언
Begin -> 실행명령
Exception -> 예외처리(에라)
End -> 종료

※ sql문의 특징
- 데이터 액세스를 위한 오라클 서버와의 통신
- 데이터와 테이블 정의를 조작, SQL*PLUS BUFFER에 보관
- 종료문자로 종료, 표준 ansi 따른다
- multi-line으로 지정 가능(하나의 절은 하나의 라인에 사용)
- 대소문자 구분이 없음(통일시 빠르다)

※ sql*plus문의 특징
- sql 명령어를 인식하여 서버에 전송
- 데이터 베이스 값을 조작 못함, 한번에 한행씩 실행 버퍼에 저장안됨
- 명령어의 축약이 가능
- set, show, clear, column, btitle, ttitle, break, compute
ed[it], get, save, spool, host, sta[rt], r[un], help
- 편집명령 : a[ppend] text, c[hange]/old/new, cl[ear] buffer
del, i[nput] text, l[ist] n, n text

Ⅱ. 데이터베이스 작성

▷ Table : 데이터 저장(실제로는 파일 형태)
▷ View : 하나이상의 Table에 있는 데이터의 부분집합에 대한 논리적인 표현
▷ Sequence : Primary Key 값을 생성하는 것
▷ Index : 일부 Query에 대한 성능 향상을 위한 것

? TABLE

▷ 생성
CREATE TABLE [SCHEMA.]table명
(column data-type [default 식] [column제약조건],
.......
[table 제약조건]);

- Table명 : Table명 과 column명은 문자로 시작 30자 까지 허용
A-Z, a-z, 0-9, _, $, #
- Data-type : VARCHAR2(n) - 가변길이 문자 1-2000 BYTE
CHAR(n) - 고정길이 문자 1-255 BYTE
NUMBER(p,s) - 전체p, 소수s자리(ps생략시 38자리까지 유효한 부동소수)
DATE - B.C.4712년1월 - A.D.4712년12월31일
LONG - 2GB까지의 가변길이 문자값(Table한개당 하나만 허용)
RAW, LONG RAW - VARCHAR2, LONG과 같지만 이진데이타 저장에 사용
- 제약조건 : NOT NULL - NULL값을 가질수 없음.(column 레벨에서 명시)
UNIQUE - Table의 모든행에서 고유한 값을 가져야함.
PRIMARY KEY - Table의 각행을 유일하게 식별할수 있는 column표시
FOREIGN KEY - 다른 Table의 참조되는 column과의 관계설정
CHECK - 반드시 참이어야 하는 조건 명시
> constraint emp_empno_uk unique(empno) ....emp_empno_pk primary key(empno)

> constraint emp_empno_ck check(deptno in(1,2,3,4))
> constraint emp_empno_fk foreign key(empno) references dept(id) [on delete cascade]

▷ 변경
- column의 추가, 변경(data-type,크기,default값,제약조건(not null))
. data가 있을 경우 data-type 변경,크기 줄이기가 안되고 default값 변경시
이후 생성되는 row에서만 적용된다.
>alter table emp add(ename2 varchar(10));
>alter table emp modify(ename2 varchar2(20));
- Table 삭제, 이름변경, 주석달기, 내용삭제
>drop table emp cascade constraint;
>rename emp to newemp;
>comment on table emp is 'Employee Infromation';
>comment on column emp.ename is 'Name of Employee';
>truncate table emp; -> 테이블의 모든행을 삭제하고 저장공간을 해제
- 제약조건의 추가 및 삭제, Disable, Enable
>alter table emp add constraint emp_mgr_fk foreign key (mgr) references dept(id)
>alter table emp drop constraint emp_mgr_fk cascade;
>alter table emp disable|enable constraint emp_mgr_fk cascade;

▷ DML(Data Manupulation Language)

○ INSERT => INSERT INTO table명[(column[,column,,,])] VALUES (값[,값,,,」);
- INSERT INTO test(id) VALUES ('SEOUL');
- INSERT INTO test SELECT * FROM EMP;
- CREATE TABLE test AS (SELECT * FROM EMP WHERE ...);
조건 만족안될때는 테이블 구조만 생성된다.
○ UPDATE => UPDATE table명 SET column = 값[,column = 값,,,] [WHERE 조건];
- UPDATE EMP SET DEPTID = 10 WHERE ID = 2;
- UPDATE EMP SET COMM = 10; -> 모든행의 값을 변경
○ DELETE => DELETE FROM table명 [WHERE 조건];
- DELETE FROM test; -> 몽땅 지움
- DELETE FROM test WHERE ID = 1; -> 선별적으로 지움
- DROP TABLE -> TABLE 삭제
- TRUNCATE TABLE -> 전체삭제
○ COMMIT
- 자동 COMMIT 되는 경우
DDL, DCL 수행시, SET QUTO C

OMMIT ON시, EXIT (NOMAL하게 종료한경우)
○ SAVEPOINT
- SAVEPOINT를 설정하여 ROLLBACK할 위치를 지정가능
○ ROLLBACK
- SYSTEM FAIL, 정전

? SEQUENCE

▷ 테이블 행에 사용될 Primary Key값을 자동적으로 만드는데 사용되는 데이터베이스
객체로서 테이블과 독립적으로 생성되고 저장되며 여러 사용자가 공유 할수 있다.
▷ Oracle 7 Server가 생성하고 증감 시킨다.
▷ 하나의 SEQUENCE를 여러 테이블에서 쓸수 있습니다.

▷ 생성
CREATE SEQUENCE name [INCREMENT BY n] [ START WITH n ]
[{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}]
[ {CYCLE n | NOCYCLE} ] [ {CACHE n | NOCACHE} ]
- NOMAXVALUE:10의 27승 NOMINVALUE:1
>create sequence emp_id start with 1 maxvalue 5 nocache cycle;
>insert into emp values(emp_id.nextval, '&a');
>select emp_id.currval from dual;
-확인 select sequence_name,min_value,max_value,increment by,last_number from user_sequences;
▷ 변경
- START WITH는 한번 쓰고 없어지므로 ALTER SEQUENCE 를 써서 변경할수 없고

삭제후 다시 생성해야 합니다.
ALTER SEQUENCE name [INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}]
[ {CYCLE n | NOCYCLE} ] [ {CACHE n | NOCACHE} ]
DROP SEQUENCE name

? 뷰(VIEW)
▷ 뷰는 자체 데이터는 없는 실질적으로는 QUERY 문장임
▷ 데이터의 논리적인 부분집합이나 조합
▷ 여러 Table에서 복잡한 JOIN과 Query 과정으로 만들어 하나의 Table처럼 이용
▷ 데이터베이스에 대한 액세스를 일부 제한 하는데 이용
▷ 생성
CREATE [OR REPLACE] [force|noforce] VIEW 뷰명 [(alias[,alias...])]
AS SUBQUERY [WITH CHECK OPTION [CONTRAINT 제약조건]]
[WITH READ ONLY]

- force : base table유무에 관계없이 qbfmf 만든다.(미리 뷰를 만들때)
- sub query에서 수식, 함수 등은 반드시 alias를 써야 뷰의 column명이 된다.
- or replace 를 써서 기존의 뷰를 변경할 수 있다.
>create or replace view emp45
as select id,ename,job from emp
where deptno = 45;
>create or replace view emp45 (id_45,ename_45,job_45)
as select id,ename,job from emp
where deptno = 45;
- VIEW 삭제
>DROP VIEW 뷰명

? 인덱스(INDEX)
▷ Table과 독립적인 데이터를 빠르게 찾기위한 데이터베이스의 객체
▷ oracle optimizer 가 유지관리 하며 사용자는 개입할 필요가 없음.
▷ Binary Tree 형태로 검색
▷ 인덱스 유형
- Unique 인덱스
- Non-Unique 인덱스
- 단일 column인덱스
- 연결 또는 조합 인덱스

▷ 생성
- CREATE INDEX index명 on table명(column[,column,,,]);

- 인덱스 필요시점 - where 나 join절에서 동일 column을 자주 사용할때
- column이 넓은 범위의 값 또는 많은 null값을 가질때
- table이 크고 대부분의 query가 10-15%이하를 검색할때
-> table이 자주 변경될때 index를 만들면 변경시 속도가 늦어진다.

- 인덱스 확인
. 뷰 : user_indexes - 인덱스의 이름과 unique여부를 보유
user_ind_columns - 인덱스명, 테이블명,column명 보유
- 인덱스 삭제
. DROP INDEX 인덱스명;

□ ORACLE DATA DICTIONARY VIEW

USER : 사용자 소유의 객체에 관한 정보를 저장
ALL : 사용자에게 ACCESS가 허용된 객체에 관한 정보 보유
DBA : DBA권한을 가진 사용자가 ACCESS할수 있는 정보를 보유
V$ : 서버의 성능과 locking에 관련된 정보 보유 dba에게만 허용

>SELECT * FROM DICTIONARY;

-사용자가 ACCESS할수 있는 모든 DICTIONARY 뷰와 주석을 보여줌.
DICTIONARY, DICT_COLUMNS
USER_OBJECTS, USER_CONSTRAINTS, USER_CONS_COLUMNS

□ PRIVILEGES

>CREATE USERS user-id IDENTIFIED BY password;
>CREATE USER user-id EXTERNAL; -> O.S의 PASSWORD를 가져옴
>ALTER USER user-id IDENTIFIED BY 암호;
>DROP USER
- 권한 : CREATE SESSION, TABLE, SEQUENCE, VIEW, PROCEDURE
>GRANT 권한[,권한,,,] TO 사용자[,사용자,,,];
- ROLE : 권한들의 그룹
>CRETAE ROLE role명; >GRANT 권한,,,, TO role명;
>GRANT role명 TO id;
▷권한부여
GRANT {권한,,,|ALL} [(column)] ON 객체
TO {사용자,,,,|ROLE|PUBLIC} [WITH GRANT OPTION];
- WITH GRANT OPTION -> OBJECT PRIVILEGE
- WITH ADMIN OPTION -> SYSTEM PRIVILEGE
▷권한회수
REVOKE {권한,,,|ALL} ON 객체 FROM {사용자,,,|ROLE|PUBLIC};
▷권한확인
권한관련 DICTIONARY VIEW - ROLE_SYS_PRIVS

, ROLE_TAB_PRIVS, USER_ROLE_PRIVS
USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD, USER_COL_PRIVS_RECD

[출처] [본문스크랩] oracle정리|작성자 메멘토