work smart !!

[MSSQL - SELECT 와 INSERT 동시 처리]

편의상 아래와 같은 세개의 테이블이 있다고 가정하겠습니다.

첫번째와 두번째의 테이블인 table_a 와 table_b 는 컬럼의 갯수 및 데이터형식이 똑 같은 쌍동이 테이블입니다., table_c 는 두개의 컬럼(Nickname, Tel)이 더 추가되어 있는 테이블로 table_a 와 table_c 와는 조금 다른 구조를
가지고 있습니다.



1. 두 테이블의 컬럼이 같을때 SELECT INSERT 하기

두개의 테이블간에 컬럼이 table_a 와 table _b 처럼 완전히 일치할 때는 아래와 같이 간단하게 처리할 수 있습니다.

예1) INSERT INTO table_b SELECT * FROM table_a

위의 예1) 에서는 table_a 의 레코드 전체를 table_b 로 삽입(INSERT) 시킵니다. 만약에 조건을 주어 데이터를 삽입(INSERT) 시키려 한다면 아래와 같이 뒤에 WHERE 절만 추가해 주시면 됩니다.

예2) INSERT INTO table_b SELECT * FROM table_a WHERE seq = 2

위의 SQL 문은 table_a 에서 seq 의 값이 2 인 레코드를 table_b 에 INSERT 시킵니다.



2 . 두 테이블간의 컬럼이 일치하지 않을때 SELECT INSERT 하기

두개의 테이블간에 컬럼이 table_a 와 table _c 처럼 일치하지 않을때는 아래와 같이 컬럼명을 지정해 주시면됩니다.

예3) INSERT INTO table_c ( seq, Name, Email, Idate ) SELECT seq, Name, Email, Idate FROM table_a

위의 예3) 에서는 table_a 의 컬럼중 seq, Name, Email, Idate 컬럼을 table_c 에 Insert 시키고 있습니다. 한가지 주의
할 것은 table_c 의 구조가 table_b 와 다른 만큼 INSERT 될 컬럼이 SELECT 되는 컬럼과 일치하지 않으면 아래와 같은
에러가 발생하게 됩니다.

3. SELECT INTO 를 이용한 데이터 삽입

위에서 설명한 SELECT INSERT 는 테이블의 검색결과를 이미 존재하는 테이블에 삽입(INSERT) 하게됩니다.하지만 SELECT INTO 는 테이블의 검색결과를 INTO 문 다음에 지정한 테이블을 새로 생성시켜서 삽입하게 됩니다. INTO 문 다음의 테이블은 반드시 존재하지 않아야 합니다. 존재하는 테이블이라면 아래와 같은 에러가 발생하게 됩니다.

서버: 메시지 2714, 수준 16, 상태 6, 줄 1
데이터베이스에 'table_d'(이)라는 개체가 이미 있습니다.

예4) SELECT * INTO table_d FROM table_a

위의 예4) 에서는 table_a 의 내용을 table_d라는 테이블을 새로 생성시켜서 삽입하게 됩니다. 조건절을 추가하려면
검색되는 테이블명 뒤에 추가하면 됩니다.

예5) SELECT * INTO table_d FROM table_a WHERE seq = 3

위의 SQL 문은 table_a 에서 seq 의 값이 3인 레코드를 새로 생성되는 table_d 에 INSERT 시킵니다.

혹 이런 고민에 빠진 독자가 있을지 모르지만, SELECT, UPDATE, DELETE 등의 SQL의 예약어를 테이블의 이름이나 열 이름으로 사용해야 할 경우가 있다면 이 기사가 도움이 될 것이다.

기본적으로 예약어들을 사용한다는 것은 이후 관리상의 문제나 여러 가지 문제를 발생시키기 때문에 사용하지 않는 것이 좋다.

하지만 아쉽게도 다른 데이터베이스에서 SQL Server 등으로 마이그레이션을 했을 때 예약어가 테이블명이나 필드명으로 사용되어 어쩔 수 없이 이용해야 한다면  다음과 같이 이용할 수 있다.

SQL Server에서 열 이름은 "["과 "]"로 묶어 표기하면 데이터베이스 객체 이름으로 인식된다.
오라클에서 사용하고 싶다면 큰따옴표로 묶어 기술하면 된다.

Test 테이블에 FROM 필드와 TO열을 생성하고 싶다면 다음과 같은 명령어가 필요하다.

• SQL Server일 경우
• CREATE TABLE test ( [FROM] int null, [TO] int null )
   
• Oracle일 경우
• CREATE TABLE test ( "FROM" NUMBER(8) null, "TO" NUMBER(8) null )

이를 통해 FROM필드와 TO필드를 가진 테이블이 생성된다. SELECT나 UPDATE에서도 같은 서식을 사용하여 이용할 수 있다.

• SQL Server일 경우
• SELECT [FROM] FROM test WHERE [TO] = 45
   
• Oracle일 경우
• SELECT "FROM" FROM test WHERE "TO" = 45

* 테이블명이나 필드명등에 공백문자열이 포함되어 두단어 이상으로 사용하고 싶을때도 []를 사용하면 된다.

자료출처 : http://korea.internet.com

[테이블스페이스 생성하기]

create tablespace data1 datafile '/oracle/oradata/stone/data1.dbf' size  6M
default storage (initial 50k next 50k minextents 10 maxextents 121 pctincrease 0);

테이블 스페이스는 locally managed tablespace를 사용하구요.   

uniform extent size 옵션과 함께 사용하면 데이터들이 각 disk에 고르게 분산이 되어 저장 됩니다.

물론 아시겠지만 가장 좋은 분산 방법은 hardware혹은 file system의 기능을 사용한 raid 1+0 입니다.

[테이블스페이스 삭제하기]

