13-2. DATABASE OBJECT(임시테이블, 외부테이블, flashback)

■기타 데이터베이스 객체

■12장. 기타 데이터베이스 객체

*12장 목차

1. 임시 테이블

2. 외부 테이블

3. flashback 기술

 

 

 

■임시 테이블

 "데이터를 영구히 저장하지 않고

 임시로 저장할 때 사용하는 테이블"

 

*임시 테이블  종류 2가지

1. on commit delete row : 

                              commit 을 하면 데이터가 사라진다.

 

 

2. on commit preserve rows:

                                                   session 이 종료가 되면

                             데이터가 사라진다.

 

예:

 지금 잠깐 테이블 분석을 위해 사용할 테이블 인데

 나중에 필요없는 데이터인 경우에 사용하기 유용하다.

 

 

 

 

■ on commit delete rows 테스트

 

create global temporary table emp_temp1

 ( empno number(10),

  ename varchar2(20),

  sal number(10) )

 on commit delete rows;

 

 insert into emp_temp1

 values (1111, 'SCOTT', 3000);

 

insert into emp_temp1

 values (2222, 'ALLEN', 3000);

 

insert into emp_temp1

 values (3333, 'JONES', 3000);

 

 

 select * from emp_temp1;

 

 

commit;

 

select * from emp_temp1;

 

커밋이후로 표시할 데이터가 없는걸 확인할 수 있다.

영어 뜻 그대로 커밋 할 때 지워라.on commit delete rows

 

 

 

■ on commit preserve rows 테스트

 

create global temporary table emp_temp2

 ( empno number(10),

  ename varchar2(20),

  sal number(10) )

 on commit preserve rows;

 

 

insert into emp_temp2

 values (1111, 'SCOTT', 3000);

 

insert into emp_temp2

 values (2222, 'ALLEN', 3000);

 

insert into emp_temp2

 values (3333, 'JONES', 3000);

 

 

 select * from emp_temp2;

 

 

commit;

 

select * from emp_temp2;

 

커밋이후 데이터가 남아있는걸 볼 수 있다.

 

exit;

정상종료 하고

다시 접속해서 들어가서 select * 해보면

없다아앙

 

 

 

 

 

■외부 테이블 external table

보고자 하는 엑셀파일을 데이터베이스에 안 넣고

링크만 걸어놓는다.(위치만 알려준다)

select 하면 출력된다.

 

 

External Table 실습예제

 

1. emp1.txt 편집(c:\emp1.txt)

SMITH,101,2001/03/15

JOHN,102,2002/04/15

SMITH,101,2001/03/15

JOHN,102,2002/04/15

SMITH,101,2001/03/15

JOHN,102,2002/04/15

SMITH,101,2001/03/15

JOHN,102,2002/04/15

SMITH,101,2001/03/15

JOHN,102,2002/04/15

SMITH,101,2001/03/15

JOHN,102,2002/04/15

**행을 엔터로 구분하고

 컴마로 컬럼을 구분한다.

 

 

**c 드라이브에 안들어가서

c에 폴더 aaa 만들고(꼭 영어이름)

거기에 텍스트 파일 저장. (emp1 로)

 

2. Directory 생성

 

SQL> connect   scott/tiger

 

SQL> create directory emp_dir2 as 'c:\\aaa\\';

                        ↑            ↑

                    디렉토리  이름  디렉토리 위치

 

 

 

 

3. External table 생성

 

 SQL>

      create table ext_emp2

         (emp_id  number(3),

           emp_name varchar2(10),

           hiredate  date)

           organization external

          (type oracle_loader           ---->외부 테이블 엔진을 결정

           default directory emp_dir2 --> 디렉토리가 emp_dir 이다

           access parameters

           (records delimited by newline  --->행을 엔터로 구분

            fields terminated by ","    --------->컬럼을 , 로 구분

           (emp_name char,        --------------> emp_name 은 문자형

            emp_id char,           ----------------> emp_id 는 문자형

            hiredate date "yyyy/mm/dd") )  ---->hiredate 는 날짜형

            location ('emp1.txt') );      ------------>외부 텍스트 파일 이름

      >>>문법들

 

 

4. Select & DML 테스트

 

select * from ext_emp2;

 

안됐던 사람들

F10 -도구-

 

 

 

 

 

문제473.

저번 기수 학생들 데이터(emp3.csv) 파일을 링크 걸어서

쿼리 할 외부 테이블을 생성 하시오~

 

csv 파일 emp3.csv

>>메모장으로 옮김옮김

 

c 드라이브-aaa 파일 저장- emp3.exe

 

***컬럼 구분을 컴마로 다 수정해줌.

 

 

디렉토리 생성

(스캇 연결하고)

