TABLESPACE 추가 방법

용량 조회

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

SELECT * FROM DBA_TABLESPACES;

 

2. 테이블스페이스별 용량 확인 쿼리문(MB 단위)

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) agroup by a.tablespace_nameorder by tablespace;

 

 

TB SPACE 변경 방법

 

alter tablespace 테이블스페이스명

 add datafile '데이터파일 경로' size 데이터파일 사이즈 ;

 

alter tablespace 테이블명 add datafile '경로' size 크기 autoextend on/off

 alter tablespace SYSTEM  add datafile '/orInven/ORACLE19/system02.dbf' size 5M autoextend on;

 

 

TB SPACE 추가 방법

create tablespace 테이블명 datafile '경로' size 크기 autoextend on next 최대로 키울수있는크기설정 maxsize unlimited;

 

create tablespace SYSTEM datafile '/orInven/ORACLE19/system02.dbf' size 1024M autoextend on next 100M maxsize unlimited;

 

확인 방법

col member for a30

select a.group#,a.type,a.member,b.bytes/1024/1024,b.archived,b.status

from v$logfile a, v$log b

where a.group#=b.group#

order by 1,2;

 

잘만들어졌는지 확인!

SELECT tablespace_name, block_size/1024 FROM dba_tablespaces;

 

 

테이블 삭제하는 방법

drop tablespace 테이블명 including contents [and/keep] datafiles [cascade constraints];