[ DATABASE ] - Oracle에서 사용자 생성 및 테이블스페이스 생성

사용자 생성 및 테이블스페이스(TableSpace) 생성

1. tabalespace 생성

// -> 기본 tablespace 생성 oracle 쿼리
CREATE TABLESPACE [테이블스페이스명] 
DATAFILE [파일경로] 
[옵션]

ex)
CREATE TABLESPACE myts 
DATAFILE 'C:\oracle\oradata\myts.dbf' 
SIZE 100M 
AUTOEXTEND ON NEXT 5M MAXSIZE 500M;

// tablespace 변경 시
ALTER DATABASE DATAFILE [경로] [변경사항]

ex) 
// tablespace의 데이터파일 크기 200MB로 확장
ALTER DATABASE DATAFILE 'C:\oracle\oradata\myts.dbf' RESIZE 200M;
// 해당 테이블스페이스 읽기 전용으로 상태 변경
ALTER TABLESPACE myts READ ONLY;

// -> tablespace 삭제 시 쿼리 
// 해당 tablespace는 물론 관련한 데이터 및 파일을 삭제한다.
DROP TABLESPACE myts INCLUDING CONTENTS AND DATAFILES;

---

Tablespace

• oracle에서 데이터베이스 저장 공간을 논리적으로 나뉜 단위를 의미한다. (즉 oracle에서 모든 데이터는 tablespace에 저장되어 관리된다.)
• tablespace는 물리적으로는 일종의 파일 단위로 존재한다.
• 백업이나 복구 등의 작업 단위가 된다.
• tablespace는 기본적으로는 하나의 파일로 생성되지만, 하나의 tablespace가 여러 개의 데이터 파일로 구성되도록 할 수 있다.
• 데이터 파일의 크기 설정을 통해 tablespace의 크기를 구성할 수 있다.
• 성능을 위해 인덱스 등과 같은 부분만을 별도의 테이블스페이스를 통해 저장 관리하거나 대규모 데이터를 가진 테이블을 파티셔닝하여 다른 테이블스페이스에 저장 관리하여 접근 속도와 성능을 향상시킬 수 있다. 
• 사용자마다 특정 tablespace를 할당함으로써 데이터 접근 권한을 제어할 수 있다.

oralce은 설치 시에 기본적으로 (1) SYSTEM Tablespace (2) SYSAUX Tablespace (3) UNDO Tablespace (4) TEMP Tablespace (5) USERS Tablespace 를 가지고 있다.

oracle 설치 시 기본으로 생성되는 tablespace

(1) SYSTEM Tablespace 

oracle 데이터베이스의 주요한 메타데이터를 관리하는 테이블스페이스로, SYS, SYSTEM 스키마는 물론 초기화 정보 및 주요한 시스템 테이블들이 저장된다.

(2) SYSAUX Tablespace 

SYSTEM 테이블스페이는를 보조하는 역할을 하며, 성능, 백업, 복구 등의 기능과 관련한 데이터를 포함하는 테이블스페이스이다.

(3) UNDO Tablespace 

트랜잭션 롤백 및 데이터 복구를 위한 정보를 저장하는 테이블 스페이스이다.

(4) TEMP Tablespace 

정렬 및 임시 작업 시에 데이터를 저장하는 테이블 스페이스이다.

(5) USERS Tablespace

일반 사용자 데이터를 저장 관리하는 테이블 스페이스이다.

---

CREATE TABLESPACE myts DATAFILE 'C:\oracle\oradata\myts.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE 500M; 쿼리는 myts라는 tablespace을 생성하고 해당 테이블스페이스에 저장되는 데이터는 C:\oracle\oradata 폴더 하위에 myts.dbf 라는 데이터 파일에 저장, 해당 데이터 파일의 크기는 100MB로 설정, AUTOEXTEND ON 즉 데이터 파일의 자동 확장을 허용하여, 데이터 증가로 공간이 부족해지면 지정된 크기만큼 데이터 파일이 자동으로 확장되도록 옵션을 설정. NEXT 5M은 데이터 파일이 확장될 때 증가하는 크기의 단위이다.  단 데이터 파일 크기의 무제한 확장을 막기 위해 해당 데이터 파일의 최대 크기는 500MB가 넘지 않도록 MAXSIZE 500M;설정한 것이다.  

** oracle에서 스키마나 사용자 생성 시 에러 발생하는 경우 ->  C##을 붙여서 사용해야 하는 경우가 있는데 이때 ALTER SESSION SET "_ORACLE_SCRIPT"=true;   쿼리를 통해 스키마나 사용자명 앞에 C##을 붙이지 않고도 생성할 수 있다. 현재 세션에서 _ORACLE_SCRIPT를 true로 설정한다는 것은 현 세션에서 생성하는 스키마를 시스템 정의 스키마처럼 생성되도록 해주는 쿼리이다.

C##은 오라클 데이터베이스에서 공통사용자(Common User)와 로컬 사용자(Local User)를 구분하기 위해 사용하는 접두사로, 공통사용자인 경우에는 사용자명 앞에 C##을 붙여야 사용자 생성이 가능하다. 공통사용자는 CDB와 PDB 모두에서 유효하다. 

2. 사용자 생성

// -> 기본 사용자 생성
CREATE USER [사용자명] IDENTIFIED BY [비밀번호];

ex) 
create user ora_user identified by 1234;

---

// -> tablespace 설정하여 사용자 생성
CREATE USER [사용자명] IDENTIFIED BY [비밀번호]

DEFAULT TABLESPACE [테이블스페이스명]

TEMPORARY TABLESPACE [임시테이블스페이스명];

ex) 
create user ora_user identified by 1234

default tablespace myts

temporary tablespace temp;

Oracle에서는 사용자와 스키마가 동일하다. 즉 사용자를 생성하면 해당 사용자가 소유하는 스키마가 자동으로 생성되게 된다. 하나의 데이터베이스 안에는 여러 개의 스키마가 존재할 수 있다.

아래의 두 번째 쿼리의 경우 사용자를 생성하는 동시에 해당 사용자가 사용할 기본 테이블스페이스를 myts로 설정한 것이다.

** 스키마 : Oracle에서 스키마는 특정 데이터베이스 사용자에 의해 소유되는 데이터베이스 객체의 집합을 의미하며, 여기에는 테이블, 뷰, 인덱스, 시퀀스, 프로시저, 트리거 등을 포함한다. 

위의 쿼리 실행 시 데이터베이스 툴인 dbeaver를 통해 확인해 보면 아래와 같이 ora_user 라는 스키마가 생성된 것을 확인할 수 있으며, 해당 스키마의 사용자명은 ora_user, 비밀번호는 1234, 스키마가 저장되는 tablespace는 myts를 가지게 된다.

3. 권한 부여

GRANT DBA TO [사용자명];

ex) grant dba to ora_user;