일반테이블스페이스 관리

요약 

테이블 스페이스 전체 조회

select ts.tablespace_name,ts.status,ts.contents,  ts.extent_management,ts.segment_space_management,
df.bytes/1024/1024 MB, df.file_name,df.autoextensible "Auto",df.online_status
from dba_tablespaces ts, dba_data_files df
where ts.tablespace_name=df.tablespace_name;

일반 테이블스페이스 생성

create tablespace haksa datafile '/oradata/EHR/haksa01.dbf' size 1M; -- autoextend on

테이블스페이스 내 데이터파일 추가

alter tablespace haksa add datafile '/oradata/EHR/haksa02.dbf' size 2M;

데이터파일 크기 증가 (수동/자동)

alter database datafile '/oradata/EHR/haksa01.dbf' resize 2M;
alter database datafile '/oradata/EHR/haksa01.dbf' autoextend on;

테이블스페이스/데이터파일 offline

  • 테이블스페이스
    • normal(일반적) / temporary(nomarl불가시) / immediate (Arch모드시에만 사용해야)
    • 테이블스페이스 offline ->online시 체크포인트발생하여 파일간 동기화 필요
  •  데이터파일
    • offline (arch모드가능) / offline drop (no arch모드시)
alter tablespace haksa offline; 

alter database datafile '/oradata/EHR/haksa02.dbf' offline;

테이블스페이스 삭제 (테이블존재시  )

drop tablespace haksa including contents and datafiles;

테이블스페이스 SCN체크

select a.file#,a.ts#,b.name,a.status,a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts#=b.ts#;

예시 -테이블스페이스 SCN체크

  •  
    더보기
    SQL> select a.file#,a.ts#,b.name,a.status,a.checkpoint_change#
    from v$datafile a, v$tablespace b
    where a.ts#=b.ts#;
          FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
          ----- ---------- ---------- ------- ------------------
          1 0 SYSTEM SYSTEM 427710
          2 1 UNDOTBS1 ONLINE 427710
          3 2 SYSAUX ONLINE 427710
          4 4 USERS ONLINE 427710
          5 7 UNDO01 ONLINE 428726
          6 9 HAKSA ONLINE 429871
          6 rows selected.
     
    SQL> alter system checkpoint;
          System altered.
     
    SQL> select a.file#,a.ts#,b.name,a.status,a.checkpoint_change#
    from v$datafile a, v$tablespace b
    where a.ts#=b.ts#;
          FILE# TS# NAME STATUS CHECKPOINT_CHANGE#
          ----- ---------- ---------- ------- ------------------
          1 0 SYSTEM SYSTEM 456314
          2 1 UNDOTBS1 ONLINE 456314
          3 2 SYSAUX ONLINE 456314
          4 4 USERS ONLINE 456314
          5 7 UNDO01 ONLINE 456314
          6 9 HAKSA ONLINE 456314

 

undo tablespace 관리

요약 

신규 생성

create undo tablespace undo01
datafile '/oradata/EHR/undo01.dbf' size 1024M
autoextend on;

undo tablespace 변경

alter system set undo_tablespace=undo01;

세션별 사용중 undo segment 확인

select s.sid,s.serial#,s.username,r.name "ROLLBACK SEG"
from v$session s,v$transaction t,v$rollname r
where s.taddr=t.addr and t.xidusn=r.usn;

예시-undo tablespace 생성 및 변경

  •  
    더보기
    SQL> set line 200
    SQL> col name for a10
    SQL> show parameter undo; 
         NAME TYPE VALUE
         ------------------------------------ ----------- ------------------------------
         undo_management string AUTO
         undo_retention integer 900
         undo_tablespace string UNDOTBS1
     
    SQL> create undo tablespace undo01
    datafile '/oradata/EHR/undo01.dbf' size 10M
    autoextend on; 
         Tablespace created.
     
     
    SQL> col tablespace_name for 10
    SQL> col tablespace_name for a10
    SQL> col file_name for a50
    SQL> select tablespace_name, bytes/1024/1024 MB, file_name
    from dba_data_files; 
         TABLESPACE MB FILE_NAME
         ---------- ---------- --------------------------------------------------
         SYSTEM 430 /oradata/EHR/system01.dbf
         UNDOTBS1 1024 /oradata/EHR/undotbs01.dbf
         SYSAUX 210 /oradata/EHR/sysaux01.dbf
         USERS 5 /oradata/EHR/users01.dbf
         UNDO01 10 /oradata/EHR/undo01.dbf
     
    SQL> alter system set undo_tablespace=undo01; 
         System altered.
     
    SQL> select s.sid,s.serial#,s.username,r.name "ROLLBACK SEG"
    from v$session s,v$transaction t,v$rollname r
    where s.taddr=t.addr and t.xidusn=r.usn; 
         no rows selected

 

 

