Oracle DBA SQL 230711

▩ [복습] 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가지

1. 컬럼 추가 : alter table emp
   add email varchar2(50);
2. 컬럼 삭제 : alter table emp
   drop column email;
3. 컬럼 변경 : alter table emp
   modify ename varchar2(20);
4. 컬럼 감추기 :

개발자 -------> 컬럼 삭제 요청 ------->  dba

예제: alter   table    emp
       set  unused  column  sal;

select   *
  from  emp;

select  *
  from   dictionary
  where   table_name  like  '%UNUSED%';

select *
  from  user_unused_col_tabs;

  EMP	 1

설명:  감춘 컬럼의 이름을 확인할수가 없습니다. 그냥 몇개 감췄다라고만
나옵니다.  그러므로 컬럼을 감추는 아래의 명령어를 수행했으면
수첩(달력)에 잘 적어놔야 합니다. 그래야 편하게 컬럼 삭제를 할 수
있습니다.

alter   table    emp
  set  unused  column    sal;

예제.   emp 테이블에 감춘 컬럼인 sal 을 다시 나타나게 하시오 !

	alter   table    emp
    set   used   column   sal;

이런 명령어 없습니다. 다시 나타나게 하는 방법 없습니다.
감추면 끝난것 입니다. 그러면 왜 감추느냐 ? 삭제하려고
지금 컬럼 삭제하면 db 성능이 느려지니까 지금은 잠깐 감춰놓고
나중에 한번에 삭제하려고 감추는 겁니다.

예제.  감춘 컬럼들을 한번에 삭제하는 명령어

	alter   table    emp
	  drop   unused  columns ;

	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 user_unused_col_tabs;

문제527. (※ dba 를 위한 tip) 감사를 대비해서 emp 테이블에 수행했던
ddl 작업들에 대한 이력을 확인하는 딕셔너리를 조회하시오 !

select object_name, created, last_ddl_time, timestamp
from user_objects
where object_name='EMP';

EMP 23/07/11 23/07/11 2023-07-11:10:22:47

언제 DDL을 수행했는지 시간을 확인할 수 있습니다.
어떤 명령어를 수행했는지는 확인할 수가 없습니다.

일 잘하는 DBA 들을 테이블 이력관리하는 테이블을 별도로 따로 만들어서 데이터 저장하고 관리를 합니다.

drop table table_ddl_history;

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', '개발팀 요청');

commit;

※ 위와 같이 테이블에 DDL 이 발생할 때마다 이력정보를 남기고
DB 감사에 대비하면 됩니다.

▩ [복습] SQL의 종류

Query

1. DML : insert, update, delete, merge, select
2. DDL : create, alter , drop, truncate, rename
3. DCL : grant, revoke
4. TCL : commit, rollback, savepoint

문제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;

select * from emp;

※ rename 도 DDL명령어 이므로 수행되면서 commit 됩니다.

문제530.(컬럼명 변경하기) emp 테이블의 sal 컬럼명을 salary 로 변경하시오!

alter table emp
rename column sal to salary ;

select * from emp;

문제531. (컬럼명 변경하기) emp 테이블의 job 을 job_id 로 변경하시오 !

alter table emp
rename column job to job_id;

select * from emp;

다시 emp 테이블 초기화 하세요 !

SQL> ed init_emp.sql
SQL> @init_emp.sql

▣ 094 임시 테이블 생성하기(CREATE TEMPORAY TABLE)

1. 영구히 데이터를 저장할 수 있는 테이블 : heap table

2. 임시로 데이터를 저장할 수 있는 테이블 : tempoary table

   왜 임시로 저장하는 테이블이 필요한가 ?

   1. 지금 잠깐 볼 데이터이고 영구히 저장할 필요는 없는 데이터인 경우

   2. 임시 테이블을 permanent tablespace 에 데이터를 저장하지 않고 temporary tablespace 에 데이터를 저장하므로 permanent tablespace의 디스크를 사용하지 않아도 됩니다.

**[응급조치]   system  테이블 스페이스의 공간을 늘립니다.**

1. 테이블 스페이스와 관련된 파일명을 확인합니다.
    
    select   tablespace_name,  file_name
      from  dba_data_files;

USERS			C:\APP\ITWILL\PRODUCT\18.0.0\ORADATA\XE\USERS01.DBF
UNDOTBS1		C:\APP\ITWILL\PRODUCT\18.0.0\ORADATA\XE\UNDOTBS01.DBF
SYSTEM -> 논리적이름	C:\APP\ITWILL\PRODUCT\18.0.0\ORADATA\XE\SYSTEM01.DBF -> 물리적인 공간
SYSAUX			C:\APP\ITWILL\PRODUCT\18.0.0\ORADATA\XE\SYSAUX01.DBF
    
2. system tablespace 에 공간을 추가합니다.

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 라는 영구히 데이터를 저장할 수 있는 테이블 스페이스에
있으므로 데이터를 지우지 않는 이상 계속 저장됩니다.

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;

