반응형
/********************************************************************************************
-- Title : [10g] 테이블스페이스 관리 - ver.dbrang
-- Key word : tablespace 디폴트 nologging read write 이동 파일이동 삭제
********************************************************************************************/

-- 관리 스크립트
SELECT Y.TS#, Y.TABLESPACE_NAME AS TABLESPACE, Y.FILE#, Y.FILE_NAME
         , TO_CHAR(Y.BYTES/1024/1024, '999,990.00') AS FILE_SIZE_MB
         , TO_CHAR(Y.INC, '999,999,990.00') || ' KB' AS GROWTH
         , TO_CHAR(Y.MAXBYTES/1024/1024, '999,990.00') AS MAX_SIZE_MB
         , TO_CHAR(Y.FREE_BYTES/1024/1024, '999,990.00') 
           || ' (' || LTRIM(TO_CHAR(Y.FREE_BYTES / Y.BYTES * 100, '990.00')) || '%)' AS FREE_SIZE_MB
         , Y.STATUS AS FILE_STATUS, Y.ENABLED--, Y.AUTOEXTENSIBLE
         , '--' AS X, X.STATUS AS TS_STATUS
         , X.CONTENTS, X.DEFAULT_TS, X.BIGFILE, X.LOGGING, X.ALLOCATION_TYPE AS ALLOCATION
         , X.EXTENT_MANAGEMENT AS EXT_MNG, X.SEGMENT_SPACE_MANAGEMENT AS SEG_MNG
         , TO_CHAR(X.INITIAL_EXTENT, '999,999,990') AS INIT_EXT
         , TO_CHAR(X.NEXT_EXTENT, '999,999,990') AS NEXT_EXT
         , TO_CHAR(X.MIN_EXTENTS, '999,999,990') AS MIN_EXT
         , TO_CHAR(X.MAX_EXTENTS, '999,999,999,990') AS MAX_EXT
    FROM
    (
        SELECT A.TABLESPACE_NAME, A.STATUS, A.CONTENTS, B.DEFAULT_TS
             , A.LOGGING, A.ALLOCATION_TYPE, A.PLUGGED_IN
             , A.EXTENT_MANAGEMENT          -- 9I 이상부터 사용
             , A.SEGMENT_SPACE_MANAGEMENT   -- 9I 이상부터 사용
             , A.BIGFILE                    -- 10G 이상부터 사용 
             , A.INITIAL_EXTENT
             , A.NEXT_EXTENT
             , A.MIN_EXTENTS
             , A.MAX_EXTENTS
        FROM DBA_TABLESPACES A
        LEFT OUTER JOIN (SELECT PROPERTY_VALUE AS TS_NM
                              , 'DEFAULT' AS DEFAULT_TS
                        FROM DATABASE_PROPERTIES
                        WHERE PROPERTY_NAME 
                        IN ('DEFAULT_TEMP_TABLESPACE','DEFAULT_PERMANENT_TABLESPACE')
                        ) B
        ON A.TABLESPACE_NAME = B.TS_NM
    ) X 
    INNER JOIN
    (
        SELECT V.TS#, D.TABLESPACE_NAME, D.FILE_ID AS FILE#, D.FILE_NAME
             , D.BYTES
             , D.INCREMENT_BY * 8 AS INC
             , D.MAXBYTES
             , D.STATUS || '/' || V.STATUS AS STATUS
             , V.ENABLED, D.AUTOEXTENSIBLE
             , D.USER_BYTES
             , D.FREE_BYTES
        FROM 
        (
            SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME
                 , BYTES, BLOCKS, STATUS
                 , RELATIVE_FNO, AUTOEXTENSIBLE, MAXBYTES, INCREMENT_BY
                 , (select sum(bytes) from dba_free_space where file_id = aa.file_id) "FREE_BYTES"            
                 , USER_BYTES, USER_BLOCKS, ONLINE_STATUS
            FROM DBA_DATA_FILES aa
            UNION ALL
            SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES, BLOCKS, STATUS
                 , RELATIVE_FNO, AUTOEXTENSIBLE, MAXBYTES, INCREMENT_BY
                 , (select sum(bytes) from dba_free_space where file_id = bb.file_id) "FREE_BYTES"  
                 , USER_BYTES, USER_BLOCKS, 'ONLINE' AS ONLINE_STATUS
            FROM DBA_TEMP_FILES bb
        ) D
        INNER JOIN 
        (
            SELECT FILE#, CREATION_CHANGE#, CREATION_TIME, TS#
                 , STATUS, ENABLED, BYTES, BLOCKS, NAME 
            FROM V$DATAFILE
            UNION ALL
            SELECT FILE#, CREATION_CHANGE#, CREATION_TIME, TS#
                 , STATUS, ENABLED, BYTES, BLOCKS, NAME
            FROM V$TEMPFILE
        ) V
        ON D.FILE_NAME = V.NAME
    ) Y
    ON X.TABLESPACE_NAME = Y.TABLESPACE_NAME
    ORDER BY Y.TS#, Y.FILE#;
 
 

