[Oracle] TableSpace 생성

기본적으로 테이블 스페이스를 지정하지 않고 테이블을 생성하면, 기본적으로 할당 된 테이블스페이스에 생성된다.

그러나 테이블스페이스를 따로 생성 후 지정하여 테이블 생성 시, 해당 테이블은 지정된 테이블 스페이스에 생성된다.

 

테이블스페이스를 생성하면 데이터 저장소를 분리하고 관리하는 데 유리한 측면이 있다.

대규모 데이터나 특정 애플리케이션의 데이터를 별도의 테이블스페이스에 지정하여,

성능을 최적화하거나 관리 효율성을 높일 수 있다.

 

주요 차이점
[데이터 저장 위치]

• 기본 테이블스페이스 : 테이블이 사용자의 기본 테이블스페이스에 저장된다.
• 지정된 테이블스페이스 : 테이블이 특정 테이블스페이스에 저장된다.

[관리 및 성능]

• 기본 테이블스페이스 : 모든 테이블이 기본 테이블스페이스에 저장되므로 관리가 간단하지만, 데이터가 많아지면 성능 저하가 발생할 수 있다.
• 지정 된 테이블스페이스 : 데이터 분리 및 관리가 용이하며, 성능 최적화에 유리하다.

[공간 할당]

• 기본 테이블스페이스 : 기본 테이블스페이스의 설정에 따라 공간이 할당된다.
• 지정된 테이블스페이스 : 지정된 테이블스페이스의 설정에 따라 공간이 할당된다.

대규모 데이터베이스나 특정 요구 사항이 있는 경우, 테이블스페이스를 지정하여 테이블을 생성하는 것이 더 적합하다.

기본 테이블스페이스를 사용하는 경우, 설정이 간단하지만 성능 및 관리 측면에서 제한이 있을 수 있다.

 

테이블스페이스를 생성하려고 했는데 권한이 부족하여 다음과 같은 에러가 나타났다.

SQL Error [1031] [42000]: ORA-01031: 권한이 불충분합니다

 

따라서 권한을 먼저 부여하는 것부터 해보려고 한다.

---

1. 권한 부여

테이블스페이스를 생성하려면 CREATE TABLESPACE 권한이 필요하다.

일반적으로 DBA(데이터베이스 관리자) 계정으로 로그인하여 권한을 부여할 수 있다.

 

1) 관리자로 로그인

sqlplus sys/password@orclpdb as sysdba

(orcl인 사람은 orcl로 꼭 바꿔서 입력할 것 !)

2) 사용자에게 권한 부여

GRANT CREATE TABLESPACE TO your_username;
GRANT UNLIMITED TABLESPACE TO your_username;

3) 권한 부여 후 테이블스페이스 생성

(cmd에서 sqlplus에 로그인하여 생성해도 되지만, 나는 로그인 된 DBeaver에서 바로 명령어를 실행했다.)

[내 테이블스페이스 지정 경로]

C:\app\oracle_server\oradata\orcl\orclpdb

CREATE TABLESPACE 테이블스페이스_이름01
DATAFILE 'C:\app\oracle_server\oradata\orcl\orclpdb\테이블스페이스_이름01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE 테이블스페이스_이름02
DATAFILE 'C:\app\oracle_server\oradata\orcl\orclpdb\테이블스페이스_이름02.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE 테이블스페이스_이름03
DATAFILE 'C:\app\oracle_server\oradata\orcl\orclpdb\테이블스페이스_이름03_datafile.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 50M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

4) 테이블 생성

