오늘의 요약
8. Data Dictionary
(1) 설명
Oracle 의 테이블은 '2가지' 종류가 있다.
첫번째, DB생성할 때 기본적으로 만들어지는 '자료사전테이블'
두번째, User가 데이터를 저장하고 관리하기 위한 '사용자정의테이블'
전자가 Data Dictionary 테이블이다.
(2) 종류
1) DBA_XXX : DB전체의 관련정보 저장 테이블
2) ALL_XXX : 자신이 볼 수 있는 Object 정보 테이블
3) USER_XXX : 자신이 생성한 Object 정보 테이블
4) X$_XXX : DB의 성능 분석/통계 정보 테이블 ( DB튜닝시 수정함 )
5) V$_XXX : X$_XXX 의 VIEW ( 성능 참조 )
<예1>
SQL> select * from dictionary;
SQL> select * from dict_columns;
cf) Ctl + C : 명령실행 정지
<예2> user_ (*****)
SQL> select * from user_tables;
SQL> desc user_tables;
SQL> select TABLE_NAME from user_tables;
SQL> select * from tab; --위와 비교
SQL> desc user_indexes
SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME
from user_indexes;
SQL> desc user_constraints
SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
from user_constraints;
SQL> desc user_views
SQL> select VIEW_NAME, TEXT from user_views;
cf)
create or replace view VIEW1 as
select DEPTNO "번호", round(avg(SAL)) "반올림" from EMP
where SAL>=1000
group by DEPTNO
having round(avg(SAL))>=2000
order by DEPTNO desc;
SQL> select * from user_tab_comments;
SQL> select * from user_col_comments;
<예3> all_
SQL> select * from all_tables;
SQL> desc all_tables
<예4> dba_
SQL> select * from dba_tables; --없음
SQL> conn system/java0909
SQL> select * from dba_tables;
9. Data Type ( 형 == Type )
(1) 설명
Oracle에서 제공하는 데이터 타입
(2) 종류
1) 스칼라(Scalar) 타입
cf) Scalar: 실수로 표시할 수 있는 수량
<1> 하나의 데이터 타입컬럼에
오직, '하나의 데이터'만 저장할 수 있는 타입
<2> '문자/숫자/날짜' 데이터를 저장
<3> 종류
1> NUMBER ( -38 ~ +38 자리수 )
2> BINARY_INTEGER
( -2147483648 ~ 2147483647 ) == (-2^31 ~ 2^31-1)
3> CHAR(0~255=0~2^8-1), NCHAR
- CHAR(10) -> 나머지 공간을 SPACE 로 채움
- NCHAR(10) -> 다양한 언어의 문자값을 저장
4> VARCHAR, VARCHAR2(4000), NVARCHAR2(4000)
- VARCHAR2(10) -> 필요한 공간만 채움
- NVARCHAR2(10) -> 다양한 언어의 문자값을 저장
5> BLOB, LONG ROW, CLOB, LONG
- BLOB -> 바이너리 데이터를 4G
- LONG ROW -> 바이너리 데이터를 2G
- CLOB -> 문자 데이터를 4G
- LONG -> 문자 데이터를 2G
6> DATE ( 초단위 데이터 저장 )
7> TIMESTAMP ( 마이크로초 저장 )
- TIMESTAMP WITH TIME ZONE
- TIMESTAMP WITH LOCAL ZONE
- INTERVAL YEAR TO MONTH
cf1) java.sql.Timestamp 를 이용
cf2) '1/10^6'초까지 저장은 가능하나
컴퓨터에서 생성할 수 있는 유효한 시간은
'1/10^3' 초이므로 실제 저장되는 시간은
'1/1000' 초 단위임
8> BOOLEAN ( true / false 저장 )
2) 모음(Collection) 타입
<1> 하나의 데이터 타입 컬럼에
'여러개의 데이터(배열/테이블)'를 저장할 수 있는 타입
<2> 배열/테이블 데이터를 저장
<3> 종류
1> VARRAY
2> NESTED TABLE
(3) 시간 관련 함수
1) CURRENT_DATE 함수 ( SYSDATE 유사 )
cf) 시간 포멧 수정
SQL> alter session set
NLS_DATE_FORMAT='YYYY-MM-DD AM HH:MI:SS DAY';
SQL> alter session set NLS_LANGUAGE='ENGLISH';
2) CURRENT_TIMESTAMP 함수 (*****)
SQL> select CURRENT_TIMESTAMP from DUAL;
SQL> alter session set TIME_ZONE='-10:00';
SQL> select CURRENT_TIMESTAMP from DUAL;
SQL> alter session set TIME_ZONE='00:00'; --영국표준시간
SQL> select CURRENT_TIMESTAMP from DUAL;
3) LOCALTIMESTAMP 함수
SQL> select CURRENT_TIMESTAMP, LOCALTIMESTAMP from dual;
cf) TST테이블
create table TST(
NO number,
RDATE date,
TS timestamp);
insert into TST values(10, SYSDATE, CURRENT_TIMESTAMP);
(4) ROWID 와 ROWNUM 컬럼 (*****)
1) 설명
oracle 에서 테이블을 생성하면 기본적으로 제공되는 컬럼
2) 종류
1> ROWID
-> ROW 의 고유 ID ( 중간에 row 수정/삭제시 불변 )
2> ROWNUM
-> 행의 INDEX (중간에 row 삭제시 변함 )
SQL> select count(*) from DEPT;
SQL> select count(PK컬럼) from DEPT;
SQL> select max(ROWNUM) from DEPT;
10. CONSTRAINT ( ***** )
(1) 설명
테이블의 해당 컬럼에 '원치않은 데이터가 입력/변경/삭제 되는 것을 방지'
하기위해 테이블 생성(create)시 또는 변경(alter)시 설정하는 조건
cf) 제약 조건명(constraint의 변수명)을 개발자가 직접
부여하면 추후 해당 constraint 관리가 용이
(2) 종류
1) PRIMARY KEY (식별키) [ 테이블당 (0<=count<=1) 개 ]
하나의 테이블에 오직 '하나'만 존재하며 자동으로 INDEX
가 부여되는, 하나의 ROW 데이터를 대표하는 '대표 키'
2) FOREIGN KEY (참조키/외래키) [ 테이블당 (0<=count<=n) 개 ]
부모 테이블의 'PK/UK'를 참조하는 키
3) UNIQUE KEY (유일키) [ 테이블당 (0<=count<=n) 개 ]
PK가 아니더라도 컬럼의 모든 값이 유일해야 하는 경우
에 사용되는 제약조건이다.
즉, 중복데이터를 허용하지 않는 컬럼에 부여하는 키
cf) PK와 차이점
첫째, NULL 을 입력할 수 있다.
둘째, 하나의 테이블에 여러개의 유일키 생성 가능
4) CHECK
'조건'에 맞는 데이터만 입력되도록 조건을 부여한 제약조건
cf) 위의 '조건'이란 ?
- 데이터 값의 범위
- 특정 패턴 숫자
- 문자값 설정
...
5) NOT NULL
NULL 이 입력되어서는 안되는 컬럼에 부여하는 제약조건으로
'COLUMN-LEVEL'으로만 부여할 수 있는 제약 조건
cf1) PK는 not null 포함
cf2) default 는 제약조건이 아님
cf3) not null 조건도 constraint_type 이 'C'로 표현됨
(3) LEVEL 기준 제약조건 부여방법 ( ***** )
1) COLUMN-LEVEL
-> 하나의 제약조건을 부여할 때 사용하며
'5가지'를 모두 부여할 수 있음
ex) const1.sql (이름을 부여하지 않은 케이스, 오라클에서 직접 부여한 이름으로 출력됨)
const2.sql (이름을 부여한 케이스, 개발자가 직접 부여한 이름으로 출력됨)
2) TABLE-LEVEL
-> 하나의 이상의 컬럼에 여러개의 제약조건을 부여할 때
사용함 ( not null 불가 )
ex) const3.sql(이름X), const4.sql(이름O), const5.sql(이름O, 테이블밖)
cf) 개인적인 생각
- const 1~5 중 간단하고 가독성이 좋은 것은 2번을 선호
- 제약조건 수정하려면 삭제하고 다시 만들어야 함
- disable constraint, enable constraint 은 비추천(무결성이 깨지는 편법)
- 5가지 패턴을 익숙하게 하면 Good!
11. 임시 테이블 ( TEMPORARY TABLE )
(1) 설명
개발자가 DML(insert, update, delete)문을 실행한 후
트랜젝션을 종료(commit) 하더라도 변경된 데이터들이
DB 테이블에 저장되지 않는 테이블.
즉, 잠시 데이터를 저장하는 '메모리상의 일시적 공간'
(2) 데이터를 유지하는 방법
1) ON COMMIT DELETE ROWS ( 유효범위: Transaction )
-> commit 하는 순간에 row 삭제 tables은 존재하지만 '데이터는 사라진다'
SQL> create GLOBAL TEMPORARY table T1(
NO number,
NAME varchar2(10)) ON COMMIT DELETE ROWS;
SQL> select * from tab;
SQL> insert into T1 values(10, '홍길동');
SQL> insert into T1 values(20, '이순신');
SQL> select * from T1;
SQL> update T1 set NAME='박서하' where NO=20;
SQL> select * from T1;
SQL> commit;
SQL> select * from T1; -- 데이터가 없음
2) ON COMMIT PRESERVE ROWS ( 유효범위: Session )
-> commit 하는 순간에 row 보존 ( session이 종료되면 사라진다 )
<세션1>
SQL> create GLOBAL TEMPORARY table T2(
NO number,
NAME varchar2(10)) ON COMMIT PRESERVE ROWS;
SQL> insert into T2 values(10, '홍길동');
SQL> insert into T2 values(20, '이순신');
SQL> commit;
SQL> select * from T2; -- rows 안보임
<세션2>
SQL> select * from T2; -- rows 안보임
(3) 장점
일시적으로 필요한 데이터를 저장할 수 있다.
즉, '어떤 시점'을 기준으로 데이터가 '자동 삭제'되기 때문에
DB의 resource 를 절약할 수 있다.
cf1) 임시 테이블 삭제
SQL> drop table T1;
SQL> drop table T2;
cf2) 삭제시
T1은 데이터가 있어도 drop 됨
but, T2는 데이터가 있으면 drop 안됨
12. 뷰 ( VIEW )
실제로 존재하는 것이 아닌 논리적인 '가상 테이블'
view를 사용하려면 일단 권한(create view) 필요
(1) 설명
뷰는 테이블에 대한 '가상테이블'로써 테이블이 존재하지 않으면 뷰도 존재할 수 없음.
뷰는 테이블처럼 데이터를 직접 소유하지 않고 '검색'시에만 정의된 뷰를
'틀'에 맞게 보여줌
(2) 사용 이유
1) 기본 테이블에 대한 '보안기능'을 설정해야 하는 경우
2) 복잡하며 자주 사용되는 질의 SQL문을 보다 쉽고 '간단'하게 사용해야 하는 경우
(3) 사용 단계
1) VIEW 생성 권한을 scott 계정에 부여
SQL> conn system/java1019
SQL> grant CREATE VIEW to SCOTT; -- oracle 10g부터
2) VIEW 생성
SQL> create or replace view VIEW1 as
select DEPTNO "번호", round(avg(SAL)) "반올림" from EMP
where SAL>=1000
group by DEPTNO
having round(avg(SAL))>=2000
order by DEPTNO desc;
3) VIEW 사용
SQL> select * from VIEW1;
SQL> desc VIEW1;
SQL> select 번호 from VIEW1;
(4) 특징
1) VIEW 안 데이터의 물리적인 저장공간은 따로 가지지 않는다.
-> 이유 : 테이블 안의 데이터를 '참조만' 하고 있기 때문
2) 테이블을 기초로 하는 '가상(논리)' 테이블이다.
3) 하나 이상의 테이블로 만들어진다.
4) 뷰를 access 하면 관련된 테이블도 간접적으로 access 된다.
5) 테이블에서 선택된 컬럼정보만 참조할 수 있다.('보안')
6) 복잡한 조인(JOIN)질의를 '간단', 명료하게 실행할 수 있다.
7) 미리 '튜닝된 SQL문'을 사용하여 성능을 향상시킬 수 있다.
8) DB 테이블 변경 될 때
응용프로그램(JAVA)에 대한 수정이 용이(유지 보수가 좋다.)
즉, Language Part 에서 code 수정이 필요없다.( SM 관점 )
9) 다른 세션에서도 동일하게 적용된다. ( DBMS에 저장된다. )
(5) 핸들링 ( handling )
1) 생성
<1> 틀
create or replace [FORCE|NOFORCE] view명
( 컬럼별칭1, 컬럼별칭2, ... , n)
as
select 문
<2> 옵션 설명
- replace : 이미 존재하는 뷰이름이라면 '덮어쓴다'
- force : 관련 테이블의 존재 여부에 관계없이 뷰 생성
- noforce : 관련 테이블이 존재할 때만 생성 가능
- with check option : 제약 조건을 설정
- with read only : DML작업을 할 수 없다
2) 검색
SQL> select * from tab; --VIEW도 확인 가능
SQL> select view_name from user_views;
3) 변경 (덮어쓰기)
SQL> create or replace view VIEW1(na, dn, lo)
as
select e.NAME, d.DNAME, d.LOC from CONST_DEPT d, CONST_EMP e
where e.DEPTNO=d.DEPTNO
with read only;
SQL> select * from VIEW1;
4) 삭제
SQL> drop view VIEW1;
SQL> select view_name from user_views;
(6) 종류
1) 단일(SIMPLE) 뷰
-> '하나의 테이블'로 생성되는 뷰
2) 복합(COMPLEX) 뷰
-> '2개 이상의 테이블'로 생성되는 뷰
12. SEQUENCE ( 일련번호 )
(1) 설명
연속적인 숫자값을 자동으로 증감시켜 발생시키는 객체(Object)
즉, 시퀀스는 생성한 후, 호출만하면 연속적으로 번호를
(oracle 에서) 증가/감소시켜 제공해 줌
(2) 문법
create sequence 시퀀스명
[ increment by N ]
[ start with N ]
[ maxvalue N | nomaxvalue ]
[ minvalue N | nominvalue ]
[ cycle | nocycle ]
[ cache | nocache ]
(3) 생성
SQL> create sequence MYSEQ increment by 1 start with 1 nocache;
SQL> desc seq
SQL> select SEQUENCE_NAME, INCREMENT_BY, MAX_VALUE, MIN_VALUE from seq;
(4) 사용
1) NEXTVAL
SQL> select MYSEQ.NEXTVAL from DUAL;
2) CURRVAL
SQL> select MYSEQ.CURRVAL from DUAL;
cf) 생성 후 적어도 한번은 NEXTVAL을 호출해야 시퀀스값이 할당됨
(5) 삭제
SQL> drop sequence MYSEQ;
[ PART 4 - 관리 ]
1. 계정
(1) 생성
1) 만들기
SQL> conn system/java1019
SQL> create user TEST1 identified by JAVA;
2) 접근
ERR> conn TEST1/JAVA
SQL> grant CONNECT, RESOURCE to TEST1; -- CREATE VIEW
SQL> conn TEST1/JAVA
3) 확인
SQL> desc dba_users
SQL> select USERNAME, PASSWORD, DEFAULT_TABLESPACE from dba_users
where USERNAME='TEST1';
cf) select USERNAME, PASSWORD, DEFAULT_TABLESPACE from dba_users
where USERNAME='SCOTT';
(2) 수정
1) 접속
SQL> conn system/java1019
2) 비번수정
SQL> alter user TEST1 identified by JAVAC;
3) 확인
SQL> conn TEST1/JAVAC
(3) 삭제
1) 접속
SQL> conn system/java1019
2) 삭제
SQL> drop user TEST1; -- 테이블이 없는 경우에만 삭제됨
SQL> drop user TEST1 CASCADE; -- 테이블 유무에 관계없이 삭제됨
cf) 권한 제거
SQL> conn system/java1019
SQL> revoke CREATE VIEW from TEST1;
cf1) 오라클 참고 사이트 => http://www.gurubee.net/
cf2) 더 많은 권한들을 확인
select ROLE, PASSWORD_REQUIRED, AUTHENTICATION_TYPE
from dba_roles order by ROLE;
2. 백업 및 복구 ( 도스 컨솔 )
(1) 백업
1) 전체 데이터베이스 ( Full Level Export )
2) 특정 사용자 ( User Level Export ) *****
C:\>exp scott/tiger file='C:\~\dump1.dmp'
또는
C:\>exp userid=system/java1019 owner=scott file='C:\~\dump2.dmp'
3) 특정 테이블 ( Table Level Export )
(2) 복구
1) 전체 데이터베이스 ( Full Level Import )
2) 특정 사용자 ( User Level Import ) *****
C:\>imp userid=system/java1019 file='C:\SOO\Git\Oracle\3_BACKUP\dump1.dmp'
fromuser=scott touser=SCOTT2
3) 선택된 테이블 ( Table Level Import ) - 스칼라 타입에서 자주 사용하는건
number,varchar2,date,timestamp이다. - 비밀번호 만료가 되면 시스템 계정으로 들어간 뒤
conn system/java1019user를 확인하고,show user비밀번호를 바꿔준다.alter user scott identified by tiger - 다른 세션에서도 view를 보려면
select * from scott.VIEW1이런 식으로 해주면 view를 볼 수가 있다.
휘발방지