Tablespace 관련 명령어

■ Data file 및 Tablespace 조회
SQL> select tablespace_name, bytes, file_name from dba_data_files;

SQL> select tablespace_name, status, contents, extent_management, segment_space_management
  2  from dba_tablespaces;


■ Tablespace 생성
- 일반 Tablespace
SQL> create tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기
  3  segment space management auto;

SQL> create tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기
  3  extent space management local;

- UNDO Tablespace
SQL> create undo tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기;

- TEMP Tablespace
SQL> create temporary tablespace tablespace_name
  2  tempfile 'path/data_file_name' size 크기;

- Tablespace Block 지정
SQL> create tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기
  3  blocksize [2K | 4K | 8K | 16K | 32K]
  4  segment space management auto;


■ Tablespace 확장
SQL> alter tablespace tablespace_name
  2  datafile 'path/data_file_name' size 크기;

SQL> alter database datafile 'path/data_file_name' resize 크기;


■ Tablespace 관리
SQL> alter tablespace tablespace_name offline;
SQL> alter tablespace tablespace_name online;

SQL> alter tablespace tablespace_name rename
  2  datafile 'path/data_file_name(원본 Datafile)'
  3  to 'path/data_file_name';

SQL> alter database rename
  2  file 'path/data_file_name(원본 Datafile)'
  3  to 'path/data_file_name';


■ Tablespace 삭제
SQL> drop tablespace tablespace_name
  2  including contents and datafiles cascade constraints;