Oracle :: TableSpace 용량변경

-- 테이블 스페이스 용량확인

select B.TS_NAME as "TableSpace",
       TO_CHAR(SUM(NVL(A.TOT_BYTES/1024/1024,0)),'999,999,999,999') AS "전체용량(M)",
       TO_CHAR(SUM(NVL(A.TOT_BYTES/1024/1024,0))-SUM(NVL(B.TOT_BYTES/1024/1024,0)),'999,999,999,999') AS "사용한공간(M)",     
       ROUND(((SUM(NVL(A.TOT_BYTES,0))-SUM(NVL(B.TOT_BYTES,0)))/SUM(A.TOT_BYTES))*100,1) AS "사용한공간(%)",
       TO_CHAR(SUM(NVL(B.TOT_BYTES/1024/1024,0)),'999,999,999,999') AS "남은용량(M)",
        ROUND((SUM(NVL(B.TOT_BYTES,0))/SUM(A.TOT_BYTES)*100),1) AS "남은용량(%)"
FROM (SELECT TABLESPACE_NAME AS "TS_NAME" , SUM(BYTES) AS "TOT_BYTES"
      FROM DBA_FREE_SPACE
      GROUP BY TABLESPACE_NAME) B, (SELECT TABLESPACE_NAME AS "TS_NAME",SUM(BYTES)  AS "TOT_BYTES"
                                    FROM DBA_DATA_FILES
                                    GROUP BY TABLESPACE_NAME) A
WHERE B.TS_NAME=A.TS_NAME
GROUP BY B.TS_NAME
ORDER BY B.TS_NAME;

-- 테이블 스페이스 용량확장

ALTER DATABASE DATAFILE '데이터파일경로/데이터파일이름' RESIZE [조정후용량]M;
EX) ALTER DATABASE DATAFILE '/data1/oradata/ora920/users01.dbl' RESIZE 5000M

-- 테이블 스페이스 정보 보기

select tablespace_name, file_name from dba_data_files;


출처 :: Holic's Tistory
http://holiclove.tistory.com/entry/SQL-테이블스페이스-용량-변경