여유공간이  10% 밖에 안남았으면 곧 성능이 느려질 것이므로  공간 추가할
대비를 해야합니다. 이 공간은 정렬을 위한 작업을 할때 주로 사용되고
정렬 작업이 끝나면 공간이 자동 회수 됩니다.

지금 부터 만들 임시 테이블은 바로 이 공간에 만들어집니다.

※ 임시 테이블 스페이스에 저장되는 데이터

1. 정렬 작업을 위한 SQL의 데이터(Order by)
2. 임시 테이블의 데이터
3. With 절의 임시 데이터

※ 임시 테이블의 종류 2가지

1. commit 할때 까지만 데이터를 보관하는 테이블
   관련옵션: on commit delete rows

2. 세션을 종료할때까지만 데이터를 보관하는 테이블
   관련옵션 : 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;

 select * from  emp_temp2;

다른 세션을 하나 더 열고 emp_temp2를 조회하게 되면 데이터는 볼 수 없습니다.

SQL> select * from emp_temp2;

선택된 레코드가 없습니다.

on commit delete rows옵션을 써서 만든 temporary table은 내 세션에서만 데이터를 볼 수 있고 다른 세션에서는 같은 테이블의 데이터를 볼 수 없습니다.

→ commit; 시 사라짐

[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 mgr is null
  connect by prior empno =  mgr

▩ temporary table 옵션 2가지

1. on commit delete rows : commit 하면 데이터 지워짐
2. 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 하고 데이터를 조회하시오 !

   commit;

   select * from emp_temp3;

4. 다른 세션에서 emp_temp3 데이터를 조회하시오

   select * from emp_temp3;

   안보입니다.

5. 지금 원래 세션에서 exit 명령어 수행하고 나갔다가 다시 접속해서
   emp_temp3 를 조회하시오!

   SQL> exit;
   
   C:\Users\ITWILL>sqlplus c##scott/tiger
   
   SQL> select * from emp_temp3;
   
   선택된 레코드가 없습니다.

※ 임시 테이블의 데이터는 임시로 데이터를 저장하는데 tempoaray tablespace에 저장을 합니다. commit 하거나 session 을 종료하면 데이터가 지워집니다.

※ 임시 테이블의 데이터는 내 세션에서만 볼 수 있고 다른 세션에서는 볼 수 없습니다.

문제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;

설명: 데이터를 입력하고 테이블의 사이즈가 늘어나는지를 확인하면서
공간이 할당되는지 확인을 다시 해야합니다.
데이터를 입력하면 할당되는것이고 그전에는 할당되지 않습니다.

▣ 095 복잡한 쿼리를 단순하게 하기 1(VIEW)

• oracle 의 database 오브젝트(object) 5개 ?

1. table : 행(row) 와 컬럼(column) 으로 이루어진 기본 데이터 저장소
2. view : 데이터를 저장하지는 않고 테이블의 데이터를 볼 수 있게해주는 db object
3. index
4. sequence
5. synonym

view 를 왜 사용하는가 ?
											1. 보안상의 이유로
											 (특정 컬럼을 노출하지 않고 데이터를 엑세스할 수 있게 하려고 )
                      2. 복잡한 쿼리문을 심플하게 작성하기 위해서

emp 테이블을 공개를 해야 개발자들이 개발을 할 수 있는데 sal 을 노출하지 않게 하고 싶다면 view 로 만들면 됩니다.

create view emp_view
as
select empno, ename, job, mgr, hiredate, deptno
from emp;

select * from emp_view;

※ view 는 데이터를 저장하지 않고 그냥 테이블을 바라보는 것 입니다.

문제536. emp_view 의 데이터에서 KING 의 직업을 SALESMAN 으로 변경하세요

update emp_view
set job='SALESMAN'
where ename='KING';

select * from emp_view;

※ 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);

문제538. 직업이 ANALYST , SALESMAN, CLERK 인 사원들의 모든 컬럼으로 emp_view2 를 생성하시오 ! ( 보안상의 이유로 president 와 manager는 데이터를 노출하면 안될때 )

create or replace view 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 ename, sal from emp;

복잡한 쿼리를 단순하게 검색하기 위해서 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. emp_dept 에서 KING의 부서위치를 필라델피아로 변경하세요 !

update emp_dept
set loc ='Philadelphia'
where ename='KING';

ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다 ----> dept 테이블의 정보도 다 바뀌어 버린다.
KING 뿐만 아니라 부서번호 10번인 모든 사람의 위치가 필라델피아로 바뀌어 버리기 때문이다!

위의 복합view 는 업데이트가 안됩니다. (dept 테이블쪽의 데이터가 update 안됨)

문제542. emp_dept 뷰에서 KING 의 월급을 9000 으로 변경하시오

update emp_dept
set sal = 9000
where ename='KING';

SQL 오류: ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다

