/********************************************************************************************
-- Title : [8i] 테이블스페이스와 데이터 파일 관리(OLN)
-- Reference : OLN
-- Key word : tablespace data file
********************************************************************************************/
/****************************************************************************************
-- 관리용
****************************************************************************************/
-- tablespace.sql
select tablespace_name, status, contents, logging, extent_management
allocation_type, plugged_in,
segment_space_management -- 9i 이상
from dba_tablespaces
/
-- data_file.sql
select tablespace_name, bytes, file_name
from dba_data_files
order by relative_fno
/
-- datafile.sql
select d.tablespace_name, d.file_name,
to_char(d.bytes/1024/1024, '999,999.00') "Size(MB)",
d.status, v.status, v.enabled, d.autoextensible,
to_char(d.maxbytes/1024/1024, '999,999.00') "MaxSize(MB)"
from dba_data_files d, v$datafile v
where d.file_name = v.name
order by d.tablespace_name, d.file_name
/
-- datafile_ckpt.sql
select file#, name, status, enabled, checkpoint_change#
from v$datafile
/
-- datafile_num.sql
select d.name , d.file# "FILE_NO", t.name , t.ts# "TS_NO"
from v$datafile d, v$tablespace t
where d.ts# = t.ts#
/
-- datafile_size.sql
SELECT file_name
, d.tablespace_name
, d.bytes as "file_size"
, NVL(SUM(e.bytes),0) as "bytes_used"
, ROUND(NVL(SUM(e.bytes),0) / (d.bytes), 4) * 100 as "percent_used"
, d.bytes - NVL(SUM(e.bytes),0) as "bytes_free"
FROM dba_extents e, dba_data_files d
WHERE d.file_id = e.file_id (+)
GROUP BY file_name, d.tablespace_name, d.file_id, d.bytes, status
ORDER BY d.tablespace_name, d.file_id
/
-- free_space.sql
select tablespace_name, totsize "TotSize(K)",
to_char(frsize*100/totsize, '999.00') "Free(%)", maxsize "MaxExt(K)"
from ( select sum(bytes)/1024 frsize, max(bytes)/1024 maxsize, tablespace_name
from dba_free_space group by tablespace_name) fr,
( select sum(bytes)/1024 totsize, tablespace_name tname
from dba_data_files group by tablespace_name) tt
where fr.tablespace_name = tt.tname
/
-- ts_quota.sql
select tablespace_name, username, blocks, max_blocks, bytes, max_bytes
from dba_ts_quotas
/
/****************************************************************************************
-- 테이블스페이스와 데이터 파일 확인
****************************************************************************************/
SQL> SELECT tablespace_name, file_name
2 FROM dba_data_files;
TABLESPACE_NAME FILE_NAME
---------------- -------------------------------------------------------
SYSTEM /export/home/oracle8i/iORCL/data/system01.dbf
TOOLS /export/home/oracle8i/iORCL/systs/tools01.dbf
RBS /export/home/oracle8i/iORCL/systs/rbs01.dbf
TEMP /export/home/oracle8i/iORCL/systs/temp01.dbf
USERS /export/home/oracle8i/iORCL/systs/users01.dbf
INDX /export/home/oracle8i/iORCL/systs/indx01.dbf
9 rows selected.
/****************************************************************************************
-- 테이블 스페이스 생성
****************************************************************************************/
SQL> CREATE TABLESPACE app_data
2 DATAFILE '/export/home/oracle8i/iORCL/data/app_data_01.dbf' SIZE 100M,
3 '/export/home/oracle8i/iORCL/data/app_data_02.dbf' SIZE 100M
4 MINIMUM EXTENT 500K
5 DEFAULT STORAGE ( INITIAL 500K
6 NEXT 500K
7 MAXEXTENTS 500
8 PCTINCREASE 0);
Tablespace created. -- 본 예는 기존 TS에 추가된 것임.
SQL> SELECT tablespace_name, file_name
2 FROM dba_data_files
3 WHERE tablespace_name = 'APP_DATA';
TABLESPACE_NAME FILE_NAME
---------------- -------------------------------------------------------
APP_DATA /export/home/oracle8i/iORCL/data/app_data_01.dbf
APP_DATA /export/home/oracle8i/iORCL/data/app_data_02.dbf
/****************************************************************************************
-- 지역적으로 관리되는 테이블 스페이스 예
****************************************************************************************/
SQL> CREATE TABLESPACE user_data
2 DATAFILE '/export/home/oracle8i/iORCL/data/user_data_01.dbf' SIZE 50M
3 EXTENT MANAGEMENT LOCAL
4 UNIFORM SIZE 5M;
Tablespace created.
SQL> SELECT tablespace_name, file_name
2 FROM dba_data_files;
3 WHERE tablespace_name = 'USER_DATA';
TABLESPACE_NAME FILE_NAME
---------------- -------------------------------------------------------
USER_DATA /export/home/oracle8i/iORCL/data/user_data_01.dbf
/****************************************************************************************
-- 임시 테이블스페이스 생성 예
****************************************************************************************/
SQL> CREATE TEMPORARY TABLESPACE user_temp
2 TEMPFILE '/export/home/oracle8i/iORCL/data/user_temp_01.dbf' SIZE 50M
3 EXTENT MANAGEMENT LOCAL
4 UNIFORM SIZE 5M;
Tablespace created.
/****************************************************************************************
-- 테이블스페이스에 데이터 파일 추가
****************************************************************************************/
SQL> ALTER TABLESPACE app_data
2 ADD DATAFILE '/export/home/oracle8i/iORCL/data/app03.dbf' SIZE 50M;
Tablespace altered.
SQL> SELECT tablespace_name, file_name
2 FROM dba_data_files
3 WHERE tablespace_name = 'APP_DATA';
TABLESPACE_NAME FILE_NAME
---------------- -------------------------------------------------------
APP_DATA /export/home/oracle8i/iORCL/data/app_data_01.dbf
APP_DATA /export/home/oracle8i/iORCL/data/app_data_02.dbf
APP_DATA /export/home/oracle8i/iORCL/data/app03.dbf /* 추가 확인 */
/****************************************************************************************
-- 데이터 파일 자동 확장 예
****************************************************************************************/
SQL> ALTER TABLESPACE app_data
2 ADD DATAFILE
3 '/export/home/oracle8i/iORCL/data/app04.dbf' SIZE 30M
4 AUTOEXTEND ON NEXT 5M
5 MAXSIZE 30M;
Tablespace altered.
SQL> SELECT tablespace_name, file_name, autoextensible
2 FROM dba_data_files
3 WHERE tablespace_name = 'APP_DATA';
TABLESPACE_NAME FILE_NAME AUT
---------------- ------------------------------------------------- ----
APP_DATA /export/home/oracle8i/iORCL/data/app_data_01.dbf NO
APP_DATA /export/home/oracle8i/iORCL/data/app_data_02.dbf NO
APP_DATA /export/home/oracle8i/iORCL/data/app03.dbf NO
APP_DATA /export/home/oracle8i/iORCL/data/app04.dbf YES /* 추가 확인 */
/****************************************************************************************
-- 수동으로 데이터 파일의 크기 변경
****************************************************************************************/
SQL> ALTER DATABASE
2 DATAFILE '/export/home/oracle8i/iORCL/data/app_data_02.dbf' RESIZE 5M;
/* 10M → 5M 로 변경 */
Database altered.
SQL> SELECT tablespace_name, file_name, bytes
2 FROM dba_data_files
3 WHERE tablespace_name = 'APP_DATA';
TABLESPACE_NAME FILE_NAME BYTES
---------------- ------------------------------------------------- ----------
APP_DATA /export/home/oracle8i/iORCL/data/app_data_01.dbf 104857600
APP_DATA /export/home/oracle8i/iORCL/data/app_data_02.dbf 5242880 /* 축소 */
APP_DATA /export/home/oracle8i/iORCL/data/app03.dbf 52428800
APP_DATA /export/home/oracle8i/iORCL/data/app04.dbf 31457280
/****************************************************************************************
-- 디폴트 스토리지 설정 변경
****************************************************************************************/
SQL> ALTER TABLESPACE app_data
2 MINIMUM EXTENT 2M;
Tablespace altered.
SQL> ALTER TABLESPACE app_data
2 DEFAULT STORAGE (INITIAL 2M
3 NEXT 2M
4 MAXEXTENTS 999);
Tablespace altered.
/****************************************************************************************
-- 오프라인 테이블스페이스
****************************************************************************************/
SQL> ALTER TABLESPACE app_data OFFLINE;
Tablespace altered.
SQL> ALTER TABLESPACE app_data ONLINE;
Tablespace altered.
/****************************************************************************************
-- 데이터 파일 이동 : ALTER TABLESPACE
****************************************************************************************/
-- 우선 OFFLINE 이어야 한다.
SQL> ALTER TABLESPACE app_data OFFLINE;
Tablespace altered.
## 파일 이동
$ mv app_data_01.dbf /export/home/oracle8i/iORCL/init/app_data_01.dbf
-- ALTER TABLESPACE로 파일 이동
SQL> ALTER TABLESPACE app_data
2 RENAME DATAFILE '/export/home/oracle8i/iORCL/data/app_data_01.dbf'
3 TO '/export/home/oracle8i/iORCL/init/app_data_01.dbf';
Tablespace altered.
-- ONLINE 변경
SQL> ALTER TABLESPACE app_data ONLINE;
Tablespace altered.
-- 변경 확인
SQL> SELECT tablespace_name, file_name, bytes
2 FROM dba_data_files
3 WHERE tablespace_name = 'APP_DATA';
TABLESPACE_NAME FILE_NAME
---------------- -------------------------------------------------
APP_DATA /export/home/oracle8i/iORCL/init/app_data_01.dbf /* 변경 */
APP_DATA /export/home/oracle8i/iORCL/data/app_data_02.dbf
APP_DATA /export/home/oracle8i/iORCL/data/app03.dbf
APP_DATA /export/home/oracle8i/iORCL/data/app04.dbf
/****************************************************************************************
-- 데이터 파일 이동 : ALTER DATABASE
****************************************************************************************/
-- 데이터베이스를 마운트 한다.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 34869408 bytes
Fixed Size 73888 bytes
Variable Size 11079680 bytes
Database Buffers 23543808 bytes
Redo Buffers 172032 bytes
Database mounted.
## 대상 폴더에 파일을 존재시킨다.
$ mv app_data_02.dbf /export/home/oracle8i/iORCL/init/app_data_02.dbf
-- ALTER DATABASE 문으로 변경한다.
SQL> ALTER DATABASE
2 RENAME FILE
3 '/export/home/oracle8i/iORCL/data/app_data_02.dbf'
4 TO '/export/home/oracle8i/iORCL/init/app_data_02.dbf';
Database altered.
-- 데이터베이스를 오픈한다.
SQL> ALTER DATABASE OPEN;
Database altered.
-- 확인
SQL> SELECT tablespace_name, file_name, bytes
2 FROM dba_data_files
3 WHERE tablespace_name = 'APP_DATA';
TABLESPACE_NAME FILE_NAME
---------------- -------------------------------------------------
APP_DATA /export/home/oracle8i/iORCL/init/app_data_01.dbf
APP_DATA /export/home/oracle8i/iORCL/init/app_data_02.dbf /* 변경 */
APP_DATA /export/home/oracle8i/iORCL/data/app03.dbf
APP_DATA /export/home/oracle8i/iORCL/data/app04.dbf
/****************************************************************************************
-- READ ONLY 테이블스페이스
****************************************************************************************/
SQL> ALTER TABLESPACE app_data READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE app_data READ WRITE;
Tablespace altered.
/****************************************************************************************
-- 테이블스페이스 삭제
****************************************************************************************/
SQL> DROP TABLESPACE app_data
2 INCLUDING CONTENTS;
Tablespace dropped.
SQL> SELECT tablespace_name, file_name
2 FROM dba_data_files;
TABLESPACE_NAME FILE_NAME
---------------- -------------------------------------------------------
SYSTEM /export/home/oracle8i/iORCL/data/system01.dbf
TOOLS /export/home/oracle8i/iORCL/systs/tools01.dbf
RBS /export/home/oracle8i/iORCL/systs/rbs01.dbf
TEMP /export/home/oracle8i/iORCL/systs/temp01.dbf
USERS /export/home/oracle8i/iORCL/systs/users01.dbf
INDX /export/home/oracle8i/iORCL/systs/indx01.dbf
USER_DATA /export/home/oracle8i/iORCL/data/user_data_01.dbf