create directory emp_dir3 as 'c:\\aaa\\';

 

 

 

external 테이블 생성

 

create table ext_emp3

         (empno number(10),

           ename varchar2(10),

           age NUMBER(10),

           birth DATE,

           major VARCHAR2(40),

           email VARCHAR2(100),

           mobile VARCHAR2(20),

           address VARCHAR2(400),

           telecom VARCHAR2(10))

           organization external

          (type oracle_loader

           default directory emp_dir3

           access parameters

           (records delimited by newline

            fields terminated by ","

           (empno char,

           ename char,

            age CHAR,

            birth date "YYYY/MM/DD",

            major char,

            email CHAR,

            mobile CHAR,

            address CHAR,

            telecom CHAR

            ) )

            location ('emp3.txt') );

 

COMMIT;

 

SELECT * FROM ext_emp3;

 

 

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

엑셀 파일-오른쪽버튼-연결프로그램-메모장-

UTF-8 로 저장

 

http://nuli.navercorp.com/sharing/blog/post/1079940

( 컴퓨터 문자에 관한 참고사이트)

 

■ 문자 집합

1. 단일 바이트 문자집합(7 bit, 8 bit) ---> 영문

2. 유니코드 문자집합 (UTF-8)   ---------> 한글, 한자, 중국어,

                                                    일본어를 담는

                                                    문자집합

즉, 영문은 안 깨지는데

UTF-8 로 인코딩 변환해줘야 안 깨지고 저장이 된다

 

 

 

문제474.

전기수 학생들 데이터인 외부 테이블 ext_emp3 로

view 를 생성하는데

이름, 나이, 전공만 출력하는 view 를

ext_view 라는 이름으로 생성하시오

 

create view ext_view

as

 select ename, age, major

   from ext_emp3;

 

select * from ext_view;

 

 

 

 

 

■외부 테이블로 가능한 작업들

1. view 생성 가능

2. 다른 테이블과 조인이 가능

 

 

 

문제475.

우리반(emp2) 테이블과 전기수 학생들 테이블(ext_emp3)을

통신사를 연결고리 컬럼으로 해서

조인해서

8기학생 이름, 8기학생 나이, 7기학생 이름, 7기학생 나이를

출력하시오.

 

select e2.ename, e2.age, ex3.ename, ex3.age

from emp2 e2, ext_emp3 ex3

where e2.telecom = ex3.telecom;

 

 

 

■외부 테이블로 가능한 작업들

1. view 생성 가능

2. 다른 테이블과 조인이 가능

 

■외부 테이블의 단점

1. 인덱스가 안 만들어진다.

 

 

문제476.

전기수 학생들 테이블인 ext_emp3 의 ename 에

 인덱스를 생성하시오

 

create index ext_emp3_ename

on ext_emp3(ename);

 

에러뜬다아!!!!!!

 

하지만 db에 넣어서 작업하면 좀 더 시간이 걸리기 때문에

index 를 포기하고(효율적인 셀렉)

그냥 external table 을 이용하는 경우가 많다.

 

 

 

 

FLASHBACK 은 오라클 12C 가 설치되야 할 수 있어서

뒤로 조금 밀려남

 

오라클 12 셋업

셋업파일 관리자권한으로 실행

이멜 안쓰고 다음~다음다음다음~~~

사용자선택에서  windows 내장 계정 사용

 

 

■데이터 딕셔너리를 사용한 객체 관리

■14장. 데이터 딕셔너리를 사용한 객체 관리

 

"내가 가지고 있는 테이블이나 인덱스가 뭐가 있는지

 확인하고 위해"

 

*내가 가지고 있는 테이블 리스트 조회

 

select table_name

      from user_tables;

 

 

*데이터 베이스에 있는 모든 데이터 베이스 조회하기

select table_name

 from dba_tables;

                                  1732개 있음;;;

 

 

 

 

*데이터 딕셔너리의 종류 3가지

 

1. user_xxx  : 내가 만들고 내가 소유한 객체들을 

                      확인

 

2. all_xxx :     내가 만든 객체 + 다른 유저가

                       권한 준 객체

 

3. dba_xxx:    데이터베이스에 있는 모든 객체

 

 

* database 의 객체(object) 5가지

 

1. table           : user_tables, all_tables, dba_tables

 

2. view            : user_views, all_views, dba_views

 

3. index          : user_indexes, all_indexes, dba_indexes

 

4. sequence  : user_sequences, all_sequences,                     

                     dba_sequences

 

5. synonym  : user_synonyms, all_synonyms,

                     dba_synonyms

 

 

 

*데이터 분석을 하려면