-- 테이블스페이스 내용도 지우고 데이터파일도 같이 지우기

drop tablespace tsdiskdiary01 including contents and datafiles;

drop tablespace SAMPLE_STONE including contents;

-- 테이블스페이스 정보보기
select  tablespace_name, block_size, initial_extent, next_extent, min_extents, max_extents, pct_increase, status from   dba_tablespaces;
select * from dba_data_files;
select * from dba_tablespaces;
select * from v$datafile;

-- 테이블스페이스 만들기
create tablespace data1 datafile '/oracle/oradata/stone/data1.dbf' size  6M
default storage (initial 50k next 50k minextents 10 maxextents 121 pctincrease 0);
/*
initial 50k => 처음 생성되는 익스텐트의 크기
next 50k => 현재 존재하는 마지막 익스텐트 다음에 생성될 익스텐트에 할당할 크기
minextents 10 => 세그먼트가 생성될 때 할당되어야 하는 익스텐트의 수
maxextents 121 => 오라클이 객체에 대해 할당할수 있는 익스텐트의 최대수
pctincrease 0 => 마지막 익스텐트 다음에 생성될 익스텐트의 증가율
*/

-- 테이블스페이스 만들기
create tablespace idx1 datafile '/oracle/oradata/stone/idx1.dbf' size  6M
default storage (initial 50k next 50k pctincrease 0);

create tablespace rds datafile '/oracle/oradata/stone/rds.dbf' size  6M
default storage (initial 50k next 50k pctincrease 0);
-- temp 테이블 스페이스는 안 만들어진다.
create tablespace temp datafile '/oracle/oradata/stone/temp.dbf' size  3M
temporary;

alter tablespace data1 add datafile '/oracle/oradata/stone/data11.dbf' size  3M;
-- 테이블스페이스에 새로운 파일 추가
create tablespace test
 datafile '/oracle/oradata/stone/test.dbf' size 1m;

drop tablespace test;

-- 기존에 있는 데이터파일을 테이블스페이스 파일로 다시 사용한다.
alter tablespace data1
 add datafile  '/oracle/oradata/stone/test.dbf' reuse;

-- 데이터파일의 크기를 늘린다.
alter database datafile
 '/oracle/oradata/stone/test.dbf' resize 1m;

-- 자동으로 증가하는 테이블스페이스 만들기
alter database datafile '/oracle/oradata/stone/test.dbf'
 autoextend on;

-- 테이블스페이스 생성하기
create tablespace app1_data
 datafile '/oracle/oradata/stone/app101.dbf' size 1024k;

create tablespace appl_data
 datafile '/oracle/oradata/stone/appl01.dbf' size 100k;

-- 테이블스페이스 관련 정보보기
select * from dba_tablespaces where tablespace_name = 'TEMP';
-- 얼마의 용량이 남았는지 확인하기
select * from dba_free_space where tablespace_name = 'TEMP';

-- appl_data 테이블스페이스에 test 테이블 만들기 100k 이상하면 에러가 난다..
create table test (num number)
 tablespace appl_data
 storage (initial 50k);

select * from dba_free_space where tablespace_name = 'APP1_DATA';
select * from user_segments where segment_name = 'TEST';

-- 데이터파일 확인하기
select * from dba_data_files;

select * from dba_rollback_segs;

select * from dba_roles;
-- connect, RESOURCE, DBA 역할에 주어진 권한 확인하기
select * from dba_sys_privs where grantee = 'CONNECT';
select * from dba_sys_privs where grantee = 'RESOURCE';
select * from dba_sys_privs where grantee = 'DBA';
select * from dba_users;
-- 사용자에 할당된 테이블스페이스 영역
select * from user_ts_quotas;
-- 사용자 세션 확인하기
select * from v$session;
-- 인덱스 확인하기
select * from user_indexes;

CREATE TABLESPACE tsdiskdiary01
DATAFILE '/oracle/oradata/stone/tsdiskdiary01.dbf' SIZE 100M
DEFAULT STORAGE
(INITIAL    10K
NEXT      10K
MINEXTENTS 2
MAXEXTENTS 50
PCTINCREASE 50);

[테이블스페이스 생성하기]

create tablespace tsdiskdiary01
datafile '/oracle/oradata/stone/tsdiskdiary01.dbf' size 500M
extent management local uniform size 1M;

create user diskdiary identified by diskdiary      -- 일반계정
    default tablespace tsdiskdiary01
    profile default;

grant connect, resource to diskdiary;

drop tablespace tsdiskdiary01 including contents and datafiles;

drop user diskdiary cascade;  
-- 데이터파일 추가하기
alter tablespace sample_stone add datafile  '/oracle/oradata/stone/sample_stone02.dbf' SIZE 200M

[테이블스페이스 용량 확인하기]

SELECT a.tablespace_name,
             a.total "Total(Mb)",
             a.total - b.free "Used(Mb)",
             nvl(b.free,0) "Free(Mb)",
             round((a.total - nvl(b.free,0))*100/total,0)  "Used(%)"
from     (select   tablespace_name, round((sum(bytes)/1024/1024),0) as total
             from     dba_data_files
             group   by tablespace_name) a,
           (select  tablespace_name, round((sum(bytes)/1024/1024),0) as free
            from      dba_free_space
            group by tablespace_name) b
where   a.tablespace_name = b.tablespace_name(+)
order by a.tablespace_name;

select SEGMENT_NAME, SEGMENT_TYPE, INITIAL_EXTENT, NEXT_EXTENT
   where TABLESPACE_NAME ='SAMPLE_STONE'
   and    SEGMENT_NAME= 'AAA ';

alter tablespace sample_stone add datafile  '/oracle/oradata/stone/sample_stone02.dbf' SIZE 200M