230711 Oracle SQL 12 [alter, 컬럼 감추기, 이력관리, temporary table, VIEW]
[7월 11일 점심시간 문제] 계층형 질의문 문제
SQL의 마지막 고지가 이제 눈앞에 다가왔습니다.
다음과 같이 결과를 출력할 수 있도록 SQL을 작성하세요.
결과:
이름 조직도
KING /KING(5000)
JONES /KING(5000) /JONES(2975)
SCOTT /KING(5000) /JONES(2975)/SCOTT(3000)
ADAMS /KING(5000) /JONES(2975) /SCOTT(3000) /ADAMS(1100)
: :
: :
select ename as 이름, sys_connect_by_path( ename, '/' || '(' || sal || ')' ) as 조직도
from emp
start with ename ='KING'
connect by prior empno=mgr; 원래 sys_connect_by_path 쓸때 문법
이렇게 썼을떄 /이름/이름 이렇게 나온다
(/가 맨앞에 나옴. 맨앞 / 안나오게 하려면
substr(sys_connect_by_path( ename, '/' ) , 2, 10000) substr 쓰는것!!)
select ename, sys_connect_by_path( ename, '/' ) 이름과 / 로 서열 나오게
from emp
start with ename ='KING'
connect by prior empno=mgr;추가로, 이름과 /로 서열나오게하고 + (괄호안에 sal 값) 까지
복습
SQL의 종류
- 쿼리 (Query) -> DML에 포함
- DML : insert, update, delete, merge, select
- DDL : create, alter, drop, truncate, rename (
테이블관리) - DCL : grant, revoke (
권한관리) - TCL : commit, rollback, savepoint
-> DDL, DCL : DBA의 몫, 변경사항에 대한이력관리를 잘해놔야..! 감사를 피해갈 수 있음
-> DB자동화, 감사대비 : 엑셀 html 형태로 웹페이지로 구현 -> 파이썬 for data
alter 명령어로 테이블 변경하는 방법 4가지
1. 컬럼 추가 : alter table emp
add email varcar2(50);
2. 컬럼 삭제 : alter table emp
drop column email;
3. 컬럼 변경 : alter table emp
modify ename varchar2(20);
4. 컬럼 감추기 : alter table emp
set unused column sal;- 주의사항
1) 컬럼 삭제 변경 등은 업무시간에 수행하면 안된다. -> 성능 느려짐
2) 컬럼 삭제할때는 주의해서 해야함 -> 복구가 안되기 때문에
unused 명령어로 컬럼 감추기
alter table emp
set unused column sal;사전 이용해서 감춘 컬럼 확인하기
(이름 확인 불가)
-> 이렇게 count 만 뜨기 때문에 어떤 컬럼을 감춘지를 잘 메모해놓아야한다.
예제. emp 테이블에 감춘 컬럼인 sal 을 다시 나타나게 하시오
-> 명령어 없다. 다시 나타나게 하는 방법이 없다 감추면 끝난 것!
왜 감추냐면? 컬럼 삭제를 위해서 우선 감춰놓는것 (지금 삭제하면 db성능 느려지니까)
감춘 컬럼들을 한번에 삭제하는 명령어
alter table emp
drop unused column;해서 삭제하고,
select * from user_unused_col_tabs;해서 확인! (사전 사용)
[정리]
1. 컬럼 삭제 요청이 들어오면? 업무 외 시간에 수행합니다.
2. 빨리 삭제해달라고 요청이 들어오면? 컬럼을 감추고 밤에 삭제합니다.
( + 컬럼 감췄을때는 반드시 노트합니다!)
문제 525.
emp 테이블의 job 과 mgr 컬럼을 감추시오alter table emp set unused column job; alter table emp set unused column mgr;select * from user_unused_col_tabs;같이 한번에는 안되나봄.,?
문제 526. 감춘 컬럼들을 모두 삭제하시오
alter table emp drop unused columns;select * from emp;
문제 527. (dba를 위한 tip)
감사를 대비해서 emp 테이블에 수행했던 DDL 작업들에 대한 이력을 확인하는 dictionary 를 조회하시오select * from user_objects where object_name='EMP';
-> 언제 DDL을 수행했는지 시간을 확인할 수 있음 (어떤 명령어를 수행했는지는 확인할 수 없음!)select object_name, created, last_ddl_time, timestamp from user_objects where object_name='EMP';
이력관리 테이블로 업무 활용
일 잘하는 DBA 들은 테이블 이력관리하는 테이블을 별도로 따로 만들어서 데이터를 저장하고 관리한다.
create table table_ddl_history
( t_num number(10),
ddl_date date, -> 날짜만
ddl_time timestamp, -> 시분초 저장
object_name varchar2(10),
object_type varchar2(10),
ddl_command varchar2(100),
ddl_comment varchar2(100) );insert into table_ddl_history
values (1, sysdate, systimestamp, 'emp', 'table', 'alter table emp drop column sal', '개발팀요청' ); 
-> 위와같이 테이블에 DDL이 발생할 때 마다 이력정보를 남기고 DB감사에 대비하면 된다.
rename 로 테이블명, 컬럼명 변경하기
문제 528. (테이블명 변경하기)
emp 테이블의 이름을 emp_info 로 변경하시오rename emp to emp_info; (변경전이름) (변경후이름) select * from emp; -> 이름이 바뀌었으니 안보임! select * from emp_info; ->보임!
문제 529. 다시 emp_info 를 emp 로 이름을 변경하시오
rename emp_info to emp;
※ raname 도 DDL 명령어이므로 수행되면서 commit 된다! (rollback 안됨)
문제 530. (컬럼명 변경하기)
emp 테이블의 sal 컬럼명을 salary 로 변경하시오alter table emp rename column sal to salary;
문제 531. (컬럼명 변경하기)
emp 테이블의 job 을 job_id 로 변경하시오alter table emp rename column job to job_id;
테이블 스크립트 저장해놓고 사용하기
1) 명령프롬포트 접속 sqlplus c##scott/tiger
2) ed init_emp(스크립트 이름 지정).sql (ed)
3) @init_emp.sql (@)
복구하는 스크립트 등등 저장해놓고 사용하는법임!
터미널창에서 작업하는것은 DBA가 주로 함..!
094. 임시테이블 생성하기 (CREATE TEMPORARY TABLE)
- 영구히 데이터를 저장할 수 있는 테이블 : heap table , Permanent Tablespace (일반적인 테이블 스페이스)
-> USERS, SYSAUX, SYSTEM, EXAMPLES 가 해당된다. - 임시로 데이터를 저장할 수 있는 테이블 : temporary table (임시 테이블)
왜 임시테이블이 필요한가?
1) 지금 잠깐 볼 데이터이고 영구히 저장할 필요는 없는 데이터
2) 임시 테이블을 permanent tablespace 에 데이터를 저장하지 않고
temporary tablespace 에 데이터를 저장하므로
permanent tablespace의 디스크를 사용하지 않아도 됩니다.
tablespace 테이블스페이스 사용량 조회 쿼리문 (공간관리)
select t.tablespace_name,
( (t.total_size - f.free_size) / t.total_size) * 100 usedspace
from (select tablespace_name, sum(bytes)/1024/1024 total_size
from dba_data_files
group by tablespace_name) t,
(select tablespace_name, sum(bytes)/1024/1024 free_size
from dba_free_space
group by tablespace_name) f
where t.tablespace_name = f.tablespace_name(+);
테이블스페이스 공간 늘리는 방법
(응급조치) system 테이블 스페이스의 공간을 늘립니다
1. 테이블 스페이스와 관련된 파일명을 확인합니다
select tablespace_name, file_name
from dba_data_files; 
- system tablespace 의 공간을 추가합니다.
검색된 것은 C:\APP\ITWILL\PRODUCT\18.0.0\ORADATA\XE\SYSTEM01.DBF 이나, 02로 이름 변경하여 공간 확보해주는 것!alter tablespace system add datafile 'C:\APP\ITWILL\PRODUCT\18.0.0\ORADATA\XE\SYSTEM02.DBF' size 500m;
문제 532.
emp 와 dept 테이블이 어느 테이블 스페이스에 저장되어 있는지 확인하시오select table_name, tablespace_name from user_tables where table_name in ('EMP','DEPT');
설명 : USERS 라는 영구히 데이터를 저장할 수 있는 테이블 스페이스에 있으므로,
데이터를 지우지 않는 이상 계속 저장됩니다. (Permanent Tablespace 에 속함)
※ DB의 공간관리는 DBA의 주 업무이므로 테이블이 생성될 때
이 테이블을 영구히 저장할 테이블로 생성할지,
아니면 임시로 데이터를 저장할 테이블로 생성할지를 잘 결정할 수 있어야한다.
임시테이블 스페이스 사용량 조회 쿼리문
SELECT TABLESPACE_NAME
,ROUND(ALLOCATED_SPACE/1024/1042/1024,2) AS ALLOC_GB
,ROUND((ALLOCATED_SPACE-FREE_SPACE)/1024/1024/1024,2) AS USED_GB
,ROUND(FREE_SPACE/1024/1024/1024,2) AS FREE_GB
,ROUND((ALLOCATED_SPACE-FREE_SPACE)/ALLOCATED_SPACE*100,2) AS "USED(%)"
FROM DBA_TEMP_FREE_SPACE;문제 533.
임시 테이블 스페이스의 여유공간이 얼마나 있는지 확인하시오
used가 90% 되면 성능 느려짐
-> 리눅스쉘이나 파이썬으로 알림울리게 자동화 쉘 가지고 있으면 됨.
※ 여유공간이 10% 밖에 안남았으면 곧 성능이 느려질 것이므로 공간 추가할 대비를 해야한다.
이 공간은 정렬을 위한 작업을 할 때 주로 사용되고, 정렬 작업이 끝나면 공간이 자동 회수 된다.
지금부터 만들 임시 테이블은 바로 이 공간에 만들어진다.
임시테이블 스페이스에 저장되는 데이터
- 정렬 작업을 위한 SQL의 데이터
- 임시 테이블의 데이터
- with 절의 임시 데이터
임시테이블(temporary table)의 종류 2가지
- commit 할때 까지만 데이터를 보관하는 테이블
관련 옵션 :on commit delete rows - 세션을 종료할때 까지만 데이터를 보관하는 테이블 (☆이걸 많이씀!)
관련 옵션 :on commit preserve rows
ex) sk telecom의 임시테이블 : 매월 핸드폰 요금 계산하는 프로그램의 당월 핸드폰 사용 데이터 (이번달것만 보기위해!)
on commit delete rows
<문법>
create global temporary table emp_temp2
( empno number(10),
ename varchar2(20),
sal number(10) )
on commit delete rows; insert into emp_temp2
select empno, ename, sal
from emp;
commit 하고나서 다시 보면 안보임!! -> commit 전까지만 보관하는 임시테이블
명령프롬포트에서 다른 세션을 하나 더 열고 emp_temp2 를 조회하게 되면 데이터 볼 수 없다.
on delete commit rows 옵션을 써서 만든 temporary table 은 내 세션에서만 데이터를 볼 수 있고 다른 세션에서는 같은 테이블의 데이터를 볼 수 없다.
on commit preserve rows
예제.
1) on commit preserve rows 로 임시테이블을 생성합니다.
create global temporary table emp_temp3
( empno number(10),
ename varchar2(10),
sal number(10) )
on commit preserve rows;2) emp_temp3 테이블에 emp 테이블의 데이터를 입력하시오
insert into emp_temp3(empno, ename, sal)
select empno, ename, sal
from emp;3) commit 하고 데이터를 조회하시오
조회된다. exit 쳐서 세션을 종료시키야만 종료됨
4) 다른 세션에서 emp_temp3 데이터를 조회하시오
다른 세션에서는 역시 조회 안된다!
5) 지금 원래 세션에서 exit 명령어 수행하고 나갔다가 다시 접속해서 emp_temp3 를 조회하시오
오라클 껐다가 다시 켜서 조회했더니 조회안된다!
※ 임시 테이블의 데이터는 임시로 데이터를 저장하는데,
temporary tablespace 에 저장을 합니다.
commit 하거나 session 을 종료하면 데이터가 지워집니다.
※ 임시 테이블의 데이터는 내 세션에서만 볼 수 있고 다른 세션에서는 볼 수 없습니다.
undo data -> 취소 데이터
문제 534. emp_temp2 테이블이 어느 테이블 스페이스에 저장되었는지 확인하시오
select table_name, tablespace_name from dba_tables where table_name ='EMP_TEMP2';
'tablespace_name' 이 null 로 나온다. 데이터가 비어있는 상태의 emp_temp2 임시 테이블에 어떤 공간도 할당되지 않은것 (테이블 구조는 있다.)
문제 535. 임시 테이블에 공간이 할당되는 시점이 언제인지 테스트하시오
insert into emp_temp3(empno, ename, sal) select empno, ename, sal from emp; XXXXXXXXXXXXXXXXXX설명 : 데이터를 입력하고 테이블의 사이즈가 늘어나는지를 확인하면서,
공간이 할당되는지 확인을 다시 해야합니다.
데이터를 입력하는 순간에 할당 되는 것이고 그 전에는 할당되지 않습니다.
(OCP문제)
답 A,D,E
(테스트 하면서 문제 풀어야함)
095. 복잡한 쿼리를 단순하게 하기 1(VIEW)
Oracle 의 database 오브젝트(object) 5개 ?
- table : 행(row)과 컬럼(column)으로 이루어진 기본 데이터 저장소
- view : 데이터를 저장하지는 않고, 테이블의 데이터를 볼 수 있게 해주는 db object
- index
- sequence
- synonym
- view 를 왜 사용하는가?
1) 보안상의 이유로 (특정 컬럼을 노출하지 않고 데이터를 엑세스 할 수 있게 하려고)
2) 복잡한 쿼리문을 심플하게 작성하기 위해서
ex ) emp 테이블을 공개를 해야 개발자들이 개발을 할 수 있는데, sal 을 노출하지 않게 하고 싶다면 view 로 만들면 된다.
create view emp_view
as
select empno, ename, job, mgr, hiredate, deptno
from emp;
select * from emp_view;
월급과 커미션이 안보이는 테이블이 출력되었다.
문제 536.
emp_view 의 데이터를 update 하는데, KING의 직업을 SALESMAN 으로 변경하세요update emp_view set job ='SALESMAN' where ename = 'KING' ;
emp 테이블 자체에서도 변경되었다. view 를 업데이트 했다는건 emp를 업데이트 했다는 것이다. (?)
※ view 는 데이터를 저장하지 않고 그냥 테이블을 바라보는 '쿼리문' 이다.
emp_view 를 업데이트 했지만 실제로는 emp 테이블이 update 되는 것이다.
월급같은거 보면 안되니까 사용하는것뿐!
문제 537.
아래의 update 문장의 실행계획을 확인하시오explain plan for update emp_view set job='SALESMAN' where ename='KING'; select * from table(dbms_xplan.display);
emp_view 를 업데이트했지만 emp를 업데이트했다는 것을 확인 할 수 있다.
특정 행만 선택해서 view 만들기
문제 538.
직업이 ANALYST, SALESMAN, CLERK 인 사원들의 모든 컬럼으로
emp_view2 를 생성하시오 (보안상의 이유로 president 와 manager는 데이터를 노출하면 안될 때)create or replace view emp_view2 -> or replace 쓰게 되면 'emp_view2' 가 있다면, 바꿔버린다. as select * from emp where job in ('ANALYST','SALESMAN','CLERK'); select * from emp_view2;
문제 539.
emp_view2 의 데이터를 변경하는데 FORD의 월급을 0으로 변경하시오update emp_view2 set sal=0 where ename='FORD'; select * from emp;
VIEW 의 종류 2가지
-
복잡한 쿼리를 단순하게 검색하기 위해서 view 를 생성하는데,
이걸 이해하려면 view 의 종류를 알아야한다.단순 view 복합 view테이블 갯수 1개 2개 이상
Group함수 또는 포함안함 포함
Group by 절DML 여부 가능 불가능 할 수도 있다
(데이터 수정여부)
JOIN 으로 만든 VIEW
문제 540. -> 복합 view
이름, 월급, 부서위치를 출력하는 view 를 emp_dept 로 생성하시오create or replace view emp_dept as select e.ename, e.sal, d.loc from emp e, dept d where e.deptno = d.deptno ; select * from emp_dept;
문제 541.
emp_dept(view)에서 KING 의 부서위치를 필라델피아로 변경하세요update emp_dept set loc='Philadelphia' where ename='KING';KING 의 loc 인 new york 으로 데이터가 있는데,
new york 이 loc 인 다른 행들이 또 있기 때문에 안됨
dept 테이블쪽의 데이터가 update 안된다. (?) 이유맞나
문제 542. emp_dept view 에서 KING의 월급을 9000으로 변경하시오
update emp_dept set sal=9000 where ename='KING';같은 오류 발생~! 조인하면서 발생한 문제이다. emp 테이블에서만 변경했으면 업데이트 가능
dept table 에 제약을 거는것 (null 값, 중복값 등 없애게 까다롭게 관리할수 있는 제약들을 거는것)
>
alter table dept
add constraint dept_pk primary key(deptno); 입력 한 후에 하면 업데이트 된다. 중복데이터가 없다는걸 보장해주는 제약을 설정했으니 오라클에서 업데이트 시켜준다.
※ join 으로 만든 view 는 update 가 m 쪽에 해당하는 테이블의 데이터는 업데이트가 되고, 1쪽에 해당하는 테이블의 데이터는 update 가 안된다.
dept emp
1 : m dept는 deptno 가 10,20,30,40 하나씩 있고,,? emp 는 deptno 가 많아서..? 일 대 다 의 관계..? 뭔 개소리야ㅠ
일단 많은쪽은 업데이트가 되고 적은쪽이 안된다고 생각하자
문제 543. (복습문제)
직업, 직업별 토탈월급을 출력하시오select job, sum(sal) from emp group by job;
문제 544. 위의 결과를 출력하는 view를 jom_sum 이라는 이름으로 생성하시오 (복합view)
jom 으로 오타났음 걍 하자ㅜcreate or replace view jom_sum as select job, sum(sal) from emp group by job;
라고 하면 에러난다.create or replace view jom_sum as select job, sum(sal) as sumsal -> 컬럼별칭 from emp group by job;컬럼별칭을 써야 에러가 안난다.
복합 view 의 업데이트?
문제 545.
job_sum 뷰에서 직업이 CLERK의 sumsal 을 7000으로 변경하시오update jom_sum set sal=7000 where job='CLERK';
emp 테이블에서도 변경이 되어야하는데, 그럼 값이 잘못되니까.. 안바뀜
복합 view는 거의 변경이 안된다고 보는게 맞음.
※ group by 절을 사용해서 만든 위의 복합뷰는, 데이터를 update 할 수 없다.
문제 546.
위의 job_sum 뷰 를 delete 로 지우시오delete from jom_sum;SQL 오류: ORA-01732: 뷰에 대한 데이터 조작이 부적합합니다
※ 복합뷰는 update, insert, delete 다 안된다.
※ group by 절을 사용해서 만든 위의 복합뷰는 data를 delete 할 수 없다.
VIEW 를 사용하면? 간단하게 표현가능
select ename, loc from emp_dept where loc='DALLAS';VIEW 를 사용안하면? 조인하여 표현해야함
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno and d.loc='DALLAS';VIEW 가 필요한 이유
- 보안상의 이유
- 복잡한 쿼리문을 간단하게 검색하기 위해서
- 특정 DATA를 갱신하지 못하게 할 때, 또는 전체 DATA의 DML 여부를 막게 하고 싶을때
VIEW 의 옵션 2가지
with check option: 특정 데이터를 갱신 못하게 할 때with read only: 모든 데이터에 대해서 DML을 막고 싶을 때 (insert, update, delete, merge, select)
ex. 이마트 신세계 백화점 카드 개발할 때 PM이 요청한 내용?
백화점 카드 포인트 적립 기준 테이블에 2% 5% 등 적립율이 입력되어있는데,
이 테이블에 절대 DML 못하게 막아라.
-> view
with read only
create view emp77
as
select *
from emp
with read only;
select * from emp77;-> insert 같은 DML 작업 하려고 했을때, 읽기전용뷰에서는 DML작업할 수 없다고 뜸!!
문제 547.
부서번호가 10,20번인 사원들의 모든 컬럼을 가져오는 view 를 생성하시오create or replace view emp547 as select * from emp where deptno in (10,20) ;
문제 548.
emp547 view를 수정하는데, KING의 월급을 8400으로 수정하시오update emp547 set sal=8400 where ename='KING';단순 뷰라서 업데이트 가능함!!
문제 549.
emp547 view를 수정하는데, KING의 부서번호를 30번으로 수정하시오update emp547 set deptno=30 where ename='KING';이것도 변경 완료
-> 그런데 이렇게 변경하고 났더니, KING 은 사라진다!!
emp547은 부서번호 10,20만 볼 수 있기 때문이다.
= update 는 가능하지만 emp547 에서는 볼 수 없게 된다.
with check option
문제 550.
emp547 뷰를 다시 만드는데 deptno 만큼은 갱신되지 못하게끔 뷰를 생성하시오
(뷰 생성시 where 절 조건에 위배되게끔 뷰를 갱신 못하게 하시오) (?)create or replace view emp547 as select * from emp where deptno in (10,20) with check option;
where 절에 기술한 '컬럼' 만큼은 변경 안되게!
문제 551. (오늘의 마지막 문제)
월급이 4000 이하인 사원들의 모든 컬럼을 담는 VIEW 를 emp551 로 생성하는데,
emp551에서 월급을 4000보다 크게 갱신하지 못하도록 view 를 생성하시오create or replace view emp551 as select * from emp where sal <= 4000 with check option; select * from emp551;update emp551 set sal = 5000 where ename='BLAKE';
