-- 일반 테이블스페이스 생성
SQL> create tablespace khyup
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf' size 10M
3 segment space management auto;
-- 일반 테이블스페이스 조회
SQL> select tablespace_name, status, contents, extent_management, segment_space_management
2 from dba_tablespaces;
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
-- 각 데이터 파일의 실제 사용량 확인
SQL> set line 200;
SQL> col file# for 999;
SQL> col ts_name for a10;
SQL> col total_blocks for 9999999;
SQL> col used_blocks for 9999999;
SQL> col pct_used for a10
SQL> select distinct d.file_id file#,
2 d.tablespace_name ts_name,
3 d.bytes/1024/1024 MB,
4 d.bytes/8192 total_blocks,
5 sum(e.blocks) used_blocks,
6 to_char(nvl(round(sum(e.blocks)/(d.bytes/8192),4),0) * 100,'09.00') || ' %' pct_used
7 from dba_extents e, dba_data_files d
8 where d.file_id = e.file_id
9 group by d.file_id, d.tablespace_name, d.bytes
10 order by 1, 2;
-- 테이블스페이스 용량 관리하기
-- 수동으로 테이블스페이스에 Data File 추가하기
SQL> alter tablespace khyup add datafile
2 '/ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf' size 20M;
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
-- 수동으로 Data File 크기 늘려주기
SQL> alter database datafile
2 '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf' resize 20M;
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
-- 자동으로 Data File 크기 증가시키도록 설정
SQL> alter database datafile
2 '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf' autoextend on;
SQL> set line 200
SQL> col tablespace_name for a10
SQL> col file_name for a50
SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "Auto", online_status
2 from dba_data_files;
-- (번외) autoextend 테스트
SQL> create table khyup
2 (no number);
SQL> begin
2 for i in 1..500000 loop
3 insert into khyup values(i);
4 end loop;
5 commit;
6 end;
7 /
SQL> select tablespace_name, bytes/1024/1024 MB, file_name, checkpoint_change#
2 from dba_data_files;
-- Tablespace Offline
SQL> alter tablespace khyup offline;
SQL> alter tablespace khyup offline temporary;
-- DB가 archive log mode 일 경우 Data File offline 방법
SQL> alter database datafile '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf' offline;
-- DB가 no archive log mode 일 경우 Data File offline 방법
SQL> alter database datafile
'/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf' offline drop;
SQL> select file#, name, status from v$datafile;
SQL> alter tablespace khyup online;
SQL> alter tablespace khyup offline temporary;
SQL> recover tablespace khyup;
-- 테이블 스페이스 삭제하기
SQL> drop tablespace khyup including contents and datafiles;
offline 되는 Tablespace의 Data File 이동하기
parameter file
에는 control file에 대한 경로가 들어있고,
control file
에는 data file, redo log file에 대한 경로가 들어 있습니다.
SQL> alter tablespace khyup offline;
$ cp /ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf /home/oracle/disk1/khyup01.dbf
$ cp /ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf /home/oracle/disk2/khyup02.dbf
SQL> select name, status from v$datafile;
SQL> alter tablespace khyup rename
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf'
3 to '/home/oracle/disk1/khyup01.dbf';
SQL> alter tablespace khyup rename
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf'
3 to '/home/oracle/disk2/khyup02.dbf';
SQL> alter tablespace khyup online;
SQL> select name, status from v$datafile;
offline 되지 않는 Tablespace(system tablespace, undo tablespace, default temporary tablespace)의 Data File 이동하기
SQL> shutdown immediate;
SQL> startup mount;
$ cp /ORA19/app/oracle/oradata/ORACLE19/system01.dbf /home/oracle/disk3/system01.dbf
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/system01.dbf'
3 to '/home/oracle/disk3/system01.dbf';
SQL> select name, status from v$datafile;
SQL> alter database open;
Redo Log File 이동하기
SQL> select status from v$instance;
SQL> shutdown immediate;
SQL> startup mount;
SQL> select member from v$logfile;
SQL> !cp <기존 경로> <새로운 경로>
SQL> alter database rename
2 file '<기존 경로>'
3 to '<새로운 경로>';
SQL> select member from v$logfile;
SQL> alter database open;
example
Tablespace를 생성합니다.
/ORA19/app/oracle/oradata/ORACLE19/example01.dbf
: 10M
khyup
Tablespace를 생성합니다.
/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf
: 10M
/ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf
: 20M
SQL>
Control File, Redo Log File, Data File을 아래와 같이 이동시키세요.
/home/oracle/disk1/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
/home/oracle/disk2/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log
/home/oracle/disk3/control03.ctl, undotbs01.dbf
/home/oracle/disk4/system01.dbf, sysaux01.dbf, khyup01.dbf
/home/oracle/disk5/khyup02.dbf, example01.dbf
-- Control File 작업
SQL> select status from v$instance;
SQL> show parameter spfile;
SQL> select name from v$controlfile;
SQL> !mkdir /home/oracle/disk1 /home/oracle/disk2 /home/oracle/disk3
SQL> !cp <기존 Control File 경로> /home/oracle/disk1/control01.dbf
SQL> !cp <기존 Control File 경로> /home/oracle/disk2/control02.dbf
SQL> !cp <기존 Control File 경로> /home/oracle/disk3/control03.dbf
SQL> alter system set control_files='/home/oracle/disk1/control01.ctl',
2 '/home/oracle/disk2/control02.ctl',
3 '/home/oracle/disk3/control03.ctl'
4 scope=spfile;
SQL> shutdown immediate;
SQL> startup;
SQL> select status from v$instance;
SQL> select name from v$controlfile;
SQL> !rm <기존 Control File 경로>
-- Redo Log File 작업
SQL> select status from v$instance;
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
2 from v$logfile a, v$log b
3 where a.group#=b.group#
4 order by 1, 2;
SQL> shutdown immediate;
SQL> startup mount;
SQL> select status from v$instance;
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/redo01.log /home/oracle/disk1/redo01_a.log
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/redo02.log /home/oracle/disk1/redo02_a.log
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/redo03.log /home/oracle/disk1/redo03_a.log
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo01.log'
3 to '/home/oracle/disk1/redo01_a.log';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo02.log'
3 to '/home/oracle/disk1/redo02_a.log';
SQL> alter database rename
2 file '/ORA19/app/oracle/oradata/ORACLE19/redo03.log'
3 to '/home/oracle/disk1/redo03_a.log';
SQL> select member from v$logfile;
SQL> alter database open;
SQL> select status from v$instance;
SQL> alter database add logfile member
2 '/home/oracle/disk2/redo01_b.log' to group 1;
SQL> alter database add logfile member
2 '/home/oracle/disk2/redo02_b.log' to group 2;
SQL> alter database add logfile member
2 '/home/oracle/disk2/redo03_b.log' to group 3;
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
2 from v$logfile a, v$log b
3 where a.group#=b.group#
4 order by 1, 2;
SQL> !rm /ORA19/app/oracle/oradata/ORACLE19/redo01.log /ORA19/app/oracle/oradata/ORACLE19/redo02.log /ORA19/app/oracle/oradata/ORACLE19/redo03.log
-- Data File 작업(system, undo tablespace)
SQL> select status from v$instance;
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
SQL> shutdown immediate;
SQL> startup mount;
SQL> !mkdir /home/oracle/disk4 /home/oracle/disk5
SQL> !cp <기존 system01.dbf 파일 경로> /home/oracle/disk4/system01.dbf
SQL> !cp <기존 undotbs01.dbf 파일 경로> /home/oracle/disk3/undotbs01.dbf
SQL> alter database rename
2 file '<기존 system01.dbf 파일 경로>'
3 to '/home/oracle/disk4/system01.dbf';
SQL> alter database rename
2 file '<기존 undotbs01.dbf 파일 경로>'
3 to '/home/oracle/disk3/undotbs01.dbf';
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
SQL> alter database open;
-- Data File 작업(일반 Tablespace: sysaux01.dbf, khyup01.dbf, khyup02.dbf, example01.dbf)
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 dba_data_files;
SQL> alter tablespace sysaux offline;
SQL> alter tablespace khyup offline;
SQL> alter tablespace example offline;
SQL> !cp <기존 sysaux Tablespace 경로> /home/oracle/disk4/sysaux01.dbf
SQL> !cp <기존 khyup Tablespace 경로> /home/oracle/disk4/khyup01.dbf
SQL> !cp <기존 khuyp Tablespace 경로> /home/oracle/disk5/khyup02.dbf
SQL> !cp <기존 example Tablespace 경로> /home/oracle/disk5/example01.dbf
SQL> alter tablespace sysaux rename
2 datafile '<기존 sysaux Tablespace 경로>'
3 to '/home/oracle/disk4/sysaux01.dbf';
SQL> alter tablespace khyup rename
2 datafile '<기존 khyup Tablespace 경로>'
3 to '/home/oracle/disk4/khyup01.dbf';
SQL> alter tablespace khyup rename
2 datafile '<기존 khyup Tablespace 경로>'
3 to '/home/oracle/disk5/khyup02.dbf';
SQL> alter tablespace example rename
2 datafile '<기존 example Tablespace 경로>'
3 to '/home/oracle/disk5/example01.dbf';
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
2 from dba_data_files;
SQL> alter tablespace sysaux online;
SQL> alter tablespace khyup online;
SQL> alter tablespace example online;