[복습] SQL | Oracle SQL의 분류(DDL, DML, DCL, TCL, DQL) 중 DDL의 DROP

어제 배운 DDL의 CREATE 잠깐 언급하고 바로 DROP으로 넘어가겠다.

 

DDL(Data Definition Language): CREATE, DROP, ALTER, TRUNCATE

DDL은 Auto commit을 지원한다.

 

1) CREATE 문법

 

위 포맷을 근거로, 다음 중 테이블을 만들 때 정의되지 않는 것은? 이라는 문제도 SQLD에 출제된다.

 

create table table_name
as
select * from org_name;

 

위처럼 CTAS 할 때 original table의 컬럼명, 컬럼순서, 데이터 타입, not null 속성 그대로 복제되며, not null을 제외한 나머지 제약조건은 복제되지 않는다.

 

create table table_name
as
select * 
  from emp
 where 1=2; 

 

위와 같이 where절에 거짓 조건을 주면 구조만 복제되고 데이터는 복제되지 않는다.

 

 

728x90

 

 

2)DROP

DROP은 테이블을 삭제한다.

구조를 삭제하는 것으로 즉 아예 테이블을 통으로 날려버린다. 그 이후 해당 테이블을 다시 확인할 수 없다.

테이블 삭제시 즉시 반영되는 auto commit이며, 위에서 언급했듯 테이블 삭제 후 해당 테이블명으로 조회 불가하다.

테이블 삭제 후 삭제 정보를 recyclebin에 보관하는데 이 의미는 복구가 가능하다는 것이다.

그러나 purge 옵션으로 테이블을 삭제하게 되면 영구 삭제가 된다.

 

단계별로 테스트를 해보자.

가. table 삭제

drop table emp_t1;

drop table emp_t2 purge;

 

나. recyclebin 조회

select *

  from user_recyclebin;

 

 

다. 복구

복구하는 문법은 아래와 같다.

따라서

 flashback table "BIN$XRzd9EkDTbWyrdi0oVXW9A==$0" to before drop;  

여기서 쌍따옴표 안의 테이블명은 recyclebin으로 들어갔을 때 랜덤하게 부여되는 이름이다.

table_name은 user_recyclebin에 조회되는 OBJECT_NAME으로 전달하면 된다.

 

라. 테이블 조회

select * from  EMP_T1;

 

 

업무를 할 때 이러한 경우가 있다.

예를 들어 emp 테이블을 사용하다가 emp 테이블을 삭제하였다. 다른 사람이 emp 테이블을 새로 생성한 경우 삭제한 emp 테이블을 복구하려면 to before drop 할 수 없다. 그러므로 rename 해야한다.

 

예제) emp_t1을 삭제 후(purge 없이) emp_t1 테이블을 emp와 구조는 같지만 데이터는 없는 형태로 새로 생성하여라. 이 이후 삭제된 emp_t1을 다시 복구 시도하여라.

 

가. emp_t1 테이블 삭제.

drop table emp_t1;

 

나. emp와 같은 구조의 emp_t1 생성

create table emp_t1

as

select * from emp

where 1=2;

 

다. 기존에 삭제한 emp_t1 복구시도

flashback table "BIN$EcHGHSosTuGI79+EeWGHvw==$0" to before drop rename to emp_t1_new; 

여기서 rename to 하지 않으면 에러가 발생한다. step 나에서 생성한 테이블과 충돌이 발생하기 때문이다.

 

 

 

여기서 중요한 업무 쿼리가 있다.★

계속 지우고 만들고 반복하는 테이블 중에 원본이 삭제될 수도 있기 때문에 백업테이블을 만들고자 한다.

본 SCOTT 유저가 소유하는 모든 테이블에 대해(recyclebin table 제외) 백업테이블을 만드려고 하면 일일이 다음과 같이 우선 입력하면 된다.

 

SELECT 'CREATE TABLE '||TNAME||'_BACKUP AS SELECT * FROM '||TNAME||';'
  FROM TAB
 WHERE TNAME NOT LIKE 'BIN%';
  

이렇게 하면 result 창에 아래와 같이 출력된다.

 

 

여기는 총 98개정도 되지만 100개 행이 넘는다면 result 창에서 마우스 우클릭하여 Fetch all rows 선택 후에,

100개가 넘지 않는다면 그냥 마우스 우클릭 후 Select All 선택 하고 새로운 창을 열어 Ctrl + v(붙여넣기) 하면 아래처럼 나타난다.

 

 

그러면 create table 이 여러 줄 생성되는 것이므로 그대로 F5 눌러서 실행하면 backup본이 생성된다.

 

여기까지 하고 나머지는 집에 가서 복습해야겠다.