/**************************
-- 테이블스페이스 생성 예
**************************/
-- TS 생성 예
CREATE TABLESPACE ts_data0
DATAFILE '/home/oracle/oradata/INFRAORA/ts_data0_01.dbf' size 10M;

CREATE TABLESPACE ts_data1
DATAFILE '/home/oracle/oradata/INFRAORA/ts_data1_01.dbf' size 10M
                  AUTOEXTEND ON;

CREATE TABLESPACE ts_data2
DATAFILE '/home/oracle/oradata/INFRAORA/ts_data2_01.dbf' size 10M
                  AUTOEXTEND ON NEXT 8K MAXSIZE 20M;  -- 파일크기 size 10M 보다 작으면 에러

CREATE TABLESPACE ts_data3
DATAFILE '/home/oracle/oradata/INFRAORA/ts_data3_01.dbf' size 10M
                  AUTOEXTEND ON NEXT 16k MAXSIZE UNLIMITED;

CREATE TABLESPACE ts_data4
DATAFILE '/home/oracle/oradata/INFRAORA/ts_data4_01.dbf' size 10M
                  AUTOEXTEND ON NEXT 8k MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;  -- 64kb가 default

CREATE TABLESPACE ts_data5
DATAFILE '/home/oracle/oradata/INFRAORA/ts_data5_01.dbf' size 100M
                  AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,
             '/home/oracle/oradata/INFRAORA/ts_data5_02.dbf' size 200M
                  AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;   -- 1M가 default

CREATE TABLESPACE ts_data7 /* Default Template */
DATAFILE '/home/oracle/oradata/INFRAORA/ts_data7_01.dbf' size 100M
                  AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED,
             '/home/oracle/oradata/INFRAORA/ts_data7_02.dbf' size 200M
                  AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

-- TS 생성 확인
SELECT *
FROM DBA_TABLESPACES;
 
 
/****************************
-- 테이블스페이스 공간 관리
****************************/
-- 1. 데이터 파일 크기 확인
SELECT tablespace_name, file_name, bytes
FROM dba_data_files
WHERE tablespace_name = 'TS_DATA0';

-- 크기 변경
ALTER DATABASE
DATAFILE '/home/oracle/oradata/INFRAORA/ts_data0_01.dbf' RESIZE 20M;

ALTER DATABASE
DATAFILE '/home/oracle/oradata/INFRAORA/ts_data0_01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

-- 데이터 파일 크기 변경 확인
SELECT tablespace_name, file_name, bytes
FROM dba_data_files
WHERE tablespace_name = 'TS_DATA0';

-- 2. 파일 확인
SELECT tablespace_name, file_name, bytes
FROM dba_data_files
WHERE tablespace_name = 'TS_DATA0';

-- 새로운 파일 추가
ALTER TABLESPACE ts_data0
ADD DATAFILE '/home/oracle/oradata/INFRAORA/ts_data0_03.dbf' SIZE 20M
    AUTOEXTEND ON NEXT 5M MAXSIZE 20M;

-- 파일 추가 확인
SELECT tablespace_name, file_name, bytes
FROM dba_data_files
WHERE tablespace_name = 'TS_DATA0';

 
/****************************
-- 테이블스페이스 이름변경
****************************/
-- TS 이름 확인
SELECT *
FROM dba_tablespaces
WHERE tablespace_name = 'TS_DATA0';

