asm 환경에서 tablespace 관리

[송지환]

[ 전체 tablespace 확인 ]

select * from dba_data_files ; 
select * from dba_tablespaces ;.

[ Temp tablespace 확인 ]

select * from dba_temp_files ;

[ 현재 유저의 default tablespace 확인 ]

select * from user_users ;
==> Defaut Tablespace로 설정된 부분을 확인

[ 유저의 default tablespace 변경 ]

alter user [유저명] default tablespace [테이블스페이스명]

[ 테이블의 tablespace 변경 ]

alter table [테이블명] move tablespace [테이블스페이스명]

[ tablespace 수정이나 삭제시 online/offline 설정 ]

alter tablespace [테이블스페이스명] online;
alter tablespace [테이블스페이스명] offline;

[ tablespace의 물리적인 파일의 이름 또는 위치변경 ]

alter tablespace rename A to B;

SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME                                             
------------------------------------------------------------
SYSTEM                                                      
UNDOTBS1                                                    
SYSAUX                                                      
TEMP                                                        
USERS                                                       
D_TEST                                                      
I_TEST                                                      
7 개의 행이 선택되었습니다.
 
SQL> alter tablespace d_test rename to test_data;
테이블스페이스가 변경되었습니다.
 
SQL> alter tablespace i_test rename to test_index;
테이블스페이스가 변경되었습니다.
 
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME                                             
------------------------------------------------------------
SYSTEM                                                      
UNDOTBS1                                                    
SYSAUX                                                      
TEMP                                                        
USERS                                                       
TEST_DATA                                                   
TEST_INDEX                                                  
7 개의 행이 선택되었습니다.

[ tablespace 공간관리 ]

alter database datafile 'c:\경로\test1.dbf' resize 10M;
  ==> 공간이 가득차면 실행
​
alter tablespace [테이블 스페이스명] add datafile 'c:\경로\test2.dbf' size 10M;
  ==> 공간이 가득차면 실행
​
alter tablespace [테이블 스페이스명] add datafile 'c:\경로\test2.dbf' size 10M
       autoextend on next 10M maxsize 10M;
  ==> 10M씩 자동증가

[ tablespace 삭제 ]

drop tablespace [테이블 스페이스명] include contents;
  ==> 테이블스페이스 내의 객체(테이블,인덱스등)를 다 지운다.
​
drop tablespace [테이블 스페이스명] including contents;
  ==> 테이블스페이스의 모든 세그먼트를 삭제한다.
  ==> 단, 데이타가 있는 테이블스페이스는 삭제할수 없다.
​
drop tablespace [테이블 스페이스명] cascade constraints;
  ==> 삭제된 테이블스페이스 내의 테이블의 기본키와 유일키를 참조하는
      다른 테이블스페이스의 테이블로부터 참조무결성 제약 조건을 삭제한다.        
​
drop tablespace [테이블 스페이스명] including contents and datafiles;
  ==> 물리적파일까지 삭제한다.

[ ASM 환경에서 테이블스페이스 생성 ]

CREATE TABLESPACE AUDIT_SPACE DATAFILE
  '+DATADG_MAIN' SIZE 10000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
  '+DATADG_MAIN' SIZE 10000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
  '+DATADG_MAIN' SIZE 10000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON

[ tablespace 생성1 ]

create tablespace [테이블 스페이스명]
datafile 'c:\경로\test.dbf'
size 10M ==> 여기까지 작성하면 기본적인 사항에 대해서만 생성된다.
autoextend on next 10M ==> (옵션)데이타 파일 용량초과시 자동증가설정
maxsize 100M ==> (옵션)데이타파일 최대크기지정
extent management local ==> (옵션)
uniform size 1M ==> (옵션)
​
​DROP TABLESPACE WELFARE_CCA INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE TS_HANA
DATAFILE '/oracle/oradata/hana/ts_hana01.dbf'
SIZE 1G AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

[ tablespace 생성2 ]

create tablespace [테이블 스페이스명]
datafile 'c:\경로\test.dbf'
size 10M
default storage(
   initial 80k  ==> 테이블 스페이스의 맨 첫번째 extents의 크기
   next 80k  ==> 다음 extents의 크기
   minnextents 1  ==> 생성할 extents의 최소값
   maxnextents 121  ==> 생성할 extents의 최대값
   pctincrease 80  ==> extents의 증가율,(Default값은 50%)
) online ;

[ temp tablespace 생성 ]

create temporary tablespace [테이블 스페이스명]
tempfile 'c:\경로\test_temp.dbf'
size 10M
extent management local
uniform size 512k
  ==> localy managed tablespace nuiform size만 생성가능하다.
       (주의)autoallocate, extent management dicionary option을 사용하면 ora-25319 error 발생한다.
  ==> rename 이 불가능하다.

[ temp tablespace를 default tablespace로 변경 ]

alter database default temporary tablespace [테이블 스페이스명]

[ undo tablespace 생성 ]

create undo tablespace [테이블 스페이스명] ==> 이부분만 다름.
datafile 'c:\경로\test_undo.dbf'
size 10M
autoextend on next 10M
maxsize 100M
  ==> undo_management와 undo_tablespace, undo_retention parameter를 제공
  ==> 지역적으로 관리되는 익스텐트만 사용가능
  ==> 시스템에 의해 관리되는 익스텐트 할당만 사용가능하다.
      (uniformsize를 할당 할 수 없고 autoallocate만 가능)

--데이터 파일 추가
ALTER TABLESPACE TS_HANA ADD DATAFILE
'/data9/orcl/hana_10.dbf' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 20G,
'/data11/orcl/hana_11.dbf' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 20G;.

--기존 데이터 파일 자동증가 중지
ALTER DATABASE
  DATAFILE '/data10/orcl/hana_01.dbf' AUTOEXTEND OFF,
  DATAFILE '/data10/orcl/hana_02.dbf' AUTOEXTEND OFF;.

--테이블 스페이스 조회
select df.tablespace_name "Tablespace",
    round(df.TBS_maxbyte /1073741824,2) "Total(GB)",
    round((df.TBS_byte - nvl(fs.Free_byte,0))/1073741824,2) "Used(GB)",
    round((df.TBS_byte - nvl(fs.Free_byte,0))/df.TBS_maxbyte *100,0) "Used(%)",
    round((df.TBS_maxbyte - (df.TBS_byte - nvl(fs.Free_byte,0))) /1073741824,2) "Free(GB)",
    round(((df.TBS_maxbyte - (df.TBS_byte - nvl(fs.Free_byte,0)))/df.TBS_maxbyte) *100,0) "Free(%)",
    nvl(fs.pieces,0) "Pieces",
    round(nvl(fs.Max_free,0) /1048576) "MaxFree(MB)",
    db.status "Status"
from (select tablespace_name
           , sum(bytes) TBS_byte
           , sum(decode(maxbytes,0,bytes,maxbytes)) TBS_maxbyte
      from dba_data_files
      group by tablespace_name ) df,
     (select tablespace_name, max(bytes) Max_free
          , sum(bytes) Free_byte, count(*) pieces
      from dba_free_space
      group by tablespace_name ) fs,
     (select tablespace_name, initial_extent, next_extent, status
      from dba_tablespaces ) db
where df.tablespace_name = db.tablespace_name
and df.tablespace_name = fs.tablespace_name(+)
order by 6; .