Oracle Tablespace 생성/수정/삭제 (ASM 사용)

 

---

Oracle Tablespace 생성/수정/삭제 (ASM 사용)

---

 

---

1. 암호화 테이블스페이스 생성/삭제

-- CREATE Tablespace
CREATE SMALLFILE TABLESPACE TSKG01DT DATAFILE 
  '+DATA' SIZE 30G AUTOEXTEND OFF 
  ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);

-- DROP Tablespace
DROP TABLESPACE TSKG01DT INCLUDING CONTENTS AND DATAFILES;

 

2. 일반 테이블 스페이스 생성/추가

-- CREATE Tablespace
CREATE SMALLFILE TABLESPACE TSKG01DT DATAFILE
  '+DATA' SIZE 30G AUTOEXTEND OFF;

CREATE TABLESPACE TSKG01DT DATAFILE 
  '+DATA' SIZE 10M AUTOEXTEND ON MAXSIZE 30G
 ,'+DATA' SIZE 10M AUTOEXTEND ON MAXSIZE 30G;

-- ADD DATAFILE
ALTER TABLESPACE TSKG01DT ADD DATAFILE 
  '+DATA' SIZE 30G AUTOEXTEND OFF;
	
ALTER TABLESPACE TSKG01DT ADD DATAFILE
  '+DATA' SIZE 10M AUTOEXTEND ON MAXSIZE 30G
 ,'+DATA' SIZE 10M AUTOEXTEND ON MAXSIZE 30G;

-- DROP Tablespace
DROP TABLESPACE TSKG01DT INCLUDING CONTENTS AND DATAFILES;

 

3. UNDO 테이블 스페이스 생성/추가/삭제

-- CREATE UNDO Tablespace
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE
  '+DATA' SIZE 30G AUTOEXTEND OFF;

-- ADD Datafile UNDO Tablespace
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE
 '+DATA' SIZE 30G AUTOEXTEND OFF;

-- DROP UNDO Tablespace
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

 

4. TEMP 테이블 스페이스 생성/추가/삭제

-- CREATE TEMP Tablespace
CREATE BIGFILE TEMPORARY TABLESPACE TEMP_BATCH TEMPFILE SIZE 1024G AUTOEXTEND OFF;
CREATE SMALLFILE TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 30G AUTOEXTEND OFF;

-- ADD Tempfile TEMP Tablespace
ALTER TABLESPACE TEMP ADD TEMPFILE SIZE 30G AUTOEXTEND OFF;

-- DROP TEMP Tablespace
DROP TABLESPACE TEMP INCLUDING CONTESNTS AND DATAFILES;
	
-- Change Default TEMP Tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;