-- TS 이름 변경
ALTER TABLESPACE TS_DATA0
RENAME TO TS_DATA00;

-- TS 이름 변경 확인
SELECT *
FROM dba_tablespaces
WHERE tablespace_name = 'TS_DATA00';

-- TS 이름 원위치
ALTER TABLESPACE TS_DATA00
RENAME TO TS_DATA0;
 
 
/***********************************************
-- 테이블스페이스 읽기전용/로깅/오프라인 변경
***********************************************/
-- 상태 확인
SELECT tablespace_name, logging, status
FROM dba_tablespaces
WHERE tablespace_name = 'TS_DATA1';

-- 읽기 전용
ALTER TABLESPACE ts_data2 READ WRTIE;

-- 상태 확인
SELECT tablespace_name, logging, status
FROM dba_tablespaces
where tablespace_name = 'TS_DATA1';

-- NO로깅
ALTER TABLESPACE ts_data2 NOLOGGING;

-- 상태 확인
SELECT tablespace_name, logging, status
FROM dba_tablespaces
where tablespace_name = 'TS_DATA1';

-- OFFLINE
ALTER TABLESPACE ts_data2 OFFLINE;

-- 상태 확인
SELECT tablespace_name, logging, status
FROM dba_tablespaces
where tablespace_name = 'TS_DATA1';
 
 
/***********************************************
-- 테이블스페이스 파일 이동(OPEN상태에서)
***********************************************/
-- OFFLINE
ALTER TABLESPACE ts_data0 OFFLINE; 

-- OFFLINE 확인(status)
SELECT *
FROM dba_tablespaces
WHERE tablespace_name = 'TS_DATA1';

-- 데이터 파일 확인
SELECT tablespace_name, file_name
FROM dba_data_files
WHERE tablespace_name = 'TS_DATA1';

-- 데이터 파일 이동(+파일명변경)
> mv ts_data1_01.dbf ../ts_data01_01.dbf

-- ALTER TABLESPACE로 파일 정보 갱신
ALTER TABLESPACE ts_data0
RENAME DATAFILE '/home/oracle/oradata/INFRAORA/ts_data1_01.dbf'
TO '/home/oracle/oradata/ts_data01_01.dbf';

-- 변경된 데이터 파일 확인
SELECT tablespace_name, file_name
FROM dba_data_files
WHERE tablespace_name = 'TS_DATA1';

-- ONLINE
ALTER TABLESPACE ts_data0 ONLINE;
 
 
/***********************************************
-- 테이블스페이스 파일 이동(CLOSE상태에서)
***********************************************/
-- DB 셧다운
> shutdown immediate;

-- 데이터 파일 이동(+파일명 변경)
> mv ts_data0_01.dbf ./INFRAORA/ts_data01_01.dbf

-- DB 마운트
startup mount;

-- 데이터 파일 확인
SELECT b.TS#, b.name, a.name
FROM v$datafile a, v$tablespace b
WHERE a.TS# = b.TS#
AND b.name = 'TS_DATA0';

-- ALTER DATABASE로 파일 정보 갱신
ALTER DATABASE
RENAME FILE '/home/oracle/oradata/ts_data01_01.dbf'
TO '/home/oracle/oradata/INFRAORA/ts_data1_01.dbf';

-- 변경된 데이터 파일 확인
SELECT b.TS#, b.name, a.name
FROM v$datafile a, v$tablespace b
WHERE a.TS# = b.TS#
AND b.name = 'TS_DATA0';

-- DB 오픈
ALTER DATABASE OPEN;
 
 
/***********************************************
-- 디폴트 테이블스페이스 지정
***********************************************/
-- 디폴트 테이블스페이스 확인
select *
from database_properties
where property_name like 'DEFAULT%';

-- 디폴트 테이블스페이스 변경/지정
alter database default tablespace ts_data1;

-- 디폴트 테이블스페이스 변경 확인
select *
from database_properties
where property_name like 'DEFAULT%';


/***********************************************
-- 테이블스페이스 삭제
***********************************************/
-- TS 삭제
DROP TABLESPACE app_data
INCLUDING CONTENTS;

 

반응형
저작자표시 비영리

+ Recent posts

티스토리툴바