alter table dept
add constraint dept_pk primary key(deptno); -> 확실하게 중복된 데이터가 없다는 것을 알려준다!

update emp_dept
set sal = 9000
where ename = 'KING';

※ 조인으로 만든 view 는 update 가 m 쪽에 해당하는 테이블의 데이터는 update 가 되고 1쪽에 해당하는 테이블의 데이터는 update 가 안됩니다.

문제543. (복습문제) 직업, 직업별 토탈월급을 출력하시오 !

select job, sum(sal)
from emp
group by job;

문제544. 위의 결과를 출력하는 view 를 job_sum 이라는 이름으로 생성하시오!

create view job_sum
as
select job, sum(sal) as sumsal
from emp
group by job;

ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다
컬럼 별칭을 써줘야한다.

문제545. job_sum 뷰에서 직업이 CLERK 의 sumsal 을 7000 으로 변경하시오

update job_sum
set sumsal = 7000
where job='CLERK';

SQL 오류: ORA-01732: 뷰에 대한 데이터 조작이 부적합합니다

※ group by 절을 사용해서 만든 위의 복합뷰는 data 를 update 할 수 없습니다. 복합뷰는 일단 그냥 안된다고 알고 있으면 된다

문제546. 위의 job_sum 뷰를 delete 로 지우시오 !

delete from job_sum;

SQL 오류: ORA-01732: 뷰에 대한 데이터 조작이 부적합합니다

※ group by 절을 사용해서 만든 위의 복합뷰는 data 를 delete 할 수 없습니다

insert into job_sum
values ('dhekftn', 300);

SQL 오류: ORA-01733: 가상 열은 사용할 수 없습니다

※ group by 절을 사용해서 만든 위의 복합뷰는 data 를 insert 할 수 없습니다.

※  view  가 필요한 이유 ?  1. 보안상의 이유
                        2. 복잡한 쿼리문은 간단하게 검색하기 위해서

※ view 가 필요한 이유 ?

1. 보안상의 이유

2. 복잡한 쿼리문은 간단하게 검색하기 위해서

3. view 를 사용하면 ?

   select ename, loc from emp_dept where loc='DALLAS';

4. view 를 사용안하면 ?

   select e.ename, d.loc
   from emp e, dept d
   where e.deptno = d.depton and d.loc='DALLAS';

※  view  가 필요한 이유 ?  
													1. 보안상의 이유
													2. 복잡한 쿼리문은 간단하게 검색하기 위해서
													3.  특정 DATA 를 갱신하지 못하게 할때
													또는 전체 DATA 의 DML 여부를 막게 하고 싶을때

▣ view 의 옵션 2가지 ?

1. with check option : 특정 데이터를 갱신 못하게 할때
2. with read only : 모든 데이터에 대해서 DML 을 막고 싶을때

이마트 신세계 백화점 카드 개발할때  PM 이  요청한 내용 ?

백화점 카드 포인트 적립 기준 테이블에  2%, 5%  적립율이 입력되어있는데
이 테이블에 절대 DML 못하게 막아라 ! (DELETE, UPDATE 불가능)

lock  table   emp  in exclusive  mode;

이렇게 하면 안되고  with  read  only 로  view 로 만들어 주면 됩니다.

create    view     emp77
as
select    *
  from  emp
  with  read  only;

select  * from emp77;

문제547. 부서번호가 10번, 20번인 사원들의 모든 컬럼을 가져오는
view 를 생성하시오 !

create or replace view emp547
as
select *
from emp
where deptno in ( 10, 20 );

select * from emp547;

문제548. emp547 뷰를 수정하는데 KING 의 월급을 8400으로 수정하시오

update emp547
set sal = 8400
where ename='KING';

문제549. emp547 뷰를 수정하는데 KING 의 부서번호를 30번으로 수정하시오

update emp547
set deptno = 30
where ename='KING';

update 는 가능하지만 emp547에서는 안보이게 됩니다.

select *
from emp547;

rollback;

문제550. emp547 뷰를 다시 만드는데 deptno 만큼은 갱신되지 못하게끔 뷰를 생성하시오 ! (뷰생성시 where 절 조건에 위배되게끔 뷰를 갱신하지 못하게 하시오!)

create or replace view emp547
as
select *
from emp
where deptno in (10, 20)
with check option; ---> where절에 기술한 deptno 만큼은 update가 되지 않는다!

update emp547
set deptno = 30
where ename='KING';

SQL 오류: ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

문제551. (오늘의 마지막 문제) 월급이 4000 이하인 사원들의 모든 컬럼을 담는 VIEW 를 emp551 로 생성하는데 emp551에서 월급을 4000 보다 크게 갱신하지 못하도록 view 를 생성하시오 !

view 생성하고 view 를 갱신해서 갱신 안되는 것까지 같이 올려주세요 ~

create or replace view emp551
as 
select *
  from emp
  where sal <= 4000
  with check option;
  
update emp551
  set sal = 4500;