테이블 정의서를 확인해야 한다.

  테이블 정의서는 테이블 생성할 때 이 테이블에 대해 설명해

    놓은 문서이다.

 

select *

from user_tab_comments;

 

 

*emp 테이블에 대해서

데이터 분석을 하기 용이하도록

emp 테이블에 대한 정보를 database 에

기록하시오 !

 

comment on table emp is

 '사원에 대한 정보가 있는 테이블로서

사원번호, 이름, 월급, 직업, 커미션, 입사일, 관리자번호, 부서번호에 대한 정보가 있습니다';

 

select *

from user_tab_comments;

 

 

 

문제477.

emp2 테이블에 대한 테이블 정보를

데이터 베이스에 저장하시오.

 

comment on table emp2 is

'우리반 학생들에 대한 정보가 있는 테이블로서

 이름, 나이, 생년월일, 전공, 이메일, 텔레콤, 주소에 대한

정보가 있습니다' ;

 

select *

from user_tab_comments;

 

 

 

 

문제478.

사원테이블의 컬럼에 대한 주석도 남기시오!

 

comment on column emp.empno is

 '사원번호 입니다' ;

 

select * from user_col_comments

where table_name = 'EMP';

 

 

 

■15장. 대형 데이터 집합 조작

*목자

1. insert  문에서의 서브쿼리

2. 다중 insert 문 4가지

3. merge 문

4. flashback Query

 

 

■ insert 문에서의 서브쿼리

 

insert into emp(empno, ename, sal)  <<<<----서브쿼리 가능

 values(1111, 'scott', 3000) ;            <<<<<<---서브쿼리 가능

 

insert into (select empno, ename, sal, deptno

                    from emp

                    where deptno = 10

                    with check option )

values (&empno, '&ename', &sal, &deptno);

 

도스창에서 실행 할 것.

위에 쿼리 돌리면

사원번호, 이름, 월급, 부서번호 물어보는 질문뜸.

 

 

 

*scott 접속 안되는 사람들은 이렇게 명령어 칠 것.

 

set ORACLE_SID = xe

 

sqlplus scott/tiger

 

 

 

*xe 이름 확인하기

select instance_name

from v$instance;

 

■flashback 기술

 

*xe 이름 확인하기

select instance_name

from v$instance;

 

 

 

insert into (select empno, ename, sal, deptno

                    from emp

                    where deptno = 10

                    with check option )

values (&empno, '&ename', &sal, &deptno);

 

 

물어보는 값 다 입력하고

/ 엔터 하면

또 물어본다 ㅋㅋ

 

그래서 다른 값들 집어넣으면

안된다

 

 

문제479.

위의 insert 문을 다시 작성하는데 월급이 0 ~ 9000 사이의 데이터만 입력되게끔 insert 문을 작성하시오

 

insert into (select empno, ename, sal, deptno

                  from emp

                  where sal between 0 and 9000

                    with check option)

 values(&empno, '&ename', &sal, &deptno);

 

 

 

 

■ flashback query

*새로운 데이터베이스로 접속을 하는 방법

 

ORACLE_SID=orcl

 

sqlplus / as sysdba

 

select instance_name

 from v$instance;

 

그러면 orcl 뜸.

 

 

 

문제480.(오늘의 마지막 문제)

7기 학생들이 나이의 평균과

8기 학생들의 나이의 평균을

아래와 같이 출력하시오

 

기수      나이평균값

7기           27

8기           28

 

 

 

select '7기' as 기수,

        round(avg(age)) as 나이평균값

from ext_emp3

union

select '8기' as 기수,

       round(avg(age)) as 나이평균값

from emp2;

 

 

 

■ 오라클12 로 접속

1. 도스창을 연다

2. 아래의 명령어를 날린다.

set ORACLE_SID=orcl

 

lsnrctl status

 

 

3. 리스너의 상태를 확인한다.

       ↓

 데이터 베이스 접속할 때 통과해야 하는 데몬

*데몬이.... 건물의 경비원 아저씨를 상태하는 거...랄까

 

이러면 경비원 아저씨 잘 살아계신거임.....ㅎ

 

4. 오라클에 sys 유저로 접속을 한다.

c:> sqlplus / as sysdba

       show user

 

5. 12c 의 데이터 베이스가 무엇이 있는지 확인

select name, pdb

   from  v$services

  order by name;

 

***pdborcl 있나 확인할 것

 

 

 

화면 캡처: 2018-11-02 오전 9:55

 

 

6. 어느 데이터베이스로 접속이 되어있는지 확인

show con_name

 

CDB$ROOT 라고 나옴

루트(뿌리)가 되는 데이터베이스 라는 말이다.

 

 

7. pdborcl 데이터베이스를 사용하겠다라고 설정

 

alter session set container=pdborcl;

