work smart !!

다들 알고 계시겠지만 TRUNCATE 와 DELETE의 비교입니다.

TRUNCATE 명령은 WHERE 절이 없는 DELETE와 유사하나, 아래 내용들에 있어 차이를 보인다.

• TRUNCATE는 테이블 크기에 관계없이 매우 빠르다. DELETE는 롤백이 발생될 경우에 대비하여 언두 정보를 생성하여 로그 파일에 기록하지만, TRUNCATE는 언두를 생성하지 않는다.

• TRUNCATE는 DDL로서 다른 DDL과 마찬가지로 묵시적으로 커밋을 수행하기 때문에 롤백 할 수 없다. 커밋되지 않은 상태의 DML변경 또한 TRUNCATE 명령과 함께 커밋 될 것이다.

• TRUNCATE는 테이블과 모든 인덱스의 HWM(high-water mark)을 리셋한다. 전체 테이블 스캔 및 인덱스 전체-고속 스캔(index fast-full scan)은 HWM까지의 모든 데이터 블록을 읽기 때문에 DELETE 후에는 전체 스캔 작업이 향상되지 않지만, TRUNCATE 후 이 작업은 매우 빨라진다.

• TRUNCATE는 어떤 DELETE 트리거도 발생시키지 않는다.

• 한 사용자가 다른 사용자의 테이블을 TRUNCATE 하도록 허용하는 객체 권한은 존재하지 않는다. 다른 스키마의 테이블을 TRUNCATE하기 위해서는 DROP ANY TABLE 시스템 권한이 요구된다.

• 테이블이 TRUNCATE될 때, 테이블과 모든 인덱스의 스토리지는 초기값으로 리셋된다. DELETE는 테이블이나 인덱스의 크기를 축소시키지 않는다.

  [출처] [오라클Tip] TRUNCATE vs DELETE (디비고's 쉽게 따는 IT자격증) |작성자 지와타네

혹 이런 고민에 빠진 독자가 있을지 모르지만, 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

-오라클 덤프 export

1. DB서버로 접속 하여 root 권한으로 변경

su -

2.오라클 유져로 변경

su - oracle

3.sysdba 계정으로 오라클 sqlplus 실행

sqlplus "/as sysdba"

4.덤프 받을 DB 에 유져 데이터를 export

exp userid/password file=filename.dmp full=Y;

-오라클 import

1.export 받은 덤프 파일을 import 할 DB 서버로 이동시킨후 DB서버 접속(해당 덤프 파일을 oracle 유져가 사용할수 있도록 권한 설정 chmod 777)

su -

su - oracle

2.sysdba 계정으로 오라클 sqlplus 실행 하여 import 받을 테이블 스패이스를 생성후 유져 생성

create user userid identified by password default tablespace tablespace_name temporary tablespace temp;

3. 생성한 유저에게 권한 부여

grant connect, resource, dba to userid;

4. 생성한 유저에게 import(이때 sqlplus 를 빠져나가서 oracle 계정으로 실행하여야 함)

imp userid/password file='filename.dmp' full=y;