유용한 쿼리 #1

1. 컬럼 추가 및 컬럼코멘트 달기 

ALTER TABLE HQLTTM966 ADD(Q966_MAT_PATTERN VARCHAR2(5));

COMMENT ON COLUMN HQLTTM966.Q966_MAT_PATTERN IS '품목Pattern';

2.  테이블 COPY ( 테이블 생성 및 데이터 복사)

CREATE TABLE HQLTTM951_RIM951 AS

SELECT * FROM HQLTTM951

3. 데이타 있는 테이블 컬럼 변경하기

 

create table hqlttm966_temp as select * from hqlttm966;

delete from hqlttm966;

     

alter table hqlttm966 modify (q966_pickup_date date);

alter table hqlttm966 modify (q966_test_date date);

alter table hqlttm966 modify (q966_write_date date);

alter table hqlttm966 modify (q966_draft_date date);

alter table hqlttm966 modify (q966_review_date date);

alter table hqlttm966 modify (q966_confirm_date date);

alter table hqlttm966 modify (q966_trans_date date);

insert into hqlttm966

select * from hqlttm966_temp;

4. TEXT 혹은 주석명으로 SOURCE 찾기

SELECT *

  FROM ALL_SOURCE

 WHERE NAME NOT LIKE 'BIN%'

   AND TEXT LIKE '%966%'