오늘의 TIL
- 컬럼 감추기(unused)
- 임시 테이블 생성하기(CREATE TEMPORAY TABLE)
- 임시 테이블의 종류 2가지
- 복잡한 쿼리를 단순하게 하기 (VIEW)
- 단순view, 복잡view
- veiw의 옵션 2가지
복습
SQL의 종류
1. Query
2. DML : insert, update, delete, merge, select
3. DDL : create, alter , drop, truncate, rename
4. DCL : grant, revoke
5. TCL : commit, rollback, savepoint엑셀, html 형태로 웹페이지로 구현 <--- 파이썬 for dba
- alter 명령어로 테이블 변경하는 방법 4가지
- 컬럼 추가 : alter table emp
add email varchar2(50);- 컬럼 삭제 : alter table emp
drop column email;- 컬럼 변경 : alter table emp
modify ename varchar2(20);
점심시간 문제
select ename , sys_connect_by_path(ename || '(' || sal || ')' , '/' ) 조직도 from emp start with ename ='KING' connect by prior empno = mgr;
컬럼 감추기(unused)
alter table emp set unused column sal;
감춘 컬럼 확인하기
select * from dictionary where table_name like '%UNUSED%'; ------------------ select * from user_unused_col_tabs;
예제. 감춘 컬럼들을 한번에 삭제하는 명령어
alter table emp drop unused columns;
업무 프로세스 !!
1. 컬럼 삭제 요청이 들어오면 - 업무 외 시간에 수행
2. 빨리 삭제해달라고 하면 - 컬럼을 감추고 밤에 삭제(컬럼을 감췄으면 반드시 노트(달력)에 잘 적어놓기!
문제 525. emp테이블의 job, mgr 컬럼 감추기
alter table emp set unused column job; alter table emp set unused column mgr;
문제 527. (dba를 위한 tip) 감사를 대비해서 emp 테이블에 수행했던 ddl 작업들에 대한 이력을 확인하는 딕셔너리를 조회하시오
select object_name, created, last_ddl_time, timestamp from user_objects where object_name='EMP';
위와같이 테이블에 DDL이 발생할 때 마다 이력정보를 남기고 DB감사에 대비하면 됩니다.
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', '개발팀 요청');
문제 528. emp 테이블의 이름을 emp_info로 변경
rename emp to emp_info; select * from emp; select * from emp_info;
문제 529. 위 변경사항 다시 이름emp로
rename emp_info to emp;
- rename도 DDL명령어라서 수행되면서 commit;된다
문제 530.(컬럼명 변경) emp 테이블의 sal 컬럼명을 salary로 변경
alter table emp rename column sal to salay; select * from emp;
문제 531. emp테이블의 job -> job_id로 변경
alter table emp rename column job to job_id; select * from emp;
스크립트 저장방법
ed init_emp.sqled 뒤에 파일명생성! 으로 메모장 생성후 스크립트 붙여넣고 저장!@하고 돌리면 알아서 내가 저장했던 스크립트 돌아간다!
임시 테이블 생성하기(CREATE TEMPORAY TABLE)
- 영구히 데이터를 저장할 수 있는 테이블 : heap table
- 임시로 데이터를 저장할 수 있는 테이블 : tempoary table
- 왜 임시로 저장하는 테이블이 필요한가?
지금 잠깐 볼 데이터, 영구히 저장할 필요는 없는 데이터인 경우- 임시 테이블을 permanent tablespace 에 데이터를 저장하지 않고 temporary tablespace 에 데이터를 저장하므로
permanent 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(+);
- users에 emp, dept가 있다.
지금 엄청 차있다!!
응급조치
1. 테이블 스페이스와 관련된 파일명 확인select tablespace_name, file_name from dba_data_files;
왼쪽 컬럼은 논리적이름, 오른쪽은 물리적 파일명!
저 주소따라가면SYSTEM01을 찾을 수 있다.
- system tablestpace에 공간을 추가
alter tablespace system add datafile 'C:\APP\ITWILL\PRODUCT\18.0.0\ORADATA\XE\SYSTEM02.DBF' size 500m; //01을 02로 변경
SYSTEM늘어났음 !
문제 532. emp테이블, dept테이블이 어느 테이블 스페이스에 저장되어있는지 확인하기
select table_name, tablespace_name from user_tables where table_name in('eEMP', 'DEPT');
- 설명 : users라는 영구히 데이터를 저장할 수 있는 테이블 스페이스에 있으므로 데이터를 지우지 않는 이상 계속 저장됩니다.
※ DB의 공간관리는 DBA의 주 업무이므로 테이블이 생성될 때, 이 테이블을 영구히 저장할 테이블로 생성할지 임시로 데이터를 저장할 테이블로 생성할지를 잘 결정해야한다.
임시테이블 스페이스 사용량 조회 쿼리문
문제 533. 임시테이블 스페이스의 여유공간이 얼마나 있는지 확인하기
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;
- 사용률이 67%이고 200MG정도가 여유롭다 (0.19)
여유공간이 10%밖에 안남았으면 곧 성능이 느려질 것이므로 공간 추가할 대비를 해야한다. 이 공간은 정렬을 위한 작업을 할때 주로 사용되고 정렬 작업이 끝나면 공간이 자동 회수됩니다.
지금부터 만들 임시테이블은 바로 이 공간에 만들어진다.
임시 테이블 스페이스에 저장되는 데이터
정렬 작업을 위한 SQL데이터임시 테이블의 데이터WITH 절의 임시데이터
임시 테이블의 종류 2가지**
-
commit 할때 까지만 데이터를 보관하는 테이블.
-> 관련옵션 :on commit delete rows -
세션을 종료할때까지만 데이터를 보관하는 테이블.
-> 관련옵션 :on commit preserve rows예: sk telecom 의 임시테이블 : 매월 핸드폰 요금 계산하는 프로그램
당월 핸드폰 사용 데이터
문법
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;하면 없어진다 !!!
- 다른 세션을 하나 더 열고 emp_temp2를 조회하면 데이터는 볼 수 없다.
- temporary table은 내 세션에서만 데이터를 볼 수 있고 다른 세션에서는 볼 수 없다.
temporary table 옵션 2가지
on commit delete rows: commit 하면 데이터 사라짐on commit preserve rows: 세션이 종료되어야 데이터 사라짐
예제1. on delete preserve rows 로 임시테이블을 생성
create global temporary table emp_temp3 (empno number(10), ename varchar2(10), sal number(10) ) on commit preserve rows;
예제2. 위 테이블에 데이터 입력
insert into emp_temp3 (empno, ename, sal) select empno, ename, sal from emp;
예제3. commit하고 데이터 조회해보기 -> 안지워짐 !
예제4. 다른 세션에서 조회해보기 -> 안보임
예제5. 세션 나갔다가 들어와서 다시 조회 -> 안보임
※ 임시테이블의 데이터는 임시로 데이터를 저장하는데
temporary tablespace에 저장한다. commit하거나, session을 종료하면 데이터가 지워진다.
※ 임시테이블의 데이터는 내 세션에서만 볼 수 있고 다른 세션에서는 볼 수 없다!
OCP 임시테이블 문제 !
임시테이블에 업데이트 수행하면 그 데이터는 어디에 저장되는가?
답 E
답 A,D,E
문제 534. EMP_TEMP3 테이블에 어느 테이블 스페이스 저장되었는지 확인
select table_name, tablespace_name from dba_tables where table_name = 'EMP_TEMP3';
- tablespace_name이 null로 나온다는 것은,공간이 할당되지 않았다 라는 것을 의미한다. 데이터가 비어있는 상태의 emp_temp3 임시테이블에 어떤 공간도 할당되지 않았다. 구조는 있지만 공간을 할당되지 않았다.
문제 535. 임시 테이블에 공간이 할당되는 시점이 언제인지 테스트!
insert into emp_temp3 (empno, ename, sal) select empno, ename, sal from emp;
- 데이터를 입력하고 테이블의 사이즈가 늘어나는지 결과를 비교ㅏ면서 공간이 할당되는지 확인을 해봐야한다.
데이터를 입력해서 할당되느는게 아니라 그전에는 할당되지 않는다.
복잡한 쿼리를 단순하게 하기 1(VIEW)
오라클의 database 오브젝트(object) 5개 !
table: 행과 컬럼으로 이루어진 기본 데이터 저장소view: 데이터를 저장하지는 않고 테이블의 데이터를 볼 수 있게 해주는 db objectindexsequencesynonym
-> 데이터베이스 관리자가 관리하는 데이터 저장소들.
- view 사용 이유
- 보안상의 이유 (특정 컬럼을 노출하지 않고 데이터 엑세스 하고자)
- 복잡한 쿼리문을 심플하게 작성하기 위해
ex) emp테이블을 공개해야 개발자들이 개발을 할 수 있는데, sal을 노출하지 않게 하고싶다면 view 사용.
create view emp_view as select empno, ename, job, mgr, hiredate, deptno from emp;
- view는 데이터를 저장하지 않고 그냥 테이블을 바라보는 것!
문제 536. emp_view의 데이터를 업데이트하는데, KING의 직업을 SALESMAN으로 변경
update emp_view set job = 'SALESMAN' where ename = 'KING';
view는 데이터를 저장하지 않는다!!
-> 그냥 테이블을 바라보는 쿼리문이다. emp_view를 업데이트 했지만 실제로는 emp테이블이 업데이트 되었다.
문제 537. 아래의 update문장의 실행계획을 확인하세요
explain plan for update emp_view set job = 'SALESMAN' where ename = 'KING'; select * from table (dbms_xplan.display);
- 동그라미 안에 보면 emp가 업데이트가 되었다..!
문제 538. 직업이 ANALYST,SALESMAN,CLERK인 사원들의 모든 컬럼으로 emp_view2를 생성하세요!
create or replace view emp_view2 as select * from emp where job in('ANALYST','SALESMA','CLERK');
문제 539. EMP_VIEW2 의 데이터를 변경하는데 ford의 월급을 0으로 변경하시오
update emp_view2 set sal = 0 where ename = 'FORD';
- 또한 EMP테이블이 변경되었다.
복잡한 쿼리를 단순하게 하기 2(VIEW)
view의 종류를 알아야한다.
view 의 종류 2가지 !
단순view 복합view 테이블 갯수 1개 2개이상 group함수 또는 포함안함 포함 group by 절 DML 여부 가능 불가능할 수 있음
문제 540. 이름, 월급, 부서위치를 출력하는 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. 위 뷰에서 king의 부서위치를 필라델피아로 변경하세요
update emp_dept set loc = 'PHILADELPHIA' where ename = 'KING';
에러가 난다 !
- 위 복합view는 업데이트가 안된다. (dept테이블쪽의 데이터가 업데이트가 안됨)
문제 542. emp_dept 뷰에서 king의 월급을 9000으로 변경
update emp_dept set sal = 9000 where ename = 'KING';
- 얘도 같은 에러가 뜸. 그래서 아래처럼 제약을 걸어주었다.
alter table dept add constraint dept_pk primary key(deptno);
- 위 sql실행 후에는 (제약) 업데이트가 된다.
- 제약을 하면 null값 안들어가고 중복도 안된다. (나중에 배움)
그런데, KING의 부서위치를 필라델피아로 바꾸는것은 안된다.
왜냐면 부서위치가 조인되어있는거라서 KING과 같은 부서번호를 가진 사람들은 다 부서위치가 바뀌게된다. (아래처럼 에러가 남!)
※ 조인으로 만든 VIEW는, 업데이트가 m쪽에 해당하는 테이블의 데이터는 업데이트가 되고, 1쪽에 해당하는 테이블의 데이터는 업데이트가 안된다.
1 : m dept empdept 의 deptno는 10,20,30,40 밖에없지만 emp의 deptno는 10여러개 20여러개 30여러개... 가 있어서 1대 다 ! 관계이다.
문제 543. (복습) 직업, 직업별 토탈월급을 출력
select job, sum(sal) from emp group by job;
문제 544. 위 결과를 출력하는 view를 job_sum이라는 이름으로 생성
복합뷰임 -> group by가 있음
create view job_sum as select job, sum(sal) as sumsal // 여기 컬럼별칭을 써야 에러안남!! from emp group by job;
문제 545. job_sum 뷰에서 직업이 clerk의 sum(sal)을 7000으로 변경
update job_sum set sumsal = 7000 where job = 'CLERK';
- 안된다. 이게 변경이되는것이 말이안됨! 그러면 emp 테이블에서 clerk이라는 직업을 가진 사람들의 sal을 어떻게 변경할것...???????
- GROUP BY절을 사용해서 만든 위의 복합뷰는 DATA를 UPDATE할 수 없다! (delete도 안됨)
※ 복합뷰에서 변경이 되는 뷰는 조인에서 M쪽에 해당되는 것만 변경되고, 나머지는 다 안된다!!
VIEW 관련 OCP문제
문제 546. 위 job_sum 뷰를 지우세요!(delete로) -> 안됨
delete from job_sum;
※ 다시한번, view가 필요한 이유?
보안상의 이유복잡한 쿼리문 간단하게 검색하기 위해
1. 뷰 사용하면
view ename, loc from emp_dept where loc ='DALLAS';2. 뷰 사용 안하면
select e.ename, d.loc from emp e , dept d where e.deptno = d.deptno and d.loc = 'DALLAS';
특정 데이터를 갱신하지 못하게 할때또는전체테이터의 DML 여부 막게 하고 싶을때
VIEW의 옵션 2가지
with check option: 특정 데이터를 갱신 못하게 할 때with read only: 모든 데이터에 대해 DML을 막고싶을 때
ex) 백화점 카드 포인트 적립 기준 테이블에 2%, 5% ... 적립율이 입력되어있는데, 이 테이블에 절대 DML못하게 막아라!! ->
with read only 사용lock table emp in exclusive mode;위처럼 말고 아래처럼 view 만들기
create view emp77 as select * from emp with read only;
- 이렇게 아무것도 delete, update, insert안된다.
문제 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 뷰를 수정하는데 킹의 부서번호를 30번으로 수정
update emp547 set deptno = 30 where ename = 'KING';
- update는 가능하지만 emp547에서는 안보이게 된다 !
문제 550. emp547 뷰를 다시 만드는데 deptno 만큼은 갱신되지 못하게 뷰를 생성하시오! (뷰 생성시 where절 조건에 위배되게 뷰를 갱신하지 못하게 하기)
create or replace view emp547 as select * from emp where deptno in(10,20) with check option;
문제 551. (오늘의 마지막문제) 월급이 4000이하인 사원들의 모든 컬럼을 담는 뷰를 emp551로 생성하는데 emp551에서 월급을 4000보다 크게 갱신하지 못하도록 뷰 생성
create or replace view emp551 as select * from emp where sal <= 4000 with check option;
- sal을 변경하려고 하니
with check option에 위배된다는 에러가 뜬다.