temp tablespace 관리

요약 

조회 (파일/디폴트 테이블스페이스)

select file_id,tablespace_name,bytes/1024/1024 MB, file_name
from dba_temp_files;

select * from database_properties
where property_name like 'DEFAULT%';

생성

create temporary tablespace temp2
tempfile '/oradata/EHR/temp02.dbf' size 1024M;

 

default temporary tablespace로 변경

alter database default temporary tablespace temp2;

기존 temporary tablepsace 삭제

drop tablespace temp;

예시- temp tablespace 신규 할당 및 default 변경

  •  
    더보기
    SQL> set line 200
    SQL>col tablespace_name for a10
    SQL>col file_name for a50
     
    SQL> select file_id,tablespace_name,bytes/1024/1024 MB, file_name
          from dba_temp_files; 
          FILE_ID TABLESPACE MB FILE_NAME
          ---------- ---------- ---------- --------------------------------------------------
          1 TEMP 1024 /oradata/EHR/temp01.dbf
     
     
    SQL> create temporary tablespace temp2
    tempfile '/oradata/EHR/temp02.dbf' size 10M; 
          Tablespace created.
     
     
    SQL> set line 200
    SQL>col property_name for a30
    SQL>col property_value for a10
    SQL>col description for a50SQL> SQL> SQL>
    SQL> select * from database_properties
    where property_name like 'DEFAULT%'; 
          PROPERTY_NAME PROPERTY_V DESCRIPTION
          ------------------------------ ---------- --------------------------------------------------
          DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
          DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
          DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
     
    SQL> alter database default temporary tablespace temp2;  
          Database altered.
     
    SQL> drop tablespace temp; 
          Tablespace dropped.
     
    SQL> select * from database_properties
    where property_name like 'DEFAULT%'; 
          PROPERTY_NAME PROPERTY_V DESCRIPTION
          ------------------------------ ---------- --------------------------------------------------
          DEFAULT_TEMP_TABLESPACE TEMP2 Name of default temporary tablespace
          DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
          DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
     
    SQL> select file_id,tablespace_name,bytes/1024/1024 MB, file_name
    from dba_temp_files; 
          FILE_ID TABLESPACE MB FILE_NAME
          ---------- ---------- ---------- --------------------------------------------------
          2 TEMP2 10 /oradata/EHR/temp02.dbf
 

 

undo tablespace 관리

요약 

신규 생성

create undo tablespace undo01
datafile '/oradata/EHR/undo01.dbf' size 1024M
autoextend on;

undo tablespace 변경

alter system set undo_tablespace=undo01;

세션별 사용중 undo segment 확인

select s.sid,s.serial#,s.username,r.name "ROLLBACK SEG"
from v$session s,v$transaction t,v$rollname r
where s.taddr=t.addr and t.xidusn=r.usn;

예시-

  •  
    더보기
    SQL> set line 200
    col name for a10
    SQL> show parameter undo;   
          NAME TYPE VALUE
          ------------------------------------ ----------- ------------------------------
          undo_management string AUTO
          undo_retention integer 900
          undo_tablespace string UNDOTBS1
    SQL> create undo tablespace undo01
    datafile '/oradata/EHR/undo01.dbf' size 10M
    autoextend on; 
          Tablespace created.
     
     
    SQL> col tablespace_name for 10
    SQL> col tablespace_name for a10
    SQL> col file_name for a50
    SQL> select tablespace_name, bytes/1024/1024 MB, file_name
    from dba_data_files; 
          TABLESPACE MB FILE_NAME
          ---------- ---------- --------------------------------------------------
          SYSTEM 430 /oradata/EHR/system01.dbf
          UNDOTBS1 1024 /oradata/EHR/undotbs01.dbf
          SYSAUX 210 /oradata/EHR/sysaux01.dbf
          USERS 5 /oradata/EHR/users01.dbf
          UNDO01 10 /oradata/EHR/undo01.dbf
     
    SQL> alter system set undo_tablespace=undo01; 
          System altered.
     
    SQL> select s.sid,s.serial#,s.username,r.name "ROLLBACK SEG"
    from v$session s,v$transaction t,v$rollname r
    where s.taddr=t.addr and t.xidusn=r.usn; 
          no rows selected
저작자표시 비영리 변경금지

'Oracle > 기타' 카테고리의 다른 글

[관리] 계정/ArchiveLog  (0) 2022.03.23
[관리] 파일 (data/redolog/control file)  (0) 2022.03.23
[기타] DB Link 구성  (0) 2022.03.23
[기타]다중 인스턴스 구성 (clone 생성 및 복원)  (0) 2022.03.23

+ Recent posts

티스토리툴바