그리고

show con_name

그러면 pdborcl 로 이름이 바뀌었을 겁니다

 

 

8. scott 유저의 lock 을 해제하고

패스워드를 tiger 로 변경한다

 

alter user scott

 account unlock;

 

alter user scott

 identified by tiger;

 

개방되지 않는다고 에러어 뜹니다아

그러면!!!!!

select instance_name, status

 from v$instance;

 

alter database pdborcl open;

 

 

 

화면 캡처: 2018-11-02 오전 10:06

 

 

 

9. pdborcl 에 sys 유저로 접속한다

connect sys/oracle@localhost:1522/pdborcl as sysdba

                                        ↓         ↓       ↓

                               아이피주소   포트번호  디비이름  

 

 

10. 현재 접속한 데이터 베이스가 open 인지 확인

select instance_name, status

 from v$instance;

 

11. pdborcl 데이터 베이스를 올린다.

alter database pdborcl open;

이미 개방됬다고 뜸 ㅎ

 

12. 이제 scott 연겨얼

 

connect scott/tiger@localhost:1522/pdborcl

***sys 로 접속했던거 그대로 복붙하면 안되에엥

 

select * from dept

 

 

13. 이제 오라클게이트 키고

포트 1522(원래거는 1521)

ex 를 pdborcl 로 바꾸고 접속

 

 

 

 

■ 데이터 분석가에게 필요한 flshback 기술 정리

 "데이터를 과거로 되돌리는 기능"

1. flashback drop

2. flashback query

3. flashback table

 

 

■ flashback drop

 

drop table emp;

*휴지통 속에 emp 가 이는지 확인하시오

show recyclebin;

     **cmd 에서만 된다

 

이제 휴지통에서 복원작업

flashback table emp to before drop;

 

 

문제481.

emp 테이블을 ctas로 백업하시오

 

create table emp_backup

as

 select * from emp;

 

 

■ -flashback query

 " 과거의 데이터를 확인하는 커리문"

 

delete from emp;

commit;

이제 재빨리!!!!!!!!!!!!!!!!골든타임 안에!!!!!

select *

 from emp

 as of timestamp to_timestamp('2018/11/02:10:30:00',

                                                          'RRRR/MM/DD:HH24:MI:SS');

 

내가 지웠던 시점으로 timestamp 먹임.

 

어라 시간 한참 지났는데도

계속 조회되네용

왜냐면요

혼자 쓰는 거라서 그렇데요.

 

 

 

■-flashback table

1. table 을 flashback table 이 가능하도록 설정한다.

 

alter table emp enable row movement;

 

 

2.위에서 확인한 flashback query 시간대로

테이블을 되돌린다.

 

flashback table emp to timestamp

 to_timestamp('2018/11/02:10:30:00',

                               'RRRR/MM/DD:HH24:MI:SS');

 

**위에 alter 를 해줘서

2번의 명령어로 플래시백 된거임.

 

3. flashback 이 잘 되었는지 확인한다.

 

select * from emp;

 

commit;

 

 

 

 

문제482.

dept 테이블과 salgrade 테이블을 delete 하고

commit 하시오.

 

delete from dept;

delete from salgrade;

commit;

 

■ -오라클의 데이터를 삭제하는 방법 3가지

               delete           truncate           drop

1. 데이터    삭제              삭제               삭제

2. 저장공간  유지             삭제                삭제

3. 저장구조  유지             유지                삭제

4. 롤백유무  가능             불가능             불가능

5. flachback    가능             불가능             가능

     유무

 

 

 

문제483.

dept 테이블과 salgrade 테이블을

flashback 해서 복구하시오!

 

 

alter table dept enable row movement;

 

flashback table dept on timestamp

to_timestamp('2018/11/02:11:17:00','RRRR/MM/DD:HH24:MI:SS');

 

언더바임

 

alter table salgrade enable row movement;

 

flashback table salgrade on timestamp

to_timestamp('2018/11/02:11:17:00','RRRR/MM/DD:HH24:MI:SS');

 

 

commit;

 

 

*골든타임 확인하는 방법

 

connect sys/oracle@localhost:1522/pdborcl as sysdba;

 

show parameter undo_retention;

 

value 가 900 나오는데

900초를 뜻함. 즉 15분

 

grant dba to scott;

 

alter user sh

 account unlock;

 

alter user sh

 identified by sh;

 

alter user hr

 account unlock;

 

alter user hr

 identified by hr;

 

 

골든타임 바꾸는것도 가능

connect / as sysdba

 

alter system set undo_retention=2000;  (2000초)

 

이제~

 

exit;

 

set ORACLE_SID=xe  (오라클 11로 접속)

 

sqlplus scott/tiger