32강 테이블 구조 생성, 변경 및 삭제하는 DDL(2)

-- 1. alter table로 테이블 구조 변경하기
-- 2. drop table로 테이블 구조 삭제하기
-- 3. 테이블의 모든 로우를 제거하는 TRUNCATE
-- 4. 테이블 명을 변경하는 RENAME
-- 5. 데이터 딕셔너리와 데이터 딕셔너리 뷰

-- 02. 테이블 구조를 변경하는 ALTER TABLE
-- ALTER TABLE 명령문은 기존 테이블의 구조를 변경하기 위한 DDL 명령문 입니다.
-- 테이블에 대한 구조 변경은 컬럼의 추가, 삭제, 컬럼의 타입이나 길이를 변경할 때 사용합니다.
-- 테이블의 구조를 변경하게 되면 기존에 저장되어 있던 데이터에 영향을 주게 됩니다.

-- alter table로 칼럼 추가, 수정, 삭제하기 위해서는 다음과 같은
-- 명령어를 사용합니다.

-- ADD COLUMN절을 사용하여 새로운 칼럼을 추가한다.
-- MODIFY COLUMN 절을 사용하여 기존 칼럼을 수정한다.
-- DROP COLUMN 절을 사용하여 기존 칼럼을 삭제한다.

-- 2.1 새로운 칼럼 추가하기
-- ALTER TABLE ADD 문은 기존 테이블에 새로운 컬럼을 추가합니다.

-- 새로운 컬럼은 테이블 맨 마지막에 추가되므로 자신이 원하는 위치에
-- 만들어 넣을 수 없습니다.

-- 또한 이미 이전에 추가해 놓은 로우가 존재한다면 그 로우에도 컬럼이
-- 추가되지만, 컬럼 값은 NULL 값으로 입력됩니다.

-- alter table table_name
-- add(column_name, date_type expr,...);

-- <실습하기> EMP01 테이블에 JOB 컬럼 추가하기
-- EMP01 테이블에 문자 타입의 직급(JOB) 칼럼을 추가해 봅시다.
drop table emp01;

create table emp01
as select * from emp;

alter table emp01
add( job1 varchar2(9));

-- <탄탄히 다지기>
-- 03. DEPT03 테이블에 문자 타입의 부서장(DMGR) 칼럼을 추가해봅시다.
select * from dept03;

alter table dept02
add(DMGR NUMBER(4));

-- 2.2 기존 칼럼 속성 변경하기
-- ALTER TABLE MODIFY 문을 다음과 같은 형식으로 사용하면 테이블에
-- 이미 존재하는 컬럼을 변경할 수 있게 됩니다.

-- alter table table_name
-- modify (column_name, data_type expr, ...)

-- 컬럼을 변경한다는 것은 컬럼에 대해서 데이터 타입이나 크기, 기본 값들을
-- 변경한다는 의미입니다.

-- <실습하기> 컬럼의 크기 변경하기
-- 1. 직급(job) 컬럼을 최대 30글자까지 저장할 수 있게 변경해 보도록 하자.
alter table emp01
modify(job varchar(30));

desc emp01;

-- <탄탄히 다지기>
-- 4. DEPT02 테이블의 부서장(DMGR)칼럼을 숫자 타입으로 변경해봅시다.
alter table dept02
MODIFY (dmgr number(4));

desc dept02;

-- 2.3 기존 컬럼 삭제
-- 테이블에 이미 존재하는 컬럼을 삭제해 봅시다.
-- ALTER TABLE ~ DROP COLUMN 명령어로 칼럼을 삭제할 수 있습니다.
-- alter table table_name
-- drop column 컬럼명;

-- emp 테이블의 직급 컬럼을 삭제해 보도록 합시다.
alter table emp01
drop COLUMN job;

desc emp01;

-- DEPT02 테이블의 부서장(DMGR) 컬럼을 삭제해 봅시다.
alter table dept02
drop COLUMN dmgr;

desc dept02;

-- 2.4 set unused 옵션 적용하기
-- 특정 테이블(emp02)에서 컬럼(job)을 삭제하는 경우 다음과 같이
-- 무조건 삭제하는 것은 위험합니다.

-- 테이블에 저장된 내용이 많을 경우(몇 만 건에 대한 자료) 해당
-- 테이블에서 컬럼을 삭제하는 데 꽤 오랜 시간이 걸리게 될 것입니다.
-- 컬럼을 삭제하는 동안에 다른 사용자가 해당 컬럼을 사용하려고
-- 접근하게 되면 지금 혀냊 테이블이 사용되고 있기 때문에 다른 사용자는
-- 해당 테이블을 이용할 수 없게 됩니다. 이런 경우 작업이 원할하게
-- 진행되지 않고 락(lock)이 발생하게 됩니다.

-- alter table에 set unused 옵션을 지정하면 컬럼을 삭제하는 것은
-- 아니지만 컬럼의 사용을 논리적으로 제한할 수 있게 됩니다.

-- set unused 옵션은 사용을 논리적으로 제한할 뿐 실제로 컬럼을
-- 삭제하지 않기 때문에 작업 시간이 오래 걸리지 않습니다. 그렇기 때문에
-- 락이 걸리는 일도 일어나지 않게 됩니다.

-- <실습하기> 직급 컬럼 사용 제한하기
-- 1. set unused 옵션이 사용되는 용도를 살펴보았으므로 이제 emp02 테이블의
-- job 컬럼의 사용을 논리적으로 제한해 봅시다.
alter table emp02
set unused(job);

-- 2. 가장 사용빈도가 적은 시간에 실제적인 삭제 작업을 진행합시다.
alter table emp02
drop unused columns;

