테이블스페이스 관리 관련 쿼리문과 DBF 파일 삭제 시 Drop

lusida0131 2023. 4. 15. 17:31

2023. 4. 15. 17:31

728x90

Tablespace란?

하나 또는 여러 개의 데이터 파일로 구성되어 있는 논리적인 데이터 저장구조이다.

DataFile이란?

DB Table의 데이터가 물리적으로 저장되는 공간이다. (Data File 1개의 최대 용량 32GB) Tablespace는 1개 이상의 DataFile로 구성되어 있다.

DataFile 확장 방법

DataFile 추가
AUTOEXTEND로 추가
RESIZE

Tablespace 용량 확인 쿼리문

SELECT    A.TABLESPACE_NAME AS "테이블스페이스명",
          A.FILE_NAME AS "파일경로",
          A.BYTES/1024/1024/1024 AS "총크기",
          (A.BYTES - B.FREE)/1024/1024/1024 AS "사용공간",
          B.FREE/1024/1024/1024 AS "여유 공간",
          TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' AS "여유공간"
FROM
(
 SELECT FILE_ID,
        TABLESPACE_NAME,
        FILE_NAME,
        SUBSTR(FILE_NAME,1,200) AS FILE_NM,
        SUM(BYTES) BYTES
   FROM DBA_DATA_FILES
 GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)
) A,
(
 SELECT TABLESPACE_NAME,
        FILE_ID,
        SUM(NVL(BYTES,0)) AS FREE
   FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME,FILE_ID
) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
 AND A.FILE_ID = B.FILE_ID
ORDER BY A.TABLESPACE_NAME;

Tablespace 관련 조회 쿼리문

select * from dba_tablespaces;

설정된 테이블 스페이스 확인

SELECT * FROM dba_data_files;

데이터가 저장되는 물리경로 및 테이블스페이스 정보

SELECT * FROM user_users;

현재 유저의 default_tablespace 확인

Tablespace 생성

create tablespace [TABLESPACENAME]
datafile '파일경로' size 10M 
autoextend on next 10M 
maxsize 100M 
uniform size 1M

datafile '파일경로' size 10M : 초기 데이터 파일 경로와 크기 설정
autoextend on next 10M : 초기 설정한 크기를 모두 사용하는 경우 자동으로 파일의 크기가 정해진 크기만큼 커지는 기능
maxsize 100M : 데이터파일이 최대로 커질 수 있는 크기 지정, 기본값 = unlimited
uniform size 1M : EXTENT 한 개의 크기를 설정

Tablespace에 Data File 추가하는 방법

1. DataFile 추가
ALTER TABLESPACE [TABLE_SPACE_NAME] ADD DATAFILE [DATA_FILE_NAME] SIZE [SIZE];
2. DataFile 추가 후 Auto Extensible 설정
ALTER TABLESPACE [TABLE_SPACE_NAME] ADD DATAFILE [DATA_FILE_NAME] SIZE [초기추가용량] 
AUTUEXTEND ON NEXT [자동증가 용량] MAXSIZE [최대 용량]

Tablespace 변경 관련 쿼리문

ALTER USER [USER_NAME] DEFAULT TABLESPACE [TABLE_SPACE_NAME];

유저의 default Tablespace 변경

ALTER TABLE [TABLE_NAME] move tablespace [TABLE_SPACE_NAME];

테이블의 Tablespace 변경

ALTER TABLESPACE RENAME A TO B

Tablespace 물리적인 파일의 이름 또는 위치변경

ALTER DATABASE MOVE DATAFILE '/원본 경로/파일 이름' TO '/이동 경로/파일 이름';

Tablespace Online dbf 파일 위치 이동

DROP TABLESPACE [TABLE_SPACE_NAME] include contents;

테이블스페이스 내 객체(테이블, 인덱스 등)를 모두 삭제

DROP TABLESPACE [TABLE_SPACE_NAME] INCLUDING contents;

테이블스페이스의 모든 세그먼트를 삭제 (데이터가 테이블스페이스는 삭제할 수 없다)

DROP TABLESPACE [TABLE_SPACE_NAME] CASCADE constranints;

삭제된 테이블스페이스 내의 테이블의 기본키와 PK를 참조하는 다른 테이블스페이스의 테이블로부터 참조무결성 제약 조건을 삭제한다.

DROP TABLESPACE [TABLE_SPACE_NAME] INCLUDING CONTENTS AND DATAFILES;

테이블 스페이스의 물리파일까지 삭제

유저 생성시 Tablespace 지정

CREATE USER [USER_NAME] IDENTIFIED BY [PASSWORD]
DEFAULT TABLESPACE [TABLESPACE_NAME]
TEMPORARY TABLESPACE [TABLESPACE_NAME];

기본(디폴트) 테이블스페이스, 임시(temp) 테이블스페이스를 명시해 줄 수 있다.
기본 테이블스페이스란 해당 사용자로 로그인한 뒤 테이블과 같은 각종 데이터베이스 객체가 저장되는 테이블스페이스이다.
임시 테이블스페이스는 해당 사용자가 사용하는 디폴트 임시 테이블스페이스를 말한다.

실수로 dbf 파일을 삭제해버렸다면

shutdown immediate 시 테이블 스페이스를 찾을 수 없다고 에러가 난다.

해결 방법 1

테이블스페이스 오프라인 드롭을 하자

alter database datafile 'file path' offline drop;

해결 방법 2

1. shutdown abort 로 강제 종료
2. startup nomount;
3. alter database mount;
4. alter database datafile 'file path' offline
→ control 파일 정보에 해당 dbf 파일 offline 상태 알려준다.
5. alter database open;

참고

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=tawoo0&logNo=221547724506

https://coding-factory.tistory.com/411

https://itkevin.tistory.com/36

https://yoo-hyeok.tistory.com/136

https://dreamingnote.tistory.com/entry/RESIZE로-TABLESPACE의-data-file-크기-변경

http://bluehb.egloos.com/m/1507700