오라클 테이블 스페이스 생성하기 및 다른 사용자 권한 부여

오라클 테이블 스페이스를 만드는 방법에 대해서 알아 보겠습니다. 오라클의 테이블 스페이스는 데이터가 저장될 실제 서버상의 파일과 맵핑 됩니다. 생성할 테이블 스페이스 이름과 연관된 파일을 지정하여 생성합니다. 그리고나서 테이블 및 인덱스를 생성할 때 어떤 테이블 스페이스를 사용할지를 지정하게 되는 것입니다.

 

테이블 스페이스를 지정하지 않고 그냥 테이블 또는 인덱스를 생성하게되면 기본값이 "USERS" 테이블 스페이스에 저장 되게 됩니다.

 

테이블 스페이스를 사용하는 이점은 테이터를 서로 다른 디스크에 배치할 수 있게 해줍니다. 인덱스용 테이블 스페이스는 빠른 SSD 에, 자주 사용되지 않는 데이터들은 SATA 디스크에 배치하는 등의 최적화를 하는게 가능합니다. 백업시에도 테이블 스페이스별로 하는 분리하여 하는것도 가능합니다.

 

용량이 부족해지면 나중에 데이터 파일을 추가하는 것도 가능합니다.

 

 

1. 테이블 스페이스를 생성합니다.

 

테이블 스페이스는 extent 관리를 dictionary로 할지 local로 할지 지정하여 생성할 수 있습니다. 확장 관리를 local로 하면 확장과 관련된 정보가 생성되는 테이블 스페이스에 저장됩니다.

 

CREATE TABLESPACE 테이블스페이스명
DATAFILE '데이타 파일명' SIZE integer [K|M]
[EXTENT MANAGEMENT
	{DICTIONARY | LOCAL
		{AUTOALLOCATE | UNIFORM [SIZE integer [K|M] }}]

예)
CREATE TABLESPACE PRJ_USER
	DATAFILE 'd:\datas\prj_user\prj_user01.dbf' SIZE 100M
	EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

 

이 예는 확장 관리를 local로 하고, 그 상세 관리 또한 자동으로 이루어지도록 생성한 것입니다.

 

 

2. 데이터 파일을 추가하는 방법

 

-- 주어진 옵션으로 자동확장 합니다.
ALTER TABLESPACE PRJ_USER
	ADD DATAFILE 'd:\datas\prj_user\prj_user02.dbf' SIZE 10M
		AUTOEXTEND ON
		NEXT 512K
		MAXSIZE 250M;

-- 자동확장을 하지 않도록 생성합니다.
ALTER DATABASE DATAFILE 'd:\datas\prj_user\prj_user02.dbf'
	AUTOEXTEND OFF;

 

 

3. 데이터파일 크기를 수동으로 리사이즈하기

 

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'
	RESIZE 100M;

 

 

4. 테이블 및 인덱스 생성시 테이블 스페이스 지정하기

 

-- 테이블 생성
CREATE TABLE prj_member (
	mem_id    varchar2(20) not null,
	mem_nm    varchar2(100) not null
) TABLESPACE PRJ_USER PCTFREE 20;

-- 주키 생성
ALTER TABLE prj_member
	ADD CONSTRAINT PK_prj_member PRIMARY KEY ( mem_id )
	USING INDEX TABLESPACE PRJ_USER;

 

 

5. 오라클의 다른 사용자 계정에 권한 부여하기

 

prj_user 스키마의 모든 테이블에 조회(select) 권한만을 가지는 prj_vw_user 계정을 만들어 보겠습니다. 먼저 prj_vw_user 접속권한을 부여합니다.

 

GRANT CREATE SESSION TO prj_vw_user;

 

prj_user 계정으로 로그인 하여 다음 쿼리를 실행해서 모든 테이블에 조회 권한을 주는 스크립트를 생성합니다.

 

SELECT 'GRANT SELECT ON ' || OBJECT_NAME || ' TO prj_vw_user;'
  FROM USER_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE';

 

생성된 결과 스크립트를 실행해서 권한을 부여합니다.