-- 03. 테이블 구조를 삭제하는 DROP TABLE
-- drop table문은 기존 테이블을 제거합니다.

-- 1. create table을 학습할 때 만들어 놓았던 EMP01 테이블을 삭제해 봅시다.
drop table emp01;

-- 04. 테이블의 모든 로우를 제거하는 TRUNCATE
-- 기존에 사용하던 테이블의 모든 로우를 제거하기 위한 명령어로
-- TRUNCATE가 제공됩니다.

truncate table emp02;

select * from emp02;

-- 05. 테이블 명을 변경하는 RENAME
-- emp02의 테이블의 이름을 test란 이름으로 변경합시다.
rename emp02 to test;

-- 데이터 딕셔너리와 데이터 딕셔너리 뷰
-- DB 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는
-- 시스템 테이블을 데이터 딕셔너리라고 합니다.

-- 데이터 딕셔너리는 사용자가 테이블을 생성하거나 사용자를 변경하는
-- 등의 작업을 할 때 DB 서버에 의해 자동으로 갱신되는 테이블로 사용자는
-- 데이터 딕셔너리의 내용을 직접 수정하거나 삭제할 수 없습니다.

-- 이러한 데이터 딕셔너리를 사용자가 조회해 보면 시스템이 직접
-- 관리하는 테이블이기에 암호 같은 기호만 보여질 뿐 내용은 알 수
-- 없습니다.

-- 데이터 딕셔너리 원 테이블은 직접 조회하기란 거의 불가능한 일입니다.

-- 6.1 USER_데이터 딕셔너리
-- 접두어로 USER가 붙은 데이터 딕셔너리는 자신의 계정이 소유한 객체
-- 등에 관한 정보를 조회 합니다.

-- USER가 붙은 데이터 딕셔너리 중에서 자신이 생성한 테이블이나
-- 인덱스나 뷰 등과 같은 자신의 계정 소유의 객체 정보를 저장한
-- USER_TABLES 데이터 딕셔너리 뷰를 살펴보도록 하겠습니다.

-- <실습하기> USER_TABLES 데이터 딕셔너리 뷰 살피기
-- 1. DESC 명령어로 데이터 딕셔너리 뷰 USER_TABLES의 구조를 살펴봅시다.
desc user_tables;

-- 2. USER_TABLES 데이터 딕셔너리 뷰는 현재 접속한 사용자 계정이 소유한 모든 테이블
-- 정보를 조회 할 수 있는 뷰이기에 현재 사용자가 누구인지를 살펴봅시다.
show user;

-- 3. 데이터 딕셔너리 user_table의 구조를 살펴보면 무수히 많은 컬럼으로 구성
-- 되었음을 알 수 있습니다. 이중에서 테이블의 이름을 알려주는 TABLE_NAME
-- 컬럼의 내용을 살펴봅시다. 현재 사용한 계정이 SCOTT이므로 SOCTT 사용자가 
-- 가능한 테이블의 이름만 알 수 있습니다.
select table_name from user_tables
order by table_name desc;

-- ALL_데이터 딕셔너리
-- 사용자 계정이 소유한 객체는 자신의 소유이므로 당연히 접근이 가능합니다.

-- 만일 자신의 계정이 아닌 다른 계정 소유의 테이블이나 시퀀스 등은 어떨까요?

-- 오라클에서는 타계정의 객체는 원천적으로 접근 불가능합니다.

-- 하지만 그 객체의 소유자가 접근할 수 있도록 권한을 부여하면 타 계정의
-- 객체에도 접근이 가능합니다.

-- ALL_데이터 딕셔너리 뷰는 현재 계정이 접근 가능한 객체, 즉 자신
-- 계정의 소유이거나 접근 권한을 부여 받은 타계정의 객체 등을 조회 할 수
-- 있는 데이터 딕셔너리 뷰입니다.

-- 현재 계정이 접근 가능한 테이블의 정보 조회하는 뷰입니다.

-- <실습하기> all_tables 데이터 딕셔너리 뷰 살피기
-- 1. DESC 명령어로 데이터 딕셔너리 뷰 all_tables의 구조를 살펴봅시다.
desc all_tables;

-- 2. 데이터 딕셔너리 뷰 all_tables의 컬럼 종류가 무수히 많습니다. 이 중에
-- 서 owner, table_name 컬럼 값만 살펴보도록 합시다.
select owner, table_name from all_tables;

-- 6.3 DBA_데이터 딕셔너리 뷰
-- DBA_ 데이터딕셔너리는 DBA가 접근 가능한 객체 등을 조회 할 수 있는 뷰입니다.

-- 앞서도 언급했지만 DBA가 접근 불가능한 정보는 없기에 데이터베이스에 있는 모든
-- 객체 등의 의미라 할 수 있습니다.

-- USER_와 ALL_와 달리 DBA_ 데이터딕셔너리뷰는 DBA 시스템 권한을 가진 사용자만 접근할 수 있습니다.

-- <실습하기> DBA_TABLES 데이터 딕셔너리 뷰 살피기
-- 1. 다음은 테이블 정보를 살펴보기 위해서 DBA_TABLES데이터 딕셔너리 뷰의 내용을 조회해봅시다.
select table_name, owner from dba_tables;

-- 2. DBA 권한을 가진 system 계정으로 접속하여 다시 한번 DBA_TABLES 데이터 딕셔너리 뷰의
-- 내용을 조회해 보시오. 사용자 계정과 암호를 소문자로 입력해야 인식한다는 점에 주의하기 바랍니다.
conn system/oracle
        -- id/pw
select table_name, owner
from dba_tables;