[ORACLE] TABLESPACE

1. TABLESPACE 조회

SELECT * FROM DBA_TABLESPACES;

2. 용량 조회

SELECT TABLESPACE_NAME

           , FILE_NAME

           , BYTES/1024 AS KBytes

           , RESULT/1024 AS USED_KBytes

  FROM ( SELECT E.TABLESPACE_NAME

                         , E.FILE_NAME

                         , E.BYTES

                         , (E.BYTES-SUM(F.BYTES)) AS 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

3. 생성

CREATE TABLESPACE [TABLESPACE명] DATAFILE [파일경로] SIZE [사이즈] ;

CREATE TABLESPACE USER DATAFILE '/user.dbf' SIZE  500M ;

4. 용량변경

ALTER DATABASE DATAFILE [파일경로] RESIZE [사이즈];

ALTER DATABASE DATAFILE '/user.dbf' RESIZE 700M;

5. 삭제

DROP TABLESPACE [TABLESPACE명] INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;