CREATE TABLE "스키마이름"."테이블이름" 
   ( "컬럼이름1" 데이터타입 NOT NULL ENABLE, 
     "컬럼이름2" NUMBER NOT NULL ENABLE, 
     PRIMARY KEY ("컬럼이름1", "컬럼이름2")
   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
   STORAGE(INITIAL 32505856 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "테이블스페이스이름03"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
   NOCOMPRESS LOGGING
   STORAGE(INITIAL 55574528 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "테이블스페이스이름03";

▶인덱스 및 테이블스페이스 SQL문 설명

1. 테이블 스페이스 (TableSpace)

데이터베이스에서 물리적인 저장소를 논리적으로 구분한 것이다.

테이블, 인덱스, 기타 데이터 오브젝트를 저장하는 데 사용된다.

TABLESPACE "테이블스페이스이름03"

위 구문은 테이블이 "테이블스페이스이름03"이라는 이름의 테이블스페이스에 저장된다는 의미이다.

 

2. 인덱스 지정 (Index Specification)

인덱스는 데이터베이스에서 검색 성능을 향상시키기 위해 사용된다.

USING INDEX절을 통해 기본키 제약 조건에 사용될 인덱스를 생성하고, 

해당 인덱스의 스토리지 및 기타 매개변수를 설정할 수 있다.

USING INDEX 
PCTFREE 10 
INITRANS 2 
MAXTRANS 255 
COMPUTE STATISTICS 
STORAGE(
    INITIAL 32505856 
    NEXT 1048576 
    MINEXTENTS 1 
    MAXEXTENTS 2147483645
    PCTINCREASE 0 
    FREELISTS 1 
    FREELIST GROUPS 1 
    BUFFER_POOL DEFAULT 
    FLASH_CACHE DEFAULT 
    CELL_FLASH_CACHE DEFAULT
)

• PCTFREE 10 : 블록의 여유 공간 비율을 설정한다. 여기선 블록 당 10%의 여유 공간을 남겨둔다. 이는 업데이트 시 데이터를 추가할 공간을 의미한다.
• INITRANS 2 : 초기 트랜잭션 슬롯 수를 설정한다. 여기서는 블록 당 2개의 트랜잭션을 허용한다.
• MAXTRANS 255 : 최대 트랜잭션 슬롯 수를 설정한다. 여기서는 블록 당 최대 255개의 트랜잭션을 허용한다.
• COMPUTE STATISTICS : 인덱스 생성 후 통계를 수집하여 옵티마이저가 더 나은 쿼리 계획을 세울 수 있도록 한다.
• STORAGE : 인덱스의 저장 매개변수를 설정한다.
  • INITIAL 숫자 : 초기 할당된 저장 공간(바이트 단위)
  • NEXT 숫자 : 다음 할당된 저장 공간(바이트 단위)
  • MINEXTENTS 숫자 : 최소 할당된 익스텐트 수
  • MAXEXTENTS 숫자 : 최대 할당된 익스텐트 수
  • PCTINCREASE 0 : 추가 익스텐트 할당 시 증가율 설정. 여기서는 증가율 0%
  • FREELISTS 1 : 프리리스트 수를 설정. 프리리스트는 사용 가능한 블록 목록이다.
  • FREELIST GROUPS 1 : 프리리스트 그룹 수 설정
  • BUFFER_POOL DEFAULT : 버퍼 풀 설정. 여기서는 기본 버퍼 풀을 사용한다.
  • FLASH_CACHE DEFAULT : 플래스 캐시 설정. 여기서는 기본 플래시 캐시를 사용한다.
  • CELL_FLASH_CACHE DEFAULT : 셀 플래시 캐시 설정. 여기서는 기본 셀 플래시 캐시를 사용한다.

3. SEGMENT CREATION IMMEDIATE

해당 구문은 테이블 생성 시 즉시 세그먼트를 할당하도록 지정한다.

Oracle 11g부터는 기본적으로 DEFERRED가 적용되어 테이블에 데이터가 삽입될 때까지 세그먼트 할당을 지연할 수 있다.

SEGMENT CREATION IMMEDIATE

 

(storage의 숫자는 내가 임의로 변경해놓았다.)

5) 코멘트 코드 작성 

COMMENT ON COLUMN 스키마이름.테이블이름.컬럼이름 IS '주석';

 

▶ SQL Error [1031] [42000]: ORA-01031: 권한이 불충분합니다

내가 테이블스페이스 이름을 변경하고 싶어서 삭제하려는데 다음과 같은 에러가 나타났다.

SQL Error [1031] [42000]: ORA-01031: 권한이 불충분합니다

해당 오류는 데이터베이스 사용자가 테이블스페이스를 삭제할 권한이 없다는 뜻이다.

이를 해결하기 위해서는 테이블스페이스를 삭제할 수 있는 권한이 필요하다.

 

1. 관리자로 로그인

sqlplus sys/패스워드@orclpdb as sysdba

2. DBA 권한 부여

GRANT DBA TO 사용자_이름;

GRANT DROP TABLESPACE TO 사용자_이름;

▶ 테이블스페이스 삭제

테이블스페이스 이름과 테이블스페이스 파일의 이름을 동일하게 하고 싶어서

삭제하고 다시 생성하려고 한다.

 

1. SYSDBA 권한으로 로그인

SYSDBA권한을 가진 사용자로 로그인한다. 일반적으로 이는 SYS 사용자이다.

sqlplus sys as sysdba

2. 로그인 후 비밀번호 입력

3. PDB 확인

여러 PDB가 있는 경우, 현재 접속한 PDB가 맞는지 확인해야 한다.

아래 명령어는 현재 접속한 컨테이너의 이름을 출력한다. ORCLPDB가 맞는지 확인해보자.

SHOW CON_NAME;

만약 다른 PDB에 접속해야 한다면, 4번 명령어를 사용하여 PDB로 접속할 수 있다.

4. 다른 PDB에 접속하기

ALTER SESSION SET CONTAINER = orclpdb;

5. 테이블스페이스 삭제 

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;