Oracle 테이블스페이스 생성, 연결, 조회, 용량 조회

Oracle 테이블스페이스 생성, 연결, 조회, 용량 조회

 

테이블 스페이스를 생성하고 조회하는 방법에 대해서 정리한다.

 

1. 테이블 스페이스 생성

예시 500 MB로 생성하고 공간을 다 사용했을경우에는 자동 증가 시킨다.

 

-- 테이블 스페이스 생성
create tablespace 테이블스페이스명
datafile 'C:\oracle\test_tablespace.dbf' size 500m
AUTOEXTEND ON NEXT 10M; 

-- 계정 생성 & 테이블 스페이스 연결
CREATE USER 유저명 IDENTIFIED BY 비밀번호
DEFAULT TABLESPACE 테이블스페이스명 
QUOTA UNLIMITED ON 테이블스페이스명; -- QUOTA size 무제한

grant unlimited tablespace to 유저명; -- QUOTA size 권한을 따로 줄수도 있음.
alter user 유저명 quota unlimited on 테이블스페이스명;  -- QUOTA 권한 제거

 

 

2. 테이블 스페이스 정보 조회

SELECT * FROM DBA_TABLESPACES;

 

 

3.  테이블 스페이스별 용량 확인 방법

 

select   substr(a.tablespace_name,1,30) tablespace,
         round(sum(a.total1)/1024/1024,1) "TotalMB",
         round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",
         round(sum(a.sum1)/1024/1024,1) "FreeMB",
         round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"
from
         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
          from     dba_free_space
          group by tablespace_name
          union
          select   tablespace_name,sum(bytes) total1,0,0,0
          from     dba_data_files
          group by tablespace_name) a
group by a.tablespace_name
order by tablespace;

계정에 따로 tablespace 지정을 안하면 USERS에 담긴다.

테이블 스페이스이름 , 총용량, 사용용량, 여유용량, 사용율 항목을 확인 할수 있다.

 

 

4. 테이블 스페이스 파일 위치 확인

 

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024 AS MBytes, RESULT/1024 AS USE_MBytes FROM
  (
  SELECT E.TABLESPACE_NAME,E.FILE_NAME,E.BYTES, (E.BYTES-SUM(F.BYTES)) RESULT
  FROM DBA_DATA_FILES E, DBA_FREE_SPACE F
  WHERE E.FILE_ID = F.FILE_ID
  GROUP BY E.TABLESPACE_NAME, E.FILE_NAME, E.BYTES
  ) A;