Oracle 테이블 스페이스 추가

-- Data File별 상태보기
SELECT   A.tablespace_name,
         A.file_name,
         to_char(TRUNC(NVL(SUM(B.BYTES/1048576),0) / (A.BYTES/1048576),4)*100)||'%' pct_free,
       NVL(TRUNC(SUM(B.BYTES/1048576),2),0)||'MB' FREE_SPACE,
       TRUNC((A.BYTES/1048576)-NVL(SUM(B.BYTES/1048576),2),0)||'MB' USED_SPACE,
       TRUNC(A.BYTES/1048576,2)||'MB' FILE_SIZE,
         autoextensible,
         status
FROM   DBA_DATA_FILES A, DBA_FREE_SPACE B
WHERE  A.FILE_ID=B.FILE_ID (+)
GROUP BY A.tablespace_name, A.file_name,
        A.bytes/1048576,autoextensible,status,A.BYTES
UNION
SELECT   AA.tablespace_name,
         AA.file_name,
         to_char(TRUNC(NVL(SUM(BB.BYTES/1048576),0) / (AA.BYTES/1048576),4)*100)||'%' pct_free,
       NVL(TRUNC(SUM(BB.BYTES/1048576),2),0)||'MB' FREE_SPACE,
         TRUNC((AA.BYTES/1048576)-NVL(SUM(BB.BYTES/1048576),2),0)||'MB' USED_SPACE,
         TRUNC(AA.BYTES/1048576,2)||'MB' FILE_SIZE,
       autoextensible,
         status
FROM     DBA_TEMP_FILES AA, DBA_FREE_SPACE BB
WHERE    AA.FILE_ID=BB.FILE_ID (+)
GROUP BY  AA.tablespace_name,AA.FILE_NAME,AA.BYTES/1048576,autoextensible,status,AA.BYTES
ORDER BY 1,2;

 

--새로운 데이터 파일을 기존 테이블 스페이스에  추가. 
-- system 권한. (system, manager)
ALTER TABLESPACE 테이블스페이스명
            ADD DATAFILE '/data01/oradata/테이블스페이스명_02.dbf(이름도 기존 이름에 1을 더한다.)' SIZE 2048M
            AUTOEXTEND OFF;

-- AUTOEXTEND 수정

-- ON : 자동 확장     

ALTER DATABASE DATAFILE '/data01/oradata/테이블스페이스명_02.dbf' AUTOEXTEND ON;